· 5 years ago · Oct 12, 2020, 05:30 AM
1DROP TABLE IF EXISTS `employees`;
2CREATE TABLE `employees`(
3 `id` INT PRIMARY KEY AUTO_INCREMENT,
4 `first_name` VARCHAR(50) NOT NULL,
5 `last_name` VARCHAR(50) NOT NULL,
6 `title` VARCHAR(50) NOT NULL,
7 `notes` TEXT(400)
8);
9
10DROP TABLE IF EXISTS `customers`;
11CREATE TABLE `customers`(
12 `account_number` VARCHAR(10) PRIMARY KEY,
13 `first_name` VARCHAR(50) NOT NULL,
14 `last_name` VARCHAR(50) NOT NULL,
15 `phone_number` INT NOT NULL UNIQUE,
16 `emergency_name` VARCHAR(50),
17 `emergency_number` INT,
18 `notes` TEXT(400)
19);
20
21DROP TABLE IF EXISTS `room_status`;
22CREATE TABLE `room_status`(
23 `room_status` VARCHAR(50) PRIMARY KEY,
24 `notes` TEXT(400)
25);
26
27DROP TABLE IF EXISTS `room_types`;
28CREATE TABLE `room_types`(
29 `room_type` VARCHAR(50) PRIMARY KEY,
30 `notes` TEXT(400)
31);
32
33DROP TABLE IF EXISTS `bed_types`;
34CREATE TABLE `bed_types`(
35 `bed_type` VARCHAR(50) PRIMARY KEY,
36 `notes` TEXT(400)
37);
38
39DROP TABLE IF EXISTS `rooms`;
40CREATE TABLE `rooms`(
41 `room_number` INT PRIMARY KEY,
42 `room_type` VARCHAR(50) NOT NULL,
43 `bed_type` VARCHAR(50) NOT NULL,
44 `rate` FLOAT(2),
45 `room_status` VARCHAR(50) NOT NULL,
46 `notes` TEXT(400)
47);
48
49DROP TABLE IF EXISTS `payments`;
50CREATE TABLE `payments`(
51 `id` INT PRIMARY KEY AUTO_INCREMENT,
52 `employee_id` INT NOT NULL,
53 `payment_date` DATE NOT NULL,
54 `account_number` VARCHAR(10) NOT NULL,
55 `first_date_occupied` DATE NOT NULL,
56 `last_date_occupied` DATE NOT NULL,
57 `total_days` INT NOT NULL,
58 `amount_charged` FLOAT(2) NOT NULL,
59 `tax_rate` FLOAT(2) NOT NULL,
60 `tax_amount` FLOAT(2) NOT NULL,
61 `payment_total` FLOAT(2) NOT NULL,
62 `notes` TEXT(400)
63);
64
65
66DROP TABLE IF EXISTS `occupancies`;
67CREATE TABLE `occupancies`(
68 `id` INT PRIMARY KEY AUTO_INCREMENT,
69 `employee_id` INT NOT NULL,
70 `date_occupied` DATE NOT NULL,
71 `account_number` VARCHAR(10) NOT NULL,
72 `room_number` INT NOT NULL,
73 `rate_applied` FLOAT(2),
74 `phone_charge` INT,
75 `notes` TEXT(400)
76);
77
78INSERT INTO `employees` (`first_name`, `last_name`, `title`, `notes`)
79VALUES
80('Ivan', 'Ivanov', 'Senior', NULL),
81('Slavcho', 'SLavchov', 'CEO', NULL),
82('Mitko', 'Mitkov', 'Senior', NULL);
83
84INSERT INTO `customers` (`account_number`, `first_name`, `last_name`, `phone_number`, `emergency_name`, `emergency_number`, `notes`)
85VALUES
86('034567', 'Dragan', 'Draganov', 0887815610, 'DD', 0887815610, NULL),
87('145678', 'Petkan', 'Petkanov', 0887815611, 'PP', 0887815611, NULL),
88('256789', 'Valentin', 'Valentinov', 0887815612, 'VV', 0887815612, NULL);
89
90INSERT INTO `room_status` (`room_status`, `notes`)
91VALUES
92('free', NULL),
93('occupied', NULL),
94('cleaning', NULL);
95
96INSERT INTO `room_types` (`room_type`, `notes`)
97VALUES
98('Box', NULL),
99('Apartment', NULL),
100('House', NULL);
101
102INSERT INTO `bed_types` (`bed_type`, `notes`)
103VALUES
104('single', NULL),
105('double', NULL),
106('triple', NULL);
107
108INSERT INTO `rooms` (`room_number`, `room_type`, `bed_type`, `rate`, `room_status`, `notes`)
109VALUES
110(111, 'Box', 'single', 5.55, 'free', NULL),
111(222, 'Apartment', 'double', 6.66, 'occupied', NULL),
112(333, 'House', 'triple', 7.77, 'cleaning', NULL);
113
114INSERT INTO `payments` (`employee_id`, `payment_date`, `account_number`, `first_date_occupied`, `last_date_occupied`
115, `total_days`, `amount_charged`, `tax_rate`, `tax_amount`, `payment_total`, `notes`)
116VALUES
117(1, '2018-08-18', '01234567', '2020-08-16', '2018-08-18', 8, 35, 25, 11, 90, NULL),
118(2, '2019-09-19', '012345678', '2020-08-16', '2019-09-19', 9, 45, 25, 12, 190, NULL),
119(3, '2020-10-20', '0123456789', '2020-08-16', '2020-10-20', 10, 55, 25, 13, 290, NULL);
120
121INSERT INTO `occupancies` (`employee_id`, `date_occupied`, `account_number`, `room_number`, `rate_applied`, `phone_charge`, `notes`)
122VALUES
123(1, '2018-08-18', '012345678', 222, 50, 50, NULL),
124(2, '2018-08-18', '123456789', 111, 60, 50, NULL),
125(3, '2018-08-18', '234567890', 333, 70, 50, NULL);