Database Design Using Normalization Questions and Answers

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: 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: 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.

Q: Eliminating modification anomalies is a(n) ________ of normalization.

Correct answer: advantageThis is the central benefit and the reason normalization exists. A relation carrying more than one theme suffers insertion, deletion and update anomalies: a fact cannot be recorded without an unrelated one, deleting a row destroys information, and a single change must be made in many places. Decomposing into well-formed relations removes all three.

Q: Normalization ________ data duplication.

Correct answer: eliminatesProperly normalized tables store each fact once. The repetition of non-key data across rows is exactly what the normal forms are defined to remove, and its absence is what makes update anomalies impossible. Foreign key values do repeat, but they are the links between relations rather than duplicated data in the sense meant here.

Direction for [ Question No: 1 To 1 ] :

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

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

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

Each answer below shows example data from a table. Which answer is an example of the inconsistent values problem?

A.Three columns have the values 534-2435, 534-7867, and 546-2356 in the same row.
B.Three rows have the values Brown Small Chair, Small Chair Brown, and Small Brown Chair in the same column.
C.Three rows have the values Brown, NULL, and Blue in the same column.
D.One row has the value "He is interested in a Silver Porsche from the years 1978-1988" in a column.

Answer: B

Correct answer: Three rows have the values Brown Small Chair, Small Chair Brown, and Small Brown Chair in the same column.

All three rows describe the same thing but no two strings match, so grouping, counting and joining on the column all give wrong answers. It arises wherever free text is entered without a controlled vocabulary, and the remedy is to constrain the values - by a lookup table or a check constraint - rather than to clean them repeatedly.

Discuss in Forum

Eliminating modification anomalies is a(n) ________ of normalization.

A.advantage
B.disadvantage
C.either an advantage or disadvantage
D.neither an advantage nor disadvantage

Answer: A

Correct answer: advantage

This is the central benefit and the reason normalization exists. A relation carrying more than one theme suffers insertion, deletion and update anomalies: a fact cannot be recorded without an unrelated one, deleting a row destroys information, and a single change must be made in many places. Decomposing into well-formed relations removes all three.

Discuss in Forum

10.

Normalization ________ data duplication.

A.eliminates
B.reduces
C.increases
D.maximizes

Answer: A

Correct answer: eliminates

Properly normalized tables store each fact once. The repetition of non-key data across rows is exactly what the normal forms are defined to remove, and its absence is what makes update anomalies impossible. Foreign key values do repeat, but they are the links between relations rather than duplicated data in the sense meant here.

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: 1 To 1 ] :

Topics

Other Subjects

About Us

Stay informed about all important Dates & Notifications