D426 Data Management Foundations 2

relational algebra

operations based on set theory that generate a result table from one or two input tables; these are the theoretical foundation of the SQL language

project (SQL operation)

selects only specified columns of a table

product (SQL operation)

lists all combinations of specified rows of two tables

join (SQL operation)

combines two tables by comparing related columns

union (SQL operation)

selects all rows of two tables

intersect (SQL operation)

selects rows common to two tables

difference (SQL operation)

selects rows that appear in one table but not another

rename (SQL operation)

changes a table name

aggregate (SQL operation)

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

relational rules

part of the relational model and govern data in every relational database

Ex.

• Unique primary key

• Unique column names

• No duplicate rows

business rules

based on business policy and specific to a particular database

SQL constraints

the methods by which relational rules and business rules are implemented on a database, which the database system then enforces, though some business rules may be enforced by applications running on the database instead

SQL clause

a group of SQL keywords like SELECT, FROM, and WHERE with table names like City, column names like Name, and conditions like Population > 100000

• SELECT Name

• FROM City

• WHERE Population > 100000;

each has its own line

literals

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

keywords

words with special meaning

eg. select, from, where

identifiers

objects from the database like tables, columns, etc

comments

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

SQL sublanguages

1. Data Definition Language (DDL)

2. Data Query Language (DQL)

3. Data Manipulation Language (DML)

4. Data Control Language (DCL)

5. Data Transaction Language (DTL)

DDL; Data Definition Language

an SQL sublanguage that defines the structure of the database

DQL; data query language

an SQL sublanguage that retrieves data from the database

DML; data manipulation language

an SQL sublanguage that manipulates data stored in a database

DCL; data control language

an SQL sublanguage that controls database user access

DTL; data transaction language

an SQL sublanguage that manages database transactions

automated script

a series of SQL statements that are prepared in advance and saved in a file or as a database stored procedure so that they can be executed repeatedly as a set by invoking the name of the file or stored procedure either by a computer program or from the SQL command line

database system instance

a single executing copy of a database system

CREATE DATABASE DatabaseName

creates a new database

DROP DATABASE DatabaseName

deletes a database, including all the tables in it

SHOW

provides database users and administrators with information about databases, the database contents (tables, columns, etc.), and server status information

SHOW DATABASES

lists databases available in the database system

SHOW TABLES

lists tables available in the currently selected database

SHOW COLUMNS

lists columns available in a specific table named by a FROM clause

SHOW CREATE TABLE

shows the CREATE TABLE statement that was used for a given table

USE

selects a database and is required to show information about tables within a specific database

table

has a name, a fixed sequence of columns, and a varying set of rows

column

has a name and a data type

row

an unnamed sequence of values, each of which corresponds to a column and belongs to the column’s data type/domain

cell

a single column of a single row

empty table

a table without rows

ALTER TABLE

adds, delete, or modifies columns on an existing table by specifying the table name followed by a clause that indicates what should be altered

ADD

an alter table clause that adds columns to a table

change

an alter table clause that changes the name and data type of an existing column

DROP

an alter table statement that deletes a column

TRANSPOSE

an alter table operation in which rows become columns and columns become rows. Not commonly applied to tables, but important for mathematical matrices.

LIMIT

a clause that can be added to MySQL statements to limit the number of rows returned by e.g. SELECT statements

WHERE

a clause that specifies a condition for selecting rows

fully NULL

when a simple or composite foreign key is NULL in all columns

referential integrity

a relational rule that requires foreign key values are either fully NULL or match some primary key value

1. A primary key is updated

2. A foreign key is updated

3. A row containing a primary key is deleted

4. A row containing a foreign key is inserted

Referential integrity violations

RESTRICT

a referential integrity action that rejects an insert, update, or delete that would violate referential integrity. The only RI action supported in MySQL for foreign key inserts and updates.

SET NULL

a referential integrity action that sets invalid foreign keys to NULL

SET DEFAULT

a referential integrity action that sets invalid foreign keys to the foreign key default value. Not supported in some MySQL configurations.

CASCADE

a referential integrity action that causes changes to primary keys to affect foreign keys as well.

ON UPDATE/ON DELETE

optional clauses of the FOREIGN KEY constraints that are followed by a referential integrity action keyword

primary key

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

1. Unique

2. Not NULL

requirements for simple primary keys to ensure that the key values identify exactly one row each

composite primary key

a primary key that consists of multiple columns, which are denoted with parentheses

1. Unique

2. Not NULL

3. Minimal

requirements for composite primary keys to ensure that each value corresponds to only one row

PRIMARY KEY (column_name, additional_columns)

syntax for the primary key constraint

AUTO_INCREMENT

a constraint that can be applied to a numeric column which will cause it to assign an automatically incrementing value when a new row is inserted

foreign key

a column, or group of columns, that refer to a primary key on the same table or another. They may be repeated, and they may be NULL. They must follow referential integrity.

FOREIGN KEY (ColumnName) REFERENCES TableName(column_name)

syntax for a foreign key clause

constraint

a rule that governs allowable values in a database based on relational and business rules and implemented with special keywords in a CREATE TABLE statement

column constraint

a constraint that appears after the column name and data type in a CREATE TABLE statement and governs values in that column

Ex: NOT NULL, DEFAULT

table constraint

a constraint that appears in a separate clause of a CREATE TABLE statement and governs values in one or more columns

Ex: FOREIGN KEY

UNIQUE (ColumnName, column_name)

a constraint that ensures that values in a column, or group of columns, are unique and can be applied to either a column or as a table constraint. Violating statements are rejected.

CHECK (expression)

specifies an expression on one or more columns of a table and is violated if the expression is false. A NULL value causes the statement to evaluate as “unknown” and is not a violation.

CONSTRAINT new_name KEYWORD SYNTAX

an optional keyword available for table constraints only, which allows the user to specify a different name than the default for any constraint

SELECT Column_Name, Constraint_Name
FROM Information_Schema.Key_Column_Usage
WHERE Table_Name = 'TableName';

statement to have MySQL display all names of constraints on TableName, including default names

CHANGE CurrentColumnName NewColumnName NewDataType [ConstraintDeclaration]

clause used as part of an ALTER TABLE statement to change a named constraint

ADD CONSTRAINT ConstraintName ConstraintDeclaration

clause to add a constraint as part of an ALTER TABLE statement. Will fail when the table already contains data that violates the constraint

DROP CONSTRAINT ConstraintName

clause to drop a constraint as part of an ALTER TABLE statement