Part $1$ Design the Database

$1.$ Create an Entity$$Relationship $($ER$)$ model of the data requirements for the University Database case study using the UML notation. Note: if necessary, use the additional concepts of the Enhanced Entity$$Relationship $($EER$)$ model. State any assumptions necessary to support your design.

$2.$ Derive relational schema from your ER model that represents the entities and relationships. Identify primary, alternate and foreign keys. Note: use the following notation to describe your relational schema, as shown in the example of a Staff relation given below.

Staff $($staffNo$,$ fName, lName, address, NIN, sex, DOB, deptNo$)$ Primary Key staffNo

Alternate Key lName, DOB

Alternate Key NIN

Foreign Key deptNo references Department$($deptNo$)$ On Delete No Action On Update Cascade

$3.$ Use the technique of normalization to validate the structure of your relational schema. Demonstrate that each of your relations is in third normal form $(3$NF$)$ by displaying the functional dependencies between attributes in each relation. Note, if any of your relations are not in $3$NF$,$ this may indicate that your ER model is structurally incorrect or that you have introduced errors in the process of deriving relations from your model.

$4.$ To further demonstrate your knowledge of normalization, assume that a proposed $($badly structured$)$ relation for the University Database database has the following structure.$$matricNo

name

sex moduleTitle

F BITS

M Software Dev.

F FDBS M OOAD F FDBS

M FDBS

modulestartDate performance flatNo

$27/09/01$ Pass F$00101/10/01$ Pass F$00127/09/01$ Fail F$00201/10/01$ Pass F$00327/09/01$ Pass F$00127/09/01$ Fail F$001$

address

$6$ lady Lane, Paisley $6$ lady Lane, Paisley $28$ New Str$,$ Paisley $28$ New Str$,$ Paisley $6$ lady Lane, Paisley $6$ lady Lane, Paisley$00/5021$ Mcleod, A $00/4647$ Smith, J $01/4670$ Owen, M $01/4765$ Smith, J $00/5021$ Mcleod, A $00/4647$ Smith, J$$Identify the functional dependencies represented in this relation and demonstrate the process of normalizing this relation into $3$NF relations.