CIS336 Lab 1: Normal Forms and Entity Relationship Diagrams

Introduction

In this exercise, you will analyze a de-normalized data set presented in the form of a spreadsheet. You will construct the Entity Relationship Diagram (ERD) depicting the logical design of the database. Your ERD will use Crows Foot notation to denote the relationships between tables.

Sample Exercise, With Solution

Before completing your lab, please review this example problem. The diagram below is a partial depiction of a business spreadsheet for a retail store operation. The store sells books. Books have a 13-digit International Standard Book Number (ISBN), a title, a publisher, and a unit price. Invoices track sales of books. An Invoice contains one or many line items, with each line item reflecting the sale of one or more copies of a specific book. Every publisher has a company name, and a publisher code.

InvNum	ISBN13	InvDate	BookTitle	PubCode	PubName	Qty	unitPrice
1022	9781291940336	2015-03-09	MYSQL Functions	1001	Lulu.com	3	4.99
1022	9780321833877	2015-03-09	MySQL, Fifth Edition	1029	Addison-Wesley	5	33.95
1022	9781890774820	2015-03-09	Murach's MySQL, 2nd Edition	1032	Murach, Mike & Associates, Inc	2	48.95
1249	9781449374020	2015-02-22	MySQL Cookbook: Solutions for Database Developers and Administrators	1118	O'Reilly Media, Incorporated	9	50.59
1249	9781449325572	2015-02-22	PHP & MySQL: The Missing Manual	1118	O'Reilly Media, Incorporated	6	29.95
1249	9781890774790	2015-02-22	Murach's PHP and MySQL, 2nd Edition	1032	Murach, Mike & Associates, Inc	1	48.95

Here is the ERD, using Crows Foot notation for one to many relationships.

Lets take a look at the above relational database.

You will notice that each table has a theme. That means that each column is dependent on the primary key. It table is an entity. The attributes in each table are a characteristic of the entity. It would make no sense to have the attribute QTY in the Invoices table because QTY would not be dependent on the Invoice number alone. It is the quantity for a book in a lineitem. It is dependent on both parts of the composite primary key of Invoice Number AND the Book Id. So, it satisfies Second Normal Forms. Remember that the idea is simple. You want to make sure you minimize data redundancy. That is why you normalize your tables into second and third normal forms. It would not make sense to have Publisher Name in the Books table because it is not dependent on the ISBN number. Books and Publishers are two different and separate entities. So that satisfies Third Normal Forms. First Normal Forms has been satisfied because there are no repeating columns in any of the tables.

Student Exercise

The student exercise for this lab is similar to the sample exercise presented above. Below is a partial spreadsheet. It is not normalized data. That means that there is a lot of data redundancy. Your job is to create an ERD for the database.

Create the entities and their relationships from it. First, find the entities. The entities are decomposable nouns. The nouns can be broken down into attributes which are atomic (cannot be broken down) nouns. So, find those nouns. You will find the entity names in the columns below. One is STORE because it is a noun and it is NOT ATOMIC. It can be broken down. It has several attributes as you can see below.

Next find the primary key for each entity. A primary key candidate must have unique values for each row. Find the attributes that belong to each theme. Remember that an attribute must be dependent on the primary key attribute of that entity. For instance, an employee will have his own unique employee ID. After you do that, then find the relationships. For instance, a region can have many stores. A store can have many employees.

Deliverable:

Draw the ERD for Macys, using Crows Foot notation using VISIO.

Copy and Paste your ERD to an empty Word Document for your submission.

Macy's USA Region Store Employee Employee Code Last Name First Name Job Base Store RegionDescription Store Address Store Phone EmployeelD Job Code Description PayYTD Sales NorthWest NorthWest NorthWest 123 Main 123 Main 200 Elk Ave 813-343-3333 813-343-3333 813-679-1234 100 JonesSally 100 Brown 101 Carter 500 100 SalesPerson 101 Clerk 102 Manager 40000 1,000,000 20000 1,000,000 80000 1,000,000 William oseph NorthWest NorthWest NorthWest 500 Low St. 500 Low St. 500 Low St. 687-487-4534 687-487-4534 687-487-4534 234 Jamison Amy 234 Clover 234 DeLuca Thomas Peter 345 262 789 U100 SalesPerson 40000 1,800,000 101 Clerk U102 Manager 20000 1,800,000 80000 1,800,000 NorthEast NorthEast NorthEast 800 3rd St. 800 3rd St. 800 3rd St. 345-564-7867 345-564-7867 345-564-7867 800 897 Thompson Bruce 897 Jackson Samuel 897 Thompson Bruce 100 SalesPerson 40000 2,000,000 101 Clerk 102 Manager 20000 2,000,000 80000 2,000,000 800 SouthEast SouthEast SouthEast 1Superior Lane 1 Superior Lane 1 Superior Lane 675-688-3457 675-688-3457 675-688-3457 600 HamiltonMary 600 Jackson Samuel 600 Thompson Bruce 1000 2000 800 100 SalesPerson 40000 150,000 101 Clerk 102 Manager 20000 150,000 80000 150,000 SouthWest SouthWest SouthWest 100 Rt 4 345 Lotus St. 5178 Randolf Ave.286-456-7812 286-456-3879 286-456-7945 300 Roberts Brooks 301 Williams Taylor 302 Daly 100 SalesPerson 40000 1,000,000 101 Clerk 102 Manager 234 20000 1,000,000 80000 1,000,000 James SouthWest 300 High St. SouthWest 300 High St. SouthWest 300 High St. 378-341-5679 378-341-5679 378-341-5679 897 Thompson Bruce 897 Jackson Debra 897 Thompson Bruce 1020 1023 1056 J100 SalesPerson 40000 2,000,000 101 Clerk 102 Manager 20000 2,000,000 80000 2,000,000