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.