Review on Business Data Modeling:
- How do you handle composite 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: Advanced Entities and Relationships
1) What if relationships have data or attributes? (Chapter 3 of LIU)
Associative Entities or Gerunds: where to keep students grades, Where do you store data on whether a payment is late or not? Bank employees may set up or modify accounts. How do you keep a log of the operations?
2) What if there are multivalued attributes? (Chapter 3 of LIU)
Weak entities: How do you handle multiple sections of courses? How to handle if a student has multiple addresses? Library Example Revisit
A List of Weak Entity Examples:
- Book and Book (library system)
- Test and Test (healthcare)
- Course and Course (Registration System)
- Flight and Flight (Airline Reservation)
3) What about relationships among entities of the same type? (Chapter 3 of LIU)
Recursive relationships: Create an ERD to capture data on employees and dependents. How to handle the relationship between a manager and supervisees?
Example 1: (Library System): Students reserve, checkout, and return books using the library system.
Identify Business Objects: Note that in a library, each title often has multiple copies. So what is the book object? Is it the catalog entry or the actual book copy? Do we need to manage data on individual copies? In fact, we will need to have separate records for catalog book (ISBN, Title, Price, etc.) and a physical book (book copy number, condition, acquired date, etc.). Patrons check out and return physical books but reserve catalog books.
Identify Relationships: Student reserve books (catalog books), and student checkout books (physical books or book copies). By the semantics heuristics, it is tempting to connect Student and Book directly. However, it will not be appropriate in this case. One student can potentially reserve any books at different times and we need to have historical record on the reserve actions, which may be termed Reservations, Orders, Transactions, Checkouts, Purchases, etc. So it is more appropriate to have indirect connection as follows:
- Student --- Reservation --- Book
- Student --- Checkout --- BookCopy
- Student --- Return -- BookCopy
Additional Data Modeling Questions:
- Flight Reservation System: Create an ERD for an airline booking system to keep track of customers, reservations, passengers, flights, scheduled flights, airlines, airports, cancellations, invoices, payments, and boarding passes. Note that different airlines often share the same flight but advertise it as different flights with its own flight number. Then draw the complete ERD to model the data requirements
- Create an ERD using Data Modeler for a clinic to keep track its patients, appointments, doctors, and visits.
- Create an ERD for an inventory manager to keep track its vendors, products, categories, inventories, orders, shipments, and backorders
- A student club wants to have a database to manage its data on members, committees, and hosted events. The club assigns members to its numerous committees. It is possible that one member can serve in more than one committee and will chair at most one committee. Each year the club organizes many events. Each time, the club delegates one committee to be in charge of an event. Build an ERD to model the club’s business objects using Data Modeler and then convert the ERD into a relational model. (Hint: Member and Committee have two different relationships: serve and chair).
Reading Assignments: Chapters 3 of LIU
Writing Assignments:
- Correctness Questions: online at course.org
- Hands-on Questions: Student Registration System: Create an entity-relationship model for courses, sections, perquisites, instructors, and students, and make sure to address the following questions in your model: where do you to keep students grades, where to keep instructors evaluations? How to handle if a student has multiple addresses? Where to keep instructors evaluations? How do you handle course prerequisites?
|
