Class Exercise:  Database Design II

Database structure (schema) design:
   Draw a diagram to model the database, identify the data elements, choose 
   atomic attributes by subdividing multi-components data items, identify tables
   and assign columns, specify keys, enforce table relationships, assess 
   normalization forms, jdentify indexes needed to support querys
1. Describe techniques for identifying data elements required by the system:
   analyze existing system, evaluate comparable systems, interview personnel,
   review documents, identify the entity classes from associated elements.
2. Create atomic attributes: identify data elements composed of components; 
   determine whether multi-component elements will need to be parsed, 
   verify components may be concatenated;
3. Identify tables and assign columns:
   a. Specify a table definition for each entity class represented in the model;
   b. To determine table columns, group data elements by entity class;
   c. Delete unecessary data elements; 
4. Identify primary and foreign keys.
   a. For primary key, choose unique attribute(s), that are persistent and short.
   b. If a suitable column does not exist, create an artificial primary key.
   c. For a 1:M relationship between tables, choose/add a foreign key column for
      the "many" side. It must have the same data type as the related primary key.
   d. For M:M relationships between tables, define a correlative table to link;
      Each of the tables will have a 1:M relationship with the linking table;
   e. Tables with a one-to-one relationship are related by their primary keys;
      this structure is created for performance or to minimize null values.      
5. Create constraints to enforce referential integrity (maintain relationships 
   between tables).
   a. Table with a foreign key is to have values that match primary key values in
      the related table.
   b. For MySQL, use InnoDB tables.
   c. When a table is created, decide whether to use "declarative referential 
      integrity" -- foreign key constraint with rules for delete and update.
   d. When referential integrity is not enforced, child rows may be "orphaned".
   e. Enforcing referential integrity can affect performance.   
6. Normalize as needed.
   a. Decide how data is separated into tables.
   b. This step is taken to reduce redundance and increase consistency.
   c. When normalized, each table has a single theme.
   d. Apply normal forms in sequence:
      1NF: cell values are atomic, no repeating groups;
      2NF: nonKey attributes depend on the entire primary key;
      3NF: nonKey attributes depend only on the primary key.
7. Identify columns to be indexed based upon querys to be issued and data to be
   retrieved. Create an index when the column(s)
   a. is a foreign keys;
   b. used frequently in searches and joins;
   c. has many distinct values;
   d. is frequently updated.   
   * An index is automatically created for primary key. 
8. Determine whether the database structure should be "denormalized".
   After normalization, tables will need to be joined to provide
   needed results.
   a. When a column from a joined table is used repeatedly, better 
      performance may result from combining tables.
   b. If a table is updated infrequently, denormalizing may improve
      efficiency with low risk of data redundancy or modification 
      anomalies.
   c. Performance may also be improved by including columns with derived
      values. Do ensure the value is synchronized with the source column(s).   
9. Normalize this relation:
   Schedule(sId, sName, major, classYear, (cCode, cTitle, meetingTime, room,
      fId, fName), totalHours)