Assignment Information

The following relational schema (primary keys are underlined) are used by the real estate company as described mostly in Assignment 1, with one extra specification that "When a listed property is sold, it is sold by a realtor that may or may not be same as the listing realtor". The ER diagram in Assignment 2 now reflects the extra specification.

Properties(folio, civicAddress, zoning, builtYear, description)
MajorProjects(folio, year, description)
Realtors(RID, serviceName, name, contactInfo, expLevel)
Listings(listingID, folio, listedBy, listDate, status, description, askingPrice)
ClosedListings(listingID, soldBy, closingDate, purchasePrice)
Clients(email, name, otherInfo)
Leads(email, listingID, interest)

This assignment is based on the above relational schema.

Many more details, including constraints, data types, etc, are captured in the SQL definitions of these tables.

You may use the supplied table definitions to create empty tables. You will then need to populate the tables (insert sample data) yourself in order to test your SQL queries.

Your Tasks:

Express each of the following two queries in an equivalent single SQL query and Relational Algebra query respectively against the relational schema defined above. The extra requirement is that the SQL query must be a pure PSJ (projection, selection and join) one that doesn't have ANY sub-queries.

1. List the service name and contact information of each realtor who holds at least one active listing now.
   (There shouldn't be any duplicates in the result.)
2. List the service name and contact information of each realtor who doesn't hold any active listing now.

Express each of the following two queries in an equivalent single SQL query and Datalog query respectively against the relational schema defined above. (No extra requirement.)

1. For the very latest sold property or properties, show its/their listed property's civic address, its/their asking price and its/their purchasing price.
2. For each client who showed any interest in EVERY active listing with an aksing price less than or equal to $300,000, show the client's email. (Probably because that's a useful lead.)

A realtor with the service name "Eric Dunn" (remember that service names are unique and can be used to identify a realtor) has several of his listed property pending to be sold by himself with the purchasing price of each sale exactly $1200 higher than the asking price of the corresponding property's listing.

Today, all these pending sales (the status of these listings are "Pending") went through, which means that the status of these listings should be updated to "Sold", and these listings should be recored to the ClosedListings table.

Because these sales all have the same pattern, bulk SQL update statements can be used to update the content of the relevant tables in the database efficiently.

Your task is to write these SQL statements to perform the above mentioned two updates. Properly order your statements so that the updates can be performed efficiently by bulk SQL update statements and no information is lost in the process.

How to Submit

You are expected to include a legend to explain your relational algebra operators used in this assignment, and to include your Datalog and SQL queries in a text file (with the extension name .txt or .sql), and to include your Relational Algebra queries in the same text file or in a separated PDF file.

Make your queries logically correct and easy to understand. Make sure that your SQL queries can be successfully executed by Oracle database engine.

You can submit your assignment solutions using one of the following two ways:

• Submit a hard copy before the due date/time.
• Login to your VIU Learn account, find the CSCI 370 course page, click on the "Assessment" drop-down menu, click on the "Assignments" item, then click on the folder named "A6". Then you can click on the "Add a File" button to browse and upload your solution file(s).