BUAD 283 MIS - Assignment 02 Entity-Relationship (E-R) Database Design Eco-Gem Construction, Inc. Project Database You have been asked by Sandra Lee, the owner of Eco-Gem Construction to design a project planning database for managing her customers, building projects, suppliers, and employees. After several conversations with Sandra, you determine that an E-R diagram would be the best place to start in mapping out the data requirements from a logical perspective. Your task is to clearly design and present a relational database system that effectively and efficiently meets her business needs. Project Details: From your meeting notes with Sandra, you have been able to discern the following details with respect to preparing an E-R diagram for the proposed project planning database: 1. Customers: Your design needs to store each customer's contact information for billing purposes, including the business name (if applicable), contact person's name, full mailing address, phone number, and email. (Note: A customer might be a singleproperty homeowner or a property manager who owns several properties. Therefore, the billing address stored here may or may not be the same as the property's address.) 2. Properties: Your design must store information about each of the properties, including the building name (if commercial), full site address (required for construction permits), original photo, year of construction, and a "flag" to indicate whether the necessary building permits have been obtained. 3. Projects: The most important entity of your database is for storing the project details for a particular property. (Note: More than one project may be performed at a single property over its lifetime.) This entity must store the project's start date and expected finish date, its estimated budget, and a detailed description of the work to be completed. You should also allow for some additional information to be stored, such as special requests (e.g., "Don't start work before 9:00am"). Each project will have many employees assigned to work on it (e.g., framers and carpenters), but one employee must be designated as the "Site Supervisor." Sandra also wants to keep track of each employee's duties and total work hours on a projectby-project basis. Besides the company's own employees, external suppliers must be contracted to provide various services (e.g., lumber, scaffolding, and concrete). Sandra needs to track what services were provided by each supplier on a project and whether or not an invoice has been received for work completed and/or paid by her company. 4. Employees: Your design must store basic contact information for each employee, including their full name, home mailing address, phone number, and email address. Additionally, Sandra needs to store private employment details in encrypted form, including their hired date, hourly wage (as there are no salaried employees), and social insurance number for reporting purposes to the WCB and the CRA. 5. Suppliers: Your design needs to store information about each of the company's suppliers, along with the type of services they can provide (e.g., rentals, roofing, carpentry, etc.) The best suppliers are contracted for many projects each calendar year. In addition to the billing details (i.e., full mailing address and GST Registration \#), make sure to include the name, email, and phone \# for the owner or primary contact person. While the above information has been provided to help get you started, these descriptions are not meant to be a complete list of the entities (tables) that you will need to solve Sandra's problems; however, they will get you started on the right track! Assignment Deliverables: This assignment may be completed individually or with a partner. The deliverable is a 4-to-5 page document that includes: 1. a 1-page cover memo that uses a professional memo template. Include the name(s) and student number(s) of each team member, the submission date, a statement of the project's purpose and requirements, and a description of any constraints or assumptions that you have made in order to complete your data model. Your memo should be well-written and addressed to Sandra Lee at Eco-Gem Construction, Inc. 2. a 1-page E-R diagram that shows the required entities (tables) and their relationships using Crows-Foot notation. Each entity should specifically identify the Primary (PK) and Foreign Keys (FK) and the list of attributes (fields) to be stored within each table. Title your diagram as "Figure 1. Entity-Relationship Diagram" and, optionally, add free-form notes that you deem necessary to explain the relationships or cardinalities selected. 3. a 2-3 page database dictionary that describes the attributes to be contained within each entity. Title this section "Table 1. Database Dictionary" and provide individual table captions for each entity. For each attribute in the dictionary, include its name, description, data type (e.g., text, number, multimedia, currency, date/time, or yeso), and estimated width in characters. Also, specify whether an attribute is a key field or requires data validation (e.g., dates entered as YYYY-MM-DD). (Note: Please use the Excel dictionary template provided in Moodle for structuring your dictionary.) 4. ensure that a single Word or PDF document is uploaded to the Moodle dropbox for grading - if you use Excel to develop your database dictionary, you will need to import or copy and paste it into Word. Points will be deducted for uploading multiple documents. For the E-R diagram, export it from Draw.io or Diagrams.net as a PNG image file and then insert it into your Word document. Lastly, if you are working with a partner, only one of you needs to upload the final document (but ensure that both names appear on the memo cover page)