COMP231 1

COMP231

Relational Model

COMP231 2

Outline

Introduction Terminology ER-to-Relational Mapping

COMP231 3

Introduction The relational model was first

introduced by Ted Codd of IBM Research in 1970

and attracted due to its simplicity, elegance and mathematical foundations

The model uses the concept of a mathematical relation – which looks like a table of values

COMP231 4

Outline

Introduction Terminology ER-to-Relational Mapping

COMP231 5

Terminology Relation table; denoted by R(A1, A2, ..., An) where R is

a relation name and (A1, A2, ..., An) is the relation schema of R

Attribute (column) denoted by Ai

Tuple (Record) row Attribute value value stored in a table cell Domain legal type and range of values of an attribute

denoted by dom(Ai) Attribute: Age Domain: [0-100] Attribute: EmpName Domain: 50 alphabetic chars Attribute: Salary Domain: non-

negative integer

COMP231 6

Terminology

STUDENT Name Student-id Age CGA Chan Kin Ho 99223367 23 11.19 Lam Wai Kin 96882145 17 10.89 Man Ko Yee 96452165 22 8.75 Lee Chin Cheung 96154292 16 10.98 Alvin Lam 96520934 15 9.65

Attributes/Columns (collectively as a schema)

Tu

ples/Row

s

Relation Name/Table Name

COMP231 7

Schema The relation schema is

(Name, Student-id, Age, CGA)

OR

The primary key is underlined in the above

Name Student-id Age CGA

COMP231 8

Foreign Key

A foreign key is a set of attributes in one relation r that is used to refer to a tuple in another relation s. (it must correspond to the primary key of the second relation)

COMP231 9

Foreign Key Student

(Student-id, Student-Name) Take

(Student-id, Course-id, semesterNo)

Student-id in relation Student is a primary key

Student-id in relation Take is a foreign key

COMP231 10

Foreign Key Student

(Student-id, Student-Name) Take

(Student-id, Course-id, semesterNo) Course

(Course-id, Course-Name)

Course-id in relation Course is a primary key

Course-id in relation Take is a foreign key

COMP231 11

Outline

Introduction Terminology ER-to-Relational Mapping

COMP231 12

ER-to-Relational Mapping Typically, database designers begin with

the ER model, which is very expressive and user-friendly to human

Then, the ER model is mapped to the relational model for DBMS manipulations

Database queries and updates will be written according to the relational model

COMP231 13

ER-to-Relational Mapping

Translating traditional ER diagrams Translating Class Hierarchy

COMP231 14

Dependent

Dependent_of

Dependentname

sex

bdate

relationship

Controls

Projectpname

pnumber plocation

supervisor

supervision

supervisee

works_on

Dependent_of

hours

DepartmentEmployee

sexname

eid

bdate

addr

salary

works_for

Manages

start_date

numberdname

COMP231 15

Steps

Step 1 (Strong Entity Set) Step 2 (Weak Entity Set) Step 3 (1-to-1 Relationship) Step 4 (1-to-many Relationship) Step 5 (Many-to-many

Relationship) Step 6 (Non-binary Relationship)

COMP231 16

Step 1 (Strong Entity)

For each strong entity set E in the ER schema, create a relation schema R that

includes all the attributes of E. choose one set of key attributes of E

as a primary key for R.

COMP231 17

Dependent

Dependent_of

Dependentname

sex

bdate

relationship

Controls

Projectpname

pnumber plocation

supervisor

supervision

supervisee

works_on

Dependent_of

hours

DepartmentEmployee

sexname

eid

bdate

addr

salary

works_for

Manages

start_date

numberdname

COMP231 18

Projectpname

pnumber plocation

DepartmentEmployee

sexname

eid

bdate

addr

salary

numberdname

COMP231 19

Step 1 (Strong Entity) Example We create the relation schemas EMPLOYEE,

DEPARTMENT and PROJECT.

nameEMPLOYEE

DEPARTMENT

PROJECT

eid bdate addr sex salary

dname dnumber

pname pnumber plocation

COMP231 20

If there is a derived attribute, what should we do?

We have two choices.Choice 1: Include this derived attribute Adv: We can directly obtain the value of the derived attribute Disadv: We may encounter some data inconsistenciesChoice 2: NOT include this derived attribute Adv: We can avoid data inconsistency Disadv: We need to perform some operations to obtain the

value of the derived attribute

COMP231 21

If there is a composite attribute, what should we do?

