数据库大题的答案Word文件下载.docx

数据库大题的答案Word文件下载.docx

《数据库大题的答案Word文件下载.docx》由会员分享，可在线阅读，更多相关《数据库大题的答案Word文件下载.docx（38页珍藏版）》请在冰豆网上搜索。

Eachprojectisworkedonbyoneormoregraduatestudents（knownastheproject’sresearchassistants）.

Whengraduatestudentsworkonaproject,aprofessormustsupervisetheirworkontheproject.Graduatestudentscanworkonmultipleprojects,inwhichcasetheywillhavea（potentiallydifferent）supervisorforeachone.

Departmentshaveadepartmentnumber,adepartmentname,andamainoffice.

Departmentshaveaprofessor（knownasthechairman）whorunsthedepartment.

Professorsworkinoneormoredepartments,andforeachdepartmentthattheyworkin,atimepercentageisassociatedwiththeirjob.

Graduatestudentshaveonemajordepartmentinwhichtheyareworkingontheirdegree.

Eachgraduatestudenthasanother,moreseniorgraduatestudent（knownasastudentadvisor）whoadviseshimorheronwhatcoursestotake.

DesignanddrawanERdiagramthatcapturestheinformationabouttheuniversity.

UseonlythebasicERmodelhere;

thatis,entities,relationships,andattributes.Besuretoindicateanykeyandparticipationconstraints.

9.ConsidertheuniversitydatabasefromExercise8andtheERdiagramyoudesigned.WriteSQLstatementstocreatethecorrespondingrelationsandcaptureasmanyoftheconstraintsaspossible.Ifyoucannotcapturesomeconstraints,explainwhy.

Answer：

1.createtableprofessors（prof_ssnchar（10）,

namechar（64）,

ageinteger,

rankinteger,

specialitychar（64）,

primarykey（profssn））

2.createtabledepts（dnointeger,

dnamechar（64）,

officechar（10）,

primarykey（dno））

3.createtableruns（dnointeger,

prof_ssnchar（10）,

primarykey（dno,profssn）,

foreignkey（profssn）referencesprofessors,

foreignkey（dno）referencesdepts）

4.createtableworkdept（dnointeger,

pc_timeinteger,

primarykey（dno,prof_ssn）,

foreignkey（prof_ssn）referencesprofessors,

foreignkey（dno）referencesdepts）

observethatwewouldneedcheckconstraintsorassertionsinsqltoenforcetherulethatprofessorsworkinatleastonedepartment.

5.createtableproject（pidinteger,

sponsorchar（32）,

startdatechar（20）,

enddatechar（20）,

budgetfloat,

primarykey（pid））

6.createtablegraduates（gradssnchar（10）,

degprogchar（32）,

majorinteger,

primarykey（gradssn）,

foreignkey（major）referencesdepts）

notethatthemajortableisnotnecessarysinceeachgraduatehasonlyonemajor

andsothiscanbeanattributeinthegraduatestable.

7.createtableadvisor（seniorssnchar（10）,

gradssnchar（10）,

primarykey（seniorssn,gradssn）,

foreignkey（seniorssn）referencesgraduates,

foreignkey（gradssn）referencesgraduates）

8.createtablemanages（pidinteger,

profssnchar（10）,

primarykey（pid,profssn）,

foreignkey（pid）referencesprojects）

9.createtableworkin（pidinteger,

foreignkey（profssn）referencesprofessors,

foreignkey（pid）referencesprojects）

observethatwecannotenforcetheparticipationconstraintforprojectsinthe

workintablewithoutcheckconstraintsorassertionsinsql.

10.createtablesupervises（profssnchar（10）,

pidinteger,

primarykey（profssn,gradssn,pid）,

foreignkey（profssn）referencesprofessors（profssn）,

foreignkey（gradssn）referencesgraduates（gradssn）,

foreignkey（pid）referencesprojects（pid））

Notethatwedonotneedanexplicit（明确的）tablefortheWorkProjrelationsinceeverytimeaGraduateworksonaProject,heorshemusthaveaSupervisor.

10.Considerthefollowingrelations:

Student（snum:

integer,sname:

string,major:

string,level:

string,age:

integer）

Class（name:

string,meetsat:

string,room:

string,fid:

Enrolled（snum:

integer,cname:

string）

Faculty（fid:

integer,fname:

string,deptid:

Themeaningoftheserelationsisstraightforward;

forexample,Enrolledhasonerecordperstudent-classpairsuchthatthestudentisenrolledintheclass.

WritethefollowingqueriesinSQL.Noduplicatesshouldbeprintedinanyoftheanswers.

1.FindthenamesofallJuniors（level=JR）whoareenrolledinaclasstaughtbyI.Teach.

2.FindtheageoftheoldeststudentwhoiseitheraHistorymajororenrolledin