Database Design Project

Phase II: Detailed design report

Real Estate Listing and Purchase

Dipanjan Chakraborty(dchakr1@cs.umbc.edu)

Shamit Patel(spatel22@cs.umbc.edu)

Prapti Sen(psen1@cs.umbc.edu)
CONTENTS

1. Revisions to the original design.

a. Refinements in the design

b. New Relation Schemas in the 1NF form

c. Changed ER diagram.

2. Detailed Graphical User Interface

a. Selling Agent

b. Listing Agent

c. Home Owner

d. Home Buyer

3. SQL statements to create the relation schemas and constraints

4. PL/SQL packages (pseudo codes) for the different front end functions

a. Package Listing Agent

b. Package Home Owner

c. Package Home Buyer

d. Package Selling Agent

e. Package general Queries

5. Contribution log.

1. Revisions to the original design

a. Refinements in the Design

1. Inclusion of passwords and Authentication

To include the facility of secure transactions in the system, we have decided to include the passwords of the respective users in the tables, which the users can access. This has been decided based on the views of the respective users on different tables in the system. In order to restrict update on tuples which dosent belong to a particular user, we have kept the corresponding password in the tables. Apart from that, there would also be a table which would contain the user SSN and the corresponding password and the type of agent he is registered as in the database. This table is used for initial authentication.

2. Inclusion of a few more attributes in the BUYS relationship set. We have decided to include the offer_id and the requirement_id in the table BUYS in order to facilitate some more complex queries to the system.

3. Inclusion of home-id to uniquely identify a home in the database which has been put up for sale. This reduces the size of the database since the whole address is now not required to be kept in the different relationship sets.

b. New Relation Schemas in the 1NF form

1. HOME: <home_id, apartment_no, street, city, state, zipcode, country, house_type, bedroom, bathroom, garaze_size, style, construction_month, construction_day, construction_year, area, fireplace, basement, basement_details, gif_name>

2. DETAILS: <zipcode, locality, house_type, base_price, extra_bedroom_price, extra_bathroom_price, garage_price, lawn_price, carpet_price, ac_price, furnishing_price>

3. HOME_OWNER: <home_owner_ssn, password_home_owner, last_name, middle_name, first_name, apartment_no, street, city, state, zipcode, country, residence_phone_area_code, residence_phone_number, apartment_no, street, city, state, zipcode, country, office_phone_area_code, office_phone_number, other_business, secondary_owner_ssn, secondary_owner_name, email>

4. HOME_BUYER: < home_buyer_ssn, password_home_buyer, last_name, middle_name, first_name, residence_street, residence_city, residence_state, residence_zipcode, residence_country, residence_phone_area_code, residence_phone_number, office_street, office_city, office_state, office_zipcode, office_country, office_phone_area_code, office_phone_number, other_business, email>

5. LISTING_AGENT: < listing_agent_ssn, password_listing_agent, last_name, middle_name, first_name, residence_street, residence_city, residence_state, residence_zipcode, residence_country, residence_phone_area_code, residence_phone_number, office_street, office_city, office_state, office_zipcode, office_country, office_phone_area_code, office_phone_number, other_business, email>

6. SELLING_AGENT: < selling_agent_ssn, password_selling_agent, last_name, middle_name, first_name, residence_street, residence_city, residence_state, residence_zipcode, residence_country, residence_phone_area_code, residence_phone_number, office_street, office_city, office_state, office_zipcode, office_country, office_phone_area_code, office_phone_number, other_business, email>

7. OWNS: <home_owner_ssn, home_id, password_home_owner >

8. BUYS: <home_buyer_ssn, home_id, password_home_buyer, requirement_id, offer_id>

9. CONTRACT_LA: <home_owner_ssn, listing_agent_ssn, password_home_owner, month_of_contract, day_of_contract, year_of_contract, house_street, house_city, house_state, house_zipcode, house_country, commission>

10. PUT_FOR_SALE: < home_id, listing_agent_ssn, password_listing_agent, listing_month, listing_day, listing_year, asking_price, month_of_sale, day_of_sale, year_of_sale>

11. DECISION: < apartment_no, street, city, state, zipcode, country, offer_id, listing_agent_ssn, password_listing_agent, month_of_acceptance, day_of_acceptance, year_of_acceptance, home_owner_ssn, who_accepted_ssn, selling_price>

12. CONTRACT_SA: < home_buyer_ssn, selling_agent_ssn, password_home_buyer, month_of_contract, day_of_contract, year_of_contract, commission>

13. MAKE_OFFER:< home_id, offer_id, selling_agent_ssn, password_selling_agent >

14. HOME_OFFER:<offer_id, home_id, month_of_offer, day_of_offer, year_of_offer, offer_hour, offer_minutes, offer_seconds, offered_price>

15. HOME_BUYER_REQUIREMENT:<home_buyer_ssn, password_home_buyer, requirement_id, house_type, bedroom, bathroom, garage_size, style, area, fireplaces, basement, basement_details, price_limit>

ER DIAGRAM FOR THE SYSTEM

2. Detailed GUI

a. Selling agent

2.3. View transactions/houses

a. All clients under SA.

b. All houses sold on/before some date.

c. Houses sold to a particular buyer.(Buyer SSN given)

d. Requirements of a particular client.

e. Latest requirement of a particular client.

f. Houses with some particular requirement.

g. Houses with asking price in a particular range.

h. Houses with offered price in a particular range.

i. Commission of other selling agents.

j. Date of contract with a particular client.

k. Enquire prices of houses in a given locality.

2.2. Make Offer

Home ID:

Offered Price:

2. Selling Agent Scope

Update Details

Make Offer

View transactions/houses

Please login as a as a SA,LA,HO,HB.

Login :

Password:

I am a New User.

		1. Insert Selling Agent Details SSN: Name: Office Address: Residence Address: Office Phone: Residence Phone: Email: Other profession:
				2.2.1. This is the offer you have made Home ID: Offer ID: Address: Date of Offer: Offered Price: Time of Offer:
2.1. Update Selling Agent Details Name: Office Address: Residence Address: Office Phone: Residence Phone: Email: Other profession:

b. Listing Agent

1. Main screen ()

Login as

· Home Buyer

· Home Owner

· Selling Agent

· Listing Agent

· New user

Submit

1.2. Login screen ()

Listing Agent details

Selling_Agent_SSN:

Name:

Password:

Retype the password:

Office Address:

Office Phone:

Residence Address:

Residence Phone:

E-mail:

Other Profession:

1.

3.1 Select House

3.2 View Report/Queries

3.3 View Client

3.4 Change Details

2

2.1

House Details

View Offer

sorted by Time

Price

ID

2.1.1

Home Id Address OfferId Offered Price Time of Offer

1.

2.

3.

Select Offer

2.1.2

2.1.3

2.2

