The Relational Model

honorableclunkSoftware and s/w Development

Oct 30, 2013 (3 years and 10 months ago)

85 views

Murali Mani

The Relational Model

Murali Mani

Why Relational Model?


Currently the most widely used


Vendors: Oracle, Microsoft, IBM


Older models still used


IBM’s IMS (hierarchical model)


Recent competitions


Object Oriented Model: ObjectStore


Implementation standard for relational Model


SQL (Structured Query Language)


SQL 3: includes object
-
relational extensions

Murali Mani

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)

sNumber

sName

1

Dave

2

Greg

Student

sNumber

sName

2

Greg

1

Dave

Student

Cardinality = 2

Arity/Degree = 2

Murali Mani

Relational Model


Schema for a relation


Eg: Student (sNumber, sName)


PRIMARY KEY (Student) = <sNumber>


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

Murali Mani

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) = <sNumber>


Primary key attributes cannot take null values

Murali Mani

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) = <sNumber>



CANDIDATE KEY (Student) = <sName>

Murali Mani

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

Murali Mani

Keys: Example

sNumber

sName

address

1

Dave

144FL

2

Greg

320FL

Student

Primary Key: <sNumber>

Candidate key: <sName>

Some superkeys: {<sNumber, address>,




<sName>,




<sNumber>,




<sNumber, sName>




<sNumber, sName, address>}

Murali Mani

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) = <pName>




FOREIGN KEY Student (advisor)





REFERENCES Professor
(pName)

Murali Mani

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).

Murali Mani

Foreign Keys: Examples

Dept (dNumber, dName)

Person (pNumber, pName, dept)


PRIMARY KEY (Dept) = <dNumber>

PRIMARY KEY (Person) =
<pNumber>

FOREIGN KEY Person (dept)


REFERENCES Dept (dNumber)

Persons working for Depts

Person and his/her father

Person (pNumber, pName, father)



PRIMARY KEY (Person) = <pNumber>

FOREIGN KEY Person (father)


REFERENCES Person (pNumber)

Murali Mani

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.

Murali Mani

Relational Model: Summary


Structures


Relations (Tables)


Attributes (Columns, Fields)


Constraints


Key


Primary key, candidate key (unique)


Super Key


Foreign Key

Murali Mani

ER schema


R敬慴in慬 獣h敭a





Simple Algorithm


Entity type E


Relation E’


Attribute of E


A
ttribute as E’


Key for E


Primary Key for E’


For relationship type R between E
1
, E
2
, …, E
n


Create separate relation R’


Attributes of R’ are primary keys of E
1
, E
2
, …, E
n

and
attributes of R


Primary Key for R’ is defined as:


If the maximum cardinality of any E
i

is 1, primary key for R’ =
primary key for E
i


Else, primary key for R’ = primary keys for E
1
, E
2
, …, E
n


Define “appropriate” foreign keys from R’ to E
1
, E
2
, …, E
n

Murali Mani

Simple algorithm: Example 1

Person (pNumber, pName)

Dept (dNumber, dName)

WorksFor (pNumber, dNumber, years)

PRIMARY KEY (Person) = <pNumber>

PRIMARY KEY (Dept) = <dNumber>

PRIMARY KEY (WorksFor) = <pNumber, dNumber>

FOREIGN KEY WorksFor (pNumber) REFERENCES Person (pNumber)

FOREIGN KEY WorksFor (dNumber) REFERENCES Dept (dNumber)

Murali Mani

Simple Algorithm: Example 2

PRIMARY Key (Supplier) = <sName> PRIMARY Key (Consumer) = <cName>

PRIMARY Key (Product) = <pName>

PRIMARY Key (Supply) = <supplier, consumer, product>

FOREIGN KEY Supply (supplier) REFERENCES Supplier (sName)

FOREIGN KEY Supply (consumer) REFERENCES Consumer (cName)

FOREIGN KEY Supply (product) REFERENCES Product (pName)

