Logical Database Design
For the ER diagram you created in assignment, the artefact of the conceptual database design, map the ER model into the relational model according to how it was designed in the ER diagram. You may however first refine or completely re-do your ER diagram if necessary, and you are allowed to make use of any part of our above displayed ER diagram skeleton to incorporate into your design in any way you like if you feel your original design is not in a state to be implemented later. While there can be a variety of acceptable designs, we here attach one simplistic rough sketch for the comparison purpose.
Consider a greatly simplified core recruitment system whose ER diagram is shown on the right.
Draw the corresponding GRD, exhibiting all the primary keys and foreign keys. For simplicity, no other attributes nor multiplicity constraints are required.
Are the tables in the GRD all in 3NF?
For a particular casual job, if none of the (registered) casual staff meet the requirements on the corresponding expertise, will the primary key or foreign key constraints in your GRD prevent the database system from assigning an unqualified casual to the job? Briefly explain why.
For all the relations that arise from this ("first-cut") ER diagram, list all those (in schemas) that are already in 3NF. If there are some relations that are not in 3NF yet, list them as well.
Draw the global relation diagram for your final, revised, and normalised database design, and keep all the relevant details there. It should be in a form similar to Figure 17.9 (page 516) of the textbook, but all the attributes should be kept there too. Include in the diagram all the primary keys, foreign keys, and the multiplicity constraints. Identify and discuss the potential data redundancies or anomalies that may still exist in your design, if any.