Transcript ppt

The Relational Model
CS3431
1
Why Relational Model?

Currently the most widely used


Older models still used


Vendors: Oracle, Microsoft, IBM
IBM’s IMS (hierarchical model)
Recent competitions



Object Oriented Model: ObjectStore, Oracle
XML Databases : native and extensions
Stream Databases : startups
cs3431
2
Relational Query Languages
(SQL Standard)




Developed by IBM (system R) in 1970s
Standard as used by many vendors (portable)
SQL (Structured Query Language)
Standards:




SQL-86
SQL-89 (minor revision)
SQL-92 (major revision)
SQL-99 (major extensions in OO, current standard)
cs3431
Relational Model

Structures:




Relations (also called Tables)
Attributes (also called Columns or Fields)
Note: Every attribute is simple (not composite or
multi-valued)
Instance :

a table with rows (tuples) and columns (attributes)
cs3431
4
Relational Model

#Rows = cardinality,
#attributes = degree / arity.
Eg: Student Relation
Student
Student
sNumber
sName
sNumber
sName
1
Dave
2
Greg
2
Greg
1
Dave

Are 2 relations equivalent?

Think of a relation as a set of tuples
Cardinality = 2
Arity/Degree = 2
i.e., all rows are distinct (not required by commercial database)
cs3431
5
SQL DDL

DDL = Data Definition Language

Create tables, columns of tables, types of
columns, primary key constraints, unique
constraints, foreign key constraints

Drop tables, add/drop columns, add/drop
constraints – primary key, unique, foreign key
cs3431
9
Creating Tables
CREATE TABLE <tableName> (
<col> <type>,
<col> <type>,
…
<col> <type>,
[CONSTRAINT <cName>] PRIMARY KEY (…),
[CONSTRAINT <cName>] UNIQUE (…),
[CONSTRAINT <cName>] FOREIGN KEY (…)
REFERENCES <tableName> (…)
);
cs3431
10
DDL ---- Creating Relations


Creates Students relation.
Observe that type (domain)
of each field is specified,
and enforced by DBMS
whenever tuples are added
or modified.
As another example,
Enrolled table holds
information about courses
that students take.
cs3431
CREATE TABLE Students
(sid: CHAR(20),
name: CHAR(20),
login: CHAR(10),
age: INTEGER,
gpa: REAL)
CREATE TABLE Enrolled
(sid: CHAR(20),
cid: CHAR(20),
grade: CHAR(2))
Dropping tables
DROP TABLE <tableName>
cs3431
12
Adding/Dropping Columns
ALTER TABLE <tableName> ADD <col>
<type>;
ALTER TABLE <tableName> DROP COLUMN
<col>;
cs3431
13
Adding/Dropping Constraints
ALTER TABLE <tableName> ADD
[CONSTRAINT <cName>] …
ALTER TABLE <tableName> DROP
CONSTRAINT <cName>
cs3431
14
SQL DML: Basic

DML = Data Manipulation Language for
querying and modification

Modification: Insert/Delete values from table

Insert a value into a table
INSERT INTO <tableName> VALUES (…)
eg: INSERT INTO Student VALUES (1, ‘Dave’);

Delete all values from a table
DELETE FROM <tableName>
cs3431
15
Integrity Constraints (ICs)

IC: condition that must be true for any
instance of the database



ICs are specified when schema is defined.
ICs are checked when relations are modified.
A legal instance of a relation is one that
satisfies all specified ICs.

DBMS should not allow illegal instances.
cs3431
Constraints in Relational Model



Key constraints
Foreign Key constraints
(More constraints later)
cs3431
17
Key Constraints

A set of attributes is a key for a relation if:


Unique: No two distinct tuples can have the same
values in all key fields
Minimal: A proper subset of the key attributes is
not a key.

INTUITION : Minimal subset of columns of the
relation that uniquely identify the tuple.
cs3431
18
Keys: Example
Student
sNumber
sName
address
1
Dave
144FL
2
Greg
320FL
Is <sNumber> a key ?
cs3431
19
Primary Key Constraints

