Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Systems and Database Design Assignment: Normalisation and E - R Modelling Assignment Marks: Marked out of 2 0 , worth 2 0 % of unit

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:00AM
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 CSG1207 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 PER1 WA State 3
1 Boris Markson 53 more things to do
in zero gravity
CSXPNG Pinguino VIC1 Victoria Park 2
2 Terbert
Humperdinkle
How to train a skunk
for fun and profit
SCIIND Indigo PER2 City of Perth 2
3 Agnes
Finkleworth
The Bantworth
Chronicals
CSXPNG Pinguino PER2 City of Perth 1
3 Agnes
Finkleworth
The Bantworth
Chronicals
CSXPNG Pinguino VIC1 Victoria Park 1
3 Agnes
Finkleworth
The Bantworth
Chronicals
CSXPNG Pinguino JOO1 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. R1=(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 0NF,1NF,2NF and 3NF. State any assumptions you make. Use relational symbolic notation as indicated in
the second lecture, and name the relations upon reaching 3NF.
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 0NF.
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 CSG1207 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 0NF,1NF,2NF and 3NF. State any assumptions you make. Use relational symbolic notation as indicated in
the second lecture, and name the relations upon reaching 3NF.
Include a physical E-R diagram of your normalised results. Remember to state any assumptions regarding the
cardinality of the relationshi

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Big Data Systems A 360-degree Approach

Authors: Jawwad ShamsiMuhammad Khojaye

1st Edition

0429531575, 9780429531576

More Books

Students also viewed these Databases questions

Question

=+What is your personal mission statement?

Answered: 1 week ago