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 cscora2.lasalle.edu). The schema for the tables is given below. 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!
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.
Schema:
OWNER – owner of a beach house
· Ownerid, first, middle, last, gender, streetaddress, city, county, state, zip, phone
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, tennis, beachtags, smoking, pets, securitydep, datelisted, condition, ownerid, listagent, baseprice
· FK ownerid to OWNER (M:1)
· FK listagent to AGENT (M:1) (an agent who has arranged with the owner to try to rent the house)
· NOTE: hottub has values of ‘Yes’ or ‘No’
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, phone, position, officenum
· FK officeid to OFFICE (M:1)
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, gender, address, city, county, state, zip, phone, age, looking, agentid
§ FK agentid 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, weekid, askingprice, available
§ FK houseid to HOUSE (M:1)
§ FK weekid to WEEKS (M:1)
§ NOTE – available 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, renterid, weekid, askprice, agentid, reserved, discount, pricepaid
§ FK houseid to HOUSE (M:1)
§ FK renterid to RENTER (M:1)
§ FK weekid to WEEKS (M:1)
§ FK agentid to AGENT (M:1) (agent who gets credit for the rental)
Queries:
1) Show all unique neighborhoods of houses (no duplicates, no other info other than neighborhood).
2) Show all info about the week for all weeks whose season is considered to be ‘EarlySeason’
3) 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.
4) For house 4995, show all rentals including the asking price and price paid, the name (last and first) and age of the renter, and the weekid for the week rented, sorted by the weekid (descending)
5) For owner 949, 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, the name (first and last) and home state of the renter, and the name (first and last) of the agent responsible for renting the house (NOT other agents such as the listing agent). Sort by house address with ties broken by week starting date. NO SHORTCUTTING by giving Ids instead of the info asked for!!!
6) Show all rentals for week 249 for houses on ‘Central’ Ave. Show the street address, renter id, agent id, asking price and price paid. Sort by price paid (descending)
7) For houses with a hottub, show the street address, the number it sleeps, the number of bedrooms, the number of bathrooms, and the ratio of people to bathrooms.
8) For all listings (available) that have not been rented yet, what is the lowest asking price?
9) Show for all agents (you can use agentid), the total price paid for all rentals for which they are responsible for (NOT listing agent or working with renter, but actually got credit for the booking). Sort by the total, descending.
10) Show for all weeks with at least one rental, the starting date and the total number of rentals for that week.
11) Show info about the house with the highest 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.