If multiple keys, one of them is chosen to be
the Primary Key.

E.g.: PRIMARY KEY (Student) = <sNumber>

Restriction:

Primary key attributes cannot take null values
cs3431
20
Candidate Keys (SQL: Unique)


Keys that are not primary keys are Candidate Keys.
Specified in SQL using UNIQUE

E.g.: Student (sNumber, sName)
PRIMARY KEY (Student) = <sNumber>
CANDIDATE KEY (Student) = <sName>

Note: Attribute of unique key may have null values !
cs3431
21
More on Key Constraints

Superkey K:


REMINDER: A set of attributes is a key if:



if values for K are sufficient to identify a unique tuple of
relation r(R)
No two distinct tuples can have the same values in all key fields
A proper subset of the key attributes is not a key.
Superkey:


Contains some key in its attribute set
A proper subset of a superkey may be a superkey again ?
cs3431
22
Violation of key constraints

A relation violates a primary key constraint if:



There is a row with null values for any attribute of
primary key, or,
There are 2 rows with same values for all
attributes of primary key
A relation violates the unique constraint if:

2 rows in R have the same non-null values for any
unique attribute R.a
cs3431
23
Keys: Example
Student
sNumber
sName
address
1
Dave
144FL
2
Greg
320FL
Primary Key: <sNumber>
Candidate key: <sName>
Some superkeys:
{<sNumber, address>,
<sName>,
<sNumber>,
<sNumber, sName>
<sNumber, sName, address>}
cs3431
24
Primary & Candidate Keys in SQL