View Report

HomeID Address Price Time of offer

Queries

Client List

1. View Offer for Client 1

2. View Offer for Client 2

3.

New n Add Client n

New n+1 Add Client n+1

2.3

2.3.1

View Offer For Client number

Offer ID Price Selling agent

2.3.2

Add client details

Name

House Owner SSN

Secondary Owner SSN

Email

Residence

Residence Phone

Office

Office phone

Other business

2.3.4

Change details

Listing Agent details

Selling_Agent_SSN:

Name:

Password:

Retype the password:

Office Address:

Office Phone:

Residence Address:

Residence Phone:

E-mail:

Other Profession

c. GUI design for Home Owner

Sign Up:

1

Home owner Details:

Name

Email

Residence

Residence Phone

Office

Office Number

Primary Owner SSN

Secondary Owner SSN

Other Business

Select Listing Agent:

Sorted by Number OF days on market

Price of Houses sold

1.1.1

2.

1. View Data OF Home Sales
2. View Offers
3. Get Estimates
4. Change Details

Back

Home Sale Information

HomeID Address Price Owner

2.1

2.2

Offers sorted

1. Time
2. Price

2.2.1

Home Id Address OfferId Offered Price Time of Offer

1.

2.

3.

2.3

2.3.1

d. GUI Design for Home Buyer

1. Main screen (goto 2)

Login as

· Home Buyer

· Home Owner

· Selling Agent

· Listing Agent

· New user

Submit

1.2. Login screen (goto 3)

1.1.Options

· Select a Selling Agent

· Insert/update/view your requirements

· Enquire house prices

· Update your personal information

· Get updated information on your transaction

1.1.1. Selection of Selling Agent

Select Selling Agent based on

· Maximum houses sold

· Minimum average prices of houses sold

· Maximum houses sold in a locality

1.1.1.1à1.1.1.4

1.1.2. Insertion/deletion/view requirements

1.1.2.1.Insert Screen

1.1.2.2. Update Screen

1.1.2.3. View Requirements: Same as Update Screen without the submit button

1.1.3. Enquire house prices

Locality ZipCode

Details

No Of Bedrooms No. Of Garage ……

1.1.4. Update personal information

1.1.5 Get updated information on your transaction

· Check whether your Selling agent has accepted a house

· Enquire details about your selected selling agent

· No. House has not been selected

· Yes!

· Verify details of your house

· Verify details of the transaction

1.1.5.1

1.1.5.1.1 House Details

1.1.5.2. Enquire details of your Selling Agent

3. SQL statements to create the relation schemas and constraints

1. Table HOME

CREATE TABLE home

(

Home_ID NUMBER(4) NOT NULL PRIMARY KEY,

apartment_no NUMBER (4) CONSTRAINT postv_apt

CHECK (apartment_no > 0),

street CHAR(25) NOT NULL,

city CHAR(25) NOT NULL,

state CHAR(4) NOT NULL,

zipcode NUMBER(9) NOT NULL CONSTRAINT postv_zip

CHECK (zipcode > 0),

country CHAR(25) NOT NULL,

house_type CHAR(25) ,

bedroom NUMBER(3) CONSTRAINT postv_bed

CHECK (bedrooom >= 0),

bathroom NUMBER (4) CONSTRAINT postv_bath

CHECK (bathroom >= 0),

garaze_size NUMBER(6) CONSTRAINT postv_garage

CHECK (garaze_size >= 0),

style CHAR(20),

construction_month NUMBER(2) NOT NULL CONSTRAINT postv_mon

CHECK (construction_month > 0),

construction_day NUMBER (2) NOT NULL CONSTRAINT postv_day

CHECK (construction_day > 0),

construction_year NUMBER (4) NOT NULL CONSTRAINT postv_year

CHECK (construction_year > 0),

area NUMBER (6) CONSTRAINT postv_area

CHECK (area > 0),

fireplace NUMBER (2) CONSTRAINT postv_fire

CHECK (fireplace > 0),

basement CHAR(3) CONSTRAINT postv_base

CHECK (basement >= 0),

basement_details CHAR(40),

gif_name CHAR(40)

);

2. Table DETAILS

CREATE TABLE details

(

zipcode NUMBER(9) NOT NULL PRIMARY KEY,

locality CHAR(30) NOT NULL,

house_type CHAR(25) ,

base_price NUMBER(6) NOT NULL CONSTRAINT pos_base_pr

CHECK (base_price > 0),

bedroom_price NUMBER (4) NOT NULL CONSTRAINT pos_bed_pr

CHECK (bedroom_price > 0),

bathroom_price NUMBER (4) NOT NULL CONSTRAINT pos_bath_pr

CHECK (bathroom_price > 0),

garage_price NUMBER (4) NOT NULL CONSTRAINT pos_bath_pr

CHECK (garage_price > 0),

lawn_price NUMBER (4) NOT NULL CONSTRAINT pos_lawn_pr

CHECK (lawn_price > 0),

carpet_price NUMBER (4) NOT NULL CONSTRAINT pos_carp_pr

CHECK (carpet_price > 0),

ac_price NUMBER (4) NOT NULL CONSTRAINT pos_ac_pr

CHECK (ac_price > 0),

furnishing_price NUMBER (4) NOT NULL CONSTRAINT pos_fur_pr

CHECK (furnishing _price > 0)

);

3. Table HOME_OWNER

CREATE TABLE home_owner

(

home_owner_ssn NUMBER(9) NOT NULL PRIMARY KEY,

last_name CHAR(20) NOT NULL,

middle_name CHAR(15) ,

first_name CHAR(20) NOT NULL,

home_apartment_no NUMBER (4) NOT NULL CONSTRAINT pos_home_no CHECK (home_apartment_no > 0)

home_street CHAR(25) NOT NULL,

home_city CHAR(25) NOT NULL,

home_state CHAR(4) NOT NULL,

home_zipcode NUMBER(9) NOT NULL CONSTRAINT pos_home_zip CHECK (home_zipcode > 0)

home_country CHAR(25) NOT NULL,

home_phone_area_code NUMBER(3) NOT NULL CONSTRAINT pos_ph_ar_cd CHECK (home_phone_area_code > 0)

home_phone_number NUMBER(7) NOT NULL CONSTRAINT pos_ph_num CHECK (home_phone_number > 0)

office_apartment_no NUMBER(4) NOT NULL CONSTRAINT pos_apt_no CHECK (office_apartment_no > 0)

office_street CHAR(25) NOT NULL,

office_city CHAR(25) NOT NULL,

office_state CHAR(4) NOT NULL,

office_zipcode NUMBER(9) NOT NULL CONSTRAINT pos_off_zip CHECK (office_zipcode > 0)

office_country CHAR(25) NOT NULL,

office_phone_area_code NUMBER(3) NOT NULL CONSTRAINT pos_off_ph_ar CHECK (office_phone_area_code > 0)

office_phone_number NUMBER(7) NOT NULL CONSTRAINT pos_off_ph CHECK (office_phone_number > 0)

other_business CHAR(25) ,

secondary_owner_ssn NUMBER(9) NOT NULL CONSTRAINT pos_sec_ssn CHECK (secondary_owner_ssn > 0)

secondary_owner_name CHAR(60) NOT NULL,

email CHAR(40) NOT NULL

);

