Car Rental Company Database System
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
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