Possibly many candidate keys (specified using
UNIQUE), one chosen as primary key.
CREATE TABLE Student
(sid CHAR(20),
ssn INTEGER,
cid CHAR(20),
PRIMARY KEY (sid)
UNIQUE (cid)
cs3431
Primary and Candidate Keys in
SQL
“For a given student and course, there
is a single grade.”
“Students can take only one course,
and receive a single grade for that
course; further, no two students in
a course receive the same grade.”
CREATE TABLE Enrolled
(sid CHAR(20)
cid CHAR(20),
grade CHAR(2),
PRIMARY KEY (sid,cid) )
CREATE TABLE Enrolled
(sid CHAR(20)
cid CHAR(20),
grade CHAR(2),
PRIMARY KEY (sid),
UNIQUE (cid, grade) )
Used carelessly, an IC can prevent the storage
of database instances that arise in practice!
cs3431
Foreign Keys, Referential
Integrity

Foreign key : Set of fields in one relation that is used to
"refer" to a tuple in another relation.
-

Like a `logical pointer’.
Foreign key :


FK in referencing relation must match PK of referenced relation.
Match = same number of columns, compatible data types
(column names can be different).
Enrolled (referencing relation)
sid
53666
53666
53650
53666
cid
grade
Carnatic101
C
Reggae203
B
Topology112
A
History105
B
Foreign Key
Students (referenced relation)
sid
53666
53688
53650
cs3431
Primary Key
name
login
Jones jones@cs
Smith smith@eecs
Smith smith@math
age
18
18
19
gpa
3.4
3.2
3.8
Foreign Keys in SQL

Only students listed in Students relation
should be allowed to enroll for courses.
CREATE TABLE Enrolled
(sid CHAR(20), cid CHAR(20), grade CHAR(2),
PRIMARY KEY (sid,cid),
FOREIGN KEY (sid) REFERENCES Students (sid))
If all foreign key constraints are enforced, referential integrity is
achieved, i.e., no dangling references.

cs3431
Foreign Keys: More Examples
Dept (dNumber, dName)
Person (pNumber, pName, dept)
Persons working for Depts
PRIMARY KEY (Dept) = <dNumber>
PRIMARY KEY (Person) =
<pNumber>
FOREIGN KEY Person (dept)
REFERENCES Dept (dNumber)
Person (pNumber, pName, father)
PRIMARY KEY (Person) = <pNumber>
FOREIGN KEY Person (father)
REFERENCES Person (pNumber)
cs3431
Person and his/her father
29
Violation of Foreign Key
Constraints

Suppose we have:


FOREIGN KEY R1 (S1) REFERENCES R2 (S2)
This constraint is violated if


Consider a row in R1 with non-null values for all
attributes of S1
If there is no row in R2 which have these values
for S2, then the FK constraint is violated.
cs3431
30
Enforcing Referential Integrity

Consider Students and Enrolled; sid in Enrolled is a
foreign key that references Students.
Enrolled (referencing relation)
sid
53666
53666
53650
53666
cid
grade
Carnatic101
C
Reggae203
B
Topology112
A
History105
B
Foreign Key


Students (referenced relation)
sid
53666
53688
53650
name
login
Jones jones@cs
Smith smith@eecs
Smith smith@math
age
18
18
19
gpa
3.4
3.2
3.8
Primary Key
Insertion: What if a new Student tuple is inserted?
Insertion: What should be done if an Enrolled tuple with
a non-existent student id is inserted?

Reject it
cs3431
Enforcing Referential Integrity
Enrolled (referencing relation)
sid
53666
53666
53650
53666
cid
grade
Carnatic101
C
Reggae203
B
Topology112
A
History105
B
Foreign Key
Students (referenced relation)
sid
53666
53688
53650
name
login
Jones jones@cs
Smith smith@eecs
Smith smith@math
age
18
18
19
Primary Key
Deletion: What if an Enrolled tuple is deleted?
cs3431
gpa
3.4
3.2
3.8
Enforcing Referential Integrity
Enrolled (referencing relation)
sid
53666
53666
53650
53666
cid
grade
Carnatic101
C
Reggae203
B
Topology112
A
History105
B
Foreign Key
Students (referenced relation)
sid
53666
53688
53650
login
name
Jones jones@cs
Smith smith@eecs
Smith smith@math
age
18
18
19
gpa
3.4
3.2
3.8
Primary Key
Deletion: What if a Students tuple is deleted?




Cascading -- Also delete all Enrolled tuples that refer to it.
No Action -- Disallow deletion of a Students tuple that is
referred to.
Set Default -- Set sid in Enrolled tuples that refer to it to a
default sid.
Set Null -- Set sid in Enrolled tuples that refer to it to a special
value null, denoting `unknown’ (Not always applicable)
cs3431
Similar if primary key of Students tuple is updated.
Referential Integrity in SQL

SQL/99 supports all
4 options on
deletes & updates:



Default is NO ACTION
(delete/update is
rejected)
CASCADE (also delete
all tuples that refer to
deleted tuple)
SET NULL / SET
DEFAULT (sets foreign
key value of referencing
tuple)
CREATE TABLE Enrolled
(sid CHAR(20),
cid CHAR(20),
grade CHAR(2),
PRIMARY KEY (sid,cid),
FOREIGN KEY (sid)
REFERENCES Students
ON DELETE CASCADE
ON UPDATE SET DEFAULT )
cs3431
Constraint Violation

What if cyclic dependencies between two tables?

Solution:



1. Group both insertions into one transaction
2. Tell DBMS not to check the constraints until after the
whole transaction has finished and is committed
How:



Follow declaration of constraint by “DEFERRABLE”
Plus “INITIALLY DEFERRED”
 defer just before transaction commits
Or plus “INITIALLY IMMEDIATE”
 check made immediately after each statement
cs3431
Where do ICs Come From?

ICs are based upon semantics of real-world
enterprise being described in database relations.

We can check a database instance to see if an
IC is violated ?

We can infer that an IC is true by looking at an
instance ?



No, NEVER !
An IC is a statement about all possible instances!
From example, we know name is not a key, but the
assertion that sid is a key is given to us.
cs3431
Relational Model: Summary

Structures



Relations (Tables)
Attributes (Columns, Fields)
Constraints + Constraint Enforcement


Domain Constraint
Key



Primary key, candidate key (unique)
Super Key
Foreign Key
cs3431
38