DBMS Notes

mobiprep (6).png

Database Management System

mobiprep (6).png

Data Modeling

mobiprep (6).png

Database Architecture

mobiprep (6).png

Relational Model

mobiprep (6).png

Relational Algebra

mobiprep (6).png

Entity Relationship Model

mobiprep (6).png

Functional Dependencies

mobiprep (6).png

Normalization

mobiprep (6).png

Transaction And Concurrency Control

mobiprep (6).png

Deadlock

mobiprep (6).png

Files and Storage

mobiprep (6).png

Indexing

Heading

Q

1

Define Normalization.

LRM_EXPORT_207556595493866_20190724_1939

Ans

The process of removal of duplicate or redundant data from the relation is called normalization. Normalization improves data integrity and data consistency. It also increases the storage efficiency of the database.

LRM_EXPORT_207556595493866_20190724_1939

Q

2

What is the need for Normalization.

LRM_EXPORT_207556595493866_20190724_1939

Ans

The main aim of normalization is to decrease data redundancy and to achieve data integrity and data consistency. If the same data is stored in different places of a database, it may lead to data inconsistency, and insufficient use of storage. This decreases the efficiency of the database. Hence, normalization is very much important in the design of an efficient database.

LRM_EXPORT_207556595493866_20190724_1939

Q

3

What are the different Normal Forms?

LRM_EXPORT_207556595493866_20190724_1939

Ans

The different normal forms are:
i. Un-normalized form
ii. First Normal Form
iii. Second NormalForm
iv. Third Normal Form
v. Boyce-Codd Normal Form
vi. Fourth Normal Form
vii. Fifth Normal Form
viii. Domain Key Normal Form

LRM_EXPORT_207556595493866_20190724_1939

Q

4

Explain 1NF in DBMS with example.

LRM_EXPORT_207556595493866_20190724_1939

Ans

Every attribute in the table must be a single-valued attribute. If a relation contains a composite or multi-valued attribute, it violates 1 st normal form. Entries in the column should be of the same data type. Each row must be uniquely identifiable.
The above table has multi-valued Course attribute. Hence, the table is not in 1NF. To convert it into 1NF, one of the following methods can be followed. Method 1 Method 2.


























LRM_EXPORT_207556595493866_20190724_1939

Q

5

What is 2NF in DBMS?

LRM_EXPORT_207556595493866_20190724_1939

Ans

For a relation to be in 2 nd normal form , the following conditions must be satisfied by the relation:
a. The relation must be in 1 st normal form.
b. The relation must not contain any partial dependency. If the partial subset of candidate key determines non-prime attribute, it is called partial dependency.
To convert a relation that has a partial dependency to 2NF, we need to split the table into two tables. 2NF reduces the redundant data from getting stored in memory.

LRM_EXPORT_207556595493866_20190724_1939

Q

6

What is 3NF in DBMS?

LRM_EXPORT_207556595493866_20190724_1939

Ans

For a relation to be in 3NF, the following conditions must be satisfied.
1. The relation must be in 2NF
2. The non-prime attributes should not have a transitive dependency.
3.To satisfy 3NF, either LHS should be a super key or RHS should be a prime attribute

LRM_EXPORT_207556595493866_20190724_1939

Q

7

What is Boyce Codd Normal Form in DBMS?

LRM_EXPORT_207556595493866_20190724_1939

Ans

a. BCNF does not allow dependencies between attributes that belong to a candidate key
b. A relation is in BCNF if it is in 3NF and for every FD, LHS is super key
c. A relation is in BCNF if and only if in every non-trivial functional dependency X→Y, X is a super key
d. BCNF can lead to lossy decomposition or dependencies may be lost.
e. BCNF is free from redundancy.
f. Steps to convert a relation into BCNF
i. Place the 2 candidate primary keys (which are dependent on each other) in separate entities

LRM_EXPORT_207556595493866_20190724_1939

Q

8

What are 4th and 5th Normal Forms in DBMS?

LRM_EXPORT_207556595493866_20190724_1939

Ans

4NF
For a relation to be in 4NF
1. It should satisfy BCNF
2. It should not have a multi-valued dependency.
It states that the table must not contain more than 1 multivalued dependency (can contain 1 multivalued dependency) 4NF eliminates independent many-to-one relationships between columns
5NF
1.The 5 th Normal Form is satisfied when all tables are decomposed into as many tables as possible to avoid redundancy.
2. Conditions for a relation to be in 5NF o The relation should be in 4NF
It should not have join dependency
3.The 5 th Normal Form is also known as the Project Join Normal Form or Projected Normal Form.

LRM_EXPORT_207556595493866_20190724_1939

Q

9

Define Partial Dependency.

LRM_EXPORT_207556595493866_20190724_1939

Ans

If a part (or a partial subset) of a candidate key determines a non-prime attribute, it is called partial dependency.
Part of primary key → non-prime attribute Partial dependencies are removed by the Second Normal Form.

LRM_EXPORT_207556595493866_20190724_1939

Q

10

Define Transitive Dependency.

LRM_EXPORT_207556595493866_20190724_1939

Ans

a. Transitive dependency
i. If A→B and B→C are two FDs, then A→C is called transitive dependency
b. If there is a transitive dependency, we have to decompose the relation into two
c. The transitive dependency can be solved by the following steps:
i. Move all items involved in the transitive dependency (B and C in the above example) to a new entity
ii. Identify the primary key for the new entity iii. Place the primary key for the new entity as a foreign key on the original entity
d. The 3NF removes the transitive dependencies.

LRM_EXPORT_207556595493866_20190724_1939