Review:
ER Modeling Tips:
1. Did you capture all the basic requirements? Identify all the required objects with attributes. Without them, your database will not have places to store required data. (All the important objects given in the question must present unless you can justify their absences).
2. Did you capture the basic business rules? All relationships reflect "subject-verb-object" gramma and your database needs to trace subject to object or vice versa. (Rules may be specified in the question or considered common sense such as Customer makes reservations, airlines has flights, flights route multiple airports, etc.)
3. Did you use advanced concepts to handle special requirements? This is a higher-level requirements. Remember the classic examples of using them. For example, gerunds for data on relationships, weak entity for multivalued attributes, recursive relationships for relationships among objects of the same kind.
Lecture 1: CASE
- CASE (Computer-Aided Software Engineering): Upper CASE (model user requirements), Lower CASE (create database or program specs), I-CASE (both). Example CASE Tools: Visio (upper CASE), Oracle Designer (I-CASE for database and systems development), Oracle Data Modeler (I-CASE for Database Development only)
Lecture 2: How to Identify Relationships
- Verb describing relationships and Need to Track Related Records
- data on actions may be recorded and so to turn action verbs into objects
- Examples:
- Customer buys products
- Student reserves books
- Student checkouts books
- Customer evaluate products
- Customer wish list products
Example 1: (Order System) Customers checkout items in an online store and make payments.
Identify Business Objects: In the above example, we identified Customers, Items, and Payments. What is an item object? Is it a catalog entry or actual product? Do we need to manage data on individual physical item? If all physical products of the same SKU are identical, it is easier to just create one record for the catalogue product and do not bother to record each physical products. However, in some other cases, we do need to record each physical product. Examples include antiques, expansive products with unique serial numbers, etc.
Identify Relationships: Customers buy products. By the semantics heuristics, it is tempting to connect Customer and Product directly. However, it will not be appropriate in this case. One customer can potentially buy any products at different times and we need to have historical record on the buy actions, which may be termed Orders, Transactions, Checkouts, Purchases, etc. So it is more appropriate to have indirect connection as follows: Customer --- Order --- Product.
Lecture 3: Advanced Entities and Relationships
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?
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)
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
-
Registration System: Create an entity-relationship model for courses, sections, perquisites, instructors, and students: 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?
Additional Practice Questions:
- 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: Chapter 3 of LIU and Tutorial on Oracle Data Modeler
Writing Assignments:
- Correctness Questions: online at course.org
- Hands-on Questions: An online store needs to have a database to track its customers, Employees, products, orders, returns, shipments, and payments so that customers can look for product description before order it and track shipments or return an order after the order. The store can find customer information to ship orders. Note a customer may have multiple shipping addresses. In addition, the store is interested to know which employee fulfill an order in case there is a problem. Note that an order may multiple quantities of one product, and the store sometimes have quantity discount if a customer ordered a large quantity of one product. Draw an Entity-Relationship Diagram for the store.
|
