· 6 years ago · May 12, 2019, 12:02 AM
1DROP TABLE IF EXISTS Meetings;
2DROP TABLE IF EXISTS Attendance;
3DROP TABLE IF EXISTS Employee;
4DROP TABLE IF EXISTS Attendance_Statuses;
5DROP TABLE IF EXISTS Response_Types;
6DROP TABLE IF EXISTS Meeting_Statuses;
7DROP TABLE IF EXISTS Rooms;
8
9
10
11/* begin table creation */
12
13CREATE TABLE IF NOT EXISTS Rooms
14 (Room_ID smallint unsigned not null auto_increment,
15 Capacity smallint unsigned not null,
16 constraint pk_rooms primary key (Room_ID)
17 );
18
19create table IF NOT EXISTS Attendance_Statuses
20 (Attendance_Status_ID varchar(6) not null,
21 Attendance_Status_Name varchar(30) not null,
22 constraint pk_Attendance_Status primary key (Attendance_Status_ID)
23 );
24
25create table IF NOT EXISTS Employee
26 (Employee_ID smallint unsigned not null auto_increment,
27 UsrFirst_Name varchar(50) not null,
28 UsrLast_Name varchar(50) not null,
29 constraint pk_employee primary key (Employee_ID)
30 );
31
32create table IF NOT EXISTS Response_Types
33 (Response_Type_ID varchar(3) not null,
34 Response_Name varchar(20) not null,
35 constraint pk_response_type primary key (Response_Type_ID)
36 );
37
38create table IF NOT EXISTS Meeting_Statuses
39 (Meeting_Status_ID varchar(6) not null,
40 Meeting_Status_Name varchar(20) not null,
41 constraint pk_meeting_statuses primary key (Meeting_Status_ID)
42 );
43
44create table IF NOT EXISTS Meetings
45 (Meeting_ID smallint unsigned not null auto_increment,
46 Meeting_Name varchar(50) not null,
47 Meeting_Description varchar(200) not null,
48 Meeting_DateTime datetime not null default now(),
49 Meeting_Duration varchar(20) not null,
50 Meeting_Status_ID varchar(6) not null,
51 Meeting_Room_ID smallint unsigned,
52 Organizer_ID smallint unsigned,
53 constraint fk_meetings_meeting_status foreign key (Meeting_Status_ID)
54 references Meeting_Statuses(Meeting_Status_ID) ON DELETE RESTRICT,
55 constraint fk_meetings_meeting_room foreign key (Meeting_Room_ID)
56 references Rooms(Room_ID) ON DELETE RESTRICT,
57 constraint fk_meetings_organizer foreign key (Organizer_ID)
58 references Employee(Employee_ID) ON DELETE RESTRICT,
59 constraint pk_meetings primary key (Meeting_ID)
60 );
61
62create table IF NOT EXISTS Attendance
63 (Attendance_ID smallint unsigned not null auto_increment,
64 Response_DateTime datetime not null default now(),
65 Employee_ID smallint unsigned,
66 Response_Type_ID varchar(3) not null,
67 Meeting_ID smallint unsigned,
68 Attendance_Status varchar(6) not null,
69 constraint fk_attendance_employee foreign key (Employee_ID)
70 references Employee(Employee_ID) ON DELETE RESTRICT,
71 constraint fk_attendance_response_type foreign key (Response_Type_ID)
72 references Response_Types(Response_Type_ID) ON DELETE RESTRICT,
73 constraint fk_attendance_meeting foreign key (Meeting_ID)
74 references Meetings(Meeting_ID) ON DELETE RESTRICT,
75 constraint fk_attendance_attendace_status foreign key (Attendance_Status)
76 references Attendance_Statuses(Attendance_Status_ID) ON DELETE RESTRICT,
77 constraint pk_attendance primary key (Attendance_ID)
78 );
79
80/* end table creation */
81
82/* begin data population */
83
84/* room data */
85insert into Rooms (Capacity)
86values (15),(20),(25),(50),(100);
87
88/* meeting statuses data */
89insert into Meeting_Statuses (Meeting_Status_ID, Meeting_Status_Name)
90values ('PAST', 'Past Meeting'),('NOW','Current Meeting'),('FUTURE','Incoming Meeting');
91
92/* attendance statuses data */
93insert into Attendance_Statuses (Attendance_Status_ID, Attendance_Status_Name)
94values ('HERE', 'Attending Meeting'),('ABSENT','Not Attending Meeting');
95
96/* response types data */
97insert into Response_Types (Response_Type_ID, Response_Name)
98values ('POS', 'Positive Response'),('NEG','Negative Response');
99
100/* employee data */
101insert into Employee (UsrFirst_Name, UsrLast_Name)
102values ('Michael', 'Smith'),
103('Susan', 'Barker'),
104('Robert', 'Tyler'),
105('Susan', 'Hawthorne'),
106('John', 'Gooding'),
107('Helen', 'Fleming'),
108('Chris', 'Tucker'),
109('Sarah', 'Parker'),
110('Jane', 'Grossman'),
111('Paula', 'Roberts');
112
113
114/* meetings data */
115insert into Meetings (Meeting_Name, Meeting_Description, Meeting_DateTime, Meeting_Duration, Meeting_Status_ID, Meeting_Room_ID, Organizer_ID)
116VALUES ('Professional Development','This meeting will be held for the professional development of our employees','2019-05-05 15:00:00','30 Minutes','PAST',3,10),
117('Group Meeting','This meeting will be held for group studies','2019-05-10 10:00:00','30 Minutes','NOW',4,8),
118('Coffee and Reading','This meeting will be held for relaxing with coffee and books','2019-05-12 15:00:00','45 Minutes','FUTURE',2,9),
119('Officer Meeting','This meeting will be held with administrator offices','2019-05-15 14:00:00','1 Hour','FUTURE',1,1),
120('Presentation','This meeting will be held for presenting the company to the visitors','2019-05-20 12:00:00','1 Hour','FUTURE',5,5);
121
122
123/* attendance data */
124insert into Attendance (Response_DateTime, Employee_ID, Response_Type_ID, Meeting_ID, Attendance_Status)
125VALUES ('2019-05-02 10:00:00',1,'POS',1,'HERE'),
126('2019-05-10 10:00:00',7,'POS',2,'HERE'),
127('2019-05-10 10:00:00',8,'POS',3,'ABSENT'),
128('2019-05-10 10:00:00',2,'POS',4,'ABSENT'),
129('2019-05-10 10:00:00',6,'POS',5,'ABSENT');
130
131/* end data population */