Data Management Foundations

A database application is

software that helps business users interact with database systems

A database administrator is

responsible for securing the database system against unauthorized users.

A database administrator enforces

procedures for user access and database system availability.

Authorization meaning when it comes to database access

Many database users should have limited access to specific tables, columns, or rows of a database

Database systems authorize

individual users to access specific data.

Rules of database systems

Database systems ensure data is consistent with structural and business rules

The architecture of a database system describes

the internal components and the relationships between components

The query processor

interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application

The query processor performs

query optimization to ensure the most efficient instructions are executed on the data.

The storage manager translates

the query processor instructions into low-level file-system commands that modify or retrieve data

Database sizes range from

from megabytes to many terabytes,

the storage manager uses what to quickly locate data

indexes

Metadata is

data about the database, such as column names and the number of rows in each table.

A relational database stores

data in tables, columns, and rows, similar to a spreadsheet.

All relational database systems support what query language model

SQL

Relational systems are ideal for

databases that require an accurate record of every transaction, such as banking, airline reservation systems, and student records.

The newer non-relational systems are called

NoSQL, for 'not only SQL'

NoSQL, for 'not only SQL' is optimized for

big business

MongoDB is what type of database

NoSQL

An SQL statement is a

database command, such as a query that inserts, selects, updates, or deletes data:

INSERT

inserts rows into a table.

SELECT

retrieves data from a table.

UPDATE

modifies data in a table.

DELETE

deletes rows from a table.

The SQL CREATE TABLE statement creates

creates a new table by specifying the table and column names.

Each column is assigned a data type that indicates

the format of column values. Data types can be numeric, textual, or complex

INT

stores integer values.

DECIMAL

stores fractional numeric values.

VARCHAR

stores textual values

DATE

stores year, month, and day.

For large, complex databases, the process has three phases

1. Analysis

2. Logical design

3. Physical design

The analysis phase specifies

database requirements without regard to a specific database system.

Requirements are represented as

entities, relationships, and attributes. An entity is a person, place, activity, or thing

A relationship is a link

between entities, and an attribute is a descriptive property of an entity.

Analysis has many alternative names, such as

conceptual design, entity-relationship modeling, and requirements definition.

Rectangles represent

entities

Entity names appear

at the top of rectangles

The logical design phase implements

database requirements in a specific database system

. For relational database systems, logical design

converts entities, relationships, and attributes into tables, keys, and columns.

The physical design phase adds

indexes and specifies how tables are organized on storage media.

Physical design affects

query processing speed but never affects the query result

The principle that physical design never affects query results is called

data independence.

Data independence allows

database designers to tune query performance without changes to application programs.

To simplify the use of SQL with a general-purpose language

database programs typically use an application programming interface

An application programming interface, or API

is a library of procedures or classes that links a host programming language to a database.

The MySQL Command-Line Client is

a text interface included in the MySQL Server download.

the 'world' database

a database that is usually installed with MySQL.

MySQL Server returns an error code and description when an

SQL statement is syntactically incorrect or the database cannot execute the statement.

Data structures

that prescribe how data is organized.

Rules

govern valid data.

. A tuple is

an ordered collection of elements enclosed in parentheses.

A table has a

name, a fixed tuple of columns, and a varying set of rows.

A column has a

name and a data type.

A row is an

unnamed tuple of values. Each value corresponds to a column and belongs to the column's data type.

A data type is a

· named set of values, from which column values are drawn.

synonym for table

File, Relation

synonym for row

Record, Tuple

synonym for column

Field, Attribute

Select

selects a subset of rows of a table.

Join

combines two tables by comparing related columns.

Union

selects all rows of two tables.

Aggregate

· computes functions over multiple table rows, such as sum and count.

Rules are

logical constraints that ensure data is valid.

Business rules are based on

business policy and specific to a particular database.

SQL is the standard language for

relational databases, and is commonly supported in non-relational databases

Literals

Explicit values that are string, numeric, or binary.Strings must be surrounded by single quotes or double quotes.Binary values are represented with x'0' where the 0 is any hex value.

examples of Literals

'String'"String"123x'0fa2'

Keywords

Words with special meaning

examples of keywords

SELECT, FROM, WHERE

Identifiers

Objects from the database like tables, columns, etc.

examples of identifiers

City, Name, Population

Data Definition Language

(DDL) defines the structure of the database.

Data Query Language

(DQL) retrieves data from the database.

Data Manipulation Language

DML) manipulates data stored in a database.

Data Control Language (

DCL) controls database user access.

Data Transaction Language

DTL) manages database transactions.

A cell is a

single column of a single row.

No row order

Rows are not ordered.

Rule 7 is called

data independence.

Data independence allows database administrators to

improve query performance by changing the organization of data on storage devices, without affecting query results.

INT or INTEGER

integer values

VARCHAR(N)

values with 0 to N characters

DECIMAL(M, D)

numeric values with M digits, of which D digits follow the decimal point

The ALTER TABLE statement

adds, deletes, or modifies columns on an existing table.

TINYINT

1 byte

SMALLINT

2 BYTES

MEDIUMINT

3 BYTES

INTEGER or INT

4 BYTES

BIGINT

8 BYTES

% (modulo)

Divides one numeric value by another and returnsthe integer remainder

^

Raises one numeric value to the power of another

=

Compares two values for equality

!=

Compares two values for inequality

NULL is a

special value that represents either unknown or inapplicable data

table. The UPDATE statement uses the

SET clause to specify the new column values.

The FROM keyword is followed by

the table name whose rows are to be deleted

The TRUNCATE statement

deletes all rows from a table

The MERGE statement

selects data from one table, called the source, and inserts the data to another table, called the target

A primary key is

a column, or group of columns, used to identify a row.

The primary key is usually

the table's first column and appears on the left of table diagrams, but the position is not significant to the database.