Database Design Using Normalization Questions and Answers

Q: A database of research articles in a journal uses the following schema.(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, YEAR, PRICE)The primary key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE) and the following functional dependencies exist in the schema.(VOLUME, NUMBER, STARTPAGE, ENDPAGE) → TITLE(VOLUME, NUMBER) → YEAR(VOLUME, NUMBER, STARTPAGE, ENDPAGE) → PRICEThe database is redesigned to use the following schemas.(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE)(VOLUME, NUMBER, YEAR)Which is the weakest normal form that the new database satisfies, but the old one does not?

The weakest such normal form is 2NF.In the old schema the key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE), but YEAR depends on only part of it, (VOLUME, NUMBER). A non-prime attribute depending on part of a candidate key is a partial dependency, which violates 2NF. So the old database is in 1NF but not 2NF.The redesign moves YEAR into (VOLUME, NUMBER, YEAR), where (VOLUME, NUMBER) is the whole key, removing the partial dependency. Both new schemas are in 2NF (in fact in BCNF, since every determinant is a key).1NF holds for both, so it is not a difference. 3NF and BCNF are also met by the new design, but the question asks for the weakest form the new design gains, which is 2NF.

Q: Schema I: Registration (rollno, courses)Field 'courses' is a set-valued attribute containing the set of courses a student has registered for.Non-trivial functional dependency:rollno → coursesSchema II: Registration (rollno, courseid, email)Non-trivial functional dependencies:rollno, courseid → emailemail → rollnoSchema III: Registration (rollno, courseid, marks, grade)Non-trivial functional dependencies:rollno, courseid → marks, grademarks → gradeSchema IV: Registration (rollno, courseid, credit)Non-trivial functional dependencies:rollno, courseid → creditcourseid → creditWhich one of the relational schemas above is in 3NF but not in BCNF?

The schema in 3NF but not in BCNF is Schema II.In Schema II, email → rollno means {email, courseid} is also a key, so rollno is a prime attribute. The dependency email → rollno has a determinant that is not a superkey, which breaks BCNF, but its right side is prime, which 3NF allows.The other schemas fail 3NF or even earlier normal forms. Schema I has a set-valued attribute, so it is not even in 1NF. In Schema III, marks → grade makes the non-prime grade depend on the non-key marks, a transitive dependency that breaks 3NF. In Schema IV, courseid → credit makes the non-prime credit depend on part of the key, a partial dependency that breaks 2NF.

Q: Let R = (M N O P Q) be a relational scheme with functional dependency F = {M → N, N → O, MO → P}Then closures of Q and M are :

The closures are Q and MNOP.No functional dependency has Q on its left side, so nothing beyond Q itself can be derived: \(Q^{+} = Q\).For M, apply the dependencies in turn:\[ \begin{aligned} &M \to N: \{M, N\} \\ &N \to O: \{M, N, O\} \\ &MO \to P: \{M, N, O, P\} \end{aligned} \]A closure always contains the attribute itself, so it can never be empty (Φ). MNO stops one step short, missing P, which follows from MO.

Q: The process of eliminating redundancy in a database is called

The process is called normalization.Normalization organises a database into tables so that each fact is stored once, by decomposing relations according to their functional dependencies through the normal forms (1NF, 2NF, 3NF, BCNF). Removing redundancy also removes the insertion, update and deletion anomalies it causes.Replication does the opposite, deliberately copying data to several sites for availability. Virtualization abstracts hardware resources and has nothing to do with table design. Minimization is not a database design term.

Q: Which normal form removes partial dependency ?

Partial dependency is removed by 2NF.A partial dependency exists when a non-key attribute depends on only part of a composite primary key. In ORDER_ITEM(order_id, product_id, quantity, product_name), product_name depends on product_id alone. A relation is in second normal form when it is in 1NF and has no such dependency; the fix is to move product_name into a separate PRODUCT table.1NF only requires atomic values. 3NF removes transitive dependencies (a non-key attribute depending on another non-key attribute), and BCNF requires every determinant to be a candidate key. Both assume partial dependencies are already gone.

Q: Which type of database design is used to reduce redundancy and improve integrity ?

The design used to reduce redundancy is Normalization.Normalisation organises tables through normal forms (1NF, 2NF, 3NF, BCNF), splitting data so each fact is stored once. This removes redundancy and the insert, update and delete anomalies it causes, improving data integrity.Denormalisation deliberately adds redundancy to speed up reads, which is the opposite. Hybrid and object-oriented designs are not the technique for this purpose.

Q: In database normalization, what problem does 4NF (Fourth Normal Form) address ?

