2/15/26, 12:16 AM Thea

1
  

Study Guide Summaries

Edit  Regenerate

Database Normalization
Introduction to Normalization
Normalization is a systematic process of decomposing relations (tables) that exhibit undesirable
characteristics into smaller, well-structured relations. Its primary goals are to:

Eliminate Redundancy: Reduce the repetition of data, which saves storage space and prevents
inconsistencies.

Organize Data Efficiently: Structure data in a logical and manageable way.

Reduce Anomalies: Minimize potential problems that can occur during data operations like
insertion, updating, and deletion.

The main normal forms, in increasing order of strictness, are:

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

Boyce Codd Normal Form (BCNF)

First Normal Form (1NF)

A relation is in First Normal Form (1NF) if and only if:

The domain of each attribute contains only atomic (simple, indivisible) values.

The value of any attribute in a tuple (row) must be a single value.

It disallows composite attributes that are themselves multi-valued.

It disallows nested relations, where each tuple can contain a relation within a relation.

Example 01: Department Table (Not in 1NF)

Dno Dname ManagerEno Dloc

1 HQ 100 Colombo
2 Marketing 200 Colombo
Kandy
3 Research 300 Galle
Gampaha
N'eliya
Reason for not being in 1NF: The Dloc attribute is multi-valued (e.g., Colombo and Kandy for
Department 2).

Decomposition to achieve 1NF:

Department Table:

Dno Dname ManagerEno

1 HQ 100
2 Marketing 200
3 Research 300
Department Location Table:

Dno Dloc
1 Colombo
2 Colombo
2 Kandy
3 Galle

[Link] 1/8
2/15/26, 12:16 AM Thea

Dno Dloc 1

3 Gampaha   
3 N'eliya
Now, both tables are in 1NF because all attributes contain single, atomic values.

Example 02: Nested Relation (Un-normalized)

Emp-Project { Eno, Ename, Address { Pno, hours} }

This represents a nested relation where Address contains Pno and hours. To normalize this,
repeating groups (like the nested relation) must be removed.

Anomalies in Data Operations

Anomalies are issues that arise in databases when data is not properly normalized.

Insert Anomaly: Occurs when it's difficult or impossible to add new data without having to add
redundant data. For example, if a course has no students enrolled yet, you might not be able to
add the course information if the course details are only stored when a student enrolls in it.

Update Anomaly: Occurs when updating a single piece of data requires updating multiple rows,
leading to the risk of inconsistencies if not all occurrences are updated. For instance, if a
student's address is stored with every course they are enrolled in, changing their address would
require updating many rows.

Delete Anomaly: Occurs when deleting a piece of data unintentionally removes other, unrelated
data. For example, if a student is deleted, and their course information was only stored
alongside their student record, that course information might be lost.

Second Normal Form (2NF)

A relation is in 2NF if:

1. It is in 1NF.

2. Every non-key attribute is fully functionally dependent on the primary key.

Fully Functional Dependency: An attribute 'B' is fully functionally dependent on 'A' if it is functionally
dependent on 'A' and not functionally dependent on any proper subset of 'A'. This concept is
particularly relevant for relations with composite primary keys.

Example:

Consider a relation Student (Sno, Sname, Age). If Sno is the primary key, then Sname and Age are
fully functionally dependent on Sno because Sno is a single attribute.

Example with Composite Primary Key:

Items (Invoice_No, Item_No, Item_Name, Invoice_Date, Order_Qty)

Assume the primary key is {Invoice\_No, Item\_No}.

Invoice\_Date is functionally dependent on Invoice\_No (a part of the primary key).

Item\_Name is functionally dependent on Item\_No (a part of the primary key).

Order\_Qty is fully functionally dependent on {Invoice\_No, Item\_No} (the entire primary

key).

This relation is not in 2NF because Invoice_Date and Item_Name are only dependent on a part of
the composite primary key, not the whole key. To achieve 2NF, we would decompose it:

Invoice Table: (Invoice_No, Invoice_Date) - Primary Key: Invoice_No Item Table: (Item_No,
Item_Name) - Primary Key: Item_No Invoice Item Table: (Invoice_No, Item_No, Order_Qty) -
Primary Key: {Invoice_No, Item_No}

