Database Project (200 points)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 (50 points)
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).
[Deliverable: Word doc containing business rules]
example.;
Business rules
• Brief, precise, and unambiguous descriptions of a policies, procedures, or principles within a specific organization
• Apply to any organization that stores and uses data to generate information
• Description of operations that help to create and enforce actions within that organization’s environment
• Must be rendered in writing
• Must be kept up to date
• Sometimes are external to the organization
• Must be easy to understand and widely disseminated
• Describe characteristics of the data as viewed by the company
• Generally, nouns translate into entities
• Verbs translate into relationships among entities
• Relationships are bi-directional
Business rules example
• “If the contract value is greater than $1M, the bid must be approved by the Manager before being submitted.”
• “If member’s age is greater than 55 years, their co-pay is half the standard rate.”
• An invoice contains one or more invoice lines.
• Each invoice line is associated with a single invoice.
• A store employs many employees.
• Each employee is employed by only one store.
• A college has many departments.
• Each department belongs to a single college.
• A driver may be assigned to drive many different vehicles.
• Each vehicle can be driven by many drivers.
• A client may sign many contracts.
• Each contract is signed by only one client.
• A sales representative may write many contracts.
• Each contract is written by one sales representative.
Phase 2 – Database Design (50 points)
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.
1. Write the SQL code to create the needed tables. Ensure all primary keys, foreign keys and constraints are defined.
2. After implementing the table structures, insert dummy data for 25 transactions, placed between January-1-2015 and June-30-2015.
[Deliverable: Text file containing SQL statements]
Phase 4 – Validation (50 points)
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.
[Deliverable: Text file containing SQL statements]

Submission Instructions
All deliverables must be combined and submitted as a single zip file. The SQL file must be in plain text format (filename with .sql extension).