数据库大题的答案.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,cname:
string)
Faculty(fid:
integer,fname:
string,deptid:
integer)
Themeaningoftheserelationsisstraightforward;forexample,Enrolledhasonerecordperstudent-classpairsuchthatthestudentisenrolledintheclass.
WritethefollowingqueriesinSQL.Noduplicatesshouldbeprintedinanyoftheanswers.
1.FindthenamesofallJuniors(level=JR)whoareenrolledinaclasstaughtbyI.Teach.
2.FindtheageoftheoldeststudentwhoiseitheraHistorymajororenrolledin