The Relational Model

1
The Relational Model
2
Why Relational Model?

• Currently the most widely used
• Vendors Oracle, Microsoft, IBM
• Older models still used
• IBMs IMS (hierarchical model)
• Recent competitions
• Object Oriented Model ObjectStore
• Implementation standard for relational Model
• SQL (Structured Query Language)
• SQL 3 includes object-relational extensions

3
Relational Model

• Structures
• Relations (also called Tables)
• Attributes (also called Columns or Fields)
• Note Every attribute is simple (not composite or
  multi-valued)
• Constraints
• Key and Foreign Key constraints (More constraints
  later)
• Eg Student Relation (The following 2 relations
  are equivalent)

Student
Student
sNumber sName
1 Dave
2 Greg
sNumber sName
2 Greg
1 Dave
Cardinality 2 Arity/Degree 2
4
Relational Model

• Schema for a relation
• Eg Student (sNumber, sName)
• PRIMARY KEY (Student) ltsNumbergt
• Schema for a database
• Schemas for all relations in the database
• Tuples (Rows)
• The set of rows in a relation are the tuples of
  that relation
• Note Attribute values may be null

5
Primary Key Constraints

• A set of attributes is a key for a relation if
• 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 A proper subset of a superkey may be a
  superkey
• If multiple keys, one of them is chosen to be the
  primary key.
• Eg PRIMARY KEY (Student) ltsNumbergt
• Primary key attributes cannot take null values

6
Candidate Keys (SQL Unique)

• Keys that are not primary keys are candidate
  keys.
• Specified in SQL using UNIQUE
• Attribute of unique key may have null values !
• Eg Student (sNumber, sName)
• PRIMARY KEY (Student) ltsNumbergt
• CANDIDATE KEY (Student) ltsNamegt

7
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
• Consider R (a, b) where a is unique. R violates
  the unique constraint if all of the following are
  true
• 2 rows in R have the same non-null values for a

8
Keys Example
Student
sNumber sName address
1 Dave 144FL
2 Greg 320FL
Primary Key ltsNumbergt Candidate key
ltsNamegt Some superkeys ltsNumber, addressgt,
ltsNamegt, ltsNumbergt, ltsNumber, sNamegt
ltsNumber, sName, addressgt
9
Foreign Key Constraints

• To specify an attribute (or multiple attributes)
  S1 of a relation R1 refers to the attribute (or
  attributes) S2 of another relation R2
• Eg Professor (pName, pOffice)
• Student (sNumber, sName, advisor)
• PRIMARY KEY (Professor) ltpNamegt
• FOREIGN KEY Student (advisor)
  REFERENCES Professor (pName)

10
Foreign Key Constraints

• FOREIGN KEY R1 (S1) REFERENCES R2 (S2)
• Like a logical pointer
• The values of S1 for any row of R1 must be values
  of S2 for some row in R2 (null values are
  allowed)
• S2 must be a key for R2
• R2 can be the same as R1 (i.e., a relation can
  have a foreign key referring to itself).

11
Foreign Keys Examples

• Dept (dNumber, dName)
• Person (pNumber, pName, dept)
• PRIMARY KEY (Dept) ltdNumbergt
• PRIMARY KEY (Person) ltpNumbergt
• FOREIGN KEY Person (dept)
• REFERENCES Dept (dNumber)

Persons working for Depts
Person (pNumber, pName, father) PRIMARY KEY
(Person) ltpNumbergt FOREIGN KEY Person (father)
REFERENCES Person (pNumber)
Person and his/her father
12
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.

13
Relational Model Summary

• Structures
• Relations (Tables)
• Attributes (Columns, Fields)
• Constraints
• Key
• Primary key, candidate key (unique)
• Super Key
• Foreign Key

14
ER schema ? Relational schema

• Simple Algorithm
• Entity type E ? Relation E
• Attribute of E ? Attribute as E
• Key for E ? Primary Key for E
• For relationship type R between E1, E2, , En
• Create separate relation R
• Attributes of R are primary keys of E1, E2, ,
  En and attributes of R
• Primary Key for R is defined as
• If the maximum cardinality of any Ei is 1,
  primary key for R primary key for Ei
• Else, primary key for R primary keys for E1,
  E2, , En
• Define appropriate foreign keys from R to E1,
  E2, , En

15
Simple algorithm Example 1
Person (pNumber, pName) Dept (dNumber,
dName) WorksFor (pNumber, dNumber, years)
PRIMARY KEY (Person) ltpNumbergt PRIMARY KEY
(Dept) ltdNumbergt PRIMARY KEY (WorksFor)
ltpNumber, dNumbergt FOREIGN KEY WorksFor (pNumber)
REFERENCES Person (pNumber) FOREIGN KEY WorksFor
(dNumber) REFERENCES Dept (dNumber)
16
Simple Algorithm Example 2
Supplier (sName, sLoc) Consumer (cName,
cLoc) Product (pName, pNumber) Supply (supplier,
consumer, product, price, qty)
PRIMARY Key (Supplier) ltsNamegt PRIMARY Key
(Consumer) ltcNamegt PRIMARY Key (Product)
ltpNamegt PRIMARY Key (Supply) ltsupplier,
consumer, productgt FOREIGN KEY Supply (supplier)
REFERENCES Supplier (sName) FOREIGN KEY Supply
(consumer) REFERENCES Consumer (cName) FOREIGN
KEY Supply (product) REFERENCES Product (pName)
17
Simple Algorithm Example 3
Part (pName, pNumber) Contains (superPart,
subPart, quantity)
PRIMARY KEY (Part) ltpNumbergt PRIMARY KEY
(Contains) ltsubPartgt FOREIGN KEY Contains
(superPart) REFERENCES Part (pNumber) FOREIGN KEY
Contains (subPart) REFERENCES Part (pNumber)
18
Decreasing the number of Relations

