CSG1207/CSI5135, Semester 1, 2016 Assignment 1 Page 1 CSG1207/CSI5135 Systems and Database Design Assignment 1: Normalisation and E-R Modelling Assignment Marks: Marked out of 40, (20% of unit)

Due Date: 4 April 2016, 9:00AM Background Information This assignment tests your knowledge and understanding of the two data modelling 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.

A small amount of marks are dedicated to presentation, correct notation and advanced solutions. 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 grey areas 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. 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. 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

an assignment that does not demonstrate a deep understanding. Always remember to clearly state all assumptions you make. The most common assumptions are likely to regard irrelevant or derived data (Tasks 1 and 2) or the

cardinality of relationships between entities (Tasks 3 and 4). CSG1207/CSI5135, Semester 1, 2016 Assignment 1 Page 2

Task 1 – Normalisation (9 marks) The table below shows part of a spreadsheet used to store details of music albums. Each row of the table represents an album by an artist.

Album ID Album Name Release Year Artist ID Artist Name Country Genre ID Genre Name 1254 Eternal Nightcap 1997 210 The Whitlams Australia 24 Indie Rock 1255 Discovery 2001 114 Daft Punk France 21 Electronic

1256 Tallahassee 2002 247 The Mountain Goats USA 25 Folk Rock 1257 Again 2003 351 Pnau Australia 21 Electronic 1258 The Sunset Tree 2005 247 The Mountain Goats USA 25 Folk Rock 1259 Random Access

Memories 2013 114 Daft Punk France 21 Electronic You have the following extra information about the scenario:  The "Country" column contains the country of origin of the artist who released the album.

 In this spreadsheet/scenario, an album is only associated with one artist and one genre. Normalise this table 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 your resultant data sets upon reaching 3NF. There are several correct ways to normalise this data, leading to almost identical solutions. CSI5135 Additional Requirement If you are in CSI5135, the following additional requirements apply. If you are in CSG1207, you do not need to do this (but you are welcome and encouraged to do so if you want).

Once you have normalised the table to 3NF and named your data sets, convert your results into a physical E-R diagram. Remember to indicate all cardinality, attributes, primary and foreign keys. Remember to state any assumptions that are relevant to the E-R diagram. CSG1207/CSI5135, Semester 1, 2016 Assignment 1 Page 3

Task 2 – Advanced Normalisation (9 marks) The form below depicts an invoice for an order from a take-away restaurant. Normalise this form 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 your resultant data sets upon reaching 3NF.

It is likely that you will need to make an assumption or two regarding the customer details and how they relate to the order. CSI5135 Additional Requirement If you are in CSI5135, the following additional requirements apply. If you are in CSG1207, you do not

need to do this (but you are welcome and encouraged to do so if you want). Once you have normalised the table to 3NF and named your data sets, convert your results into a

physical E-R diagram. Remember to indicate all cardinality, attributes, primary and foreign keys. Remember to state any assumptions that are relevant to the E-R diagram. CSG1207/CSI5135, Semester 1, 2016 Assignment 1 Page 4 Task 3 – Entity-Relationship Modelling (9 marks)

You have been hired to design a database system for an accounting firm named "The Firm" that has multiple branches. You have the following information about the way the accounting firm operates:

 Details of The Firm's branches must be recorded. This includes a branch ID number, the name of the branch (e.g. "The Firm - Joondalup"), its address and phone number. Many

accountants can work at each branch.  The Firm has four pay levels for their accountants. The pay levels are detailed below: Pay Level ID Pay Level Name Annual Pay Expected Experience

1 Trainee $45,000 0 2 Junior Accountant $60,000 1 3 Accountant $75,000 4

4 Senior Accountant $95,000 8  The "Expected Experience" attribute represents the minimum number of years that an accountant should have been working at the firm before receiving that pay level.  The Firm stores the following details about the accountants that work in their branches: A staff ID number, their first name and last name (two separate attributes), their phone number and their hire date (the date they started working for The Firm). Use foreign keys to

identify the branch they work at and their pay level.  The Firm stores the following details about their clients: Their tax file number, their first

name and last name (two separate attributes), their phone number and their email address. For a person's details to be stored as a client, they must have at least one job (see below).  The client details can also include a foreign key to identify their preferred

accountant, however not all clients have a preferred accountant.  Whenever a client goes to The Firm to have some work done by an accountant this is known as a job. The Firm stores the following details about jobs: A job ID number, foreign keys to identify the accountant and the client involved, the date/time of the job, how much the client was charged for the job and whether or not the client has paid for the job ("Y" or "N").

Based on the details above, you are required to draw both a logical E-R diagram for this database and then a corresponding physical E-R diagram. Clearly show all cardinality, primary and foreign

keys, attributes and relationships as appropriate. Adhere to the distinctions between logical and physical E-R diagrams defined in Lecture 3. Use enhanced E-R model notation where/if appropriate.

Remember to state any assumptions you make. CSG1207/CSI5135, Semester 1, 2016 Assignment 1 Page 5 Task 4 – Advanced Entity-Relationship Modelling (9 marks)

The accounting firm from Task 3 now requires you to update and expand the previous database you designed for them, in order to record some additional information. You must update and expand your E-R diagrams from Task 3 in order to incorporate these new requirements:

 Some accountants mentor other accountants. The database must record the identity of an accountant's mentor, if they have one. While an accountant can only have one mentor, they

