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