need to specify how the tables should be linked and what referential constraints
ID: 3623250 • Letter: N
Question
need to specify how the tables should be linked and what referential constraints should be in place. Also, you need to provide specifications on the queries should be designed to produce the reports desired.
Tables:
STUDENT
StudentID*
StudentName
DegreeProgram
DegreeLevel
INSTRUCTOR
InstructorID*
InstructorName
InstructorTitle
ClassID**
CLASS
ClassID*
InstructorID**
ClassName
Location
ClassYear
DEPARTMENT
DeptID*
ClassID**
InstructorID**
DeptName
REGISTRATION
RegistrationID*
StudentID**
ClassID**
Withdraw
Semester
RegistrationYear
GRADE
GradeID*
StudentID**
ClassID**
ClassGrade
OverallGrade
Please Note that Primary Key is always listed first with a single “*”
Then Foreign Key is listed with double “**”
I also put these in order in the table.
Reports requested are:
Thanks!
STUDENT
StudentID*
StudentName
DegreeProgram
DegreeLevel
Explanation / Answer
Create TABLE STUDENT
(
StudentId int NOT NULL PRIMARYKEY,
StudentName varchar(30),
DegreeProgram varchar(10),
DegreeLevel int
)
CREATE TABLE INSTRUCTOR
(
InstructorID int NOT NULL PRIMARYKEY,
InstructorName varchar(30),
InstructorTitle varchar(30),
ClassID int
PRIMARY KEY (InstructorID),
FOREIGN KEY (ClassId) REFERENCES
Class (ClassId)
)
CREATE TABLE Class
(
ClassId int NOT NULL PRIMARYKEY,
InstructorID int,
PRIMARY KEY (ClassId),
FOREIGN KEY (InstructorID) REFERENCES
INSTRUCTOR (InstructorID)
ClassName varchar(30),
Location varchar(30),
)
CREATE TABLE Department
(
DeptId int NOT NULL PRIMARYKEY,
DeptName varchar(20),
ClassID int
PRIMARY KEY (DeptId),
FOREIGN KEY (ClassId) REFERENCES
Class (ClassId),
InstructorID int,
PRIMARY KEY (ClassId),
FOREIGN KEY (InstructorID) REFERENCES
INSTRUCTOR (InstructorID)
)
CREATE TABLE REGISTRATION
(
RegistrationID int NOT NULL PRIMARYKEY,
StudentId int
PRIMARY KEY (RegistrationID),
FOREIGN KEY (StudentID) REFERENCES
STUDENT (StudentID),
ClassID int
PRIMARY KEY (RegistrationID),
FOREIGN KEY (ClassId) REFERENCES
Class (ClassId),
Semester int,
Withdraw int,
RegistrationYear int
)
StudentId int
PRIMARY KEY (GradeID),
FOREIGN KEY (StudentID) REFERENCES
PRIMARY KEY (GradeID),
FOREIGN KEY (ClassId) REFERENCES
Query
Select from c.location, in. InstructorName c.classname student s
Inner join REGISTRATION r on r. StudentId=s. StudentId
Inner join Class c on c. ClassId =r. ClassID
Inner join INSTRUCTOR in on in.InstructorID = c. InstructorID
Where r. Semester = 2
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.