Create a file called realestate.sql using the following schema:

CREATE TABLE Agent( agentid integer, name VARCHAR(30), phone VARCHAR(30), email VARCHAR(30), commissionrate float, mentorid integer );

CREATE TABLE Buyer( buyerid integer, name VARCHAR(30), phone VARCHAR(10), email VARCHAR(30), employer VARCHAR(30), mortagecompany VARCHAR(30) );

CREATE TABLE Seller( sellerid integer, name VARCHAR(30), phone VARCHAR(10), email VARCHAR(30) );

CREATE TABLE Property( propertyid integer, county VARCHAR(30), town VARCHAR(30), lotnumber VARCHAR(30), price double(10,2), agentid integer, sellerid integer );

CREATE TABLE Bid( propertyid integer, buyerid integer, amount double(10,2), biddate date, status VARCHAR(30) ); Part 2 Using the file realestate.sql created in Part 1, make modifications to the file in the following ways: 1. Change the Create Table commands so that primary keys are added to all tables. The primary key for agent is agentid, the primary key for buyer is buyerid, the primary key for seller is sellerid, and the primary key for property is propertyid, and the primary key for bid is propertyid and buyerid. 2. Change the Create Table commands so that foreign key constraints are added to the tables. In table agent, mentorid references another agentid. 3. Change the Create Table commands so that name of an agent, the name of a buyer, and the name of a seller cannot be null. 4. Change the Create Table command so that deleting an agent record will automatically set a default to another agent in table property. 5. Create an realestatedata.sql file and write INSERT statements to add 4 agents, 4 buyers, 4 sellers, 8 properties, and 8 bids. 6. Write 2 UPDATE statements to update the amount in table bid. 7. Write 2 DELETE statements to remove an agent. Run your new realestate.sql in MySQL to verify correctness.

Part 3 Use the revised realestate.sql file to create queries for the following questions. Create an realestateQueries.sql file that contains all the queries.

1. Output the name, phone, and email of all buyers and sellers.

2. Output the buyer id and name of all buyers that have never made a bid.

3. Output the county, town, and lot number of the property with the highest bid.

4. Output the agent name, seller name, county, town, and price of all properties.

5. Output the name and email of agents and their mentors.

6. Output the average commission rate for all agents.

7. Output the buyer name, phone, email, property id, amount, and status for each bid. If a buyer did not make a bid, we should still see their information.

8. Output the names of all agents and the number of properties they have.

9. Output the town, lot number and price for each property in the county of bany.

10.Output the names of all agents and the number of properties they have, but only include agents that have more than 3 properties.