Notes on upcoming exam #

The join symbol on canvas will be represented as

SQL #

File: SQL Slides

We can use SQL to implement our relational models in code.

History #

We will be covering the SQL core.

• DDL = data definition language, this corresponds to our relational model.
• insert, delete, update
• query

DBMS should implement all of the SQL standards, but they may not in some places.

CREATE TABLE #

In SQL, we call our relations “tables”.

CREATE TABLE DEPARTMENT(
    DNAME        VARCHAR(10)     NOT NULL,
    DNUMBER      INTEGER         NOT NULL,
    MGRSSN       CHAR(9),
    MGRSTARTDATE CHAR(9)
);

• The variable names are followed by their domain, ie VARCHAR(10) or INTEGER.
• We can also specify the NOT NULL constraint for mandatory attributes.

Type definitions can be different depending on the DBMS.

CREATE TABLE DEPARTMENT(
    DNAME VARCHAR(10) NOT NULL,
    DNUMBER INTEGER NOT NULL CHECK (DNUMBER > 0 AND DNUMBER < 21),
    MGRSSN CHAR(9),
    MGRSTARTDATE DATE,
    PRIMARY KEY (DNUMBER),
    UNIQUE (DNAME),
    FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE (SSN)
);

• VARCHAR(10) means a variable length string, up to 10
• We can specify the size of integers after CHECK
• We can specify primary key using PRIMARY KEY. We also explicitly specify that the primary key is NOT NULL.
• We can specify that the department name is unique using UNIQUE, this means that the department name is a candidate key (even though we picked department number as the primary key). Multiple attributes can be specified inside a UNIQUE, delimited by commas.
• We can specify foreign keys and where they reference using FOREIGN KEY.

The DBMS will enforce these constraints for you when inserting tuples.

Referential integrity options #

When we delete or update, these options can specify the behavior on a constraint violation.

For example

-- inside CREATE TABLE EMPLOYEE
    DNO INT NOT NULL DEFAULT 1,
    FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNUMBER)
        ON DELETE SET DEFAULT
        ON UPDATE CASCADE,
-- ...

• We can specify the default values using DEFAULT
• If we were adding an employee with a department number that doesn’t exist, the DBMS would reject the operation by default. But by adding our referential integrity options after the reference, we can specify more operations when that violation happens.

ON DELETE SET DEFAULT

ON UPDATE CASCADE

Note: If we did something like ON DELETE CASCADE, it would delete both the department number and the employees with that foreign key will be deleted as well. Everything referenced to it will be deleted.

In MySQL, the syntax is

foreign key (DNO) references DEPARTMENT (DNUMBER)
    on delete restrict 
    on update no action

For our events, we can do

• restrict is the same as rejecting the operation
• cascade
• set null
• no action

Note: MySQL may or may not be case sensitive, it depends on the host. We will use MySQL on a linux system, so it is case sensitive (table name / attribute names must be full caps).

Naming constraints #

DROP TABLE #

Allows us to remove tables.

DROP TABLE DEPENDENT;

The DBMS will check if anything is using the DEPENDENT table, and if so it will reject the operation. If your intent is to drop that table and all of its references, those references must be dropped first.

Note: It is suggested not to use RESTRICT and CASCADE when dropping tables. Some DBMS do not support them, MySQL doesn’t change its behavior if they are included.

Order to create and drop tables #

If we are creating both employee and department, we want to create department first because employee will have a foreign key referencing department.

CREATE TABLE DEPARTMENT(
-- attributes
);
CREATE TABLE EMPLOYEE(
-- attributes with foreign key referencing DEPARTMENT
);

When we are dropping tables, we want to drop the employee first, then the department. We need to do it in this order because dropping the department will be rejected by default because some employees reference it.

DROP TABLE EMPLOYEE;
DROP TABLE DEPARTMENT;

ALTER TABLE #

We can use ALTER in place of dropping then recreating.

We can add an additional attribute like

ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12);

If we want to drop the address in an employee:

ALTER TABLE EMPLOYEE DROP ADDRESS;

Or we can change defaults for attributes

-- remove the default for MGRSSN
ALTER TABLE DEPARTMENT ALTER MGRSSN DROP DEFAULT;

-- set a new default for MGRSSN
ALTER TABLE DEPARTMENT ALTER MGRSSN SET DEFAULT '122444444';