• The first ste, turn Entites into tables and relationships follows some given patterns.
• Chapter 5 introduces the patterns
• Chapter 6 introduces the solutions.
• I really didn't like this so:
• We look for patterns with known solutions.
  • A standard solution to a common problem
  • Anyone solving this problem who knows the solution will konw what is happening.
  • Provides a common language with which to discuss the solution/problem.
  • Think for loop/stack as opposed to free style solution.
• 1:1 Strong entities
  • A sports club.
  • Each member is an entity.
  • Each locker is an entity.
  • A member may have a locker, but only one.
  • They are strong.
    • Each can exist without the other.
  • Some members may not rent a locker.
  • Some lockers may not be rented.
  • 
  • This is solved by placing a the primary key of one table into the other table as a forein key.
  • Verify that given one, we can find the other.
  • The 1:1 is enforced by requiring the foreign key to be unique at the database level.
  • It is listed as an alternate key, but just to document, it might be null, so it can't be a PK.
• 1:M strong
  • A department within a company.
  • We determine that a department must be assigned to exactaly one company
  • But that a company may have many departments. (OR it may have 0)
  • A department does not exist without a company.
  • 
  • Place the primary key from the company into the department table as a foreign key.
  • We really don't want the FK to be unique in this case.
• N:M strong.
  • Many companies supply parts.
  • Each part may be supplied by many companies.
  • Each company may supply many parts.
  • Each part must come from a company
  • But we may deal with a company which doesn't supply parts (Shipping)
  • 
  • Solution: Build an intersection table.
    • It holds two foreign keys.
    • Which are primary keys in the other two tables.
    • And the combination makes up a primary key for this table.
• An Association Pattern
  • look at the bottom of page 172 and 173.
  • In the second report, the price is included.
  • The price is really not part of the company, there are many prices for a company in that case.
  • It is really not part of the part either. Again parts come from many companies.
  • The price does not exist alone, thus it is a weak relationship.
  • It is id dependant, as to save the price we need attributes from other entities.
  • 
  • The solution is just like it looks.
  • The same solution as above, but it includes some data.
  • He points out that this is strange, such a table will NEVER appear in the company's documentation.
  • This is called an association table.
  • He discusses a ternary relationship as well.
    • An architect's office has clients, projects and architects.
    • A client may have many different assignmetns associated with a project
    • An architect may be assigned to many different assignments associated with a project.
    • A project may have many different assignments.
    • But an assignment has only one client, architect and project.
    • 
    • The solution is still a table with three foreign keys and one attribute that is not.
• A Multivalued Attribute Pattern
  • A company has multiple phone numbers.
  • They may also have multiple contact persons.
  • The phones might be in a generic bank (ie no association between phones and contacts)
  • 
  • Or each contact may have an individual phone number.
  • 
  • Again, the pattern is easy, just a compound primary key, with the company name as a foreign key.
• Archtype/Instance Pattern
  • A version/instance pattern.
  • A course exists as a prototype.
  • It has multiple instances (sections)
  • In this example, there is a design for a yacht (archtype).
  • Then versions (instances)of the yacht are built.
  • In these cases, the primary key attribute of the archtype is/is not part of the instance. (Depending on design)
  • Read the discussion about this on page 179.
  • 
  • In either case, the Primary key from the archtype is a foreign key in the instance.
    • For a strong relation, it is an alternate key
    • For a weak relation, it is part of a composit primary key.