资源描述:
《数据库大题的答案.doc》由会员上传分享,免费在线阅读,更多相关内容在教育资源-天天文库。
1、..第一套试卷8.Considerthefollowinginformationaboutauniversitydatabase:ProfessorshaveanSSN,aname,anage,arank,andaresearchspecialty.Projectshaveaprojectnumber,asponsorname(e.g.,NSF),astartingdate,anendingdate,andabudget.GraduatestudentshaveanSSN,aname,anage,anda
2、degreeprogram(e.g.,M.S.orPh.D.).Eachprojectismanagedbyoneprofessor(knownastheproject’sprincipalinvestigator).Eachprojectisworkedonbyoneormoreprofessors(knownastheproject’sco-investigators).Professorscanmanageand/orworkonmultipleprojects.Eachprojectisworke
3、donbyoneormoregraduatestudents(knownastheproject’sresearchassistants).Whengraduatestudentsworkonaproject,aprofessormustsupervisetheirworkontheproject.Graduatestudentscanworkonmultipleprojects,inwhichcasetheywillhavea(potentiallydifferent)supervisorforeach
4、one.Departmentshaveadepartmentnumber,adepartmentname,andamainoffice.Departmentshaveaprofessor(knownasthechairman)whorunsthedepartment.Professorsworkinoneormoredepartments,andforeachdepartmentthattheyworkin,atimepercentageisassociatedwiththeirjob.Graduates
5、tudentshaveonemajordepartmentinwhichtheyareworkingontheirdegree.Eachgraduatestudenthasanother,moreseniorgraduatestudent(knownasastudentadvisor)whoadviseshimorheronwhatcoursestotake.DesignanddrawanERdiagramthatcapturestheinformationabouttheuniversity.Useon
6、lythebasicERmodelhere;thatis,entities,relationships,andattributes.Besuretoindicateanykeyandparticipationconstraints..zyzl....9.ConsidertheuniversitydatabasefromExercise8andtheERdiagramyoudesigned.WriteSQLstatementstocreatethecorrespondingrelationsandcaptu
7、reasmanyoftheconstraintsaspossible.Ifyoucannotcapturesomeconstraints,explainwhy.Answer:1.createtableprofessors(prof_ssnchar(10),namechar(64),ageinteger,rankinteger,specialitychar(64),primarykey(profssn))2.createtabledepts(dnointeger,dnamechar(64),officech
8、ar(10),primarykey(dno))3.createtableruns(dnointeger,prof_ssnchar(10),primarykey(dno,profssn),foreignkey(profssn)referencesprofessors,foreignkey(dno)referencesdepts)4.createtableworkdept(dnointeger,prof_ssnchar(10),p