Case Study - Parramatta City Council Car Park System the requirements for your project:

Stage 1: Group Practical assignment 1- conceptual and logical database design

You will analyse the case study describe above and create the conceptual and logical database design solutions. The students will work in a team of 2 to 4 people to do this stage of assignment. Answers to written assignment should be typed using a word processor and suitable diagrams to be created using suitable case tool.

Stage 2: Individual Practical assignment 2 - Physical database design and database query

In this assignment, each of you individually will be expected to use MYSQL WorkBench, MYSQL server to create a database, related dataset and write the required SQL queries. You will create the database based on the logical database design you have completed in Group Practical assignment 1.

i. List all the details of season ticket holders for each of the seven car park.

ii. List total number of car parking spaces assigned to season ticket holders and ordinary customers for each car park.

iii. List the total amount of money (payment) collected by each of the seven car park (from all payment machines) for a given day.(You may select any day.)

iv. List all records of payments for each payment machines in a car park. (You may select any ONE of the sever car parks)

v. List the records of security guard visit for a week for all car parks. (You may select any calendar week- Sunday to Saturday.)

vi. List all records of fault tickets for a selected car park for a selected week.

(You may select any calendar week- Sunday to Saturday.)

vii. List the total number of cars parked in one selected car park on every Saturday in January 2017 sort in descending (ie. List the Saturday with highest first, and the lowest last in the list. The dates for Saturday in January 2017 are 7, 14, 21, 28)

viii. list the maximum and minimum number of cars parked in each car park by ordinary customers on a day.

(You may select any day, and group by hour, and group by car park. This query allows you show the time (hour, e.g 1:00pm-2pm) when the car parks have the maximum of cars, and the time when the car parks has least number of cars)

ix. Print the details of one ordinary parking ticket: ticket no., issue date, issue time, issuing machine.

x. Print the details of one receipt shows the Council address, address of the car park, VAT number, date and amount paid.