Task Table 1:

Task

Table 1: GRADE REPORT relation 

Student_IDStudent_NameCampus_AddressMajorCourse_IDCourse_titleInstructor_NameInstructor_LocationGrade16830058WilliamsAlburyBITITC114Database SysPeterB104HD16830058WilliamsAlburyBITITC211Sys AnalysisJohnB115D543291073BakerWaggaAcctITC200Database MgtDavidH310D543291073BakerWaggaAcctAcc11Fund AcctWilliamA120C543291073BakerWaggaAcctMkg21Intro MktMikeA112P

Using above table

1. Draw a dependency diagram to show the functional dependencies in the relation 

2. Decompose the GRADE REPORT relation into a set of 3NF relations

3. Draw a relational schema for your 3NF relations and show the referential integrity constraints

NOTE

For guidance on how to normalise database relations from a dependency diagram, watch this very short YouTube video: https://www.youtube.com/watch?v=vkUyuLLgmwQ 

Manually drawing the dependency diagram, or the set of 3NF relations or the relational schema is NOT acceptable. 

Rationale

This assessment aligns with the following subject outcomes:

• be able to analyse a database design and apply normalisation theory and techniques

Marking criteria

Each task is worth 5 marks. The marking criteria for this assignment is: 

TaskHDDICRPSDraw a dependency diagram to show the functional dependencies in the relationThe dependency diagram accurately identifies and correctly shows all PKs and all functional, partial, and transitive dependenciesThe dependency diagram accurately identifies and correctly shows all PKs and most functional, partial, and transitive dependenciesThe dependency diagram identifies and shows most PKs and some functional, partial, and transitive dependenciesThe dependency diagram identifies and shows few PKs and few functional, partial, and transitive dependenciesDecompose GRADE REPORT into a set of 3NF relationsThe set of 3NF relations accurately identifies and correctly shows all PKs and all the relevant attributesThe set of 3NF relations accurately identifies and correctly shows all PKs and most of the relevant attributesThe set of 3NF relations identifies and shows most PKs and some of the relevant attributesThe set of 3NF relations identifies and shows few PKs and few of the relevant attributesDraw a relational schema for your 3NF relations and show the referential integrity constraintsThe relational schema accurately identifies and correctly shows all of the referential integrity constraintsThe relational schema accurately identifies and correctly shows most of the referential integrity constraintsThe relational schema identifies and shows some of the referential integrity constraintsThe relational schema identifies and shows few of the referential integrity constraints 

Requirements

Either save your diagrams in a PDF file or embed them in a Word document.

Don't use any other file formats

Please include your student ID, name and subject code in your submission.