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

Lecture 1: 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)Identity Relationships:

Lecture 2: Identifying Relationships (Verb describing relationships and need to track related records)

• data on actions to be recorded and so 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
  • Patrons reserve Books 

Lecture: Can relationships have data or attributes (characteristics)? (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?

Lecture: How to handle 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

Lecture: 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 System: 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?

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.

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. 

Example 2: (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.

A List of Examples to Consider:

• Book and Book (library system)
• Test and Test (healthcare)
• Course and Course (Registration System)
• Flight and Flight (Airline Reservation)

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 Practice Questions:

• Create an ERD for an inventory manager to keep track its vendors, products, categories, inventories, orders, shipments, and backorders
• Create an ERD for a clinic to keep track its patients, appointments, doctors, and visits. Then convert the ERD into a relational model. Print out the relational model to submit.

Reading Assignments: Chapter 3 of LIU and Oracle Data Modeler

Writing Assignments:

• Correctness Questions: online at course.org
• Hands-on 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. draw the complete ERD to model the data requirements. Note that: 
  • Customers are those who book reservations while passengers are people who are booked flights for
  • Each reservation may book multiple flights with each for one passenger and has its own Record Locator No
  • Each flight has scheduled start airport and destination airport as well as zero or more stopovers.
  • Sometimes different airlines often share the same flight but advertise it as different flights with its own flight number.
  • A reservation can be canceled partially, meaning some but not all reserved flights can be canceled.