Mbv9V5ex

1CREATE DATABASE `car_rental`;
2
3USE `car_rental`;
4
5CREATE TABLE IF NOT EXISTS `categories`(
6	`id` INT AUTO_INCREMENT PRIMARY KEY,
7	`category` VARCHAR(50) NOT NULL,
8	`daily_rate` DECIMAL,
9	`weekly_rate` DECIMAL,
10	`monthly_rate` DECIMAL,	
11	`weekend_rate` DECIMAL
12);
13
14INSERT INTO `categories` (`category`)
15	VALUES ('Van'),
16		('Sedan'),
17		('4x4');
18		
19CREATE TABLE IF NOT EXISTS `cars`(
20	`id` INT AUTO_INCREMENT PRIMARY KEY,
21	`plate_number` VARCHAR(50) NOT NULL UNIQUE,
22	`make` VARCHAR(50),
23	`model` VARCHAR(50),
24	`car_year` INT(4),	
25	`category_id` INT,
26	`doors` INT(1),
27	`picture` LONGBLOB,
28	`car_condition` VARCHAR(50) NOT NULL,
29	`available` ENUM ('true','false') NOT NULL
30);
31
32ALTER TABLE `cars`
33	ADD CONSTRAINT `FK_cars_categories` 
34	FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`);
35
36INSERT INTO `cars` (`plate_number`,`category_id`,`car_condition`,`available`)
37	VALUES ('Van123',1,'fsd',1),
38		('Sedan123',2,'dsffds',2),
39		('4x4',1,'fsdsfsd',2);
40		
41CREATE TABLE IF NOT EXISTS `employees`(
42	`id` INT AUTO_INCREMENT PRIMARY KEY,
43	`first_name` VARCHAR(50),
44	`last_name` VARCHAR(50) NOT NULL,
45	`title` VARCHAR(50),
46	`notes` TEXT
47);				
48
49INSERT INTO `employees`(`last_name`)
50	VALUES ('Ivan'),
51		('Petkan'),
52		('Pesho');
53		
54CREATE TABLE IF NOT EXISTS `customers`(
55	`id` INT AUTO_INCREMENT PRIMARY KEY,
56	`dirver_licence_number` VARCHAR(50) UNIQUE,
57	`full_name` VARCHAR(50) NOT NULL,
58	`address` VARCHAR(50),
59	`city` VARCHAR(50),
60	`zip_code` INT,
61	`notes` TEXT
62);
63
64INSERT INTO `customers`(`full_name`)
65	VALUES ('P.P'),
66		('D.D'),
67		('A.A');
68		
69CREATE TABLE IF NOT EXISTS `rental_orders`(
70	`id` INT AUTO_INCREMENT PRIMARY KEY,
71	`employee_id` INT NOT NULL,
72	`customer_id` INT NOT NULL,					
73	`car_id` INT NOT NULL,
74	`car_condition` VARCHAR(50) NOT NULL,
75	`tank_level` DECIMAL,
76	`kilometrage_start` INT(7),
77	`kilometrage_end` INT(7),
78	`total_kilometrage` INT(7),
79	`start_date` DATETIME,
80	`end_date` DATETIME,
81	`total_days` INT(4),
82	`rate_applied` DECIMAL,
83	`tax_rate` DECIMAL,
84	`order_status` VARCHAR(50),
85	`notes` TEXT
86);
87
88ALTER TABLE `rental_orders`
89	ADD CONSTRAINT `FK_orders_employees`	
90	FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`),
91	ADD CONSTRAINT `FK_orders_customers`
92	FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`),
93	ADD CONSTRAINT `FK_orders_carsID`
94	FOREIGN KEY (`car_id`) REFERENCES `cars`(`id`),
95	ADD CONSTRAINT `FK_orders_carsCOND`
96	FOREIGN KEY (`car_condition`) REFERENCES `cars`(`car_condition`);
97	
98INSERT INTO `rental_orders` (`employee_id`,`customer_id`,`car_id`)
99	VALUES (1,1,1),
100		(2,2,2),
101		(3,3,3);