Prepared by Prabesh Bhandari (For 3rd Sem BIT, TU)

Unit – 5 Relational Database Design

1. What are the benefits of using normalization? Discuss 1NF, 2NF, and 3NF with suitable example.
Data normalization is the process of reorganizing data within a database so that users can utilize it for
further queries and analysis. Simply put, it is the process of developing clean data. This includes
eliminating redundant and unstructured data and making the data appear similar across all records and
fields.
Benefits of using normalization:
- Utilizing database or data redundancy through normalization
- Duplication may be eliminated.
- By normalizing, we may reduce null values.
- Results in a smaller database (since there is less data duplication or zero).
- Minimize/avoid issues with data modification.
- It makes the queries easier.

a) 1NF - First Normal Form:

The most basic form of data normalization is 1NF which ensures there are no two same entries in a group.
For a table to be in the first normal form, it should satisfy the following rules:
• Each cell should contain a single value
• Each record should be unique
The table in 1NF will look like this:

Salutation Full Name Address Skills

Mr. John Denver 12, Bates Brothers Road Content writing

Mr. John Denver 12, Bates Brothers Road Social media marketing

Ms. Mary Ann 34, Shadowman Drive Machine Learning

Ms. Mary Ann 34, Shadowman Drive Data science

 Prepared by Prabesh Bhandari (For 3rd Sem BIT, TU)

Ms. Nancy Drew 4, First Plot Street DBMS

b) 2NF - Second Normal Form:

In a 2NF table, all the subsets of data that can be placed in multiple rows are placed in separate tables.
For a table to be in the second normal form, it should satisfy the following rules:

• It should be in 1NF
• The primary key should not be functionally dependant on any subset of candidate key

Let’s divide the 1NF table into two tables - Table 1 and Table 2. Table 1 contains all the employee
information. Table 2 contains information on their key skills.

Table 1

Employee ID Salutation Full Name Address

1 Mr. John Denver 12, Bates Brothers Road

2 Ms. Mary Ann 34, Shadowman Drive

3 Ms. Nancy Drew 4, First Plot Street

Table 2

Employee ID Key skills

1 Content marketing

1 Social media marketing

2 Machine learning
 Prepared by Prabesh Bhandari (For 3rd Sem BIT, TU)

2 Data science

3 DBMS

We have introduced a new column called Employee ID which is the primary key for Table 1. The records
can be uniquely identified using this primary key.
In Table 2, Employee ID is the foreign key.

3NF - Third Normal Form:

For a table to be in the third normal form, it should satisfy the following rules:

• It should be in 2NF
• It should not have any transitive functional dependencies
A transitive functional dependency is when a change in a column (which is not a primary key) may cause
any of the other columns to change.
In our example, if there is a name change (male to female), there may be a change in the salutation (Mr.,
Ms., Mrs., etc.). Hence we will introduce a new table that stores the salutations
Table 1

Employee ID Full Name Address Salutation

1 John Denver 12, Bates Brothers Road 1

2 Mary Ann 34, Shadowman Drive 2

3 Nancy Drew 4, First Plot Street 2

 Prepared by Prabesh Bhandari (For 3rd Sem BIT, TU)

Table 2

Employee ID Key skills

1 Content marketing

1 Social media marketing

2 Machine learning

2 Data science

3 DBMS

Table 3

Salutation ID Salutation

1 Mr.

2 Ms.

3 Mrs.

Now, there are no transitive functional dependencies and our table is now in 3F. Salutation ID is the
primary key in Table 3. Salutation ID in Table 1 is foreign to the primary key in Table 3.
 Prepared by Prabesh Bhandari (For 3rd Sem BIT, TU)

BCNF - Boyce and Codd Normal Form:

Boyce and Codd Normal Form is a higher version of 3NF and is also known as 3.5NF. A BCNF is a 3NF table
that does not have multiple overlapping candidate keys. For a table to be in BCNF, it should satisfy the
following rules:

-It should be in 3NF

-For each functional dependency ( X → Y ), X should be a super key

