BUAD 283 MIS - Assignment 01 Entity-Relationship (E-R) Database Design Glenmore Glass, Inc. Project Database You have been asked by Jacinda Hill, the owner operator of Glenmore Glass to design a project planning database for managing her customers, projects, suppliers, and employees After several conversations with Jacinda, you determine that an E-R diagram would be the best place to start in mapping out the data requirements from a logical perspective. In this assignment, you will design (not develop) a structural database system that best meets Jacinda's business needs. Project Scenario From your meeting notes with Jacinda, you have been able to discem the following details with respect to designing a logical E-R diagram for the proposed project planning database: Customers: Your design needs to store the primary customer contact information for billing purposes. including their home phone, cell phone, and email address. (Note: The address in this table may or may not be the same as the project's property address.) Properties. A single customer may own one or several properties for which you will be installing glass. Your design should store information about each property, including the building name (if applicable), address, photos, year of construction, and the name, phone, and email of an on-site contact person. Projects. Your database needs to capture information about each project, including the start date property, budget, description of work (eg, inventory of windows to be installed), and any special requests (eg, "don't start work before 9:00am each day). Note that more than one project may be assigned to a single property over the years. Furthermore, cach project can have many employees assigned to work at it (eg, installers and finishing carpenters), but one employee is always designated as the supervisor and main contact for that project. Additionally. multiple suppliers may be contracted to provide lumber. scaffolding, window coverings, and other resources to the project. Like any good manager, Jacinda will need to print out a contact list that shows the phone and address information for each of her employees and suppliers. Employees: Your design should store basic information about each employee, including their full name, home mailing address, phone number, and email address, Jacinda also wants to store their employment details (eg, date hired, hourly wage, social insurance number, and information on who to contact and how in case of an emergency.) While the contact details for each employee should be accessible by all workers, the employment and emergency contact details should be kept separate and secure for only Jacinda to see. Suppliers: Your design needs to store contact information for each supplier, along with the type of services they provide (e.g., equipment rental, trim and molding, or blinds.) Each supplier can be contracted to handle multiple projects, even on the same day. Make sure to also include details for the primary contact person within the company. While this information has been provided to help get you started, these entities tables are not meant to be a complete list of entities tables that you will need to solve Jacinda's problems, however, they will get you going on the right track. Assignment Deliverables: This assignment should be completed with a partner. The deliverable is a 4page document that includes: a l-page transmittal or cover memo using a professional template. Include the name(s) and student number(s) of each team member, a statement of the project's purpose and/or business requirements, and a list of any assumptions that you have made in completing your ER diagram. Your memo should be addressed to Jacinda Hill at Glenmore Glass, Inc 2.a l-page E-R diagram that shows the required entities tables and relationships. Each entity table should specifically identify the Primary (PK) and Foreign Keys (FK) required and the list of attributes/fields to be stored within each table. Title your diagram as "Figure 1. Entity- Relationship Diagram" and provide any descriptive notes that you deem necessary to explain the relationships or cardinalities that you have selected a l-or 2-page database dictionary (prepared using an Excel worksheet or a Word table) that describes the attributes/fields to be contained within each entity table. For each entity/table, specify each attribute's name, description, data type (eg, text , number, date time. currency, yeso), estimated width in characters, and whether it is a key field or requires data validation (e.g., dates entered as YYYY-MM-DD). A sample data dictionary layout appears below: Awhe key el Type With Description Employee I may Key Test Employee number Su-3 Tells Esprve son YYYY.M.CO Assignment 2 ER Diagram W21