Question

View 2

Seneca Courses Inc.

Product Spreadsheet

All the course books are maintained on the following Product spreadsheet. SCI is currently having difficulty searching for books written by the same author, and would like the new database system to list each author separately. There can be one or many authors for each book, but there is no limit to the number of authors of a book.

PROD ID	TITLE	EDITION	Purchase Cost	Retail Price	AUTHOR
100860	DATABASE SYSTEMS: DESIGN AND IMPLEMENTATION	THIRD EDITION	$84.98	$99.98	CARLOS CORONEL, PETER ROB
100861	DATABASE SYSTEMS: DESIGN AND IMPLEMENTATION	FOURTH EDITION	$92.82	$109.20	CARLOS CORONEL, PETER ROB
100871	MODERN RETAILING	FOURTH EDITION	$88.36	$110.45	DANIEL CASTON, JENNY TAPSCOTT
100890	UNDERSTANDING MICROSOFT ACCESS	NINTH EDITION	$65.52	$72.80	PIERRE LEBLAND, ROBERT COBB
102130	ORACLE: INTRODUCTION TO PL/SQL		$53.24	$59.15	PHILIP ROBB
200376	ORACLE: ADVANCED PL/SQL DEVELOPMENT		$64.26	$71.40	PHILIP ROBB, DAN CHEN

Business Rules:

1. PURCHASE COST the default value is 0.

2. RETAIL PRICE must always have a value.

Normalize the above user view. Document all steps including UNF, 1NF, Dependencies, 2NF, and 3NF and explain or list the primary key and foreign

Answer 1

To convert to 1NF, the rules are:

1. Every column should have single value, eg: author will store one author's name not two author's name.
2. There should be a column with unique id for each row. eg: ID column is added and it contains 1, 2, 3, ... all values should be unique for each row.

To convert to 2NF, the rules are:

1. Database should be in 1NF.
2. Tables should not have any Partial Dependency, i.e. all columns must depend on primary key A + B. If a column C depends only on B or A then it is known as Partial Dependence.
eg: ProdID is unique and give any value from the table, but Title + Edition can also give any details, but the column Author is only dependent on Title and the prices are dependent on both title and edition. Since author is dependent only on one column of composite key so it is partial dependence.

To convert to 3NF, the rules are:

1. Database should be in 2NF
2. Tables should not have any Transitive Dependency, i.e. all columns in the table should be dependent on primary key suppose A + B. If a column C depends on another column D which is not primay key, this is known as Transitive Dependency.

Final Tables after converting to 3NF:

BOOK:

BookID   TITLE   EDITION   PurchaseCost   RetailPrice
AUTHOR:

AuthorID   AUTHOR
BookAuthor:

BookID   AuthorID
Bold letters are primary key of those tables.

Please contact me if you have any queries.

Thank you :)