Patterns in Forms, Reports and ER-Models

• But first, the diagram from last time.
• 
• We will begin looking at some standard things that happen in database design.
• We will call these patterns.
• 1:1 Strong Entity Relationships
  • Example: lockers in a sports club as they relate to customers

    Customer ID	Locker Number
    C-100	L-001
    C-101	L-201
    C-102	L-022

  • Lockers exist without customers.
  • Customers exist without lockers.
  • Each customer has (at most) one locker
  • Each locker has (at most) one customer
  • But there may be empty lockers, and there may be customers who don't want a locker.
  • This is a strong relationship.
  • 
  • The big question here seems to be, Is the relationship optional or mandatory?
• 1:n Strong Entity Relationships
  • Book's example is bad: Departments within a company.
  • How about parts and a car.
  • Think of this as a junkyard database.
  • They have a bunch of cars sitting around for parts.
  • They also have some parts on the shelf.
  • There are also some hulks sitting around with no usable parts.
  • A car has many parts.
  • A part is only on one car, but it might not be on any
  • A part can not be on more than one car.
  • This is strong since both a car and a part may exist without each other)
  • 
• N:M strong entity relationship
  • A instructor course relationship.
  • We have a list of courses.
  • We have a list of instructors.
  • Many different instructors may teach a given course (qualified, not assigned)
  • An instructor is qualified to teach many different courses (qualified not assigned)
  • 
• ID-Dependent Relationships: Association pattern.
  • Look at the table on the bottom of 173.
  • We have parts (name, price, ...)
  • We have suppliers
  • But we assume the price from the supplier might change.
  • So we have a third entity, the quotation.
  • QUOTATION(PartNumber,SupplierID,Price)
  • QUOTATION is in a weak relation to both part and supplier.
  • Some companies might not supply a particular part. (So they will not have a quote)
  • But we need at least one company to sell us any given part. (But more may)
  • The price of a part from a company might change over time (so there will be possible multiple quotes from the same company)
  • But we need at least one quote per part.
• The Multivalued attribute Pattern.
  • Consider the university course list.
  • Each instructor can be assigned to a class
  • Each class has at least one instructor (But there may be more than one)
  • A class CAN NOT exist without an instructor.
  • So this is a weak relationship.