6Fnm22ZM

1DROP DATABASE IF EXISTS scheduling;
2CREATE DATABASE IF NOT EXISTS scheduling;
3
4USE scheduling;
5
6CREATE TABLE IF NOT EXISTS Person(
7person_id INT(10) PRIMARY KEY AUTO_INCREMENT,
8first_name VARCHAR(20) NOT NULL,
9last_name VARCHAR(20) NOT NULL
10);
11
12CREATE TABLE IF NOT EXISTS Building(
13building_id INT(10) PRIMARY KEY AUTO_INCREMENT,
14building_name VARCHAR(30) NOT NULL
15);
16
17CREATE TABLE IF NOT EXISTS Room(
18room_id INT(10) PRIMARY KEY,
19room_number INT(5) NOT NULL,
20building_id INT(20) NOT NULL,
21capacity INT(5) NOT NULL,
22CONSTRAINT fk_building_id FOREIGN KEY (building_id)
23	REFERENCES Building(building_id)
24);
25
26CREATE TABLE IF NOT EXISTS Meeting(
27meeting_id INT(10) PRIMARY KEY,
28room_id INT(5) NOT NULL,
29meeting_start DATETIME NOT NULL,
30meeting_end DATETIME NOT NULL,
31CONSTRAINT fk_room_id FOREIGN KEY (room_id)
32	REFERENCES Room(room_id)
33);
34
35CREATE TABLE IF NOT EXISTS Person_meeting(
36person_id INT(10),
37meeting_id INT(10),
38CONSTRAINT pk_person_meeting PRIMARY KEY (person_id, meeting_id),
39CONSTRAINT fk_person_id FOREIGN KEY (person_id)
40	REFERENCES Person(person_id),
41CONSTRAINT fk_meeting_id FOREIGN KEY (meeting_id)
42	REFERENCES Meeting(meeting_id)
43);
44
45INSERT INTO Person(first_name, last_name)
46VALUES('Tom', 'Hanks'), 
47('Anne', 'Hathaway'),
48('Tom', 'Cruise'),
49('Meryl', 'Streep'),
50('Chris', 'Pratt'),
51('Halle', 'Berry'),
52('Robert', 'De Niro'),
53('Julia', 'Roberts'),
54('Denzel', 'Washington'),
55('Melissa', 'McCarthy');
56
57INSERT INTO Building(building_id, building_name)
58VALUES(1,'Headquarters'), 
59(2, 'Main Street Building');
60
61INSERT INTO Room(room_number, building_id, capacity)
62VALUES(100, 1, 5),
63(200, 1, 4),
64(300, 1, 10),
65(10, 2, 4),
66(20, 2, 4);
67
68INSERT INTO Meeting(room_id, meeting_start, meeting_end)
69VALUES(1, '2016-12-25 09:00:00', '2016-12-25 10:00:00'),
70(1, '2016-12-25 10:00:00', '2016-12-25 12:00:00'),
71(1, '2016-12-25 11:00:00', '2016-12-25 12:00:00'),
72(2, '2016-12-25 09:00:00', '2016-12-25 10:00:00'),
73(4, '2016-12-25 09:00:00', '2016-12-25 10:00:00'),
74(5, '2016-12-25 14:00:00', '2016-12-25 16:00:00');
75
76INSERT INTO Person_meeting(person_id, meeting_id)
77VALUES(1, 1),
78(10, 1),
79(1, 2),
80(2, 2),
81(3, 2),
82(4, 2),
83(5, 2),
84(6, 2),
85(7, 2),
86(8, 2),
87(9, 3),
88(10, 3),
89(1, 4),
90(2, 4),
91(8, 5),
92(9, 5),
93(1, 6),
94(2, 6),
95(3, 6);