NOTE: ThIS assignment is MUCH more important THAN PREVIOUS ASSIGNMENTS – 5% OF COURSE GRADE.
Task:
There is a set of tables owned by “csc240r” available on the server at La Salle (password red, and host string cscdart). The design for the tables is given on page 2. All attributes for a table are listed, with the PK indicated via an underlined attribute. FKs are also indicated. Any questions about the contents of the database, please ask!!!! Your task is to develop queries to answer the questions given below, and run them against the demo database. Capture the execution/results in a (spool) file, and turn in the file on disk, and a print out – showing the commands and the successful execution of them. Remember to set the pagesize and the linesize to reasonable numbers so that results are fairly readable! Also, if you wait to spool until you have all of the queries correct (save your work using notepad or wordpad) then you will have a nice clean file that is easy to read and grade, and which will look like you got them all right on the first try! J
THIS IS AN INDIVIDUAL ASSIGNMENT. YOU ARE NOT TO COLLABORATE WITH OTHER STUDENTS!
There are 11 queries, each is worth 1 point; you can get a max of 11/10 on this assignment.
The info mentioned in the query should be used (if I say owner ‘235’, you can use the ID, if I use a name, don’t shortcut by looking up the ID yourself and using the ID)
Queries:
1) Show all info about the office for all offices whose company name is ‘Hager’. (this will wrap badly (messy). That is ok)
2) Show first name, last name, and position for all agents.
3) Show first name, last name, city, and zip for all owners who live in ‘Burlington’ county ‘NJ’ and are ‘Female’, sorted by zip (ascending)
4) For house 1091, show all rentals including the price paid, the name (last and first) and county and state of the renter, and the weekid for the week rented, sorted by the weekid (descending) (Use the HouseID and WeekID to avoid additional tables – this is your first JOIN!)
5) For owner 262, show all rentals of all their houses, including the house street address, the starting date for the week rented, the date reserved, the price paid, and the last name and age of the renter. Sort by house address with ties broken by week starting date. NO SHORTCUTTING by showing Ids instead of the info asked for!!! (This is intended to be a multiple-table JOIN!)
6) Show all houses in ‘New’ condition on ‘Central’ . Show the owner’s owner id, last name and first name, and the house’s street address, and number of bedrooms. Sort by last name of the owner (ascending) (Hint: Houses on Central have all different street numbers, so you need to do something a little special).
7) Show all unique counties of the renters (no duplicates, and no other info other than county).
8) Show the highest price paid for any rental during week ‘235’ in which a discount was received.
9) Show for all numbers of blocks from the beach, the average price paid for rentals. Sort by blocks from the beach ascending.
10) Show the owner id and number of houses owned by all owners who own more than 3 houses. Sort by number of houses owned (descending).
11) Show info about the house with the lowest asking price that is still available for week 235. Show the street address, number of bedrooms, number of bathrooms, blocks from the beach, ac, condition, and asking price.
Design:
OWNER – owner of a beach house
OFFICE – realty office that rents beach houses
· Officeid,
· company,
· officename,
· address,
· city,
· state,
· zip,
· phone,
· fax
AGENT – real estate agent that works for a realty office
· Agentid,
· first,
· middle,
· last,
· officeid - FK to OFFICE (M:1)
· phone,
· position,
· officenum
HOUSE – a beach house
· Houseid,
· address,
· city,
· state,
· zip,
· phone,
· neighborhood,
· bedrooms,
· bathrooms,
· totalrooms,
· cots,
· sleeps,
· floor,
· type,
· blocks,
· facebeach,
· oceanview,
· oceanfront,
· garage,
· parking,
· tvs,
· cable,
· ac,
· heat,
· washer,
· dryer,
· frontporch,
· backporch,
· outsideshower,
· pool,
· hottub, (NOTE: has values of ‘Yes’ or ‘No’)
· tennis,
· beachtags,
· smoking,
· pets,
· securitydep,
· datelisted,
· condition,
· ownerid - FK to OWNER (M:1)
· listagent - FK to AGENT (M:1) (an agent who has arranged with the owner to try to rent the house)
· baseprice
WEEKS – weeks that anybody might rent a beach house (even weird ones)
· Weekid,
· startdate,
· enddate,
· season,
· special
RENTER – a person who might rent a beach house
· Renterid,
· first,
· middle,
· last,
· ender,
· address,
· city,
· county,
· state,
· zip,
· phone,
· age,
· looking,
· agentid - FK to AGENT (M:1) (an agent who is helping the person try to find a house to rent)
AVAIL – a bridge between house and weeks showing all possible weeks that houses may be available (last attribute indicates if it is still available as opposed to already rented)
· Availid,
· houseid - FK to HOUSE (M:1)
· weekid - FK to WEEKS (M:1)
· askingprice,
· available (NOTE –has values of ‘Y’ or ‘N’)
RENTAL – central transaction in the system – a house is rented to a particular renter for a particular week. A particular agent gets credit for the rental (could be the listing agent for the house, the agent working with the renter, or another agent)
· Rentalid,
· houseid - FK to HOUSE (M:1)
· renterid - FK to RENTER (M:1)
· weekid - FK to WEEKS (M:1)
· askprice,
· agentid - FK to AGENT (M:1) (agent who gets credit for the rental)
· reserved,
· discount,
· pricepaid