4. Table HOME_BUYER

CREATE TABLE home_buyer

(

home_buyer_ssn NUMBER(9) NOT NULL PRIMARY KEY,

last_name CHAR(20) NOT NULL,

middle_name CHAR(15),

first_name CHAR(20) NOT NULL,

home_apartment_no NUMBER (4) NOT NULL CONSTRAINT pos_hb_apt CHECK (home_apartment_no > 0)

home_street CHAR(25) NOT NULL,

home_city CHAR(25) NOT NULL,

home_state CHAR(4) NOT NULL,

home_zipcode NUMBER(9) NOT NULL CONSTRAINT pos_hb_zip CHECK (home_zipcode > 0),

home_country CHAR(25) NOT NULL,

home_phone_area_code NUMBER(3) NOT NULL CONSTRAINT pos_hb_ph_ar CHECK (home_phone_area_code > 0),

home_phone_number NUMBER(7) NOT NULL CONSTRAINT pos_hb_ph CHECK (home_phone_number > 0),

office_apartment_no NUMBER(4) NOT NULL CONSTRAINT pos_hb_off CHECK (office_apartment_no > 0),

office_street CHAR(25) NOT NULL,

office_city CHAR(25) NOT NULL,

office_state CHAR(4) NOT NULL,

office_zipcode NUMBER(9) NOT NULL CONSTRAINT pos_hb_zip CHECK (office_zipcode > 0)

office_country CHAR(25) NOT NULL,

office_phone_area_code NUMBER(3) NOT NULL CONSTRAINT pos_hb_ph_ar CHECK (office_phone_area_code > 0)

office_phone_number NUMBER(7) NOT NULL CONSTRAINT pos_hb_ph CHECK (office_phone_number > 0)

other_business CHAR(25),

email CHAR(40) NOT NULL

);

5. Table LISTING_AGENT

(constraints same as the above two cases)

CREATE TABLE listing_agent

listing_agent_ssn NUMBER(9) NOT NULL PRIMARY KEY,

last_name CHAR(20) NOT NULL,

middle_name CHAR(15),

first_name CHAR(20) NOT NULL,

home_apartment_no NUMBER (4) NOT NULL,

home_street CHAR(25) NOT NULL,

home_city CHAR(25) NOT NULL,

home_state CHAR(4) NOT NULL,

home_zipcode NUMBER(9) NOT NULL,

home_country CHAR(25) NOT NULL,

home_phone_area_code NUMBER(3) NOT NULL,

home_phone_number NUMBER(7) NOT NULL,

office_apartment_no NUMBER(4) NOT NULL,

office_street CHAR(25) NOT NULL,

office_city CHAR(25) NOT NULL,

office_state CHAR(4) NOT NULL,

office_zipcode NUMBER(9) NOT NULL,

office_country CHAR(25) NOT NULL,

office_phone_area_code NUMBER(3) NOT NULL,

office_phone_number NUMBER(7) NOT NULL,

other_business CHAR(25) NOT NULL,

email CHAR(40)

);

6. Table SELLING_AGENT

(constraints same as the above )

CREATE TABLE selling_agent

selling_agent_ssn NUMBER(9) NOT NULL PRIMARY KEY,

last_name CHAR(20) NOT NULL,

middle_name CHAR(15) NOT NULL,

first_name CHAR(20) NOT NULL,

home_apartment_no NUMBER (4) NOT NULL,

home_street CHAR(25) NOT NULL,

home_city CHAR(25) NOT NULL,

home_state CHAR(4) NOT NULL,

home_zipcode NUMBER(9) NOT NULL,

home_country CHAR(25) NOT NULL,

home_phone_area_code NUMBER(3) NOT NULL,

home_phone_number NUMBER(7) NOT NULL,

office_apartment_no NUMBER(4) NOT NULL,

office_street CHAR(25) NOT NULL,

office_city CHAR(25) NOT NULL,

office_state CHAR(4) NOT NULL,

office_zipcode NUMBER(9) NOT NULL,

office_country CHAR(25) NOT NULL,

office_phone_area_code NUMBER(3) NOT NULL,

office_phone_number NUMBER(7) NOT NULL,

other_business CHAR(25) NOT NULL,

email CHAR(40)

);

7. Table OWNS

CREATE TABLE owns

(

home_owner_ssn NUMBER(9) NOT NULL FOREIGN KEY,

home_id NUMBER(9) NOT NULL FOREIGN KEY

);

8. Table BUYS

CREATE TABLE buys

(

home_buyer_ssn NUMBER(9) NOT NULL FOREIGN KEY ,

password_home_buyer CHAR(25) NOT NULL FOREIGN KEY,

home_id NUMBER(9) NOT NULL FOREIGN KEY requirement_id NUMBER(10) NOT NULL FOREIGN KEY

offer_id NUMBER(10) NOT NULL FOREIGN KEY

)

9. Table CONTRACT_LA

CREATE TABLE contract_la

(

home_owner_ssn NUMBER(9) NOT NULL FOREIGN KEY,

password_home_owner CHAR(30) NOT NULL FOREIGN KEY;

listing_agent_ssn NUMBER(9) NOT NULL FOREIGN KEY,

month_of_contract NUMBER(2) NOT NULL CONSTRAINT pos_mo_cont CHECK (month_of_contract > 0)

day_of_contract NUMBER(2) NOT NULL CONSTRAINT pos_day_cont CHECK (day_of_contract > 0)

year_of_contract NUMBER(4) NOT NULL CONSTRAINT pos_year_cont CHECK (year_of_contract > 0)

house_street CHAR(25) NOT NULL FOREIGN KEY,

house_city CHAR(25) NOT NULL FOREIGN KEY,

house_state CHAR(4) NOT NULL FOREIGN KEY,

house_zipcode NUMBER(9) NOT NULL FOREIGN KEY,

house_country CHAR(25) NOT NULL FOREIGN KEY,

commission NUMBER(4) NOT NULL CONSTRAINT pos_comm CHECK (commission > 0)

);

10. Table PUT_FOR_SALE

CREATE TABLE put_for_sale

(

home_id NUMBER(9) NOT NULL FOREIGN KEY,

listing_agent_ssn NUMBER(9) NOT NULL FOREIGN KEY,

password_listing_agent CHAR(30) NOT NULL FOREIGN KEY,

listing_month NUMBER(2) NOT NULL CONSTRAINT pos_mo_lis CHECK (listing_month > 0)

listing_day NUMBER(2) ) NOT NULL CONSTRAINT pos_day_lis CHECK (listing_day > 0)

