Task 1 - Create an Entity Relationship Diagram (ERD).

Task 1 - Create an Entity Relationship Diagram (ERD).

The scenario to be modelled is as follows:

The Wildwood Department Store chain is located on Vancouver Island, BC, and has recently expanded from one location in Victoria to having new ones in Campbell River, Colwood, Langford, and Nanaimo as well.

To ensure that each location can be open and fully staffed 7 days per week, they would like to create a scheduling availability system for their now over 200 employees. Their goal is to have a system that can answer questions similar to ones like which employees are available to work in which location next Tuesday.

Each store location has a food section, a clothing section, a hardware section, and a sporting goods section, and the Victoria store also has a human resources section and a payroll section. Employees are only allowed to work in the sections which they have been trained in, but they may work in any location that has that section.

However, to reduce their employees carbon footprints, they will only schedule employees who have a commute of less than 20 minutes to a given store location, so the company has collected the set of commute times to/from each store location for each employee. Each employee must provide either or both a contact phone number and an email address where they may be contacted, and indicate which is the preferred method. And at the beginning of each year, every employee must specify which days of the week they will be available for that year no changes are then allowed until the next calendar year.

Create a fully-attributed ERD for a data model in 3rd normal form which includes all elements required to describe the above scenario.

Task 2 Convert flattened data into a set of relational tables.

The matrix below contains a dataset representing a set of suppliers shopped at by a given household in a flattened format. Normalize out this data into a set of populated tables which will represent the same data in at least 3rd Normal Form. Ensure that all data from the table is included in the tables which you create, such that if the correct query were run against your tables, the query results would duplicate the data from the original matrix. Include surrogate primary key values in the created tables if/where required.

Supplier Supply Categories Products Address Status Phone Numbers

ABC Groceries Food, Candy Cheerios, Milk, Chocolate Bars 15 Fourth Street Victoria BC Active 250-555-2221 (front desk),250-555-2225 (deli)

XYZ Hardware Construction Hammers, Nails 20 First Avenue Langford BC Active 250-555-2223

GHI Lumber Construction Spruce 2x4s, Nails 30 Second Avenue Langford BC Inactive 250-555-1212 (switchboard)250-555-2224 (wood),250-555-2226 (hardware)

MNO Chocolates Candy Easter Bunnies, Chocolate Bars 40 Fifth Street Vancouver BC Active 250-555-2227

PQR Flowers Decorations Roses 22 Hillside Ave Victoria BC Inactive 250-555-2228 (red flowers),250-555-2229 (blue flowers)

DEF Meats Food Steak 44 Oak Bay Ave Victoria BC Active 250-555-2230

Task 3 Analyze a provided ERD for design flaws.

In the ERD shown below, there are a number of sub-optimal design decisions incorporated into the model which the diagram represents. For each deficiency that you find:

a) identify where the deficiency exists (name the entity, attribute, and/or relationship)

b) describe the nature and impact of the deficiency; and

c) recommend a potential way(s) to modify the model to correct the deficiency.

ERD to be analyzed:

(A higher-definition copy of the above ERD is provided in the accompanying email in PDF format.)