4NF addresses multi-valued dependencies.A relation is in fourth normal form when it is in BCNF and has no non-trivial multi-valued dependencies. If an employee's skills and languages are independent of each other but stored in one table, every skill must be paired with every language, which causes redundancy. 4NF splits them into Employee-Skill and Employee-Language tables.Partial dependencies are removed by 2NF and transitive dependencies by 3NF. Join dependencies are the subject of 5NF.

A database of research articles in a journal uses the following schema.

(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, YEAR, PRICE)

The primary key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE) and the following functional dependencies exist in the schema.

(VOLUME, NUMBER, STARTPAGE, ENDPAGE) → TITLE
(VOLUME, NUMBER) → YEAR
(VOLUME, NUMBER, STARTPAGE, ENDPAGE) → PRICE

The database is redesigned to use the following schemas.

(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE)
(VOLUME, NUMBER, YEAR)

Which is the weakest normal form that the new database satisfies, but the old one does not?

A.1NF
B.2NF
C.3NF
D.BCNF

Exam Year(s)

GATE CS - 2016,

Answer: B

The weakest such normal form is 2NF.

In the old schema the key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE), but YEAR depends on only part of it, (VOLUME, NUMBER). A non-prime attribute depending on part of a candidate key is a partial dependency, which violates 2NF. So the old database is in 1NF but not 2NF.

The redesign moves YEAR into (VOLUME, NUMBER, YEAR), where (VOLUME, NUMBER) is the whole key, removing the partial dependency. Both new schemas are in 2NF (in fact in BCNF, since every determinant is a key).

1NF holds for both, so it is not a difference. 3NF and BCNF are also met by the new design, but the question asks for the weakest form the new design gains, which is 2NF.

Discuss in Forum

Direction for [ Question No: 2 To 2 ] :

Consider the following four relational schemas. For each schema, all non-trivial functional dependencies are listed. The underlined attributes are the respective primary keys.

Schema I: Registration (rollno, courses)
Field 'courses' is a set-valued attribute containing the set of courses a student has registered for.
Non-trivial functional dependency:
rollno → courses

Schema II: Registration (rollno, courseid, email)
Non-trivial functional dependencies:
rollno, courseid → email
email → rollno

Schema III: Registration (rollno, courseid, marks, grade)
Non-trivial functional dependencies:
rollno, courseid → marks, grade
marks → grade

Schema IV: Registration (rollno, courseid, credit)
Non-trivial functional dependencies:
rollno, courseid → credit
courseid → credit

Which one of the relational schemas above is in 3NF but not in BCNF?

A.Schema I
B.Schema II
C.Schema III
D.Schema IV

Exam Year(s)

GATE CS - 2018,

Answer: B

The schema in 3NF but not in BCNF is Schema II.

In Schema II, email → rollno means {email, courseid} is also a key, so rollno is a prime attribute. The dependency email → rollno has a determinant that is not a superkey, which breaks BCNF, but its right side is prime, which 3NF allows.

The other schemas fail 3NF or even earlier normal forms. Schema I has a set-valued attribute, so it is not even in 1NF. In Schema III, marks → grade makes the non-prime grade depend on the non-key marks, a transitive dependency that breaks 3NF. In Schema IV, courseid → credit makes the non-prime credit depend on part of the key, a partial dependency that breaks 2NF.

Discuss in Forum

Let R = (M N O P Q) be a relational scheme with functional dependency F = {M → N, N → O, MO → P}

Then closures of Q and M are :

A.Φ, MNOP
B.Φ, Φ
C.Q, MNOP
D.Q, MNO

Exam Year(s)

MH SET - 2025, SET Exam - 2025,

Answer: C

The closures are Q and MNOP.

No functional dependency has Q on its left side, so nothing beyond Q itself can be derived: \(Q^{+} = Q\).

For M, apply the dependencies in turn:

\[ \begin{aligned} &M \to N: \{M, N\} \\ &N \to O: \{M, N, O\} \\ &MO \to P: \{M, N, O, P\} \end{aligned} \]

A closure always contains the attribute itself, so it can never be empty (Φ). MNO stops one step short, missing P, which follows from MO.

Discuss in Forum

The process of eliminating redundancy in a database is called

A.Replication
B.Virtualization
C.Normalization
D.Minimization

Exam Year(s)

Tripura Public Service Commission - 2026,

Answer: C

The process is called normalization.

Normalization organises a database into tables so that each fact is stored once, by decomposing relations according to their functional dependencies through the normal forms (1NF, 2NF, 3NF, BCNF). Removing redundancy also removes the insertion, update and deletion anomalies it causes.

Replication does the opposite, deliberately copying data to several sites for availability. Virtualization abstracts hardware resources and has nothing to do with table design. Minimization is not a database design term.

Discuss in Forum

Which normal form removes partial dependency ?

A.1NF
B.2NF
C.3NF
D.BCNF

Exam Year(s)

Tripura Public Service Commission - 2026,

Answer: B

