A candidate key is a minimal set of attributes that uniquely identifies each tuple within a

table. In other words, there should not be any two rows in a table that can have the same
values for the columns that are the part of candidate key. Candidate key is a subset of Super
key. It is very important for establishing relationships between tables and maintaining data
integrity. Candidate keys play a pivotal role in database normalization as they help us to
eliminate data redundancy and anomalies.

Key Features of Candidate Key in DBMS

Some key features of Candidate Key are:

 A candidate key has unique values for every row in a table.

 Every candidate key is a super key, but not all super keys qualify as candidate keys.
Simply put, a candidate key is a minimal super key.

 A candidate key can contain null values, but this depends on the database's design
and constraints.

 It should not have redundant attributes, meaning it must be as simple as possible to

uniquely identify a row.

 Using a candidate key, we can uniquely determine the values of all other attributes in
a table.

 The primary key is chosen from the set of candidate keys.

 Candidate keys help us classify table attributes into:

o Prime Attributes: Attributes that form part of the candidate key.

o Non-Prime Attributes: Attributes not included in any candidate key.

 Candidate keys ensure data integrity and prevent duplication in the table.
  In a multi-table database, a foreign key in one table often refers to a candidate key
(usually the primary key) in another table to establish relationships.

 Accessing rows in a table is faster when using a primary key, which is a type of
candidate key.

Example of Candidate Key

Let's try to understand, the concept of the candidate key with an example of a student table.

Candidate Key

Here’s how the candidate keys are identified from the table:

1. Roll No.:

 Each Roll No. is unique for every student.

 It can uniquely identify each row in the table.

 Hence, Roll No. is a candidate key.

2. Phone:

 Each Phone number is also unique for every student.

 It can also uniquely identify each row in the table.

 Hence, Phone is another candidate key.

How to Identify a Candidate Key in a Database Table?

There are several methods to identify a candidate key in a database table:

Method 1: Checking Unique Columns or Sets of Columns

This method involves identifying a column or a combination of columns that can uniquely
identify each row in a table. By examining the values in each column, you can determine if
they are unique across the entire table. Any column or combination of columns that meets
this criterion can be considered a candidate key.

Method 2: Using Functional Dependencies (FDs)

In this approach, the functional dependencies between attributes are used to identify
candidate keys. A functional dependency exists when one attribute determines another. For
instance, if a table has student_ID and student_name, there is a functional dependency
where student_ID determines student_name. The candidate key is found by identifying the
smallest set of attributes that can determine all other attributes in the table.

Method 3: Using Normal Forms

This method leverages the guidelines provided by database normalization (1NF, 2NF, 3NF,
etc.) to identify candidate keys. For example, in a table that adheres to the 2nd normal form
(2NF), a primary key is required, which is also a candidate key. By ensuring the table follows
a specific normal form, you can pinpoint the candidate key.

Why do we Need a Candidate Key?

Candidate keys are needed to maintain data accuracy and uniqueness in a database.
Without them, it would be difficult to ensure that each row in a table is unique, which could
lead to problems like data duplication and inconsistency.

By defining one or more candidate keys for a table, database administrators can ensure that
every row has unique values for the selected attributes. This helps avoid data redundancy
and prevents issues like insertion, deletion, or update errors. If a table has multiple
candidate keys, one of them is typically chosen as the primary key, while the others are
referred to as alternate keys.

Role of Candidate key in DBMS

Identifying Prime Attributes:

 Prime attributes are those that are part of any candidate key.

 Non-prime attributes are not part of any candidate key.

 This distinction is useful for identifying dependencies and normalizing the database.

Data Anomaly Prevention:

 Insertion Anomaly: Prevents inserting duplicate records by enforcing unique values.

 Deletion Anomaly: Ensures that removing one record does not accidentally affect
another.

 Update Anomaly: Avoids inconsistencies during updates since each record is

uniquely identified.

Facilitating Efficient Data Retrieval:

  Searching, sorting, and joining operations are optimized by using candidate keys,
especially when a primary key is chosen from them.

DIFFERENCE BETWEEN SUPER KEY AND CANDIDATE

KEY

Super Key Candidate Key

Super Key is an attribute (or set of

Candidate Key is a subset of
attributes) that is used to uniquely
a super key.
identifies all attributes in a relation.

All super keys can't be candidate But all candidate keys are
keys. super keys.

Various super keys together makes Various candidate keys

the criteria to select the candidate together makes the criteria
keys. to select the primary keys.

In a relation, number of super keys While in a relation, number

is more than number of candidate of candidate keys are less
keys. than number of super keys.

Super key attributes can contain Candidate key attributes can

NULL values. also contain NULL values.

 Conclusion
 A super key is the basic key in a database table. Candidate
