Lecture4: Guidelines for good relational design Mapping ERD to Relation.

Prepared by L. Nouf Almujally & Aisha AlArfaj

Reviewed by Fatima Alhayan

Edited by Kholoud Baselm

1

Ref. Chapter3

I S 2 2 0 : D a t a b a s e F u n d a m e n t a l s

Co

llege

of

Co

mp

ute

r an

d In

form

atio

n S

cien

ces

- In

form

atio

n S

yste

ms

Dep

t.

Outlines

• Logical Data Model

• Relational Model Terminology

• Relational Data Structure

• Relational Databases and Relational Database Schemas

• Relational Keys

• Some Guidelines:

• Semantics of the Relation Attributes

• Insertion, deletion and update anomalies

• Null Values in Tuples

• Primary key

• Avoid Data redundancy

• Derive relations for logical data model (Mapping ERD to Relations)

Lect

ure

4

2

The Process of Database Design

Lect

ure

4

3

Conceptual Design

Logical Design

(Relational Model)

Physical Design

Review

Lect

ure

4

4

Logical Data Model

• In this phase, the main objective is to translate the conceptual data model created in phase 1 into a logical data model of the data requirements of the enterprise.

Lect

ure

4

5

Relational Model Terminology

• The logical (relational) model represents the database as a collection of relations. Informally, each relation resembles a table of values or, to some extent, a file of records.

• A relation is a table with columns and rows.

• Attribute is a named column of a relation.

• Tuple is a row of a relation.

• Alternative Terminology for Relational Model:

6

Lect

ure

4

Relational Data Structure

• Relation is a table with columns & rows. Holds information about entities.

• Attribute is a named column of a relation.

• Tuple is a row of a relation.

• Degree of a relation is the number of attributes it contains.

• Cardinality of a relation is the number of tuples it contains.

Rel

atio

nal

Model

7

Relational Data Structure

• A relation schema R • A relation schema is used to describe a relation

• R is called the name of this relation.

• Denoted by R (A1, A2, ...,An)

• is made up of a relation name R and a list of attributes, A1, A2, ..., An.

• A relation schema R of degree n is denoted by R (A1, A2, ..., An).

Lect

ure

4

8

STUDENT (Name, Ssn, Home_phone, Address, Office_phone, Age, Gpa)

Another Example

Lect

ure

4

9

→ Write the relation schema for those two relation

Relational Databases and Relational Database Schemas

• A relational database usually contains many relations, with tuples in relations that are related in various ways.

• When we refer to a relational database, we implicitly include both its schema and its current state.

• A relational database schema S is a set of relation schemas S = {R1, R2, ..., Rm} and a set of integrity constraints IC.

• A relational database state DB of S is a set of relation states DB = {r1, r2, ..., rm} such that each ri is a state that satisfy the specified integrity constraints.

Lect

ure

4

10

Lect

ure

4

11

• The relational database schema: COMPANY = {EMPLOYEE, DEPARTMENT, DEPT_LOCATIONS, PROJECT, WORKS_ON, DEPENDENT}

• The underlined attributes represent primary keys.

• This Figure shows the schema diagram

Lect

ure

4

12

Figure shows a relational database state corresponding to the COMPANY schema.

Lect

ure

4

13

Relational Keys

• Candidate key (CK) is an attribute, or set of attributes, that uniquely

identifies a tuple, and no proper subset is a CK within the relation.

• Primary Key (PK) is the CK that is selected to identify tuples uniquely

within the relation.

• Foreign Key (FK) is an attribute, or set of attributes, within one relation

that matches the CK of some relation. Used to represent relationship

between tuples of two relations.

Rel

atio

nal

Model

14

GUIDELINE 1: Semantics of the Relation Attributes and tuples

• Design a schema that can be explained easily relation by relation.

Properties of Relations:

• The relation has a name that is distinct from all other relation names in the relational DB.

• Each Attribute has a distinct name

• Each cell of the relation should contains exactly single value

