Systems and Database Design

Assignment: Normalisation and E$-$R Modelling

Assignment Marks: Marked out of $20,$ worth $20\%$ of unit

Due Date: $29$ December $2023,9$:$00$AM

Background Information

This assignment tests your knowledge and understanding of two of the concepts we have covered in this unit $$

Normalisation and Entity$-$Relationship Modelling. Both processes aim to organise and represent 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 are also considered.

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 or repeat 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.

Your assumptions should give context to your solutions, making it clear to staff that you have considered certain

aspects of the scenarios and decided upon how to approach them.

Summer School, $2023$ CSG$1207$ Assignment Page $1$

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

The table below shows part of a spreadsheet used by libraries to keep track of their stock of books.

BookId Author Title PublisherId Publisher LibraryCode LibraryName Stock

$1$ Boris Markson $53$ more things to do

in zero gravity

CSXPNG Pinguino PER$1$ WA State $3$

$1$ Boris Markson $53$ more things to do

in zero gravity

CSXPNG Pinguino VIC$1$ Victoria Park $2$

$2$ Terbert

Humperdinkle

How to train a skunk

for fun and profit

SCIIND Indigo PER$2$ City of Perth $2$

$3$ Agnes

Finkleworth

The Bantworth

Chronicals

CSXPNG Pinguino PER$2$ City of Perth $1$

$3$ Agnes

Finkleworth

The Bantworth

Chronicals

CSXPNG Pinguino VIC$1$ Victoria Park $1$

$3$ Agnes

Finkleworth

The Bantworth

Chronicals

CSXPNG Pinguino JOO$1$ Joondalup $3$

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, i$.$e$.$

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

Design a relational database for this scenario by normalising the 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 the relations upon reaching $3$NF$.$

Hint: You can$/$should introduce auto$-$incrementing ID columns to serve as the primary key of a relation if a suitable

attribute does not exist in the data. Remember to state assumptions and include them from $0$NF$.$

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

cardinality of the relationships in the diagram.

Summer School, $2023$ CSG$1207$ Assignment Page $2$

Task $2$ Normalisation $2(5$ marks$)$

The form below depicts an invoice for a purchase from a fast food store.

Design a relational database for this scenario by normalising the 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 the relations upon reaching $3$NF$.$

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

cardinality of the relationshi