Guest User

Car Rental

a guest
Feb 2nd, 2018
767
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE IF NOT EXISTS `categories`(
  2.     `id` INT AUTO_INCREMENT PRIMARY KEY,
  3.     `category` VARCHAR(50) NOT NULL,
  4.     `daily_rate` DECIMAL,
  5.     `weekly_rate` DECIMAL,
  6.     `monthly_rate` DECIMAL,
  7.     `weekend_rate` DECIMAL
  8. );
  9.  
  10. INSERT INTO `categories` (`category`)
  11.     VALUES ('Van'),
  12.         ('Sedan'),
  13.         ('4x4');
  14.        
  15. CREATE TABLE IF NOT EXISTS `cars`(
  16.     `id` INT AUTO_INCREMENT PRIMARY KEY,
  17.     `plate_number` VARCHAR(50) NOT NULL UNIQUE,
  18.     `make` VARCHAR(50),
  19.     `model` VARCHAR(50),
  20.     `car_year` INT(4), 
  21.     `category_id` INT,
  22.     `doors` INT(1),
  23.     `picture` LONGBLOB,
  24.     `car_condition` VARCHAR(50) DEFAULT 0,
  25.     `available` ENUM ('true','false')
  26. );
  27.  
  28. INSERT INTO `cars` (`plate_number`)
  29.     VALUES ('Van123'),
  30.         ('Sedan123'),
  31.         ('4x4--');
  32.        
  33. CREATE TABLE IF NOT EXISTS `employees`(
  34.     `id` INT AUTO_INCREMENT PRIMARY KEY,
  35.     `first_name` VARCHAR(50),
  36.     `last_name` VARCHAR(50) NOT NULL,
  37.     `title` VARCHAR(50),
  38.     `notes` TEXT
  39. );             
  40.  
  41. INSERT INTO `employees`(`last_name`)
  42.     VALUES ('Ivan'),
  43.         ('Petkan'),
  44.         ('Pesho');
  45.        
  46. CREATE TABLE IF NOT EXISTS `customers`(
  47.     `id` INT AUTO_INCREMENT PRIMARY KEY,
  48.     `driver_license_number` VARCHAR(50),
  49.     `full_name` VARCHAR(50) NOT NULL,
  50.     `address` VARCHAR(50),
  51.     `city` VARCHAR(50),
  52.     `zip_code` INT,
  53.     `notes` TEXT
  54. );
  55.  
  56. INSERT INTO `customers`(`full_name`)
  57.     VALUES ('P.P'),
  58.         ('D.D'),
  59.         ('A.A');
  60.        
  61. CREATE TABLE IF NOT EXISTS `rental_orders`(
  62.     `id` INT AUTO_INCREMENT PRIMARY KEY,
  63.     `employee_id` INT NOT NULL,
  64.     `customer_id` INT NOT NULL,                
  65.     `car_id` INT NOT NULL,
  66.     `car_condition` VARCHAR(50) DEFAULT 0,
  67.     `tank_level` DECIMAL,
  68.     `kilometrage_start` INT(7),
  69.     `kilometrage_end` INT(7),
  70.     `total_kilometrage` INT(7),
  71.     `start_date` DATETIME,
  72.     `end_date` DATETIME,
  73.     `total_days` INT(4),
  74.     `rate_applied` DECIMAL,
  75.     `tax_rate` DECIMAL,
  76.     `order_status` VARCHAR(50),
  77.     `notes` TEXT
  78. );
  79.        
  80. INSERT INTO `rental_orders` (`employee_id`,`customer_id`,`car_id`)
  81.     VALUES (1,1,1),
  82.         (2,2,2),
  83.         (3,3,3);
RAW Paste Data