Partial dependency is removed by 2NF.

A partial dependency exists when a non-key attribute depends on only part of a composite primary key. In ORDER_ITEM(order_id, product_id, quantity, product_name), product_name depends on product_id alone. A relation is in second normal form when it is in 1NF and has no such dependency; the fix is to move product_name into a separate PRODUCT table.

1NF only requires atomic values. 3NF removes transitive dependencies (a non-key attribute depending on another non-key attribute), and BCNF requires every determinant to be a candidate key. Both assume partial dependencies are already gone.

Discuss in Forum

Which type of database design is used to reduce redundancy and improve integrity ?

A.Denormalization
B.Normalization
C.Hybrid design
D.Object-oriented design

Exam Year(s)

Tripura Public Service Commission - 2025,

Answer: B

The design used to reduce redundancy is Normalization.

Normalisation organises tables through normal forms (1NF, 2NF, 3NF, BCNF), splitting data so each fact is stored once. This removes redundancy and the insert, update and delete anomalies it causes, improving data integrity.

Denormalisation deliberately adds redundancy to speed up reads, which is the opposite. Hybrid and object-oriented designs are not the technique for this purpose.

Discuss in Forum

In database normalization, what problem does 4NF (Fourth Normal Form) address ?

A.Partial dependencies
B.Transitive dependencies
C.Multi-valued dependencies
D.Join dependencies

Exam Year(s)

Tripura Public Service Commission - 2026,

Answer: C

4NF addresses multi-valued dependencies.

A relation is in fourth normal form when it is in BCNF and has no non-trivial multi-valued dependencies. If an employee's skills and languages are independent of each other but stored in one table, every skill must be paired with every language, which causes redundancy. 4NF splits them into Employee-Skill and Employee-Language tables.

Partial dependencies are removed by 2NF and transitive dependencies by 3NF. Join dependencies are the subject of 5NF.

Discuss in Forum

If a relation with a Schema R is decomposed into two relations \(R_1\) and \(R_2\) such that \((R_1 \cup R_2) = R_1\) then which one of the following is to be satisfied for a lossless joint decomposition (\(\to\) indicates functional dependency)

A.\((R_1 \cap R_2) \to R_1\) or \(R_1 \cap R_2 \to R_2\)
B.\(R_1 \cap R_2 \to R_1\)
C.\(R_1 \cap R_2 \to R_2\)
D.\(R_1 \cap R_2 \to R_1\) and \(R_1 \cap R_2 \to R_2\)

Exam Year(s)

UGC NET - 2012,

Answer: A

A decomposition of R into R1 and R2 is lossless if the common attributes form a key of at least one of the two relations, that is if \(R_1 \cap R_2 \to R_1\) or \(R_1 \cap R_2 \to R_2\) holds. Either one of the conditions is enough: it is not necessary that both hold, so the last option, which asks for both, is too strong, and each of the second and third options gives only one of the two possibilities. The first option states the condition with 'or', which is the lossless join test. The keyed answer is the first option.

Discuss in Forum

Third normal form is based on the concept of ______.

A.Closure Dependency
B.Transitive Dependency
C.Normal Dependency
D.Functional Dependency

Exam Year(s)

UGC NET - 2012,

Answer: B

Third normal form is based on the concept of transitive dependency. A relation is in 3NF if it is in second normal form and no non-key attribute depends on another non-key attribute, that is, there is no transitive dependency of a non-key attribute on the key. Functional dependency is the basis of all the normal forms and is not the concept that marks third normal form, and closure dependency and normal dependency are not standard terms. The keyed answer is transitive dependency.

Discuss in Forum

10.

Referential integrity is directly related to

A.Relation key
B.Foreign key
C.Primary key
D.Candidate key

Exam Year(s)

UGC NET - 2012,

Answer: B

Referential integrity is directly related to the foreign key. It is the rule that a value of a foreign key in one relation must either be null or match the value of the primary key of a tuple in the relation it refers to, so that there are no dangling references. A relation key, a primary key and a candidate key identify the tuples inside a relation and do not carry the reference between relations. The keyed answer is the foreign key.

Discuss in Forum

Loading…

Topics

Other Subjects

Tags: Database Design Using Normalization Questions and Answers || Database Design Using Normalization MCQ Questions and Answers || Database Design Using Normalization GK Questions and Answers || Database Design Using Normalization GK MCQ Questions || Database Design Using Normalization Multiple Choice Questions and Answers || Database Design Using Normalization GK || GK on Database Design Using Normalization || Database Questions and Answers || Database MCQ Questions and Answers || Database GK Questions and Answers || GK on Database

Database Design Using Normalization Questions and Answers

Direction for [ Question No: 2 To 2 ] :

Topics

Other Subjects

About Us

Stay informed about all important Dates & Notifications