Important instructions: MUST read before answering the assignment.
Each student will submit the following two things in a “ZIP” folder: a. PDF file of their answer sheet. b. SQL queries written on a given template. c. Not following will result in zero points. 4. Points for each question are written in front of each question.
Assignments should NOT be handwritten. Students may use Microsoft Word, Google Doc, or Overleaf. Handwritten assignments will be returned without grading them.
The answer to each question must be detailed and you are not allowed to copy-paste.
Any type of cheating and/or plagiarism is NOT allowed. If your answer is identical to the answer/material available anywhere (since two people when answering even a very simple question, can never use the exact same word with a very high probability), this will result in zero.
please see following 3 files one camscnner has all questions, second data provided file name is sql_data_assigmnet3.txt and third template file whre you have to write 7 query for question 3 subparts (query 1to 7)
last few time expert did not work on good on assignment 1 and 2 which were simlar like this assignment 3 one. kindly request please generate data to MySQL as instruction told and write all right queries and show me video where and how did you generate and quires run in video so i can do my side after you submit to me.
CamScanner 11-29-2022 16.31 https://digital-camscanner.onelink.me/P3GL/g26ffx3k https://digital-camscanner.onelink.me/P3GL/g26ffx3k https://digital-camscanner.onelink.me/P3GL/g26ffx3k https://digital-camscanner.onelink.me/P3GL/g26ffx3k https://digital-camscanner.onelink.me/P3GL/g26ffx3k https://digital-camscanner.onelink.me/P3GL/g26ffx3k -- Question 1 -- Query is written below. -- Question 2 -- Query is written below. -- Question 3 -- Query is written below. -- Question 4 -- Query is written below. -- Question 5 -- Query is written below. -- Question 6 -- Query is written below. -- Question 7 -- Query is written below. -- Question 8 -- Query is written below. 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'