1. General Information
The purpose of this assignment is to provide you with experience in analyzing, designing and implementing a solution for a university parking office. Your solution should be implemented as a program for a database system using Microsoft Access.
This assignment will help you to understand the nature and purpose of database analysis, design and implementation. It offers you experience in managing a technical database project.
This assignment is to be attempted by groups of 4 to 5 students. Each group is collectively responsible for both the submission and the outcome. Individual efforts will not be marked. There are no restrictions on the use of word processors or similar tools for the production of 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.
2. Problem Description - The University Parking Office Case Study
University parking office requires you to design and implement 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 university parking office database followed by examples of query transactions that should be supported by the database.
University parking office stores the details of each student and staff of the university. 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 their vehicle registration number, vehicle type, make and model. 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 their vehicle registration number, vehicle type, make and model.
Student information stored for a car park permit relates to those currently parking in university car parks and those on the waiting list. 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 university car parks provide general parking and disabled parking. University parking office offers reserved car parking. These are fully covered car parks. Reserved car parks provide reserved parking and disabled parking. 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 identifies each car park space in a car park.
A student or staff of university 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 university parking office 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 or staff wishes to start using university car parks) and the end date (i.e the last day a student or staff is allowed to use university car parks).
At the start of each semester, each student and staff 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, type of car parking requested and the staff number of 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 university parking office car parking that processed the payment, amount and student number for student that made the payment or staff number for the staff that made the payment. University car park office offers several types car parks with different prices. Car park membership price are $800 for all staff and $600 for students per year. There are weekly memberships. The details and cost of memberships are:
Car park ID Membership type Cost (staff) Cost (student) Car park type
M1 6 Months $800 $400 General
M2 Weekly $20 $10 General
M3 12 Months $1000 $600 Reserved
M4 Weekly $40 $30 Reserved
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 car park 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. The details of all payments made by each visitor including their vehicle registration are stored in university car park office.
Disabled car parking fees are the same as general parking fees. However the person that needs to use disabled parking needs to provide a certificate from their doctor indicating that he/she is required to use disabled parking to the university parking office. The doctor certificate is scanned and stored in the university car park office database with details of the person that is permitted to park in disable car parking spaces.
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. The university parking office stores information about the cleaning history of their car parks. The details recorded about cleaning services include, car park number, date of cleaning, staff number of staff that performed the cleaning.
The university parking office has special parking inspectors. These inspectors regularly check the car park and issue fines (i.e. $89.00 for each fine) when staff or students have park in incorrect locations in a car park or disobey university car parking rules. When a staff or student has a fine, then she/he needs to pay the fine within 14 days of the date of the issue of the fine. The details of each fine issued by each special inspector are recorded in the university parking office database. These details are: Fine number, student/staff car park permit number, vehicle registration number, inspector number (who issued the fine), reason for issuing fine, date and time when the fine was issued.
For students enrolled in Database Design G, you are required to have the following details into your design and implementation of your assignment:
The university parking office rents car parks on Sundays to general public. Car parks can be hired to any member of public who wants to organise a Sunday Market. The cost of hiring of a car park is $300 per day. The details of the person hiring a car park for Sunday market are stored in university parking office database. These details are: parking number, date of hire and detail of the person who hired the car park. These details are: person number, person first and last name, address and telephone number.
The details of all payment for Sunday car park hire is stored in university car park office. These details are payment number, payment date, the method of payment (check, cash, Visa card), person number, car park number (i.e. the car park that was hired).
3. Requirements:
University parking office database program developed by your team should have well designed screens that are easy to use, understand and follow by its staff. A novice user should be able to use the university parking office database application (program) by reading instruction on the computer screen of your program. For security reasons the views needs to be created for your database so that staff and students can not access directly the database tables etc (Hint: use switchboard in MS Access to achieve this task).
The university parking office staff should be able to use the database system to:
1. Display a report listing the names and numbers of all staff and students with the details of their car park lease agreements.
2. Display the details of the total car park fees paid by all students.
3. Display the details of the total car park fees paid by all staff.
4. Display details of students that have not paid their car park invoices.
5. Display details of staff that have not paid their car park invoices.
6. Display details of students that have paid their invoices.
7. Display details of staff that have paid their invoices.
8. Display the details of inspections where the car park was found to be in an unsatisfactory condition.
9. Display details of students using the university car parks.
10. Display details of all staff using the university car parks.
11. Check details of all car parks that are book for Sunday market.
12. Check the income of all car parks that are booked for Sunday market so far.
13. Check all car park fines issued that are not paid.
The students should be able to use the university parking office database to:
1. Check the telephone number of each staff and their office number.
2. Check car parking fees per year and per week.