· 4 years ago · May 05, 2021, 11:28 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
57CREATE TABLE CREDIT_CARD
58(
59 card_no VARCHAR(16) NOT NULL,
60 exp_date DATE NOT NULL,
61 cvv VARCHAR(11) NOT NULL,
62 cardholder_name VARCHAR(255) NOT NULL,
63 PRIMARY KEY (card_no)
64);
65
66INSERT INTO `credit_card` (`card_no`, `exp_date`, `cvv`, `cardholder_name`) VALUES
67('1111222233334444', '2025-01-01', '111', 'John Test'),
68('1122334455667788', '2022-10-12', '165', 'Benny Jets'),
69('1234567812345678', '2023-05-01', '321', 'Billy Bob'),
70('1776249513432100', '2024-06-01', '98', 'Adam Apple'),
71('1856319538470098', '2021-12-01', '861', 'Sue Mae');
72
73CREATE TABLE CUSTOMER
74(
75 birth_date DATE NOT NULL,
76 license_no VARCHAR(14) NOT NULL,
77 card_no VARCHAR(16) NOT NULL,
78 PRIMARY KEY (license_no),
79 FOREIGN KEY (card_no) REFERENCES CREDIT_CARD(card_no)
80);
81
82INSERT INTO `customer` (`birth_date`, `license_no`, `card_no`) VALUES
83('1977-04-04', '133298640012', '1856319538470098'),
84('1945-11-11', 'A1223B3443CCDD', '1234567812345678'),
85('1999-01-31', 'AB0000L0L0', '1776249513432100'),
86('1996-11-19', 'ABCD1234DEFG', '1111222233334444'),
87('1984-12-25', 'Y1112BACD334', '1122334455667788');
88
89CREATE TABLE RESERVATION
90(
91 res_no INT NOT NULL AUTO_INCREMENT,
92 start_date DATE NOT NULL,
93 add_guests INT NOT NULL,
94 end_date DATE NOT NULL,
95 rm_id INT NOT NULL,
96 hotel_id INT NOT NULL,
97 PRIMARY KEY (res_no),
98 FOREIGN KEY (rm_id) REFERENCES ROOM(rm_id),
99 FOREIGN KEY (hotel_id) REFERENCES HOTEL(hotel_id)
100);
101
102INSERT INTO `reservation` (`res_no`, `start_date`, `add_guests`, `end_date`, `rm_id`, `hotel_id`) VALUES
103(1, '2021-12-12', 0, '2021-12-15', 1, 1),
104(2, '2021-06-01', 1, '2021-06-03', 2, 2),
105(3, '2021-07-04', 2, '2021-07-10', 3, 3),
106(4, '2021-08-20', 0, '2021-08-25', 14, 4),
107(5, '2022-09-15', 3, '2022-09-18', 15, 5);
108
109
110CREATE TABLE CONTACT_INFO
111(
112 email VARCHAR(255) NOT NULL,
113 phone_no VARCHAR(15) NOT NULL,
114 cust_address VARCHAR(255) NOT NULL,
115 fname VARCHAR(255) NOT NULL,
116 lname 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`, `fname`, `lname`, `license_no`) VALUES
123('john.test@test.com', '1234561234', '123 Boring Ave Oregon 12234', 'John', 'Test', '133298640012'),
124('bennyandthejets@gmail.com', '9998801234', '1 Long St Columbia MO 65201', 'Benny', 'Jets', 'A1223B3443CCDD'),
125('bbob@bob.com', '7760013324', '400 Error Way New York City NY 11234', 'Billy', 'Bob', 'AB0000L0L0'),
126('adam.apple@apple.com', '6606432567', '19 Orchard Way St Louis MO 65989', 'Adam', 'Apple', 'ABCD1234DEFG'),
127('kallmekaren@yahoo.com', '16423479886', '100 Prison Ct Sassington NC 23443', 'Sue', 'Mae', '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);
162