Parramatta City Council Car Park System Case Study - Data Base Assignment

Internal Code: MAS5732

Data Base Assignment:

Entry to the Car Park: When a car approaches an entry barrier, its presence is detected by a sensor under the road surface, and a ‘Press Button’ display is flashed on the control pillar. The ordinary customer must press a button on the control pillar, and a ticket is printed and issued. The ticket must be printed within five seconds. A ‘Take Ticket’ display is flashed on the control pillar. If the car park is full, no ticket is issued, and a ‘Full’ display is flashed on the control pillar. If a vehicle leaves the car park, then the ‘Press Button’ display is activated again where there is a vehicle waiting. When the customer pulls the ticket from the control pillar, the barrier is raised. The season ticket holder does not press the button but inserts his or her season ticket into a slot on the control pillar. A check is made that the season ticket is valid for this car park and has not expired, that it is a weekday and that the season ticket holder is not recorded as having already entered this car park and not left. If all these checks are passed, then the barrier is raised. The checks must take no longer than five seconds. A record is made of the time of entry for that season ticket holder. A sensor on the other side of the barrier detects when the car has passed and the barrier is lowered. The ticket issued to each ordinary customer has a barcode on it. The barcode has a number on it and the date (ddmmyyyy) and time (hhmmss) of entry to the car park. The number, date and time of entry are also printed on the ticket in human-readable form. The details of the ticket are stored: ticket no., issue date, an issue time, issuing machine. The number of vehicles in the car park is incremented by 1 and a check is made against the capacity of the car park. If the car park is full, then a display near the entrance is switched on to say ‘Car Park Full’, and no further tickets are issued until a vehicle leaves the car park. Leaving the Car Park: When the customer drives up to the exit barrier, the car is detected by a sensor, and an ‘Insert Ticket’ display is flashed on the control pillar. The customer must insert the ticket. The barcode is read and a check is made that no more than 15 minutes have elapsed since the payment time for that ticket. If more than 15 minutes have elapsed, an intercom in the control pillar is activated and connected to the attendant in the car park office. The customer can talk to the attendant, and the attendant can view the details of the ticket on his or her computer. The attendant can activate the barrier remotely, for example, if there is a queue to get out and the customer is likely to have been reasonably delayed. If no more than 15 minutes have elapsed, the barrier is raised. A sensor on the other side of the barrier detects when the car has passed and the barrier is lowered. The number of vehicles in the car park is decremented by 1 and a check is made against the capacity of the car park. If the car park was full, then the display near the entrance is switched to say ‘Spaces’, and a check is made to see if any vehicles are waiting. If they are, then the control pillar for the first waiting vehicle is notified. If the driver of the vehicle waiting there does not press the button (for example, because they have backed out and left), then the control pillar for the next waiting vehicle is notified. At any time, the attendant can view the status of a pay station or a barrier control pillar. Once a connection is made, the status is updated every 10 seconds. Season ticket holders do not have to go to the pay station when they are ready to leave the car park; they go to the exit and insert their season ticket into a slot on the exit barrier control pillar. The barrier is raised and a record is made of the time at which the season ticket holder left. Questions:  i. List all the details of season ticket holders for each of the seven-car park. ii. List a 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. v. List the records of security guard visit for a week for all car parks. vi. List all records of fault tickets for a selected car park for a selected week. vii. List the total number of cars parked in one selected car park on every Saturday in January 2017 sort in descending viii. list the maximum and a minimum number of cars parked in each car park by ordinary customers on a day. (You may select any day, and group by an hour, and group by a 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 the least number of cars) ix. Print the details of one ordinary parking ticket: ticket no., issue date, an issue time, issuing machine. x. Print the details of one receipt shows the Council address, an address of the car park, VAT number, date and amount paid.