listing_year NUMBER(4) NOT NULL CONSTRAINT pos_year_lis CHECK (listing_year > 0),

asking_price NUMBER(9) NOT NULL CONSTRAINT pos_ask_prc CHECK (asking_price > 0),

month_of_sale NUMBER(2) NOT NULL CONSTRAINT pos_mo_sale CHECK (month_of_sale > 0),

day_of_sale NUMBER(2) NOT NULL CONSTRAINT pos_day_sale CHECK (day_of_sale > 0),

year_of_sale NUMBER(4) NOT NULL CONSTRAINT pos_year_sale CHECK (year_of_sale > 0)

);

11. Table DECISION

CREATE TABLE decision

(

home_id NUMBER(9) NOT NULL FOREIGN KEY ,

offer_id NUMBER(9) NOT NULL FOREIGN KEY,

listing_agent_ssn NUMBER(9) NOT NULL FOREIGN KEY,

password_listing_agent CHAR(30) NOT NULL FOREIGN KEY,

month_of_acceptance NUMBER(2) NOT NULL CONSTRAINT pos_mo_acc CHECK (month_of_acceptance > 0),

day_of_acceptance NUMBER(2) NOT NULL CONSTRAINT pos_day_acc CHECK (day_of_acceptance > 0),

year_of_acceptance NUMBER(4) NOT NULL CONSTRAINT pos_year_acc CHECK (year_of_acceptance > 0),

home_owner_ssn NUMBER(9) NOT NULL FOREIGN KEY,

selling_agent_ssn NUMBER(9) NOT NULL FOREIGN KEY,

selling_price NUMBER(9) NOT NULL CONSTRAINT selling_price CHECK (selling_price > 0)

);

12. Table CONTRACT_SA

CREATE TABLE contract_sa

(

home_buyer_ssn NUMBER(9) ) NOT NULL FOREIGN KEY,

selling_agent_ssn NUMBER(9) ) NOT NULL FOREIGN KEY,

password_home_buyer CHAR(30) ) NOT NULL FOREIGN KEY,

month_of_contract NUMBER(2) NOT NULL CONSTRAINT pos_mo_sa_con CHECK (month_of_contract > 0),

day_of_contract NUMBER(2) ) NOT NULL CONSTRAINT pos_day_sa_con CHECK (day_of_contract > 0),

year_of_contract NUMBER(4) ) NOT NULL CONSTRAINT pos_year_sa_con CHECK (year_of_contract > 0),

commission NUMBER(4) ) NOT NULL CONSTRAINT pos_comm._sa CHECK (commission > 0),

,

);

13. Table MAKE_OFFER

CREATE TABLE make_offer

(

home_id NUMBER(9) NOT NULL FOREIGN KEY,

offer_id NUMBER(9) NOT NULL PRIMARY KEY,

selling_agent_ssn NUMBER(9) NOT NULL FOREIGN KEY,

password_selling_agent CHAR(30) NOT NULL FOREIGN KEY,

offered_price NUMBER(5) ) NOT NULL CONSTRAINT offer_pos_price CHECK (offered_price > 0),

);

14. Table HOME_OFFER

CREATE TABLE home_offer

(

home_id NUMBER(9) NOT NULL FOREIGN KEY,

offer_id NUMBER(9) NOT NULL FOREIGN KEY,

month_of_offer NUMBER(2) NOT NULL CONSTRAINT pos_mo_off CHECK (month_of_offer > 0),

day_of_offer NUMBER(2) NOT NULL CONSTRAINT pos_day_off CHECK (day_of_offer > 0),

year_of_offer NUMBER(4) NOT NULL CONSTRAINT pos_year_off CHECK (year_of_offer > 0),

offer_hour NUMBER(2) NOT NULL CONSTRAINT pos_off_hr CHECK (offer_hour > 0),

offer_minutes NUMBER(2) NOT NULL CONSTRAINT pos_off_min CHECK (offer_minutes > 0) ,

offer_seconds NUMBER(2) NOT NULL CONSTRAINT pos_off_sec CHECK (offer_seconds > 0),

offered_price NUMBER(6) NOT NULL CONSTRAINT pos_off_prc CHECK (offered_price > 0)

);

15. Table HOME_BUYER_REQUIREMENT

CREATE TABLE home_buyer_requirement

(

home_buyer_ssn NUMBER(9) NOT NULL FOREIGN KEY,

password_home_buyer CHAR(30) NOT NULL FOREIGN KEY,

requirement_id NUMBER(9) NOT NULL PRIMARY KEY,

house_type CHAR(25) NOT NULL,

bedroom NUMBER(3) NOT NULL CONSTRAINT pos_bed_req CHECK (bedroom > 0),

bathroom NUMBER (4) NOT NULL CONSTRAINT pos_bath_req CHECK (bathroom > 0),

garaze_size NUMBER(6) NOT NULL CONSTRAINT pos_gar CHECK (garze_size> 0),

style CHAR(20) NOT NULL,

area NUMBER(9) NOT NULL CONSTRAINT pos_ar CHECK (area> 0),

fireplace NUMBER (2) NOT NULL CONSTRAINT pos_fire CHECK (fireplace> 0),

basement CHAR(3) NOT NULL ,

basement_details CHAR(40) NOT NULL,

price_limit NUMBER(6) NOT NULL CONSTRAINT pos_price CHECK (price_limit> 0),

);

4. PL/SQL packages (pseudo codes) for the different front-end functions

a. Package Listing agent

CREATE OR REPLACE Package Listing_Agent_Queries IS

Procedure Insert_Listing_Agent (Variables);

Procedure Update_Listing_Agent (Variables);

Procedure View_Offer_Listing_Agent (Variables);

Procedure Offer_Confirmed_Listing_Agent (Variables);

Procedure View_Report_Listing_Agent (Variables);

Procedure Client_List (Variables);

Procedure View_Offer_Client (Variables);

Procedure Add_Client_Details (Variables);

END;

CREATE OR REPLACE PACKAGE BODY Listing_Agent_Queries IS

/* 1.Register new Listing Agent*/

/*

· Check if the tuple exists

· Insert the tuple into Listing Agent

*/

( Declare variables;)

Procedure Insert_Listing_Agent

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. Check if the tuple with the same value of “SSN” exists;
2. Insert otherwise

INSERT INTO Listing_Agent (listing_agent_ssn, last_name, middle_name, first_name, residence_street, residence_city, residence_state, residence_zipcode, residence_country, residence_phone_area_code, residence_phone_number, office_street, office_city, office_state, office_zipcode, office_country, office_phone_area_code, office_phone_number, other_business, email)

