Systems and database design assignment

Assignment 1 -- Normalization and E-R Modelling

Objectives from Unit Outline

Use Data Modelling approaches to convert data sets into data models

Related learning outcomes

1. Identify the difference between a data set and its attributes;
2. Identify the relationships between different data sets (1:1, 1: many, many: many).
3. Describe and perform a normalization process (e.g., ONF ->3NF);
4. Describe types of data anomalies;
5. Identify key attributes and dependencies (i.e., partially & transitive functional dependencies) among attributes of datasets;
6. Draw entity-relationship diagrams from problem statements or forms;

Background Information

The logical model of a relational database consists of entities and relationships between them. The physical database is made up of tables, each of which corresponds to an entity in the logical model. The tables in the physical database are referenced with each other by foreign keys, each of which corresponds to a relationship in the logical model.

A physical database may suffer from types of anomalies. To remove anomalies, datasets (i.e., the logical structures of a table in physical database or of an entity in logical model of the database) are normalised to Normal Forms (e.g., ONF, 1NF, 2NF, 3NF etc.). Normalised datasets removed functional dependencies between data attributes (columns) thus have good properties to be used to create tables.

This Assignment enables you to practice identifying entities and relationships between them, drawing E-R diagrams, creating data sets from problem statements and/or forms, as well as normalising data sets to 3NF. These skills are important first steps in constructing databases.

Value: 15%

Submission Instructions

