数据库大题的答案Word格式文档下载.docx

数据库大题的答案Word格式文档下载.docx

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

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,

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

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（pid）referencesprojects）

9.createtableworkin（pidinteger,

foreignkey（profssn）referencesprofessors,

foreignkey（pid）referencesprojects）

observethatwecannotenforcetheparticipationconstraintforprojectsinthe

workintablewithoutcheckconstraintsorassertionsinsql.

10.createtablesupervises（profssnchar（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:

Enrolled（snum:

integer,ame:

string）

Faculty（fid:

integer,fname:

string,deptid:

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

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

createtableworksin（ssnchar（10）,

dnointeger,

primarykey（ssn,dno）,

foreignkey（ssn）referencesemployees,

foreignkey（dno）referencesdepartments）

createtablemanages（ssnchar（10）,

primarykey（dno）,

createtabledependents（ssnchar（10）,

namechar（10）,

primarykey（ssn,name）,

ondeletecascade）

8.Considerthefollowingrelations:

1）.Findthenamesoffacultymembersforwhomthecombinedenrollmentofthecoursesthattheyteachislessthanfive.

2）.Foreachlevel,printthelevelandtheaverageageofstudentsforthatlevel.

3）.ForalllevelsexceptJR,printthelevelandtheaverageageofstudentsforthatlevel.

4）.ForeachfacultymemberthathastaughtclassesonlyinroomR128,printthefacultymember’snameandthetotalnumberofclassessheorhehastaught.

5）.Findthenamesofstudentsenrolledinthemaximumnumberofclasses.

6）.Findthenamesofstudentsnotenrolledinanyclass.

1.selectdistinctf.fname

where5>

（selectcount（e.snum）

fromclassc,enrollede

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

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

groupbys.level

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

wheres.level<

‘jr’

4.selectf.fname,count（*）ascoursecount

fromfacultyf,classc

wheref.fid=c.fid

groupbyf.fid,f.fname

havingevery（c.room=‘r128’）

5.selectdistincts.sname

wheres.snumin（selecte.snum

=all（selectcount（*）

fromenrollede2

groupbyame））

6.selectdistincts.sname

wheres.snumnotin（selecte.snum

fromenrollede）

第三套试卷

2.GiventworelationsR1andR2,whereR1containsN1tuples,R2containsN2tuples,andN2>

N1>

0,givetheminimumandmaximumpossiblesizes（in

tuples）fortheresulti