keys play a main role in identifying the most important key
known as the primary key. Super keys provide the
foundation for selecting the candidate keys while candidate
keys in turn serve as the basis for choosing the primary key
of the relation. This relationship between these different
types of keys helps establish the structure and organization
of the data within the database table.
A Prime attribute is any column (attribute) in a database relation that is part of at least one
candidate key. These attributes are essential for uniquely identifying rows (tuples) in a table
and are used in normalization to prevent redundancy. Attributes not part of any candidate
key are called non-prime attribute.

Example:
Consider a STUDENT table with attributes: (Student ID, Email, Name, Age).

 Candidate Keys: Assume Student ID can uniquely identify a student, and Email can
also uniquely identify a student.

 Candidate Key 1: {Student ID}

 Candidate Key 2: {Email}

 Prime Attributes: Student ID, Email (because they are part of candidate keys).

 Non-Prime Attributes: Name, Age (because they are not part of any candidate
key).

Key points :

 If a candidate key is composite, e.g., {Course_ID, Student_ID}, then

both Course_ID and Student_ID are prime attributes.

 An attribute can be prime even if it is only part of one candidate key, not all of
them.

What is Normalization in DBMS?

Normalization is a process in Database Management Systems (DBMS) used to organize
data in tables so that data redundancy is reduced and data integrity is improved.

It divides a large table into smaller related tables and establishes relationships between
them using keys.

Definition:
Normalization is the process of structuring a database to eliminate data redundancy, avoid
anomalies, and ensure data consistency.

Normalization is based on normal forms such as:

 1NF (First Normal Form)

  2NF (Second Normal Form)

 3NF (Third Normal Form)

 BCNF (Boyce-Codd Normal Form)

2. Need of Normalization in DBMS

Normalization is required to solve problems that occur in poorly designed databases.

1. Reduce Data Redundancy

Redundancy means duplicate data stored multiple times.

Example (Unnormalized Table)

Student_ID Student_Name Course Teacher

101 Ravi DBMS Sharma

102 Anil DBMS Sharma

103 Pooja DBMS Sharma

Here Teacher name “Sharma” is repeated many times.

Normalization stores it only once in a separate table.

2. Remove Update Anomaly

If the teacher name changes, we must update many rows, which may create inconsistency.

Example:

Teacher "Sharma" becomes "Dr. Sharma"

All rows must be updated.

Normalization avoids this.

3. Remove Insert Anomaly

Sometimes we cannot insert data without other information.

Example:

If no student is enrolled yet, we cannot insert a new course and teacher in the table.

Normalization solves this by separating tables.

4. Remove Delete Anomaly

Deleting a record may cause loss of useful information.

Example:

If the last student of DBMS course is deleted, the teacher information may also be lost.
Normalization prevents this.

3. Example of Normalization

Unnormalized Table

Student_ID Student_Name Course Teacher

101 Ravi DBMS Sharma

102 Anil DBMS Sharma

103 Pooja AI Verma

Problems:

 Teacher name repeated

 Data redundancy
 Update anomalies
After Normalization
Student Table

Student_ID Student_Name Course_ID

101 Ravi C1

102 Anil C1

103 Pooja C2
Course Table

Course_ID Course_Name Teacher

C1 DBMS Sharma
C2 AI Verma

Now:
 Data duplication reduced
 Data easier to update
 Database more efficient
4. Advantages of Normalization
1. Reduces data redundancy
2. Eliminates update, insert, and delete anomalies
3. Improves data consistency
4. Saves storage space
5. Makes database easier to maintain
Summary

Normalization is a database design technique used to structure data efficiently by dividing

large tables into smaller tables and linking them through keys.

It is needed to:
 Reduce redundancy
 Avoid anomalies
 Improve data integrity
 Make databases more efficient and maintainable

First Normal Form (1NF)

First Normal Form (1NF) is the foundational level of database normalization. A table is in
1NF if every column contains only atomic (single, indivisible) values and there are no
repeating groups of data.

Key Rules of 1NF

To comply with 1NF, a database table must follow these rules:

 Atomicity: Each cell must hold exactly one value. It cannot contain a list, set, or
array of multiple values.

 No Repeating Groups: You cannot have multiple columns that store similar types of
data for the same record (e.g., Phone1, Phone2, Phone3).

 Unique Column Names: Every column must have a unique name.

 Unique Records: Every row must be uniquely identifiable, typically through

a Primary Key.

 Consistent Data Types: All values in a column must be of the same data type (e.g.,
you cannot mix names in a column meant for dates).

Examples of 1NF Violations and Corrections

1. Multi-Valued Attributes (Non-Atomic Values)

In this scenario, a single cell contains multiple values separated by commas.

