eAfbAv2r

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 (25) NOT NULL,
9last_name VARCHAR (25) 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 KEY AUTO_INCREMENT,
19room_number INT (5) NOT NULL,
20building_id INT (10) NOT NULL,
21capacity INT (10) NOT NULL
22);
23
24CREATE TABLE IF NOT EXISTS Meeting(
25meeting_id INT (10) PRIMARY KEY AUTO_INCREMENT,
26room_id INT (5) NOT NULL,
27meeting_start DATETIME NOT NULL,
28meeting_end DATETIME NOT NULL,
29
30CONSTRAINT room_fk FOREIGN KEY (room_id)
31	REFERENCES Room (room_id)
32);
33
34CREATE TABLE IF NOT EXISTS Person_meeting(
35person_id INT (10) NOT NULL,
36meeting_id INT (10) NOT NULL,
37
38CONSTRAINT person_meeting_pk PRIMARY KEY (person_id, meeting_id),
39CONSTRAINT person_fk FOREIGN KEY (person_id)
40	REFERENCES Person (person_id),
41CONSTRAINT meeting_fk FOREIGN KEY (meeting_id)
42	REFERENCES Meeting (meeting_id)
43);
44
45INSERT INTO Person(first_name, last_name)
46	VALUES ('Tom', 'Hanks');
47INSERT INTO Person(first_name, last_name)
48	VALUES ('Anne', 'Hathaway');
49INSERT INTO Person(first_name, last_name)
50	VALUES ('Tom', 'Cruise');
51INSERT INTO Person(first_name, last_name)
52	VALUES ('Meryl', 'Streep');
53INSERT INTO Person(first_name, last_name)
54	VALUES ('Chris', 'Pratt');
55INSERT INTO Person(first_name, last_name)
56	VALUES ('Halle', 'Berry');
57INSERT INTO Person(first_name, last_name)
58	VALUES ('Robert', 'De Niro');
59INSERT INTO Person(first_name, last_name)
60	VALUES ('Julia', 'Roberts');
61INSERT INTO Person(first_name, last_name)
62	VALUES ('Denzel', 'Washington');
63INSERT INTO Person(first_name, last_name)
64	VALUES ('Melissa', 'McCarthy');
65    
66INSERT INTO Building (building_name)
67	VALUES ('Headquarters');
68INSERT INTO Building (building_name)
69	VALUES ('Main Street Building');
70    
71INSERT INTO Room (room_number, building_id, capacity)
72	VALUES (100, 1, 5);
73INSERT INTO Room (room_number, building_id, capacity)
74	VALUES (200, 1, 4);
75INSERT INTO Room (room_number, building_id, capacity)
76	VALUES (300, 1, 10);
77INSERT INTO Room (room_number, building_id, capacity)
78	VALUES (10, 2, 4);
79INSERT INTO Room (room_number, building_id, capacity)
80	VALUES (20, 2, 4);
81
82INSERT INTO Meeting (room_id, meeting_start, meeting_end)
83	VALUES (1, '2016-12-25 09:00:00', '2016-12-25 10:00:00');
84INSERT INTO Meeting (room_id, meeting_start, meeting_end)
85	VALUES (1, '2016-12-25 10:00:00', '2016-12-25 12:00:00');
86INSERT INTO Meeting (room_id, meeting_start, meeting_end)
87	VALUES (1, '2016-12-25 11:00:00', '2016-12-25 12:00:00');
88INSERT INTO Meeting (room_id, meeting_start, meeting_end)
89	VALUES (2, '2016-12-25 09:00:00', '2016-12-25 10:00:00');
90INSERT INTO Meeting (room_id, meeting_start, meeting_end)
91	VALUES (4, '2016-12-25 09:00:00', '2016-12-25 10:00:00');
92INSERT INTO Meeting (room_id, meeting_start, meeting_end)
93	VALUES (5, '2016-12-25 14:00:00', '2016-12-25 16:00:00');
94    
95INSERT INTO Person_meeting (person_id, meeting_id)
96	VALUES (1, 1);
97INSERT INTO Person_meeting (person_id, meeting_id)
98	VALUES (10, 1);
99INSERT INTO Person_meeting (person_id, meeting_id)
100	VALUES (1, 2);
101INSERT INTO Person_meeting (person_id, meeting_id)
102	VALUES (2, 2);
103INSERT INTO Person_meeting (person_id, meeting_id)
104	VALUES (3, 2);
105INSERT INTO Person_meeting (person_id, meeting_id)
106	VALUES (4, 2);
107INSERT INTO Person_meeting (person_id, meeting_id)
108	VALUES (5, 2);
109INSERT INTO Person_meeting (person_id, meeting_id)
110	VALUES (6, 2);
111INSERT INTO Person_meeting (person_id, meeting_id)
112	VALUES (7, 2);
113INSERT INTO Person_meeting (person_id, meeting_id)
114	VALUES (8, 2);
115INSERT INTO Person_meeting (person_id, meeting_id)
116	VALUES (9, 3);
117INSERT INTO Person_meeting (person_id, meeting_id)
118	VALUES (10, 3);
119INSERT INTO Person_meeting (person_id, meeting_id)
120	VALUES (1, 4);
121INSERT INTO Person_meeting (person_id, meeting_id)
122	VALUES (2, 4);
123INSERT INTO Person_meeting (person_id, meeting_id)
124	VALUES (8, 5);
125INSERT INTO Person_meeting (person_id, meeting_id)
126	VALUES (9, 5);
127INSERT INTO Person_meeting (person_id, meeting_id)
128	VALUES (1, 6);
129INSERT INTO Person_meeting (person_id, meeting_id)
130	VALUES (2, 6);
131INSERT INTO Person_meeting (person_id, meeting_id)
132	VALUES (3, 6);