Use SQL DDL to specify the schema of the Student Registration System fragment. Specify SQL domains for attributes with small numbers of values, such as DeptId and Grade.

Question

What will be an ideal response?

Answer 1

```
CREATE TABLE Student (
Id INTEGER,
Name CHAR(20),
Address CHAR(50),
Status CHAR(10)
PRIMARY KEY (Id) )
```
```
CREATE TABLE Professor (
ProfId INTEGER,
Name CHAR(20),
DeptId Departments,
PRIMARY KEY (ProfId) )
```
```
CREATE TABLE Course (
CrsCode CHAR(6),
DeptId Departments,
CrsName CHAR(20),
Descr CHAR(100),
PRIMARY KEY (CrsCode),
UNIQUE (DeptId, CrsName) )
```
```
CREATE TABLE Transcript (
StudId INTEGER,
CrsCode CHAR(6),
Semester Semesters,
Grade Grades,
PRIMARY KEY (StudId, CrsCode, Semester),
FOREIGN KEY (StudId) REFERENCES Student (Id)
ON DELETE NO ACTION
ON UPDATE CASCADE,
FOREIGN KEY (CrsCode) REFERENCES Course (CrsCode)
ON DELETE NO ACTION
ON UPDATE CASCADE
FOREIGN KEY (CrsCode, Semester) REFERENCES
Teaching (CrsCode, Semester)
ON DELETE NO ACTION
ON UPDATE CASCADE )
```
```
CREATE TABLE Teaching (
ProfId INTEGER,
CrsCode CHAR(6),
Semester Semesters,
PRIMARY KEY (CrsCode, Semester),
FOREIGN KEY (ProfId) REFERENCES Professor(Id)
ON DELETE NO ACTION
ON UPDATE CASCADE,
FOREIGN KEY (CrsCode) REFERENCES Course (CrsCode)
ON DELETE SET NULL
ON UPDATE CASCADE )
```

The Grades domain is de?ned in Section 3.3.6. The domain of departments is de?ned below.

```
CREATE DOMAIN Departments CHAR(3)
CHECK ( VALUE I N (’CS’,’MAT’,’EE’,’MUS’,’PHY’,’CHE’) )
```
```
CREATE DOMAIN Semesters CHAR(6)
CHECK ( VALUE I N (’fall’, ’spring’, ’summer’) )
```