Violation (Not in 1NF):
The Courses column contains multiple values, making it difficult to search or update
individual

courses.

Student ID Name Courses

1 Alice Math, Science

2 Bob History

Correction (1NF Compliant):

Split the multi-valued data into separate rows. Every cell now contains only one value.

Student ID Name Course

1 Alice Math

1 Alice Science

2 Bob History

2. Repeating Groups
In this scenario, multiple columns are created to store the same type of information for
one record.

Violation (Not in 1NF)

The table uses Phone 1 and Phone 2 columns. This structure is rigid; adding a third phone
number would require changing the entire table schema.

Employee ID Name Phone 1 Phone 2

101 Ravi 555-0101 555-0102

102 Sarah 555-0201

Correction (1NF Compliant):

Move the repeating information into a separate table or separate rows to ensure each
attribute is represented uniquely.
Employee ID Name Phone Number

101 Ravi 555-0101

101 Ravi 555-0102

102 Sarah 555-0201

Why 1NF Matters

 Simpler Queries: It is easier to write SQL queries to find specific data when each
cell has only one value.

 Data Integrity: It prevents "update anomalies" where you might update one phone
number but miss another hidden in a list.

 Scalability: New data points can be added as new rows without altering the table's
structure.

Second Normal Form (2NF)

Second Normal Form (2NF) is all about removing redundancy that occurs when a table
has a "composite primary key" (a key made of two or more columns).

To be in 2NF, a table must meet two conditions:

1. It must be in 1NF (no multi-valued cells).

2. No Partial Dependency: Every non-key column must depend on the entire primary
key, not just a part of it.

The "Partial Dependency" Problem

Imagine a school database where the primary key is a combination of Student

ID and Subject ID.

Violation (1NF but NOT 2NF):

In this table, the primary key is {Student ID + Subject ID}.

Student ID (PK) Subject ID (PK) Teacher Name Score

101 BIO Dr. Smith 85

101 CHM Dr. Jones 90

102 BIO Dr. Smith 78

The Issue: The Score depends on both the student and the subject (Full Dependency).
However, the Teacher Name depends only on the Subject ID. Dr. Smith teaches Biology
regardless of which student is in the row.

The Result: If 50 students take Biology, "Dr. Smith" is typed 50 times. This is a Partial
Dependency.

The Solution (2NF Compliant)

To fix this, we break the table into two separate tables so that every column
depends on its whole key.
Table 1: Exams (Focuses on the student's performance)
Primary Key: {Student ID, Subject ID}

Student ID Subject ID Score

101 BIO 85

101 CHM 90

102 BIO 78

Table 2: Subjects (Focuses on subject details)

Primary Key: Subject ID

Subject ID Teacher Name

BIO Dr. Smith

CHM Dr. Jones

Why do this?
  Update Accuracy: If Dr. Smith retires and Dr. Miller takes over Biology, you only
change one row in the Subjects table instead of 50 rows in the Exams table.

 Data Integrity: You eliminate the risk of having "Dr. Smith" for one Biology student
and a typo like "Dr. Smyth" for another.

Third Normal Form (3NF)

Rule of 3NF

A table is in 3NF if:

1. It is already in 2NF

2. No transitive dependency

Transitive dependency means:

A non-key attribute depends on another non-key attribute.

Third Normal Form (3NF) is a stage in database normalization that ensures data integrity
and reduces redundancy. A table is in 3NF if it meets two requirements:

1. It is in Second Normal Form (2NF).

2. No Transitive Dependencies exist.

Transitive Dependency occurs when a non-key attribute depends on another non-key

attribute rather than directly on the primary key (i.e., if A -> B and B-> C, then A -> C is a
transitive dependency).

Example of 3NF (Student Table)

Imagine a school system storing student information.

1. The Unnormalized Table (Student_Data)

This table is in 2NF because RollNo acts as the primary key.

RollNo Name Dept Dept_Head

101 Adam CS Dr. Smith

102 Eva ME Dr. Jones

103 John CS Dr. Smith

2. Analyze Functional Dependencies

 RollNo -> Name (RollNo determines Name)

  RollNo -> Dept (RollNo determines Dept)

 Dept -> Dept_Head (Dept determines Department Head)

Transitive Dependency found:

RollNo -> Dept -> Dept_Head.

Dept_Head is not directly dependent on the RollNo (primary key); it depends on

the Department.

3. Identify 3NF Anomalies

 Update Anomaly: If "CS" department changes its head from "Dr. Smith" to "Dr.
Lee," we must update multiple rows, risking data inconsistency.

 Insertion Anomaly: We cannot add a new department (e.g., "Civil") with its head
until a student joins that department.

4. Transformation to 3NF

To convert to 3NF, we must remove the transitive dependency (Dept-> Dept_Head) and
place it in a separate table.

Step A: Create Table 1 - Student (Non-transitive data)

Remove Dept_Head from the main student table.

RollNo (PK) Name Dept (FK)

101 Adam CS

102 Eva ME

103 John CS

Step B: Create Table 2 - Department (Transitive dependency)

Create a new table for the Dept -> Dept_Head relationship.

Dept (PK) Dept_Head

CS Dr. Smith

ME Dr. Jones

Final 3NF Structure

Now, all non-key attributes are dependent only on the primary key, achieving 3NF.
 1. Student Table: (RollNo -> Name, Dept)

2. Department Table: (Dept-> Dept_Head)

Advantages of this Structure

 Reduced Redundancy: "Dr. Smith" is stored only once.

 Data Integrity: Updating the Department Head happens in only one place.

 Flexibility: We can add new departments without needing students

Comparison of Normal Forms (1NF, 2NF, 3NF)

Feature 1NF (First Normal 2NF (Second Normal Form) 3NF (Third Normal
Form) Form)

Prerequisite None (Raw data) Must be in 1NF Must be in 2NF

Core Atomic values (No No partial functional No transitive

Requirement sets/repeating dependencies dependencies
groups)

Dependency Each cell contains a Non-key attributes must Non-key attributes must
single value depend on the whole key depend only on the key

What it Repeating Partial dependencies Transitive dependencies

Removes groups/arrays (dependency on part of a (key ->A -> B-> C))
composite key)

