(a) Use the Structured Query Language to create enroll table with proper primary
ID: 3818395 • Letter: #
Question
(a) Use the Structured Query Language to create enroll table with proper primary key, foreign key, and according to the following constraints. Assumptions: the student table and course table have been already created for the database. (1) student# must be defined as an integer and existed in the student table; (2) course# must be defined as a small integer and a value in the course table; (3) semesterdate has a default value of fall 2015; (4) grade has to be equal to a value of A, or B, or C, or D, or F or W; (5) a student cannot enroll in the same class twice and/or more than 5 classes in the same semester; and (6) any course can only have up to 50 students enrolled in the course.
Explanation / Answer
CREATE TABLE Enroll (
EnrollID INTEGER ,
Student# INTEGER ,
StudentName CHAR(20) ,
Course# smallint ,
CourseName CHAR(20) ,
SemesterDate INTEGER DEFAULT 2015 ,
SemesterName CHAR(20) ,
Grade CHAR (1) ,
PRIMARY KEY ( EnrollID ) ,
FOREIGN KEY (Student# )
REFRENCES Student ( Student# ) ,
FOREIGN KEY ( Course# )
REFRENCES Course (Course# ) ,
CHECK ( Grade IN ( A , B , C , D , F , W ) ) ,
CHECk (( COUNT(*) FROM EnrollID ) <= 50 )
)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.