The purpose of this database is to maintain the data of a car rental service. It would contain data related to rental cars, rental locations, customers, customer service, memberships, bookings, payment information, maintenance and insurance data. The database will be used for administrative and analysis purposes.
● Allow customers to book rental cars and manage their bookings
● Rental service should be able to serve the customers with the nearest available cars for a specified time frame.
● Facilitate the rental agency to buy cars from different vendors.
● Determine maintenance of cars based on usage patterns
● Generate reports for business to analyze revenue which are aggregated over membership types, car types, booking times etc..
● Customer can only look at the available cars after authentication
● Customers cannot book a car without verification and a valid membership.
● Customers can have only one active booking.
● Customer can have multiple(max 3) booking without overlap
● Rental Cars cannot have multiple active bookings at any point of time.
● Rental Cars should be picked up and dropped off at the same location.
● Rental Cars cannot be rented without taking insurance.
● Customers will incur late fees if the booking goes over the end time.
● Car maintenance records must be maintained.
● An employee can have only one customer service request associated with him/her.
● A car entry cannot be created without a valid vendor ID
● Use Crow's Foot Notation.
● Specify the primary key fields in each table by specifying PK beside the fields. Draw a line between the fields of each table to show the relationships between each table. This line should be pointed directly to the fields in each table that are used to form the relationship.
● Specify which table is on the one side of the relationship by placing a one next to the field where the line starts
● Specify which table is on the many sides of the relationship by placing a crow's feet symbol next to the field where the line ends.
Each team will submit the ‘SQL code’ to implement the database design as well as enter a minimal amount of data (at least 10 rows for each table) using the SQL INSERT scripts, Data Import Wizard, and/or stored procedures. Specific objects to be reflected in the code include the database, tables, data types, primary and foreign keys, and views. Each team is expected to create at least 2 views (often used for reporting purposes).
The implementation must include at least two of the following three items:
- Table-level CHECK Constraints based on a function
- Computed Columns based on a function
- Column Data Encryption