2. What is functional dependency? How can we use functional dependency to normalize a relation in
3NF?Discuss with suitable example.

Functional Dependency (FD) is a constraint that determines the relation of one attribute to another
attribute in a Database Management System (DBMS). Functional Dependency helps to maintain the
quality of data in the database. It plays a vital role to find the difference between good and bad database
design.
A functional dependency is denoted by an arrow “→”. The functional dependency of X on Y is represented
by X → Y.

Functional dependency can be used to normalize a relation in 3NF:

A relation is in 3NF if at least one of the following condition holds in every non-trivial function dependency
X –> Y:
-X is a super key.
-Y is a prime attribute (each element of Y is part of some candidate key).

3. With the information given below, calculate any three members of F(6)
R = (A, B, C, G, H, I)
F = {A - > B, A - > C, CG - > I, B - > H}

4. What is integrity? Explain different types of database integrity.

Data integrity is the overall accuracy, completeness, and consistency of data. Data integrity also refers to
the safety of data in regard to regulatory compliance and security. It is maintained by a collection of
processes, rules, and standards implemented during the design phase. When the integrity of data is
secure, the information stored in a database will remain complete, accurate, and reliable no matter how
long it’s stored or how often it’s accessed. Its types are as follows:

a) Physical integrity
 Prepared by Prabesh Bhandari (For 3rd Sem BIT, TU)

Physical integrity is the protection of the wholeness and accuracy of that data as it’s stored and retrieved.
When natural disasters strike, power goes out, or hackers disrupt database functions, physical integrity is
compromised. Human error, storage erosion, and a host of other issues can also make it impossible for
data processing managers, system programmers, applications programmers, and internal auditors to
obtain accurate data.

b) Logical integrity
Logical integrity keeps data unchanged as it’s used in different ways in a relational database. Logical
integrity protects data from human error and hackers as well, but in a much different way than physical
integrity does. There are four types of logical integrity:

- Entity integrity
Entity integrity relies on the creation of primary keys — the unique values that identify pieces of data —
to ensure that data isn’t listed more than once and that no field in a table is null. It’s a feature of relational
systems which store data in tables that can be linked and used in a variety of ways.

- Referential integrity
Referential integrity refers to the series of processes that make sure data is stored and used uniformly.
Rules embedded into the database’s structure about how foreign keys are used ensure that only
appropriate changes, additions, or deletions of data occur. Rules may include constraints that eliminate
the entry of duplicate data, guarantee that data entry is accurate, and/or disallow the entry of data that
doesn’t apply.

- Domain integrity
Domain integrity is the collection of processes that ensure the accuracy of each piece of data in a domain.
In this context, a domain is a set of acceptable values that a column is allowed to contain. It can include
constraints and other measures that limit the format, type, and amount of data entered.

- User-defined integrity
User-defined integrity involves the rules and constraints created by the user to fit their particular needs.
Sometimes entity, referential, and domain integrity aren’t enough to safeguard data. Often, specific
business rules must be taken into account and incorporated into data integrity measures.

5. Discuss loss-less decomposition and dependency preservation property of normalization.

Lossless Join Decomposition:

- The lossless join property is a feature of decomposition supported by normalization. It is the ability to
ensure that any instance of the original relation can be identified from corresponding instances in the
smaller relations.

R : relation, F : set of functional dependencies on R,

X,Y : decomposition of R
 Prepared by Prabesh Bhandari (For 3rd Sem BIT, TU)

- A decomposition {R1, R2, …, Rn} of a relation R is called a lossless decomposition for R if the natural join
of R1, R2, …, Rn produces exactly the relation R.

- A decomposition is lossless if we can recover:

R(A, B, C) -> Decompose -> R1(A, B) R2(A, C) -> Recover -> R’(A, B, C)

Thus,R’ = R

- Decomposition is lossles if :

X ∩ Y -> X, that is: all attributes common to both X and Y functionally determine ALL the attributes in X.

