More Patterns

• Line-Item Pattern
  • Consider a company where customers place orders
    • A customer places an order
    • The customer may go through a sales person, or not.
    • An order has many different items. (Called line items in this context)
    • Four simple entities
      • Customer
      • Sales Person
      • Order
      • Item
    • But like may other examples an additional entity is needed.
      • Is a line item (item no, quantity, price, extended price) part of any of these entities?
      • Probably not, so a line item entity is created.
      • 
      • Notice LINE_ITEM <-> SALES_ORDER is weak, (Identifying).
  • Nothing very special here in implementation.
    • SalesOrderNumber will be part of a primary key in LINE_ITEM
    • ItemNumber will be a foreign key in the LINE_ITEM table.
    • It may not be null.
    • This does lead to a discussion (Eventually) of what happens if an order is deleted.
    • Or if an item is deleted from an order.
  • Just a side note on other tables
    • SALES_ORDER will have foreign keys:
      • CustomerID which must be not NULL, WHY?
      • SalespersonID, which may be NULL, WHY?
  • Several other examples of this pattern are given.
• For Use By Pattern
  • This pattern occurs when polymorphism is required.
  • Look at table 5-36 on page 182
  • This pattern is named after the "For Use By" fields.
  • Notice in this case, there are two of them.
  • This is the same as having special cases for graduate/undergraduate students.
  • This is a subtype in an ER-Diagram.
    • A subtype is indicated by a circle.
    • If the circle has an X, the type is exclusive (only one case)
    • Graduate/Undergraduate
    • If the circle does not have an X, it is inclusive, student in multiple clubs.
    • We will be using two different tables for the subtypes.
    • We will need to add a field tot he FISHING_LICENSE (IsCommercial) to indicate which table to search for data in.
    • We will use FICHING_LICENSE.LicenseNumber as a primary key in each of the subtypes.
    • He states that there is no way to make sure that a license number is not used in both tables in relational databases. The font end logic needs to make sure of this.
• Recursive Patterns
• 1:1
  • The example is using a DB to store information about a train.
  • Specifically we want to maintain the order of the cars.
  • So an engine has a relationship with 0 or 1 engines.
  • An engine has a relationship with 0 or 1 cars
  • A car has a relationship with 0 or 1 cars.
  • 
  • In each case, place the relating id into the table and mark it unique, but nullable.
• 1:N recursive
  • An employee manages other employees.
  • A field in the employee field contains manager id.
• N:M recursive
  • Many parts build a wagon.
  • The handle is built of many parts