Print this chapterPrint this chapter

Week 4

4.4 ER to Relational Schema Mapping

Converting an ER diagram to relational tables follows these rules:

ER Concept

Mapping Rule

Strong Entity

Create a table with all simple attributes. Composite attributes → individual columns. Multi-valued attribute → separate table. Derived attributes → usually omitted (computed in queries).

Weak Entity

Create a table with its own attributes + the PK of the owner entity (as FK). Primary key = {owner PK + weak entity's partial key}.

1:1 Relationship

Include the PK of one entity as FK in the other (whichever has total participation is preferred).

1:N Relationship

Include the PK of the '1' side as a FK in the 'N' side table.

M:N Relationship

Create a new junction/bridge table with the PKs of BOTH entities as its composite PK (both are also FKs).

Multi-valued Attribute

Create a separate table with the entity PK + the multi-valued attribute. The PK is {entity PK, attribute value}.

 

MAPPING EXAMPLE — University ER to Relational Schema:

STUDENT (MatricNo, FirstName, LastName, Email, Level, DeptCode[FK→DEPARTMENT])

DEPARTMENT (DeptCode, DeptName, Faculty)

COURSE (CourseCode, CourseName, CreditUnits, DeptCode[FK→DEPARTMENT])

ENROLMENT (MatricNo[FK→STUDENT], CourseCode[FK→COURSE], Grade, Semester) — PK: (MatricNo, CourseCode, Semester)

LECTURER (StaffID, FullName, Rank, DeptCode[FK→DEPARTMENT])

Reading List / References

Codd, E. F. (1970). A Relational Model of Data for Large Shared Data Banks. Communications of the ACM, 13(6), 377-387.

Ramakrishnan, R. & Gehrke, J. (2003). Database Management Systems, Chapters 3 & 19. McGraw-Hill.

Silberschatz et al. (2020). Database System Concepts, Chapter 2: Relational Model. McGraw-Hill.

Activities

Self-Assessment Quiz: 1. What is the difference between a superkey and a candidate key? 2. State the Entity Integrity constraint. Why is it necessary? 3. State the Referential Integrity constraint. What happens if you delete a department that students still reference (ON DELETE RESTRICT)? 4. Map the following 1:N relationship to relational tables: One BANK_BRANCH has many ACCOUNTS. Each ACCOUNT belongs to exactly one BRANCH.