BCS403

Database Management Systems

Repeated and Important Questions
Module 3:
1. What is a Normalization? 1NF, 2NF, 3NF with examples [MQP-I,
MQP-II, June 2024]
• First normal form (1NF) states that the domain of an attribute must
include only atomic (simple, indivisible) values and that the value of any
attribute in a tuple must be a single value from the domain of that attribute.
• Consider the following DEPARTMENT relation, It is not in 1NF. Because
the domain of Dlocations contains sets of values and hence is nonatomic.

Second normal form (2NF) • Definition. A relation schema R is in 2NF if

every nonprime attribute A in R is fully functionally dependent on the
primary key of R.
 • Second normal form (2NF) is based on the concept of full functional
dependency. A functional dependency X → Y is a full functional dependency
if removal of any attribute A from X means that the dependency does not
hold any more. Example1: {Ssn, Pnumber} → Hours is a full dependency
(neither Ssn → Hours nor Pnumber→Hours holds).
• A functional dependency X → Y is a partial functional dependency if
removal of any attribute A from X and the dependency still holds;
Example2: The dependency {Ssn, Pnumber}→Ename is partial because
Ssn→Ename holds.

Third Normal Form (3NF)

• Definition: A relation schema R is in 3NF if it satisfies 2NF and no nonprime
attribute of R is transitively dependent on the primary key
. • Third normal form (3NF) is based on the concept of transitive dependency.
A functional dependency X→Y in a relation schema R is a transitive
dependency if there exists a set of attributes Z in R such that X→Z and Z→Y
hold.
Ex: The dependency Ssn→Dmgr_ssn is transitive in EMP_DEPT, because of
the dependencies Ssn → Dnumber and Dnumber → Dmgr_ssn.
2.What is Functional dependency? Explain the inference rules for
functional dependency with proof. [MQP-II, June 2024]
ANS:
Functional Dependencies
• Definition :A functional dependency, denoted by X → Y, between two sets of
attributes X and Y that are subsets of R specifies a constraint on the tuples in a
relation state r of R. The constraint is that, for any two tuples t1 and t2 in r that
have t1[X] = t2[X], they must also have t1[Y] = t2[Y].
• A functional dependency, denoted by X → Y means that the values of the Y
are determined by the values of X .
• A functional dependency is a property of the semantics or meaning of the
attributes. The database designers will use their understanding of the semantics
of the attributes of R to specify the functional dependencies in a relation.
• Consider the relation schema EMP_PROJ from the semantics of the attributes
and the relation, the following functional dependencies should hold:
a. Ssn→Ename
b. Pnumber →{Pname, Plocation}
c. {Ssn, Pnumber}→Hours
• These functional dependencies specifies that

(a) the value of an employee‘s Social Security number (Ssn) uniquely

determines the employee name (Ename),
(b) the value of a project‘s number (Pnumber) uniquely determines the
project name (Pname) and location (Plocation), and
(c) Combination of Ssn and Pnumber values uniquely determines the
number of hours the employee currently works on the project per week
(Hours).
• Types of functional dependency :

1. A functional dependency X → Y is a full functional dependency if

removal of any attribute A from X means that the dependency does not hold
any more; Ex: {Ssn, Pnumber} → Hours is a full dependency (neither Ssn
→ Hours nor Pnumber→Hours holds).
2. A functional dependency X → Y is a partial functional dependency if
removal of any attribute A from X and the dependency still holds; Ex: {Ssn,
Pnumber}→Ename is partial because Ssn→Ename holds.
3. A functional dependency X→Y in a relation schema R is a transitive
dependency if there exists a set of attributes Z in R such that X→Z and
Z→Y hold. Ex: The dependency Ssn→Dmgr_ssn is transitive in
EMP_DEPT, because of the dependencies Ssn → Dnumber and Dnumber →
Dmgr_ssn.
4. Trivial Functional Dependency. If a functional dependency (FD) X → Y
holds, where Y is a subset of X, then it is called a trivial FD. Non-trivial − If
an FD X → Y holds, where Y is not a subset of X, then it is called a non-
trivial FD.
3.SQL commands: INSERT, DELETE, UPDATE [MQP-II, June 2024]
ANS:
1 The INSERT Command
• INSERT is used to add a single tuple to a relation. We must specify the
relation name and a list of values for the tuple.
• While adding values for all the columns of the table, need not specify the
column names in the SQL query The values should be listed in the same
order in which the corresponding attributes were specified in the CREATE
TABLE command.
 • The INSERT INTO syntax would be as follows:
INSERT INTO table_name VALUES (value1, value2, value3, ...);
For example, to add a new tuple to the EMPLOYEE relation shown.
INSERT INTO EMPLOYEE VALUES (‘Richa’, ‘K’, ‘rani’,
‘653298653’,‘1962-12-30’, ’98 Oak Forest, Katy, TX’, ‘M’, 37000,
‘653298653’, 4 );
2 The DELETE Command
• The DELETE command removes tuples from a relation. The WHERE
clause, selects the tuples to be deleted. Tuples are explicitly deleted from only
one table at a time.
• The deletion propagates to tuples in other relations if referential triggered
actions(on delete cascade) are specified in the referential integrity constraints
of the DDL.
• A missing WHERE clause specifies that all tuples in the relation are to be
deleted; however, the table remains in the database as an empty table. The
DROP TABLE command to remove the table definition.
Example1:
DELETE FROM EMPLOYEE WHERE Lname=‘Brown’;
Example 2:
DELETE FROM EMPLOYEE
3 The UPDATE Command
• The UPDATE command is used to modify attribute values of one or more
selected Tuples, the WHERE clause in the UPDATE command selects the tuples
to be modified from a single relation.
• Updating a primary key value may propagate to the foreign key values of tuples
in other relations if such a referential triggered action(on update cascade) is
specified in the referential integrity constraints of the DDL.
• An additional SET clause in the UPDATE command specifies the attributes to
be modified and their new values.
Explain the types of update anomalies in SQL with an example.[ MQP-I, MQP-
II, June 2024]
ANS:
Update anomalies can be classified into insertion anomalies, deletion anomalies,
and modification anomalies.
Insertion Anomalies. Insertion anomalies can be differentiated into two types,
based on the EMP_DEPT relation:
1. To insert a new employee tuple into EMP_DEPT, we must include either the
attribute values for the department that the employee works for, or NULLs (if the
employee does not work for a department as yet).
2. It is difficult to insert a new department that has no employees as yet in the
EMP_DEPT relation. The only way to do this is to place NULL values in the
attributes for employee. This violates the entity integrity for EMP_DEPT because
Ssn is its primary key.
Deletion Anomalies. The problem of deletion anomalies is related to the second
insertion anomaly situation.
1. If we delete from EMP_DEPT an employee tuple that happens to represent the
last employee working for a particular department, the information concerning
that department is lost from the database.
2. This problem does not occur in DEPARTMENT relation since tuples are stored
separately.
Modification Anomalies.
1. In EMP_DEPT, if we change the value of one of the attributes of a particular
department say, the manager of department 5 we must update the tuples of all
employees who work in that department; otherwise, the database will become
inconsistent.
2. If we fail to update some tuples, the same department will be shown to have
two different values for manager in different employee tuples, which would be
wrong.
Unique Questions:-
1. Normalize EMP_PROJ to 2NF [Dec 2024]
2.FD Equivalence: F vs E [MQP-II]
3.JDBC driver types [Dec 2024]
4.String operations and datatypes in SQL [June 2024]
5.Minimal cover for FDs [MQP-I]
6.SQL on IA Marks, Student schema [Dec 2024]

https://youtube.com/@towards_wisdom_?si=XwieyBvjpvcIkXmC

Subscribe for more