Integrity Constraints and Normal Form MCQ Quiz - Objective Question with Answer for Integrity Constraints and Normal Form - Download Free PDF

Correct answer is Option1

Explanation:Use the union rule to replace

EF → G and EF → H 

EF → GH

F = { A → B  ABCD → E  EF → GH  ACDF → EG }

B is extraneous in ABCD → E because B ∈ ABCD and {A → B, ABCD → E, EF → GH, ACDF → EG}

logically implies {A → B, ACD → E, EF → GH, ACDF → EG}.

This is because every  ACD → E.

This FD can be derived using Armstrong’s Axioms from A → B and ABCD → E via transitivity rule

So remove B from ABCD → E.

F = { A → B ACD → E  EF → GH ACDF → EG }

E is extraneous in ACDF → EG because E ∈ EG and {A → B, ACD → E, EF → GH, ACDF → G}

logically implies {A → B, ACD → E, EF → GH, ACDF → EG}

remove E from ACDF → EG

F = { A → B ACD → E EF → GH ACDF → G}

G is extraneous in ACDF → G. Note that ACDF → G is already implied by ACD → E and EF → GH in F

remove ACDF → G from F.

None of the remaining FD's in F have extraneous attributes so the minimal cover is

A → B, ACD → E, EF → G, EF → H.

Hence the correct answer is option 1. 

The correct answer is Option 2

Key Points

• Difference (-) operation: The difference operation in relational algebra removes tuples from the first relation that are present in the second relation. It is a fundamental operation in relational algebra used to manipulate and analyze data.
• Non-Commutative: The difference operation is non-commutative because the order of the relations matters. For example, A - B is not the same as B - A.
• Associative: The difference operation is associative. This means that for three relations A, B, and C, the operation satisfies the property: (A - B) - C = A - (B - C).

Additional Information

• Relational algebra forms the theoretical foundation for relational databases. It defines operations such as selection, projection, union, intersection, difference, Cartesian product, and join.
• The difference operation is particularly useful in identifying records that exist in one dataset but not in another.

Conclusion: The difference operation in relational algebra is non-commutative and associative. Therefore, the correct answer is Option 2.

The correct answer is normal forms

Key Points

• Normal Forms: ✅ Normal forms are a set of guidelines used in relational database design to eliminate redundancy and prevent modification anomalies such as insertion, deletion, and update anomalies. By organizing data into well-defined tables, normal forms ensure consistency and integrity of the database.
• Functional Dependencies: ❌ Functional dependencies are a part of the process of determining normal forms but are not the technique itself. They describe relationships between attributes in a table.
• Data Integrity: ❌ Data integrity refers to maintaining the accuracy and consistency of data over its lifecycle, but it is not specifically the technique to prevent modification anomalies.
• Referential Integrity: ❌ Referential integrity ensures that relationships between tables remain consistent by enforcing foreign key constraints, but it does not directly address modification anomalies.

Additional Information

• There are different normal forms, such as 1NF, 2NF, 3NF, BCNF, etc., each addressing specific types of anomalies and redundancy.
• Normalization is essential for designing efficient and scalable databases.
• For example, 1NF ensures that the table has no repeating groups or arrays, while 2NF eliminates partial dependencies, and 3NF removes transitive dependencies.

The correct answer is Option 4) To eliminate data redundancy.

Key Points

• Normalization is a process in database design aimed at reducing data redundancy and ensuring data integrity.
• It involves organizing data into multiple related tables and applying rules called normal forms (1NF, 2NF, 3NF, etc.).
• The main goal is to:
  • Eliminate duplicate data
  • Ensure logical data dependencies
  • Make data updates, insertions, and deletions more efficient and less error-prone
• By removing redundancy, normalization also helps avoid anomalies such as update, insert, and delete anomalies.

Additional Information

• Option 1 – Incorrect: Enforcing referential integrity is a function of foreign keys, not normalization itself.
• Option 2 – Incorrect: Data encryption is a security feature, not related to the database design structure.
• Option 3 – Incorrect: Normalization may not always improve retrieval performance; in fact, it can require more joins, which could slightly impact performance.
• Normal Forms:
  • 1NF: Eliminates repeating groups
  • 2NF: Removes partial dependencies
  • 3NF: Removes transitive dependencies

The correct answer is One to one relationship between two sets of attributes

Key Points

• Functional Dependency: A functional dependency occurs when one set of attributes uniquely determines another set of attributes. It represents a one-to-one relationship between two sets of attributes.
• One-to-One Relationship: ✅ In a functional dependency, for every value of the determinant (left-hand side attribute), there is exactly one corresponding value in the dependent attribute (right-hand side).
• Many-to-Many Relationship: ❌ This does not represent a functional dependency because multiple values on the left-hand side could correspond to multiple values on the right-hand side.
• Many-to-One Relationship: ❌ While many-to-one mapping is possible, functional dependency strictly refers to one-to-one determination.
• None of the Above: ❌ Incorrect, as functional dependency has a precise definition involving one-to-one determination.

