The Relational Model Murali Mani Why Relational Model? Currently - - PDF document

1

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

2

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)

Greg 2 Dave 1 sName sNumber Student Dave 1 Greg 2 sName sNumber 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

3

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>

4

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

320FL Greg 2 144FL Dave 1 address sName sNumber Student Primary Key: <sNumber> Candidate key: <sName> Some superkeys: {<sNumber, address>, <sName>, <sNumber>, <sNumber, sName> <sNumber, sName, address>}

5

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

6

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.

7

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 → 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

8

Murali Mani

Simple algorithm: Example 1

Person (pNumber, pName) Dept (dNumber, dName) WorksFor (pNumber, dNumber, years)

Person pNumber pName Dept dNumber dName Works For (1, *) (0, *) 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

Supplier sName sLoc Consumer cName cLoc Supply price Product pName pNumber qty (1, *) (0, *) (0, *)

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)

9

Murali Mani

Simple Algorithm: Example 3

Contains Part pName pNumber subPart superPart quantity (0, 1) (0, *)

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.

10

Murali Mani

Example 1

Student sNumber sName Professor pNumber pName Has Advisor (1,1) (0, *) years

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 dNumber dName Works For (0,1) (0, *) years

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

11

Murali Mani

Example 3

Contains Part pName pNumber subPart superPart quantity (0, 1) (0, *)

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

• f 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.

12

Murali Mani

Example 1

Student sNumber sName Professor pNumber pName Has Advisor (0,1) (1,1) years

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 Professor pNumber pName Has Advisor (1,1) (1,1) years

Student (sNumber, sName, pNumber, pName, years) PRIMARY KEY (Student) = <sNumber> CANDIDATE KEY (Student) = <pNumber> Note: pNumber cannot be null for any student.

13

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 sName sNumber sAge major state street address city

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)

14

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)

15

Murali Mani

ISA Relationship types: Method 1

Student GradStudent sName sNumber ISA ISA UGStudent program year

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 GradStudent sName sNumber ISA ISA UGStudent program year

Student (sNumber, sName, year, program) PRIMARY KEY (Student) = <sNumber> Note: There will be null values in the relation.

16

Murali Mani

ISA Relationship types: Method 3

Student GradStudent sName sNumber ISA ISA UGStudent program year

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

• nly one of the relations as

appropriate.