Assignment--Relational Algebra

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

Assignment—Relational Algebra Total Point: 10 Student's Name: 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 )) 1 |P a g e

Branch Viewing 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-1825Flat$350.00 CR62MaryTregear01224-196720Flat$600.00 CR74MikeRitchie01475-392178House$750.00 CR76JohnKay0207-774-5632Flat$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 PropertynostreetcityPostcodetyperooms rentownerNostaffNo branchNo PA1416 HolheadAberdeen AB7 5SUHouse6 $650.00 CO46SA9B007 PG165 Novar DrGlasgowG12 9AXFlat4 $450.00 CO93SG14B003 PG2118 Dale RdGlasgow G12House5 $600.00 CO87SG37B003 PG362 Manor RdGlasgowG32 4QXFlat3 $375.00 CO93SG37B003 PG46 Lawrence StGlasgowG11 9QXFlat3 $350.00 CO40B003 PL946 Argyll StLondonNW2Flat4 $400.00 CO87SL41B005 Registration clientNobranchNostaffNodateJoined 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