Additional Information

• Example: Consider attributes A and B in a database. If A → B (A determines B), then for every value of A, there is a unique value of B.
• Usage: Functional dependencies are critical in database normalization to reduce redundancy and maintain data integrity.
• Notation: Functional dependency is denoted as X → Y, where X determines Y.

Hence, the correct answer is: option 2) One to one relationship between two sets of attributes

Concept:

The closure of F, denoted as F+, is the set of all regular FD, that can be derived from.

For trivial functional dependency,

Let A and be two sets consists of attributes of a relation

A → B

A $\supseteq$ B 

Explanation:

Option 1: 

{P, R} → {S, T}

{P, R} $⊉$ {S, T}

Not a trivial functional dependency

Option 2: 

{P, R} → {R, T}

{P, R} $⊉$ {R, T}

Not a trivial functional dependency

Option 3: 

{P, S} → {S}

{P, S} $\supseteq$ {S}

It is a trivial functional dependency

Option 4: 

{P, S, U} → {Q}

{P, S, U} $⊉$ {Q}

Not a trivial functional dependency

NOTE:

$\supseteq$ → superset

$⊉$ → not superset

The correct answer is option 3.

Concept:

Normalization:

Normalization is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies.

1NF (First Normal Form):

• Each table cell should contain a single value.
• Each record needs to be unique.

2NF (Second Normal Form):

• It should be in 1NF.
• Single Column Primary Key that does not functionally dependant on any subset of candidate key relation.

3NF (Third Normal Form):

• It should be in 2NF.
• It has no transitive functional dependencies.

Boyce-Codd Normal Form (BCNF):

• A relation R is in BCNF if R is in Third Normal Form and for every FD, LHS is super key.
• A relation is in BCNF iff in every non-trivial functional dependency X –> Y, X is a super key.

The given relation schema:

Singer(singerName, songName).

• Every Binary Relation ( a Relation with only 2 attributes ) is always in BCNF. If a relation is BCNF then it should be in 3NF, 2NF, 1NF.

Hence Singer(singerName, songName) is Boyce-Codd Normal Form (BCNF).

Hence the correct answer is BCNF.

Answer: Option 4

Concept:

Lossless Decomposition:

for a Decomposition of two Relation, R₁ and R₂ to be lossless 2 condition needs to be satisfied that is

1. R1 ∩ R2 → R1 or R₂ i.e. common attributes must be key to either of the relation.

2. attributes of R1 ∪ attributes of R2 ≡ attributes of R

Explanation:

D1 : R = [(P, Q, S, T); (P, T, X); (Q, Y); (Y, Z, W)]

lets first take 2 relations R₁(P, Q, S, T )  R₂(P, T, X) 

common attributes are PT and PT → TX ( according to augmentation property )

so relation becomes R₁(P, Q, S, T, X) R₂(Q, Y) 

The common attribute is Q and Q→ Y is key to R₂ Hence (P, Q, S, T, X, Y)

 So now relation becomes R1(P, Q, S, T, X, Y) R2(Y, Z, W)

 The common attribute is Y and Y is key to R₂.

Hence all attributes get combined into one relation and hence this Decomposition is lossless.

D2 : R = [(P, Q, S); (T, X); (Q, Y); (Y, Z, W)]

If you observe relation (T, X); Its attributes not common to any other relations.

even if we combined all other attributes R₁(P, Q, S, Y, Z, W)  R₂(T, X) 

still no common attributes Hence this decomposition is lossy. 

1 NF

A relation R is in first normal form (1NF) if and only if all underlying domains contain atomic values only.

2 NF

A relation R is in second normal form (2NF) if and only if it is in 1NF and every non-key attribute is fully dependent on the primary key.

Example: A → B, B → C , A → C here A is key and relation is in  2NF but A → B, B → C , A → C is transitive

3 NF

A relation R is in third normal form (3NF) if and only if it is in 2NF and every non-key attribute is non-transitively dependent on the primary key. Hence Option 3 is the correct answer.

BCNF

A relation R is in Boyce-Codd normal form (BCNF) if and only if every determinant is a candidate key.

Concept:

Many to one relationship:  When more than one instance of an entity is associated with a single instance of another entity is called many to one relationship.

Explanation:

There exists many to one relationship between two entities r₁ and r­₂.

PK(r₁)- primary key of relation r₁

PK(r₂) – primary key of relation r₂

Consider attributes of relation r₁ are (employee id, employee name)

Consider attributes of relation r₂ are (address, department)

From here, employee id can clearly tell us about the employee, it can uniquely identify a person.  But the department cannot uniquely identify the employee.

