Overview/Review of DB Logical Design

Overview/Review of DB Logical Design ER Model to Relational Model Mapping

Transforming ER into RDB ER RDB attr attr table relationships table table entities

Transforming ER into RDB (2) • Make a relation for each entity • Unique identifier of entity becomes key of relation • Attributes of entity becomes attributes of relation • For each 1 - M relationship: • Take the primary key of the 1-side entity and place it as a foreign key in the M-side entity relation

Transforming ER into RDB (3) • 1 - 1 relationships can be mapped similarly when compared to 1 - M’s • Where do you house the foreign key? • Make a relation for each M - M relationship • Attributes of relation • Attributes of relationship + key of each entity • Primary key of relation • Unique identifier of each entity {+ attribute of relationship}

StaffNo Staff Branch Property PropNo Rent Transforming ER into RDB (4) Address • Example: M 1 Percent IsAllocated Oversees M M 1 M Has BranchNo TelNo Staff (StaffNo, Address, …) Branch (BranchNo, TelNo, …) Property (PropNo, Rent, …, StaffNo, BranchNo) IsAllocated (StaffNo, BranchNo, Percent) Note: Underline attributes are PKs and italics are FKs; an attribute can be both PK and FK

Transforming ER into RDB (5) • Sometimes a 1 - M relationship should be mapped into a separate relation • E.g., Employee (Emp#, …) Car (Car#, …) IsAssigned (Emp#, …., Car#) Car# M 1 IsAssigned Emp# Employee Car

SS# SS# MarriedTo Person Spouse Owns Car Accident License# ER to Relational: Exercise Age 1 1 M M 1 M Logs VIN model Damage $

Overview/Review of DB Logical Design