Reference no: EM132592423

Smart Parking Meter

Problem Description:

Today, street parking meters are none intelligent devices. You are supposed to pay when you park your car on metered streets. However, if you don't pay then you may not get caught as it depends on the parking enforcement employee to go around and check who paid and who didn't. Imagine how much money the city is losing from those who park but don't pay.

The city though of using technology to create smart meters that will use a camera inside the meter. The camera will capture the car plate number and time of parking. Here are the city business rules:

• The cameras will be connected to the city's central computing system.
• Hourly parking rate= $1
• Only credit or debit cards can be used as methods of payments. Cash not accepted.
• If a customer over runs his payment time, then a ticket will be issued automatically with the overrun time plus a penalty of 25%.
• If a customer park but doesn't pay, a ticket will be issued based on the following formula: hourly Parking time/24 multiplied by 5.
• If a car is left beyond 12PM the system sends a message to one of the city's towing companies to come and remove it
• Paid tickets will be deleted from the system within one month from receiving the payment
• Tickets will be sent to violators using Canada post.

The city hired your team to design and implement the smart meters database. You decided to apply what you learned in INFO 2312 to implement the city's database requirements.

Deliverables:

1. Clear and documented Entity Relationship diagrams
2. Database creation and data population. (Sql code and data. Should be fully organized, described, and commented). (Note: All tickets status will be unpaid when entered in the DB)
3. Provide the following: (All Sql code and data Should be fully organized, described, and commented)
a. List parking violations sorted by date and time. violation data includes the violator information such as name, address, and phone number, car plate number, violation address, violation date and time, and ticket amount. Also, the payment due date
b. List violators names and phone numbers who received the tickets but didn't pay yet
c. List number of violations categorized by the meter's street address
d. List towed cars categorized by the towing company and the meter's street address
e. List tickets payed before the due date
f. List tickets payed after the due date
g. List tickets not paid
h. List the tickets that are older than one month after receiving the payment then delete them then run the code again to verify.
4. Presentation (structure will be provided later)
5. A sheet that describes each team member participation in the project deliverables