Assignment 2 – Database Implementation and SQL Statements
* To analyse and comprehend a provided ER diagram
* To create and update normalized relations of the data from the provided ER * To create and update Database Schema
* To implement a database based on the provided ER diagram * To write required SQL statements to query the database
* ERDiagram.docx – contains a copy of the initial ER diagram * STUDENTcreateDatabase.txt – contains sql statements to create some of the tables required * STUDENTinsertData.txt – contains sql statements to insert some of the data required When to submit
Due Date: Thursday of Week 11.
Refer to the Course Description for further details regarding the due date, extensions and late policy. Project Specification
Acme RealEstate Pty Ltd is a small real estate agency that has a number of branches throughout the state. They have decided to implement a new database to manage their business activities. They had employed a consultant to undertake the work, however, after creating an ER diagram, a file containing the statements to create a part of the database and to insert some sample data, the consultant left the business. The management executive decided to commission you as a consultant to continue the work utilizing the ER diagram that has been created. You are required to analyze and improve the ER diagram, to develop a relational database schema, create normalize relational data structures, implement and load the database, and to write the required SQL queries. During the process of finalizing the design and implementing the database, you may decide to alter the structure of the ER diagram, for example there may be tables that are not in 3NF that need to be changed or names of fields that you would like to alter. If you do decide to make changes to the structure please include within your documentation an ER diagram incorporating the changes, a short statement summarizing the changes that have been made and a rationale for why the changes were necessary.
Acme RealEstate Pty Ltd
Acme provided details regarding their organizations current business activities to assist you with completing the required tasks. Acme Realestate is a small agency with branches in a number of locations. Consultants are located at a particular branch and each branch has a consultant acting as a manager. Acme would like to store some basic information about their consultants in the new database including their name details, mobile number and the branch that they are working at. Acme would also like to store the address details for each branch. Vendors list their properties for sale with Acme. Acme do not handle rental properties at all. Acme currently stores the name of their vendors along with relevant address details including, but not limited to, postal address and location address. A vendor may list multiple properties for sale with Acme. For each property Acme stores both a short and long description that are used in any promotional material used to sell the property. They also store the address of the property, how many bedrooms and bathrooms the property has, and the size of the land that the property is on. Acme also store any additional features that the property may have including off-street parking, tennis court, pool, gazebo etc. After a property is listed Acme also store a number of photos for the property for use in promotional material. Each property has at least one photo but may have a larger number. When a property is listed the details about the listing agent are kept so that when the property is sold agents may be paid an appropriate commission. When a property is purchased details about the client who has purchased the property are stored, including their name, address and phone number(s). A purchaser may buy multiple properties from Acme. When a purchaser makes an offer on a property the status of a...
Please join StudyMode to read the full document