Functional Dependency

In a relational database management system, a Functional Dependency is a concept that describes the relationship between two sets of attributes where one attribute determines the value of another attribute.

It is represented as:

X → Y

Where:

• X is called the Determinant.
• Y is called the Dependent.

Properties of Functional Dependencies

The main properties of functional dependencies are:

1. Reflexivity
2. Augmentation
3. Transitivity

1. Reflexivity

If Y is a subset of X, then:

X → Y

holds according to the reflexivity rule.

Example:

{roll_no, name} → name

This dependency is valid because name is a part of {roll_no, name}.

2. Augmentation

If:

X → Y

is a valid dependency, then:

XZ → YZ

is also valid according to the augmentation rule.

Example:

If:

{roll_no, name} → dept_building

is valid, then:

{roll_no, name, dept_name} → {dept_building, dept_name}

is also valid.

3. Transitivity

If:

X → Y

and:

Y → Z

are both valid dependencies, then:

X → Z

is also valid according to the transitivity rule.

Example:

roll_no → dept_name

and:

dept_name → dept_building

Therefore:

roll_no → dept_building

Types of Functional Dependencies in DBMS

The main types of functional dependencies discussed in the notes are:

1. Trivial Functional Dependency
2. Non-Trivial Functional Dependency
3. Multivalued Functional Dependency
4. Transitive Functional Dependency
5. Fully Functional Dependency
6. Partial Functional Dependency

1. Trivial Functional Dependency

In a Trivial Functional Dependency, the dependent is a subset of the determinant.

If:

X → Y

and Y is a subset of X, then it is called a trivial functional dependency.

Example:

{roll_no, name} → name

Here, name is a subset of {roll_no, name}.

Another example is:

roll_no → roll_no

2. Non-Trivial Functional Dependency

In a Non-Trivial Functional Dependency, the dependent is not a subset of the determinant.

If:

X → Y

and Y is not a subset of X, then it is called a non-trivial functional dependency.

Example:

roll_no → name

Here, name is not a subset of roll_no.

Another example is:

{roll_no, name} → age

Here, age is not a subset of {roll_no, name}.

3. Multivalued Functional Dependency

In a Multivalued Functional Dependency, the entities of the dependent set are not dependent on one another.

If:

a → {b, c}

and there is no functional dependency between b and c, then it is called a multivalued functional dependency.

Example:

roll_no → {name, age}

According to the notes, name and age are not dependent on each other.

That means:

name → age

or:

age → name

does not exist.

4. Transitive Functional Dependency

In a Transitive Functional Dependency, the dependent attribute is indirectly dependent on the determinant.

If:

a → b

and:

b → c

then according to the transitivity rule:

a → c

This is called a transitive functional dependency.

Example:

enrol_no → dept

and:

dept → building_no

Therefore:

enrol_no → building_no

This is an indirect dependency and is therefore called a transitive functional dependency.

5. Fully Functional Dependency

In a Fully Functional Dependency, an attribute or a set of attributes uniquely determines another attribute or set of attributes.

If a relation R contains attributes X, Y, and Z with dependencies:

X → Y

and:

X → Z

then, according to the provided notes, these dependencies are fully functional.

6. Partial Functional Dependency

In a Partial Functional Dependency, a non-key attribute depends on a part of a composite key rather than on the complete composite key.

Suppose a relation R contains attributes:

• X
• Y
• Z

If X and Y together form the composite key and Z is a non-key attribute, then:

X → Z

represents a partial functional dependency.

Normalization

Normalization is the process of minimizing redundancy from a relation or a set of relations.

Redundancy in a database may cause:

• Insertion anomalies
• Deletion anomalies
• Update anomalies

Normalization helps reduce redundancy in database relations.

Normal Forms are used to eliminate or reduce redundant data in database tables.

Advantages of Normalization

The main advantages of normalization include:

1. Reduced Data Redundancy

Normalization helps eliminate duplicate data in tables.

This:

• Reduces storage requirements
• Improves database efficiency

2. Improved Data Consistency

Normalization ensures that data is stored in a consistent and organized manner.

This reduces the risk of:

• Data inconsistency
• Data errors

3. Simplified Database Design

Normalization provides guidelines for organizing tables and relationships.

This makes databases easier to:

• Design
• Understand
• Maintain

4. Improved Query Performance

According to the provided notes, normalized tables can make data easier to search and retrieve.

This may improve query performance.

5. Easier Database Maintenance

Normalization divides a database into smaller and more manageable tables.

This can make it easier to:

• Add data
• Modify data
• Delete data
• Maintain the database

Applications of Normal Forms in DBMS

Normal forms are useful in several areas of database management.

1. Data Consistency

Normal forms help ensure that data remains consistent and does not unnecessarily contain duplicate information.

This helps prevent errors and inconsistencies.

2. Data Redundancy

Normal forms minimize redundant data by organizing information into tables containing unique data.

This can:

• Reduce storage requirements
• Make data easier to manage

3. Response Time

According to the provided notes, normal forms can improve query performance by reducing the number of joins required to retrieve data.

This may improve overall system performance.

4. Database Maintenance

Normal forms make databases easier to maintain by reducing redundant information that may otherwise need repeated:

• Updating
• Deleting
• Modifying

5. Database Design

Normal forms provide guidelines for designing databases that are:

• Efficient
• Flexible
• Scalable

This can make databases easier to modify, update, and expand.

Levels of Normalization

There are various levels of normalization.

The notes discuss:

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

First Normal Form (1NF)

A relation violates the First Normal Form (1NF) if it contains:

• Composite attributes
• Multi-valued attributes

A relation is in First Normal Form if each attribute contains only a single value.

Conditions for 1NF

A table is in 1NF if:

1. There are only single-valued attributes.
2. The attribute domain does not change.
3. Every attribute or column has a unique name.
4. The order in which data is stored does not matter.

Example of 1NF

Roll_No	Name	Course
1	Nexu Corner	c/ C++
2	Nepal Enotes	Java
3	Nexus	Python/ DBMS

Table 1

The source also provides the following table:

SUBJECT_NO	SUBJECT_FEE	SUBJECT_NO
S1	1000	S1
S2	1500	S2
S3	1000	S3
S4	2000	S4
S5	2000	S5

Table 2

Second Normal Form (2NF)

The Second Normal Form (2NF) is based on the concept of fully functional dependency.

2NF mainly applies to relations containing composite keys.

A composite key is a primary key made up of two or more attributes.

According to the notes, a relation with a single-attribute primary key is automatically in at least 2NF.

A relation that is not in 2NF may suffer from update anomalies.

Conditions for 2NF

For a relation to be in Second Normal Form:

1. It must already be in First Normal Form.
2. It must not contain any partial dependency.

A relation is in 2NF if no non-prime attribute depends on only a proper subset of a candidate key.

Advantages of 2NF

The advantages of 2NF include:

1. Redundant data is reduced more effectively.
2. Data remains more consistent.
3. It improves flexibility in database design.
4. It improves the overall organization of data.
5. It can improve database security.

Example of 2NF

According to the source, the given table is not in 2NF.

The notes explain that many subjects have the same subject fee.

The following conditions are described:

1. SUBJECT_FEE cannot determine the values of SUBJECT_ID or SUBJECT_NO by itself.
2. SUBJECT_FEE along with SUBJECT_ID cannot determine SUBJECT_NO.
3. SUBJECT_FEE along with SUBJECT_NO cannot determine SUBJECT_ID.

To convert the relation into 2NF, the relation is divided into two relations.

Relation 1

SUBJECT_ID and SUBJECT_NO

Relation 2

SUBJECT_NO and SUBJECT_FEE

After decomposition, according to the provided notes, the tables are in 2NF.

Third Normal Form (3NF)

Although Second Normal Form reduces redundancy compared with 1NF, relations may still suffer from update anomalies.

These problems can occur due to Transitive Dependency.

To remove transitive dependencies, a relation is converted into Third Normal Form (3NF).

Transitive Dependency

If:

A → B

and:

B → C

then:

A → C

also holds.

This is called a Transitive Dependency.

Advantages of 3NF

The advantages of Third Normal Form include:

1. Update anomalies can be removed.
2. It has less redundancy compared with 1NF and 2NF.
3. It helps maintain data integrity and consistency.
4. It can result in a smaller database that is easier to understand and manage.
5. It is free from many common anomalies and provides useful criteria for establishing a database.