Consider the E-R diagram depicted in Figure 4.38. Write down the corresponding relational schema using SQL. Include all keys and other applicable constraints.

Question

What will be an ideal response?

Answer 1

```
CREATE TABLE Student (
Id INTEGER,
Name CHAR(20),
Major CHAR(3),
PRIMARY KEY (Id, Major) )
```
```
CREATE TABLE Class (
Semester CHAR(5),
CrsCode CHAR(6),
Room CHAR(15),
PRIMARY KEY (Semester, CrsCode) )
```
```
CREATE TABLE Takes (
StudId INTEGER NOT NULL,
Semester CHAR(5) NOT NULL,
CrsCode CHAR(6) NOT NULL,
FOREIGN KEY (Semester,CrsCode) REFERENCES Class,
FOREIGN KEY (StudId) REFERENCES Student (Id) )
```