Need All the questions done
Assignment 3 Q1 Q2 Q3 DROP DATABASE IF EXISTS assignment3; CREATE DATABASE assignment3; USE assignment3; CREATE TABLE Building( building_name varchar(100) NOT NULL, PRIMARY KEY(building_name) ); CREATE TABLE Floor( building_name varchar(100) NOT NULL, floor_name varchar(100) NOT NULL, FOREIGN KEY(building_name) REFERENCES Building(building_name), PRIMARY KEY(building_name, floor_name) ); CREATE TABLE Region( building_name varchar(100) NOT NULL, floor_name varchar(100) NOT NULL, region_name varchar(100) NOT NULL, dept_name varchar(100) NOT NULL, FOREIGN KEY(building_name, floor_name) REFERENCES Floor(building_name, floor_name), PRIMARY KEY(building_name, floor_name, region_name) ); CREATE TABLE Room( building_name varchar(100) NOT NULL, floor_name varchar(100) NOT NULL, region_name varchar(100) NOT NULL, room_name varchar(100) NOT NULL, capacity int, FOREIGN KEY(building_name, floor_name, region_name) REFERENCES Region(building_name, floor_name, region_name), PRIMARY KEY(building_name, floor_name, region_name, room_name) ); CREATE TABLE Office( building_name varchar(100) NOT NULL, floor_name varchar(100) NOT NULL, region_name varchar(100) NOT NULL, room_name varchar(100) NOT NULL, FOREIGN KEY(building_name, floor_name, region_name, room_name) REFERENCES Room(building_name, floor_name, region_name, room_name), PRIMARY KEY(building_name, floor_name, region_name, room_name) ); CREATE TABLE Lab( building_name varchar(100) NOT NULL, floor_name varchar(100) NOT NULL, region_name varchar(100) NOT NULL, room_name varchar(100) NOT NULL, FOREIGN KEY(building_name, floor_name, region_name, room_name) REFERENCES Room(building_name, floor_name, region_name, room_name), PRIMARY KEY(building_name, floor_name, region_name, room_name) ); CREATE TABLE LectureHall( building_name varchar(100) NOT NULL, floor_name varchar(100) NOT NULL, region_name varchar(100) NOT NULL, room_name varchar(100) NOT NULL, FOREIGN KEY(building_name, floor_name, region_name, room_name) REFERENCES Room(building_name, floor_name, region_name, room_name), PRIMARY KEY(building_name, floor_name, region_name, room_name) ); CREATE TABLE Department( dept_name varchar(50) NOT NULL, start_date date NOT NULL, phone_no varchar(100) NOT NULL, chair int NOT NULL, manager int NOT NULL, PRIMARY KEY(dept_name) ); CREATE TABLE User( user_id int NOT NULL, name varchar(100) NOT NULL, PRIMARY KEY(user_id) ); CREATE TABLE UCIAffiliate( user_id int NOT NULL, njit_email_address varchar(350) NOT NULL, PRIMARY KEY(user_id), FOREIGN KEY(user_id) REFERENCES User(user_id) ); CREATE TABLE Visitor( user_id int NOT NULL, purpose varchar(350) NOT NULL, PRIMARY KEY(user_id), FOREIGN KEY(user_id) REFERENCES User(user_id) ); CREATE TABLE Faculty( user_id int NOT NULL, research_area varchar(50) NOT NULL, PRIMARY KEY(user_id), FOREIGN KEY(user_id) REFERENCES User(user_id) ); CREATE TABLE FacultyDepartment( user_id int NOT NULL, dept_name varchar(100) NOT NULL, PRIMARY KEY(user_id, dept_name), FOREIGN KEY(user_id) REFERENCES Faculty(user_id), FOREIGN KEY(dept_name) REFERENCES Department(dept_name) ); CREATE TABLE Staff( user_id int NOT NULL, employement_type varchar(100) NOT NULL, PRIMARY KEY(user_id), FOREIGN KEY(user_id) REFERENCES User(user_id) ); CREATE TABLE StaffDepartment( user_id int NOT NULL, dept_name varchar(100) NOT NULL, PRIMARY KEY(user_id), FOREIGN KEY(user_id) REFERENCES Staff(user_id), FOREIGN KEY(dept_name) REFERENCES Department(dept_name) ); CREATE TABLE Student( user_id int NOT NULL, dept_name varchar(100) NOT NULL, cgpa double, PRIMARY KEY(user_id), FOREIGN KEY(user_id) REFERENCES User(user_id), FOREIGN KEY(dept_name) REFERENCES Department(dept_name) ); CREATE TABLE Undergraduate( user_id int NOT NULL, PRIMARY KEY(user_id), FOREIGN KEY(user_id) REFERENCES Student(user_id) ); CREATE TABLE Graduate( user_id int NOT NULL, PRIMARY KEY(user_id), FOREIGN KEY(user_id) REFERENCES Student(user_id) ); CREATE TABLE ResearchAssistant( user_id int NOT NULL, advisor int NOT NULL, PRIMARY KEY(user_id), FOREIGN KEY(user_id) REFERENCES Graduate(user_id), FOREIGN KEY(advisor) REFERENCES Faculty(user_id) ); CREATE TABLE TeachingAssistant( user_id int NOT NULL, PRIMARY KEY(user_id), FOREIGN KEY(user_id) REFERENCES Graduate(user_id) ); CREATE TABLE Quarter( year int NOT NULL, session varchar(50), start_date date, end_date date, PRIMARY KEY(year, session) ); CREATE TABLE Classes( class_id int NOT NULL, year int NOT NULL, session varchar(50) NOT NULL, building_name varchar(100) NOT NULL, floor_name varchar(100) NOT NULL, region_name varchar(100) NOT NULL, room_name varchar(100) NOT NULL, teacher int NOT NULL, start_time int, end_time int, PRIMARY KEY(class_id), FOREIGN KEY(year, session) REFERENCES Quarter(year, session), FOREIGN KEY(teacher) REFERENCES Faculty(user_id), FOREIGN KEY(building_name, floor_name, region_name, room_name) REFERENCES LectureHall(building_name, floor_name, region_name, room_name) ); CREATE TABLE ClassEnrollment( class_id int NOT NULL, student_id int NOT NULL, PRIMARY KEY(class_id, student_id), FOREIGN KEY(class_id) REFERENCES Classes(class_id), FOREIGN KEY(student_id) REFERENCES Student(user_id) ); CREATE TABLE ClassDays( class_id int NOT NULL, day varchar(10) NOT NULL, PRIMARY KEY(class_id, day), FOREIGN KEY(class_id) REFERENCES Classes(class_id) ); CREATE TABLE TAAssignment( class_id int NOT NULL, ta_id int NOT NULL, PRIMARY KEY(class_id, ta_id), FOREIGN KEY(class_id) REFERENCES Classes(class_id), FOREIGN KEY(ta_id) REFERENCES TeachingAssistant(user_id) ); CREATE TABLE Seminars( seminar_id int NOT NULL, speaker int NOT NULL, host int NOT NULL, building_name varchar(100) NOT NULL, floor_name varchar(100) NOT NULL, region_name varchar(100) NOT NULL, room_name varchar(100) NOT NULL, start_time int NOT NULL, end_time int NOT NULL, PRIMARY KEY(seminar_id), FOREIGN KEY(host) REFERENCES Faculty(user_id), FOREIGN KEY(speaker) REFERENCES Visitor(user_id), FOREIGN KEY(building_name, floor_name, region_name, room_name) REFERENCES Room(building_name, floor_name, region_name, room_name) ); CREATE TABLE LocationInformation( user_id int NOT NULL, building_name varchar(100) NOT NULL, floor_name varchar(100) NOT NULL, region_name varchar(100) NOT NULL, room_name varchar(100) NOT NULL, start_time int, end_time int, PRIMARY KEY(user_id, building_name, floor_name, region_name, room_name, start_time, end_time), FOREIGN KEY(user_id) REFERENCES User(user_id), FOREIGN KEY(building_name, floor_name, region_name, room_name) REFERENCES Room(building_name, floor_name, region_name, room_name) ); INSERT INTO Building VALUES ('Facilities_Management_Building'), ('Building_Services_Bldg'), ('Env_Health__Safety_Services_Facility'), ('Mesa_Arts_Building'); INSERT INTO Floor VALUES ('Facilities_Management_Building','Floor1'), ('Building_Services_Bldg','Floor1'), ('Building_Services_Bldg','Floor2'), ('Env_Health__Safety_Services_Facility','Floor1'), ('Env_Health__Safety_Services_Facility','Floor2'), ('Env_Health__Safety_Services_Facility','Floor3'), ('Mesa_Arts_Building','Floor1'), ('Mesa_Arts_Building','Floor2'); INSERT INTO Region VALUES ('Facilities_Management_Building','Floor1','Region1','Sociology'), ('Building_Services_Bldg','Floor1','Region1','Sociology'), ('Building_Services_Bldg','Floor1','Region2','Sociology'), ('Building_Services_Bldg','Floor2','Region1','Computer_Science'), ('Env_Health__Safety_Services_Facility','Floor1','Region1','History'), ('Env_Health__Safety_Services_Facility','Floor1','Region2','Public_Health'), ('Env_Health__Safety_Services_Facility','Floor2','Region1','Computer_Science'), ('Env_Health__Safety_Services_Facility','Floor2','Region2','History'), ('Env_Health__Safety_Services_Facility','Floor3','Region1','Sociology'), ('Mesa_Arts_Building','Floor1','Region1','Sociology'), ('Mesa_Arts_Building','Floor2','Region1','Sociology'); INSERT INTO Room VALUES ('Facilities_Management_Building','Floor1','Region1','Room1',5), ('Facilities_Management_Building','Floor1','Region1','Room2',4), ('Building_Services_Bldg','Floor1','Region1','Room1',5), ('Building_Services_Bldg','Floor1','Region1','Room2',3), ('Building_Services_Bldg','Floor1','Region2','Room1',5), ('Building_Services_Bldg','Floor1','Region2','Room2',5), ('Building_Services_Bldg','Floor2','Region1','Room1',3), ('Building_Services_Bldg','Floor2','Region1','Room2',4), ('Env_Health__Safety_Services_Facility','Floor1','Region1','Room1',7), ('Env_Health__Safety_Services_Facility','Floor1','Region2','Room1',6), ('Env_Health__Safety_Services_Facility','Floor1','Region2','Room2',4), ('Env_Health__Safety_Services_Facility','Floor2','Region1','Room1',8), ('Env_Health__Safety_Services_Facility','Floor2','Region2','Room1',5), ('Env_Health__Safety_Services_Facility','Floor2','Region2','Room2',3), ('Env_Health__Safety_Services_Facility','Floor3','Region1','Room1',6), ('Mesa_Arts_Building','Floor1','Region1','Room1',7), ('Mesa_Arts_Building','Floor1','Region1','Room2',7), ('Mesa_Arts_Building','Floor2','Region1','Room1',5), ('Mesa_Arts_Building','Floor2','Region1','Room2',5); INSERT INTO Office VALUES ('Env_Health__Safety_Services_Facility','Floor1','Region1','Room1'), ('Env_Health__Safety_Services_Facility','Floor2','Region2','Room2'), ('Building_Services_Bldg','Floor1','Region1','Room1'), ('Building_Services_Bldg','Floor2','Region1','Room2'), ('Mesa_Arts_Building','Floor1','Region1','Room1'), ('Building_Services_Bldg','Floor2','Region1','Room1'); INSERT INTO Lab VALUES ('Building_Services_Bldg','Floor1','Region2','Room2'), ('Env_Health__Safety_Services_Facility','Floor2','Region2','Room1'), ('Mesa_Arts_Building','Floor1','Region1','Room2'), ('Env_Health__Safety_Services_Facility','Floor2','Region1','Room1'); INSERT INTO LectureHall VALUES ('Facilities_Management_Building','Floor1','Region1','Room1'), ('Facilities_Management_Building'