street city - Department of Computer Science and ...

1 Exercises31employee(person-name, street ,c ity)works(person-name,company-name,salar y)company(company-name, city )manages(pers on-name,manager-name) relational database for Exercises , and 2, we sawhowto representmany-to-many, many-to-one, one-to-many, suchrelationship setsin the :Supposethe primarykeyof relation schema is 1 2 n andthe primarykeyof relationschema is 1 2 m .Thenare-lationshipbetweenthe2 setscan be representedas a tuple( 1 2 n 1 2 m). Ina one-to-onerelationship, each valueon 1 2 n willappear inexactlyonetupleandlikewisefor 1 2 m .Inamany-to-onerelationship( ,many -one ), eachvalue on 1 2 n willappearonce,and eachvalueon 1 2 n may appear manytimes.

2 Inamany-to-manyrelationship, values on both 1 2 n and 1 2 m will appearmany times. However, in all the above cases 1 2 n, 1 2 m is a primarykey, sono tupleon ( 1 n 1 m) willappearmore database of Figure , where theprimary keysare un-derlined. Givean expressionintherelational algebrato expresseachofthefol- all andcities ofresidence of all employees whowork ,streetaddress,and citiesof residenceofall employeeswhoworkfor First Bank Corporation and earnmore than$10, all employeesinthisdatabasewholivein thesamecityas thecompanyfor which they all employeeswholivein thesamecityand on all employeesinthisdatabasewhodo notworkfor the names of all employeeswho earnmore than everyemployeeofSmall Bank be locatedinseveral companieslocatedin everycityinwhichSmall BankCorporationis :a.

3 - - = FirstBankCorporation b. - - = FirstBankCorporation 32 Chapter 3 RelationalModelc. - ( - = FirstBankCorporation 10000) d. - e. - ( - = 2 - = 2 = 2 ) 2 followingsolutionsassumethat all people work forexactlyonecom-pany. If oneallowspeopletoappear inthedatabase ( )butnotappearinworks, theproblemis more complicated. We givesolutionsforthismore realisticcase later. - - = FirstBankCorporation If peoplemay notwork for anycompany: - - ( - = FirstBankCorporation ) g. - - ( 2 2 - = Small Bank Corporation ) 2 :Small BankCorporationwillbeincludedineachanswe r. - - = Small Bank Corporation Figure , whichshowstheresultof thequery Findthe namesof all customerswho havea loanatthe bank.

4 Rewritethequerytoinclude notonlythename, but alsothecityofresidence foreach nowcustomer Jacksonno longer appears in theresult,even thoughJackson does in facthavea loanfromthe Jackson does not appearinthe youwant Jacksontoappear database to achievethis effect? , suppose thatyou want Jackson to appearin the result. Write a queryusingan outerjointhat accomplishesthisdesire withoutyourhavingtomodifythe :The rewrittenqueryis - - a loan, no addressis givenfor , Jackson doesnot appearin the best solution is to insertJackson s address intothecustomerrelation. Iftheaddressis unknown,nullvaluesmay thedatabasesystemdoes notsupportnulls,a special valuemay be used (suchasunknown)forJackson s streetand city .

5 Thespecial valuechosenmustnotbea plausiblename - - that tuplesfromtheparticipatingrelationsare notlostin be extendedso that tuplesfromtheleft, right,orbothrelations arenot lost fromthe result of a theta leftouterthetajoin ofr(R)ands(S) canbe de ned as Thetupleofnullsis ofsize equal to right outerthetajoin ofr(R)ands(S) canbe de ned as Thetupleofnullsis ofsize equal to (R)ands(S) canbe de ned as The rsttupleofnullsis ofsize equal to thenumberofattributesinR,andthe second one is of sizeequalto the numberof database of Figure Givean expressionintherela-tional algebra foreach database so that Jonesnowlivesin employeesof First Bank Corporation a 10percent managersin this database a 10percent managersin this database a 10percent salaryraise, unless the salarywould be greaterthan$100, suchcases, give only a 3 percent tuplesin theworksrelationfor employees ofSmall Bank :a.

6 - - = Jones - = Jones b. - - 1 1 ( - = FirstBankCorporation ) - = FirstBankCorporation syntax allowsreferenceto a singlerelationonly. Sincethisup-daterequiresaccesstobothther elationto be updated(works)andtheman-agesrelation,we mustuseseveral thetuplesofworkstobeupdatedandstore themin a temporary relation( 1). Thenwe createa temporary relationcontainingthenewtuples ( 2). Finally, we deletethetuplesin 1,fromworksand insert thetuples of 2. 1 - - - = - 34 Chapter 3 RelationalModel 2 - - 1 1 1 1 same situation ariseshere. As before, 1,holdsthetuplestobeupdatedand 2holdsthese tuplesintheirupdatedform. 1 - - - = - 2 - - 1 03 1 1 1 100000 1 2 2 - - 1 1 1 1 1 100000 1 1 2e.

7 = Small Bank Corporation ,writerelational-algebraqueriesto ndtheaccountsheldbymore than aggregate aggregate :a. 1 - count - - - 2 - ( - - ) 1 b. 1 1 2 3 2 ( 1 - = 2 - = 3 - ) 1 1 - ( 1 - = 2 - 2 - = 3 - 3 - = 1 - ) 2 database of Figure Givea relational -algebraexpres-sionfor each of highersalary, on average,than theaveragesalaryat :a. 1 - count-distinct - 2 max - - ( - - ) 1 - 3( - - ) 1 4( - ) 2 b. 1 - sum 2 min - ( - ) 1 - 3( - ) 1 4( ) 2 c. 1 - avg 2 - = FirstBankCorporation 1