1. Write the SQL statements that create the Student Enrollment Database as descr

Question

1. Write the SQL statements that create the Student Enrollment Database as described in the following table Allow Null Remarks No PK No No No No PK No No No Yes No PK No No No PK No No No No No Table Column Name Data Type ize COURSE NUM COURSE TITLE CREDIT HR DEP COD Character Character Integer Character Character Character Integer Integer Character Inte Fixed 8 Up to 40 Course Fixed S Fixed 5 Up to 40 FK Department DEP CODE DEP NAME ST ID FK FK FK Enrollment CRN GRADE INSTRUCTOR ID Fixed 2 Instructor INSTRUCTOR NAME Character Up to 30 DEP CODE CRN COURSE NUM SEMESTER INSTRUCTOR ID MAX ENROLLMENT ROOM DAYS TIME ST ID ST NAME ST GPA ST DOB ST ADDRESS DEP CODE Character Fixed 5 Inte Character Fixed 8 Character Fixed 15 Inte Inte Character Fixed 8 Character Fixed 4 Character Fixed 20 Integer Character Up to 30 Floatin Date Character Up to 50 Character Fixed 5 FK FK Offering 0 No No PK No Yes No Yes No Student FK

Explanation / Answer

Table1:
CREATE TABLE DEPARTMENT(
DEP_CODE VARCHAR2(5) PRIMARY KEY CHECK (DATALENGTH(DEP_CODE) =5),
DEP_NAME VARCHAR2(40) NOT NULL
)

Table2:
CREATE TABLE COURSE(
COURSE_NUM VARCHAR(8) PRIMARY KEY CHECK (DATALENGTH(COURSE_NUM) = 8),
COURSE_TITLE VARCHAR(40) NOT NULL,
CREDIT_HR NUMBER NOT NULL,
DEP_CODE VARCHAR(5) NOT NULL CHECK (DATALENGTH(COURSE_NUM) = 5),
FOREIGN KEY (DEP_CODE) REFERENCES DEPARTMENT(DEP_CODE)
)

Table3:
CREATE TABLE INSTRUCTOR(
INSTRUCTOR_ID NUMBER PRIMARY KEY,
INSTRUCTOR_NAME VARCHAR(30) NOT NULL,
DEP_CODE VARCHAR(5) NOT NULL CHECK (DATALENGTH(COURSE_NUM) = 5),
FOREIGN KEY (DEP_CODE) REFERENCES DEPARTMENT(DEP_CODE)
)

Table4:
CREATE TABLE STUDENT(
ST_ID NUMBER PRIMARY KEY,
ST_NAME VARCHAR(30) NOT NULL,
ST_GPA FLOAT,
ST_DOB DATE NOT NULL,
ST_ADDRESS VARCHAR(50),
DEP_CODE VARCHAR(5) NOT NULL CHECK (DATALENGTH(COURSE_NUM) = 5),
FOREIGN KEY (DEP_CODE) REFERENCES DEPARTMENT(DEP_CODE)
)

Table5:
CREATE TABLE OFFERING(
CRN NUMBER PRIMARY KEY,
COURSE_NUM VARCHAR(8) CHECK (DATALENGTH(COURSE_NUM)=8) NOT NULL,
SEMESTER VARCHAR(15) CHECK (DATALENGTH(SEMESTER)=15) NOT NULL,
INSTRUCTOR_ID NUMBER NOT NULL,
MAX_ENROLLMENT NUMBER NOT NULL,
ROOM VARCHAR(8) CHECK (DATALENGTH(ROOM)=8) NOT NULL,
DAYS VARCHAR(4) CHECK (DATALENGTH(DAYS)=4) NOT NULL,
TIME VARCHAR(20) CHECK (DATALENGTH(TIME) = 20) NOT NULL,
FOREIGN KEY (COURSE_NUM, INSTRUCTOR_ID) REFERENCES COURSE(COURSE_NUM), INSTRUCTOR(INSTRUCTOR_ID)
)

Table6:
CREATE TABLE ENROLLMENT(
ST_ID NUMBER NOT NULL,
CRN NUMBER NOT NULL,
GRADE VARCHAR(2) CHECK (DATALENGTH(GRADE) = 2),
FOREIGN KEY (ST_ID, CRN) REFERENCES STUDENT(ST_ID), OFFERING(CRN)
)

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

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