· 8 years ago · Nov 27, 2017, 06:16 PM
1#Timothy Kang
2#Final Project
3
4DROP DATABASE IF EXISTS scheduling;
5CREATE DATABASE IF NOT EXISTS scheduling;
6
7USE scheduling;
8
9DROP TABLE IF EXISTS Person;
10DROP TABLE IF EXISTS Building;
11DROP TABLE IF EXISTS Room;
12DROP TABLE IF EXISTS Meeting;
13DROP TABLE IF EXISTS Person_meeting;
14
15#1
16CREATE TABLE IF NOT EXISTS Person(
17person_id INT(10) PRIMARY KEY AUTO_INCREMENT,
18first_name VARCHAR(20) NOT NULL,
19last_name VARCHAR(20) NOT NULL
20);
21
22#2
23CREATE TABLE IF NOT EXISTS Building(
24building_id INT(10) PRIMARY KEY AUTO_INCREMENT,
25building_name VARCHAR(30) NOT NULL
26);
27
28#3
29CREATE TABLE IF NOT EXISTS Room(
30room_id INT(10) PRIMARY KEY AUTO_INCREMENT,
31room_number INT(5) NOT NULL,
32building_id INT(20) NOT NULL,
33capacity INT(5) NOT NULL,
34CONSTRAINT fk_building_id FOREIGN KEY (building_id)
35 REFERENCES Building(building_id)
36);
37
38#4
39CREATE TABLE IF NOT EXISTS Meeting(
40meeting_id INT(10) PRIMARY KEY AUTO_INCREMENT,
41room_id INT(5) NOT NULL,
42meeting_start DATETIME NOT NULL,
43meeting_end DATETIME NOT NULL,
44CONSTRAINT fk_room_id FOREIGN KEY (room_id)
45 REFERENCES Room(room_id)
46);
47
48#5
49CREATE TABLE IF NOT EXISTS Person_meeting(
50person_id INT(10),
51meeting_id INT(10),
52CONSTRAINT pk_person_meeting PRIMARY KEY (person_id, meeting_id),
53CONSTRAINT fk_person_id FOREIGN KEY (person_id)
54 REFERENCES Person(person_id),
55CONSTRAINT fk_meeting_id FOREIGN KEY (meeting_id)
56 REFERENCES Meeting(meeting_id)
57);
58
59#6
60INSERT INTO Person(first_name, last_name)
61VALUES('Tom', 'Hanks'),
62('Anne', 'Hathaway'),
63('Tom', 'Cruise'),
64('Meryl', 'Streep'),
65('Chris', 'Pratt'),
66('Halle', 'Berry'),
67('Robert', 'De Niro'),
68('Julia', 'Roberts'),
69('Denzel', 'Washington'),
70('Melissa', 'McCarthy');
71
72#7
73INSERT INTO Building(building_id, building_name)
74VALUES(1,'Headquarters'),
75(2, 'Main Street Building');
76
77#8
78INSERT INTO Room(room_number, building_id, capacity)
79VALUES(100, 1, 5),
80(200, 1, 4),
81(300, 1, 10),
82(10, 2, 4),
83(20, 2, 4);
84
85#9
86INSERT INTO Meeting(room_id, meeting_start, meeting_end)
87VALUES(1, '2016-12-25 09:00:00', '2016-12-25 10:00:00'),
88(1, '2016-12-25 10:00:00', '2016-12-25 12:00:00'),
89(1, '2016-12-25 11:00:00', '2016-12-25 12:00:00'),
90(2, '2016-12-25 09:00:00', '2016-12-25 10:00:00'),
91(4, '2016-12-25 09:00:00', '2016-12-25 10:00:00'),
92(5, '2016-12-25 14:00:00', '2016-12-25 16:00:00');
93
94#10
95INSERT INTO Person_meeting(person_id, meeting_id)
96VALUES(1, 1),
97(10, 1),
98(1, 2),
99(2, 2),
100(3, 2),
101(4, 2),
102(5, 2),
103(6, 2),
104(7, 2),
105(8, 2),
106(9, 3),
107(10, 3),
108(1, 4),
109(2, 4),
110(8, 5),
111(9, 5),
112(1, 6),
113(2, 6),
114(3, 6);
115
116SELECT * FROM Person;
117SELECT * FROM Building;
118SELECT * FROM Room;
119SELECT * FROM Meeting;
120SELECT * FROM Person_meeting;
121
122#11
123SELECT a.meeting_id, a.room_id, a.meeting_start, a.meeting_end, b.person_id, b.first_name, b.last_name FROM Meeting a, Person b, Person_meeting c
124WHERE b.person_id = c.person_id
125AND a.meeting_id = c.meeting_id
126AND b.first_name = 'Tom'
127AND b.last_name = 'Hanks';
128
129#12
130SELECT a.meeting_id, b.person_id, b.first_name, b.last_name FROM Meeting a, Person b, Person_meeting c
131WHERE a.meeting_id = 2
132AND a.meeting_id = c.meeting_id
133AND b.person_id = c.person_id;
134
135#13
136SELECT a.building_name, b.person_id, b.first_name, b.last_name FROM Building a, Person b, Person_meeting c, Meeting d, Room e
137WHERE a.building_name = 'Main Street Building'
138AND b.person_id = c.person_id
139AND d.meeting_id = c.meeting_id
140AND d.room_id = e.room_id
141AND e.building_id = a.building_id;
142
143#14
144SELECT a.meeting_id, a.room_id, COUNT(b.person_id) FROM Meeting a, Person b, Person_meeting c, Room d
145WHERE a.meeting_id = c.meeting_id
146AND b.person_id = c.meeting_id
147AND a.room_id = d.room_id
148GROUP BY meeting_id;
149
150#15
151SELECT a.person_id, a.first_name, a.last_name, c.meeting_start, c.meeting_end FROM Person a
152INNER JOIN Person_meeting b
153 ON a.person_id = b.person_id
154INNER JOIN Meeting c
155 ON c.meeting_id = b.meeting_id
156WHERE c.meeting_end < '2016-12-25 12:00:00'
157GROUP BY a.person_id;