Use SQL statements to create STUDENT, FACULTY, COURSE, CRSSECTION, REGISTRATION, ROOM, TERM, LOCATION, MAJOR, and DEPARTMENT tables in the Indo–US (IU) College database tables as given in Chapter 3. Use SQL*Plus commands or Notepad to debug your statements’ errors, if there are any.

Question

• Define a primary key constraint for each table. (Do not specify foreign keys yet.)
• Define NOT NULL, DEFAULT, UNIQUE, and CHECK constraints wherever appropriate.
Before running your statements, start spooling to a file named CH4LAB1A.LST. When all tables are created, stop spooling and print the spooled file.

Answer 1

CREATE TABLE student
```
(StudentId CHAR (5),
Last VARCHAR2 (15) CONSTRAINT student_last_nn NOT NULL,
First VARCHAR2 (15) CONSTRAINT student_first_nn NOT NULL,
Street VARCHAR2 (25),
City VARCHAR2 (15),
State CHAR (2) DEFAULT 'NJ',
Zip CHAR (5),
StartTerm CHAR (4),
BirthDate DATE,
FacultyId NUMBER (3),
MajorId NUMBER (3),
Phone CHAR (10),
CONSTRAINT student_studentid_pk PRIMARY KEY (StudentID));
```

CREATE TABLE faculty
```
(FacultyId NUMBER (3),
Name VARCHAR2 (15) CONSTRAINT faculty_name_nn NOT NULL,
RoomId NUMBER (2),
Phone CHAR (3) CONSTRAINT faculty_phone_uk UNIQUE,
DeptId NUMBER (1),
CONSTRAINT faculty_facultyid_pk PRIMARY KEY (FacultyId));
```

CREATE TABLE course
```
(CourseId VARCHAR2 (6),
Title VARCHAR2 (20) CONSTRAINT course_title_uk UNIQUE,
Credits NUMBER (1) CONSTRAINT course_credits_ck
CHECK ((credits>=0) AND (credits<=4)),
PreReq VARCHAR2(6),
CONSTRAINT course_courseid_pk PRIMARY KEY (CourseId));
```

CREATE TABLE crssection
```
(CsId NUMBER (4),
CourseId VARCHAR2 (6) CONSTRAINT crssection_courseid_nn NOT NULL,
Section CHAR (2) CONSTRAINT crsssection_section_nn NOT NULL,
TermId CHAR (4) CONSTRAINT crssection_termid_nn NOT NULL,
FacultyId NUMBER (3),
Day VARCHAR2 (2),
StartTime VARCHAR2 (5),
EndTime VARCHAR2 (5),
RoomId NUMBER (2),
MaxCount NUMBER(2) CONSTRAINT crssection_maxcount_ck
CHECK (MaxCount > 0),
CONSTRAINT crssection_csid_pk PRIMARY KEY (CsId));
```

CREATE TABLE registration
```
(StudentId CHAR (5),
CsId NUMBER (4),
Midterm CHAR CONSTRAINT registration_midterm_ck
CHECK (MidTerm IN ('A', 'B', 'C', 'D', 'F', 'W')),
Final CHAR CONSTRAINT registration_final_ck
CHECK (final IN ('A', 'B', 'C', 'D', 'I', 'F', 'W')),
RegStatus CHAR CONSTRAINT reg_status_ck
CHECK (RegStatus in ('X', 'R', 'W')),
CONSTRAINT registration_stuid_csid_pk
PRIMARY KEY (StudentId, CsId));
```

CREATE TABLE room
```
(RoomType CHAR,
RoomDesc VARCHAR2 (9),
CONSTRAINT room_roomtype_pk PRIMARY KEY (RoomType));
```

CREATE TABLE term
```
(TermId CHAR (4),
TermDesc VARCHAR2 (11),
StartDate DATE,
EndDate DATE,
CONSTRAINT term_termid_pk PRIMARY KEY (TermId));
```

CREATE TABLE location
```
(RoomId NUMBER (2),
Building VARCHAR2 (7) CONSTRAINT location_bulding_nn NOT NULL,
RoomNo CHAR (3) CONSTRAINT location_roomno_nn NOT NULL,
Capacity NUMBER (2) CONSTRAINT location_capacity_check
CHECK(Capacity>0),
RoomType CHAR,
CONSTRAINT location_roomid_pk PRIMARY KEY (RoomId),
CONSTRAINT location_roomno_uk UNIQUE (RoomNo));
```

CREATE TABLE major
```
(MajorId NUMBER (3),
MajorDesc VARCHAR2 (25),
CONSTRAINT major_majored_pk PRIMARY KEY (MajorId));
```

CREATE TABLE department
```
(DeptId NUMBER (1),
DeptName VARCHAR2 (20),
FacultyId NUMBER (3),
CONSTRAINT department_deptid_pk PRIMARY KEY (DeptId));

```