Complete reading and reviewing all the learning resources specified for this module.

Meet with your team to discuss individual assignments and set milestones for the completion of each task.

Review the scenario and the business rules identified as part of the business operations. You will be designing a database schema using the information below to determine the data elements, complete the normalization process, create an ERD, and build a schema in an Oracle database using SQL$.$ You will have to apply your critical thinking skills to complete this project.

Scenario

After building a successful Real Estate Investment business your parents decide it's time to retire and have asked you to take over the family business. You weren$$t sure if you were ready to leave your lucrative career as a Database Administrator but after careful deliberation, you$$ve agreed to take over. You realize that the business was not functioning at its most optimal level, as several staff members are still using the primitive file and cabinet method to maintain records. After a thorough evaluation, you've decided to apply the knowledge you've acquired as a DBA to improve the day$-$to$-$day operations of your family$-$owned business. In doing so$,$ you recruit a couple of your IT friends to assist with designing a new application.

Business Requirements

Your new application will support all business operations; this includes maintaining records of buyers, properties acquired, contractors, sales agents, agreements, and employees. Over the years there have been thousands of properties acquired and sold. Staff members have worked with a number of contractors consisting of Interior Designers and General Contractors. Your business also employs five employees $($an accountant, a lawyer, a secretary$,$ and two maintenance workers$).$ In addition, over the years your parents have worked with several independent Real Estate Sales Agents.

Your database should contain details from previous and potential buyers including account number, name, address, contact info, occupation, salary, and credit score.

You also need to maintain Employee details employer code, employer name, employer address, employer phone number, etc...

It should also contain data for contractors such as id$,$ name, address, and phone number. In addition, you also need to include the sales agent's id$,$ name, address, and phone number.

You also should maintain data for contract agreements between various contractors and your company. These agreements should include an agreement number, contractor id$,$ contractor name, contractor address, contractor phone number, and a description of the agreement.

A contractor can enter into several agreements, but each agreement can only be associated with one contractor.

Lastly, the database schema should contain all the properties acquired and sold, each property should have a parcel number, address, and specification such as the number of rooms, square feet, purchase date, purchase amount, market value, and architecture style description. The architectural styles include Cope Code, Colonial Contemporary, Ranch and etc... For the architecture style description, you need to store information such as the style and description of each style to provide additional characteristics for each property.

The property should also be associated with details for both the buyer and sales agent $($id$,$ name, address, phone$).$

You've also noted that a buyer can purchase one or more properties from you and the property can have one or more buyers.

The sale of a property can be conducted by one agent, but an agent can sell many properties.

Based on your evaluation, identify all the data elements required to build your database schema, you are welcome to add additional attributes if you see fit... Refer to either the Group Project Demo or the examples from chapter six for assistance with completing the normalization process.

HINT: You should result in at least seven tables.

HINT: The purchase date and amount have a transitive dependency on a column other than the PK$.$

HINT: There is a M:N relationship that must be eliminated.

Once you$$ve completed the normalization process and identified all the entities, attributes, and relationships. You should determine datatype for each column, and consider the type of data to be stored. Refer back to Data Types.

Now you can create your ERD.

Group Project Example

Group Project ExampleApply the correct Oracle Naming Convention used when designing your schema.

Specify the PK and FK in your diagram.

Include the datatype for each attribute in your diagram.

Remember to be mindful of M:N relationships.