Physical Model SQL Implementation for Vehicle Rental Network
-- Logical Model and SQL Implementation for Vehicle Rental Network
-- Create table for Address
CREATE TABLE Address (
AddressNumber INT PRIMARY KEY,
City VARCHAR(50),
Country VARCHAR(50),
Street VARCHAR(100)
);
-- Create table for Agency
CREATE TABLE Agency (
AgencyID INT PRIMARY KEY,
Name VARCHAR(100),
NumberOfStaff INT,
TelNumber VARCHAR(15),
AddressNumber INT,
FOREIGN KEY (AddressNumber) REFERENCES Address(AddressNumber)
);
-- Create table for Mark
CREATE TABLE Mark (
MarkID INT PRIMARY KEY,
Name VARCHAR(100),
YearFounded INT,
CountryOfOrigin VARCHAR(50)
);
-- Create table for Model
CREATE TABLE Model (
ModelID INT PRIMARY KEY,
ModelName VARCHAR(100),
YearOfRelease INT,
SeatingCapacity INT,
Transmission VARCHAR(50),
FuelType VARCHAR(50),
EngineSize DECIMAL(5, 2)
);
-- Create table for Type
CREATE TABLE Type (
TypeName VARCHAR(100) PRIMARY KEY,
Description VARCHAR(255),
BodyType VARCHAR(50),
Dimension VARCHAR(50)
);
-- Create table for Vehicle
CREATE TABLE Vehicle (
LicensePlate VARCHAR(20) PRIMARY KEY,
Location VARCHAR(100),
Condition VARCHAR(50),
AgencyID INT,
MarkID INT,
ModelID INT,
TypeName VARCHAR(100),
FOREIGN KEY (AgencyID) REFERENCES Agency(AgencyID),
FOREIGN KEY (MarkID) REFERENCES Mark(MarkID),
FOREIGN KEY (ModelID) REFERENCES Model(ModelID),
FOREIGN KEY (TypeName) REFERENCES Type(TypeName)
);
-- Create table for Customer
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
City VARCHAR(50),
PostalCode VARCHAR(20),
Country VARCHAR(50)
);
-- Create table for Rental
CREATE TABLE Rental (
RentalID INT PRIMARY KEY,
CustomerID INT,
LicensePlate VARCHAR(20),
RentalDate DATE,
ReturnDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
FOREIGN KEY (LicensePlate) REFERENCES Vehicle(LicensePlate)
);
-- Insert fake records into Address
INSERT INTO Address VALUES (1, 'New York', 'USA', '5th Avenue');
INSERT INTO Address VALUES (2, 'Los Angeles', 'USA', 'Sunset Boulevard');
-- Insert fake records into Agency
INSERT INTO Agency VALUES (1, 'Downtown Rentals', 15, '123-456-7890', 1);
INSERT INTO Agency VALUES (2, 'West Coast Rentals', 20, '987-654-3210', 2);
-- Insert fake records into Mark
INSERT INTO Mark VALUES (1, 'Toyota', 1937, 'Japan');
INSERT INTO Mark VALUES (2, 'Ford', 1903, 'USA');
-- Insert fake records into Model
INSERT INTO Model VALUES (1, 'Corolla', 2020, 5, 'Automatic', 'Gasoline', 1.8);
INSERT INTO Model VALUES (2, 'F-150', 2022, 3, 'Manual', 'Diesel', 3.5);
-- Insert fake records into Type
INSERT INTO Type VALUES ('Sedan', 'Compact car', 'Sedan', '4m x 1.8m');
INSERT INTO Type VALUES ('Truck', 'Heavy-duty vehicle', 'Truck', '6m x 2.5m');
-- Insert fake records into Vehicle
INSERT INTO Vehicle VALUES ('NY1234', 'Downtown', 'Excellent', 1, 1, 1, 'Sedan');
INSERT INTO Vehicle VALUES ('LA5678', 'Warehouse', 'Good', 2, 2, 2, 'Truck');
-- Insert fake records into Customer
INSERT INTO Customer VALUES (1, 'John', 'Doe', 'New York', '10001', 'USA');
INSERT INTO Customer VALUES (2, 'Jane', 'Smith', 'Los Angeles', '90001', 'USA');
-- Insert fake records into Rental
INSERT INTO Rental VALUES (1, 1, 'NY1234', '2023-12-01', '2023-12-10');
INSERT INTO Rental VALUES (2, 2, 'LA5678', '2023-12-05', '2023-12-12');