Chat with us, powered by LiveChat PROJECT IT 403 - Essayabode

4/25/24, 12:57 PM Gemini Car Rental Company Database System This document outlines the design and implementation of a database system for a car rental company. ER Diagram: The Entity-Relationship (ER) diagram represents the conceptual schema of the database. It includes the following entities and their relationships: Customer: Attributes – CustomerID (PK), Name, Email, PhoneNumber, DateOfBirth Car: Attributes – CarID (PK), Manufacturer, Model, Year, LicensePlate, DailyRate, LocationID (FK) RentalLocation: Attributes – LocationID (PK), Name, StreetAddress, City, State, PostalCode RentalTransaction: Attributes – TransactionID (PK), CustomerID (FK), CarID (FK), LocationID (FK), StartDate, EndDate Relationships: Customer rents Car (Many-to-Many): A customer can rent multiple cars, and a car can be rented by multiple customers. This relationship is mediated by the RentalTransaction entity. Car belongs to Location (One-to-Many): A car belongs to one rental location. RentalTransaction occurs at Location (Many-to-One): A rental transaction is associated with one rental location. RentalTransaction involves Customer and Car (Many-to-Many): A rental transaction involves one customer and one car. Tables Before Normalization: We can create a single table containing all attributes from the entities and relationships: CarRentalSystem ( CustomerID int PRIMARY KEY, Name varchar(255), Email varchar(255), PhoneNumber varchar(255), DateOfBirth date, CarID int, Manufacturer varchar(255), Model varchar(255), Year int, LicensePlate varchar(255), DailyRate decimal(10,2), LocationID int, LocationName varchar(255), — Redundant attribute StreetAddress varchar(255), City varchar(255), State varchar(255), PostalCode varchar(255), TransactionID int, StartDate date, EndDate date ) https://gemini.google.com/app/a9f173d649ec5757 1/3 4/25/24, 12:57 PM Gemini This table suffers from data redundancy and anomalies. Normalization will address these issues. Tables After Normalization: To eliminate redundancy, we will decompose the table into several normalized tables: 1. Customer: (CustomerID (PK), Name, Email, PhoneNumber, DateOfBirth) 2. Car: (CarID (PK), Manufacturer, Model, Year, LicensePlate, DailyRate, LocationID (FK)) 3. RentalLocation: (LocationID (PK), Name, StreetAddress, City, State, PostalCode) 4. RentalTransaction: (TransactionID (PK), CustomerID (FK), CarID (FK), LocationID (FK), StartDate, EndDate) Implementation and Population: The normalized tables can be created in a database management system like MySQL. You can populate these tables with at least 10 rows of sample data for each entity. Sample Queries: 1. Customers who rented more than twice: SQL SELECT c.Name FROM Customer c INNER JOIN RentalTransaction rt ON c.CustomerID = rt.CustomerID GROUP BY c.CustomerID HAVING COUNT(*) > 2; Use code with caution. con 2. Cars in Jazan with MY > 2007: SQL SELECT * FROM Car c INNER JOIN RentalLocation rl ON c.LocationID = rl.LocationID WHERE rl.City = ‘Jazan’ AND c.Year > 2007; Use code with caution. con 3. Cars rented from Jeddah with customer details: SQL https://gemini.google.com/app/a9f173d649ec5757 2/3 4/25/24, 12:57 PM Gemini SELECT c.Manufacturer, c.Model, cu.Name, cu.Email, cu.PhoneNumber FROM Customer cu INNER JOIN RentalTransaction rt ON cu.CustomerID = rt.CustomerID INNER JOIN Car c ON rt.CarID = c.CarID INNER JOIN RentalLocation rl ON rt.LocationID = rl.LocationID WHERE rl.City = ‘Jeddah’; Use code with caution. con 4. Cars rented more than three times: SQL SELECT c.Manufacturer, c.Model FROM Car c INNER JOIN RentalTransaction rt ON c.CarID = rt.CarID GROUP BY c.CarID HAVING COUNT(*) > 3; Use code with caution. con 5. Cars rented for more than five days with customer details: SQL SELECT c.Manufacturer, c.Model, cu.Name, cu.Email, cu.PhoneNumber FROM Customer cu INNER JOIN RentalTransaction rt ON cu.CustomerID = rt.CustomerID INNER JOIN Car c ON rt.CarID = c.CarID WHERE https://gemini.google.com/app/a9f173d649ec5757 3/3