Project Overview

The goal of the project is provide realistic experience in the conceptual design, logical design,

implementation, operation and maintenance of a small relational database using the DBMS taught in class

Description

The application consists of the operations of a real$-$estate office. The office needs to keep track of agents,

buyers, sellers, properties on the market and recently sold properties. This office focuses on homes rather

than business real estate. The management of this real$-$estate office is not very computer literate. You are

being asked to design the database, populate it with sample data and to write several SQL queries to

demonstrate the system. The real$-$estate office is soliciting approximately $50$ proposals.

To learn more about the application domain look at some real$-$estate web sites.

Project Requirements

This section lists the things that you need to do$.$ See below for a specific list of what to turn in$.$

$1.$ Entity$-$relationship model

a$.$ Construct an entity$-$relationship model representing the conceptual design of the database.

At minimum include all of the entity sets mentioned in the handout, include primary key

for each entity set plus at least those attributes mentioned in the handout, and all appropriate

relationship sets. You may make your design more complete based on your study of real

estate web sites

b$.$ Include primary keys in the E$-$R diagram and show the cardinalities of the relationships

$2.$ Relational model

a$.$ Convert the E$-$R schema to a relational database schema. Be sure to include appropriate

indices and constraints. Be sure your relational schema conforms to your E$-$R schema. If

you change your design later in the project don$$t forget to update the E$-$R schema.

Normalize your tables to the $3$ NF$.$

$3.$ Create each of the tables in SQL in your own database management system

$4.$ Populate your tables with a sufficient number of data items $($look at the queries below to know what

type of data to put in your database$).$

$5.$ For simplicity, create images only for $2$ or $3$ houses including the most expensive one and let the

others be null

$6.$ Queries

Construct and execute SQL queries for the following:

a$.$ Find addresses of homes for sale in the city Bethlehem$3$ costing between $20$million cfa and

$25$million cfa

b$.$ Find the addresses of homes for sale in the school district $3$Parkland$3$ with $4$ or more

bedrooms and no swimming pool

c$.$ Find the name of the agent who has sold the most property in the year $2020$ by total cfa

valued. For each agent, compute the average selling price of properties sold in $2020$ and the average

time the property was on the market. Note that this suggests use of date attributes in your

design

e$.$ Show a picture of the most expensive houses$($s$)$ in the database

f$.$ Record the sale of a property that had been listed as being available. This entails storing

the sales price, the buyer $,$ the selling agent, the buyer$$s agent$($if any$\backslash$deg and the date

g$.$ Add a new agent to the database.

Add additional queries to cover interesting data that you captured that we did not consider above.

What to submit

$1.$ Your E$-$R diagram, printed out on paper

$2.$ Your relational schema, print out the create table SQL statements you used

$3.$ Use select count$(*)$ from r for each relation r in your schema so we g get a sense of how big

your tables are

$4.$ Submit the queries in the format

a$)$Query in English

b$)$SQL query

c$)$Answer