Professor ( SSN Name Address Officephone Courseid )

高等資料庫管理

指導教授:周清江

學生:鄭文瑄

陳佳妏

日期:93/3/10

2.2

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 theTeaches relationship set. For each situation, draw an ER diagram that describes it (assuming no further constraints hold).

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

Table

Professor ( SSN，Name，Address，OfficePhone，CourseID )

Course ( CourseID，Name，Time，Room，SemesterNo，SSN )

Semester ( SemesterNo，Year，Semester)

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))

2. 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.)

Table

Professor ( SSN，Name，Address，OfficePhone，CourseID )

Course ( CourseID，Name，Time，Room，SemesterNo，SSN )

Semester ( SemesterNo，Year，Semester)

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))

3. Every professor must teach some course.

Table

Professor ( SSN，Name，Address，OfficePhone，CourseID )

Course ( CourseID，Name，Time，Room，SemesterNo，SSN )

Semester ( SemesterNo，Year，Semester)

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))

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

Table

Professor ( SSN，Name，Address，OfficePhone，CourseID )

Course ( CourseID，Name，Time，Room，SemesterNo，SSN )

CREATE TABLE Professor

(SSN CHAR(20),

Name CHAR(20),

AddressCHAR(64),

OfficePhoneCHAR(24),

CourseID INTEGER,

PRIMARY KEY(SSN),

FOREIGN KEY(CourseID) REFERENCE Course

ON DELETERESTRICT

ON UPDAE CASCADE );

CREATE TABLECourse

( CourseID INTEGER,

Name CHAR(20),

Time DATETIME,

Room CHAR(20),

SemesterNo INTEGER,

SSN CHAR(20),

PRIMARY KEY(CourseID));

5. Every professor teaches exactly one course (no more, no less), and every

course must be taught by some professor.

Table

Professor ( SSN，Name，Address，OfficePhone，CourseID )

Course ( CourseID，Name，Time，Room，SemesterNo，SSN )

CREATE TABLE Professor

(SSN CHAR(20),

Name CHAR(20),

AddressCHAR(64),

OfficePhoneCHAR(24),

CourseID INTEGER,

PRIMARY KEY(SSN),

FOREIGN KEY(CourseID) REFERENCE Course

ON DELETERESTRICT

ON UPDAE CASCADE );

CREATE TABLECourse

( CourseID INTEGER,

Name CHAR(20),

Time DATETIME,

Room CHAR(20),

SemesterNo INTEGER,

SSN CHAR(20),

PRIMARY KEY(CourseID));

6. Now suppose that certain courses can be taught by a team of professors

jointly, but it is possible that no one professor in a team can tech the

course. Model this situation, introducing additional entity sets and

relationship sets if necessary.

Table

Professor ( SSN，Name，Address，OfficePhone)

Professor_Team(Team_ID,Team_Name)

Course(Course_ID,Course_Name)

Member_of(Team_ID,SSN)

Team_Teach(Team_ID,Course_ID,Semester)

Pro_Teach(SSN,Course_ID,Semester)

CREATE TABLE Professor

( SSN CHAR(20),

Name CHAR(20),

Address CHAR(64),

OfficePhone CHAR(24),

PRIMARY KEY(SSN));

CREARE TABLE Professors_Team

(Team-IDCHAR(10),

Team_NameVARCHAR(15),

PRIMARY KEY(Team-ID) );

CREARE TABLE Course

(Course_IDCHAR(10),

Course_NameVARCHAR(15),

PRIMARY KEY(Course_ID) );

CREARE TABLE Member-of

(Pro_SSNCHAR(10)

Team_IDCHAR(10)

PRIMARY KEY(Pro_SSN，Team_ID)

FOREIGN KEY(Pro_SSN) REFERENCE Professor

ON DELETE RESTRICT

ON UPDAE CASCADE ,

FOREIGN KEY(Team_ID) REFERENCE Professors_Team

ON DELETECASCADE

ON UPDAE CASCADE );

CREARE TABLE Team-Teach

(Course_IDCHAR(10)

Team_IDCHAR(10)

PRIMARY KEY(Course_ID，Team_ID)

FOREIGN KEY(Course_ID) REFERENCE Course

ON DELETE RESTRICT

ON UPDAE CASCADE );

FOREIGN KEY(Team_ID) REFERENCE Professors_Team

ON DELETECASCADE

ON UPDAE CASCADE );

CREARE TABLE Pro-Teach

(Pro_SSNCHAR(10) ,

Course_IDCHAR(10) ,

SemsterCHAR(10) ,

PRIMARY KEY(SSN，Course_ID)

FOREIGN KEY(SSN) REFERENCE Professor

ON DELETE CASCADE

ON UPDAE CASCADE ,

FOREIGN KEY(Course_ID) REFERENCE Course

ON DELETECASCADE

ON UPDAE CASCADE );