The Scenario:

Suppose we have the following data set that contains information about students, the units they are studying and the degrees that they are enrolled in:

student$\_$id first$\_$name last$\_$name date$\_$of$\_$birth degree school unit$\_$code unit$\_$name degree$\_$length

$55643$ John Smith $23/01/84$ Bachelor of Science Science and Technology SCI$100$ Science in Practice $3$

$55643$ John Smith $23/01/84$ Bachelor of Science Science and Technology COSC$110$ Introduction to Programming and the UNIX Environment $3$

$55643$ John Smith $23/01/84$ Bachelor of Science Humanities PHIL$102$ The Art of Good Thinking $3$

$76423$ Jane Doe $4/06/91$ Bachelor of Science Science and Technology COSC$100$ Introduction to Informatics $3$

$76423$ Jane Doe $4/06/91$ Bachelor of Arts Humanities PHIL$102$ The Art of Good Thinking $3$

$76423$ Jane Doe $4/06/91$ Bachelor of Arts Behavioural, Cognitive and Social Sciences GEPL$111$ Earth in Crisis? $3$

$96744$ Bob Smith $7/05/67$ Bachelor of Arts Humanities PHIL$102$ The Art of Good Thinking $3$

$96744$ Bob Smith $7/05/67$ Bachelor of Arts Humanities CLLA$101$ Introduction to Classical Languages $3$

$22342$ Alex Wise $24/09/77$ Bachelor of Computer Science Science and Technology COSC$110$ Introduction to Programming and the UNIX Environment $3$

$22342$ Alex Wise $24/09/77$ Bachelor of Computer Science Science and Technology COSC$100$ Introduction to Informatics $3$

$22342$ Alex Wise $24/09/77$ Bachelor of Computer Science Science and Technology COSC$210$ Database Management Systems $3$

$44398$ David Jackson $10/07/91$ Bachelor of Rural Science Science and Technology SCI$100$ Science in Practice $4$

$44398$ David Jackson $10/07/91$ Bachelor of Rural Science Science and Technology CHEM$110$ Chemistry $14$

$44398$ David Jackson $10/07/91$ Bachelor of Rural Science Environmental and Rural Science ECOL$100$ Ecology: Concepts and Applications $4$

Here is a comma$-$separated$-$values $($csv$)$ file of the data presented above for you to review. This can be viewed with spreadsheet or text editing software and it may be easier to analyse the data using the sort and filter functions.

Task:

Your task is to design a relational database schema for the data provided. This database schema will need to be in $3$NF$.$ To do this you should first list all the functional dependencies that are implied from data, specify the key attributes and use these to normalise the schema so that it is in $3$NF$.$

Once you have completed your relational schema, you should explain why each of the relations is in $3$NF$.$