Relational Algebra: Queries and Expressions for Database Analysis

School: Southern Arkansas University - Course: DBMS 3403 - Subject: Accounting

Task A: [(Expression 0.75 + Output relation 0.5) * 4 = 5] Using the tables in page 2, construct relational algebra expressions to answer the following queries and represent the answer as a relation: 1.First and last names of the clients registered at Branch B003. 2.Which staff members were born after 1955? 3.List the flats with rent less than $500. 4.Telephone numbers of the owners who have property at London. Task B: [1*5 = 5] Using the tables in page 2, show the relation (table including column headings) created by the following relational algebra expressions as a two-dimensional table: 1.fName,LName, rooms, renttype =preftype (ClientX PropertyForRent) ) 2.LName,positioncity = "London" ( PropertyForRentStaff) ) 3.staffNo( Registeration )staffNo (PropertyForRent) 4.branchNo(Branch)-branchNo (Staff ) 5.fName,LNamepropertyNo IS NULL(StaffPropertyForRent ))

2 |P a g eBranchViewing branchNostreetcitypostcode B00256 Clover DrLondonNW10 6EU B003163 Main StGlasgowG11 9QX B00432 Manse RdBristolBS99 1NZ B00522 Deer RdLondonSWI 4EH B00716 Argyll StAberdeenAB2 3SU Client clientNofNamelNametelNoprefTypemaxRent CR56AlineStewart0141-848-1825 Flat$350.00 CR62MaryTregear01224-196720Flat$600.00 CR74MikeRitchie01475-392178House$750.00 CR76JohnKay0207-774-5632 Flat$425.00 PrivateOwner ownerNofNamelNameAddresstelNo CO40TinaMurphy63 Wells St, Glasgow G420141-943-1728 CO46JoeKeogh2 FergusDr, Aberdeen AB2 7SX01224-861212 CO87CarolFarrel6 Achray St, Glasgow G32 9DX0141-357-7419 CO93TonyShaw12 Park Pl, Glasgow G4 0QR0141-225-7025 PropertyForRent PropertynostreetcityPostcode typerooms rentownerNo staffNo branchNo PA1416 HolheadAberdeen AB7 5SU House6 $650.00 CO46SA9B007 PG165 Novar DrGlasgow G12 9AX Flat4 $450.00 CO93SG14B003 PG2118 Dale RdGlasgow G12House5 $600.00 CO87SG37B003 PG362 Manor RdGlasgow G32 4QX Flat3 $375.00 CO93SG37B003 PG46 Lawrence St Glasgow G11 9QX Flat3 $350.00 CO40B003 PL946 Argyll StLondonNW2Flat4 $400.00 CO87SL41B005 Registration clientNobranchNo staffNodateJoined CR56B003SG374/11/2000 CR62B007SA93/7/2000 CR74B003SG3711/16/1999 CR76B005SL411/2/2001 Staff staffNofNamelnamepositionsexDOBsalarybranchNo SA9MaryHoweAssistantF2/19/19709,000.00 B007 SG14DavidFordSupervisor M3/24/195818,000.00 B003 SG37AnnBeechAssistantF11/10/196012,000.00 B003 SG5SusanBrandManagerF6/3/194024,000.00 B003 SL21JohnWhiteManagerM10/1/194530,000.00 B005 SL41JulieLeeAssistantF6/13/19659,000.00 B005