Third Normal Form (3NF)

A relation is in 3NF if:

1. It is in 2NF.
2. Every non-key attribute is non-transitively dependent on the primary key.

[Link] 2/8
2/15/26, 12:16 AM Thea

Transitive Dependency: If attributes X, Y, and Z exist in a relation, and X → Y and Y → Z , then Z 1

is transitively dependent on X (via Y). In 3NF, a non-key attribute should not be dependent on   
another non-key attribute.

Condition B for 3NF: Suppose in a relation R, a functional dependency X → A exists. Then, for R to
be in 3NF, one of the following must be true:

X is a super key of R.

A is a prime attribute of R (meaning A is part of some candidate key).

Super Key: A set of one or more attributes that can uniquely identify a row in a table.

Candidate Key: A minimal super key; a super key with no redundant attributes.

Example 1: Supplier Relation (Decomposition to 3NF)

Relation: Supplier = {Sno, Pno, Sname, City, Status, Pname, Qty} Functional
Dependencies:

{Sno, Pno} \rightarrow Qty

Sno \rightarrow {Sname, City}

Pno \rightarrow Pname

City \rightarrow Status Primary Key: {Sno, Pno}

Analysis:

1. 1NF: Assume it's in 1NF (atomic values).

2. 2NF:

Qty is fully functionally dependent on {Sno, Pno}.

Sname and City are dependent on Sno (part of PK).

Pname is dependent on Pno (part of PK).

Status is dependent on City, which is dependent on Sno (transitive dependency: Sno

\rightarrow City \rightarrow Status).

To achieve 2NF, we decompose based on dependencies on parts of the key:

R1 = {Sno, Sname, City} (PK: Sno)

R2 = {Pno, Pname} (PK: Pno)

R3 = {Sno, Pno, Qty} (PK: {Sno, Pno})

3. 3NF: Now, check the decomposed relations for 3NF.

R1 = {Sno, Sname, City}: Sno is PK. Sname and City depend on Sno. This is in 3NF.

R2 = {Pno, Pname}: Pno is PK. Pname depends on Pno. This is in 3NF.

R3 = {Sno, Pno, Qty}: {Sno, Pno} is PK. Qty depends on {Sno, Pno}. This is in 3NF.

However, we still have the City \rightarrow Status dependency in the original relation, which
implies a transitive dependency from Sno to Status (Sno \rightarrow City \rightarrow
Status). The relation R1 = {Sno, Sname, City} needs further decomposition. The
dependency City \rightarrow Status means that Status is dependent on City, and City is
a non-key attribute in R1. To satisfy 3NF, City must be the primary key of a new relation, or
Status must be part of a key. Decomposition into 3NF:

Supplier_Info = {Sno, Sname, City} (PK: Sno)

City_Status = {City, Status} (PK: City)

Product_Info = {Pno, Pname} (PK: Pno)

Supply = {Sno, Pno, Qty} (PK: {Sno, Pno})

Let's re-verify the dependencies for the 3NF relations:

Supplier_Info: Sno \rightarrow Sname, Sno \rightarrow City. Sno is PK. All non-key
attributes (Sname, City) are directly dependent on the PK. No transitive dependencies. In
3NF.

City_Status: City \rightarrow Status. City is PK. Status is directly dependent on PK. In
3NF.

Product_Info: Pno \rightarrow Pname. Pno is PK. Pname is directly dependent on PK. In
3NF.

[Link] 3/8
2/15/26, 12:16 AM Thea

Supply: {Sno, Pno} \rightarrow Qty. {Sno, Pno} is PK. Qty is directly dependent on PK. 1

In 3NF.   

Boyce Codd Normal Form (BCNF)

A relation is in BCNF if, for every non-trivial functional dependency X → A that holds in the
relation, X is a super key of the relation.

BCNF is a stricter version of 3NF. If a relation is in BCNF, it is also in 3NF. However, a relation in 3NF is
not necessarily in BCNF.

