A car rental service provides cars for rent to customers for a period of time. Cars are kept at rental stations which are situated at fixed locations.
System Functional Requirements
Customers provide their name, age and email to register into the system and are given a unique id. Once registered, a customer can make reservations for a car from a particular location giving both the rental date and time and the return date and time. Each reservation has a unique id and a status. Before the rental date and time, the value of the status is confirmed, once the customer picks up a car, the status is changed to on-going. If the customer does not pick up the car or cancels the reservation before the rental data, the status is changed to cancelled. After the car is returned the status is changed to complete. Each reservation (that is not cancelled) involves exactly one car which has a make, model, and a unique license number. Every rental station has a unique code, along with a name and a location. Once a reservation is complete an invoice is generated and sent to the customer. The invoice has a number (which together with the reservation id is unique), a date, an amount, and a status (unpaid/paid). If the invoice is not paid within a certain period a new invoice for the same reservation, but with a new number, is generated with an amount that includes 10% penalty).
Design an ER diagram for the car rental service using the (min, max) cardinality constraints and diagramming conventions used in the course slides. The design should be for an operational database to help the company run its business. For accounting, the company needs to keep a record of all past and current reservations and invoices. Consider what should be modeled as data values and therefore is not in the ER diagram) and what should be part of the schema. The data can change as new reservations are made and time passes, but your schema should be fixed and able to represent the data mentioned. You may attach a written explanation of your design decisions and how they capture the information above.
Create a relational schema that represents the information in the ER diagram below. Write all the create table statements and constraints needed to enforce the constraints in the ER diagram. If any of the constraints can not be expressed in SQL using your design or a different design with primary keys, foreign keys, unique constraints or non-null constraints, explain why. For such constraints, write an SQL query that returns 'True if the constraint is satisfied on the database, and 'False' otherwise. Your solution must run in postgres.
ER Diagram: see image.