VALUES (variables in the procedure whose value comes from the front end);

END;

1.1.4. Updating the tuples in Listing_Agent

/* Simply update the tuple with the new values from the front end if it exists */

Procedure: Update_Listing_Agent

/*

· Check if the tuple exists..ERROR else

o Update the tuple if it exists

*/

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. Check if the tuple with the same value of “SSN” exists;
2. UPDATE Listing_Agent

SET Listing_Agent_residence_street = (variable containing the value of the residence street)

…….(similar for other variables)

WHERE Listing_agent_ssn=(variable containing the Listing_Agent_SSN);

END;

2.1.1 View Offer

/*View the offers made on the selected house*/

Procedure: View_Offer_Listing_Agent

/* Check for each Homeid in Homeoffer to get all the Offers*/

(

Declare Variables /* should be populated from the front end*/;

) IS

BEGIN

1. Check if the tuple with the same value of “Home_ID” exists;

2. Select *

From Home_Offer

Where Home_Id = (Variable containing the homeid)

Group by( variable entered by user)/*Variable telling whether to sort by Price or by date*/;

END;

2.1.3 Offer Confirmed

/*

Insert the tuple into Decision table

*/

Procedure: Offer_Confirmed_Listing_Agent

/* Confirming the offer by inserting the tuple in to the Decision table*/

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. INSERT INTO Decision (listing_agent_ssn, home_owner_ssn, offer_id, selling_price, who_accepted_ssn, date_of_acceptance)

VALUES ( As entered by the user and stored in the variables);

END;

2.2 View Report for Listing Agent

/*

Get the Listing Agents’ Login Id

Find the tuples containinig the sam eId and display

*/

Procedure: View_Report_Listing_Agent

/* Check if the LoginID exists or no in DECISION

If yes then search for the tuple in DECISION and return the contents*/

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. Check if the tuple with the same value of “LoginID” exists;

2. Select *

From Decision

Where Listing_Agent_SSN = (Variable containing the Loginid);

END;

2.3 Client List for the Listing Agent

/*

Find all the clients for a particular Listing Agent

*/

Procedure: Client_List

/*

· Get the LoginId from front end

· Search for the tuple in CONTRACT_LA and get Home-ownerSSn which is used to search in Home_Owner

*/

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. Select name,Residence

From HOME_OWNER

Where home_owner_ssn In

(Select Home_Owner_SSN

From CONTRACT_LA

Where Listing_Agent_SSN =( Variable containing the LoginId)

) ;

2.4 View Offer for Client

/* To give all the offers for the given client for the particular Listing Agent*/

Procedure: View_Offer_Client

/*

• Find the tuple having given Home_Owner_SSN and Listing_Agent_SSN in CONTRACT_LA
• Use the address from that tuple to get the address which is used to search the HOME_OFFER table to get all the information in that tuple

*/

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. Declare Variables /* should be populated from the front end*/;

2. Select *

From HOME_OFFER

Where Home_Id in

(Select Home_Id

From CONTRACT_LA

Where Home_Owner_SSN = (Variable entered by the user)

AND Listing_Agent_SSN =”LoginId”

);

END;

2.5 Adding New Clients

/*

This will simply add the Client Details

*/

Procedure: Add_Client_Details

/*

· Check if the tuple exists

· Insert The tuple in House_Put_For_Sale

*/

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. Check if the tuple with the same value of “SSN” exists;

2. Insert otherwise

INSERT INTO Put_For_Sale (listing_agent_ssn, residence_street, residence_city, residence_state, residence_zipcode, residence_country, asking_price, listing_date)

VALUES (variables in the procedure whose value comes from the front end);

END;

END;

c. HomeOwner Queries

CREATE OR REPLACE Package Home_Owner_Queries IS

Procedure Insert_Home_Owner (Variables);

Procedure Update_Home_Owner (Variables);

Procedure Listing_Agent_List (Variables);

Procedure Home_Sales_Information (Variables);

Procedure View_Home_Owner (Variables);

Procedure Get_Estimates (Variables);

END;

CREATE OR REPLACE PACKAGE BODY Home_Owner_Queries IS

/*

1. Register new Home Owner

· Check if the tuple exists

· Insert the tuple into Home Owner

*/

Procedure Insert_Home_Owner

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. Check if the tuple with the same value of “SSN” exists;

2. Insert otherwise

INSERT INTO Home_Owner (home_owner_ssn, last_name, middle_name, first_name, residence_street, residence_city, residence_state, residence_zipcode, residence_country, residence_phone_area_code, residence_phone_number, office_street, office_city, office_state, office_zipcode, office_country, office_phone_area_code, office_phone_number, other_business, email, secondary_owner_ssn)

VALUES (variables in the procedure whose value comes from the front end);

END;

/*1.Updating the tuples in Home_Owner

Simply update the tuple with the new values from the front end if it exists */

Procedure Update_Home_Owner

/*

· Check if the tuple exists..ERROR else

o Update the tuple if it exists

*/

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. Check if the tuple with the same value of “SSN” exists;

2. UPDATE Home_Owner

SET Home_Owner_residence_street = (variable containing the value of the residence street)

…….(similar for other variables)

WHERE Home_Owner_ssn=(variable containing the Home_Owner_SSN);

END;

/*1.1.1. Listing agents list sorted by some parameter

Give the list of the Listing Agenst based on some parameters

*/

Procedure Listing_Agent_List

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. Select *

From Listing_Agent

Group by (variable having some parameter);

END;

/*2.1 Home Sales Information

Give the information about the selling price and the location of all houses

*/

Procedure Home_Sales_Information

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. Select Selling Price, Address

From DECISION, HOMEOWNER

Where Home_Owner_SSN in

(Select Home_Owner_SSN From HOMEOWNER);

END;

/*2.2 View Offer for Home_Owner

To give all the offers for the given client for the particular Listing Agent*/

Procedure View_Home_Owner

/*

• Find the tuple having given Home_Owner_SSN and Listing_Agent_SSN in CONTRACT_LA
• Use the address from that tuple to get the address which is used to search the HOME_OFFER table to get all the information in that tuple

*/

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. Select *

From HOME_OFFER

Where address in

(Select House_Address

From CONTRACT_LA

Where Home_Owner_SSN = (LoginId)

AND Listing_Agent_SSN =(Variable entered by the user)

);

END;

/*1.1.3 Getting Estimate of House prices

Getting estimate of a house when the user gives the parameters*/

Procedure Get_Estimates

/*

• Query the table to retrieve the data for a zipcode/locality
• Multiply the values obtained by the corresponding inputs.
• Produce the outputs

*/

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. zipcode /locality obtained from front end;
2. get data;
3. temp_details=details%rowtype;;
4. SELECT * into tmp_details

