Examine the table shown below. This table represents the hours worked per week for temporary staff at each branch of a company.

Question


(a) The table shown above is susceptible to update anomalies. Provide examples of how insertion, deletion, and modification anomalies could occur on this table.
(b) Identify the functional dependencies represented by the data shown in the table. State any assumptions you make about the data (if necessary).
(c) Using the functional dependencies identified in part (b), describe and illustrate the process of normalization by converting Table 1 to Third Normal Form (3NF)
relations. Identify the primary and foreign keys in your 3NF relations.
(d) Create an Entity–Relationship (ER) model using the Unified Modeling Language (UML) to represent the data shown in Figure 1. Your ER model should show all entities, relationships, and attributes.

Answer 1

(a) Using the data shown in this table, the student should provide examples of how insertion, deletion, and update anomalies could occur.
(b) 
(c) Primary key for table is staffNo, branchNo Student should identify that primary key for table and describe why the table is in 1NF. First Normal Form (1NF) is a relation in which the intersection of each row and column
contains one and only one value. Student should indicate why table is not in 2NF. Second Normal Form (2NF) is a relation that is in first normal form and every non-primary-key attribute is fully functionally dependent on the primary key.