Week 4
Teaching and Learning Methods to be Employed
i. Lecture with relational model concepts and worked examples
ii. ER-to-relational mapping exercise
iii. Lab: Creating tables with constraints in MySQL
iv. Quiz on entity and referential integrity
Learning Outcomes / Objectives
By the end of this week, students should be able to:
1. Describe the relational data model using formal terminology.
2. Convert an ER diagram into a relational schema.
3. Define and distinguish between different types of keys.
4. Explain and implement entity integrity and referential integrity constraints.
5. Write SQL DDL statements to create tables with constraints.
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. |