Extracted text: 4. 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 has a transitive dependency on a column other than the PK. • HINT: There is a M:N relationship that most be eliminated. 5. Once you've completed the normalization process and identified all the entities, attributes, relationships. You should determine datatype for each column, consider the type of data to be stored. Refer back to Data Types. 6. Now you can create your ERD. • Group Project Example • Apply 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.
Extracted text: 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 decides its time to retire and has asked you take over the family business. You wasn't sure if you were ready to leave your luscious 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 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 the business operations; this includes maintaining records of buyers, properties acquired, contractors, sales agent, agreements, and employees. Over the years there has 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 (accountant, lawyers, secretary, and two maintenance worker). In addition, over the years your parents has worked with several independent Real Estate Agents. • Your database should contain details from previous and potential buyers including account number, name, address, contact info, occupation, salary, credit score, employer code, employer name, employer address, employer phone number, and etc. It should also contain employees, contractor, and agents id, name, address, phone number, and etc. In addition, it should maintain contract agreements between various contractors and your company. These agreements should include the agreement number, contractor details (id,name, address, 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, specification such as the number of rooms, square feet, purchase date, purchase amount, and market value, architecture style. The property should also be associated with the appropriate buyer and agent details such as (id,name, address, phone number). You should identify the various architecture style, so the appropriate style id and description of each style can be referenced in the property details. Some architectural styles includes Cope Code, Colonial Contemporary, Ranch and etc. • You've also noted that a buyer can purchase one or more properties from you and the property can have one or more buyers. But a property can have only one architectural style, each architectural style can be referenced by many properties. The sale of a property can conducted by one agent but an agent can sale many properties.