FROM details WHERE zipcode=(variable containing the zipcode) and locality=(variable containing the locality);

5. Calculate_the_price(tmp_details);
6. DMBS_output (result);

END;

END;

d. Package Homebuyer

We have decided to include all the relevant functions of the homebuyer in a package called Home_buyer. The different functions are included as procedures.

CREATE OR REPLACE PACKAGE Home_buyer IS

2. Register new Home buyer

/*

· Insert into Home_Buyer the tuple

· Check if the tuple exists

*/

PROCEDURE Insert_home_buyer

(

declare variables;

)

END;

CREATE OR REPLACE PACKAGE BODY Insert_home_buyer IS

PROCEDURE Insert_home_buyer

declare variables once again;

IS

BEGIN

3. Check if the tuple with the same value of “SSN” exists
4. Insert otherwise

INSERT INTO Home_buyer (home_buyer_ssn, last_name, middle_name, first_name, residence_street, residence_city, residence_state, residence_zipcode, residence_country, residence_phone_area_code, residence_phone_number, office_street, office_city, office_state, office_zipcode, office_country, office_phone_area_code, office_phone_number, other_business, email)

VALUES (variables in the package whose value comes from the front end);

END;

END;

2.1.4. Updating the tuples in Home_buyer

/* Simply update the tuple with the new values from the front end if it exists */

/*

· Check if the tuple exists..ERROR otherwise

· Update the tuple if it exists

*/

PROCEDURE Update_home_buyer

(

declare variables;

)

END;

CREATE OR REPLACE PACKAGE BODY Update_home_buyer IS

PROCEDURE Update_home_buyer

declare variables once again;

IS

BEGIN

3. Check if the tuple with the same value of “SSN” exists
4. UPDATE Home_buyer

SET home_buyer_residence_street = (variable containing the value of the residence street)

…….(similar for other variables)

WHERE home_buyer_ssn=(variable containing the home_buyer SSN)

END;

END;

1.1.2.1. Inserting the requirements of a Home_buyer

/*

• Takes in the requirements and stores in the database table home_buyer_requirements

*/

PROCEDURE Insert_home_buyer_requirements

(

declare variables;

)

END;

CREATE OR REPLACE PACKAGE BODY Insert_home_buyer_requirements IS

PROCEDURE Insert_home_buyer_requirements

declare variables once again;

IS

BEGIN

1. Declare variables

2. Insert the values /* each requirement has got an unique requirement ID*/

3. INSERT INTO Home_buyer_requirement (home_buyer_ssn, requirement_id, house_type, bedroom, bathroom, garage_size, style, area, fireplaces, basement, basement_details, price_limit)

VALUES (variables in the package)

END;

END;

1.1.2.2. Updating the requirements of a Home buyer

/*

· Check if the tuple exists..ERROR otherwise

· Update the tuple if it exists

*/

PROCEDURE Update_home_buyer_requirements

(

declare variables;

)

END;

CREATE OR REPLACE PACKAGE BODY Update_home_buyer_requirements IS

PROCEDURE Update_home_buyer_requirements

declare variables once again;

IS

BEGIN

1. Check if the tuple with the same value of “SSN” exists
2. UPDATE Home_buyer_requirement

SET home_buyer_house_type = (variable containing the value of the house_type)

…….(similar for other variables)

WHERE home_buyer_ssn=(variable containing the home_buyer SSN)

AND requirement_id=(variable containing the requirement_id)

END;

END;

1.1.3. Enquire House prices

/*

• Query the table to retrieve the data for a zipcode/locality
• Multiply the values obtained by the corresponding inputs.
• Produce the outputs

*/

PROCEDURE enquire_house_prices

(

declare variables;

)

END;

CREATE OR REPLACE PACKAGE BODY enquire_house_prices IS

PROCEDURE enquire_house_prices

declare variables once again;

IS

BEGIN

7. zipcode/locality obtained from front end
8. get data
9. temp_details=details%rowtype;
10. SELECT * into tmp_details

FROM details WHERE zipcode=(variable containing the zipcode) and locality=(variable containing the locality)

11. Calculate_the_price(tmp_details)
12. DMBS_output (result)

END;

END;

1.1.5.2. Enquire details of the chosen Selling Agent

/* gives you details about your selected Selling Agent */

PROCEDURE enquire_details_SA

(

declare variables;

)

END;

CREATE OR REPLACE PACKAGE BODY enquire_details_SA IS

PROCEDURE enquire_details_SA

declare variables once again;

IS

BEGIN

1. SELECT Selling_agent_ssn FROM contract_SA

WHERE home_buer_ssn=(variable in the package) home_buyer_passwd=(variable containing the passwd)

2. SELECT * INTO temp_data_SA

FROM selling_agent WHERE selling_agent_ssn=Selling_agent_ssn

3. DBMS output (result)

END;

END;

1.1.5.1. Enquire details about the transaction if a new house has been bought on your behalf

PROCEDURE enquire_details_transaction

(

declare variables;

)

END;

CREATE OR REPLACE PACKAGE BODY enquire_details_transaction IS

PROCEDURE enquire_details_transaction

declare variables once again;

IS

BEGIN

1. SELECT Offer_id from buys WHERE home_buyer_ssn=(variable containing the value from front end)

2. SELECT * INTO temp_details FROM decision WHERE offer_id=Offer_id

3. if (temp_details==null) DMBS output (“House not yet sold”)

else DBMS output(temp_details)

END;

END;

1.1.1.1. Produce a sorted list of selling agents based on the maximum number of houses sold

PROCEDURE enquire_details_transaction

(

declare variables;

)

END;

CREATE OR REPLACE PACKAGE BODY enquire_details_transaction IS

PROCEDURE enquire_details_transaction

declare variables once again;

IS

BEGIN

1. SELECT name FROM selling_agent WHERE selling_agent_ssn in

(SELECT who_accepted_ssn, count(who_accepted_ssn)

FROM decision GROUP BY who_accepted_ssn ASC)

2. DBMS output (result)

END;

END;

1.1.1.2. Produce a sorted list of selling agents based on the min. average prices of houses sold.

PROCEDURE sorted_list_max_avg_price_houses

(

declare variables;

)

END;

CREATE OR REPLACE PACKAGE BODY sorted_list_max_avg_price_houses IS

PROCEDURE sorted_list_max_avg_price_houses

declare variables once again;

IS

BEGIN

1. Declare variables

2. SELECT selling_agent_ssn, AVG(selling_price)

FROM make_offer, decision

WHERE make_offer.offer_id=decision.offer_id

GROUP BY selling_agent_ssn ASC

END;

END;

1.1.1. Selection of a selling agent by a home buyer

PROCEDURE select_selling_agent

(

declare variables;

)

END;

CREATE OR REPLACE PACKAGE BODY select_selling_agent IS

