Guest User

Hotel

a guest
Feb 2nd, 2018
311
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE IF NOT EXISTS `employees`(
  2.     `id` INT AUTO_INCREMENT PRIMARY KEY,
  3.     `first_name` VARCHAR(50),
  4.     `last_name` VARCHAR(50) NOT NULL,
  5.     `title` VARCHAR(50),
  6.     `notes` TEXT
  7. ); 
  8.  
  9. INSERT INTO `employees`(`last_name`)
  10.     VALUES ('Petrov'),
  11.         ('Ivanov'),
  12.         ('Dimitrov');      
  13.  
  14. CREATE TABLE IF NOT EXISTS `customers`(
  15.     `account_number` VARCHAR(50) UNIQUE PRIMARY KEY,
  16.     `first_name` VARCHAR(50),
  17.     `last_name` VARCHAR(50) NOT NULL,
  18.     `phone_number` VARCHAR(50),
  19.     `emergency_name` VARCHAR(50),
  20.     `emergency_number` VARCHAR(50),
  21.     `notes` TEXT
  22. );
  23.  
  24. INSERT INTO `customers`(`account_number`,`last_name`)
  25.     VALUES ('122','Petrov'),
  26.         ('123412','Ivanov'),
  27.         ('1212341','Dimitrov');
  28.  
  29. CREATE TABLE IF NOT EXISTS `room_status`(
  30.     `id` INT AUTO_INCREMENT PRIMARY KEY,
  31.     `room_status` VARCHAR(50) NOT NULL,
  32.     `notes` TEXT
  33. );
  34.  
  35. INSERT INTO `room_status`(`room_status`)
  36.     VALUES ('ready'),
  37.         ('empty'),
  38.         ('occupied');  
  39.  
  40. CREATE TABLE IF NOT EXISTS `room_types`(
  41.     `id` INT AUTO_INCREMENT PRIMARY KEY,
  42.     `room_type` VARCHAR(50) NOT NULL,
  43.     `notes` TEXT
  44. );
  45.  
  46. INSERT INTO `room_types`(`room_type`)
  47.     VALUES ('single'),
  48.         ('double'),
  49.         ('appartment');
  50.  
  51.  
  52. CREATE TABLE IF NOT EXISTS `bed_types`(
  53.     `id` INT AUTO_INCREMENT PRIMARY KEY,
  54.     `bed_type` VARCHAR(50) NOT NULL,
  55.     `notes` TEXT
  56. );
  57.  
  58. INSERT INTO `bed_types`(`bed_type`)
  59.     VALUES ('single'),
  60.         ('double'),
  61.         ('king');
  62.  
  63. CREATE TABLE IF NOT EXISTS `rooms`(
  64.     `room_number` INT(4) NOT NULL PRIMARY KEY,
  65.     `room_type` VARCHAR(50) NOT NULL,
  66.     `bed_type` VARCHAR(50) NOT NULL,
  67.     `rate` DECIMAL NOT NULL,
  68.     `room_status` VARCHAR(50) NOT NULL,
  69.     `notes` TEXT
  70. );
  71.  
  72. INSERT INTO `rooms`(`room_number`,`room_type`,`bed_type`,`rate`,`room_status`)
  73.     VALUES (124,'dsfs','dsfs',124.21,'dsfs'),
  74.         (1214,'dsfs','dsfs',1231.21,'dsfdfsds'),
  75.         (1224,'dsfs','dsfs',112114.21,'dssdfsfssdffs');
  76.  
  77. CREATE TABLE IF NOT EXISTS `occupancies`(
  78.     `id` INT AUTO_INCREMENT PRIMARY KEY,
  79.     `employee_id` INT,
  80.     `date_occupied` DATETIME,
  81.     `account_number` VARCHAR(50),
  82.     `room_number` INT(4) NOT NULL,
  83.     `rate_applied` DECIMAL,
  84.     `phone_charge` DECIMAL,
  85.     `notes` TEXT
  86. );
  87.  
  88. INSERT INTO `occupancies`(`room_number`)
  89.     VALUES (2),
  90.         (3),
  91.         (3);
  92.    
  93. CREATE TABLE IF NOT EXISTS `payments`(
  94.     `id` INT AUTO_INCREMENT PRIMARY KEY,
  95.     `employee_id` INT,
  96.     `payment_date` DATETIME,
  97.     `account_number` VARCHAR(50) NOT NULL,
  98.     `first_date_occupied` DATETIME,
  99.     `last_date_occupied` DATETIME,
  100.     `total_days` INT(4),
  101.     `amount_charged` DECIMAL,
  102.     `tax_rate` DECIMAL,
  103.     `tax_amount` DECIMAL,
  104.     `payment_total` DECIMAL NOT NULL,
  105.     `notes` TEXT
  106. ); 
  107.  
  108. INSERT INTO `payments`(`account_number`,`payment_total`)
  109.     VALUES ('1',3),
  110.         ('2',3),
  111.         ('1',2);
RAW Paste Data