SQL: Data Definition Language
CSC 343 Winter 2021
MICHAEL LIUT (MICHAEL.LIUT@UTORONTO.CA) ILIR DEMA (ILIR.DEMA@UTORONTO.CA)
DEPARTMENT OF MATHEMATICAL AND COMPUTATIONAL SCIENCES UNIVERSITY OF TORONTO MISSISSAUGA
Database Schemas in SQL
SQL is primarily a query language, for getting information from a database. ◦ DML: Data Manipulation Language
But SQL also includes a data-definition component for describing database schemas.
◦ DDL: Data Definition Language
2
Creating a schema
In Postgres every database may contain multiple schemas. The default schema is public.
To create a new schema issue the following statement:
CREATE SCHEMA myschema;
To switch to an existing schema, issue the following statement:
SET search_path TO myschema;
3
Creating (Declaring) a Relation
Simply:
◦ CREATE TABLE );
Deleting a relation:
◦ DROP TABLE
4
Elements of Table Declarations
Most basic element: an attribute and its type.
Common Types:
◦ INT or INTEGER (synonyms).
◦ Also: SMALLINT and BIGINT
◦ REAL and DOUBLE PRECISION (FLOAT is a synonym for both).
◦ NUMERIC allows for arbitrary precision numbers
◦ CHAR(n) = fixed-length string of n characters.
◦ VARCHAR(n) = variable-length string of up to n characters.
Additional data type found here:
https://www.postgresql.org/docs/current/datatype.html
5
Example: Create Table
CREATE TABLE Sells (
bar CHAR(20),
beer VARCHAR(20),
price REAL );
6
SQL Values
Integers and reals are represented as you would expect.
Strings are too, except they require single quotes. ◦ Two single quotes = real quote
e.g. ‘Joe’’s Bar’.
Any value can be NULL
◦ Unless attribute has NOT NULL constraint
e.g. price REAL not null,
7
Dates and Times
DATE and TIME are types in SQL ◦ Postgres even has TIMESTAMP
◦ Ranging from 4713 BC to 294276 AD with 1 microsecond resolution The form of a date value is:
DATE ‘yyyy-mm-dd’
e.g. DATE ‘2020-09-30’ is September 30th, 2020.
8
Times and Values
The form of a time value is:
◦ TIME ‘hh:mm:ss’ – with an optional decimal point for a fraction of a second.
e.g. TIME ‘15:30:02.5’ = two and a half seconds after 3:30pm.
9
Declaring Keys
An attribute or list of attributes may be declared PRIMARY KEY or UNIQUE.
Either says that no two tuples of the relation may agree in all the attribute(s) on the list.
Caution: UNIQUE allows null values whereas PRIMARY KEY does not.
10
Example
Beers (name, manf)
Bars (name, addr, license) Drinkers (name, addr, phone) Likes (drinker, beer)
Sells (bar, beer, price) Frequents (drinker, bar)
Underline = key (tuples cannot have the same value in all key attributes)
11
Declaring Multi-Attribute Keys
A key declaration can also be another element in the list of elements of a CREATE TABLE statement.
This form is essential if the key consists of more than one attribute. ◦ Could be used for one-attribute keys.
12
Example
The bar and beer together are the key for Sells:
CREATE TABLE Sells (
bar CHAR(20),
beer VARCHAR(20), price REAL,
PRIMARY KEY (bar, beer)
);
13
Declaring Single-Attribute Keys
Place PRIMARY KEY or UNIQUE NOT NULL after the type in the declaration of the attribute.
Example:
CREATE TABLE Beers (
name CHAR(20) PRIMARY KEY,
manf CHAR(20) );
14
Primary Key vs. Unique
1. There can be only one PRIMARY KEY for a relation, but several UNIQUE (or UNIQUE NOT NULL) attributes.
2. No attribute of a PRIMARY KEY can ever be NULL in any tuple. But attributes declared UNIQUE may have NULL’s, and there may be several tuples with NULL.
15
Types of Constraints
1. Keys
2. Foreign-key (referential-integrity) constraint
3. Domain constraint
◦ Constrain values of a particular attribute.
4. Tuple-based constraint
◦ Relationship among components.
5. Assertions: any SQL Boolean expressions
16
Foreign Keys
Values appearing in attributes of one relation must appear together in certain attributes of another relation.
Example: in Sells(bar, beer, price), we might expect that a beer value also appears in Beers.name
17
Expressing Foreign Keys
Use keyword REFERENCES, either:
1. After an attribute (for one-attribute keys).
2. As an element of the schema: FOREIGN KEY ()
REFERENCES
Referenced attributes must be declared PRIMARY KEY or UNIQUE.
18
Example: with Attribute
CREATE TABLE Beers ( name CHAR(20)
manf CHAR(20) );
CREATE TABLE Sells (
bar CHAR(20),
beer CHAR(20)
price REAL );
PRIMARY KEY,
REFERENCES
Beers(name),
19
Example: As Schema Element
CREATE TABLE Beers ( name CHAR(20)
manf CHAR(20) );
CREATE TABLE Sells (
bar CHAR(20),
beer CHAR(20), price REAL FOREIGN KEY (beer)
PRIMARY KEY,
);
REFERENCES
Beers(name)
20
Enforcing Foreign-Key Constraints
If there is a foreign-key constraint from relation R to relation S, two violations are possible:
1. An insert or update to R introduces values not found in S.
2. A deletion or update to S causes some tuples of R to “dangle.”
21
Actions Taken
Example: suppose R = Sells, S = Beers.
An insert or update to Sells that introduces a nonexistent beer must be rejected.
A deletion or update to Beers that removes a beer value found in some tuples of Sells can be handled in three ways.
22
Actions Taken
Default: Reject the modification.
Cascade: Make the same changes in Sells. ◦ Deleted beer: delete Sells tuple.
◦ Updated beer: change value in Sells.
Set Null: Change the beer to NULL.
23
Reference Options
RESTRICT: rejects the delete or update operation for the parent table. CASCADE: ON DELETE or ON UPDATE the row from the parent table, and
automatically delete or update the matching rows in the child table. SET NULL: delete or update the row from the parent table, and set the
Foreign Key column(s) in the child table to NULL. NO ACTION: equivalent to RESTRICT.
More details found in the Postgres documentation.
24
Example: Cascade
Delete the Bud tuple from Beers:
◦ Then delete all tuples from Sells that have beer = ‘Bud’.
Update the Bud tuple by changing ‘Bud’ to ‘Budweiser’
◦ Then change all Sells tuples with beer = ‘Bud’ to beer = ‘Budweiser’
25
Example: Set NULL
Delete the Bud tuple from Beers:
◦ Change all tuples of Sells that have beer = ‘Bud’ to have beer = NULL.
Update the Bud tuple by changing ‘Bud’ to ‘Budweiser’: ◦ Same change as for deletion.
26
Example: Setting Policy
CREATE TABLE Sells ( bar CHAR(20),
beer CHAR(20), price REAL, FOREIGN KEY(beer)
);
REFERENCES Beers(name) ON DELETE SET NULL
ON UPDATE CASCADE
27
Choosing a Policy
When we declare a foreign key, we may choose policies SET NULL or CASCADE independently for deletions and updates.
Follow the foreign-key declaration by: ON [UPDATE, DELETE][SET NULL CASCADE]
Two such clauses may be used. Otherwise, the default (reject) is used.
28
Attribute-Based Checks
Constraints on the value of a particular attribute.
Add CHECK(
Keep in mind, Postgres does not allow subqueries in CHECK constraints.
29
Example: Attribute-Based Check
CREATE TABLE Sells ( bar CHAR(20),
beer CHAR(20),
price REAL CHECK (price <= 5.00) );
30
Timing of Checks
Attribute-based checks are performed only when a value for that attribute is inserted or updated.
◦ Example: CHECK (price <= 5.00) checks every new price and rejects the modification (for that tuple) if the price is more than $5.
31
Tuple-Based Checks
CHECK (
◦ But other attributes or relations require a sub-query. Checked on insert or update only.
32
Example: Tuple-Based Check
• Only Joe’s Bar can sell beer for more than $5. CREATE TABLE Sells (
bar
beer
price
CHECK (bar = ‘Joe’’s Bar’ OR price <= 5.00)
);
CHAR(20), CHAR(20),
REAL,
33
Questions?
Q
THANKS FOR LISTENING
I’LL BE ANSWERING QUESTIONS NOW
&
A
34
Citations, Images and Resources
Database Management Systems (3rd Ed.), Ramakrishnan & Gehrke
Some content is based off the slides of Dr. Fei Chiang - http://www.cas.mcmaster.ca/~fchiang/ http://csharpcorner.mindcrackerinc.netdna-cdn.com/UploadFile/BlogImages/06112016031910AM/sql.png
35