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
Additional Advanced Entities and Relationships: 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 2: Can two or more relationships exist simultaneously? (Chater 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
Additional Modeling Exercises:
- Create an ERD for the following problem using Oracle Data Modeler: BizbyOrder Books is specialized in ordering books for two types of customers: individuals and businesses in lower Manhattan. This is how these two different customers are different. When an individual customer orders books, he or she has to pay 20% down payment. A business customer can establish a credit line with BizbyOrder and pays 50% down if only if the order amount exceeds its credit limit. Business orders often purchase multiple copies of the same title while individual customers do so rarely. Often, when an order containing multiple books is not completely fulfilled, one or more back orders may be created.
- PartDepot wants to develop a system to handle its inventory, products, categories, replacement orders including quantity and discounts, shipments, backorders (when an order is not received completely, a backorder will be created), vendors, bills, and payments. Note that some locations may have multiple branches or stations to take orders. Identify weak entity (or entities), associative entity (entities), and recursive relationship (relationships). Then draw the complete ERD to model the data requirements.
- Library System: Create an ERD for a library to keep track of its readers, books, physical book copies, reservations, checkouts, returns, due dates, late fees. Note that different reader categories often come with different borrowing privileges such as types of books to check out and how long to keep the books. Identify weak entity (or entities), associative entity (entities), and recursive relationship (relationships). Then draw the complete ERD to model the data requirements.
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 manually on paper and submit a copy of your relational model.
|
