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 pages 2-3. 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 ‘361’, 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 week for all weeks whose season name is ‘Summer’.
2) Show company and office name for all offices.
3) Show all unique neighborhoods of the houses (no duplicates, and no other info other than neighborhood).
4) Show first name, last name, city, and zip for all owners who live in ‘Camden’ county ‘NJ’ and are ‘Male’, sorted by zip (ascending)
5) Show all houses that sleep at least 15 people, have ‘Central’ air conditioning (ac), and are less than 1 block from the beach – for each, show the houseid, address (skip city, state, and zip), bedrooms, bathrooms, sleeps, and condition.
6) For house 7900, 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!)
7) Show all houses in ‘New’ condition with base prices (this is in House!) from $1900-$2000 . 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).
8) For owner 361, 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!)
9) Show the lowest price paid for any rental during week ‘235’ in which the discount was zero.
10) Show for all numbers of bedrooms, the average price paid for rentals. Sort by number of bedrooms ascending.
11) Show info about houses available for week 235 with the highest known number of bedrooms. Show the street address, number of bedrooms, number of bathrooms, blocks from the beach, ac, condition, and asking price. Sort by asking price descending.
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