Answered step by step
Verified Expert Solution
Question
1 Approved Answer
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
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 realestate 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 realestate 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 realestate office is soliciting approximately proposals.
To learn more about the application domain look at some realestate 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
Entityrelationship model
a Construct an entityrelationship 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 ER diagram and show the cardinalities of the relationships
Relational model
a Convert the ER schema to a relational database schema. Be sure to include appropriate
indices and constraints. Be sure your relational schema conforms to your ER schema. If
you change your design later in the project dont forget to update the ER schema.
Normalize your tables to the NF
Create each of the tables in SQL in your own database management system
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
For simplicity, create images only for or houses including the most expensive one and let the
others be null
Queries
Construct and execute SQL queries for the following:
a Find addresses of homes for sale in the city Bethlehem costing between million cfa and
million cfa
b Find the addresses of homes for sale in the school district Parkland with or more
bedrooms and no swimming pool
c Find the name of the agent who has sold the most property in the year by total cfa
valued. For each agent, compute the average selling price of properties sold in 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 housess 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 buyers agentif anydeg 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
Your ER diagram, printed out on paper
Your relational schema, print out the create table SQL statements you used
Use select count from r for each relation r in your schema so we g get a sense of how big
your tables are
Submit the queries in the format
aQuery in English
bSQL query
cAnswer
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started