PROCEDURE select_selling_agent

declare variables once again;

IS

BEGIN

1. Declare variables

2. Check if the ssn supplied from the front end is a valid ssn

3. INSERT INTO contract_sa

VALUES (variables from the front end)

END;

END;

END;

d. Package Selling Agent

1) Insertion of a new Selling Agent:

3. Register new Selling Agent

/*

· Insert into Selling Agent the tuple

· Check if the tuple exists

*/

Procedure: Insert_selling_agent

5. Declare Variables /* should be populated from the front end*/
6. Check if the tuple with the same value of “SSN” exists
7. Insert otherwise

Insert into SELLING_AGENT(Selling_Agent_SSN,Name,Password,Office Address,Office Phone,Residence Address,Residence Phone,E-mail,Other Profession)

Values (variables in the Procedure whose value comes from the front end);

2.1) Update details of a Selling Agent:

/* Simply update the tuple with the new values from the front end if it exists */

Procedure: Update_selling_agent

/*

· Check if the tuple exists..ERROR otherwise

· Update the tuple if it exists

*/

5. Declare Variables /* should be populated from the front end*/
6. Check if the tuple with the same value of “SSN” exists
7. UPDATE Selling_Agent

If SSN is changed (say at the backend by running a query),display error message:

SSN is the primary key. Can't be changed.

Else

Update SELLING_AGENT

Set (Name= variable containing name,

Password = variable containing password,

Office Address= ….,

Office Phone = …..,

Residence Address = …..,

Residence Phone = ….,

E-mail = ….,

Other Profession = …..)

Where Selling_Agent_SSN = “Login ID”

End if.

2.2) Make Offer:

/* Insert a tuple in the Make_Offer and Home Offer table */

Procedure: Insert_Make_Offer

/*

· Insert the tuple in the Make Offer table.

· Insert the tuple in the Home Offer table.

*/

1. Declare Variables /* should be populated from the front end*/

Select max(Offer_ID) from Make_Offer (into some variable - say max_offer_id)

Insert into Make_Offer(Offer_ID,Selling_Agent_SSN,Offered_Price,Date_Of_Offer,Time)

Values(max_offer_id,Login_ID,Offered_Price,Current Date,Current Time)

Insert into Home_Offer(max_offer_id,Home_Id, Offered_Price,Date_Of_Offer,Time)

Values(max_offer_id,Home_Id,Offered_Price,Current Date,Current Time)

2.2.1 Offer made

/* Select a tuple from Home_Offer showing the details of the offer */

Procedure: Select_Home_Offer

/*

· Select a tuple from the Home Offer table.

· Select address from the Home Table.

*/

1. Home ID is stored from 2.2.

Select Address into home_address (home_address is a temporary variable)

From Home

where (Home_ID = given Home_ID)

Select *

From Home_Offer

where Offer_ID = max(Offer_ID)

2.3.a) All clients under Selling Agent

/* Select a tuple from Contract_SA showing all clients under a SA. */

Procedure: Select_Home_Buyers

/*

· Select a tuple from the Contract_SA table.

*/

1. Declare Variables /* Login ID comes from the front end.*/

Select home_buyer_name

From Home_buyer

Where home_buyer_ssn In (Select home_buyer_ssn

From Contract_SA

Where Selling_Agent_SSN = "Login_ID")

2.3.b) All houses sold on/before some date

/* Select a tuple from decision table showing the houses sold by the SA. */

Procedure: Select_Houses_Sold.

/*

· Select a tuple from the Decision table.

*/

1. Declare Variables /* Login ID and other variables come from the front end*/

Select Address,Home_ID,Offer_ID

From Home,Home_Offer,Decision

Where Home.Home_Id = Home_Offer.Home_ID

And

Home_Offer.Offer_ID = Decision.Offer_Id

And

Decision. Date_of_acceptance<=”given_date”

And

Decision.Selling_Agent_SSN = "Login_ID"

2.3.c) Houses sold to a particular buyer

/* Select a tuple from decision table showing the houses sold by the SA. */

Procedure: Select_Houses_Sold.

/*

· Select a tuple from the Decision table which is there a corresponding SSN in BUYS table.

*/

Select Offer_ID,Address

From Decision,Buys

Where Decision.Offer_ID = Buys.Offer_ID

And

Home_Buyer_SSN = “Given SSN”

2.3.d) Requirement of a particular client

/* Select all tuples from home_buyer_requirement table showing all the requirementa of the home buyer. */

Procedure: Select_requirement.

/*

· Select all tuple from the home_buyer_requirement table,which satisfy the condition..

*/

1. Declare variable /*The home buyer SSN and selling agent SSN comes from the front end */

Select Home_Buyer_SSN

From Home_Buyer

Where Home_Buyer_SSN = “Given SSN”

If tuple exists

Select *

From Home_buyer_requirement

where Selling_Agent_SSN = “Login_ID”

And

home_buyer_SSN = "given SSN"

Else Error message:

The Client does not exist.

2.3.e) Latest requirement of a particular client

/* Select a tuple from home_buyer_requirement table showing the most recent requirement of the home buyer. */

Procedure: Select_latest_requirement.

/*

· Select a tuple from the home_buyer_requirement table.

*/

1. Declare variable /*The home buyer SSN and selling agent SSN comes from the front end */

Select Home_Buyer_SSN

From Home_Buyer

Where Home_Buyer_SSN = “Given SSN”

If tuple exists

Select *

From Home_buyer_requirement

where Selling_Agent_SSN = “Login_ID”

And

home_buyer_SSN = "given SSN"

And

Requirement_ID = max(Requirement_ID)

Else Error message:

The Client does not exist.

2.3.f)Houses with some particular requirement.

/* Select a tuple from Home, listing houses where the requirements match with that of the client. */

Procedure: Select_homes.

/*

· Select a tuple from the home table.

*/

1. Declare variables /*The requirements come from the front end */

Select Home_ID,Address

From Home,Put_For_Sale

Where Bedroom = "some value"

Style = "some value"

Construction_date <,= "some_date"

Basement = "some value"

Fireplace = "some value"

Area = "some value"

Bathroom = "some value"

garage = "some value"

House_Type = "some value"

and Home.Home_ID = Put_For_Sale.Home_ID

2.3.g) Houses with asking price in a particular range

/* Select a tuple from Put_for_sale , giving houses within a particular range.

Procedure: Select_homes_asking_price.

/*

· Select a tuple from the put_for_sale table.

*/

1. Declare variables /*Asking price range comes from front end. */

Select Address , Asking_Price

From Home ,Put_For_Sale

Where Home.Home_Id = Put_For_Sale.Home_ID

And

Put_For_Sale.asking_price between lower_amount and highest_amount

2.3.h) Houses with offered price in a particular range