Supplier (sName, sLoc)

Consumer (cName, cLoc)

Product (pName, pNumber)

Supply (supplier, consumer,


product, price, qty)

Murali Mani

Simple Algorithm: Example 3

PRIMARY KEY (Part) = <pNumber>

PRIMARY KEY (Contains) = <subPart>


FOREIGN KEY Contains (superPart) REFERENCES Part (pNumber)

FOREIGN KEY Contains (subPart) REFERENCES Part (pNumber)

Part (pName, pNumber)

Contains (superPart, subPart, quantity)

Murali Mani

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.

Murali Mani

Example 1

Student (sNumber, sName, advisor, years)

Professor (pNumber, pName)


PRIMARY KEY (Student) = <sNumber>

PRIMARY KEY (Professor) = <pNumber>

FOREIGN KEY Student (advisor) REFERENCES Professor (pNumber)


Note: advisor will never be null for a student

Murali Mani

Example 2

Person (pNumber, pName, dept, years)

Dept (dNumber, dName)


PRIMARY KEY (Person) = <pNumber>

PRIMARY KEY (Dept) = <dNumber>

FOREIGN KEY Person (dept) REFERENCES Dept (dNumber)


Dept and years may be null for a person

Murali Mani

Example 3

Part (pNumber, pname, superPart, quantity)


PRIMARY KEY (Part) = <pNumber>

FOREIGN KEY Part (superPart) REFERENCES Part (pNumber)


Note: superPart gives the superpart of a part, and it may be null

Murali Mani

Decreasing the number of
Relations



Technique 2
(not recommended)


If the relationship type R between E1 and E2 is
1:1, 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.

Murali Mani

Example 1

Student (sNumber, sName, pNumber, pName, years)

PRIMARY KEY (Student) = <sNumber>

CANDIDATE KEY (Student) = <pNumber>


Note: pNumber, pName, and years can be null for students with
no advisor

Murali Mani

Example 2

Student (sNumber, sName, pNumber, pName, years)

PRIMARY KEY (Student) = <sNumber>

CANDIDATE KEY (Student) = <pNumber>


Note: pNumber cannot be null for any student.

Murali Mani

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.

Murali Mani

Composite and Multi
-
valued
attributes in ER

Student (sNumber, sName, sAge, street, city, state)

StudentMajor (sNumber, major)


PRIMARY KEY (Student) = <sNumber>

PRIMARY KEY (StudentMajor) = <sNumber, major>

FOREIGN KEY StudentMajor (sNumber) REFERENCES Student (sNumber)

Murali Mani

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.

Murali Mani

Weak entity types: Example

Dept (dNumber, dName)

Course (cNumber, dNumber, cName)


PRIMARY KEY (Dept) = <dNumber>

PRIMARY KEY (Course) = <cNumber, dNumber>

FOREIGN KEY Course (dNumber) REFERENCES Dept (dNumber)

Murali Mani

ISA Relationship types:
Method 1

Student (sNumber, sName)

UGStudent (sNumber, year)

GradStudent (sNumber, program)


PRIMARY KEY (Student) = <sNumber>

PRIMARY KEY (UGStudent) = <sNumber>

PRIMARY KEY (GradStudent) = <sNumber>


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)

Murali Mani

ISA Relationship types:
Method 2

Student (sNumber, sName, year, program)

PRIMARY KEY (Student) = <sNumber>


Note: There will be null values in the relation.

Murali Mani

ISA Relationship types:
Method 3

Student (sNumber, sName)

UGStudent (sNumber, sName, year)

GradStudent (sNumber, sName, program)

UGGradStudent (sNumber, sName,


year, program)


PRIMARY KEY (Student) = <sNumber>

PRIMARY KEY (UGStudent) = <sNumber>

PRIMARY KEY (GradStudent) = <sNumber>

PRIMARY KEY (UGGradStudent) = <sNumber>

Any student will be represented in
only one of the relations as
appropriate.