Database Systems MySQL Project
Database Systems
MySQL Project
In this assignment you are given a database schema, including a set of integrity
constraints, and asked to implement and use this schema in the MySQL RDMS.
Given that an empty database has been created, this entails creating the necessary
tables, initializing them with some test data, and executing a variety of queries.
You are then asked to draw the associated ER model. This admittedly is doing
things a bit in reverse, since the normal procedure is to create the ER model first,
and then use this to produce the database schema. Finally, you are asked to develop
either a stand-alone software package or a website that interacts with you
database.
Problem Context
A real estate multiple listing service (MLS) keeps track of the houses and business
properties for sale, the real estate agents that posts the listing to the database, and
the potential buyers that are working with the agents. Houses and business
properties are kinds of property. A listing entry shows information about the house
or business property and the agent that posted the listing. Each agent works for a
eal estate firm. Buyers who are looking for houses or business properties work
with a real estate agent who has access to the MLS. Following is an analysis of the
information requirements and integrity constraints.
Information Requirements
1. Property: Has an address, owner’s name, and price.
2. House: Inherits the information required by a property and in addition has
the number of bedrooms, number of bathrooms, and size in square feet.
Make the House address a foreign key reference to a Property address.
3. Business Property: Inherits the information required by a property and in
addition has the type of business, e.g., gas station, store front, office space,
etc., and the size in square feet. Make the Business Property address a
foreign key reference to a Property address.
4. Agent: Has an id, a name and phone number. An agent’s employment record
contains the date when his/her employment started.
5. Firm: Has an id, a name, and an address.
6. Buyer: Has an id, a name, a phone number, and a set of preferences
consisting of the type of property in which the buyer is interested (house o
usiness property) and, if a house, the desired number of bedrooms and
athrooms, and, if a business, the desired type of business property, and, fo
either a house or business property, the desired price range.
7. Listing: Has an MLS number, the property address, the agent’s id, and the
date the listing was posted. The MLS number serves as an id.
Database Schema
● Property ( address: VARCHAR(50), ownerName: VARCHAR(30), price
INTEGER )
● House ( bedrooms: INTEGER, bathrooms: INTEGER, size INTEGER,
address: VARCHAR(50))
[Make the House address a foreign key reference to a Property address.]
● BusinessProperty ( type: CHAR(20), size: INTEGER, address:
VARCHAR(50) )
[Make the BusinessProperty address a foreign key reference to a Property
address.]
● Firm ( id: INTEGER, name: VARCHAR(30), address VARCHAR(50) )
● Agent ( agentId: INTEGER, name VARCHAR(30), phone CHAR(12),
firmId INTEGER, dateStarted DATE )
● Listings ( address: VARCHAR(50), agentId: INTEGER, mlsNumber:
INTEGER, dateListed DATE)
● Buyer ( id: INTEGER, name VARCHAR(30), phone CHAR(12),
propertyType: CHAR(20), bedrooms INTEGER, bathrooms INTEGER,
usinessPropertyType: CHAR(20), minimumPrefe
edPrice: INTEGER,
maximumPrefe
edPrice: INTEGER )
● Works_With( buyerId: INTEGER, agentID:INTEGER )
Integrity Constraints
1. A property is identified by its address.
2. A property can be listed by at most one agent.
3. A listing is identified by its MLS number.
4. An agent works for one, and only one, firm.
Data Requirements
1. Insert sufficiently many records into your tables to return at least 1 record
for each of the queries below.
2. In any case, insert at least 5 records into each table.
3. Indicate the primary keys, foreign keys, and any non-null fields
appropriately.
You are to turn in:
1. An ER diagram that translates into this schema.
2. A MySQL script file that creates the tables that implement the given relation
schemas and populates these tables with test data according to the
instructions given above.
3. A MySQL script file containing SQL instructions that implement the queries
described below.
4. A document showing the output resulting from each of the ten queries
shown below.
5. Instructions on how to run or a video demonstration of the database
interacting with your web page or program.
The queries are:
1. Find the addresses of all houses cu
ently listed.
2. Find the addresses and MLS numbers of all houses cu
ently listed.
3. Find the addresses of all 3-bedroom, 2-bathroom houses cu
ently listed.
4. Find the addresses and prices of all 3-bedroom, 2-bathroom houses with
prices in the range $100,000 to $250,000, with the results shown in
descending order of price.
5. Find the addresses and prices of all business properties that are advertised as
office space in descending order of price.
6. Find all the ids, names and phones of all agents, together with the names of
their firms and the dates when they started.
7. Find all the properties cu
ently listed by agent with id “001” (or some othe
suitable id).
8. Find all Agent.name-Buyer.name pairs where the buyer works with the
agent, sorted alphabetically by Agent.name.
9. For each agent, find the total number of buyers cu
ently working with that
agent, i.e., the output should be Agent.id-count pairs
10.For some buyer that is interested in a house, where the buyer is identified by
an id (e.g., “001”), find all houses that meet the buyer’s preferences, with the
esults shown in descending order of price.
Some basic facts about MySQL
1. To see the databases in your account use:
show databases;
2. To use a particular database use:
use
;
3. To see all the tables in your database (after doing the above) use:
show tables;
4. To see the schema for a particular table use:
describe table-name;
5. A MySQL statement can span multiple lines, and it must end with ;
For example:
select * from
eturn leads to a new line
property;
eturn causes the statement to be executed;
6. Exit MySQL with exit;
eturn
How to create and execute a script file:
1) Write your SQL commands into a text file with the extension .sql (the extension
isn’t actually necessary, but it is customary to use this).
2) Put the (script).sql file in some directory in your program account.
3) Navigate to that directory, log into your MySQL database using the instructions
given above, and then execute either “source (script).sql” or “./ (script).sql”.
Interacting with the database
The last requirement of this project is to develop either a stand-alone software
package or a website that interacts with your database, using whateve
programming language or web technology that you wish. Your program or website
should be able to do as much as possible of the following:
1. Display all listings and their associated property info. Break out separate
lists for houses and business properties.
2. Search houses based on price range, number of bedrooms, and number of
athrooms.
3. Search business properties based on price range and range of size in square
feet.
4. Display all agents and their associated info.
5. Display all buyers and their associated info, including preferences.