X ∩ Y -> Y, that is: all attributes common to both X and Y functionally determine ALL the attributes in Y

If X ∩ Y forms a superkey of either X or Y, the decomposition of R is a lossless decomposition.

Dependency Preserving Decomposition:

- A decomposition D = {R1, R2, ..., Rn} of R is dependency-preserving with respect to F if the union of the
projections of F on each Ri in D is equivalent to F;

if (F1∪ F2 ∪ …∪Fn)+ = F +

- Example-

R= (A, B, C )

F = {A ->B, B->C}

Key = {A}

R is not in BCNF

Decomposition R1 = (A, B), R2 = (B, C)

R1 and R2 are in BCNF, Lossless-join decomposition, Dependency preserving

- Each Functional Dependency specified in F either appears directly in one of the relations in the
decomposition.

- It is not necessary that all dependencies from the relation R appear in some relation Ri.

- It is sufficient that the union of the dependencies on all the relations Ri be equivalent to the
dependencies on R.

- is lost in the decomposition

 Prepared by Prabesh Bhandari (For 3rd Sem BIT, TU)

6. Explain Trivial functional dependency and Non-Trivial functional dependency with examples.

Trivial functional dependency:

A → B has trivial functional dependency if B is a subset of A.
The following dependencies are also trivial like: A → A, B → B

Example:
Consider a table with two columns Employee_Id and Employee_Name.
{Employee_id, Employee_Name} → Employee_Id is a trivial functional dependency as
Employee_Id is a subset of {Employee_Id, Employee_Name}.
Also, Employee_Id → Employee_Id and Employee_Name → Employee_Name are trivial dependencies
too.

Non-trivial functional dependency:

A → B has a non-trivial functional dependency if B is not a subset of A.
When A intersection B is NULL, then A → B is called as complete non-trivial.

Example:
ID → Name,
Name → DOB

7. Why do we need inference rules?

We need inference rules because of the following reasons:

- It provides a set of rules for a simple reasoning technique in functional dependencies.

- An inference rule is an assertion that can apply a user on a set of functional dependencies to derive other
FD (functional dependencies).

- Using the inference rule, we can derive additional functional dependency from the initial set.

- Used to conclude functional dependencies on a relational database.

8. When are two sets of functional dependencies equivalent? How can we determine their equivalence?
Two or more than two sets of functional dependencies are called equivalence if the right-hand side of one
set of functional dependency can be determined using the second FD set, similarly the right-hand side of
the second FD set can be determined using the first FD set.

Alternatively, we can say E is covered by F. Two sets of functional dependencies E and F are equivalent if
E+= F+. That is E is equivalent to F if E covers F and F covers E.

To determine whether F covers E, we calculate X+ with respect to F for each FD X->y in E and then check
whether X+ includes the attributes Y.
Example
 Prepared by Prabesh Bhandari (For 3rd Sem BIT, TU)

R=(A,B,C,D,E,F)
F1={A->BC, B->CDE, AE->F}
F2={A->BCF, B->DE, E->AB}
Check whether F1 and F2 are equivalent or not.

Solution
To check F1 covers F2 −
A+={A,B,C,D,E,F} contains B,C,F
B+={B,C,D,E} contains D,E
E+={E} contains A,B
So F1 does not cover F2.
Hence F1 and F2 are not equivalent.

9. Describe full and partial dependency with suitable example.

Fully Functional Dependency :

If X and Y are an attribute set of a relation, Y is fully functional dependent on X, if Y is functionally
dependent on X but not on any proper subset of X.
Example –
In the relation ABC->D, attribute D is fully functionally dependent on ABC and not on any proper subset
of ABC. That means that subsets of ABC like AB, BC, A, B, etc cannot determine D.

Partial Functional Dependency :

A functional dependency X->Y is a partial dependency if Y is functionally dependent on X and Y can be
determined by any proper subset of X.
For example, we have a relationship AC->B, A->D, and D->B.
Now if we compute the closure of {A+}=ADB
Here A is alone capable of determining B, which means B is partially dependent on AC.