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 Jacindas business needs.

Project Scenario

From your meeting notes with Jacinda, you have been able to discern the following details with respect to designing a logical E-R diagram for the proposed project planning database:

1. 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 projects property address.)
2. 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.
3. Projects: Your database needs to capture information about each project, including the start date, property, budget, description of work (e.g., inventory of windows to be installed), and any special requests (e.g., dont start work before 9:00am each day). Note that more than one project may be assigned to a single property over the years. Furthermore, each project can have many employees assigned to work at it (e.g., 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.
4. 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 (e.g., 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.
5. 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 Jacindas 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:

1. a 1-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 projects 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 1-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.
3. a 1- 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 attributes name, description, data type (e.g., text, number, date/time, currency, yes/no), 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:

Entity/Table Name (e.g., EMPLOYEES)
Attribute	Key Field	Type	Width	Description	Validation
Employee ID	Primary Key	Text	9	Employee number	Starts with 300
First Name		Text	15	Employees given name
.
Birthdate		Date	10	Date of birth	YYYY-MM-DD
.