Review:

• Homework Question to Review
• Database Concepts:
  • A record is an array of observations on one entity: Entity (ERD) <—> Record (RM)
  • Attributes are properties or dimensions of entities (business objects) to be observed, but attributes must be relevant, singled valued, and non-foreign.
  • Composite attributes must be split into a few single-valued ones, and foreign attributes suggest relationships.
  • Relational Model (a logical data model): records, relations, fields, primary keys, foreign keys, constraints
• ER Model (A conceptual or business data model): Entities, entity sets, attributes, unique identifier, relationships, relationship sets, mapping cardinality, optionality, business rules

• Cardinality and Optionality: 

  • Attribute is optional is its value may or may not be observed or recorded (Symbols for Mandatory * and Optional o)
  • Relationship: Is one entity required or optional to participate in some relationships with other entities? For instance, in Student --- Reservation relationship, a student may or may not have reservations, and so the student entity is optional in the relationship. Each reservation is made by one student, and so the reservation is mandatory in the relationship. For another example, a reservation must contain at least some books, but some books may have never been reserved. So the optionality of Reservation -- Book relationship is M:O

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

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. 

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: This is an extension of Homework 1 Question. 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. In addition, the store is interested to know which employee fulfill an order in case there is a problem. Draw an Entity-Relationship Diagram for the store.