112 Chapter 4 The Relational Model Branch Figure 4-3 Instance of the Dream Home rental database. branch No street city postcode B005 B007 B003 B004 B002 22 Deer Rd London SW1 4EH 16 Argyll St Aberdeen AB2 3SU 163 Main St Glasgow G11 90X 32 Manse Rd Bristol BS99 INZ 56 Clover Dr London NW10 6EU Staff staffNo Name IName position sex DOB salary branch No B003 SL21 SG37 SG14 SA9 SG5 SL41 John Ann David Mary Susan Julie White Manager M Beech Assistant F Ford Supervisor M Howe Assistant F Brand Manager F Lee Assistant F 1-Oct-45 30000 B005 10-Nov-60 12000 24-Mar-5818000 B003 19-Feb-70 9000 B007 3-Jun-40 24000 B003 13-Jun-65 9000 B005 PropertyForRent city propertyNo street postcode type rooms rent ownerNo staffNo branch No B007 SA9 SL41 B005 PA14 PL94 PG4 PG36 PG21 PG16 16 Holhead Aberdeen AB7 5SU 6 Argyll St London NW2 6 Lawrence StGlasgow G11 90X 2 Manor Rd Glasgow G32 4QX 18 Dale Rd Glasgow G12 5 Novar Dr Glasgow G12 9AX House 6 Flat 4 Flat 3 Flat 3 House 5 Flat 4 650 400 350 375 C046 CO87 C040 C093 CO87 C093 B003 B003 B003 B003 SG37 SG37 SG14 600 450 Client clientNo Name IName telNo prefType maxRent eMail CR76 CR56 John Aline Mike Mary Kay 0207-774-5632 Flat Stewart 0141-848-1825 Flat Ritchie 01475-392178 House Tregear 01224-196720 Flat 425 350 750 600 john.kay@gmail.com astewart@hotmail.com mritchie01@yahoo.co.uk maryt@hotmail.co.uk CR74 CR62 Private Owner telNo eMail password ownerNo Name IName address ******** ******** C046 CO87 C040 C093 Joe Carol Tina Tony Keogh 2 Fergus Dr, Aberdeen AB2 75X 01224-861212 jkeogh@lhh.com Farrel 6 Achray St, Glasgow G32 9DX 0141-357-7419 cfarrel@gmail.com Murphy 63 Well St, Glasgow G42 0141-943-1728 tinam@hotmail.com Shaw 12 Park Pl, Glasgow G4 OQR 0141-225-7025 tony.shaw@ark.com ******* Viewing Registration clientNo branchNo staffNo date Joined clientNo property No viewDate comment CR76 CR56 SL41 SG37 SG37 CR56 CR76 CR56 CR62 CR56 PA14 PG4 PG4 PA14 PG36 B005 B003 B003 B007 24-May-13 too small 20-Apr-13 too remote 26-May-13 14-May-13 no dining room 28-Apr-13 2-Jan-13 11-Apr-12 16-Nov-11 7-Mar-12 CR74 CR62 SAS Please write Relational Algebra expressions and result tables according to the following queries and above tables. [7] Select all the female staffs with a salary more than $20,000. [8] List all staff with a salary between $20,000 and $30,000 [9] Produce a table of staff number, first name, last name, and working branch from the staff table. [10] How many properties are cost more than $300 per month? [11] Find the minimum, maximum and average salary of staffs. [12] Find the average salary for each branch. (please use grouping) [13] List the staffs who work in the branch at '163 Main S. [14] List the properties that are handled by staff who work in the branch at '163 Main S. [15] List the names of all clients who have viewed a property and leave a comment. [16] For each branch, list the branch number, number of staff members, and sum of their salary. [17] Find out the names of the clients that Ann Beach registered. [18] Find out the owner' address of the property PA14