• Present your assignment in hardcopy (on A4 paper) in a FLAT FILE with a clear plastic front cover (so that unit name, student name, student ID number, due date, etc. can be seen without opening the file). DO NOT SUBMIT LOOSE PAGES OR PAGES JUST STAPLED TOGETHER. Internal students should submit their assignments in the Assignment Box in the school reception by the due date. - DO NOT SEND THE ASSIGNMENT DIRECTLY TO YOUR TUTOR. Alternatively, if you like to submit electronically, you may Submit Your Coursework via email attachment to your tutor - in this case you must discuss with your tutor beforehand on how to submit and where to Submit Your Coursework
• Remember to keep a copy of your
• Your assignment must be word-processed and all diagrams developed using graphics software (however no extra marks will be awarded for colour diagrams).
• Pages must be
• You will be assessed on the presentation of your assignment, as well as its contents.
• Your attention is drawn to the university rules governing cheating and referencing. In general, cheating is the inclusion of the unacknowledged work of another person. Plagiarism (presenting other people's work and ideas as your own) will result in a zero
• The methods used for normalisation and formatting of Entity Relationship Diagrams must comply with the methods and formats as taught in this unit. Do not use methods or formats from other texts or previous units (if a conflict occurs).

Tasks

Question 1 - Normalization (25 marks)

The following is an extract from the form that is currently in use in the AutoWise Mechanical and Electrical Services Group. The form is used to report services done by one of their garages. It is also taken as an official Tax Invoice when issued to customers once their vehicles were serviced. (Assume that both amounts in the Part and Labour sections include 10 percents of GST, thus the GST amount can be calculated)

Normalize this form to a suitable database design for the AutoWise Mechanical and Electrical Services Group, clearly show the stages of 0NF, 1NF, 2NF, and 3NF. State any assumptions you make about the data shown in this table. For example, you may assume that each customer may receive only one service from a given garage on a particular day; a vehicle may have only one owner, etc.

Name your resultant datasets, and then convert the normalization result to a physical E R diagram.

Question 2 - Normalization (15 marks)

Normalise to 3NF and produce a physical E R model for one separate paper based form of your choice, preferably from one of the following areas:

• Mechanic
• Hotel
• University Timetable

The form you choose must contain at least 1 (one) repeating group, and must be normalised to at least 3 (three) datasets. Show fully worked normalisation for the form (see workshop 1 solutions for example).

A brief description of the form is required. If you use only a part of the data items from the form, give your reasons of doing so. State any assumptions necessary to support your normalization process.

Attach a copy of the form (readable) with your assignment. If you choose a form from Internet, print/attach the form and provide the URL of the form.

Question 3 -- Entity-relationship modelling (25 marks)

You have been asked by your local Swimming club to create a database to keep their records in. The club consists of members who actively compete in competitions and coaches, each of whom coaches one or more of the members. Competitions are held every second Thursday and the club records who competes in each race and their placing in each race. Competitions may occur at one of three venues (Hyde Park Pool

• 8 lanes, Beatty Park Pool - 10 Lanes, The Super drome - 12 Lanes). All members have a unique membership number, Name, Address, DOB and other standard contact details. Competitions have a unique name and notes are kept on the temperature and wind speed and direction. Some initial assumptions are:

• A member is only ever assigned one coach
• There are 10 15 races per competition
• Races have a race number which is unique only within each competition

On the basis of the above description, draw a suitable logical Entity Relationship (ER) diagram, and convert it to a physical one. Identify entities and their attributes, relationships and their types, and show clearly the primary / foreign key attributes.

Question 4 - Advanced Entity-relationship modelling (35 marks)

You are required by the director of the University Accommodation Office (UAO) to design a database to assist with the administration of the office. The requirements collection and analysis phase of the database design process based on the director's view has provided the following requirement specification for the UAO database.

• The data stored on each student includes the student number, name (first and last name), home address (street, city/town, postcode, country), date of birth, sex, category of student (e.g., first year undergraduate (1UG), postgraduate (PG), etc.), nationality, smoker (yes or no), special needs, any additional comments, current status (placed/waiting), and what course the student is studying on. The student information stored relates to those currently renting a room and those on the waiting
• Students may rent a room in a university owned student village or student flat. Each student village has a name, address, phone number, and a village manager who supervises the operation of the village. The villages provide only single rooms, which have a room number, place number, and weekly rent rate. The place number uniquely identifies each room in all the villages controlled by the UAO and is used when renting a room to a
• The UAO also offers student flats. These flats are fully furnished and provide single room accommodation for groups of 3, 4, or 5 students. The information held on student flats includes a flat number, address, and the number of single bedrooms available in each flat. The flat number uniquely identifies each flat. Each bedroom in a flat has a weekly rent rate, a room number, and a place number. The place number uniquely identifies each room available in all student flats and is used when renting a room to a student.
• A student may rent a room in a student village or student flat for various periods of time. New lease agreements are negotiated at the start of each academic year with a minimum rental period of one semester (15 weeks) and a maximum rental period of one year, which includes Semesters 1, 2, and the Summer Semester. Each individual lease agreement between a student and the UAO is uniquely identified using a lease number. The data stored on each lease includes the lease number, duration of the lease (given as semesters), name, and student number, place number, room number, address details of the student village or student flat, the date the student wishes to enter the room, and the date the student wishes to leave the room (if known).
• Student flats are inspected by staff on a regular basis to ensure that the accommodation is well maintained. The information recorded for each inspection is the name of the member of staff who carried out the inspection, the date of inspection, an indication of whether the property was found to be in a satisfactory condition (yes or no), and any additional
• Some information is also held on members of staff on the UAO and includes the staff number, name (first and last name), home address (street, city/town, postcode), date of birth, sex, position (e.g., Village Manager, Administrative Assistant, Cleaner), and location (e.g., UAO or Village).
• When a student joins the University he or she is assigned to a member of staff who acts as his or her Advisor of studies. The Advisor is responsible for monitoring the student's welfare and academic progress. The data held on a student's Advisor includes her/his full name, position, name of department/school, phone extension, and room
• The UAO also stores a limited amount of information on the courses run by the University including the course number, course title, course coordinator's name, phone extension, room number, and department/school name. Each student is associated with a single
• Whenever possible, information on a student's next of kin is stored which includes the name, relationship, address (street, city/town, postcode), and contact telephone

Based on the above requirements, you are required to create an Advanced Entity- Relationship model to represent the data requirements of the UAO case study. Develop the model using the following the steps (state any assumptions necessary to support your design):

• Identify
• Identify relationships and their
• Determine primary/foreign key and any other
• Specialize / generalize entity types, i.e., determine super/subtypes (where appropriate).
• Draw the ER diagram (both logical and physical).