Programming Homework Help

This assignment calls for you to create the logical model for a relational database based

on a problem statement, and physically implement the database using SQL. There are two

parts to the assignment. Part 1 is the data modeling and normalization of a data set into a

3NF ERD. Part 2 is writing the SQL script to physically implement the tables, populate

them with sample data, and query all of the records.

Part 1: Data modeling and normalization

Create the logical model for the following problem statement. Use Crow’s Foot notation

form to illustrate the ERD, and use dependency diagrams to demonstrate functional

dependencies in 1NF, 2NF, and 3NF as deliverables for the assignment.

Problem Statement:

ABC Real Estate is a very small real estate company owned by James Good. In fact, the

company is so small that James is the only agent in the company. He wants to have a

database that keeps track of key information for his company. Of course, property

information is very important. James wants to be able to store the address (street, city

state, and zip) for each property. He also wants to track the number of bedrooms and

bathrooms and the listing price for each property. Some properties are single-family

homes. For these properties, he wants to store the lot size. For condominiums, he wants

to know the monthly association fee. Information about the area in which each property is

located is also important. James wants to track the name of the area, along with the names

of the schools, including the high school, elementary school, and middle school. He also

wants to store general comments about the area. James uses a variety of advertising

outlets, such as newspapers, magazines, and Web sites, to advertise the properties he is

selling. He wants to track which outlets are used to advertise each property. Keep in mind

that a property may be advertised several times in the same outlet. James also wants to

know when each ad was placed and how much the ad cost. The database must also store

the name and main phone number of the outlet.

Information about each client must also be stored, including first and last names, main

contact phone number, and email address. James wants the database to track which client

sells each property and which client buys each property. Remember that different clients

sell and buy each property. James pays past clients for referring others to his company.

When such referrals result in a sale, he pays the referring client a small fee. Although a

client may earn many fees, James will only pay once for a referred client.

Finally, James sometimes sells properties that are listed by other companies. For these

properties, he wants the database to track which company listed the property. He also

wants to store basic information about each company, including name and phone number.

Part 2: Physically Implementing the Database

Write one SQL script that will create the database, populate it with at least five sample

records, and generate a report to show the records in each table. Provide the SQL script as

a deliverable for the assignment.