Elm0406.docx
- 文档编号:11208951
- 上传时间:2023-02-25
- 格式:DOCX
- 页数:20
- 大小:20.50KB
Elm0406.docx
《Elm0406.docx》由会员分享,可在线阅读,更多相关《Elm0406.docx(20页珍藏版)》请在冰豆网上搜索。
Elm0406
CHAPTER6:
THERELATIONALALGEBRAANDRELATIONALCALCULUS
AnswerstoSelectedExercises
6.15ShowtheresultofeachoftheexamplequeriesinSection6.5iftheyareappliedto
thedatabaseofFigure5.6.
Answer:
(QUERY1)Findthenameandaddressofallemployeeswhoworkforthe'Research'
department.
Result:
FNAMELNAMEADDRESS
JohnSmith731Fondren,Houston,TX
FranklinWong638Voss,Houston,TX
RameshNarayan975FireOak,Humble,TX
JoyceEnglish5631Rice,Houston,TX
(QUERY2)Foreveryprojectlocatedin'Stafford',listtheprojectnumber,the
controllingdepartmentnumber,andthedepartmentmanager'slastname,address,and
birthdate.
Result:
PNUMBERDNUMLNAMEADDRESSBDATE
104Wallace291Berry,Bellaire,TX20-JUN-31
304Wallace291Berry,Bellaire,TX20-JUN-31
(QUERY3)Findthenamesofallemployeeswhoworkonalltheprojectscontrolledby
departmentnumber5.
Result:
(emptybecausenotuplessatisfytheresult).
LNAMEFNAME
(QUERY4)Makealistofprojectnumbersforprojectsthatinvolveanemployeewhose
lastnameis'Smith'asaworkerorasamanagerofthedepartmentthatcontrolsthe
project.
Result:
PNO
1
2
(QUERY5)Listthenamesofallemployeeswithtwoormoredependents.
Result:
LNAMEFNAME
SmithJohn
WongFranklin
(QUERY6)Listthenamesofemployeeswhohavenodependents.
Result:
LNAMEFNAME
ZelayaAlicia
NarayanRamesh
EnglishJoyce
JabbarAhmad
BorgJames
(QUERY7)Listthenamesofmanagerswhohaveatleastonedependent.
Result:
LNAMEFNAME
WallaceJennifer
WongFranklin
6.16SpecifythefollowingqueriesontheCOMPANYrelationaldatabaseschemashowninFigure5.5,usingtherelationaloperatorsdiscussedinthischapter.AlsoshowtheresultofeachqueryasitwouldapplytothedatabasestateofFigure5.6.
(a)Retrievethenamesofemployeesindepartment5whoworkmorethan10hoursper
weekonthe'ProductX'project.
(b)Listthenamesofemployeeswhohaveadependentwiththesamefirstnameas
themselves.
(c)Findthenamesofemployeesthataredirectlysupervisedby'FranklinWong'.
(d)Foreachproject,listtheprojectnameandthetotalhoursperweek(byall
employees)spentonthatproject.
(e)Retrievethenamesofemployeeswhoworkoneveryproject.
(f)Retrievethenamesofemployeeswhodonotworkonanyproject.
(g)Foreachdepartment,retrievethedepartmentname,andtheaveragesalaryof
employeesworkinginthatdepartment.
(h)Retrievetheaveragesalaryofallfemaleemployees.
(i)Findthenamesandaddressesofemployeeswhoworkonatleastoneprojectlocated
inHoustonbutwhosedepartmenthasnolocationinHouston.
(j)Listthelastnamesofdepartmentmanagerswhohavenodependents.
Answers:
Intherelationalalgebra,asinotherlanguages,itispossibletospecifythesamequery
inmultipleways.Wegiveonepossiblesolutionforeachquery.Weusethesymbolsfor
SELECT,PforPROJECT,JforEQUIJOIN,*forNATURALJOIN,andfforFUNCTION.
(a)EMP_W_X<--(sPNAME='ProductX'(PROJECT))J(PNUMBER),(PNO)
(WORKS_ON)
EMP_WORK_10<--(EMPLOYEE)J(SSN),(ESSN)(sHOURS>10(EMP_W_X))
RESULT<--PLNAME,FNAME(sDNO=5(EMP_WORK_10))
Result:
LNAMEFNAME
SmithJohn
EnglishJoyce
(b)E<--(EMPLOYEE)J(SSN,FNAME),(ESSN,DEPENDENT_NAME)(DEPENDENT)
R<--PLNAME,FNAME(E)
Result(empty):
LNAMEFNAME
(c)WONG_SSN<--PSSN(sFNAME='Franklin'AND
LNAME='Wong'(EMPLOYEE))
WONG_EMPS<--(EMPLOYEE)J(SUPERSSN),(SSN)(WONG_SSN)
RESULT<--PLNAME,FNAME(WONG_EMPS)
Result:
LNAMEFNAME
SmithJohn
NarayanRamesh
EnglishJoyce
(d)PROJ_HOURS(PNO,TOT_HRS)<--PNOfSUMHOURS(WORKS_ON)
RESULT<--PPNAME,TOT_HRS((PROJ_HOURS)J(PNO),(PNUMBER)
(PROJECT))
Result:
PNAMETOT_HRS
ProductX52.5
ProductY37.5
ProductZ50.0
Computerization55.0
Reorganization25.0
Newbenefits55.0
(e)PROJ_EMPS(PNO,SSN)<--PPNO,ESSN(WORKS_ON)
ALL_PROJS(PNO)<--PPNUMBER(PROJECT)
EMPS_ALL_PROJS<--PROJ_EMPS-:
-ALLPROJS(*DIVISIONoperation*)
RESULT<--PLNAME,FNAME(EMPLOYEE*EMP_ALL_PROJS)
Result(empty):
LNAMEFNAME
(f)ALL_EMPS<--PSSN(EMPLOYEE)
WORKING_EMPS(SSN)<--PESSN(WORKS_ON)
NON_WORKING_EMPS<--ALL_EMPS-WORKING_EMPS(*DIFFERENCE
*)
RESULT<--PLNAME,FNAME(EMPLOYEE*NON_WORKING_EMPS)
Result(empty):
LNAMEFNAME
(g)DEPT_AVG_SALS(DNUMBER,AVG_SAL)<--DNOfAVGSALARY
(EMPLOYEE)
RESULT<--PDNUMBER,AVG_SAL(DEPT_AVG_SALS*DEPARTMENT)
Result:
DNUMBERAVG_SAL
Research33250
Administration31000
Headquarters55000
(h)RESULT(AVG_F_SAL)<--fAVGSALARY(sSEX='F'(EMPLOYEE))
Result:
AVG_F_SAL
31000
(i)E_P_HOU(SSN)<--
PESSN(WORKS_ONJ(PNO),(PNUMBER)(sPLOCATION='Houston'(PROJECT)))
D_NO_HOU<--
PDNUMBER(DEPARTMENT)-PDNUMBER(s
DLOCATION='Houston'(DEPARTMENT))
E_D_NO_HOU<--PSSN(EMPLOYEEJ(PNO),(DNUMBER)(D_NO_HOU))
RESULT_EMPS<--E_P_HOU-E_D_NO_HOU(*thisissetDIFFERENCE*)
RESULT<--PLNAME,FNAME,ADDRESS(EMPLOYEE*RESULT_EMPS)
Result:
LNAMEFNAMEADDRESS
WallaceJennifer291Berry,Bellaire,TX
(j)DEPT_MANAGERS(SSN)<--PMGRSSN(DEPARTMENT)
EMPS_WITH_DEPENDENTS(SSN)<--PESSN(DEPENDENT)
RESULT_EMPS<--DEPT_MANAGERS-EMPS_WITH_DEPENDENTS
RESULT<--PLNAME,FNAME(EMPLOYEE*RESULT_EMPS)
Result:
LNAMEFNAME
BorgJames
6.18ConsidertheLIBRARYrelationalschemashowninFigure6.14,whichisusedto
keeptrackofbooks,borrowers,andbookloans.Referentialintegrityconstraintsare
shownasdirectedarcsinFigure6.14,asinthenotationofFigure6.7.Writedown
relationalexpressionsforthefollowingqueriesontheLIBRARYdatabase:
(a)HowmanycopiesofthebooktitledTheLostTribeareownedbythelibrarybranch
whosenameis"Sharpstown"?
(b)HowmanycopiesofthebooktitledTheLostTribeareownedbyeachlibrary
branch?
(c)Retrievethenamesofallborrowerswhodonothaveanybookscheckedout.
(d)Foreachbookthatisloanedoutfromthe"Sharpstown"branchandwhoseDueDateistoday,retrievethebooktitle,theborrower'sname,andtheborrower'saddress.
(e)Foreachlibrarybranch,retrievethebranchnameandthetotalnumberofbooksloanedoutfromthatbranch.
(f)Retrievethenames,addresses,andnumberofbookscheckedoutforallborrowers
whohavemorethanfivebookscheckedout.
(g)Foreachbookauthored(orco-authored)by"StephenKing",retrievethetitleand
thenumberofcopiesownedbythelibrarybranchwhosenameis"Central".
Answer:
(Note:
WewilluseSforSELECT,PforPROJECT,*forNATURALJOIN,-for
SETDIFFERENCE,FforAGGREGATEFUNCTION)
(a)A<--BOOKCOPIES*LIBRARY-BRANCH*BOOK
RESULT<--PNo_Of_Copies(SBranchName='Sharpstown'andTitle='TheLost
Tribe'
(A))
Note:
AbetterquerywouldbetodotheSELECTsbeforetheJOINasfollows:
A<--PNo_Of_Copies((SBranchName='Sharpstown'(LIBRARY-BRANCH))*
(BOOKCOPIES*(STitle='TheLostTribe'
(BOOK))))
(b)PBranchID,No_Of_Copies((STitle='TheLostTribe'(BOOK))*BOOKCOPIES)
(c)NO_CHECKOUT_B<--PCardNo(BORROWER)-PCardNo(BOOK_LOANS)
RESULT<--PName(BORROWER*NO_CHECKOUT_B)
(d)S<--PBranchId(SBranchName='Sharpstown'(LIBRARY-BRANCH))
B_FROM_S<--PBookId,CardNo((SDueDate='today'(BOOKLOANS))*S)
RESULT<--PTitle,Name,Address(BOOK*BORROWER*B_FROM_S)
(e)R(BranchId,Total)<--BranchIdFCOUNT(BookId,CardNo)(BOOK_LOANS)
RESULT<--PBranchName,Total(R*LIBRARY_BRANCH)
(f)B(CardNo,TotalCheckout)<--CardNoFCOUNT(BookId)(BOOK_LOANS)
B5<--STotalCheckout>5(B)
RESULT<--PName,Address,TotalCheckout(B5*BORROWER)
(g)SK(BookId,Title)<--(sAuthorName='StephenKing'(BOOK_AUTHORS))*BOOK
CENTRAL(BranchId)<--sBranchName='Central'(LIBRARY_BRANCH)
RESULT<--PTitle,NoOfCopies(SK*BOOKCOPIES*CENTRAL)
6.22
(a)
PQRABC
10a510b6
10a510b5
25a625c3
(b)
PQRABC
15b810b6
15b810b5
(c)
PQRABC
10a510b6
10a510b5
15b8nullnullnull
25a625c3
(d)
PQRABC
15b810b6
nullnullnull25c3
15b810b5
(e)
PQR
10a5
15b8
25a6
10b6
25c3
10b5
(f)
PQRABC
10a510b5
6.24Specifyqueries(a),(b),(c),(e),(f),(i),and(j)ofExercise6.16inboththe
tuplerelationalcalculusandthedomainrelationalcalculus.
Answer:
(a)Retrievethenamesofemployeesindepartment5whoworkmorethan10hoursper
weekonthe'ProductX'project.
TuplerelationalCalculus:
{e.LNAME,e.FNAME|EMPLOYEE(e)ANDe.DNO=5AND(EXISTSp)(EXISTSw)
(
WORKS_ON(w)ANDPROJECT(p)ANDe.SSN=w.ESSNAND
w.PNO=p.PNUMBERAND
p.PNAME='ProductX'ANDw.HOURS>10)}
DomainrelationalCalculus:
{qs|EMPLOYEE(qrstuvwxyz)ANDz=5AND(EXISTSa)(EXISTSb)(EXISTSe)
(EXISTSf)
(EXISTSg)(WORKS_ON(efg)ANDPROJECT(abcd)ANDt=eANDf=bAND
a='ProductX'AND
g>10)}
(b)Listthenamesofemployeeswhohaveadependentwiththesamefirstnameas
themselves.
TuplerelationalCalculus:
{e.LNAME,e.FNAME|EMPLOYEE(e)AND(EXISTSd)(DEPENDENT(d)AND
e.SSN=d.ESSN
ANDe.FNAME=d.DEPENDENT_NAME)}
DomainrelationalCalculus:
{qs|(EXISTSt)(EXISTSa)(EXISTSb)(EMPLOYEE(qrstuvwxyz)AND
DEPENDENT(abcde)
ANDa=tANDb=q)}
(c)Findthenamesofemployeesthataredirectlysupervisedby'FranklinWong'.
TuplerelationalCalculus:
{e.LNAME,e.FNAME|EMPLOYEE(e)AND(EXISTSs)(EMPLOYEE(s)AND
s.FNAME='Franklin'ANDs.LNAME='Wong'ANDe.SUPERSSN=s.SSN)}
DomainrelationalCalculus:
{qs|(EXISTSy)(EXISTSa)(EXISTSc)(EXISTSd)(EMPLOYEE(qrstuvwxyz)AND
EMPLOYEE(abcdefghij)ANDa='Franklin'ANDc='Wong'ANDy=d)}
(e)Retrievethenamesofemployeeswhoworkoneveryproject.
TuplerelationalCalculus:
{e.LNAME,e.FNAME|EMPLOYEE(e)AND(FORALLp)(NOT(PROJECT(p))OR
(EXISTSw)(
WORKS_ON(w)ANDp.PNUMBER=w.PNOANDw.
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Elm0406
![提示](https://static.bdocx.com/images/bang_tan.gif)