The key difference: In 3NF, a non-key attribute can depend on another non-key attribute (as long
as that non-key attribute is transitively dependent on the PK). In BCNF, any attribute that is the
determinant of a functional dependency must be a super key.

Example 1: 3NF but not BCNF

Consider a relation with dependencies:

{Student, Course} \rightarrow Teacher

Teacher \rightarrow Course

Assume the primary key is {Student, Course}.

3NF Check:
{Student, Course} \rightarrow Teacher: {Student, Course} is the primary key (and
thus a super key). This dependency is fine for 3NF.

Teacher \rightarrow Course: Here, Teacher is a non-key attribute, and Course is part of
the primary key (a prime attribute). This dependency is also allowed in 3NF.

BCNF Check:
{Student, Course} \rightarrow Teacher: {Student, Course} is a super key. This is fine.

Teacher \rightarrow Course: For BCNF, Teacher must be a super key. However, Teacher
alone cannot uniquely identify a row because multiple students might have the same
teacher for the same course, and Teacher alone doesn't include Student. So, Teacher is not
a super key.

This relation violates BCNF because of the Teacher \rightarrow Course dependency where
Teacher is not a super key.

Decomposition to BCNF:

To decompose this into BCNF, we separate the problematic dependency:

1. Student-Teacher Table: {Student, Course, Teacher} (PK: {Student, Course})

2. Teacher-Course Table: {Teacher, Course} (PK: Teacher - assuming a teacher teaches only
one course, or {Teacher, Course} if a teacher can teach multiple courses but we want to
record this specific assignment)

Let's assume the dependency is Teacher teaches a Course. So, Teacher determines Course. If the
original relation was Student_Course_Teacher (Student, Course, Teacher) with PK {Student,
Course} and dependencies {Student, Course} \rightarrow Teacher and Teacher
\rightarrow Course.

Student_Course_Teacher is in 3NF.

To make it BCNF, we decompose:

SC_T (Student, Course, Teacher) (PK: {Student, Course})

TC (Teacher, Course) (PK: Teacher)

Now, in TC, Teacher is the PK, and Course is determined by Teacher. This is BCNF. In SC_T,
{Student, Course} is the PK, and Teacher is determined by {Student, Course}. This is BCNF.

Example 2: Decompose to BCNF

