数据库大题的答案.docx

数据库大题的答案.docx

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

数据库大题的答案

第一套试卷

8.Considerthefollowinginformationaboutauniversitydatabase:

ProfessorshaveanSSN,aname,anage,arank,andaresearchspecialty.

Projectshaveaprojectnumber,asponsorname（e.g.,NSF）,astartingdate,anendingdate,andabudget.

GraduatestudentshaveanSSN,aname,anage,andadegreeprogram（e.g.,M.S.orPh.D.）.

Eachprojectismanagedbyoneprofessor（knownastheproject’sprincipalinvestigator）.

Eachprojectisworkedonbyoneormoreprofessors（knownastheproject’sco-investigators）.

Professorscanmanageand/orworkonmultipleprojects.

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,

prof_ssnchar（10）,

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）,

ageinteger,

namechar（64）,

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（profssn）referencesprofessors,

foreignkey（pid）referencesprojects）

9.createtableworkin（pidinteger,

profssnchar（10）,

primarykey（pid,profssn）,

foreignkey（profssn）referencesprofessors,

foreignkey（pid）referencesprojects）

observethatwecannotenforcetheparticipationconstraintforprojectsinthe

workintablewithoutcheckconstraintsorassertionsinsql.

10.createtablesupervises（profssnchar（10）,

gradssnchar（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:

integer）

Enrolled（snum:

integer,ame:

string）

Faculty（fid:

integer,fname:

string,deptid:

integer）

Themeaningoftheserelationsisstraightforward;forexample,Enrolledhasonerecordperstudent-classpairsuchthatthestudentisenrolledintheclass.

WritethefollowingqueriesinSQL.Noduplicatesshouldbeprintedinanyoftheanswers.

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

2.FindtheageoftheoldeststudentwhoiseitheraHistorymajororenrolledinacoursetaughtbyI.Teach.

3.FindthenamesofallclassesthateithermeetinroomR128orhavefiveormorestudentsenrolled.

4.Findthenamesofallstudentswhoareenrolledintwoclassesthatmeetatthesametime.

5.Findthenamesoffacultymemberswhoteachineveryroominwhichsomeclassistaught.

Theanswersaregivenbelow:

1.selectdistincts.sname

fromstudents,classc,enrollede,facultyf

wheres.snum=e.snumandame=c.nameandc.fid=f.fidand

f.fname=‘i.teach’ands.level=‘jr’

2.selectmax（s.age）

fromstudents

where（s.major=‘history’）

ors.snumin（selecte.snum

fromclassc,enrollede,facultyf

whereame=c.nameandc.fid=f.fid

andf.fname=‘i.teach’）

3.selectc.name

fromclassc

wherec.room=‘r128’

orc.namein（selectame

fromenrollede

groupbyame

havingcount（*）>=5）

4.selectdistincts.sname

fromstudents

wheres.snumin（selecte1.snum

fromenrollede1,enrollede2,classc1,classc2

wheree1.snum=e2.snumandame<>ame

andame=c1.name

andame=c2.nameandc1.meetsat=c2.meetsat）

5.selectdistinctf.fname

fromfacultyf

wherenotexists（（select*

fromclassc）

except

（selectc1.room

fromclassc1

wherec1.fid=f.fid））

=============================================================第二套试卷

6Acompanydatabaseneedstostoreinformationaboutemployees（identifiedbyssn,withsalaryandphoneasattributes）,departments（identifiedbydno,withdnameandbudgetasattributes）,andchildrenofemployees（withnameandageasattributes）.Employeesworkindepartments;eachdepartmentismanagedbyanemployee;achildmustbeidentifieduniquelybynamewhentheparent（whoisanemployee;assumethatonlyoneparentworksforthecompany）isknown.Wearenotinterestedininformationaboutachildoncetheparentleavesthecompany.

DrawanERdiagramthatcapturesthisinformation.

7ConsiderthescenariofromExercise6,whereyoudesignedanERdiagramforacompanydatabase.WriteSQLstatementstocreatethecorrespondingrelationsandcaptureasmanyoftheconstraintsaspossible.Ifyoucannotcapturesomeconstraints,explainwhy.

answer:

thefollowingsqlstatementscreatethecorrespondingrelations.

createtableemployees（ssnchar（10）,

salinteger,

phonechar（13）,

primarykey（ssn））

createtabledepartments（dnointeger,

budgetinteger,

dnamechar（20）,

primarykey（dno））

createtableworksin（ssnchar（10）,

dnointeger,

primarykey（ssn,dno）,

foreignkey（ssn）referencesemployees,

foreignkey（dno）referencesdepartments）

createtablemanages（ssnchar（10）,

dnointeger,

primarykey（dno）,

foreignkey（ssn）referencesemployees,

foreignkey（dno）referencesdepartments）

createtabledependents（ssnchar（10）,

namechar（10）,

ageinteger,

primarykey（ssn,name）,

foreignkey（ssn）referencesemployees,

ondeletecascade）

8.Considerthefollowingrelations:

Student（snum:

integer,sname:

string,major:

string,level:

string,age:

integer）

Class（name:

string,meetsat:

string,room:

string,fid:

integer）

Enrolled（snum:

integer,ame:

string）

Faculty（fid:

integer,fname:

string,deptid:

integer）

Themeaningoftheserelationsisstraightforward;forexample,Enrolledhasonerecordperstudent-classpairsuchthatthestudentisenrolledintheclass.

WritethefollowingqueriesinSQL.Noduplicatesshouldbeprintedinanyoftheanswers.

1）.Findthenamesoffacultymembersforwhomthecombinedenrollmentofthecoursesthattheyteachislessthanfive.

2）.Foreachlevel,printthelevelandtheaverageageofstudentsforthatlevel.

3）.ForalllevelsexceptJR,printthelevelandtheaverageageofstudentsforthatlevel.

4）.ForeachfacultymemberthathastaughtclassesonlyinroomR128,printthefacultymember’snameandthetotalnumberofclassessheorhehastaught.

5）.Findthenamesofstudentsenrolledinthemaximumnumberofclasses.

6）.Findthenamesofstudentsnotenrolledinanyclass.

1.selectdistinctf.fname

fromfacultyf

where5>（selectcount（e.snum）

fromclassc,enrollede

wherec.name=ameandc.fid=f.fid）

2.selects.level,avg（s.age）

fromstudents

groupbys.level

3.selects.level,avg（s.age）

fromstudents

wheres.level<>‘jr’

groupbys.level

4.selectf.fname,count（*）ascoursecount

fromfacultyf,classc

wheref.fid=c.fid

groupbyf.fid,f.fname

havingevery（c.room=‘r128’）

5.selectdistincts.sname

fromstudents

wheres.snumin（selecte.snum

fromenrollede

groupbyame

havingcount（*）>=all（selectcount（*）

fromenrollede2

groupbyame））

6.selectdistincts.sname

fromstudents

wheres.snumnotin（selecte.snum

fromenrollede）

第三套试卷

2.GiventworelationsR1andR2,whereR1containsN1tuples,R2containsN2tuples,andN2>N1>0,givetheminimumandmaximumpossiblesizes（in

tuples）fortheresulti