A Case Study: Vine Estate Agency Ltd (VEA) is a licensed estate agent, providing a variety of services such as property management, sales, auction, etc. The following is a brief description of the business of VEA:

? VEA staff consists of groups of property managers, property/land sales and auctioneers. Each staff member performs only one type of services. Except for the head manager of VEA, each staff member reports work to his/her supervisor (this thus forms a self reference relationship among staff members).

? VEA keeps a file of properties that are currently tenanted, or ready to be tenanted, or for sale. The data stored on each property includes the type of property (town house, unit, or villa etc., reflecting the number of bedrooms, bath rooms, toilets etc), address, and additional comments. VEA records the ownership information of each property.

? VEA reports the property status to the owners of properties on a monthly basis. For example, if a property is currently tenanted, its monthly report would contain information about the service status such as weekly rent, weeks being rented in the period of the month (together with starting and ending dates), service/maintenance fee, monthly property maintenance and/or inspection history, etc.

? A list of tenants is maintained. The data stored on each tenant includes a tenant number, name, postal address (street, city/town, and post code), category of tenant (e.g., single, partnered or family), contact details, and any additional comments.

? A property may be jointly rented by multiple tenants; however each property has a limitation on the number of people to live in.

? VEA also has a list of candidate tenants who are willing to rent properties. Each candidate is assigned a number as identifier. The list records the candidate name, type of the properties required (e.g., house/unit, number of bedrooms, etc.), and contact details (e.g., phone and/or emails).

? Each time a candidate requests a property; VEA makes an entry in their rent waiting list. This file contains a waiting number, candidate name, anticipated starting date, type of the property required, and the maximum rent the candidate can afford.

? When a property matching a candidates request is available, it is assigned to the candidate tenant on a first-come-first-served basis, and an entry is made in the placement record folder. This folder contains an opening number, candidate number, actual start date, total bonds, weekly rent amount, and end date if the placement is completed. The placement record folder is used by VEA as a source of placement histories for its various services.

Based on the above requirements, you are required to:

1. Draw a suitable logical E-R diagram and then convert it to a physical ERD for VEA, showing clearly primary/foreign keys and other necessary attributes, relationships and their types on the diagram. Some further requirements are as below:

? Your physical ERD should contain the following entities, i.e., Tenant, Property, PropertyOwner, CandidateTenant, WaitingList, and RealEstateAgent (or staff). However there is no limitation on the number of entities.

? For simplicity, exclude any super-type/sub-types. You may choose to store only the main attributes (and ignore others) for each entity. You may provide further assumptions to restrict or explain your design.

2. Generate creation/dropping orders of tables: Give an order of creation of the tables, and an order of dropping tables for your E-R diagram.

Question 2.

Based on the physical ERD achieved in Question 1, implement an operational database for VEA using Transact-SQL. Produce the following scripts ensuring that:

You comment appropriately all scripts;

All scripts have a useful and user-friendly functionality;

(1) Database creation and data population

(a) Produce SQL code to create tables for the entities shown in your ERD, implementing Primary Keys and Foreign Keys (make sure the table creation order is correct and the codes can be run multiple times). Show the tables you have created;

(Submit as A2create.sql)

(b) Produce SQL code to populate the tables created in step (a) with sufficient test data to meet the requirements below, observing referential integrity. Display the data in the three tables Tenant, Property and PropertyOwner. (Submit as A2pop.sql)

(2) Database retrieval:

Free-style queries (i.e., you design scenarios, and you make queries):

For each of the following requirements,

(i) state clearly a query question, presenting as internal comments (for example, --List the names of owners who own more than two properties), to meet that requirement; and

(ii) answer the query question by querying your database.

(c) Produce SQL code to query the database, showing some data from 1 (one) of your tables. (Submit as A2t1.sql)

(d) Produce SQL code to query the database, showing some data from 2 (two) of your tables, using a join. (Submit as A2t2j.sql)

(e) Produce SQL code to query the database, showing some data from 2 (two) of your tables, using an outer join. (Submit as A2t2o.sql)

(f) Produce SQL code to query the database, showing some data from 2 (two) of your tables, using an appropriate join, sorted, and including at least one aggregate function over groups of rows. (Submit as A2t2sa.sql)

(g) Produce SQL code to query the database, showing some data from 3 (three) or more tables, using appropriate joins, including data in foreign key column(s).

(Submit as A2t3jf.sql)

(h) Produce code to query the database, showing some data from your tables, using a subquery. (Submit as A2sub.sql)

Fix-style queries:

Produce SQL scripts to do the following tasks;

(i) Based on your PropertyOwner and Property tables, create a view showing all properties of each owner; display the content of the view; and then query the view to display all owners who own two or less properties. (Submit as A2view.sql)

(j) Write SQL statement(s) to display the staff ID, staff name and the staffs (direct) supervisors ID and name for all staff members who have a supervisor. (Submit as A2self.sql)

(k) Write code to produce a report of your database, with appropriate headings, showing all properties, each with a list of their tenants (if any). (Submit as A2report.sql)

(l) Some tenants may pay more rent than others. Write SQL statement(s) to display the top three tenants who pay highest rent(s). (Submit as A2top.sql)

Presentation

Marks will be given for good work (Just as marks will be deducted for bad work) e.g., good presentation, work submitted as required (e.g., submitted a .zip file, not separate ones; SQL script files with extension .sql), the clarity and difficulty of the query questions (for free query questions in Q2); table dropped properly before creating them (for database creation, Q2), etc.