Scribd
Upload
10 views2 pages

Database Normalization Guide: 1NF to 3NF

The document outlines the normalization process for two tables: Branch Information and Branch and Staff Information. It explains why the tables are not in First Normal Form (1NF) and Third Normal Form (3NF), detailing the steps taken to achieve normalization, including the creation of separate tables for telephone numbers and staff information. The document also identifies primary and foreign keys for each table.

Uploaded by

Krvxdee Flex
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
10 views2 pages

Database Normalization Guide: 1NF to 3NF

The document outlines the normalization process for two tables: Branch Information and Branch and Staff Information. It explains why the tables are not in First Normal Form (1NF) and Third Normal Form (3NF), detailing the steps taken to achieve normalization, including the creation of separate tables for telephone numbers and staff information. The document also identifies primary and foreign keys for each table.

Uploaded by

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

Absolutely! Let's break down the normalization process for both tables.

Table 1: Branch Information


1. a) Why is this table not in 1NF?
The table is not in 1NF (First Normal Form) because the telNos attribute contains multiple
values (a repeating group) within a single cell. 1NF requires that each attribute of a relation
must be atomic, meaning it should contain only a single value.
1. b) Normalizing to 3NF
1. 1NF (Remove Repeating Groups):
To achieve 1NF, we eliminate the repeating group by creating a separate table for telephone
numbers.
Branch Table
branchNo branchAddress
B001 8 Jefferson Way, Portland, OR 97201
B002 City Center Plaza, Seattle, WA 98122
B003 14 8th Avenue, New York, NY 10012
B004 16 14th Avenue, Seattle, WA 98128
BranchTel Table
branchNo telNo
B001 503-555-3618
B001 503-555-2727
B001 503-555-6534
B002 206-555-6756
B002 206-555-8836
B003 212-371-3000
B004 206-555-3131
B004 206-555-4112
2. 2NF (Remove Redundancy):
The tables are already in 2NF as they don't have any non-key attributes that are functionally
dependent on part of a candidate key. (In this case, the primary key is a single attribute,
branchNo in both tables).
3. 3NF (Remove Transitive Dependencies):
There are no transitive dependencies (where a non-key attribute is dependent on another
non-key attribute) in the current tables. Therefore, the tables are already in 3NF.
1. c) Keys
Branch Table:
●​ Primary Key: branchNo
BranchTel Table:
●​ Primary Key: (branchNo, telNo) (composite key)
●​ Foreign Key: branchNo (references Branch table)
Table 2: Branch and Staff Information
2. a) Why is this table not in 3NF?
The table is not in 3NF because it has a transitive dependency. The name attribute is
functionally dependent on mgrStaffNo, which in turn is functionally dependent on branchNo. In
other words, branchNo determines the manager's staff number, and the manager's staff number
determines the manager's name.
2. b) Normalizing to 3NF
1. 1NF (Remove Repeating Groups):
The table is already in 1NF as there are no repeating groups.
2. 2NF (Remove Redundancy):
The table is already in 2NF as it has a single-attribute primary key (branchNo).
3. 3NF (Remove Transitive Dependencies):
To achieve 3NF, we remove the transitive dependency by creating a separate table for staff
information.
Branch Table
branchNo branchAddress telNo mgrStaffNo
B001 8 Jefferson Way, 503-555-3618 S1500
Portland, OR 97201
B002 City Center Plaza, 206-555-6756 S0010
Seattle, WA 98122
B003 14 8th Avenue, New 212-371-3000 S0145
York, NY 10012
B004 16 14th Avenue, 206-555-3131 S2250
Seattle, WA 98128
Staff Table
staffNo name
S1500 Tom Daniels
S0010 Mary Martinez
S0145 Art Peters
S2250 Sally Stern
2. c) Keys
Branch Table:
●​ Primary Key: branchNo
●​ Foreign Key: mgrStaffNo (references Staff table)
Staff Table:
●​ Primary Key: staffNo

You might also like