Review on Business Data Modeling:
- How do you handle composite attributes?
- How do you handle multi-valued attributes?
- Verb describing relationships and Need to Track Related Records
- data on actions may be recorded and so to turn action verbs into objects
- Customer buys products
- Student reserve books
- Orders contains products, direct or indirect relationships?
- Employees balance inventories, direct or indirect relationships?
- Employee manage accounts
- Doctors prescribe tests
- Professors teach students
- Customers wish-list products
- Customers rate Products
- Professors advise Students
- Patrons checkout Books
Lecture 1: 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
Example 2. Convert the entity-relationship diagram into a relational model manually.
Example 3: Transform the following E-R diagram into a relational model.
Additional Transformation Exercises:
- 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 2: 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 3: Convert the ERD involving courses, perquisites, classes, instructors, and students, and enrollment into a relational model.
Additional Conversion Examples:
Review of Forward Engineering Rules
- Where do you create foreign keys for 1:m relationships? Are FK values required?
- Where do you create foreign keys for m:m relationships? Are FK values required?
- Where do you create foreign keys for 1:1 relationships? Are FK values required?
- How do you handle weak entities and associative entities?
Reading Assignments: Chapters 3 and 6 of LIU
Writing Assignments:
- Correctness Questions: online at course.org
- Hands-on Questions: Covert the following ERD into the relational model and implement the relational model in Oracle
|