• Technique 1
• If the relationship type R contains an entity
  type, say E, whose maximum cardinality is 1, then
  R may be represented as attributes of E.
• If the cardinality of E is (1, 1), then no new
  nulls are introduced
• If the cardinality of E is (0, 1) then new
  nulls may be introduced.

19
Example 1
Student (sNumber, sName, advisor,
years) Professor (pNumber, pName) PRIMARY KEY
(Student) ltsNumbergt PRIMARY KEY (Professor)
ltpNumbergt FOREIGN KEY Student (advisor)
REFERENCES Professor (pNumber) Note advisor
will never be null for a student
20
Example 2
Person (pNumber, pName, dept, years) Dept
(dNumber, dName) PRIMARY KEY (Person)
ltpNumbergt PRIMARY KEY (Dept) ltdNumbergt FOREIGN
KEY Person (dept) REFERENCES Dept (dNumber) Dept
and years may be null for a person
21
Example 3
Part (pNumber, pname, superPart,
quantity) PRIMARY KEY (Part) ltpNumbergt FOREIGN
KEY Part (superPart) REFERENCES Part
(pNumber) Note superPart gives the superpart of
a part, and it may be null
22
Decreasing the number of Relations

• Technique 2 (not recommended)
• If the relationship type R between E1 and E2 is
  11, and the cardinality of E1 or E2 is (1, 1),
  then we can combine everything into 1 relation.
• Let us assume the cardinality of E1 is (1, 1). We
  have one relation for E2, and move all attributes
  of E1 and for R to be attributes of E2.
• If the cardinality of E2 is (1, 1), no new
  nulls are introduced
• If the cardinality of E2 is (0, 1) then new
  nulls may be introduced.

23
Example 1
Student (sNumber, sName, pNumber, pName,
years) PRIMARY KEY (Student) ltsNumbergt CANDIDATE
KEY (Student) ltpNumbergt Note pNumber, pName,
and years can be null for students with no advisor
24
Example 2
Student (sNumber, sName, pNumber, pName,
years) PRIMARY KEY (Student) ltsNumbergt CANDIDATE
KEY (Student) ltpNumbergt Note pNumber cannot
be null for any student.
25
Other details

• Composite attribute in ER
• Include an attribute for every component of the
  composite attribute.
• Multi-valued attribute in ER
• We need a separate relation for any multi-valued
  attribute.
• Identify appropriate attributes, keys and foreign
  key constraints.

26
Composite and Multi-valued attributes in ER
Student (sNumber, sName, sAge, street, city,
state) StudentMajor (sNumber, major) PRIMARY KEY
(Student) ltsNumbergt PRIMARY KEY (StudentMajor)
ltsNumber, majorgt FOREIGN KEY StudentMajor
(sNumber) REFERENCES Student (sNumber)
27
Weak entity types

• Consider weak entity type E
• A relation for E, say E
• Attributes of E attributes of E in ER keys
  for all indentifying entity types.
• Key for E the key for E in ER keys for all
  the identifying entity types.
• Identify appropriate FKs from E to the
  identifying entity types.

28
Weak entity types Example
Dept (dNumber, dName) Course (cNumber, dNumber,
cName) PRIMARY KEY (Dept) ltdNumbergt PRIMARY
KEY (Course) ltcNumber, dNumbergt FOREIGN KEY
Course (dNumber) REFERENCES Dept (dNumber)
29
ISA Relationship types Method 1
Student (sNumber, sName) UGStudent (sNumber,
year) GradStudent (sNumber, program) PRIMARY KEY
(Student) ltsNumbergt PRIMARY KEY (UGStudent)
ltsNumbergt PRIMARY KEY (GradStudent)
ltsNumbergt FOREIGN KEY UGStudent (sNumber)
REFERENCES Student (sNumber) FOREIGN KEY
UGStudent (sNumber) REFERENCES Student
(sNumber)
An UGStudent will be represented in both Student
relation as well as UGStudent relation (similarly
GradStudent)
30
ISA Relationship types Method 2
Student (sNumber, sName, year, program) PRIMARY
KEY (Student) ltsNumbergt Note There will be
null values in the relation.
31
ISA Relationship types Method 3
Student (sNumber, sName) UGStudent (sNumber,
sName, year) GradStudent (sNumber, sName,
program) UGGradStudent (sNumber, sName,
year, program) PRIMARY KEY
(Student) ltsNumbergt PRIMARY KEY (UGStudent)
ltsNumbergt PRIMARY KEY (GradStudent)
ltsNumbergt PRIMARY KEY (UGGradStudent) ltsNumbergt
Any student will be represented in only one of
the relations as appropriate.