Question

Really need help on this, apperciate all answers and feedback =D.

You are the Senior Consultant at Abacus Consulting, tasked with the database project for Amadeus Real Estate client. The company employs real estate agents who work with customers to buy and sell properties (both residential and commercial). As part of your lead role, you are initially responsible for meeting with the client team and gathering requirements. You are then scheduled to design the data model and implement a working prototype to demonstrate the validity of the data model. Your specific tasks are broken down as below

Phase 1 – Requirements Gathering

As part of the requirements phase, develop a minimum of 10 business rules that apply to the client’s business. Each business rule must be related to a data model feature and must be clearly demonstrated in the data model (developed in phase-2).

Phase 2 – Database Design

Develop a database model (ERD) based on the business rules developed in Phase-1. Use the Crow’s Foot notation, indicate all attributes, primary/foreign key relationships, cardinalities and relationship strengths.

[Deliverable: ERD]

Phase 3 – Implementation (50 points)

Implement the table structure based on the ERD developed in phase-2. Assume property details (type, area, list price, sale price …etc). Assume 3% commission on all transactions.

Write the SQL code to create the needed tables. Ensure all primary keys, foreign keys and constraints are defined.

After implementing the table structures, insert dummy data for 25 transactions, placed between January-1-2015 and June-30-2015.

Phase 4 – Validation

To validate the data model you have developed in phase-3, you are asked to write some queries and verify the results with expected results.

Write the SQL statements that provide the following information:

Number of sales for the period March-1-2015 to March-31-2015

Agent who sold the most number of properties between Feb-1-2015 to June-30-2015.

Agent who sold the most $$ value of properties between Jan-1-2015 to Mar-31-2015.

Most expensive property sold between Jan-1-2015 to June-30-2015.

Answer 1

CREATE TABLE  "PERSON" 
   (    "PERSONID" VARCHAR2(20) NOT NULL ENABLE, 
        "LASTNAME" VARCHAR2(50) NOT NULL ENABLE, 
        "MIDDLENAME" VARCHAR2(50) NOT NULL ENABLE, 
        "INITIALS" VARCHAR2(5) NOT NULL ENABLE, 
        "TITLE" VARCHAR2(3) NOT NULL ENABLE, 
        "GENDER" CHAR(1) NOT NULL ENABLE, 
        "BIRTHDATE" DATE NOT NULL ENABLE, 
         CONSTRAINT "PERSON_PK" PRIMARY KEY ("PERSONID")
  USING INDEX  ENABLE
   );

CREATE TABLE  "OWNER" 
   (    "PERSONID" VARCHAR2(20) NOT NULL ENABLE, 
         CONSTRAINT "OWNER_PK" PRIMARY KEY ("PERSONID")
  USING INDEX  ENABLE
   );
ALTER TABLE  "OWNER" ADD CONSTRAINT "OWNER_FK" FOREIGN KEY ("PERSONID")
          REFERENCES  "PERSON" ("PERSONID") ENABLE;

CREATE TABLE  "CLIENT" 
   (    "PERSONID" VARCHAR2(20) NOT NULL ENABLE, 
        "MAXPRICE" NUMBER(10,2), 
         CONSTRAINT "CLIENT_PK" PRIMARY KEY ("PERSONID")
  USING INDEX  ENABLE
   );
ALTER TABLE  "CLIENT" ADD CONSTRAINT "CLIENT_FK" FOREIGN KEY ("PERSONID")
          REFERENCES  "PERSON" ("PERSONID") ENABLE;

CREATE TABLE  "AGENT" 
   (    "PERSONID" VARCHAR2(20) NOT NULL ENABLE, 
        "OFFICE" VARCHAR2(100), 
         CONSTRAINT "AGENT_PK" PRIMARY KEY ("PERSONID")
  USING INDEX  ENABLE
   );

ALTER TABLE  "AGENT" ADD CONSTRAINT "AGENT_FK" FOREIGN KEY ("PERSONID")
          REFERENCES  "PERSON" ("PERSONID") ENABLE;

CREATE TABLE  "CLIENT" 
   (    "PERSONID" VARCHAR2(20) NOT NULL ENABLE, 
        "MAXPRICE" NUMBER(10,2), 
         CONSTRAINT "CLIENT_PK" PRIMARY KEY ("PERSONID")
  USING INDEX  ENABLE
   );
ALTER TABLE  "CLIENT" ADD CONSTRAINT "CLIENT_FK" FOREIGN KEY ("PERSONID")
          REFERENCES  "PERSON" ("PERSONID") ENABLE;

Number of sales for the period March-1-2015 to March-31-2015

select count(*) from sales where saledate between
to_Date('Mar-01-2015','Mon-dd-yyyy') and to_date('Mar-31-2015','Mon-dd-yyyy') ;

Agent who sold the most number of properties between Feb-1-2015 to June-30-2015.

select * from (
select * from (
(select agentpersonId , count(*) as numSold from sales where saledate between to_Date('Feb-01-2015','Mon-dd-yyyy') and to_date('Jun-30-2015','Mon-dd-yyyy')
group by agentpersonId)
) order by numSold desc )
where rownum = 1;

Agent who sold the most $$ value of properties between Jan-1-2015 to Mar-31-2015.

select * from (
select * from (
(select agentpersonId , sum(price) as dollarAmount from sales where saledate between to_Date('Jan-01-2015','Mon-dd-yyyy') and to_date('Mar-31-2015','Mon-dd-yyyy')
group by agentpersonId)
) order by dollarAmount desc )
where rownum = 1;

Most expensive property sold between Jan-1-2015 to June-30-2015.

select propertyId ,price from
(
(select propertyId,price from sale
where
saledate between to_Date('Jan-01-2015','Mon-dd-yyyy') and to_date('Jun-30-2015','Mon-dd-yyyy')
order by price desc )
) where rownum =1;