Review: Course and Sections (why splits), Course and Prerequisites (Two entities or one?), Grade (where to store it), minimum grade required for prerequisites
Lecture: What about entities that are different but similar? (Chapter 3 of LIU)
Super and sub type entities: How to handle different types of entities that have many attributes in common, e.g., part-time and full-time students and instructors? patient or visit as super type for inpatient and outpatient? Customer or Transaction as super type for Buy and Sell
Lecture: Can two or more relationships exist simultaneously? (Chapter 3 of LIU)
Exclusive Relationships: two or more relationships that cannot hold simultaneously. Examples: Offices for part-timers and full-timers, claims made by patients and clinics
Lecture: Forward Engineering: ERD to Relational Model: (Remember these rules, Chapter 6 of LIU)
- Each entity set is converted into a table with its attributes as the columns of the table
- Depending on the mapping cardinality, you modify the tables to make them related
- If the relationship is 1:m or m:1, you put the primary key of the table for one-side entity set into the table for the many-side entity set
- If the relationship is m:m, you build a junction table which has the primary keys of both entity sets as columns
- If the relationship is 1:1, you can put the primary key of one entity set into the table for another entity set.
- Foreign keys are optional if and only the receiving side is optional. Foreign keys in a junction table is always required.
Example 1: Convert the ERD for Customer – Order – Product problem into the relational model
Additional Transformation Exercises:
-
Transform the following E-R diagram into a relational model. Then create the database using Oracle.
- The following relational model was converted from an ERD that has been lost (Bold fields indicate the primary key). Please recover the ERD based on all the information given:
Part: PartNo, Description, Quantity, UnitPrice
Bin: BINNO, Capacity, Location, PartNo
Department: Name, Phone, Contact, Account, Balance
Dept_Part: Department, Part
Supplier: Name, Address, City, State, Zip, Phone
SPL_Part: FK_Name, FK_Partno
Lecture: Convert ERD into relational models: conversion rules on Weak entities, gerunds, exclusive relationships, recursive relationships, and super/sub type entities (Chapter 6 of Liu)
Example 2: Convert the ERD involving courses, perquisites, classes, instructors, and students, and enrollment into a relational model.
Additional Conversion Examples: Convert the following ERD into a relational model:
 
Reading Assignments: Chapters 3 and 6 of LIU
Writing Assignments:
- Correctness Questions: online at course.org
- Hands-on Questions:
- Convert the following ERD into relational model manually on paper:
|
