Simple Database Development on SQL Server 2008

The following describes the scenario and business rules of a real estate firm that lists property for sale. A system analyst has prepared an ERD for the logical model and a definition for each entity type and relationships. You are the database developer who is required to implement this database on SQL Server 2008. You must complete the following: • Create a new database called REFIRM • Create tables and relationships based on the definitions • Each table must have appropriate primary key, foreign keys and indexes • Generate a DDL script to create these tables and related objects • Write a script to INSERT test data (at least 3 records for each table) • Test the database to ensure data can be inserted and updated properly • Create a view to display data from two or more tables • Write a stored procedure to update data The project submission must include: • A screenshot of the Database Diagram to show the overall design • A screenshot to show the design of each table • DDL Script file for creating tables, view, and stored procedure • DML Script file for inserting test data • Screenshots to show that the scripts can be executed successfully Entities: Employee: An employee of the firm. An employee works for one sales office and may manage one sales office. It is not explicitly indicated that the employee can only manage the office that he/she works for. This would require a business rule. Sales_Office: The office where real estate is sold. Property: Buildings for sale, such as houses, condos and apartment buildings. Owner: The individual who owns one or more properties. Attributes on Employee: Employee_ID: A unique identifier for an employee. This attribute must be unique. Employee_Name: The name of the employee. Attributes on Sales_Office: Office_Number: A unique identifier for the office. Location: The physical location of the sales office. This data may be made up of the city and state. Attributes on Property: Property_ID: The unique identifier for the property. Location: A composite attribute that consists of the street address, city, state, and Zip Code. Attributes on Owner: Owner_ID: The unique identifier for the owner. Owner_Name: The name of the owner. Relationship: Is_assigned: An employee is assigned to one sales office. A sales office may have many employees assigned but must have at least one employee. Manages: An employee may manage one sales office or no sales office. Each sales office is managed by one employee. A business rule is needed here in order to indicate that an employee can only manage the sales office in which he or she works. Lists: Each property is listed by only one sales office. Each sales office can list one, none, or many properties. Owns: Each property has one or more owners. Each owner can own one or more properties. Percent_owned is an attribute on Owns; it tracks the percent of property that the owner owns.