Here is the school database script /* Q1 */ CREATE DATABASE University; USE Univ
ID: 3711536 • Letter: H
Question
Here is the school database script /* Q1 */ CREATE DATABASE University;USE University; /* Q2 */ IF OBJECT_ID (N'dbo.Department', N'U') IS NOT NULL DROP TABLE dbo.Department; CREATE TABLE dbo.Department (DepartmentID varchar(10) not null PRIMARY KEY, Name varchar(30) not null, Budget varchar(15) null, StartDate date null, Administrator varchar(30) not null );
/* Q3 */ IF OBJECT_ID (N'dbo.Person', N'U') IS NOT NULL DROP TABLE dbo.Person; CREATE TABLE dbo.Person (PersonID varchar(10) not null PRIMARY KEY, LastName varchar(15) not null, FirstName varchar(15) not null, HireDate date null, EnrollmentDate date null, );
/* Q4 */ IF OBJECT_ID (N'dbo.OnsiteCourse', N'U') IS NOT NULL DROP TABLE dbo.OnsiteCourse; CREATE TABLE dbo.OnsiteCourse (CourseID varchar(10) not null PRIMARY KEY, Location varchar(15) null, Days varchar(20) null, Time varchar(20) null /* or: Time time null */ );
/* Q5 */ IF OBJECT_ID (N'dbo.OnlineCourse', N'U') IS NOT NULL DROP TABLE dbo.OnlineCourse; CREATE TABLE dbo.OnlineCourse (CourseID varchar(10) not null PRIMARY KEY, URL varchar(75) null );
/* Q6 */ IF OBJECT_ID (N'dbo.StudentGrade', N'U') IS NOT NULL DROP TABLE dbo.StudentGrade; CREATE TABLE dbo.StudentGrade (EnrollmentID varchar(10) not null PRIMARY KEY, CourseID varchar(10) not null, StudentID varchar(10) not null, Grade varchar(2) null /* can be A, A-, etc. */ );
/* Q7 */ IF OBJECT_ID (N'dbo.CourseInstructor', N'U') IS NOT NULL DROP TABLE dbo.CourseInstructor; CREATE TABLE dbo.CourseInstructor (CourseID varchar(10) not null, PersonID varchar(10) not null PRIMARY KEY );
/* Q8 */ IF OBJECT_ID (N'dbo.Course', N'U') IS NOT NULL DROP TABLE dbo.Course; CREATE TABLE dbo.Course (CourseID varchar(10) not null PRIMARY KEY, Title varchar(30) not null, Credits int null, DepartmentID varchar(10) null );
/* Q9 */ IF OBJECT_ID (N'dbo.OfficeAssignment', N'U') IS NOT NULL DROP TABLE dbo.OfficeAssignment; CREATE TABLE dbo.OfficeAssignment (InstructorID varchar(10) not null PRIMARY KEY, Location varchar(15) null, Timestamp datetime null, );
/* Q10 */ ALTER TABLE Course ADD CourseID varchar(10) not null PRIMARY KEY;
/* Q11 */ ALTER TABLE OnsiteCourse ADD Foreign Key (CourseID) References dbo.Course(CourseID);
/* Q12 */ ALTER TABLE OnlineCourse ADD Foreign Key (CourseID) References dbo.Course(CourseID);
/* Q13 */ ALTER TABLE StudentGrade ADD Foreign Key (CourseID) References dbo.Course(CourseID);
/* Q14 */ ALTER TABLE StudentGrade ADD Foreign Key (StudentID) References dbo.Person(PersonID);
/* Q15 */ ALTER TABLE CourseInstructor ADD Foreign Key (CourseID) References dbo.Course(CourseID);
/* Q16 */ ALTER TABLE CourseInstructor ADD Foreign Key (PersonID) References dbo.Person(PersonID);
/* Q17 */ ALTER TABLE Course ADD Foreign Key (DepartmentID) References dbo.Department(DepartmentID);
/* Q18 */ ALTER TABLE OfficeAssignment ADD Foreign Key (InstructorID) References dbo.Person(PersonID);
/* Q19 */ INSERT INTO Person VALUES ('1234', 'Smith', 'Anne', '2009-08-12', '2013-08-12'), ('2345', 'Johnson', 'Beth', '2013-08-12', '2013-08-12'), ('3456', 'Williams', 'Cara', '2013-08-12', '2013-08-12'), ('4567', 'Jones', 'Dolly', '2013-08-12', '2013-08-12'), ('5678', 'Brown', 'Elena', '2013-08-12', '2013-08-12'), ('6789', 'Davis', 'Farah', '2013-08-12', '2013-08-12'), ('7891', 'Miller', 'Ginny', '2013-08-12', '2013-08-12'), ('8910', 'Wilson', 'Hedwig', '2013-08-12', '2013-08-12'), ('9012', 'Moore', 'India', '2013-08-12', '2013-08-12'), ('1111', 'Taylor', 'Jackie', '2013-08-12', '2013-08-12');
/* Q20 */ INSERT INTO Department VALUES ('1', 'EECE', '123000', '2013-08-12', 'Ananya'), ('2', 'MED', '123000', '2013-08-12', 'Bethany'), ('3', 'CS', '123000', '2013-08-12', 'Carlina'), ('4', 'SPAN', '123000', '2013-08-12', 'Dalton'), ('5', 'PSYCH', '123000', '2013-08-12', 'Elle');
/* Q21 */ INSERT INTO Course VALUES ('1', 'Math', '3', '1'), ('2', 'Math', '3', '1'), ('3', 'Math', '3', '1'), ('4', 'Math', '3', '1'), ('5', 'Math', '3', '1'), ('6', 'Math', '3', '1'), ('7', 'Math', '3', '1'), ('8', 'Math', '3', '1'), ('9', 'Math', '3', '1'), ('10', 'Math', '3', '1');
/* Q22 */ INSERT INTO OnlineCourse VALUES ('1', 'http'), ('2', 'http'), ('3', 'http'), ('4', 'http');
/* Q23 */ INSERT INTO OnsiteCourse VALUES ('5', 'school', 'Monday', '4 pm'), ('6', 'school', 'Monday', '4 pm'), ('7', 'school', 'Monday', '4 pm'), ('8', 'school', 'Monday', '4 pm'), ('9', 'school', 'Monday', '4 pm'), ('10', 'school', 'Monday', '4 pm');
/* Q24 */ INSERT INTO CourseInstructor VALUES ('1', '1234'), ('2', '2345'), ('3', '3456'), ('4', '4567'), ('5', '5678'), ('6', '6789'), ('7', '7891'), ('8', '8910'), ('9', '9012');
/* Q25 */ INSERT INTO OfficeAssignment VALUES ('1234', 'school', '20180618 10:34:09 AM'), ('2345', 'school', '20180618 10:34:09 AM'), ('3456', 'school', '20180618 10:34:09 AM'), ('4567', 'school', '20180618 10:34:09 AM'), ('5678', 'school', '20180618 10:34:09 AM'), ('6789', 'school', '20180618 10:34:09 AM'), ('7891', 'school', '20180618 10:34:09 AM'), ('8910', 'school', '20180618 10:34:09 AM'), ('9012', 'school', '20180618 10:34:09 AM');
/* Q26 */ INSERT INTO StudentGrade VALUES ('1', '1', '2345', 'A'), ('2', '1', '2345', 'A'), ('3', '1', '2345', 'A'), ('4', '1', '2345', 'A'), ('5', '1', '2345', 'A'), ('6', '1', '2345', 'A'), ('7', '1', '2345', 'A'), ('8', '1', '2345', 'A'), ('9', '1', '2345', 'A'), ('10', '1', '2345', 'A'), ('11', '1', '2345', 'A'), ('12', '1', '2345', 'A'), ('13', '1', '2345', 'A'), ('14', '1', '2345', 'A'), ('15', '1', '2345', 'A'), ('16', '1', '2345', 'A'), ('17', '1', '2345', 'A'), ('18', '1', '2345', 'A'), ('19', '1', '2345', 'A'), ('20', '1', '2345', 'A'), ('21', '1', '2345', 'A'), ('22', '1', '2345', 'A'), ('23', '1', '2345', 'A'), ('24', '1', '2345', 'A'), ('25', '1', '2345', 'A'), ('26', '1', '2345', 'A'), ('27', '1', '2345', 'A'), ('28', '1', '2345', 'A'), ('29', '1', '2345', 'A'), ('30', '1', '2345', 'A'), ('31', '1', '2345', 'A'), ('32', '1', '2345', 'A'), ('33', '1', '2345', 'A'), ('34', '1', '2345', 'A'), ('35', '1', '2345', 'A'), ('36', '1', '2345', 'A'), ('37', '1', '2345', 'A'), ('38', '1', '2345', 'A'), ('39', '1', '2345', 'A'), ('40', '1', '2345', 'A'); /* Q1 */ CREATE DATABASE University;
USE University; /* Q2 */ IF OBJECT_ID (N'dbo.Department', N'U') IS NOT NULL DROP TABLE dbo.Department; CREATE TABLE dbo.Department (DepartmentID varchar(10) not null PRIMARY KEY, Name varchar(30) not null, Budget varchar(15) null, StartDate date null, Administrator varchar(30) not null );
/* Q3 */ IF OBJECT_ID (N'dbo.Person', N'U') IS NOT NULL DROP TABLE dbo.Person; CREATE TABLE dbo.Person (PersonID varchar(10) not null PRIMARY KEY, LastName varchar(15) not null, FirstName varchar(15) not null, HireDate date null, EnrollmentDate date null, );
/* Q4 */ IF OBJECT_ID (N'dbo.OnsiteCourse', N'U') IS NOT NULL DROP TABLE dbo.OnsiteCourse; CREATE TABLE dbo.OnsiteCourse (CourseID varchar(10) not null PRIMARY KEY, Location varchar(15) null, Days varchar(20) null, Time varchar(20) null /* or: Time time null */ );
/* Q5 */ IF OBJECT_ID (N'dbo.OnlineCourse', N'U') IS NOT NULL DROP TABLE dbo.OnlineCourse; CREATE TABLE dbo.OnlineCourse (CourseID varchar(10) not null PRIMARY KEY, URL varchar(75) null );
/* Q6 */ IF OBJECT_ID (N'dbo.StudentGrade', N'U') IS NOT NULL DROP TABLE dbo.StudentGrade; CREATE TABLE dbo.StudentGrade (EnrollmentID varchar(10) not null PRIMARY KEY, CourseID varchar(10) not null, StudentID varchar(10) not null, Grade varchar(2) null /* can be A, A-, etc. */ );
/* Q7 */ IF OBJECT_ID (N'dbo.CourseInstructor', N'U') IS NOT NULL DROP TABLE dbo.CourseInstructor; CREATE TABLE dbo.CourseInstructor (CourseID varchar(10) not null, PersonID varchar(10) not null PRIMARY KEY );
/* Q8 */ IF OBJECT_ID (N'dbo.Course', N'U') IS NOT NULL DROP TABLE dbo.Course; CREATE TABLE dbo.Course (CourseID varchar(10) not null PRIMARY KEY, Title varchar(30) not null, Credits int null, DepartmentID varchar(10) null );
/* Q9 */ IF OBJECT_ID (N'dbo.OfficeAssignment', N'U') IS NOT NULL DROP TABLE dbo.OfficeAssignment; CREATE TABLE dbo.OfficeAssignment (InstructorID varchar(10) not null PRIMARY KEY, Location varchar(15) null, Timestamp datetime null, );
/* Q10 */ ALTER TABLE Course ADD CourseID varchar(10) not null PRIMARY KEY;
/* Q11 */ ALTER TABLE OnsiteCourse ADD Foreign Key (CourseID) References dbo.Course(CourseID);
/* Q12 */ ALTER TABLE OnlineCourse ADD Foreign Key (CourseID) References dbo.Course(CourseID);
/* Q13 */ ALTER TABLE StudentGrade ADD Foreign Key (CourseID) References dbo.Course(CourseID);
/* Q14 */ ALTER TABLE StudentGrade ADD Foreign Key (StudentID) References dbo.Person(PersonID);
/* Q15 */ ALTER TABLE CourseInstructor ADD Foreign Key (CourseID) References dbo.Course(CourseID);
/* Q16 */ ALTER TABLE CourseInstructor ADD Foreign Key (PersonID) References dbo.Person(PersonID);
/* Q17 */ ALTER TABLE Course ADD Foreign Key (DepartmentID) References dbo.Department(DepartmentID);
/* Q18 */ ALTER TABLE OfficeAssignment ADD Foreign Key (InstructorID) References dbo.Person(PersonID);
/* Q19 */ INSERT INTO Person VALUES ('1234', 'Smith', 'Anne', '2009-08-12', '2013-08-12'), ('2345', 'Johnson', 'Beth', '2013-08-12', '2013-08-12'), ('3456', 'Williams', 'Cara', '2013-08-12', '2013-08-12'), ('4567', 'Jones', 'Dolly', '2013-08-12', '2013-08-12'), ('5678', 'Brown', 'Elena', '2013-08-12', '2013-08-12'), ('6789', 'Davis', 'Farah', '2013-08-12', '2013-08-12'), ('7891', 'Miller', 'Ginny', '2013-08-12', '2013-08-12'), ('8910', 'Wilson', 'Hedwig', '2013-08-12', '2013-08-12'), ('9012', 'Moore', 'India', '2013-08-12', '2013-08-12'), ('1111', 'Taylor', 'Jackie', '2013-08-12', '2013-08-12');
/* Q20 */ INSERT INTO Department VALUES ('1', 'EECE', '123000', '2013-08-12', 'Ananya'), ('2', 'MED', '123000', '2013-08-12', 'Bethany'), ('3', 'CS', '123000', '2013-08-12', 'Carlina'), ('4', 'SPAN', '123000', '2013-08-12', 'Dalton'), ('5', 'PSYCH', '123000', '2013-08-12', 'Elle');
/* Q21 */ INSERT INTO Course VALUES ('1', 'Math', '3', '1'), ('2', 'Math', '3', '1'), ('3', 'Math', '3', '1'), ('4', 'Math', '3', '1'), ('5', 'Math', '3', '1'), ('6', 'Math', '3', '1'), ('7', 'Math', '3', '1'), ('8', 'Math', '3', '1'), ('9', 'Math', '3', '1'), ('10', 'Math', '3', '1');
/* Q22 */ INSERT INTO OnlineCourse VALUES ('1', 'http'), ('2', 'http'), ('3', 'http'), ('4', 'http');
/* Q23 */ INSERT INTO OnsiteCourse VALUES ('5', 'school', 'Monday', '4 pm'), ('6', 'school', 'Monday', '4 pm'), ('7', 'school', 'Monday', '4 pm'), ('8', 'school', 'Monday', '4 pm'), ('9', 'school', 'Monday', '4 pm'), ('10', 'school', 'Monday', '4 pm');
/* Q24 */ INSERT INTO CourseInstructor VALUES ('1', '1234'), ('2', '2345'), ('3', '3456'), ('4', '4567'), ('5', '5678'), ('6', '6789'), ('7', '7891'), ('8', '8910'), ('9', '9012');
/* Q25 */ INSERT INTO OfficeAssignment VALUES ('1234', 'school', '20180618 10:34:09 AM'), ('2345', 'school', '20180618 10:34:09 AM'), ('3456', 'school', '20180618 10:34:09 AM'), ('4567', 'school', '20180618 10:34:09 AM'), ('5678', 'school', '20180618 10:34:09 AM'), ('6789', 'school', '20180618 10:34:09 AM'), ('7891', 'school', '20180618 10:34:09 AM'), ('8910', 'school', '20180618 10:34:09 AM'), ('9012', 'school', '20180618 10:34:09 AM');
/* Q26 */ INSERT INTO StudentGrade VALUES ('1', '1', '2345', 'A'), ('2', '1', '2345', 'A'), ('3', '1', '2345', 'A'), ('4', '1', '2345', 'A'), ('5', '1', '2345', 'A'), ('6', '1', '2345', 'A'), ('7', '1', '2345', 'A'), ('8', '1', '2345', 'A'), ('9', '1', '2345', 'A'), ('10', '1', '2345', 'A'), ('11', '1', '2345', 'A'), ('12', '1', '2345', 'A'), ('13', '1', '2345', 'A'), ('14', '1', '2345', 'A'), ('15', '1', '2345', 'A'), ('16', '1', '2345', 'A'), ('17', '1', '2345', 'A'), ('18', '1', '2345', 'A'), ('19', '1', '2345', 'A'), ('20', '1', '2345', 'A'), ('21', '1', '2345', 'A'), ('22', '1', '2345', 'A'), ('23', '1', '2345', 'A'), ('24', '1', '2345', 'A'), ('25', '1', '2345', 'A'), ('26', '1', '2345', 'A'), ('27', '1', '2345', 'A'), ('28', '1', '2345', 'A'), ('29', '1', '2345', 'A'), ('30', '1', '2345', 'A'), ('31', '1', '2345', 'A'), ('32', '1', '2345', 'A'), ('33', '1', '2345', 'A'), ('34', '1', '2345', 'A'), ('35', '1', '2345', 'A'), ('36', '1', '2345', 'A'), ('37', '1', '2345', 'A'), ('38', '1', '2345', 'A'), ('39', '1', '2345', 'A'), ('40', '1', '2345', 'A');
Explanation / Answer
14. there is no student table.
15 ,16,17. In course table courseId is primary key and in courseInstructor table courseId and personId is foreign key.
The person or insructor is linked to course. the courseId assigned to Instructor taught by him.her.
The departmentId is primary key in department and foreign key in course table. So it is linked as the course is associted with department.
19. INSERT INTO Person
VALUES
(1, 'Smith', 'Anne', '2018-04-19', 2018-04-19'),
(2, 'Johnson', 'Beth', '2018-04-19', '2018-04-19'),
(3, 'Williams', 'Cara', '2018-04-19', '2018-04-19'),
(4, 'Jones', 'Dolly', '2018-04-18', '2018-04-19'),
(5, 'Brown', 'Elena', '2018-04-19', '2018-01-19'),
(6, 'Davis', 'Farah', '2018-04-15', '2018-04-16'),
(7, 'Miller', 'Ginny', '2018-04-19','2018-04-19'),
(8, 'Wilson', 'Hedwig', '2018-04-19', '2018-04-13'),
(9, 'singh', 'maya', '2018-04-19', '2018-04-01'),
(10, 'Taylor', 'Jackie', '2018-01-12', '2018-01-12');
Script you added for inserting data into various tables are correct but if Id is in Int dont put in quotes.
else the last question your bonus
here is stored procedure:
CREATE PROCEDURE insert_person
@id int,@lname nvarchar,@fname nvarchar,@hire nvarchar,@enroll
AS
insert into Person (PersonId,LastName,Firstname,HireDate,EnrollmentDate)values(@id,@lname,@fname,@hire,@enroll)