Data Management Foundations

Database Application

Software that helps business users interact with database systems.

Database Roles

Database administrator, authorization, rules, query processor, query optimization, storage manager, transaction manager

Database Administrator

Responsible for securing the database system against unauthorized users; enforces procedures for user access & database system availability

Authorization

Authorize individual users to access specific data (tables, columns, or rows of a database)

Query Processor

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

Query Optimization

Ensures the most efficient instructions are executed on the data

Storage Manager

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

Indexes

Used by the storage manager to quickly locate data

Transaction Manager

Ensures transactions are properly executed, prevents conflicts between concurrent transactions, and restores the database to a consistent state in the event of a transaction or system failure

INSERT

Inserts rows into a table

SELECT

Retrieves data from a table

UPDATE

Modifies data in a table

DELETE

Deletes rows from a table

CREATE TABLE

Creates a new table by specifying the table and column names

Data Type

Indicates the format of column values; 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

Analysis Phase

Specifies database requirements without regard to a specific database system. Requirements are represented as entities, relationships, and attributes.

Logical Design Phase

Implements database requirements in a specific database system. For relational database systems, converts entities, relationships, and attributes into tables, keys, and columns.

Physical Design Phase

Adds indexes and specifies how tables are organized on storage media. Affects query processing speed but never affects the query result.

Data Independence

The principle that physical design never affects query results

Application Programming Interface (API)

Typically used by database programs to simplify the use of SQL with a general-purpose language

MySQL Command-Line Client

A text interface included in the MySQL Server download. Returns an error code and description when an SQL statement is syntactically incorrect or the database cannot execute the statement.

Tuple

An ordered collection of elements enclosed in parentheses

Table

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

Column

Has a name and a data type

Row

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

Business Rules

Based on business policy and specific to a particular database

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.

Keywords

Words with special meaning.

ex. SELECT, FROM, WHERE

Identifiers

Objects from the database like tables, columns, etc.

Comments

Statement intended only for humans and ignored by the database when parsing an SQL statement

Data Definition Language (DDL)

Defines the structure of the database

Data Query Language (DQL)

Retrieves data from a 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

Data Independence

Allows database administrators to improve query performance by changing the organization of data on storage devices, without affecting query results

DROP TABLE

Deletes a table, along with all the table's rows, from a database

ALTER TABLE

Adds, deletes, or modifies columns on an existing table

Integer Data Types

Represent positive and negative integers, varying by the number of bytes allocated for each value.

TINYINT

1 byte; Signed range: -128 to 127; Unsigned range: 0 to 255

SMALLINT

2 bytes; Signed range: -32,768 to 32,767; Unsigned range: 0 to 65,535

INTEGER or INT

4 bytes; Signed range: -2,147,483,648 to 2,147,483,647; Unsigned range: 0 to 4,294,967,295

MEDIUMINT

3 bytes; Signed range: -8,388,608 to 8,388,607; Unsigned range: 0 to 16,777,215

BIGINT

8 bytes; Signed range: -263 to 263 -1; Unsigned range: 0 to 264 -1

% (modulo)

Divides one numeric value by another and returns the integer remainder

^ (Exponentiation)

Raises one numeric value to the power of another

= (Equality)

Compares two values for equality

!= (Inequality)

Compares two values for inequality

UPDATE Statement

Uses the SET clause to specify the new column values. An optional WHERE clause specifies which rows are updated. Omitting the WHERE clause results in all rows being updated.

DELETE Statement

Deletes existing rows in a table. The FROM keyword is followed by the table name whose rows are to be deleted. An optional WHERE clause specifies which rows should be deleted. Omitting the WHERE clause results in all rows in the table being deleted.

TRUNCATE statement

Deletes all rows from a table, similar to DELETE but with minor differences depending on the database system.

MERGE statement

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

Primary Key

A column, or group of columns, used to identify a row, usually the table's first column

Simple Primary Key

A primary key that consists of a single column

Composite Primary Key

The primary key formed by combining two or more columns in a table

Auto-Increment Column

A numeric column that is assigned an automatically incrementing value when a new row is inserted

Primary Key Errors

Inserting values for auto-increment primary keys, omitting values for primary keys that are not auto-increment columns

Foreign Key

A column, or group of columns, that refer to a primary key

Foreign Key Constraint

When specified, the database rejects insert, update, and delete statements that violate referential integrity

RESTRICT

Rejects an insert, update, or delete that violates referential integrity

CASCADE

Propagates primary key changes to foreign keys

Constraint

A rule that governs allowable values in a database, based on relational and business rules

Adding & Dropping Constraints

Constraints are added and dropped with the ALTER TABLE TableName followed by an ADD, DROP, or CHANGE clause

BETWEEN operator

Provides an alternative way to determine if a value is between two other values

LIKE operator

When used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _

ORDER BY clause

Orders selected rows by one or more columns in ascending (alphabetic or increasing) order. The DESC keyword with the ORDER BY clause orders rows in descending order.

ABS function

Returns the absolute value of a number

LOWER(s) Function

Returns the lowercase "s". Or any specified characters.

TRIM Function

Removes leading and trailing spaces in a text string, but spaces between words are not removed

HOUR(t), MINUTE(t), SECOND(t) Function

Returns the hour, minute, or second from time t

Aggregate Function

Processes values from a set of rows and returns a summary value

COUNT function

Counts the number of rows in the set

MIN function

Finds the minimum value in the set

MAX function

Finds the maximum value in the set

SUM Function

Sums all the values in the set

AVG Function

Computes the arithmetic mean of all the values in the set

HAVING clause

Used with the GROUP BY clause to filter group results

Join

A SELECT statement that combines data from two tables, known as the left table and right table, into a single result. The tables are combined by comparing columns from the left and right tables, usually with the = operator. The columns must have comparable data types.

Alias

Used to replace a column name. Follows the column name, separated by an optional AS keyword.

JOIN clause

Determines how a join query handles unmatched rows

INNER JOIN

Selects only matching left and right table rows

FULL JOIN

Selects all left and right table rows, regardless of match

LEFT JOIN

Selects all left table rows, but only matching right table rows

RIGHT JOIN

Selects all right table rows, but only matching left table rows

Outer Join

Any join that selects unmatched rows, including left, right, and full joins

UNION keyword

Combines the two results into one table

Equijoin

Compares columns of two tables with the = operator

Non-Equijoin

Compares columns with an operator other than =, such as <, >

Self Join

Joins a table to itself

CROSS JOIN

Combines two tables without comparing columns. Uses CROSS JOIN clause without an ON clause.

Subquery

A query nested within another SQL query. Sometimes called a nested query or inner query.

Materialized View

A view for which data is stored at all times. Whenever a base table changes, the corresponding view tables can also change, so materialized views must be refreshed.

WITH CHECK OPTION

The database rejects inserts and updates that do not satisfy the view query WHERE clause

Entity-Relationship Model

A high-level representation of data requirements, ignoring implementation details

Reflexive Relationship

A relationship that relates an entity to itself

Entity Relationship Diagram

A schematic picture of entities, relationships, and attributes. Entities are drawn as rectangles. Commonly called an ER diagram.