Write SQL statements to create the corresponding relations and capture as many o

Question

Write SQL statements to create the corresponding relations and capture as many of the constraints as possible. If you cannot capture some constraints, explain why.
A university database contains information about professors (identified by social security number, or SSN) and courses (identified by courseid). Professors teach courses; each of the following situations concerns the Teaches relationship set.

a. Professors can teach the same course in several semesters, and each offering must be recorded.

b. Professors can teach the same course in several semesters, and only the most recent such offering needs to be recorded. (Assume this condition applies in all subsequent questions.)

c. Every professor must teach some course.
d. Every professor teaches exactly one course (no more, no less).

e. Every professor teaches exactly one course (no more, no less), and every course must be taught by some professor.

please dont draw the ER diagram write the SQL statement for the cases to create the corresponding relations and capture as many of the constraints as possible. If you cannot capture some constraints, explain why.

Explanation / Answer

a. Professors can teach the same course in several semesters, and each offering must be recorded.

Professor ( SSNNameAddressOfficePhoneCourseID )

Course ( CourseIDNameTimeRoomSemesterNoSSN )

Semester ( SemesterNoYearSemester)

Schema

CREATE TABLE Professor

( SSN CHAR(20)         NOT NULL   UNIQUE,

Name CHAR(20)        NOT NULL,

Address CHAR(64)      NOT NULL,

OfficePhone CHAR(24) NOT NULL,

CourseID INTEGER     NOT NULL,

PRIMARY KEY(SSN)  

FOREIGN KEY(CourseID))

CREATE TABLE Course

( CourseID INTEGER      NOT NULL   UNIQUE,

Name CHAR(20)         NOT NULL,

Time DATETIME        NOT NULL,

Room CHAR(20)         NOT NULL,

SemesterNo INTEGER   NOT NULL,

SSN CHAR(20)          NOT NULL,

PRIMARY KEY(CourseID),

FOREIGN KEY(SemesterNo))

CREATE TABLE Semester

( SemesterNo INTEGER    NOT NULL   UNIQUE,

Year INTEGER           NOT NULL,

Semester CHAR(20)       NOT NULL,

PRIMARY KEY(SemesterNo))

b. Professors can teach the same course in several semesters, and only the most recent such offering needs to be recorded. (Assume this condition applies in all subsequent questions.)

Professor ( SSNNameAddressOfficePhoneCourseID )

Course ( CourseIDNameTimeRoomSemesterNoSSN )

Semester ( SemesterNoYearSemester)

Schema

CREATE TABLE Professor

( SSN CHAR(20)         NOT NULL   UNIQUE,

Name CHAR(20)        NOT NULL,

Address CHAR(64)      NOT NULL,

OfficePhone CHAR(24) NOT NULL,

CourseID INTEGER     NOT NULL,

PRIMARY KEY(SSN)  

FOREIGN KEY(CourseID))

CREATE TABLE Course

( CourseID INTEGER       NOT NULL   UNIQUE,

Name CHAR(20)          NOT NULL,

Time DATETIME         NOT NULL,

Room CHAR(20)          NOT NULL,

SemesterNo INTEGER     NOT NULL,

SSN CHAR(20)           NOT NULL,

PRIMARY KEY(CourseID),

FOREIGN KEY(SemesterNo))

CREATE TABLE Semester

( SemesterNo INTEGER    NOT NULL   UNIQUE,

Year INTEGER          NOT NULL,

Semester CHAR(20)      NOT NULL,

PRIMARY KEY(SemesterNo))

c. Every professor must teach some course.

Professor ( SSNNameAddressOfficePhoneCourseID )

Course ( CourseIDNameTimeRoomSemesterNoSSN )

Semester ( SemesterNoYearSemester)

Schema

CREATE TABLE Professor

( SSN CHAR(20)         NOT NULL   UNIQUE,

Name CHAR(20)        NOT NULL,

Address CHAR(64)      NOT NULL,

OfficePhone CHAR(24) NOT NULL,

CourseID INTEGER     NOT NULL,

PRIMARY KEY(SSN)  

FOREIGN KEY(CourseID))

CREATE TABLE Course

( CourseID INTEGER      NOT NULL   UNIQUE,

Name CHAR(20)         NOT NULL,

Time DATETIME         NOT NULL,

Room CHAR(20)         NOT NULL,

SemesterNo INTEGER    NOT NULL,

SSN CHAR(20)           NOT NULL,

PRIMARY KEY(CourseID),

FOREIGN KEY(SemesterNo))

CREATE TABLE Semester

( SemesterNo INTEGER    NOT NULL   UNIQUE,

Year INTEGER           NOT NULL,

Semester CHAR(20)      NOT NULL,

PRIMARY KEY(SemesterNo))

d. Every professor teaches exactly one course (no more, no less).

Professor ( SSNNameAddressOfficePhoneCourseID )

Course ( CourseIDNameTimeRoomSemesterNoSSN )

CREATE TABLE Professor

( SSN CHAR(20),

Name CHAR(20),

Address CHAR(64),

OfficePhone CHAR(24),

CourseID INTEGER,

PRIMARY KEY(SSN),

FOREIGN KEY(CourseID) REFERENCE Course

ON DELETE RESTRICT

ON UPDAE CASCADE );

CREATE TABLE Course

( CourseID INTEGER,

Name CHAR(20),

Time DATETIME,

Room CHAR(20),

SemesterNo INTEGER,

SSN CHAR(20),

PRIMARY KEY(CourseID));

e. Every professor teaches exactly one course (no more, no less), and every course must be taught by some professor.

Professor ( SSNNameAddressOfficePhoneCourseID )

Course ( CourseIDNameTimeRoomSemesterNoSSN )

CREATE TABLE Professor

( SSN CHAR(20),

Name CHAR(20),

Address CHAR(64),

OfficePhone CHAR(24),

CourseID INTEGER,

PRIMARY KEY(SSN),

FOREIGN KEY(CourseID) REFERENCE Course

ON DELETE RESTRICT

ON UPDAE CASCADE );

CREATE TABLE Course

( CourseID INTEGER,

Name CHAR(20),

Time DATETIME,

Room CHAR(20),

SemesterNo INTEGER,

SSN CHAR(20),

PRIMARY KEY(CourseID));

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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