may be the mentor of multiple accountants.  To keep better track of the jobs they do and how they charge for them, The Firm now wants to store details of the following job types: Job Type ID Job Type Name Cost Per Minute

1 Tax Return $4.00 2 Financial Planning $5.00

3 Retirement Planning $5.00

4 Litigation Support $7.50

5 Miscellaneous $6.50  The job type of each job must be recorded using a foreign key in the job table.  The database must also record which accountants are specialised in which job types: Each accountant specialises in at least one job type, but may specialise in many of

them. Many accountants may specialise in the same job type.  Instead of storing the costs of jobs, The Firm wants to be able to calculate them using the

Cost Per Minute attribute of the job types entity. To allow for this, change the job entity:  Remove the "job cost" attribute.  Add a "job duration" attribute, representing the duration of the job in minutes. Create updated and expanded versions of your logical and physical E-R diagrams from Task 3 that

incorporate these new requirements. Completing this task will require you make changes to some existing entities, as well as adding new entities, attributes and relationships to the diagram. Ensure

that you submit separate solutions to Tasks 3 and 4 – a total of four E-R diagrams. Remember to state any assumptions you make, use enhanced E-R model notation where/if appropriate, and show cardinality on both diagrams.

Note: This database design will form the basis of your second assignment, so be sure to dedicate some time to it and take any feedback you receive into account.

CSG1207/CSI5135, Semester 1, 2016 Assignment 1 Page 6 Presentation, Notation and Advanced Solutions (4 marks) There are some marks available for presentation, using correct notation, and providing advanced solutions which demonstrate a greater effort and understanding of the concepts being tested.

Presentation simply involves the layout and formatting of your assignment – the consistent use of styles, neat and legible diagrams, ensuring your name and student number is in the document, etc. Notation must be used correctly and consistently in both your normalisation and E-R diagrams.

Some notation only applies to normalisation or E-R diagrams. When working through the normal forms, use correct numbering of relationships and use strikethrough to indicate where a relationship is eliminated / split into multiple relationships. Adhere to the notation used in the unit materials wherever possible. If unable to do so for some

reason, make sure that whatever notation you use is consistently applied and clearly represents the meaning of what you are depicting. Include a key or legend if necessary. Please submit your assignment in PDF format, and open the PDF file before submitting it to ensure

that your diagrams appear as intended. Advanced solutions can include anything from making intelligent or perceptive assumptions, to including extra (and relevant) attributes and entities, or coming up with a solution that is particularly efficient in depicting the scenario specified in the brief. There are no fixed rules or marks for advanced solutions, so do your best and it will be rewarded as deemed appropriate. If your solution deviates significantly from the brief, be sure to discuss it with your tutor before submission.

Submission of Deliverables Please ensure that your completed assignment is in PDF format, and open the PDF file before submitting it to ensure that your diagrams appear as intended. Once your assignment is complete,

submit the file to the appropriate location in the Assessments area of Blackboard. Submissions via email or hard copies are NOT permitted, unless you are specifically instructed to do

so. An assignment cover sheet is not required, but ensure that the first page of your assignment includes the unit code, assignment number/name, year and semester, your name and student number, your tutor's name, and the time and campus of your workshop session. CSG1207/CSI5135, Semester 1, 2016 Assignment 1 Page 7 Referencing, Plagiarism and Collusion

The entirety of your assignment must be your own work (unless otherwise referenced) and produced for the current instance of the unit. Any use of unreferenced content you did not create

constitutes plagiarism, and is deemed an act of academic misconduct. All assignments will be submitted to plagiarism checking software which includes previous copies of the assignment, and the work submitted by all other students in the unit.

Remember that this is an individual assignment. Never give anyone any part of your assignment – even after the due date or after results have been released. Do not work together with other students on individual assignments – helping someone by explaining errors in their code/logic or directing them to the relevant resources is appropriate, but doing it for them or showing them how you did it is not. An unacceptable level of cooperation between students on an assignment is

collusion, and is deemed an act of academic misconduct. If you are uncertain about plagiarism, collusion or referencing, simply email your tutor, lecturer or unit coordinator and ask. Assignment 1 Marking Key

Marks are allocated as follows for this assignment. Criteria Marks Allocated Task 1 - Normalisation Table normalised into suitable 3NF structure. Working shown and correct notation used.

All assumptions stated and final data sets named. Physical ERD of results (CSI5135 only). 9 Task 2 - Advanced Normalisation

Form normalised into suitable 3NF structure. Working shown and correct notation used. Assumptions stated and final data sets named. Physical ERD of results (CSI5135 only).

9

Task 3 - Entity-Relationship Modelling Specifications translated into suitable logical and physical ERDs. Keys, attributes relationships, cardinality, etc, all clearly depicted. All assumptions stated. 9 Task 4 - Advanced Entity-Relationship Modelling

Specifications translated into suitable logical and physical ERDs. Keys, attributes relationships, cardinality, etc, all clearly depicted. All assumptions stated.

9 Presentation, Notation and Advanced Solutions Assignment is well presented, uses consistent and appropriate notation, and presents

advanced solutions which demonstrate deeper understanding. 4 Total: 40 (20% of unit)