· 4 years ago · May 10, 2021, 05:48 PM
1CREATE DATABASE IF NOT EXISTS `hotelreservations` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
2USE `hotelreservations`;
3
4-- --------------------------------------------------------
5
6--
7-- Table structure for table `contact_info`
8--
9CREATE TABLE hotel
10(
11 hotel_id INT NOT NULL AUTO_INCREMENT,
12 hotel_name VARCHAR(255) NOT NULL,
13 street VARCHAR(255) NOT NULL,
14 city VARCHAR(255) NOT NULL,
15 state VARCHAR(255) NOT NULL,
16 zip VARCHAR(10) NOT NULL,
17 PRIMARY KEY (hotel_id)
18);
19
20INSERT INTO `hotel` (`hotel_id`, `hotel_name`, `street`, `city`, `state`, `zip`) VALUES
21(1, 'Origin Inn', '1 Genesis St', 'New York City', 'New York', '10001'),
22(2, 'Not Shady Motel', '222 Sunny St', 'Bloomington', 'Indiana', '47401'),
23(3, 'Sunny Side Suites', '3556 Rainy St', 'Austin', 'Texas', '78701'),
24(4, 'Relax Inn', '7 Meditation Way', 'Columbia', 'Missouri', '65203'),
25(5, 'Stay Inn and Suites', '25 Lollipop Ln', 'Los Angeles', 'California', '90001');
26
27
28CREATE TABLE room
29(
30 rm_id INT NOT NULL AUTO_INCREMENT,
31 rm_no VARCHAR(10) NOT NULL,
32 rm_type VARCHAR(255) NOT NULL,
33 rm_cost FLOAT NOT NULL,
34 is_avail CHAR(1) NOT NULL,
35 hotel_id INT NOT NULL,
36 PRIMARY KEY (rm_id),
37 FOREIGN KEY (hotel_id) REFERENCES hotel(hotel_id)
38);
39
40INSERT INTO `room` (`rm_id`, `rm_no`, `rm_type`, `rm_cost`, `is_avail`, `hotel_id`) VALUES
41(1, '101', '1 Bed, King Size', 99.99, 'Y', 1),
42(2, '2020', '2 Beds, Twin Size', 78.99, 'Y', 2),
43(3, '201', '2 Beds, King Size', 124.99, 'Y', 3),
44(4, '440', '2 Beds, Queen Size', 109.99, 'Y', 4),
45(5, '123', '3 Beds, Twin Size', 199.99, 'Y', 5),
46(6, '124', '2 Beds, Queen Size', 109.99, 'Y', 1),
47(7, '201', '3 Beds, King Size', 299.99, 'Y', 1),
48(8, '4010', '3 Beds, Twin Size', 119.99, 'Y', 2),
49(9, '301', '2 Beds, Twin Size', 89.99, 'Y', 3),
50(10, '540', '2 Beds, Queen Size', 134.99, 'Y', 4),
51(11, '321', '3 Beds, California King Size', 194.99, 'Y', 5),
52(12, '1000', '1 Bed, Single Size', 49.99, 'Y', 2),
53(13, '101', '1 Bed, California King Size', 104.99, 'Y', 3),
54(14, '320', '1 Bed, Queen Size', 99.99, 'Y', 4),
55(15, '222', '2 Beds, Queen Size', 164.99, 'Y', 5);
56
57
58
59CREATE TABLE customer
60(
61 birth_date DATE NOT NULL,
62 license_no VARCHAR(14) NOT NULL,
63 fname VARCHAR(255) NOT NULL,
64 lname VARCHAR(255) NOT NULL,
65 PRIMARY KEY (license_no)
66);
67
68INSERT INTO `customer` (`birth_date`, `license_no`, `fname`, `lname`) VALUES
69('1977-04-04', '133298640012', 'John', 'Test'),
70('1945-11-11', 'A1223B3443CCDD', 'Benny', 'Jets'),
71('1999-01-31', 'AB0000L0L0', 'Billy', 'Bob'),
72('1996-11-19', 'ABCD1234DEFG', 'Adam', 'Apple'),
73('1984-12-25', 'Y1112BACD334', 'Sue', 'Mae');
74
75CREATE TABLE credit_card
76(
77 card_no VARCHAR(16) NOT NULL,
78 exp_date DATE NOT NULL,
79 cvv VARCHAR(11) NOT NULL,
80 cardholder_name VARCHAR(255) NOT NULL,
81 license_no VARCHAR(14) NOT NULL,
82 PRIMARY KEY (card_no, license_no),
83 FOREIGN KEY (license_no) REFERENCES customer(license_no)
84);
85
86INSERT INTO `credit_card` (`card_no`, `exp_date`, `cvv`, `cardholder_name`, `license_no`) VALUES
87('1111222233334444', '2025-01-01', '111', 'John Test','133298640012'),
88('1122334455667788', '2022-10-12', '165', 'Benny Jets','A1223B3443CCDD'),
89('1234567812345678', '2023-05-01', '321', 'Billy Bob','AB0000L0L0'),
90('1776249513432100', '2024-06-01', '98', 'Adam Apple','ABCD1234DEFG'),
91('1856319538470098', '2021-12-01', '861', 'Sue Mae','Y1112BACD334');
92
93CREATE TABLE reservation
94(
95 res_no INT NOT NULL AUTO_INCREMENT,
96 start_date DATE,
97 add_guests INT NOT NULL,
98 end_date DATE,
99 rm_id INT NOT NULL,
100 hotel_id INT NOT NULL,
101 PRIMARY KEY (res_no),
102 FOREIGN KEY (rm_id) REFERENCES room(rm_id),
103 FOREIGN KEY (hotel_id) REFERENCES hotel(hotel_id)
104);
105
106INSERT INTO `reservation` (`res_no`, `start_date`, `add_guests`, `end_date`, `rm_id`, `hotel_id`) VALUES
107(1, '2021-12-12', 0, '2021-12-15', 1, 1),
108(2, '2021-06-01', 1, '2021-06-03', 2, 2),
109(3, '2021-07-04', 2, '2021-07-10', 3, 3),
110(4, '2021-08-20', 0, '2021-08-25', 14, 4),
111(5, '2022-09-15', 3, '2022-09-18', 15, 5);
112
113
114CREATE TABLE contact_info
115(
116 email VARCHAR(255) NOT NULL,
117 phone_no VARCHAR(15) NOT NULL,
118 cust_address VARCHAR(255) NOT NULL,
119 license_no VARCHAR(14) NOT NULL,
120 PRIMARY KEY (license_no),
121 FOREIGN KEY (license_no) REFERENCES customer(license_no)
122);
123
124INSERT INTO `contact_info` (`email`, `phone_no`, `cust_address`, `license_no`) VALUES
125('john.test@test.com', '1234561234', '123 Boring Ave Oregon 12234', '133298640012'),
126('bennyandthejets@gmail.com', '9998801234', '1 Long St Columbia MO 65201', 'A1223B3443CCDD'),
127('bbob@bob.com', '7760013324', '400 Error Way New York City NY 11234', 'AB0000L0L0'),
128('adam.apple@apple.com', '6606432567', '19 Orchard Way St Louis MO 65989', 'ABCD1234DEFG'),
129('kallmekaren@yahoo.com', '16423479886', '100 Prison Ct Sassington NC 23443', 'Y1112BACD334');
130
131CREATE TABLE hotel_info
132(
133 phone_no VARCHAR(15) NOT NULL,
134 hotel_id INT NOT NULL,
135 PRIMARY KEY (phone_no, hotel_id),
136 FOREIGN KEY (hotel_id) REFERENCES hotel(hotel_id)
137);
138
139INSERT INTO `hotel_info` (`phone_no`, `hotel_id`) VALUES
140('9901234456', 1),
141('8127769876', 2),
142('5734421234', 3),
143('5501931931', 4),
144('4421002003', 5),
145('9901234450', 1),
146('9901234456', 2);
147
148CREATE TABLE pays_for
149(
150 amt_charged FLOAT NOT NULL,
151 card_no VARCHAR(16) NOT NULL,
152 res_no INT NOT NULL,
153 PRIMARY KEY (card_no),
154 FOREIGN KEY (card_no) REFERENCES credit_card(card_no),
155 FOREIGN KEY (res_no) REFERENCES reservation(res_no)
156);
157
158INSERT INTO `pays_for` (`amt_charged`, `card_no`, `res_no`) VALUES
159(399.96, '1111222233334444', 1),
160(236.97, '1122334455667788', 2),
161(874.93, '1234567812345678', 3),
162(599.94, '1776249513432100', 4),
163(659.96, '1856319538470098', 5);