Convert ERDs into relational models: Be sure to convert each many-to-many relati

Question

Convert ERDs into relational models:

Be sure to convert each many-to-many relationship into a table.

Do not convert (0,1)-to-many relationship to a table. That is, do not convert optional 1-to-many relationships.

Note: The result of conversion is a collection of “CREATE TABLE” commands or simplified commands (Please show both ways for learning expirence)

1 Student StdID Name Gender DOB AdmitDate D,C UndStudent Major Minor Class GradStudent Advisor ThesisTitle AsstStatus 2. Project ProiNo ProjName rojectNeeds Specialty as ulFills-SpecNo SpecName ProvidedBy Contractor>o.Supplies ContrNo ContrName

Explanation / Answer

1. Tables

Student(StdID,Name,Gender,DOB,AdmitDate)

----underlined are primary keys and italicised are foreign keys . Some attributes are both.

Create table Student

(

StdID int NOT NULL,

Name varchar(30) NOT NULL,

Gender char(1) NOT NULL,

DOB date NOT NULL,

Admitdate date NOT NULL,

Primary key(StdID)

);

UndStudent(UndStdID,Major,Minor,Class)

Create table UndStudent

(

UndStdID int NOT NULL,

Major varchar(20) NOT NULL,

Minor varchar(20) NOT NULL,

Class varchar(20) NOT NULL,

Primary Key(UndStdID),

Foreign Key(UndStdID) References Student(StdID)

);

GradStudent(GradStdID,Advisor,ThesisTitle,AsstStatus)

Create table GradStudent

(

GradStdID int NOT NULL,

Advisor varchar(20) NOT NULL,

ThesisTitle varchar(60) NOT NULL,

AsstStatus varchar(20) NOT NULL,

Primary Key(GradStdID),

Foreign Key(GradStdID References Student(StdID))

);

2.

Project(ProjNo,ProjName)

Create table Project

(

ProjNo int NOT NULL,

ProjName varchar(30) NOT NULL,

Primary Key(ProjNo)

);

Contractor(ContrNo,ContrName)

Create table Contractor

(

ContrNo int NOT NULL,

ContrName varchar(20) NOT NULL,

Primary Key(ContrNo)

);

Speciality(SpecNo,SpecName)

Create table Speciality

(

SpecNo int NOT NULL,

SpecName varchar(20) NOT NULL,

Primary Key(SpecNo)

);

ProjectNeeds(ProjNo,SpecNo,ContrNo)

Create table ProjectNeeds

(

ProjNo int NOT NULL,

SpecNo int NOT NULL,

ContrNo int NOT NULL,

Primary Key(ProjNo,SpecNo,ContrNo),

Foreign Key(ProjNo) References Project(ProjNo),

Foreign Key(SpecNo) References Speciality(SpecNo),

Foreign Key(ContrNo) References Contractor(ContrNo)

);

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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