University of Canberra

Faculty of Science and Technology                                                    Semester 2, 2020

Database Design 5915 and Database Design G 6672

Assignment 1

This assignment is worth 30 marks constituting 15% of the total marks for this unit.

Due date: Friday Week 7 of Semester 2 2020 at 11:55pm

1. General Information

The purpose of this assignment is to provide you with experience in analysing and designing a database for a given problem. It will help you to understand the nature and purpose of database analysis and design. This assignment is an individual assignment. There are no restrictions on the use of word processors or similar tools to produce submissions for this assignment.

Be sure to maintain regular back-ups for any models or material prepared with the aid of software. Loss of files will not be accepted as an excuse for non-completion of this assignment. Submit your assignment to the Canvas website of this subject. Marked assignments will be available from Canvas website of this subject.

2. Problem Description – The University Parking Office Case Study

University parking office (UPO) requires you to design a database to assist with the administration of the office. The requirements collection and analysis phase of the database design process has provided the following data requirements specification for the UPO database followed by examples of query transactions that should be supported by the database.

UPO stores the details of each student and staff of the university that have purchased a parking permit from UPO. These details for students include: student number, name (first and last name), home address (street, city, postcode), mobile phone number, email address, date of birth, gender, category of student (i.e. undergraduate or postgraduate), nationality, special needs, any additional comments, the course the student is enrolled in and student car park permit number, student car park permit start date, student car park permit end date. Staff details include: staff number, name (first and last name), home address (street, city, postcode), mobile phone number, email address, date of birth, gender, category of staff (i.e. full time staff or part time staff), office telephone number and office number, and staff car park permit number, staff car park permit start date, staff car park permit end date.

Student information stored for a car park permit relates to those who purchased a parking permit from UPO to park in university car park. UPO manages 3000 car park spaces.

Once UPO sell all 3000 car park permits, all student and staff that wish to purchase a car park permit will be put into a waiting list. The details of student and staff that wish to be in the waiting list for a car park is recorded in UPO database. These details for students in car park waiting list include: student number, name (first and last name), home address (street, city, postcode), mobile phone number, email address, date of birth, gender, category of student (i.e. undergraduate or postgraduate), nationality, special needs, any additional comments, the course the student is enrolled in. Staff details for staff in car park waiting list include: staff number, name (first and last name), home address (street, city, postcode), mobile phone number, email address, date of birth, gender, category of staff (i.e. full time staff or part time staff), office telephone number and office number.

Staff and students may purchase parking membership in general parking areas or reserved parking areas. Each university car park at the university has a car park number, name, location number, location address, size, capacity, type (general parking or reserved parking) and a manager, who supervises the operation of the car park. Each car parking space in each car park has a car space number, car park number, and monthly fee. The car space number and car park number together uniquely identify each car park space in a car park.

A student may purchase a membership for using university car parks at the university for a period of time. Parking agreements are negotiated at the start of each academic year, with a minimum rental period of one month and a maximum rental period of one year, which includes semesters 1 and 2. Each individual parking agreement between a student or staff and the UPO is uniquely identified using a parking lease number. The data stored on each lease includes: the lease number, duration of the lease, student name and student number, for students and staff name and staff number for staff, parking type and Start date (i.e the date the student wishes to start using university car parks) and the end date (i.e the last day a student is allowed to use university car parks).

At the start of each semester, each student is sent an invoice for a parking period based on car parking lease they have signed. Each invoice has a unique invoice number. The data stored about each invoice includes the invoice number, lease number, start and end date for use of parking, payment due date, student full name and student number for students, staff full name and staff number for staff and staff number of the staff that issued the invoice. Additional data is held about payments of invoices and that includes the date the invoice was paid, the method of payment (check, cash, Visa card), staff number of staff at the UPO car parking that processed the payment, amount paid and student number for the student that made the payment.

University car park office offers several types car parks with different prices. Car park membership price is $600 for students per year. There are weekly memberships. The details and cost of memberships are:

There are casual rates for visitors. Casual car park rates are $9 per day.

If a visitor wants to park his/her car in a university carpark then he/she should make a payment before using university car parks.

The payment for visitor parking is made at the university car park office.

Parking staff on a regular basis inspects each car park. This is to ensure that the car parks are well maintained. The information recorded after each inspection is the name of the member of staff who carried out the inspection, the date of inspection, car park number, an indication of whether the car park inspected was found to be in a satisfactory condition (yes or no), and any additional comments.

UPO has car park cleaning staff who look after the cleaning of car parks on weekly basis. The information recorded after each car park cleaning is the name of the member of cleaner who carried out the car park cleaning, the date and time of cleaning, car park number, and any additional comments made by the UPO cleaning staff.

UPO has a loyalty program and student and staff that have purchase a parking permit, receive one point for every dollar that they spend at UPO for their parking permit.

Each student and staff that joins the UPO loyalty program receives a loyalty program number and their UPO loyalty points per transaction (i.e. amount of money spent for a car park permit) are stored with UPO client loyalty number in UPO database.

These details for each student include: student number, total amount spent by student at UPO, student loyalty program number, total loyalty points received.

These details for each staff include: staff number, total amount spent by staff at UPO, staff loyalty program number, total loyalty points received.

One loyalty point is given for each ten dollars spent by student or staff at UPO. Once a student or staff has 100 loyalty point then the student or staff with 100 points will receive a voucher for $10.

3. Requirement:

For the scenario above:

• Identify entity types and their attributes, including the primary keys and foreign keys (in third normal form).                                                 (20 Marks)

• Compile an E-R diagram for the above scenario based on your solution part (a) above. (state all assumptions that you have made)             (10 Marks)

Note: If you make any assumptions, they should be explained clearly.

Submit the list of your entities and attributes including primary keys of your solution with its E-R diagram and all assumptions to Canvas site of Database Design G (6672)/Database Design (5915) on the due date and time specified above.

The first page of your assignment should include the following information:

Student Name:

Student ID:

Assignment Name: Assignment 1

Tutor name:

Tutorial day and time: