2. Problem Description - The University Parking Office Case Study The 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 specication for the university parking ofce database followed by examples of query transactions that should be supported by the database. The University parking ofce 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 the 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 stalt), offlce telephone number and office number, and their vehicle registration number, vehicle type, make and model. Students may purchase parking membership in general parking areas or reserved parking areas. Each university carpark at the university has a carpark number, name, location number, location address, size, capacity, type (general parking or reserved parking), and a manager, who supervises the operation of the carpark. Each university carparks provide general parking and disabled parking. Each car parking space in each carpark has a car space number, car park number, and monthly fee. The car space number and carpark number together uniquely identify each car park space in a carpark. A student or staff of the university may purchase a membership for using university carparks 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 olftce is uniquely identied using a parking lease number. The data stored on each lease includes: the lease number, duration of the lease, student name and 1 student number, 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 carparks) and the end date (i.e the last day a student or staff is allowed to use university carparks). At the stan of each semester, each student and staff are sent an invoice for a parking period based on carpark lease agreement that 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 the student that made the payment or staff number for the sta' that made the payment. The university carpark ofce offers several types of carparks with different prices. Carpark membership price is $800 for all staff and $600 for students per year. There are weekly memberships. The details and cost of memberships are given below: Cerpark ID Membership type Cost (staff) Cost (student) Cerpnrk type M1 6 Months SSIJG 3400 General M2 Weekly 820 St I) 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. Ifa visitor wants to park hither car in a university carpark then hea'she should make a payment before using university car parks. The payment for visitor parking is made at the university carpark office. The details of all payments made by each visitor including their vehicle registration are stored in the university car park office. The university parking office has special parking inspectors. These inspectors regularly check the carpark and issue fines (i.e., $100.00 for each fine] when staff or students have parked in incorrect locations in a car park or disobeyed university car parking rules. When a staff or student has a fine, then sher'he needs to pay the fine within 14 days of the date of the issue ofthe ne. The details of each fine issued by each special inspector are recorded in the university parking otftce database. These details are: Fine number, studenUstaff car park permit number, vehicle registration number, inspector number (who issued the fine), the reason for issuing the ne, and date and time when the ne was issued. The university carpark has a loyalty program. Staff and students that have a lease agreement with the university carpark ofce can join the carpark office loyalty program and receive one point for every dollar that they spend on their carpark lease. Each staff or student that joins the loyalty program receives a loyalty program number and their loyalty points are stored with their details in the university carpark office database. These details for students are: student name and student number, the total amount spent by the student for the carpark lease, date, loyalty program number, total loyalty points received, and carpark lease agreement number for that student These details for staff are: staff name and staff number, the total amount spent by the staff member for the carpark lease, date, loyalty program number, total loyalty points received, and carpark lease agreement number for that staff The loyalty points can be used to purchase car care products from the university car parking office. 2