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

The correct answer is Option 1.

Key Points

• Given a relation R with attributes {A, B, C} and a functional dependency set S = {A → B, A → C}, we can decompose the relation R into two relations.
• Option 1 suggests decomposing R into R1{A, B} and R2{A, C}.
• To verify the correctness of the decomposition:
  • We need to check if both decomposed relations are in BCNF (Boyce-Codd Normal Form) or at least 3NF (Third Normal Form).
  • In R1{A, B}, the functional dependency A → B holds, and A is a candidate key for R1, satisfying BCNF.
  • In R2{A, C}, the functional dependency A → C holds, and A is a candidate key for R2, satisfying BCNF.
• Thus, the decomposition is lossless and dependency preserving, making Option 1 the correct choice.

Additional Information

• Decomposition is a process of breaking down a relation into smaller relations to achieve normalization and eliminate redundancy.
• BCNF is a stricter version of 3NF where every determinant is a candidate key.
• Ensuring that decomposed relations are in BCNF helps maintain data integrity and avoid anomalies during database operations.

The correct answer is: option 3: D only

Concept & Statement Analysis:

Statement A: ✅ True
An all-key relation means every attribute is part of the candidate key. Hence, no non-trivial Functional Dependencies (FDs) exist. Since BCNF only cares about non-trivial FDs and their compliance with superkeys, the relation is automatically in BCNF.

Statement B: ✅ True
If a relation is not in 4NF due to the presence of a non-trivial Multivalued Dependency (MVD) that violates 4NF, it must be decomposed to eliminate redundancy and achieve 4NF compliance.

Statement C: ✅ True
Yes, the decomposition in 4NF removes redundancy that arises due to MVDs. That's the goal of 4NF: to eliminate non-trivial MVDs that are not supported by superkeys.

Statement D: ❌ False
This is incorrect. In fact, BCNF is stronger than 3NF. Every relation in BCNF is in 3NF, but not every 3NF relation is in BCNF. 3NF allows some redundancy in special cases where BCNF does not.

Explanation of options:

• Option 1 – A only: ❌ A is true.
• Option 2 – A, B only: ❌ A and B are true.
• Option 3 – D only: ✅ Correct. Only D is false.
• Option 4 – C only: ❌ C is true.

Hence, the correct answer is: option 3: D only

The correct answer is A, B, C only.

Key Points

• The INSERT command in SQL is used to add data to tables within a database.
• Specifically, the INSERT command can perform the following actions:
  • Add a single tuple to a relation (or table): This allows the addition of a single row of data to the table.
  • Add multiple tuples to a relation: This allows the addition of multiple rows of data to the table in one command.
  • Add values to specific attributes: This allows specifying particular columns in the table to insert data into, rather than all columns.
• The INSERT command does not create new tables; it only inserts data into existing tables.

Additional Information

• The basic syntax for the INSERT command is:

  INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

• For adding multiple rows at once, the syntax is:

  INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...), (value3, value4, ...), ...;

• When inserting values into specific columns, the columns must be listed in the order corresponding to the values provided.
• The INSERT command ensures data integrity and consistency within relational databases by enforcing constraints and data types defined for the table columns.

The correct answer is {P, S}→{S}

Explanation:

Concept of Trivial Functional Dependency

• A trivial functional dependency occurs when the right-hand side (RHS) of the functional dependency is a subset of the left-hand side (LHS). In formal terms, X to Y is trivial if Y ⊆ X .

Given Functional Dependencies:
    1. {P, R} → {S, T}
    2. {P, S, U} → {Q, R}

We need to determine which of the given functional dependencies in the options is trivial.

Analyze Each Option

1. Option 1: {P, R} → {S, T}

• RHS = {S, T} , LHS = {P, R} .
• Since {S, T} \(\not\subseteq\) {P, R} , this is not trivial.

2. Option 2: {P, R} → {R, T}

• RHS = {R, T} , LHS = {P, R} .
• Here, R ⊆ {P, R} but T \(\not\subseteq\) {P, R} , so the entire RHS {R, T} \(\not\subseteq\) {P, R} .  
• This is not trivial.

3. Option 3: {P, S} → {S}

• RHS = {S} , LHS = {P, S} .
• Since S ⊆ {P, S} , this is a trivial functional dependency.

4. Option 4: {P, S, U} → {Q}

• RHS = {Q} , LHS = {P, S, U} .
• Since Q \(\not\subseteq\) {P, S, U} , this is not trivial.

Correct Answer: 3) {P, S} → {S}

The correct answer is X → Y 

Explanation:

• In database theory, functional dependency X → Y means that for every value of X , there is exactly one corresponding value of Y .

Given Scenario:

• Every time attribute X appears:
  • The same value of Y is matched.
  • A different value of Z is matched.

Analysis:

• X → Y : This is true because the problem explicitly states that for every X , the same value of Y is matched. This satisfies the definition of functional dependency X → Y .
• X → Z : This is false because Z can have multiple different values for the same X , violating the definition of functional dependency.
• X → (Y, Z) : This is false because while Y is uniquely determined by X , Z is not. Therefore, X does not determine the pair (Y, Z) .
• (Y, Z) → X : This is false because the question does not provide any evidence or context indicating that (Y, Z) determines X .

The functional dependency described is X → Y , making Option 1 the correct choice.

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} \(\nsupseteq\) {S, T}

Not a trivial functional dependency

Option 2: 

{P, R} → {R, T}

{P, R} \(\nsupseteq\) {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} \(\nsupseteq\) {Q}

Not a trivial functional dependency

NOTE:

\(\supseteq\) → superset

\(\nsupseteq\) → 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.