数据库大题的答案.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
升级会员 