Scribd
Upload
80 views

Normalization and ERD

The document discusses the normalization of a table to 1NF, 2NF, and 3NF. To achieve 1NF, the table ensures atomic values and consistent data types. For 2NF, the table is split into three tables to remove partial dependencies between location, name, and sales amount. Finally, reaching 3NF removes transitive dependencies by separating sales amount from warehouse location into their own table.

Uploaded by

Jaleel James
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
80 views

Normalization and ERD

The document discusses the normalization of a table to 1NF, 2NF, and 3NF. To achieve 1NF, the table ensures atomic values and consistent data types. For 2NF, the table is split into three tables to remove partial dependencies between location, name, and sales amount. Finally, reaching 3NF removes transitive dependencies by separating sales amount from warehouse location into their own table.

Uploaded by

Jaleel James
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

Marks 100%

Normalization and ERD

Jaleel James
20152822

In 1NF, the aim is to ensure each table has atomic values, not containing multiple lines, the values
should be of the same kind and each column should have a unique name. All of these criteria are
met in the table given and as such, it can be said that the table is in 1NF.

In 2NF, the table should be in 1NF and it should not have any partial dependencies. In the table
given, the primary key is CustNo. This is because it is unique for every row. Dependency is when
the primary key can be used to fetch data in other row or columns of the table. Partial dependency is
when two columns need to work together in order to get the exact and correct information as one
cannot work by itself. In this case the location can bring up a total amount of sales in that location
and a specific name can bring up all the sales for that name. Should you want to know a specific
name in a specific location two tables would need to be used and as such, we have a partial
dependency. To get rid of that and move to 2nf, the table was split into 3. Warehouse, Customer and
Employee. This is to completely remove any partial dependencies. That way, the composite keys,
like Sales area and Warehouse Location would be removed.

That results in the following tables

Employee
EmpNo (PK), EmpName,
Customer
CustNo (PK), CustName, EmpNo (FK)
Warehouse
WH-No(PK), WH-Location, CustNo (FK), EmpNo (FK), Sales-Amt

In 3NF, the table should be in 2NF and it should not have and Transitive Dependency. Transitive
dependency, is when an attribute depends on another attribute that isn’t a primary key. In this case,
the sales amount depends on the Warehouse location. To normalize this table we move these two
into their own table.

That results in the following tables

Employee
EmpNo (PK), EmpName,
Customer
CustNo (PK), CustName, EmpNo (FK)
Warehouse
WH-No(PK), WH-Location, CustNo (FK), EmpNo (FK),
Sales
Sales-AMT(PK), WH-Location

You might also like