资源描述：
《大数据库大题地问题详解.doc》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、第一套试卷8.Considerthefollowinginformationaboutauniversitydatabase:ProfessorshaveanSSN,aname,anage,arank,andaresearchspecialty.Projectshaveaprojectnumber,asponsorname(e.g.,NSF),astartingdate,anendingdate,andabudget.GraduatestudentshaveanSSN,aname,anage,andadegreeprogram(e.g.,M.S.or
2、Ph.D.).Eachprojectismanagedbyoneprofessor(knownastheproject’sprincipalinvestigator).Eachprojectisworkedonbyoneormoreprofessors(knownastheproject’sco-investigators).Professorscanmanageand/orworkonmultipleprojects.Eachprojectisworkedonbyoneormoregraduatestudents(knownastheproject
3、’sresearchassistants).Whengraduatestudentsworkonaproject,aprofessormustsupervisetheirworkontheproject.Graduatestudentscanworkonmultipleprojects,inwhichcasetheywillhavea(potentiallydifferent)supervisorforeachone.Departmentshaveadepartmentnumber,adepartmentname,andamainoffice.Dep
4、artmentshaveaprofessor(knownasthechairman)whorunsthedepartment.Professorsworkinoneormoredepartments,andforeachdepartmentthattheyworkin,atimepercentageisassociatedwiththeirjob.Graduatestudentshaveonemajordepartmentinwhichtheyareworkingontheirdegree.Eachgraduatestudenthasanother,
5、moreseniorgraduatestudent(knownasastudentadvisor)whoadviseshimorheronwhatcoursestotake.DesignanddrawanERdiagramthatcapturestheinformationabouttheuniversity.UseonlythebasicERmodelhere;thatis,entities,relationships,andattributes.Besuretoindicateanykeyandparticipationconstraints.9
6、.ConsidertheuniversitydatabasefromExercise8andtheERdiagramyoudesigned.WriteSQLstatementstocreatethecorrespondingrelationsandcaptureasmanyoftheconstraintsaspossible.Ifyoucannotcapturesomeconstraints,explainwhy.Answer：1.createtableprofessors(prof_ssnchar(10),namechar(64),ageinteg
7、er,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.create
8、tableworkdept(dnointeger,prof_ssnchar(10),pc_timeinteg
