Data Models to Database Design

• Last section we modeled the data for a problem.
• Now we want to turn that model into a database.
• He points out that the same abstract design will result in different databases
  • The DBMS selected will influence what you can and can not do.
  • And you always make implementation choices.
  • But you know this.
• System analysis frequently breaks design into three stages
  • Conceptual design
  • Logical design
  • Physical design
• The first stage was the last chapter.
• We are working on the second stage now, design a database schema
• The third stage is when you implement the tables in the DBMS
• From a data model such as an E-R diagram to a database schema
  • Create a table for each entity
    • Specify primary and candidate keys
    • Specify properties for each entry
      • Data type
      • Null status
      • Default value
      • Data constraints
    • Verify that the table is normalized
  • Create relationships by placing foreign keys
    • more on this today
  • Specify logic for enforcing minimum cardinality
    • Probably several days on this
• He has delayed discussion of the physical step until later, but we have been mixing this in.
• A word of caution, the pictures he uses in this chapter are very similar.
  • 
  • But he will put far more information
  • 
  • 
• Create a table for each entity
  • The primary key, as we have discussed is important for searching and sorting
  • It is most likely the identifier for the entity.
    • He puts an icon of a key by primary keys.
    • But the letters PK would be sufficient.
    • It is best if it is short and numeric and of fixed size.
    • Name, address, email, ... do not meet these criteria
    • Surrogate keys are a good solution but
      • Not part of the actual data. Have no meaning associated with the data.
      • Could be a problem when databases are merged.
      • But Kroenke suggests a company wide policy for constructing surrogate keys.
    • He makes an interesting suggestion
      • If any tables have a surrogate key, all tables should have a surrogate key.
      • This leads to consistency
      • But may be too rigid, some candidate keys are perfectly acceptable primary keys.
      • He points out some variations in how different DBMS restrict/use surrogate keys.
      • He suggests that in general surrogate keys are good things.
  • Alternate Keys/ Candidate keys
    • Marked with
      • AK
      • n.m
      • where n is which alternate key
      • And m is which component of the alternate key.
      • 
  • Column properties
    • Can it be null (NULL/NOT NULL in diagram)
      • He points out that allowing alternate keys to be null creates a problem.
      • If they are null, then they are not true alternate keys.
      • But apparently this is a common practice.
    • Data Type-
      • Again, he points out that this is an area where different DBMS support different data types.
      • But some design software allows you to specify the DBMS, and will restrict design to those datatypes.
      • He suggests building your design independent of a DBMS however.
    • Default value
      • This can be simple.
      • Or constructed with a trigger (coming soon)
    • Data constraints
      • Domain constraints (lints on values in a column) (Ford, Chevy, Dodge)
      • Range limits data to a range.
      • intrarelational constraints limit to a comparison on another column (review date must be 3 months later than hire date)
      • Some DBMS support a CHECK (expr)

        • CREATE TABLE tablename (
               filedname fieldtype  CHECK(expr)
               ...
          )
          		      

        • But in MYSql "The CHECK clause is parsed but ignored by all storage engines." (Online Man page)
      • So this will have to be a trigger check as well.
    • Finally Verify normalization
      • Kroenke tells us that the results are generally normalized when we use the process in chapter 5.
      • Especially when the forms and reports we used are well designed.
      • But we should check on this anyway.
• Next create the relationships.
  • The tables in step 1 are just isolated tables.
  • We place foreign keys in tables to allow us to build referential integrity constraints.
  • 1:1 strong entities
    • One primary key from one table must be a foreign key in another.
    • Club member , locker relationship.
    • We can put the lockerID into the CLUB_MEMBER table, or the CLUB_MEMBER into the Locker table.
    • Is there any preference?
      • Can we find the information we want either way?
      • Does one make more sense than the other?
  • 1:n strong is easy
    • Put the primary key from the 1 as a foreign key in the n(s).
    • Company /Department relationship
  • M:N strong
    • Company/Part Relationship
    • Build an intersection table.