Car Rental Database

CREATE TABLE categories (
id INT NOT NULL UNIQUE AUTO_INCREMENT PRIMARY KEY,
category VARCHAR(255) NOT NULL UNIQUE,
daily_rate DOUBLE(6,2),
weekly_rate DOUBLE(6,2),
monthly_rate DOUBLE(6,2),
weekend_rate DOUBLE(6,2)
);

CREATE TABLE cars (
id INT NOT NULL UNIQUE AUTO_INCREMENT PRIMARY KEY,
plate_number VARCHAR(255) NOT NULL UNIQUE,
make VARCHAR(255) NOT NULL,
model VARCHAR(255) NOT NULL,
car_year YEAR NOT NULL,
category_id INT NOT NULL,
doors INT NOT NULL,
picture BLOB,
car_condition VARCHAR(255),
available VARCHAR(255)
);

CREATE TABLE employees (
id INT NOT NULL UNIQUE AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
title VARCHAR(255) NOT NULL,
notes VARCHAR(255)
);

CREATE TABLE customers (
id INT NOT NULL UNIQUE AUTO_INCREMENT PRIMARY KEY,
driver_licence_number INT NOT NULL UNIQUE,
full_name VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL,
city VARCHAR(255) NOT NULL,
zip_code INT NOT NULL,
notes VARCHAR(255)
);

CREATE TABLE rental_orders (
id INT NOT NULL UNIQUE AUTO_INCREMENT PRIMARY KEY,
employee_id INT NOT NULL UNIQUE,
customer_id INT NOT NULL UNIQUE,
car_id INT NOT NULL UNIQUE,
car_condition VARCHAR(255),
tank_level VARCHAR(255),
kilometrage_start DOUBLE(6,2),
kilometrage_end DOUBLE(6,2),
total_kilometrage DOUBLE(6,2),
start_date DATE NOT NULL,
end_date DATE NOT NULL,
total_days INT,
rate_applied DOUBLE(6,2),
tax_rate DOUBLE(6,2),
order_status VARCHAR(255),
notes VARCHAR(255)
);

INSERT INTO categories (category,daily_rate,weekly_rate,monthly_rate,weekend_rate)
VALUES
("car" , 10.2 , 80.5 , 300.4 , 10.1),
("bus" , 10.2 , 80.5 , 300.4 , 10.1),
("truck" , 10.2 , 80.5 , 300.4 , 10.1);

INSERT INTO cars (plate_number,make,model,car_year,category_id,doors,picture,car_condition,available)
VALUES
("ST5848SK" , "VW" , "Passat" , "1996" , 1 , 4 , "Very tough car" , "used car","Yes"),
("ST4383NN" , "BMW" , "318" , "1994" , 1 , 4 , "Very tough car" , "used car", "Yes"),
("A9536II" , "TOYOTA" , "COROLLA" , "2008" , 1 , 4 , "Very tough car" , "used car" ,"Yes");

INSERT INTO employees (first_name,last_name,title,notes)
VALUES
("IVAN" , "DOBKOV" , "CHIEF DEPARTMENT" , "VERY TALENTED PERSON"),
("ANTONII" , "KOSEV" , "CHIEF DEPARTMENT" , "VERY TALENTED PERSON"),
("BOYAN" , "JELQZKOV" , "CHIEF DEPARTMENT" , "VERY TALENTED PERSON");

INSERT INTO customers (driver_licence_number,full_name,address,city,zip_code,notes)
VALUES
(255238156 , "Jechko Jechkov" , "Izgrev, 9-5-4-desen" , "Burgas" , 8000 , "Top Client"),
(102011085 , "Bat Bogi" , "Izgrev, 9-6-1-lqv" , "Burgas" , 8000 , "Top Client"),
(123456987 , "Iovkitch Nikolova" , "Izgrev, 29-1-6-desen" , "Burgas" , 8000 , "Top Client");

INSERT INTO rental_orders (employee_id,customer_id,car_id,car_condition,tank_level,kilometrage_start,kilometrage_end,total_kilometrage,start_date,end_date,total_days,rate_applied,tax_rate,order_status,notes)
VALUES
(900428 , 7549 , 123 , "Poor Condition" , "Full" , 123.500 , 150.000 , 27.000 , "2024-01-13" , "2024-01-20" , 7 , 6.7 , 70.00 , "finalized" , "the car was in good shape and the customer was polite"),
(900921 , 7743 , 321 , "Poor Condition" , "Full" , 123.500 , 150.000 , 27.000 , "2024-01-13" , "2024-01-20" , 7 , 6.7 , 70.00 , "finalized" , "the car was in good shape and the customer was polite"),
(921117 , 6551 , 456 , "Poor Condition" , "Full" , 123.500 , 150.000 , 27.000 , "2024-01-13" , "2024-01-20" , 7 , 6.7 , 70.00 , "finalized" , "the car was in good shape and the customer was polite");