Relation: Emp (E#, Manager) with functional dependencies E# \rightarrow Manager and Manager
\rightarrow Specialty. (Implicitly, there's a Specialty attribute related to Manager). Let's
assume the relation is Emp_Manager_Specialty (E#, Manager, Specialty).

Dependencies:

[Link] 4/8
2/15/26, 12:16 AM Thea

E# \rightarrow Manager 1
  
Manager \rightarrow Specialty Primary Key: E#

3NF Check:
E# \rightarrow Manager: E# is PK (super key). OK.

Manager \rightarrow Specialty: Manager is a non-key attribute, Specialty is a non-key

attribute. This is a transitive dependency (E# \rightarrow Manager \rightarrow
Specialty). To be in 3NF, we need to remove this.

Decomposition to 3NF:
1. Emp_Manager (E#, Manager) (PK: E#)

2. Manager_Specialty (Manager, Specialty) (PK: Manager)

BCNF Check:
In Emp_Manager: E# \rightarrow Manager. E# is PK (super key). OK.

In Manager_Specialty: Manager \rightarrow Specialty. Manager is PK (super key). OK.

Both relations are in 3NF and BCNF.

Example 5: Normalization of a Table

Consider the table:

A B C D
a1 b1 c1 d1
a1 b2 c2 d2
a2 b3 c3 d3
Assume the primary key is A. Dependencies:

A \rightarrow B (Not necessarily, as A maps to b1 and b2)

Let's assume functional dependencies are implied by the structure. If A uniquely determines B,
C, D, then PK is A. If A determines B, B determines C, C determines D, then:
A \rightarrow B

B \rightarrow C
C \rightarrow D

If PK is A, and dependencies are A \rightarrow B, B \rightarrow C, C \rightarrow D:

1NF: Assume it is.

2NF: A is the PK. All other attributes (B, C, D) are dependent on A. The dependencies are A
\rightarrow B, A \rightarrow C (via B), A \rightarrow D (via B and C). If B, C, D are directly
dependent on A, then it's in 2NF.
3NF:
A \rightarrow B: A is PK. OK.

B \rightarrow C: B is a non-key attribute, C is a non-key attribute. This is a transitive

dependency (A \rightarrow B \rightarrow C).

C \rightarrow D: C is a non-key attribute, D is a non-key attribute. This is a transitive

dependency (A \rightarrow B \rightarrow C \rightarrow D).

To decompose into 3NF:

1. R1 = {A, B} (PK: A)

2. R2 = {B, C} (PK: B)

3. R3 = {C, D} (PK: C)

BCNF: Check the 3NF relations:

R1 = {A, B}: A \rightarrow B. A is PK (super key). OK.

R2 = {B, C}: B \rightarrow C. B is PK (super key). OK.

R3 = {C, D}: C \rightarrow D. C is PK (super key). OK.

This decomposition satisfies BCNF. The example provided in the material shows a different
decomposition for BCNF, suggesting a different interpretation of the original table's dependencies
or structure.

Let's re-evaluate Example 5 with the provided BCNF decomposition: Original: A | B C D E F G

[Link] 5/8
2/15/26, 12:16 AM Thea

If we assume dependencies: A \rightarrow B A \rightarrow C A \rightarrow D A 1

\rightarrow E A \rightarrow F A \rightarrow G   

And potentially other dependencies like E \rightarrow F, F \rightarrow G.

If the table is:

AB C D E F G
a1 b1 c1 d1 e1 f1 g1
a1 b2 c2 d2 e2 f2 g2
And we have dependencies: A \rightarrow B A \rightarrow E E \rightarrow F F \rightarrow
G B \rightarrow C C \rightarrow D

PK: A

3NF:
A \rightarrow B: OK.

A \rightarrow E: OK.

E \rightarrow F: Transitive (A \rightarrow E \rightarrow F).

F \rightarrow G: Transitive (A \rightarrow E \rightarrow F \rightarrow G).

B \rightarrow C: Transitive (A \rightarrow B \rightarrow C).

C \rightarrow D: Transitive (A \rightarrow B \rightarrow C \rightarrow D).

Decomposition to 3NF:

R1 = {A, B} (PK: A)

R2 = {B, C} (PK: B)

R3 = {C, D} (PK: C)

R4 = {A, E} (PK: A)

R5 = {E, F} (PK: E)

R6 = {F, G} (PK: F)

BCNF: Check the 3NF relations. All of them satisfy BCNF as each has a dependency where the
determinant is the primary key.

The provided BCNF decomposition is: A | C | D A | E | F | G D | B

This implies a different set of dependencies or a misunderstanding of the original table structure.
Based on the provided BCNF example:

A \rightarrow C, C \rightarrow D

A \rightarrow E, E \rightarrow F, F \rightarrow G

D \rightarrow B

If the original table was:

AB C D E F G
a1 b1 c1 d1 e1 f1 g1
a1 b2 c2 d2 e2 f2 g2
a1 b3 d3 e3 f3 g3 ...
Let's assume the dependencies as implied by the BCNF decomposition: A \rightarrow C C
\rightarrow D A \rightarrow E E \rightarrow F F \rightarrow G D \rightarrow B

And assume PK is A.

3NF:
A \rightarrow C (OK)

C \rightarrow D (Transitive: A \rightarrow C \rightarrow D)

A \rightarrow E (OK)

E \rightarrow F (Transitive: A \rightarrow E \rightarrow F)

F \rightarrow G (Transitive: A \rightarrow E \rightarrow F \rightarrow G)

D \rightarrow B (Transitive: A \rightarrow C \rightarrow D \rightarrow B)

Decomposition to 3NF:

[Link] 6/8
2/15/26, 12:16 AM Thea

1. R1 = {A, C, E} (PK: A) 1
  
2. R2 = {C, D} (PK: C)

3. R3 = {E, F} (PK: E)

4. R4 = {F, G} (PK: F)

5. R5 = {D, B} (PK: D)

BCNF: Check the 3NF relations. All satisfy BCNF as the determinant is the PK. The provided
BCNF decomposition is: A, C, D (PK: A) A, E, F, G (PK: A) D, B (PK: D) This decomposition
implies that A determines C and E. C determines D. E determines F. F determines G. D determines
B. Let's assume the original table has PK A and dependencies: A \rightarrow C A \rightarrow
E C \rightarrow D E \rightarrow F F \rightarrow G D \rightarrow B

BCNF decomposition:
1. R1 = {A, C, E} (PK: A) - Dependencies A \rightarrow C, A \rightarrow E. A is super
key. OK.

2. R2 = {C, D} (PK: C) - Dependency C \rightarrow D. C is super key. OK.

3. R3 = {E, F} (PK: E) - Dependency E \rightarrow F. E is super key. OK.

4. R4 = {F, G} (PK: F) - Dependency F \rightarrow G. F is super key. OK.

5. R5 = {D, B} (PK: D) - Dependency D \rightarrow B. D is super key. OK.

This results in the same set of relations as the 3NF decomposition if we interpret the BCNF
decomposition provided in the material as the result of decomposing a table with these
dependencies. The material's BCNF decomposition is: A, C, D A, E, F, G D, B This implies:

Relation 1: {A, C, D}. Dependencies: A \rightarrow C, C \rightarrow D. PK: A. Here, C

\rightarrow D is problematic for BCNF if C is not a super key. If A is the only candidate key,
then C is not a super key.

Relation 2: {A, E, F, G}. Dependencies: A \rightarrow E, E \rightarrow F, F

\rightarrow G. PK: A. E \rightarrow F and F \rightarrow G are problematic.

Relation 3: {D, B}. Dependency: D \rightarrow B. PK: D. OK.

This suggests the material's BCNF example might be illustrating a more complex decomposition
strategy or has specific assumptions about the original table's functional dependencies. For
BCNF, the rule is strict: if X → A is a dependency, X must be a super key. Let's assume the
dependencies from the material's BCNF decomposition are the target:

A \rightarrow C

C \rightarrow D

A \rightarrow E

E \rightarrow F

F \rightarrow G

D \rightarrow B

To achieve BCNF:

1. Decompose C \rightarrow D where C is not a super key:

R1 = {A, C, E} (PK: A) - Dependencies: A \rightarrow C, A \rightarrow E. A is super

key.

R2 = {C, D} (PK: C) - Dependency: C \rightarrow D. C is super key.

2. Decompose E \rightarrow F where E is not a super key. This dependency is within the
context of relation R1's decomposition. Let's assume A is the only candidate key for the
original relation.

We need to handle E \rightarrow F and F \rightarrow G.

R3 = {A, E} (PK: A) - Dependency A \rightarrow E.

R4 = {E, F} (PK: E) - Dependency E \rightarrow F.

R5 = {F, G} (PK: F) - Dependency F \rightarrow G.

3. Decompose D \rightarrow B where D is not a super key.

R6 = {D, B} (PK: D) - Dependency D \rightarrow B.

Combining these to match the material's BCNF example: A, C, D (This implies A \rightarrow
C and C \rightarrow D. For BCNF, C must be a super key for C \rightarrow D. If PK is A, this is
not BCNF.) A, E, F, G (This implies A \rightarrow E, E \rightarrow F, F \rightarrow G.

[Link] 7/8
2/15/26, 12:16 AM Thea

For BCNF, E must be a super key for E \rightarrow F, and F must be a super key for F 1

\rightarrow G. If PK is A, this is not BCNF.) D, B (This implies D \rightarrow B. D must be a  

super key. If D is PK, this is BCNF.) The material's example for BCNF seems to be a direct
decomposition based on identifying functional dependencies and ensuring the determinant is a
super key. The example might be illustrating that if A is the primary key, then for the
dependency C \rightarrow D, C is not a super key, thus requiring decomposition. The resulting
Materials Study Guide Smart Study Flashcards Test
relations are then checked for BCNF.

© Theaai

[Link] 8/8