Database Design and Normalization

Objectives:

1. Produce an ERD in 3NF from source documents and client input.
2. Demonstrate the process of normalization showing the database tables as they exist in the initial table, 1NF, 2NF and 3NF.
3. Merge 3NF solutions from different views to create one composite ERD

The Company

Graffiti Busters is a growing company that provides services to clean up graffiti.

Staff Training View

The staff view contains data for each employee, their training history, and training sessions. Each training session earns an employee training credits which can be used for determining promotion within the company.

Staff information and Training History
Staff ID: 132			Training Credits: 12
Name: Jason Painter			Staff Type Code: 2
Phone: 780 555-1121			Staff Type Description: Painter
			Wage: $24
Offering Code	Training ID	Start Date	End Date	Description	Credits	Grade
12	Paint101	Jan 1/21	Jan 8/21	Introduction to Painting	3	65
142	Finishing	Feb 5/21	Feb 9/21	Sanding and Finishing	6	76
96	Cleaning	Jun 4/21	Jun 9/21	Cleaning and Clearing	3	53

• Each staff member belongs to one staff type which describes their staff type and their wage.
• All staff of the same staff type have the same wage.
• Only training that is passed (> 50%) is recorded.
• There are no partial credits awarded.
• The Offering Code uniquely identifies a training opportunity (a particular Training ID offered during a particular time).
• Training ID uniquely identifies a training course (but not the offering) and is offered several times each year and occasionally more than once at the same time.

Supply View

The supply view contains data for supplies that the company uses on jobs. Each supply item belongs to a Supply category (brushes, soaps, paint, etc.). For organizational purposes, all supply items belonging to the same category are stored in the same storage room. This means that brushes are all stored in the same storage room, soaps in the same storage room, etc.

Supply Record
Supply Code: PR104	Category Code: P5
Description: Primer	Category Description: 5 gallon paint products
Storage Room: 201

Job Invoice View

The job invoice view contains data for clients, services, and their jobs There is a cost per hour for each service that can be looked up when potential clients call inquiring about costs.

Invoice
Job Number: 43				Staff ID: 132
Date: January 23, 2021				Staff Name: Jason Painter
Job Address:	11211 Trouble Street		Client ID: 2154
	Edmonton, AB T8E 1G6		Name: Debbie Smithers
				Phone: 780-555-6574
Service Code	Description	Notes	Cost per Hour			Hours	Extended Cost
Prime1	Priming	Fence Priming	$18.00			1.0	$18.00
Painting1	Painting	Fence Painting	$18.00			2.5	$45.00
					SubTotal		$63.00
					GST		$3.15
					Total		$66.15

• One staff member is assigned to each job.
• Each job can have many services on it such as painting, scrubbing, etc.
• The cost of each service is based on the cost per hour and how many hours are needed to complete the service.

Job Supplies View

The Job Supplies View contains data for the supplies that are needed for a particular job.

Job Supplies
Job Number: 43		Staff ID: 132
Job Date: January 23, 2021		Staff Name: Jason Painter
Supply Code	Description		Quantity
PR104	Primer		1
PR255	Red Paint		2

Merged ERD

• A 3NF solution for each view including ALL the attributes and entities as they appear in the initial table (0NF), 1NF, 2NF, and 3NF. Use bold to indicate primary key attributes and italics to identify foreign key attributes.
• An ERD for each 3NF solution of each view and an ERD of the merged solution (5 ERDs total) create ERD with excel.