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 logically.

Your Tasks:

Express each of the following queries in an equivalent Relational Algebra query, Datalog query and SQL query respectively against the relational schema defined above.
(Note that your relational algebra and datalog queries can borrow some comparison operators from SQL, such as "is null" and "like", and functions from Oracle SQL, such as to_date and to_char.)

1. For any listing started in November 2025, show its listed property's civic address, its status, its listing realtor's service name, and its asking price.
2. For each sold property that was listed and sold by different realtors, show its listed property's civic address, the service names of its listing realtor and sold by realtor respectively, and its asking price and purchasing price respectively.
3. For each realtor who has more than 10 years of experience and who has listed at least one property in year 2025, show the realtor's service name and contact information.
   There shouldn't be any duplicates in the result.
4. For each realtor who listed at least one property in a year that's not 2025, show the realtor's service name and experience level.
   There shouldn't be any duplicates in the result.
5. For each realtor who had never listed any property in year 2025, list the realtor's service name and experience level.

How to Submit

You are expected to include a legend to explain your relational algebra operators used in this assignment if they are different from the ones discussed in our lectures, 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 "A4". Then you can click on the "Add a File" button to browse and upload your solution file(s).