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 2×4’s, 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.
Get expert help for Create an Entity Relationship Diagram and many more. 24X7 help, plag free solution. Order online now!