/* Select a tuple from Make_offer , giving houses within a particular range.

Procedure: Select_homes_offered_price.

/*

· Select a tuple from the make_offer table.

*/

1. Declare variables /*Offered price range comes from front end. */

Select Address , Offered_Price,Offer_ID

From Home ,Home_Offer,Make_Offer

Where Home.Home_Id = Home_Offer.Home_ID

And

Make_Offer.Offer_ID = Home_Offer.Offer_Id

And

Make_Offer.offered_price between lower_amount and highest_amount

2.3.i) Commission of other selling agents

/* Select a tuple from Contract_SA , giving commissions of selling agents.

Procedure: Select_commission.

/*

· Select all tuples from the contract_SA table.

*/

1. Declare variables.

Select Commission ,Selling_Agent_SSN,Selling_Agent_Name

From Contract_SA,Selling_Agent

Where Selling_Agent.Selling_Agent_SSN = Contract_SA.Selling_Agent_SSN.

2.3.j) Date of contract with a particular client

/* Select a tuple from Contract_SA , giving date_of_contract of selling agent.

Procedure: Select_date_of_contract.

/*

· Select the date of contract for the SA.

*/

1. Declare variables.(Login_Id and Home_Buyer_SSN comes from front end.)

Select date_of_contract

From Contract_SA

Where Selling_Agent_SSN = “Login_ID”

And

Home_Buyer_SSN = “given SSN”

2.3.k) Enquire prices of houses in a particular locality

/* Select a tuple from Details , where selling agent gives the details of the house.*/

Procedure: Enquire_price.

/*

· Calculate the proce of a house with some requirements.

*/

1. Declare variables.(No. of bedrooms,Garage,Lawn etc come from front end.)

Select bedroom_price,bathroom_price,furnishing_price,ac_price,carpet_price,lawn_price,garage_price,House_type_price

From Details

Where Locality = “given locality”

Calculate the total price of the house by multiplying all the house parameters with the respective prices + base price .

Display the output to the selling agent.

e. General Queries

CREATE OR REPLACE Package General_Queries IS

Procedure Houses_Listing_Agent (Variables);

Procedure Homes_List_Date_Of_Contract (Variables);

Procedure Listing_Agent_default_Commission (Variables);

Procedure Listing_Agent_Home_Place (Variables);

Procedure Commissions_Home_Owner (Variables);

Procedure Listing_Agent_Locality_Details (Variables);

Procedure Listing_Agent_Commission_Details (Variables);

END;

CREATE OR REPLACE PACKAGE BODY General_Queries IS

/*1.All the houses under the Listing Agent

List all the houses under a given Listing Agent

*/

Procedure Houses_Listing_Agent

/*

Get the HomeId from the CONTRACT_LA and use it to find the details of the houses from HOME

*/

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. Select Address

From HOME

Where Home_Id In

(Select Home_Id

From CONTRACT_LA

Where Listing_Agent_SSN =”Login ID”

);

END;

/*2.Homes Listed at/before date of Contract

Gives the list of all the houses listed at the given date of contract

*/

Procedure Homes_List_Date_Of_Contract

/*

• Get the date of contract
• Use it to find all the tuples that satisfy in theCONTRACT_LA with < or = relation
• Find Home_Owner_SSN form that relation and use it to search the table HOME_OWNER which is used to get the addresses of the houses.

*/

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. Get the date of contract form the user;

2. Select Address

From Home_Owner

Where Home_Owner_SSN in

(Select Home_Owner_SSN

From CONTRACT_LA

Where Date_Of_Contract= (Variable containing the user input)

);

END;

/*3.LA with the default commission

Finds all the LA with the default commission say 5*/

Procedure Listing_Agent_default_Commission

/*

Set Default commission

Find Listing_Agent_SSN from the CONTRACT_LA and use it as a parameter to find the name and the addresses from the LISTING_AGENT

*/

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1.Select name, address

From LISTING_AGENT

Where Listing_Agent_SSN in

(Select LISTING_AGENT

From CONTRACT_LA

Where Commission=”5”

);

END;

/*4.All the listing Agents with the homes at some place

Search for all the Listing agents having the houses listed at the place enetered by the user*/

Procedure Listing_Agent_Home_Place

/*

• Get the locality/place entered by the user
• Use the variable to find the office of the Listing agent having the houses at that location

*/

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. Select name, office, email

From LISTING_AGENT

Where Listing_Agent_SSN in

( Select Listing_Agent_SSN

From PUT_FOR_SALE

Where address in

(Select Address

From HOME

Where Locality =(user input stored in a variable)

)

);

END;

/*5.Commissions for the given Home Owner

Find out the all the commissions for the home owners transactions*/

Procedure Commissions_Home_Owner

/*

Search for the name and commission form the tables HOME_OWNER AND CONTRACT_LA such that the Home_Owner_SSN’s are same

*/

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. Select name, commission

From HOME_OWNER, CONTRACT_LA

Where

HOME_OWNER.HOME_OWNER_SSN= CONTRACT_LA.HOME_OWNER_SSN;

END;

/*6.Listing Agents with the Houses on sale at particular area and particular details

Give the addresses and details of Listing Agents having the houses listed form particular locality and having particular details

*/

Procedure Listing_Agent_Locality_Details

/*

• Get the Locality form the user
• Get the Details for the houses from the user
• Search for the name, office, email form the LISTING_AGENT table such that the Listing Agent ‘s SSN is in PUT_FOR_SALE Where you use address from the HOME table having locality and other details

*/

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. Get the Locality form the User and store it in variable;
2. Get the various requirements from the user and store in the variable;
3. Select name, office, email

From LISTING_AGENT

Where Listing_Agent_SSN in

(Select Listing_Agent_SSN

From PUT_FOR_SALE

Where address in

(Select address

From HOME

Where Locality =(variable entered by the user)

AND (other variables entered by the user)

)

);

END;

/*7.All Home Owners having Listing Agents with the commission range less than input percentage and having the homes listed with the required details as fed by the user

• Get the commisision requirement and store in variable
• Get the details and store in other variables
• Search the query

*/

Procedure Listing_Agent_Commission_Details

(

Declare Variables /* should be populated from the front end*/

) IS

BEGIN

1. Get the minimum commission and store in variable;
2. Get the details and store in the variable;
3. Select name,commission

From Home_Owner, CONTRACT_LA

Where

(HOME_OWNER.HOME_OWNER_SSN= CONTRACT_LA.HOME_OWNER_SSN)

AND

(Select Address

From HOME

Where

(variables as required by the user);

END;

END;

5. Contribution log

Prapti: Selling Agent GUI Design, SQL Queries and constraints on the schemas

Shamit: Listing Agent and Home owner GUI Design, SQL queries

Dipanjan: Home Buyer GUI Design, SQL statements and Create table statements.

Documentation: Prapti, Dipanjan, Shamit.