Primary Goal Data Atomicity Reduce Data Redundancy Ensure Referential

Integrity

Example (ID, Name, Subjects) (StudentID, CourseID, (Student_ID, Grade,

Grade) CourseName)

Summary of Key Differences

 1NF: Ensures each column holds only one value (no comma-separated lists), and
every row has a unique identifier.

 2NF: Requires 1NF, and that any table with a composite key must have non-key
columns depending on the entire key, not just part of it.

 3NF: Requires 2NF, and that no non-key column depends on another non-key
column (no transitive dependency).
BCNF

Boyce-Codd Normal Form (BCNF) is an advanced step in the normalization process, and it's
a stronger version of the Third Normal Form (3NF). In fact, every relation in BCNF is also in
3NF, but the converse isn't necessarily true. BCNF was introduced to handle certain
anomalies that 3NF does not deal with.

A relation is in BCNF if:

1. It is already in 3NF.

2. For every non-trivial functional dependency X ->Y, X is a superkey. This essentially

means that the only determinants in the relation are superkeys.

Here, "non-trivial" means that Y is not a subset of X, and a "superkey" is a set of attributes
that functionally determines all other attributes in the relation.

The determinant (left side of FD) must uniquely identify all attributes of the table.

Example for Boyce-Codd Normal Form (BCNF)

Explanation of BCNF with Example

Step 1: Consider a Relation

Suppose we have a table:

R(Student, Course, Instructor)

Student Course Instructor

A DBMS Ram

B DBMS Ram

C OS Shyam

D OS Shyam

Step 2: Identify Functional Dependencies

From the data we observe:

1. Student, Course → Instructor

2. Course → Instructor

Explanation:

 A course is always taught by the same instructor.

  Therefore Course determines Instructor.

Step 3: Find Candidate Key

To uniquely identify each record:

Student + Course

So the Candidate Key = (Student, Course)

Step 4: Check BCNF Condition

BCNF rule:

Determinant must be a Super Key

Check dependencies:

FD1

(Student, Course) → Instructor

✔ Determinant is candidate key → Valid

FD2

Course → Instructor

❌ Determinant Course is NOT a super key

Therefore:

The table is NOT in BCNF.

Step 5: Decompose the Relation

We split the table into two relations.

Table 1: Course_Instructor

Course Instructor

DBMS Ram

OS Shyam

Functional Dependency:
Course → Instructor

Here Course is the key.

Table 2: Student_Course

Student Course
A DBMS
B DBMS
C OS
D OS
Key:

(Student, Course)

Step 6: Verify BCNF

Relation 1

Course → Instructor

Course is a key ✔

So BCNF satisfied

Relation 2

(Student, Course) is key

No partial dependency ✔

So BCNF satisfied

Final BCNF Tables

R1 (Course, Instructor)
R2 (Student, Course)

Both relations are now in BCNF.

COMPARISON TABLE :
Basis 3NF BCNF
Removes transitive
Definition Stronger version of 3NF
dependency
X is super key OR Y is prime
Condition X must be super key
attribute
Strictness Less strict More strict
Redundancy Some redundancy may remain Removes more redundancy
Dependency May not always preserve
Usually preserved
preservation dependencies
Normalization level Third level Higher than 3NF