Answered step by step
Verified Expert Solution
Link Copied!

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 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 dont 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 Bethlehem3 costing between 20million cfa and
25million cfa
b. Find the addresses of homes for sale in the school district 3Parkland3 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 buyers agent(if any\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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Intranet And Web Databases For Dummies

Authors: Paul Litwin

1st Edition

0764502212, 9780764502217

More Books

Students also viewed these Databases questions

Question

What are the APPROACHES TO HRM?

Answered: 1 week ago