Database Theory and Design - Coursework Example

The consulting company need for developing a database system to computerize its operations management requires that we first decipher the relationships between the different entities involved in its day to day business. The Entity Relationship Diagram obtained is shown below:Figure 1: Entity Relationship DiagramNote:The entity CONSULTANT inherits JOB_CODE from JOB_CLASSIFICATION as a foreign key.The entity PROJECT inherits CLIENT-ID# as a foreign key from CLIENT because of the 1: M relationship.

Therefore the unique identifier for PROJECT is a combination of CLIENT-ID# and PROJECT_CODE.There is an M: M relationship between CONSULTANT and PROJECT. This leads to the formation of an associative entity, ASSIGNMENT. By virtue of being an associative entity, ASSIGNMENT inherits the primary keys from PROJECT and CONSULTANT which form the composite primary key: CLIENT-ID#, CONSULTANT-ID#.We are also informed that the job classification determines the hourly billing rate therefore ASSIGNMENT will inherit this attribute from JOB_CLASSIFICATION.

Normalization is a formal process for deciding which attributes should be grouped together in a relation (Elmasri & Navathe, 1994). It involves decomposing relations with anomalies to produce smaller, well-structured relations.Three problems usually arise in databases where relations are not normalized. First we could have insertion anomaly, this is where adding new rows forces the user to create duplicate data in the database. Secondly we have modification anomaly, i.e. where changing data in a row forces us to make changes to other rows because of duplication.

Finally we have deletion anomaly which means that when we delete rows we may cause a loss of data that would be needed for other future rows (Connoly & Begg, 2005).First Normal Form (1NF) means that all multi-valued attributes are removed, so that we have a single value at the intersection of each row and column of the relation table. Second Normal Form (2NF) means we apply the rule for 1NF then we remove all partial functional dependencies. Third Normal Form means we apply the rules for 2NF then we remove all transitive dependencies.

From our case we show how we applied normalization rule on the associative entity ASSIGNMENT below:JOB CLASSIFICATION – job-code, description, hourly-rate is 3NF (third normal form)ASSIGNMENT – client-id#, consultant-id#, start-date, work-hours, job-code, rate-per-hour is in 2NF (second normal form). This is because rate-per hour is transitively dependent on job-code. Therefore to make ASSIGNMENT into third normal form (3NF) we remove rate-per-hour from its attributes to end up with ASSIGNMENT – client-id#, consultant-id#, start-date, work-hours, job-code.

ReferencesConnoly, T. M & Begg, C. E. (2005). Database Systems: A practical Approach to Design, Implementation and Management. Harlow: Addison-Wesley.Elmasri, R & Navathe, S. B. (1994). Fundamentals of Database Systems, 2nd ed. Redwood, CA: The Benjamin-Cummings Publishing Co.