We have two choices.Choice 1: Include the high-level attribute only (e.g., address)Choice 2: Include all low-level attributes (e.g., street, city, country)

EmployeeEmployee

addressaddress

streetstreet citycity

countrycountry

COMP231 22

If there is a multi-valued attribute, what should we do?

We have two choices.Choice 1: Include one attribute only (e.g., phone)Choice 2: Create another table containing the primary key of the entity set and the multi-valued attributee.g., create a schema PhoneTable (eid, phone)

EmployeeEmployee phonephone

eideid

COMP231 23

Step 2 (Weak Entity) For each weak entity set W in the

ER model, create a relation schema R, and include

all attributes. In addition, include the primary key(s) of

the owner(s). The primary key of R is the combination

of the primary key(s) of the owner(s) and the discriminator of the weak entity set W.

COMP231 24

Dependent

Dependent_of

Dependentname

sex

bdate

relationship

Controls

Projectpname

pnumber plocation

supervisor

supervision

supervisee

works_on

Dependent_of

hours

DepartmentEmployee

sexname

eid

bdate

addr

salary

works_for

Manages

start_date

numberdname

COMP231 25

Dependent

Dependent_of

Dependentname

sex

bdate

relationship

Dependent_of

Employee

sexname

eid

bdate

addr

salary

COMP231 26

Dependent

eid sexdependent-name bdate relationship

COMP231 27

Step 3 (1-to-1 Relationship) For each binary one-to-one (1:1)

relationship set R T ---- S

Choose one of the 2 relation schemas, say S,

get primary key of T, include it as foreign keys in S.

Better if S has total participation in R Include the attributes of the relationship set

R as attributes of S.

COMP231 28

Dependent

Dependent_of

Dependentname

sex

bdate

relationship

Controls

Projectpname

pnumber plocation

supervisor

supervision

supervisee

works_on

Dependent_of

hours

DepartmentEmployee

sexname

eid

bdate

addr

salary

works_for

Manages

start_date

numberdname

COMP231 29

DepartmentEmployee

sexname

eid

bdate

addr

salaryManages

start_date

numberdname

COMP231 30

We include the primary key of EMPLOYEE as foreign key in DEPARTMENT and rename it mgr_id. We include the attribute startdate of MANAGES and rename it mgr_start_date.DEPARTMENT dname dnumber mgr_id mgr_start_date

COMP231 31

Compare the following two choices

to include MANAGES:

Add information to EMPLOYEE

Add to DEPARTMENT

EMPLOYEEMANAGES

DEPARTMENT

COMP231 32

In the above, the NULL value is a special value meaning that the value is either unknown or not applicable.

Notice that an alternative mapping of a one-to-one relationship set is possible by merging the two entity sets and the relationship into a single relation.

This is appropriate when both participations are total.

Step 3:Advantage: The total number of relations remain unchangedDisadvantage: It may store NULL valuesif there is no total participation

COMP231 33

DepartmentEmployee

sexname

eid

bdate

addr

salaryManages

start_date

numberdname

Can we create a new relation

Manages (eid, number, start_date)

Or

Manages (eid, number, start_date)

for this relationship?Yes.

It can be used if there are onlya few relationship instancesAdvantage: It can avoid storing NULL valuesif there is no total participationDisadvantage: There is one additionalrelation

COMP231 34

Step 4 (1-to-many Relationship)

For each binary one-to-many relationship set T ---- S Include as foreign key in S the primary

key that represents the other entity set T participating in R.

Include any attributes of the one-to-many relationship set as attributes of S.

COMP231 35

Dependent

Dependent_of

Dependentname

sex

bdate

relationship

Controls

Projectpname

pnumber plocation

supervisor

supervision

supervisee

works_on

Dependent_of

hours

DepartmentEmployee

sexname

eid

bdate

addr

salary

works_for

Manages

start_date

numberdname

COMP231 36

Controls

Projectpname

pnumber plocation

supervisor

supervision

supervisee

DepartmentEmployee

sexname

eid

bdate

addr

salary

works_for numberdname

COMP231 37

The primary key dnumber of the DEPARTMENT relation schema is included as foreign key in the EMPLOYEE relation schema.

We rename it as dno. (The renaming is not necessary but makes the name more meaningful.)

nameEMPLOYEE eid bdate addr sex salary dno

EMPLOYEE WORKS_FOR DEPARTMENT

COMP231 38

Compare the following 2 choices:

EMPLOYEE