So, option 1:  Pk(r₁) → Pk(r₂)

Concept: 

To find the irreducible equivalent of the given functional dependencies, we need to find the minimal cover set for these functional dependencies by removing all the left extraneous attributes and extra dependencies.

Explanation

V → W

VW → X

Y → VX

Y → Z

Consider,

V → W (As this is a single dependency that must be required, no need to remove)

VW → X ( In this, we have to delete the left extra attribute, for this we need to find the V⁺  and W⁺ from original dependencies, Now doing V⁺ if in V⁺ we get a W, then we can remove W attribute otherwise not same in case of W⁺)

So here, V⁺ = {V W X} and W⁺ = {W}

So, W → X can be removed from these functional dependencies.

Y → VX (Y → X can be find out using Y → V, V → X)

So, the irreducible equivalent of the given functional dependencies is:

V → W

V → X

Y → V

Y → Z

Answer: Option 3

CONCEPT:

3NF:

A relation is in 3NF if there is no transitive dependency for non-prime attributes as well as it is in second normal form.Transitive dependency occurs when FD (non-key-> non-key) exists in relation.

BCNF:

It stands for Boyce Codd's normal form.

A relation R is in BCNF if whenever a non-trivial functional dependency X -> A holds in R, then X is a superkey of R. Any relation with two attributes is always in BCNF. Because when a relation contains only two attributes then one attribute determines another and the left side of the functional dependency will always be a candidate key in that case. BCNF is not always dependency preserving.

EXPLANATION: 

This table indicates the following functional dependency

AB → CDE

C → B

(AB)⁺ = {A, B, C, D, E}

(AC)+ = {A, C, B, D, E}

Prime attributes: A, B,C

Keys: AB and AC

Since AB and AC are the keys; but in the second functional dependency, C is not key.

Hence the table is in 3rd normal form but not in BCNF.

Additional Information

1NF

It does not contain any composite or multi-valued attribute.

2NF

A relation is in 2NF if it has No Partial Dependency i.e., no non-prime attribute (attributes which are not part of any candidate key) is dependent on any proper subset of any candidate key of the table

Concept:

2NF: There should not be any partial dependency in the relation.

3NF: There should not be any transitive dependency and right side of functional dependency is a prime attribute

BCNF: Left side of functional dependency is a key

If a functional dependency is in BCNF, then it will also be in lower normal forms (1 NF, 2NF, 3 NF). Also, if any one functional dependency is in the weaker normal form, then relation will be in weaker normal form

Explanation:

Functional dependencies are:

a → c

b → d

Candidate key for the relation R is  {ab}

Because (ab)+ = {a, b, c, d}

1) a → c

This is not in BCNF, because left side is not the candidate key. Also, not in 3NF because right side is not the prime attribute. It is in not in 2NF, because there exists partial dependency in this functional dependency, as a non-prime attribute is dependent on prime attribute. It is in 1NF.

2) b → d

Similar case as that of 1^st functional dependency. It is in 1NF

Concept:

Indexing is a data structure technique to efficiently retrieve records from database files based on some attributes on which the indexing has been done. It is used to optimize the performance of database.

Explanation:

The index structure typically provides secondary access path i.e. alternate way of accessing the records without affecting the physical placement of records on disk.

There are several types of ordered indexes. A primary index is specified on the ordering key field of an ordered file of records. If ordering field is non key then it is known as clustering index. Non key and ordered means if numerous records in the file can have the same value for ordering field. Clustering index is also known as non-dense index.

Clustering index is an ordered file with two fields:

• First field is of same type as clustering of data file
• Second field is a block pointer.

Correct answer is Option1

Explanation:Use the union rule to replace

EF → G and EF → H 

EF → GH

F = { A → B  ABCD → E  EF → GH  ACDF → EG }

B is extraneous in ABCD → E because B ∈ ABCD and {A → B, ABCD → E, EF → GH, ACDF → EG}

logically implies {A → B, ACD → E, EF → GH, ACDF → EG}.

This is because every  ACD → E.

This FD can be derived using Armstrong’s Axioms from A → B and ABCD → E via transitivity rule

So remove B from ABCD → E.

F = { A → B ACD → E  EF → GH ACDF → EG }

E is extraneous in ACDF → EG because E ∈ EG and {A → B, ACD → E, EF → GH, ACDF → G}

logically implies {A → B, ACD → E, EF → GH, ACDF → EG}

remove E from ACDF → EG

F = { A → B ACD → E EF → GH ACDF → G}

G is extraneous in ACDF → G. Note that ACDF → G is already implied by ACD → E and EF → GH in F

remove ACDF → G from F.

None of the remaining FD's in F have extraneous attributes so the minimal cover is

A → B, ACD → E, EF → G, EF → H.

Hence the correct answer is option 1.