• Each tuple is distinct. There are no duplicate tuples

• The order of attributes and tuples have no significance.

• Only foreign keys should be used to refer to other entities

Lect

ure

4

15

GUIDELINE 2: Insertion, deletion and update anomalies

• Design a schema that does not suffer from the insertion, deletion and update anomalies.

• Attributes of different entities (EMPLOYEEs, DEPARTMENTs, PROJECTs)

should not be mixed in the same relation

• Example

Consider the relation:

EMP_PROJ ( Emp#, Proj#, Ename, Pname, No_hours)

1. Update Anomaly: Changing the name of project number P1 from “Billing” to “Customer-Accounting” may cause this update to be made for all 100 employees working on project P1.

Lect

ure

4

16

2. Insert Anomaly: Cannot insert a project unless an employee is assigned to .

Inversely - Cannot insert an employee unless an he/she is assigned to a project.

3. Delete Anomaly: When a project is deleted, it will result in deleting all the employees who work on that project. Alternately, if an employee is the sole employee on a project, deleting that employee would result in deleting the corresponding project.

Example of an anomaly

Lect

ure

4

17

EXAMPLE OF AN UPDATE ANOMALY

Tbl_Staff_Branch

Tbl_Staff Tbl_Branch

Lect

ure

4

18

• Relations should be designed such that their tuples will have as few NULL values as possible

• Attributes that are NULL frequently could be placed in separate relations (with the primary key)

• NULL values:

• Unknown value: a particular person has a date of birth but it is unknown, so it is represented by NULL in the database.

• Unavailable value: a person has a home phone but does not want it to be listed, so it is represented as NULL in the database.

• Not applicable attribute: an attribute College Degree would be NULL for a person who has no college degrees.

GUIDELINE 3:

Null Values in Tuples

Lect

ure

4

19

1. The candidate key must be unique within its domain.

2. The candidate key cannot hold NULL values (NULL is not zero. Zero is a number. NULL is 'nonexistent value').

3. The candidate key can never change. It must hold the same value for a given occurrence of an entity for the lifetime of that entity.

GUIDELINE 4: Candidate key

Lect

ure

4

20

• Data redundancy is a term used about databases and means simply that some data fields appear more than once in the database.

• Disadvantages :

1. Weak maintaining of the database

2. Waste memory

GUIDELINE 5: Avoid Data redundancy

Lect

ure

4

21

Lect

ure

4

22

Derive relations for logical data model

• In this step, we derive relations for the logical data model to represent the entities, relationships, and attributes.

• We describe how relations are derived for the following structures that may occur in a conceptual data model:

(1) strong entity types;

(2) weak entity types;

(3) composed / multi-valued / derived attributes;

(4) binary relationship types;

(5) recursive relationship types;

(6) complex relationship types; 23

Lect

ure

4

Derive relations for logical data model

• To implement the database in relational DBMS, ERD must be translated to tables

1. Specify the name of the relation.

2. A list of the relation’s simple attributes enclosed in brackets.

3. Identify the primary key and foreign key(s) of the relation.

4. Specify the identification of a foreign key, the relation containing the referenced

• For example:

• Staff (staffNo, fName, lName, position, sex, DOB)

• Client (clientNo, fName, lName, telNo, prefType, maxRent, staffNo) Foreign Key staffNo references Staff(staffNo) 24

Lect

ure

4

1- Mapping strong entity types

• A (strong) entity set reduces to a table with the same attributes and PK.

• If composite attributes exist, only their component simple attributes are needed.

• Derived attributes are usually omitted.

Lect

ure

4

25

Name

FName MName LName

Employee

EmpNo

Employee ( EmpNo, FName, MName, LName )

• Here two new relations are created

1. First relation contains all of the attributes of the entity type except the multivalued attribute

2. Second relation contains two attributes that form the primary key of the second relation:

• primary key for the first relation, which becomes a foreign key in the second relation

• The second is the multivalued attribute

2- Mapping Multi_valued Attributes

Lect

ure

4

26

• A multivalued attribute M of an entity E is represented by a separate table EM

1. Includes the multivalued attribute M in EM

2. Includes the PK of E as FK in EM

3. The PK of EM is the combination of the PK of E and the multivalued attribute M.

2- Mapping Multi_valued Attributes

Lect

ure

4

27 E

EPK

M

EM ( M , EPK )

FK : EPK references E (EPK)

__ ___

Example of Multi-valued Attributes

• Branch( branchNo, street, city, postCode)

• BranchTel (telNo, branchNo)

FK: branchNo references Branch(branchNo)

Branch

BranchNo street

city

postCode

telNo

Lect

ure

4

28

Example of Multi-valued Attributes

Lect

ure

4

29

BranchNo Street City PostCode telNo

B1 Brookline rd London 33100 65509876

B2 River drive Dubai 1320 8976540-8907654

B3 West river rd

London 1100 6789008

telNo

Branch

Example of Multi-valued Attributes

BranchNo Street City Zip telNo

B1 Brookline Rd London 3310 65509876

B2 West End Glasgow 1320 8976540-8907654

B3 West River Rd London 1100 6789008 Lect

ure

4

30

one of guidelines is violated here

Branch

Example of Multi-valued Attributes

Lect

ure

4

31

BranchNo Street City PostCode

B1 Brookline Rd

London 3310

B2 West End Glasgow 1320

B3 West River Rd

London 1100

BranchNo telNo

B1 65509876

B2 8976540

B2 8907654

B3 6789008

The solution:

Branch Branch_Tel

• A weak entity set becomes a table that includes its key and the primary key of the owner entity as FK .

• the PK of the weak entity: is the combination of the two keys

E1 ( A, B )

FK : A references E1 (A)

3- Mapping Weak Entities

Lect

ure

4

32

E2 E1 R M N

Y X A B

E2 ( X , A, Y) ____

Employee has Dependents

• Employee ( EmpNo, Lname)

• Dependents(EmpNo, DepName, DepAge)

FK : EmpNo references Employee (EmpNo)

3- Mapping Weak Entities - Example

Lect

ure

4

33

1 M

EmpNo DepName Lname DepAge

4- Mapping Binary Relationships

Lect

ure

4

34

1. Many-to-many binary relationship set

• Create a new relation R with columns for the PKs of the two participating entity sets, and attributes of the relationship.

• The PK of the new relation consists of the PKs of the two entities.

• The PKs of the two entities also serve as foreign keys referencing the entities.

Lect

ure

4

35

E2 E1 R

M N

R1 B E2-PK E1-PK A

R (E1-PK, E2-PK , R1 )

__________ E1 ( E1-PK , A)

E2 ( E2-PK , B) FK1 : E1-PK references E1 (E1-PK) FK2 : E2-PK references E2 (E2-PK)

Lect

ure

4

36

• Student (stNo, stName)

• Subject (sCode, sName)

• Enroll (stNo, sCode, date) FKs: stNO references Student(stNo) sCode references Subject(sCode)

SUBJECT STUDENT Enroll

M N

date sName sCode stNo stName

Example 1

SNO S_ID Date

433099876 IS 220 3-1-2014

433099876 IS 333 5-4-2014

433221660 IS 220 6-3-2014

433099876 CS 110 3-1-2014

Lect

ure

4

37

SNo SName

433099865 Asma

433099876 Nouf

433221660 Noura

S_ID S_name

CS 110 Programming Language (1)

IS 333 Project Management

IS 220 Database Fundamentals

Student Subject

Enrollment

The Supplies relationship will need to become a separate relation

Example 2

Lect

ure

4

38

M M

New relation Foreign key

Foreign key

Composite primary key

• RAW_MATERIALS (Material_ID, Standard_cost, unit-of_measure)

• VENDOR (Vendor_ID, Vendor_Name, Vendor_Address)

• QUOTE (Material_ID, Vendor_ID, Unit_price) FKs: Material_ID references RAW_MATERIALS (Material_ID) Vendor_ID references VENDOR (Vendor_ID)

Lect

ure

4

39

2. One-to-many binary relationship sets

Instead of using a separate table for the relationship, just modify the tables for the two entities:

add the PK of the one side to the many side. It also serves as a FK of the many side.

Add the attributes of the relationship to the many-side.

Lect

ure

4

40

E2 E1 R

1 N

R1 B E2-PK E1-PK A

• E1 ( E1-PK , A)

• E2 ( E2-PK , B FK1 : E1-PK references E1 (E1-PK)

, E1_PK , R1 )

Example 1

Lect

ure

4

41

staff Department Has

1 N

year sName sCode DeptNo DeptName

• Department (DeptNo, DeptName)

• Staff (sCode, sName , DeptNo , year)

FK: DeptNO references Department(DeptNo)

Example 1

DeptNo DeptName

140 Information System

160 Computer Science

171 Networks Lect

ure

4

42

SID SName

1211 Nora

6550 Fatima

2250 Dena S.

8765 Samar N.

7895 Sara L.

9897 Reem N.

Department Staff

Example 1

Lect

ure

4

43

SID SName DeptNo Year

1211 Nora 140 2010

6550 Fatima 171 2008

2250 Dena S. 140 2013

8765 Samar N. 160 2005

7895 Sara L. 171 2009

9897 Reem N. 190 2006

Staff

DeptNo DeptName

140 Information System

160 Computer Science

171 Networks

Department

1

M

Example 2

Lect

ure

4

44

Foreign key

• CUSTOMER (Customer_ID, Customer_Name, Customer_Address)

• Order (Order_ID, Order_Date, Customer_ID)

FK: Customer_ID references CUSTOMER (Customer_ID)

Lect

ure

4

45

Lect

ure

4

3- One-to-one relationship sets

A. Mandatory participation on both sides

• Add the PK attributes of one side, and attributes of the relationship, to the other side.

B. Optional on both sides

• Choose one side and add its PK, and attributes of the relationship, to the other side.

46

E2 E1 R

1 1

R1 B E2-PK E1-PK A

• E1 ( E1-PK , A)

• E2 ( E2-PK , B

FK1 : E1-PK references E1 (E1-PK)

, E1-PK , R1 )

• E1 ( E1-PK , A

• E2 ( E2-PK , B)

FK1 : E2-PK references E2 (E2-PK)

, E2-PK , R1 )

Lect

ure

4

3- One-to-one relationship sets

C. Mandatory on one side

• Add the PK attributes of the optional side, and attributes of the relationship, to the mandatory side.

47

E2 E1 R

1 1

R1 B E2-PK E1-PK A

• E1 ( E1-PK , A)

• E2 ( E2-PK , B

FK1 : E1-PK references E1 (E1-PK)

, E1-PK , R1 )

1:1 relationship -Mandatory on both sides

• Employee( Emp_name, Emp_id )

• Office (officeNo, office_Loc, Emp_id, year) FKs: Emp_id references Employee (Emp_id)

Employee Office

1 1

has

Emp_name officeNo

Emp_id Office_Loc year

Lect

ure

4

48

1:1 relationship - Mandatory on one sides

• Employee( Emp_name, Emp_id )

• Spouse(Spouse_id, Spouse_name, Emp_id, year)

FKs: emp_id references employee (Emp_id)

Employee Spouse

1 1

has

Emp_name Spouse_id

Emp_id Spoude_name

year

Lect

ure

4

49

1:1 relationship - Optional on both sides

• Employee( Emp_name, Emp_id )

• Car (Car_No, Car_name, Emp_id, year) FKs: Emp_id references Employee (Emp_id)

Employee Car

1 1

use

Emp_name Car_No

Emp_id Car_name year

Lect

ure

4

50

5 – Mapping Unary Relationships

• Unary relationships are relationships between the instances of a single entity type

• They are also called recursive relationships

• The approach to mapping is different for the two types one-to-many and many-to-many

Lect

ure

4

51

Unary one-to-many (1:N) relationships

• Single relation with two copies of the primary key (one needs to be renamed and treated as the FK), plus attributes of the relationship.

• A foreign key attribute is added within the same relation that references the primary key values (this foreign key must have the same data type as the primary key)

• A recursive foreign key is a foreign key in a relation that references the primary key values of that same relation.

Lect

ure

4

52

Unary one-to-many (1:M)

Lect

ure

4

53

E1 ( E1-PK , A

FK1 : E1_PK_Copy references E1 (E1-PK)

, E1_PK_Copy , R1 )

E1 R

1

E1-PK A

M

R1

1

M

Employee ( Employee_ID,Name,Birthdate,Manager_ID)

FK : Manager_ID references Employee (Employee_ID)

Lect

ure

4

54

Unary many-to-many (M:N) relationships

• Two relations are created;

1. one to represent the entity type in the relationship

2. associative relation representing the M:N relationship itself

Associative relation:

• create a new relation with columns for the PK of the entity, copy of the primary key (renamed) , and attributes of the relationship.

• The PKs of the new relation: consists of two attributes, the PK of the entity and the renamed key. Both are taking their values from the primary key of the other relation

• The PKs also serve as foreign keys referencing the Pk in the original entity.

Lect

ure

4

55

Unary many-to-many (M:N)

Lect

ure

4

56

E1 ( E1-PK , A)

New_E ( E1-PK ,

FK1 : E1-PK references E1 (E1-PK) FK2 : E1_PK_copy references E1 (E1-PK)

E1_PK_copy , R1 )

N

E1 R

M

E1-PK A

R1

• ITEM (Item_No, Name, Unit_Cost) • COMPONENT (Item_No, Component_No, Quantity) FK1: Item_No references ITEM (Item_No) FK2: Component_No references ITEM (Item_No)

Lect

ure

4

57

M

M

Unary one-to-one (1:1)

• Mandatory participation on both sides

Follow the rules of mapping 1:M unary relationship

• Optional on both sides

Follow the rules of mapping M:N unary relationship

• Mandatory on one side

Follow either of the two methods above.

Lect

ure

4

58

Unary one-to-one (1:1)

• Person (SSN , Name)

• Marriage (Husband_SSN, Wife_SSN, Date)

FK1: Husband_SSN reference Person(SSN)

FK2: Wife_SSN reference Person(SSN)

Lect

ure

4

59

• Create a relation R to represent the relationship

• Include the PK of the participating entities E1,E2.. En as FKs in R.

• The combination of all FKs form the PK of R.

• Add the relationship attributes to R

6 – Mapping n-ary Relationships

Lect

ure

4

60

6 – Mapping n-ary Relationships

Lect

ure

4

61

E2 E1 R

R1 B E2-PK E1-PK A

E3

A E3-PK

R (E1-PK, E2-PK , E3-PK, R1 )

E1 ( E1-PK , A) E2 ( E2-PK , B)

FK1 : E1-PK references E1 (E1-PK) FK2 : E2-PK references E2 (E2-PK) FK3: E3-PK references E3 (E3-PK)

E3 ( E3-PK , A)

6 – Mapping n-ary Relationships

Lect

ure

4

62

Supplier (SName) Project (ProjName) Part (PartNo)

Supply (SName, PartNo, ProjName, Quantity) FKs : SName references Supplier(SName) PartNo references Part(PartNo) ProjName references Project(ProjName)

Lect

ure

4

63

Lect

ure

4

64

Convert the following ERD to relational tables, specify the primary key and foreign keys

References

• “Database Systems: A Practical Approach to Design, Implementation and Management.” Thomas Connolly, Carolyn Begg. 5th Edition, Addison-Wesley, 2009.

Lect

ure

4

65