The Urban Real Estate Agency is undergoing expansion. The real estate boom has led to a sharp increase in real estate transactions. The agency has more than tripled its volume of transactions and, as such, has also increased its number of employees, including real estate agents, administrative staff, and managers. The employees are divided into Accounting, Human Resources, Sales, Administration, Marketing, and Management. Agents are not all employees of the agency. Some are independent agents that work only on a commission basis. Each employee has a unique employee number. Clients approach the agency for a variety of different transactions. The majority of clients come in to the agency looking for properties to purchase. Likewise, some clients come in to list their properties to sell. Some clients come looking for properties that are available for short-term or long-term renting or leasing. The agency is adamant about providing personalized service to all their clients. A dedicated Agent is assigned to each client to guide them through the entire process of purchasing or selling their property. Each client only works with one Agent. However, each Agent may work with several clients. The Agent is responsible for documenting each transaction related to each client. The Agent meets the client to learn more about their needs, what they are looking for, where they are interested in purchasing a property (if it is for a purchase), price range, services, etc. CA-DBSQL / Database Programming Concepts with SQL 2 Clients are invited to search through the property listings database to identify the properties that interest them. They should be able to search for properties based on a variety of search criteria to refine their search. Typically, clients refine their searches on price, location, number of bedrooms, and types of properties. However, additional criteria may also be used for searching for the ideal property. To ensure that the agency is able to track all transactions properly, each transaction must capture data that links the client to the property in question and the Agent that worked with them. The agency lists a large number of different types of properties ranging from detached single-dwelling properties, duplexes, triplexes, semi-detached, town houses, condominiums, to large multi-dwelling properties. The agency mangers want all the data that relates to the agency operations to be stored in a robust database so that it can be easily accessed as needed, remain intact, and prevent information from being lost. All data must be stored in a way that is easily accessible, searchable, and maintained in an efficient manner. Business rules: Management of the Urban Real Estate Agency wants the following business rules integrated into the database design:

• Each property must have a unique listing number that identifies it. 

• Searches for properties can be made by property type, price, location, number of bedrooms, or any combination of property descriptors. 

• Transaction types are Purchase, Sell, or Rental

 • One Agent is assigned to multiple clients, but each client only works with one Agent. 

• Agents must be identified by a unique identifier. • Not all Agents are employees. Some Agents are independent agents that work for the agency on a commission basis. Management wants to be able to access Agent related data quickly. Agent specific data should be able to track the usual information (name, address, etc.) but also the Agent's real estate license number and the commissions paid to the Agent on all transactions. 

• Management wants to be able to quickly identify which clients each Agent is working with. 

• Each transaction must have a unique transaction identifier that serves to track the transaction, the transaction details, and the client that entered into the transaction. 

• Transaction details must be easily accessible by management for accounting requirements. They must be able to track transactions by date (by week, month, year as needed) and by Agent. • Each property that is listed for sale has a listing Agent. The listing Agent may be different from the Agent that is working with the client on a property purchase. • Commission on each transaction is set as 6% of the transaction amount if the listing Agent is the same as the selling Agent. If the selling Agent is different from the listing Agent, then the commission is set at 3% each. CA-DBSQL / Database Programming Concepts with SQL 

• The Agency retains a total of 6% of the transaction as fees. From this amount, 1.5% is given as commission to the listing agent and 1.5% is given as commission to the selling agent. INSTRUCTIONS For this assignment, you will be responsible for designing the database structure only. You will not need to create the database, its tables, or its relationships. Essentially, you will be responsible for analyzing the case above and designing the database structure, its tables, and its fields. 1. Initial Analysis: (5 marks) Analyze the above case and extract the four main tables that will make up your database. 

Identification of Table Attributes:  For each table identified, define the attributes or columns that you think should be in each table. You must capture all the necessary data (At this point you do not need to worry about normalization). Important note: The description above does not explicitly detail all the data that should be captured. You will need to include the attributes that logically should be part of a table to make it complete. (For example, a typical employee table would contain employee data such as address, SIN number, telephone numbers, etc.)

 3. Identification of data types: Assign the appropriate data types to each attribute. Include size of field for each data type as needed.

Normalization of Design: (10 marks) Normalize the database design. If you have identified the four main tables in the scenario and you identified the necessary columns that reflect the data that one would normally work with in such a scenario, you should have tables that contain data that should be moved to a separate table. In this step, you are required to analyze your table data and identify those attributes that should be moved to a separate table, thus normalizing the database. Design the additional tables to support your revised design. (Hint: You may need to revise some of the datatypes accordingly.) SUBMISSION INSTRUCTIONS All the work that is required for this assignment should be done electronically. You can use a MS Word document to complete all of your tasks. You are not being graded on format. Your document should contain four sections, one for each of the tasks, clearly labelled. CA-DBSQL / Database Programming Concepts with SQL 

• The cover page should include your name, student number, date of submission, and instructor name. 

• You do not need to print out a copy of the final work, simply save it as a Word or PDF document and submit it on the LMS. Work must be submitted in the correct file type and b

Answer rating: 100% (QA)

Based on the information provided you can design the database structure with four main tables Property Agent Client and Transaction Heres how you can ... View the full answer