Guest User

Car Rental

a guest
Feb 1st, 2018
213
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE DATABASE `car_rental`;
  2.  
  3. USE `car_rental`;
  4.  
  5. CREATE 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.  
  14. INSERT INTO `categories` (`category`)
  15.     VALUES ('Van'),
  16.         ('Sedan'),
  17.         ('4x4');
  18.        
  19. CREATE 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.  
  32. ALTER TABLE `cars`
  33.     ADD CONSTRAINT `FK_cars_categories`
  34.     FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`);
  35.  
  36. INSERT 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.        
  41. CREATE 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.  
  49. INSERT INTO `employees`(`last_name`)
  50.     VALUES ('Ivan'),
  51.         ('Petkan'),
  52.         ('Pesho');
  53.        
  54. CREATE 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.  
  64. INSERT INTO `customers`(`full_name`)
  65.     VALUES ('P.P'),
  66.         ('D.D'),
  67.         ('A.A');
  68.        
  69. CREATE 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.  
  88. ALTER 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.    
  98. INSERT INTO `rental_orders` (`employee_id`,`customer_id`,`car_id`)
  99.     VALUES (1,1,1),
  100.         (2,2,2),
  101.         (3,3,3);
RAW Paste Data