Improving the Design

• Table structures are cleaned up to eliminate troublesome initial partial and transitive dependencies.
• Normalization cannot, by itself be relied on to make good designs.
• It is valuable because its use helps eliminate data redundancies
• Issues to address to produce a good normalized set of tables:
• Evaluate Primary Key Assignment
• Create JOB_CODE and JOB table to reduce data entry errors
• E.g. Database designer in not mistakenly written as DB Designer
• Evaluate Naming Conventions
• Keep consistent with table name
• So CHG_HOUR-> JOB_CHG_HOUR
• Refine Attribute Atomicity
• An atomic attribute is one that cannot be subdivided and is said to display atomicity
• EMP_NAME becomes EMP_FNAME, EMP_LNAME
• Identify New Attributes that might be useful
• For example add EMP_HIREDATE or data about address etc. Identify New Relationship.
• Project and Employee tables have new relationships that may allow you to identify data about the project manager.
• Refine Primary Keys as Required for Data Granularity
• Granularity is the level of detail represented by the data. Data stored at their lowest level of granularity are said to be "atomic data".
• Use ASSIGN_NUM to get greater accuracy and granularity to avoid entity integrity violations.
• (e.g. what if employee worked on project at different times? then there is no longer a unique primary key if composite key PROJ_NUM and EMP_NUM is used.)
• Maintain Historical Accuracy
• Identify past and current charge rates using ASSIGN_CHG_HOUR instead of JOB_CHG_HOUR
• This ensures you have accurate record of what happened at the time of the project
• Evaluate Using Derive Attributes
• In large database, you might want to store derived attributes to reduce time to produce reports.