2. (TOTAL: 10 points) Show SQL data definition statements (create table) for the

Question

2. (TOTAL: 10 points) Show SQL data definition statements (create table) for the following t relations: STUDENT (SSN,firstName, lastName, age) COURSE (course ID, course_name, SSN, grade). Enforce the following integrity constraints: SSN is a primary key in relation STUDENT - course ID is a primary key in relation COURSE - age - age must be strictly greater than 15 and strictly less than 100 - grade attribute must be A, B, C, D, or F all values for the attribute SSN in the relation COURSE must exist in the relation STUDENT

Explanation / Answer

STUDENT TABLE

CREATE TABLE STUDENT (
SSN int PRIMARY KEY,
firstName varchar(255) ,
lastName varchar(255),
age int CHECK (age>15 AND age<100)
);

COURSE TABLE

CREATE TABLE COURSE (
course_ID int PRIMARY KEY,
courseName varchar(255) ,
SSN int,
grade char(1) CHECK (grade='A' OR grade='B' OR grade='C' OR grade='D' OR grade='F'),
FOREIGN KEY (SSN) REFERENCES STUDENT(SSN)
);

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.