Systems and Database Design

Assignment $1$: Normalisation and E$-$R Modelling

Assignment Marks: Not Applicable, Exemplar Only $($normally worth $20\%$ of unit$)$

Due Date: Not Applicable, Exemplar Only $($normally due at start of week $6)$

Background Information

This assignment tests your knowledge and understanding of the two database design techniques we

have covered in this unit $$ Normalisation and Entity$-$Relationship Modelling. Both techniques aim to

identify and organise a set of data in an efficient manner that minimises redundancy and establishes

the structure of related groups of data. These are important first steps in constructing databases.

The assignment consists of four tasks, focusing on normalisation and entity$-$relationship modelling.

Attempt all questions, and be sure to show all relevant stages$/$working and state any assumptions.

The presentation of your solutions$/$document and use of correct notation is taken into consideration.

A Note Regarding Assumptions

It is not possible to define every single aspect of all systems in the assignment brief, nor is it beneficial

to attempt this. Part of the process of completing the assignment tasks is making $($and declaring$)$

assumptions that define any ambiguous or unspecified details in the scenarios. It is up to you to make

these assumptions, but feel free to discuss them with your tutor if you are uncertain of their validity.

The tutorial of Module $1($recording available on unit site$)$ discusses assumptions.

There is no $$universally correct$$ set of assumptions for the tasks. Think about the problem, and make

assumptions as you encounter the need for them. All assumptions should affect or be

enforced$/$implemented by your database design, and they should not contradict something that is

specified in the assignment brief. Your assumptions are unlikely to have a major impact upon the task

or the resulting database structure $$ if they do$,$ seek advice from your tutor.

The most common assumptions are likely to regard identifying irrelevant or derived data $($Tasks $1$ and

$2),$ or the cardinality of relationships between entities $($all tasks$).$ Some assumptions may make a task

easier, but avoid always making assumptions that make the task easier, as this is a questionable

practise and is likely result in a solutions that do not demonstrate a deep understanding. Always

remember to clearly state all assumptions you make.

Exemplar CSG$1207$ Assignment Page $2$

Task $1$ Normalisation $1(5$ marks$)$

The table below shows part of a spreadsheet used to keep track of student enrolment in units.

Student Number Student Name Unit Code Unit Name

$0972343$ Eric Cartman CSG$1207$ Systems & Database Design

$0972343$ Eric Cartman CSI$2441$ Application Development

$2013442$ Stan Marsh CSI$2441$ Application Development

$0982342$ Kyle Broflowski CSG$1207$ Systems & Database Design

$0982342$ Kyle Broflowski CSI$2441$ Application Development

A student can be enrolled in many units and a unit can have many students enrolled in it$.$

Normalise this data to the third normal form, clearly showing the stages of $0$NF$,1$NF$,2$NF and $3$NF$.$

State any assumptions you make. Use relational symbolic notation as indicated in the second lecture,

and name your resultant data sets upon reaching $3$NF$.$

Take heed of the following tips and reminders:

$$ There are several valid ways to normalise this data, leading to almost identical solutions.

$$ This scenario only allows for one repeating group, i$.$e$.$ R$1=($Outer Group $\{$Repeating Group$\}).$

Not all groups of logically related attributes will begin in its own repeating group, e$.$g$.$

A repeating group may contain attributes that will later be split into different relations.

Include a physical E$-$R diagram of your normalised results. Remember to state any assumptions

regarding the cardinality of the relationships in the diagram.