DEPARTMENT

Add employees to department

Add department to employee

COMP231 39

For SUPERVISON, include the primary key of the EMPLOYEE as

foreign key in the EMPLOYEE, and call it super_id.

nameEMPLOYEE eid bdate addr sex salary dno super_id

pnamePROJECT pnumber plocation dnum

For CONTROLS relationship,

include dnum as foreign key in PROJECT,

which references the primary key dnumber of DEPARTMENT.

COMP231 40

Controls

Projectpname

pnumber plocation

supervisor

supervision

supervisee

DepartmentEmployee

sexname

eid

bdate

addr

salary

works_for numberdname

Can we create a new relation

works_for (eid, number)

for this relationship? Yes.

COMP231 41

Step 5 (Many-to-many Relationship) For each binary many-to-many

relationship set R, create a new relation schema S to represent R. Include as foreign key attributes in S the

primary keys of the relation schemas for the participating entity sets in R

their combination will form the primary key of S.

Also include attributes of the many-to-many relationship set as attributes of S.

COMP231 42

Dependent

Dependent_of

Dependentname

sex

bdate

relationship

Controls

Projectpname

pnumber plocation

supervisor

supervision

supervisee

works_on

Dependent_of

hours

DepartmentEmployee

sexname

eid

bdate

addr

salary

works_for

Manages

start_date

numberdname

COMP231 43

Projectpname

pnumber plocation

works_on

hours

Employee

sexname

eid

bdate

addr

salary

COMP231 44

Map the many-to-many relationship setsWORKS_ON by creating the relation schema WORKS_ON.Include the primary keys of PROJECT and EMPLOYEE as foreign keys.

eidWORKS_ON pnumber hours

COMP231 45

Compare the following three choices to include works_on

pnumber hours

Null NullC77 89A01 10A01 101A01 22C77 57

name id bdate addr sex salary dno

Yeung 7 080370 … F 20K 3Chan 3 031060 … M 30K 4Chan 3 031060 … M 30K 4Wong 4 010280 … F 10K 7

Cheung 8 220985 … M 24K 1Cheung 8 220985 … M 24K 1

Add to EMPLOYEE

Add to PROJECT New relation WORKS_ON

EMPLOYEEWORKS_ON

PROJECT

COMP231 46

Step 6 (Non-binary Relationship) For each non-binary relationship

set, create a new relation schema S to

represent R. Include as foreign key attributes in S

the primary keys of the participating entity sets.

Also include any attributes of the non-binary relationship set as attributes of S.

COMP231 47

For non-binary relationships, The primary key of S is usually a

combination of all the foreign keys that reference the relations representing the participating entity sets.

However if any entity set has a key constraint on the relationship (e.g., 1-to-many relationship), the primary key of the entity set can be used as a key for the relationship.

COMP231 48

DEPARTMENT dname mgr_iddnumber mgr_start_date

nameEMPLOYEE eid bdate addr sex salary dno super_id

pnamePROJECT pnumber plocation dnum

DEPENDENT eid sexdependent-name bdate relationship

eidWORKS_ON pnumber hours

Resulting relation schemas:

COMP231 49

ER-to-Relational Mapping

Translating traditional ER diagrams Translating Class Hierarchy

COMP231 50

Translating Class Hierarchy Consider the class hierarchy example.

Hour_wagesHour_wagesContract_IDContract_ID

Contract_EmpsContract_Emps

ISAISA

EmployeesEmployees

namename

IDID addressaddress

Hourly_EmpsHourly_Emps

Hour_workedHour_worked

COMP231 51

Translating Class Hierarchy Two possible ways:

1. Map each of the entity sets Employees, Hourly-Emps, and Contract-Emps to a distinct relation.

nameEMPLOYEE id address

HOURLY-EMP

CONTRACT-EMP id contract-id

id hours_worked hours_wages

COMP231 52

2. Create only 2 relations:

This requires the covering constraint to hold.

(i.e. Hourly_Emp and Contract_Emp COVER Employee)

HOURLY-EMP

CONTRACT-EMP id contract-id

id hours_worked hours_wagesname address

name address

COMP231 53

The first method is more general. Disadvantage: an extra relation is needed. More operation may be necessary when we need

to get the employee information (e.g. looking up two relations).

The second method is not always possible. Advantage : information of each employee is

more easily accessible (usually only one relation look up).

However, if an employee can be both hourly and contract based, then information of the employee will be stored twice.