Class 10 | Relational Database Design | DBMS Notes

Functional Dependency
In a relational database management, functional dependency is a concept that specifies the relationship between two sets of attributes where one attribute determines the value of another attribute. It is denoted as X → Y, where the attribute set on the left side of the arrow, X is called Determinant, and Y is called the Dependent.
Properties of functional dependencies:
i) Reflexivity: If Y is a subset of X, then X→Y holds by reflexivity rule
 Example, {roll_no, name} → name is valid.

ii) Augmentation: If X → Y is a valid dependency, then XZ → YZ is also valid by the augmentation rule.
Example, {roll_no, name} → dept_building is valid, hence {roll_no, name, dept_name} → {dept_building, dept_name}             is also valid.

iii) Transitivity: If X → Y and Y → Z are both valid dependencies, then X→Z is also valid by the Transitivity rule.
Example, roll_no → dept_name&dept_name → dept_building, then roll_no → dept_building is also valid.

Types of Functional Dependencies in DBMS:
i) Trivial Functional Dependency: In Trivial Functional Dependency, a dependent is always a subset of the determinant. i.e. If X → Y and Y is the subset of X, then it is called trivial functional dependency.
Example:Here, {roll_no, name} → name is a trivial functional dependency, since the dependent name is a subset of determinant set {roll_no, name}.
Similarly, roll_no → roll_no is also an example of trivial functional dependency.

1. 
   ii) Non-trivial Functional Dependency: In Non-trivial functional dependency, the dependent is strictly not a subset of the determinant. i.e. If X → Y and Y is not a subset of X, then it is called Non-trivial functional dependency.
   Example: Here, roll_no → name is a non-trivial functional dependency, since the dependent name is not a subset of determinant roll_no. Similarly, {roll_no, name} → age is also a non-trivial functional dependency, since age is not a subset of {roll_no, name}.

iii)  Multivalued Functional Dependency: In Multivalued functional dependency, entities of the dependent set are not dependent on each other. i.e. If a → {b, c} and there exists no functional dependency between b and c, then it is called a multivalued functional dependency.

For example: Here, roll_no → {name, age} is a multivalued functional dependency, since the dependents name & age are not dependent on each other(i.e. name → age or age → name doesn’t exist!)

1. iv) Transitive Functional Dependency: In transitive functional dependency, dependent is indirectly dependent on determinant. i.e. If a → b & b → c, then according to axiom of transitivity, a → c. This is a transitive functional dependency.
   For Example:

Here, enrol_no → dept and dept → building_no. Hence, according to the axiom of transitivity, enrol_no→ building_no is a valid functional dependency. This is an indirect functional dependency, hence called Transitive functional dependency.

v) Fully Functional Dependency: In full functional dependency an attribute or a set of attributes uniquely determines another attribute or set of attributes. If a relation R has attributes X, Y, Z with the dependencies X->Y and X->Z which states that those dependencies are fully functional.

vi) Partial Functional Dependency: In partial functional dependency a non-key attribute depends on a part of the composite key, rather than the whole key. If a relation R has attributes X, Y, Z where X and Y are the composite key and Z is non-key attribute. Then X->Z is a partial functional dependency in RBDMS.

Normalization:
Normalization is the process of minimizing redundancy from a relation or set of relations. Redundancy in relation may cause insertion, deletion, and update anomalies. So, it helps to minimize the redundancy in relations. Normal forms are used to eliminate or reduce redundancy in database tables.

Advantages of Normal Form
i) Reduced data redundancy: Normalization helps to eliminate duplicate data in tables, reducing the amount of storage space needed and improving database efficiency.
ii) Improved data consistency: Normalization ensures that data is stored in a consistent and organized manner, reducing the risk of data inconsistencies and errors.
iii) Simplified database design: Normalization provides guidelines for organizing tables and data relationships, making it easier to design and maintain a database.
iv) Improved query performance: Normalized tables are typically easier to search and retrieve data from, resulting in faster query performance.
v) Easier database maintenance: Normalization reduces the complexity of a database by breaking it down into smaller, more manageable tables, making it easier to add, modify, and delete data.

Applications of Normal Forms in DBMS
i) Data consistency: Normal forms ensure that data is consistent and does not contain any redundant information. This helps to prevent inconsistencies and errors in the database.
ii) Data redundancy: Normal forms minimize data redundancy by organizing data into tables that contain only unique data. This reduces the amount of storage space required for the database and makes it easier to manage.
iii) Response time: Normal forms can improve query performance by reducing the number of joins required to retrieve data. This helps to speed up query processing and improve overall system performance.
iv) Database maintenance: Normal forms make it easier to maintain the database by reducing the amount of redundant data that needs to be updated, deleted, or modified. This helps to improve database management and reduce the risk of errors or inconsistencies.
v) Database design: Normal forms provide guidelines for designing databases that are efficient, flexible, and scalable. This helps to ensure that the database can be easily modified, updated, or expanded as needed.

Levels of Normalization
There are various levels of normalization. Some of them are:
 i) First Normal Form
If a relation contains a composite or multi-valued attribute, it violates the first normal form, or the relation is in the first normal form if it does not contain any composite or multi-valued attribute. A relation is in first normal form if every attribute in that relation is single-valued attribute.
A table is in 1 NF if: 
i) There are only Single Valued Attributes.
ii) Attribute Domain does not change.
iii) There is a unique name for every Attribute/Column.
iv) The order in which data is stored does not matter.
Example:

                            Table 1

                                 Table 2

ii) Second Normal Form
The second Normal Form (2NF) is based on the concept of fully functional dependency. The second Normal Form applies to relations with composite keys, that is, relations with a primary key composed of two or more attributes. A relation with a single-attribute primary key is automatically in at least 2NF. A relation that is not in 2NF may suffer from the update anomalies. To be in the second normal form, a relation must be in the first normal form and the relation must not contain any partial dependency. A relation is in 2NF if it has No Partial Dependency, i.e., no non-prime attribute (attributes that are not part of any candidate key) is dependent on any proper subset of any candidate key of the table.

Advantages of 2NF:
1) Redundant data is reduced more effectively
2) Data is consistent in the database
3) It improves flexibility in designing of database
4) It also improves overall organization of data in the database
5) It also improve security of the database.

Example:

The table above is not in 2NF.
In this table, you can note that many subjects come with the same subject fee. Three things are happening here:
i) The SUBJECT_FEE won’t be able to determine the values of SUBJECT_ID or SUBJECT_NO alone;
ii) The SUBJECT_FEE along with SUBJECT_ID won’t be able to determine the values of SUBJECT_NO;
iii)The SUBJECT_FEE along with SUBJECT_NO won’t be able to determine the values of SUBJECT_ID;
To convert above relation in 2NF we split the relation into two relations:

Relation 1: SUBJECT_ID and SUBJECT_NO


Relation 2: SUBJECT_NO and SUBJECT_FEE

Now, the tables are in the 2NF.

iii) Third Normal Form
Although Second Normal Form (2NF) relations have less redundancy than those in 1NF, they may still suffer from update anomalies. If we update only one tuple and not the other, the database will be in an inconsistent state. This update anomaly is caused by a transitive dependency. We need to remove such dependencies by progressing to the Third Normal Form (3NF).
Example of Transitivity:
 If A→B and B→C are two functional dependencies, then A→C  also holds true ant it is called transitive dependency.

Advantages of 3NF:
i) Update anomalies can be removed
ii) Less redundancy than 1NF and 2NF
iii) Maintain Integrity and Consistency
iv) Result in smaller data base which is easier to understand and manage
v) Free of anomalies so it provide sufficient criteria to establish database