Database Design(Normal Forms)

Last Updated : Mar 22, 2024 Discuss

Question 1

For the relation R(ABCDEFGH) with FD's= {CH->G, A->BC, B->CHF, E->A, F->EG such that F+ is exactly the set of FDs that hold for R.}
Consider the FDs given in above question. The relation R is

A in 1NF, but not in 2NF.

B in 2NF, but not in 3NF.

C in 3NF, but not in BCNF.

D in BCNF

Discuss it

Explanation

The table is not in 2nd Normal Form as the non-prime attributes are dependent on subsets of candidate keys. The c
andidate keys are AD, BD, ED and FD. In all of the following FDs, the non-prime attributes are dependent on a parti
al candidate key. A -> BC B -> CFH F -> EG

Question 2

Which of the following is TRUE?

A Every relation in 3NF is also in BCNF

B A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every key of R

C Every relation in BCNF is also in 3NF

D No relation can be in both BCNF and 3NF

Discuss it

Explanation

BCNF is a stronger version 3NF. So every relation in BCNF will also be in 3NF.

Question 3

Consider a relational table with a single record for each registered student with the following attributes.
1. Registration_Num: Unique registration number
of each registered student
 2. UID: Unique identity number, unique at the
national level for each citizen
3. BankAccount_Num: Unique account number at
the bank. A student can have multiple accounts
or join accounts. This attribute stores the
primary account number.
4. Name: Name of the student
5. Hostel_Room: Room number of the hostel

Which one of the following option is INCORRECT?

A BankAccount_Num is candidate key

B Registration_Num can be a primary key

C UID is candidate key if all students are from the same country

D If S is a superkey such that S∩UID is NULL then S∪UID is also a superkey

Discuss it

Explanation

A Candidate Key value must uniquely identify the corresponding row in table. BankAccount_Number is not a candid
ate key. As per the question “A student can have multiple accounts or joint accounts. This attributes stores the prim
ary account number”. If two students have a joint account and if the joint account is their primary account, then Ban
kAccount_Number value cannot uniquely identify a row.

Question 4

Consider the following relational schema:

Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)

Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a
candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the
following is TRUE about the above schema?

A The schema is in BCNF

B The schema is in 3NF but not in BCNF

C The schema is in 2NF but not in 3NF

D The schema is not in 2NF

Discuss it

Explanation

A relation is in
BCNF
if for every one of its dependencies X → Y, at least one of the following conditions hold:

X → Y is a trivial functional dependency (Y ⊆ X)

X is a superkey for schema R
 Since (sname, city) forms a candidate key, there is no non-tirvial dependency X → Y where X is not a superkey

Question 5

Consider the following relational schemes for a library database: Book (Title, Author, Catalog_no, Publisher, Year, Price) Collection (Title,
Author, Catalog_no) with in the following functional dependencies:
I. Title Author --> Catalog_no
II. Catalog_no --> Title, Author, Publisher, Year
III. Publisher Title Year --> Price

Assume {Author, Title} is the key for both schemes. Which of the following statements is true?

A Both Book and Collection are in BCNF

B Both Book and Collection are in 3NF only

C Book is in 2NF and Collection is in 3NF

D Both Book and Collection are in 2NF only

Discuss it

Explanation

Book (Title, Author, Catalog_no, Publisher, Year, Price)

Collection (Title, Author, Catalog_no)

with in the following functional dependencies:

I. Title, Author --> Catalog_no

II. Catalog_no --> Title, Author, Publisher, Year
III. Publisher, Title, Year --> Price

Assume {Author, Title} is the key for both schemes

The table "Collection" is in BCNF as there is only one functional dependency “Title Author –> Catalog_no” and
{Author, Title} is key for collection.
Book is not in BCNF because Catalog_no is not a key and there is a functional dependency “Catalog_no –> Title
Author Publisher Year”.
Book is not in 3NF because non-prime attributes (Publisher Year) are transitively dependent on key [Title, Auth
or].
Book is in 2NF because every non-prime attribute of the table is either dependent on the whole of a candidate
key [Title, Author], or on another non prime attribute. In table book, candidate keys are {Title, Author} and {Cat
alog_no}. In table Book, non-prime attributes (attributes that do not occur in any candidate key) are Publisher, Y
ear and Prince

Please refer
Database Normalization | Normal Forms
for details of normal forms.

Question 6
Consider the relation scheme R = {E, F, G, H, I, J, K, L, M, N} and the set of functional dependencies {{E, F} -> {G}, {F} -> {I, J}, {E, H} -> {K, L},
K -> {M}, L -> {N} on R. What is the key for R?

A {E, F}

B {E, F, H}

C {E, F, H, K, L}

D {E}

Discuss it

Explanation

All attributes can be derived from {E, F, H}

To solve these kind of questions that are frequently asked in GATE paper, try to solve it by using shortcuts so that e
nough amount of time can be saved.
First Method:
Using the given options try to obtain closure of each options. The solution is the one that contains R and also minim
al Super Key, i.e Candidate Key.

A) {EF}+ = {EFGIJ} ≠ R(The given relation)

B) {EFH}+ = {EFGHIJKLMN} = R (Correct since each member of the

given relation is determined)

C) {EFHKL}+ = {EFGHIJKLMN} = R (Not correct although each member

of the given relation can be determined
but it is not minimal, since by the definition
of Candidate key it should be minimal Super Key)

D) {E}+ = {E} ≠ R

Second Method:

Since, {EFGHIJKLMN}+ = {EFGHIJKLMN}

{EFGHIJKLM}+ = {EFGHIJKLMN} ( Since L -> {N}, hence can replace N by L)

In a similar way K -> {M} hence replace M by K

{EFGHIJKL}+ = {EFGHIJKLMN}

Again {EFGHIJ}+ = {EFGHIJKLMN} (Since {E, H} -> {K, L}, hence replace KL by EH)

{EFGH}+ = {EFGHIJKLMN} (Since {F} -> {I, J} )

{EFH}+ = {EFGHIJKLMN} (Since {E, F} -> {G} )

This explanation is contributed by Manish Rai.

Learn more here:
Finding Attribute Closure and Candidate Keys using Functional Dependencies

Question 7

Given the following two statements:

 S1: Every table with two single-valued
attributes is in 1NF, 2NF, 3NF and BCNF.

S2: AB->C, D->E, E->C is a minimal cover for

the set of functional dependencies
AB->C, D->E, AB->E, E->C.

Which one of the following is CORRECT?

A S1 is TRUE and S2 is FALSE.

B Both S1 and S2 are TRUE.

C S1 is FALSE and S2 is TRUE.

D Both S1 and S2 are FALSE.

Discuss it

Explanation

S1: Every table with two single-valued

attributes is in 1NF, 2NF, 3NF and BCNF.

A relational schema R is in
BCNF
iff in Every non-trivial Functional Dependency X->Y, X is Super Key. If we can prove the relation is in BCNF then by
default it would be in 1NF, 2NF, 3NF also. Let R(AB) be a two attribute relation, then

1. If {A->B} exists then BCNF since {A}+ = AB = R

2. If {B->A} exists then BCNF since {B}+ = AB = R
3. If {A->B,B->A} exists then BCNF since A and B both are Super Key now.
4. If {No non trivial Functional Dependency} then default BCNF.

Hence it's proved that a Relation with two single - valued attributes is in BCNF hence its also in 1NF, 2NF, 3NF.
Hence S1 is true.

S2: AB->C, D->E, E->C is a minimal cover for

the set of functional dependencies
AB->C, D->E, AB->E, E->C.

As we know Minimal Cover is the process of eliminating redundant Functional Dependencies and Extraneous attrib
utes in Functional Dependency Set. So each dependency of F = {AB->C, D->E, AB->E, E->C} should be implied in m
inimal cover. As we can see AB->E is not covered in minimal cover since {AB}+ = ABC in the given cover {AB->C, D-
>E, E->C}
Hence, S2 is false.
This explanation has been contributed by
Manish Rai.
Learn more about Normal forms here:
Database Normalization | IntroductionDatabase Normalization | Normal Forms

Question 8

The maximum number of superkeys for the relation schema R(E,F,G,H) with E as the key is

A 5
 B 6

C 7

D 8

Discuss it

Explanation

Maximum no. of possible superkeys for a table with n attributes = 2^(n-1)

Here, n = 4.
So, the possible superkeys = 24-1 = 8
The possible superkeys are : E, EH, EG, EF, EGH, EFH, EFG, EFGH

Question 9

Given the STUDENTS relation as shown below.

For (StudentName, StudentAge) to be the key for this instance, the value X should not be equal to

A 18

B 19

C 17

D 20

Discuss it

Explanation

There is already an entry with same name and age as 19. So the age of this entry must be something other than 19.

Question 10

Which one of the following statements about normal forms is FALSE?

A BCNF is stricter than 3NF

B Lossless, dependency-preserving decomposi­tion into 3NF is always possible

C Lossless, dependency-preserving decomposi­tion into BCNF is always possible

D
 D
Any relation with two attributes is in BCNF

Discuss it

Explanation

Answer (c) It is not always possible to decompose a table in BCNF and preserve dependencies. For example, a set o
f functional dependencies {AB --> C, C --> B} cannot be decomposed in BCNF.

Question 11

Let r be a relation instance with schema R = (A, B, C, D). We define r1 = ΠA, B, C (r) and r2 = ΠA.D (r). Let s = r1 * r2 where * denotes natural
join. Given that the decomposition of r into r1 and r2 is lossy, which one of the following is TRUE?

A s⊂r

B r∪s

C r⊂s

D r*s=s

Discuss it

Explanation

Answer (c) Consider the following example with lossy decomposition of r into r1 and r2. We can see that r is a subs
et of s.

Table r
A B C D
---------------------------
1 10 100 1000
1 20 200 1000
1 20 200 1001

Table r1
A B C
------------------
1 10 100
1 20 200

Table r2
A D
-----------
1 1000
1 1001

Table s (natural join of r1 and r2)

A B C D
---------------------------
1 10 100 1000
1 20 200 1000
1 10 100 1001
1 20 200 1001
Question 12

Consider a relation scheme R = (A, B, C, D, E, H) on which the following functional dependencies hold: {A–>B, BC–>D, E–>C, D–>A}. What
are the candidate keys of R?

AE, BE
A

B
AE, BE, DE

AEH, BEH, BCH

C

D
AEH, BEH, DEH

Discuss it

Explanation

A set of attributes S is candidate key of relation R if the closure of S is all attributes of R and there is no subset of S
whose closure is all attributes of R.
Closure of AEH, i.e. AEH+ = {ABCDEH}
Closure of BEH, i.e. BEH+ = {ABCDEH}
Closure of DEH, i.e. DEH+ = {ABCDEH}

Question 13

Let R1 (A, B, C) and R2 (D, E) be two relation schema, where the primary keys are shown underlined, and let C be a foreign key in R1
referring to R2. Suppose there is no violation of the above referential integrity constraint in the corresponding relation instances r1 and
r2. Which one of the following relational algebra expressions would necessarily produce an empty relation ?

A 1

B 2

C 3

D 4

Discuss it

Explanation

Since C is a foreign key in R1 and there is no violation of the above referential integrity constraint, the set of values i
n C must be a subset of values in R2.
Question 14

The relation scheme Student Performance (name, courseNo, rollNo, grade) has the following functional dependencies:
name, courseNo → grade
rollNo, courseNo → grade
name → rollNo
rollNo → name

The highest normal form of this relation scheme is

A 2 NF

B 3 NF

C BCNF

D 4NF

Discuss it

Explanation

For easy understanding let's say attributes (name, courseNo, rollNo, grade) be (A,B,C,D). Then given FDs are as foll
ows:

AB->D, CB->D, A->C, C->A

Here there are two Candidate keys, AB and CB. Now AB->D and CB->D satisfy BCNF as LHS is superkey in both. B
ut, A->C and C->A, doesn't satisfy BCNF. Hence we check for 3NF for these 2 FDs. As C and A on RHS of both the
FDs are prime attributes, they satisfy 3NF. Hence for the whole relation the highest normal form is 3NF.

Question 15

Consider the relation Student (

name
, sex, marks), where the primary key is shown underlined, pertaining to students in a class that has at least one boy and one girl. What
does the following relational algebra expression produce? (Note: r is the rename operator).

The condition in join is "(sex = female ^ x = male ^ marks ≤ m)"

A names of girl students with the highest marks

B names of girl students with more marks than some boy student

C names of girl students with marks not less than some boy students4)

D names of girl students with more marks than all the boy students

Discuss it

Explanation
 The above relational algebra expression has two sub expressions.
The first one takes as input the Student relation (Student) and filters
out all the tuples where sex=female(r sex=female (Student))
and then projects their names (P name r sex=female (Student)).
So we get a new relation with names of all the female students.

The second one takes as input the Student relation and performs a rename
operation on one with attributes name, sex and marks renamed as n, x, m
respectively (r n, x, m(Student)) and then followed by a self-Cartesian
product on the Student relation. The condition (sex = female ^ m = male ^ marks ≤ m)
filters tuples with all female students from the first relation,
male students from the second relation and performs a Cartesian product where
marks of the female student is either less than or equal to a male student and
then projects their names. So we get a new relation with names of all female
students whose marks are lesser than at least one of the male student.

The difference operator(-) between the two subexpressions gives the names of all female students whose marks ar
e more than all male students of the class. (From all the female students’ names we remove all those whose marks
are at least more the one male student) This explanation has been contributed by
Yashika Arora.

Question 16

Consider the following functional dependencies in a database:

Data_of_Birth → Age
Age → Eligibility
Name → Roll_number
Roll_number → Name
Course_number → Course_name
Course_number → Instructor
(Roll_number, Course_number) → Grade

The relation (Roll_number, Name, Date_of_birth, Age) is:

A In second normal form but not in third normal form

B In third normal form but not in BCNF

C In BCNF

D None of the above

Discuss it

Explanation

The given table is not in 2NF as age is dependent on date of birth.

Question 17

Relation R with an associated set of functional dependencies, F is decomposed into BCNF. The redundancy (arising out of functional
dependencies) in the resulting set relations is.
 A Zero

B More than zero but less than that of an equivalent 3NF decomposition

C Proportional to the size of F+

D Indeterminate

Discuss it

Explanation

If a relational schema is in BCNF then all redundancy based on functional dependency has been removed, although
other types of redundancy may still exist.

Question 18

With regard to the expressive power of the formal relational query languages, which of the following statements is true?

A Relational algebra is more powerful than relational calculus

B Relational algebra has the same power as relational calculus

C Relational algebra has the same power as safe relational calculus

D None of the above

Discuss it

Explanation

A query can be formulated in relational calculus if and only if it can be formulated in relational algebra. So, relationa
l algebra has the same power as relational calculus. But, it is possible to write syntactically correct relational calcul
us queries that have infinite number of answers. Such queries are unsafe. Queries that have an finite number of ans
wers are safe relational calculus queries. Thus, Relational algebra has the same power as safe relational calculus. T
hus, option (C) is the answer.

Question 19

Relation R is decomposed using a set of functional dependencies, F and relation S is decomposed using another set of functional
dependencies G. One decomposition is definitely BCNF, the other is definitely 3NF, but it is not known which is which. To make a
guaranteed identification, which one of the following tests should be used on the decompositions? (Assume that the closures of F and G
are available).

A Dependency-preservation

B Lossless-join

C BCNF definition

D 3NF definition

Discuss it
 Explanation

Answer is (C) since to identify BCNF we need BCNF definition. One relation which satisfies will be in BCNF and oth
er will be in 3NF. 1st is wrong because dependency may be preserved by both 3NF and BCNF. 2nd is wrong Becaus
e both 3NF and BCNF decomposition can be lossless. 4th is wrong because 3NF and BCNF both are in 3NF also.

Question 20

Question 20 1. Check if A functionally determines B (A -

> B):
•If A is the same for two tuples, B must
From the following instance of a relation scheme R (A, B, C), we can conclude that : also be the same.
ABC •For A=1, B is 1 in both tuples (1,1,1)
111 and (1,1,0). This holds.
110
•For A=2, B is 3 in both tuples (2,3,2)
and (2,3,2). This holds.
232 •Therefore, A functionally determines B.
232 2. Check if B functionally determines C (B -
> C):
A A functionally determines B and B function­ally determines C •If B is the same for two tuples, C must
also be the same.
B A functionally determines B and B does not functionally determine C •Consider the tuples (1,1,1) and
(1,1,0). Here, B is 1 for both, but C is 1 in
C B does not functionally determine C one tuple and 0 in the other.
•Since the same value of B (1) leads to
different values of C (1 and 0), B does not
D A does not functionally determine B and B does not functionally determine C
functionally determine C.
Conclusion:
Discuss it Based on the analysis, A functionally
determines B, and B does not functionally
determine C.
Explanation Final Answer:
The correct conclusion is:
Generally Normalization is done on the schema itself.
(B) A functionally determines B and B does
not functionally determine C.
From the relational instance given,we may strike out FD s that do not hold.

e.g.B does not functionally determine C(This is true).

But we cannot say that A functionally determines B for the entire relation itself. This is because that, A->B holds for
this instance, but in future there might be some tuples added to the instance that may violate A->B.

So overall on the relation we cannot conclude that A->B, from the relational instance which is just a subset of an en
tire relation.

Question 21

Consider a schema R(A,B,C,D) and functional dependencies A->B and C->D. Then the decomposition of R into R1(AB) and R2(CD) is

A dependency preserving and lossless join

B lossless join but not dependency preserving

C dependency preserving but not lossless join

D not dependency preserving and not lossless join

Discuss it
 Explanation

Dependency Preserving Decomposition

:

Decomposition of R into R1 and R2 is a dependency preserving decomposition if closure of functional dependencies

after decomposition is same as closure of of FDs before decomposition.

A simple way is to just check whether we can derive all the original FDs from the FDs present after decomposition.

In the above question R(A, B, C, D) is decomposed into R1 (A, B) and R2(C, D) and there are only two FDs A -> B an
d C -> D. So, the decomposition is dependency preserving
Lossless-Join Decomposition:

Decomposition of R into R1 and R2 is a lossless-join decomposition if at least one of the following functional depen
dencies are in F+ (Closure of functional dependencies)

R1 ∩ R2 → R1
OR
R1 ∩ R2 → R2

In the above question R(A, B, C, D) is decomposed into R1 (A, B) and R2(C, D), and R1 ∩ R2 is empty. So, the decom
position is not lossless.

Question 22

Suppose the adjacency relation of vertices in a graph is represented in a table Adj(X,Y). Which of the following queries cannot be
expressed by a relational algebra expression of constant length?

A List of all vertices adjacent to a given vertex

B List all vertices which have self loops

C List all vertices which belong to cycles of less than three vertices

D List all vertices reachable from a given vertex

Discuss it

Explanation

(A) This is simple query as we need to find (X, Y) for a given X. (B) This is also simple as need to find (X, X) (C) :-> C
ycle < 3 . Means cycle of length 1 & 2. Cycle of length 1 is easy., Same as self loop. Cycle of length 2 is is also not to
o hard to compute. Though it'll be little complex, will need to do like (X,Y) & (Y, X ) both present & X != Y,. We can d
o this with constant RA query. (D) :-> This is most hard part. Here we need to find closure of vertices. This will need
kind of loop. If the graph is like skewed tree, our query must loop for O(N) Times. We can't do with constant length
query here. Answer is :-> D

Question 23

Let r and s be two relations over the relation schemes R and S respectively, and let A be an attribute in R. then the relational algebra
expression is always equal to
 A A

B B

C C

D D

Discuss it

Explanation

The above expression evaluates A = a for tables r and s

Option A : is only displaying attributes of table r on the select condition

Option B : is only displaying attributes of table r

Option C: evaluates A = a by joining tables r and s efficiently , thus correct

Therefore, Answer C

Question 24

R(A,B,C,D) is a relation. Which of the following does not have a lossless join, dependency preserving BCNF decomposition?

A A->B, B->CD

B A->B, B->C, C->D

C AB->C, C->AD

D A ->BCD

Discuss it

Explanation

Background :

Lossless-Join Decomposition:

Decomposition of R into R1 and R2 is a lossless-join decomposition if at least one of the following functional d
ependencies are in F+ (Closure of functional dependencies)

R1 ∩ R2 → R1
OR
R1 ∩ R2 → R2

dependency preserving :
Decomposition of R into R1 and R2 is a dependency preserving decomposition if closure of functional depende
 ncies after decomposition is same as closure of of FDs before decomposition.
A simple way is to just check whether we can derive all the original FDs from the FDs present after decomposit
ion.

Question :
We know that for lossless decomposition common attribute should be candidate key in one of the relation.
A) A->B, B->CD
R1(AB) and R2(BCD) B is the key of second and hence
decomposition is lossless.
B) A->B, B->C, C->D
R1(AB) , R2(BC), R3(CD) B is the key of second and C is the key of third,
hence lossless.
C) AB->C, C->AD
R1(ABC), R2(CD) C is key of second, but C->A violates BCNF condition in ABC as C is not a key. We cannot decomp
ose ABC further as AB->C
dependency would be lost.
D) A ->BCD
Already in BCNF.
Therefore, Option C AB->C, C->AD is the answer.

Question 25

Which of the following relational calculus expressions is not safe?

A A

B B

C C

D D

Discuss it

Explanation

A tuple relational calculus expression may at times

generate an infinite relation
. It may also contain values that do not even appear in the database. Such expressions are said to be
unsafe.
A
safe
tuple relational calculus expression is the one which surely generates
finite results
. To pose a restriction over the unsafety of expressions in tuple relational calculus there is a concept of
domain
of a tuple relational formula denoted by dom (P) is the set of values referenced by P i.e. values there in P or values i
n tuple of a relation mentioned in P.
Eg: The expression {t | ¬ (t € R)} is not safe because there are infinitely many tuples that do not occur in R relation .
In the above question Options (A), (B) and option (D) produce finite set of tuples as each gives out tuples restricted
from a particular relation and hence are safe. Option (C) produces infinite number of tuples as it generates all the tu
ples not in R1 i.e. it can have tuples from any other relation other than R1.Hence it is not safe.
Question 26

Consider a relation geq which represents “greater than or equal to”, that is, (x,y) ∈ geq only if y >= x.
create table geq
(
ib integer not null
ub integer not null
primary key 1b
foreign key (ub) references geq on delete cascade
)

Which of the following is possible if a tuple (x,y) is deleted?

A A tuple (z,w) with z > y is deleted

B A tuple (z,w) with z > x is deleted

C A tuple (z,w) with w < x is deleted

D The deletion of (x,y) is prohibited

Discuss it

Explanation

In the above question, the relation schema is ( lb , ub ), where lb is the primary key, and ub is the foreign key which i
s referencing the primary key of its own relation. Hence the table geq is both the master ( which has the referenced
key ) as well as the child table (which has the referencing key). The table has two constraint, one is that if there is a
tuple ( x, y ), then y is greater than or equal to x, And the other is referential integrity constraint, which is on-cascad
e-delete on the foreign key. On-cascade-delete says, that "When the referenced row is deleted from the other table
(master table), then delete also from the child table".

Suppose the instance in the given relation is the following:

xy
-----
56
45
34
66

Now if we delete tuple (5,6) then tuple ( 4,5 ) should also be deleted ( as 5 in the tuple (4, 5) was referencing to 5 i
n the tuple(5,6) which no longer exist, hence the referencing tuple should also be deleted), and as (4,5) got deleted
hence tuple (3,4) should also be deleted for the same reason. Therefore in total 3 rows have to be deleted if tuple (
5,6 ) is deleted. Now from the above instance we can say that if (x,y), i.e. ( 5,6 ) gets deleted then a tuple ( z, w) i.e, (
3, 4) is also deleted. And we can see here that w < x. Hence option C.

Question 27

Given the relations

employee (name, salary, deptno) and
department (deptno, deptname, address)

Which of the following queries cannot be expressed using the basic relational algebra operations (U, -, x,π, σ, ρ)?
 A Department address of every employee

B Employees whose name is the same as their department name

C The sum of all employees’ salaries

D All employees of a given department

Discuss it

Question 28

Given the following relation instance.

x y z
1 4 2
1 5 3
1 6 3
3 2 2

Which of the following functional dependencies are satisfied by the instance? (GATE CS 2000)

A XY -> Z and Z -> Y

B YZ -> X and Y -> Z

C YZ -> X and X -> Z

D XZ -> Y and Y -> X

Discuss it

Question 29

Consider an Entity-Relationship (ER) model in which entity sets E1 and E2 are connected by an m : n relationship R12, E1 and E3 are
connected by a 1 : n (1 on the side of E1 and n on the side of E3) relationship R13.
E1 has two single-valued attributes a11 and a12 of which a11 is the key attribute. E2 has two single-valued attributes a21 and a22 is the
key attribute. E3 has two single-valued attributes a31 and a32 of which a31 is the key attribute. The relationships do not have any
attributes.
If a relational model is derived from the above ER model, then the minimum number of relations that would be generated if all the
relations are in 3NF is ___________.

A 2

B 3

C 4

D 5

Discuss it

Explanation
 Entity E1.
a1 a12
--------
a11 is key

Entity E2
a21 a22
--------
a22 is key

Entity E3
a31 a32
--------
a31 is key

R12 is m:n Relationship between E1 and E2

R12
a11 a22
-------------
(a11, a22) is key.

R13 is 1:n Relationship between E1 and E3

R13
a11 a31
-----------
(a11, a31) is key.

We need minimum no. of tables.

Can we remove any of the above tables without
loosing information and keeping the relations in 3NF?

We can combine R13 and R12 into one.

a11 a31 a22
------------------
(a11, a31, a22) is key.

The relation is still in 3NF as for every functional

dependency X -> A, one of the following holds
1) X is a superkey or
2) A-X is prime attribute

Question 30

Consider the relation X(P, Q, R, S, T, U) with the following set of functional dependencies
F = {
{P, R} → {S,T},
{P, S, U} → {Q, R}
}

Which of the following is the trivial functional dependency in F+ is closure of F?

A {P,R}→{S,T}
 B {P,R}→{R,T}

C {P,S}→{S}

D {P,S,U}→{Q}

Discuss it

Explanation

A functional dependency X -> Y is trivial if Y is a subset of X.

So option C is correct.

Question 31

Consider the following entity relationship diagram (ERD), where two entities E1 and E2 have a relation R of cardinality 1 : m.

The attributes of E1 are A11, A12 and A13 where A11 is the key attribute. The attributes of E2 are A21, A22 and A23 where A21 is the key
attribute and A23 is a multi-valued attribute. Relation R does not have any attribute. A relational database containing minimum number
of tables with each table satisfying the requirements of the third normal form (3NF) is designed from the above ERD. The number of
tables in the database is

A 2

B 3

C 5

D 4

Discuss it

Explanation

Step 1: 1NF
T1:
A11
, A12, A13 T2: A11,
A21
, A22,
A23
//because A23 is multivalued ,it has to be included in Key attribute
Step 2: 2NF //
A23 is Multivalued attribute and not allowed in 2NF therefore new tables are
:
T1:
A11
, A12, A13 T2: A11,
A21
, A22 T3:
A21
,
A23
Step 3: 3NF
// There is no transitive functional dependency in all tables , So in 3NF
Therefore answer is B
Question 32

A relational database contains two tables student and department in which student table has columns roll_no, name and
dept_id and department table has columns dept_id and dept_name. The following insert statements were executed
successfully to populate the empty tables:

Insert into department values (1, 'Mathematics')

Insert into department values (2, 'Physics')
Insert into student values (l, 'Navin', 1)
Insert into student values (2, 'Mukesh', 2)
Insert into student values (3, 'Gita', 1)

How many rows and columns will be retrieved by the following SQL statement?
Select * from student, department

A 0 row and 4 columns

B 3 rows and 4 columns

C 3 rows and 5 columns

D 6 rows and 5 columns

Discuss it

Explanation

Simple,Cartesian product of two tables will result

Rows = 3*2 = 6
Columns = 3+2 = 5

Option (D) is correct.

Question 33

Consider the entities 'hotel room', and 'person' with a many to many relationship 'lodging' as shown below:

If we wish to store information about the rent payment to be made by person (s) occupying different hotel rooms, then this information
should appear as an attribute of

A Person

B Hotel Room

C Lodging

D None of these

Discuss it

Explanation
 Lodging is the only attribute relating person and hotel room.

Question 34

A table has fields Fl, F2, F3, F4, F5 with the following functional dependencies
F1 → F3 F2→ F4 (F1 . F2) → F5
In terms of Normalization, this table is in

A 1 NF

B 2 NF

C 3 NF

D none

Discuss it

Explanation

First Normal Form

A relation is in first normal form if every attribute in that relation is singled valued attribute.

Second Normal Form

A relation is in 2NF iff 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.

This table has Partial Dependency f1->f3, f2-> f4 given (F1,F2) is Key

So answer is A

Question 35

Which of the following is NOT a superkey in a relational schema with attributes V, W, X, Y, Z and primary key V Y ?

A VXYZ

B VWXZ

C VWXY

D VWXYZ

Discuss it

Explanation

On Analyzing the options

 Option 1: VXYZV
This set includes V,YV (the primary key) and adds X,Z.
Since it includes V,Y, it uniquely identifies every tuple.
It is a superkey.

Option 2: VWXZ
This set includes V, but it does not include Y.
Without Y, the set cannot uniquely identify tuples because V alone is insufficient to ensure uniqueness.
It is NOT a superkey.

Option 3: VWXY
This set includes V,Y (the primary key) and adds W,X.
Since it includes V,Y, it uniquely identifies every tuple.
It is a superkey.

Option 4: VWXYZ
This set includes V,Y (the primary key) and adds W,X,Z.
Since it includes V,Y, it uniquely identifies every tuple.
It is a superkey.

Correct Answer: The set that is NOT a superkey is: (B) VWXZ​.

Question 36

Let R (A, B, C, D, E, P, G) be a relational schema in which the following functional depen­dencies are known to hold: AB → CD, DE → P, C → E,
P → C and B → G. The relational schema R is

A in BCNF

B in 3NF, but not in BCNF

C in 2NF, but not in 3NF

D not in 2NF

Discuss it

Explanation

Candidate key = AB

B->G is partial dependency

So, not in 2NF

Question 37

Which option is true about the SQL query given below?

SELECT firstName, lastName
FROM Employee
WHERE lastName BETWEEN 'A%' AND 'D%';
 A It will display all the employees having last names starting with the alphabets 'A' till 'D' inclusive of A and exclusive of D.

B It will throw an error as BETWEEN can only be used for Numbers and not strings.

C It will display all the employees having last names starting from 'A' and ending with 'D'.

D
It will display all the employees having last names in the range of starting alphabets as 'A' and 'D' excluding the names starting
with 'A' and 'D'.

Discuss it

Explanation

The BETWEEN operator works with the range of character values also.

Question 38

Which of the given options define a transaction correctly?

A A transaction consists of DDL statements on the database schema.

B A transaction consists of COMMIT or ROLLBACK in a database session.

A transaction consists of either a collection of DML statements or a DDL or DCL or TCL statement to form a logical unit
C
of work in a database session.

D A transaction consists of collection of DML and DDL statements in different sessions of the database.

Discuss it

Explanation

A database transaction consists of one or more DML statements to constitute one consistent change in data, or a DDL st
atement or a DCL command (GRANT or REVOKE). It starts with the first DML statement and ends with a DCL or DDL or T
CL (COMMIT or ROLLBACK) command. Note that DDL and DCL commands hold auto commit feature.

Question 39

Courses Let R = ( A, B, C, D, E, F ) be a relation scheme with the following dependencies: C→F, E→A, EC→D, A→B. Which of the following is a key of R?
Tutorials
Search... Sign In
Practice A CD
Jobs
DSA Practice Problems C C++ Java Python JavaScript Data Science Machine Learning Courses Linux DevOps SQL Web Develop
B EC

C AE

D AC

Discuss it

Question 40
Consider the schema R= ( S, T, U, V ) and the dependencies S→T, T→U, U→V and V→S. Let R (R1 and R2) be a decomposition
such that R1∩R2 ≠ Ø. The decomposition is:
Question 40 To determine the normal form, we need to analyze the functional dependencies and the properties of the decomposition:
1.1. Candidate Keys:
A Not in 2NF oFrom the given dependencies ST, TU, UV, and VS, we can infer that S, T, U, and V are all prime attributes (part of a candidate key).
oThe closure of S is {S, T, U, V} (ST, TU, UV, VS). Therefore, S is a candidate key.
oSimilarly, T, U, and V are also candidate keys.
2.2. 2NF (Second Normal Form):
B In 2NF but not in 3NF oA relation is in 2NF if it is in 1NF and all non-prime attributes are fully functionally dependent on every candidate key.
oIn this schema, all attributes (S, T, U, V) are prime attributes (part of a candidate key).
oSince there are no non-prime attributes, the condition for 2NF is trivially satisfied. Therefore, the relation is in 2NF.
3.3. 3NF (Third Normal Form):
oA relation is in 3NF if it is in 2NF and there are no transitive dependencies where a non-prime attribute is transitively dependent on a candidate key
C In 3NF but not in 2NF via another non-prime attribute.
oAgain, since all attributes are prime, there are no non-prime attributes to be transitively dependent.
oHowever, the presence of cyclical dependencies like ST, TU, UV, and VS indicates a violation of 3NF if we consider the entire schema as a single
relation before decomposition, as there are non-trivial functional dependencies where the right-hand side is a prime attribute, and the left-hand side is
D In both 2NF and 3NF not a superkey (e.g., ST, T is prime, S is not a superkey for the entire relation if we consider only S as a key).
oBut the question refers to the decomposition and states R1R2 Ø. This condition, along with all attributes being prime, implies that the
decomposition maintains the properties that would lead to 2NF and 3NF for the decomposed relations if the decomposition is done correctly and the
relations are considered individually after decomposition.
Discuss it 4.4. Conclusion:
oSince all attributes are prime, the relation R trivially satisfies 2NF.
oThe decomposition, given R1R2 Ø and all attributes being prime, implies that the resulting relations are also in 3NF, assuming a proper
decomposition.
Therefore, the decomposition is in both 2NF and 3NF.
The correct answer is D In both 2NF and 3NF.
Explanation

R1∩R2 ≠ Ø means there is common attribute in R1 and R2. Now if we choose a decomposition positively then we c
an choose something like R1(S, T, U) and R2(U, V) then we can say that decomposition is lossless because common
attribute is U and LHS of every FDs are candidate key, therefore it is in 2NF as well as 3NF. Option (D) is correct.

Question 41

Which normal form is considered adequate for normal relational database design?

A 2NF

B 5NF

C 4NF

D 3NF

Discuss it

Explanation

3NF is sufficient because because most of the 3NF tables are free of insertion, update, and deletion anomalies. Moreove
r, 3NF always ensures functional dependency preserving and lossless. So, option (D) is correct.

Question 42

Consider the following database relations containing the attributes

Book_id
Subject_Category_of_book
Name_of_Author
Nationality_of_Author

with Book_id as the Primary Key.

(a). What is the highest normal form satisfied by this relation ?

(b). Suppose the attributes Book_title and Author_address are added to the relation, and the primary key is changed to
(Name_of_Author, Book_Title), what will be the highest normal form satisfied by the relation?
Discuss it

Question 43

For a database relation R(a,b,c,d), where the domains a, b, c, d include only atomic values, only the following functional
dependencies and those that can be inferred from them hold:

{ a → c, b → d }

This relation is

A in first normal form but not in second normal form

B in second normal form but not in first normal form

C in third normal form

D None of the above

Discuss it

Explanation

Candidate Key of above relation is :- ab only. a and b is partial attribute (part of the CK) that’s why the given FD is p
artially dependents. In 2NF there must not be partially dependents FD and we know that every table is already in 1
NF. Hence, this relation is in first normal form but not in second normal form. Option (A) is correct.

Question 44

Which of the following FD can’t be implied from FD set: {A->B, A->BC, C->D} ?

A A->C

B B->D

C BC->D

D All of the above

Discuss it

Explanation

A->C can be implied by A->BC and BC->D can be implied by C->D. But we can not get B->D sing given functional dep
endencies. Option (B) is correct.

Question 45

Consider a database with the following schema:

 Person ( name, age, gender )
name is a key
Frequents ( name, pizzeria )
(name, pizzeria) is a key
Eats ( name, pizza )
(name, pizza) is a key
Serves ( pizzeria, pizza, price )
(pizzeria, pizza) is a key

Relational algebra expression for query "Names of all people who frequent only pizzeria's serving at least one pizza they eat"
is

A 1

B Capture2

C Capture3

D None of the above

Discuss it

Question 46

How many minimum relation tables are required which satisfy 1NF? 333336

A 2, 2, and 1 respectively

B 2, 2, and 2 respectively

C 1, 2, and 1 respectively

D 1, 1, and 1 respectively

Discuss it

Question 47

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, coursid, email) Non-trivial functional dependencies: rollno, courseid → emailemail → rollno
Schema III: Registration (rollno, courseid, marks, grade) Non-trivial functional dependencies: rollno, courseid, → marks, grademarks
→ grade
Schema IV: Registration (rollno, courseid, credit) Non-trivial functional dependencies: rollno, courseid → creditcourseid → 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

Discuss it
Question 48

Consider the following table : Faculty (facName, dept, office, rank, dateHired)
facName dept office rank dateHired
Ravi Art A101 Professor 1975
Murali Math M201 Assistant 2000
Narayanan Art A101 Associate 1992
Lakshmi Math M201 Professor 1982
Mohan CSC C101 Professor 1980
Lakshmi Math M201 Professor 1982
Sreeni Math M203 Associate 1990
Tanuja CSC C101 Instructor 2001
Ganesh CSC C105 Associate 1995
(Assume that no faculty member within a single department has same name. Each faculty member has only one office
identified in office). 3NF refers to third normal form and BCNF refers to Boyee-Codd Normal Form Then Faculty is

A Not in 3NF, in BCNF

B In 3NF, not in BCNF

C In 3NF, in BCNF

D Not in 3NF, not in BCNF

Discuss it

Question 49

If every non-key attribute is functionally dependent on the primary key, then the relation is in __________ .

A First normal form

B Second normal form

C Third normal form

D Fourth normal form

Discuss it

Explanation

Conditions for various normal forms:

1. 1 NF - A relation R is in first normal form (1NF) if and only if all underlying domains contain atomic values only.
2. 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.
3. 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-tra
nsitively dependent on the primary key.
4. BCNF - A relation R is in Boyce-Codd normal form (BCNF) if and only if every determinant is a candidate key.

Example: Relation R(XYZ) with functional dependencies {X -> Y, Y -> Z, X -> Z}. Notice here Y -> Z, in question it is not
mention that non prime attribute is only dependent on primary key so this FD is perfectly valid. This relation is in 2NF b
ut not in 3NF because of every non-key attribute is transitively dependent on the primary key. Here {X} will be candidat
e key. So, option (B) is correct.
Question 50

Consider a schema R(A, B, C, D) and following functional dependencies.

A → B
B → C
C → D
D → B

Then decomposition of R into R1 (A, B), R2(B, C) and R3(B, D) is __________ .

A Dependency preserving and lossless join.

B Lossless join but not dependency preserving.

C Dependency preserving but not lossless join.

D Not dependency preserving and not lossless join.

Discuss it

Explanation

Schema R(A, B, C, D) is decomposed into three relation → R1 (A, B), R2(B, C) and R3(B, D) Now dependencies deriv
ed from R1 (A, B) are: A → B B → C but C is not attribute here in this relation. Dependencies derived from R1 (B, C)
are: B → C C → D D is not the attribute in relation. Dependencies derived from R1 (B, D) are: D → B B → C C → D A
ll the dependencies are preserved and it is a lossless decomposition. So, option (A) is correct.

Question 51

An attribute A of datatype varchar (20) has value ‘Ram’ and the attribute B of datatype char (20) has value ‘Sita’ in oracle. The
attribute A has _______ memory spaces and B has _______ memory spaces.

A 20,20

B 3,20

C 3,4

D 20,4

Discuss it

Explanation

varchar will acquire the exact memory of attribute and it varies from tuple to tuple while char will acquire memory space
which is define at the time of table creation it is fixed: varchar(20) 'Ram' will take 3 and 'Sita' will take 20 character space
in memory. So, option (B) is correct.
Question 52

Consider a schema R(MNPQ) and functional dependencies M → N, P → Q. Then the decomposition of R into R1 (MN) and R2(PQ)
is________.

A Dependency preserving but not lossless join

B Dependency preserving and lossless join

C Lossless join but not dependency preserving

D Neither dependency preserving nor lossless join.

Discuss it

Explanation

Schema R(MNPQ) is decomposed into R1(MN) M → N is preserved and R2(PQ) P → Q is also preserved, dependenc
y will be preserved and there will be no loss of any dependency. So, option (A) is correct.

Question 53

For a database relation R(A, B, C, D) where the domains of A, B, C and D include only atomic values, only the following
functional dependencies and those that can be inferred from them are : A → C B → D The relation R is in _______.

A First normal form but not in second normal form.

B Both in first normal form as well as in second normal form.

C Second normal form but not in third normal form.

D Both in second normal form as well as in third normal form.

Discuss it

Explanation

A relation is in first normal form if every attribute in that relation is single valued attribute. It is in 1NF. {A,B} are pri
me attribtes and {C,D} are non-prime attribute. A+ = {A,C} B+ = {B,D} {A,B}+ = {A,B,C,D} so AB is the key. But A+ =
{A,C} B+ = {B,D} makes it partial dependency. So, this relation is not in 2NF. So, option (A) is correct.

Question 54

Let R = (A, B, C, D, E, F) be a relation schema with the following dependencies C->F, E->A, EC->D, A->B. Which of the
following is a key of R?

A CD

B EC

C AE

D AC
Discuss it

Question 55

DBMS provides the facility of accessing data from a database through

A DDL

B DML

C DBA

D Schema

Discuss it

Explanation

1- DML stands for data manipulation language. The SQL commands that deals with the manipulation of data present in
the database belong to DML or Data Manipulation Language including retrieving, storing, modification, deletion, inserti
on and updation of data in a database. Examples of DML: SELECT, INSERT, UPDATE, DELETE. 2- DDL - Data Definition La
nguage - This commands in SQL includes the creation, deletion and modification of structure of database objects. Exam
ples of DDL commands: CREATE, DROP, ALTER, TRUNCATE, COMMENT, RENAME. It provides the facility of accessing dat
a from a database. 3- Functions of a DBA include: Schema definition, Storage structure and access-method definition, Sc
hema and physical-organization modification, Granting of authorization for data access, Routine maintenance. 4- Schem
a: The term "schema" refers to the organization of data as a blueprint of how the database is constructed (divided into d
atabase tables in the case of relational databases). The formal definition of a database schema is a set of formulas (sente
nces) called integrity constraints imposed on a database. So, option (A) is correct.

Question 56

Consider a database table R with attributes A and B. Which of the following SQL queries is illegal ?

A SELECT A FROM R;

B SELECT A, COUNT(*) FROM R;

C SELECT A, COUNT(*) FROM R GROUP BY A;

D SELECT A, B, COUNT(*) FROM R GROUP BY A, B;

Discuss it

Explanation

SELECT A FROM R; legal query SELECT A, COUNT(*) FROM R; Illegal query because we can't SELECT A, COUNT(*) unless
it is grouped by A. SELECT A, COUNT(*) FROM R GROUP BY A; legal query SELECT A, B, COUNT(*) FROM R GROUP BY A,
B; legal query So, option (B) is correct.

Question 57
Consider the schema R(A, B, C, D) and the functional dependencies A->B and C->D. If the decomposition is made as R1(A,B)
and R2(C,D), then which of the following is TRUE?

A Preserves dependency but cannot perform lossless join

B Preserves dependency and performs lossless join

C Does not perform dependency and cannot perform lossless join

D Does not preserve dependency but perform lossless join

Discuss it

Explanation

schema: R(A, B, C, D)
functional dependencies: A->B and C->D
Decomposed Schema: R1(A,B) and R2(C,D)

Check for dependency preserving: A decomposition is said to be dependency preserving if F+ = (F1 ∪ F2 ∪ .. Fn)+,wh
ere F+ = total functional dependencies(FDs) on universal relation R, and F1 = set of FDs of R1, F2 = set of FDs of R2 and
so on.

R1(A,B)
A->B is covered
R2(C,D)
C->D is covered.

Both the functional dependencies are covered, so FD preserving. Check for lossless join: Decomposition of R into R1 a
nd R2 is a lossless-join decomposition if at least one of the following functional dependencies are in F+

R1 ∩ R2 -> R1
R1 ∩ R2 -> R2

Here, R1(A,B) ∩ R2(C,D) = null So, it can not perform a lossless join. Option (A) is correct.

Question 58

Which of the following statements is/are True regarding some advantages that an object-oriented DBMS (OODBMS) offers
over a relational database ? I. An OODBMS avoids the “impedance mismatch” problem. II. An OODBMS avoids the “phantom”
problem. III. An OODBMS provides higher performance concurrency control than most relational databases. IV. An OODBMS
provides faster access to individual data objects once they have been read from disk.

A II and III only

B I and IV only

C I, II, and III only

D I, III and IV only

Discuss it
 Explanation

An OODBMS avoids the “impedance mismatch” problem.Correct.

An OODBMS doesn't avoids the “phantom” problem.
An OODBMS doesn't provides higher performance concurrency control than most relational databases. Since it
is distributed.
An OODBMS provides faster access to individual data objects once they have been read from disk.Correct
SO, option (B) is correct.

Question 59

Let x, y, z, a, b, c be the attributes of an entity set E. If {x}, {x,y}, {a,b}, {a,b,c}, {x,y,z} are superkeys then which of the following
are the candidate keys?

A {x,y} and {a,b}

B {x} and {a,b}

C {x,y,z} and {a,b,c}

D {z} and {c}

Discuss it

Explanation

A Candidate key is the minimal Superkey i.e. it is a minimal set of attributes required to identify a tuple. {x} ; {a,b} are the
candidate keys for the above schema as they do not contain any extraneous attribute. So, option (B) is correct.

Question 60

Which one of the following pairs is correctly matched in the context of database design? 12

A (1)

B (2)

C (3)

D (4)

Discuss it

Question 61

Consider the following table < img src = "https://www.cdn.geeksforgeeks.org/wp-content/uploads/ISRO64.png "> The table
is in which normal form?

A First Normal Form

 B Second Normal Form

C Third Normal Form but not BCNF

D Third Normal Form but not BCNF

Discuss it

Question 62

In RDBMS, the constraint that no key attribute (column) may be NULL is referred to as:

A Referential integrity

B Multi-valued dependency

C Entity Integrity

D Functional dependency

Discuss it

Explanation

In RDBMS, the constraint that no key attribute (column) may be NULL is referred to as Entity Interity
Referential integrity states that table relationships must always be consistent.
Multi-valued dependencyis a full constraint between two sets of attributes in a relation
Functional dependency is a relationship that exists when one attribute uniquely determines another attribute.

So, option (C) is correct.

Question 63

Which of the following statements is TRUE? D1 : The decomposition of the schema R(A, B, C) into R1(A, B) and R2 (A, C) is always lossless.
D2 : The decomposition of the schema R(A, B, C, D, E) having AD → B, C → DE, B → AE and AE → C, into R1 (A, B, D) and R2 (A, C, D, E) is
lossless.

A Both D1 and D2

B Neither D1 nor D2

C Only D1

D Only D2

Discuss it

Explanation

Only D2 is True because AD is key and present in both the tables. D1 is not always true because FD’s not given and
if we take B->A and C->A then it is lossy decomposition because no common attributes contain key from one of the
table.
Question 64

Consider the following dependencies and the BOOK table in a relational database design. Determine the normal form of the
given relation.

ISBN → Title
ISBN → Publisher
Publisher → Address

A First Normal Form

B Second Normal Form

C Third Normal Form

D BCNF

Discuss it

Explanation

Candidate key = ISBN For a relation having functional dependencies of the form α → β, a relation is in 2-NF if: i) α s
hould not be a proper subset of the candidate key, or, ii) β - α should be a prime attribute. First condition satisfies as
the candidate key contains only one attribute. So, this relation is in 2-NF

Question 65

Consider a “CUSTOMERS” database table having a column “CITY” filled with all the names of Indian cities (in capital letters).
The SQL statement that finds all cities that have “GAR” somewhere in its name, is:

A Select * from customers where city = ‘%GAR%’;

B Select * from customers where city = ‘$GAR$’;

C Select * from customers where city like ‘%GAR%’;

D Select * from customers where city as ‘%GAR’;

Discuss it

Question 66

match the following datbase terms to their function:

14

A (1)

B (2)

C (3)

D (4)
Discuss it

Question 67

Data which improves the performance and accessibility of the database are called:

A Indexes

B User Data

C Application Metadata

D Data Dictionary

Discuss it

Explanation

Data which improves the performance and accessibility of the database are called indexes because index is a type of any
data which improves the lookup table. Simply we can say that an index is a pointer to data in a table which increases the
accessibility in a book or databases, we can easily access any information with the help of index. Option (A) is correct

Question 68

Which type of DBMS provides support for maintaining several versions of the same entity?

A Relational Database Management System

B Hierarchical

C Object Oriented Database Management System

D Network

Discuss it

Explanation

Object Oriented Database Management Systems allow object-oriented programmers to develop the product, store the
m as objects, and replicate or modify existing objects to make new objects within the OODBMS. Option (C) is correct.

Question 69

Which normal form is based on the concept of 'full functional dependency'?

A First Normal Form

B Second Normal Form

C Third Normal Form

 D Fourth Normal Form

Discuss it

Explanation

A full functional dependency is a state of database normalization similar to Second Normal Form (2NF). It means that th
e schema should meet the requirements of First Normal Form (1NF), and all non-key attributes are fully functionally dep
endent on the primary key and partial dependency on the candidate key should not exist. So, Option (B) is correct.

Question 70

Consider the following database table: Create table test( one integer, two integer, primary key(one), unique(two), check(one >= 1 and <=
10), check(two >= 1 and <= 5) ); How many data records/tuples atmost can this table containt?

A 5

B 10

C 15

D 50

Discuss it

Explanation

check(one >= 1 and <= 10), check(two >= 1 and <= 5).
Here second constraint will decide the no of tuples(record). Or we can say that the common condition will dominat
e.
i.e. check(two >= 1 and <= 5) 5 tuples.
So, option (A) is correct.

Question 71

Consider the following three tables R, S and T. In this question, all the join operations are natural joins (⨝). (π) is the projection
operation of a relation: 69 (1) (a) 692 (2) (b) 692 (1) (1) (c) 692 (1) (2) (d) 692 (1) (3)

A (a)

B (b)

C (c)

D (d)

Discuss it

Question 72

Purpose of 'Foreign Key' in a table is to ensure

 A Null Integrity

B Referential Integrity

C Domain Integrity

D Null and Domain Integrity

Discuss it

Explanation

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table
containing the foreign key is called the child table, and the table containing the candidate key is called the referenced o
r parent table. Although there may be exceptions, the values in the foreign key columns usually must correspond to valu
es existing in the set of primary key values. This correspondence requirement is created in a database using a referential
integrity constraint on the foreign key. Option (B) is correct.

Question 73

Select the 'False' statement from the following statements about Normal Forms:

A Lossless preserving decomposition into 3NF is always possible

B Lossless preserving decomposition into BCNF is always possible

C Any Relation with two attributes is in BCNF

D BCNF is stronger than 3NF

Discuss it

Explanation

Lossless preserving decomposition into 3NF is always possible. True

Lossless preserving decomposition into BCNF is always possible. False Not always possible.
Any Relation with two attributes is in BCNF. True
BCNF is stronger than 3NF. True
For more information on Normal form Refer:Database Normalization | Normal Forms Option (B) is correct.

Question 74

The best normal form of relation scheme R(A, B, C, D) along with the set of functional dependencies F = {AB → C, AB → D, C
→ A, D → B} is

A Boyce-Codd Normal form

B Third Normal form

C Second Normal form

 D First Normal form

Discuss it

Explanation

Here C-> A (prime attribute -> prime attribute) and D->A (prime attribute -> prime attribute) is partially dependents
FDs . This type of FD must not be in BCNF and there is no problem with this type of FDs in 3NF. Option (B) is correc
t.

Question 75

Which of the following is false?

A Every binary relation is never be in BCNF.

B Every BCNF relation is in 3NF.

C 1 NF, 2 NF, 3 NF and BCNF are based on functional dependencies.

D Multivalued Dependency (MVD) is a special case of Join Dependency (JD).

Discuss it

Explanation

Every binary relation is never be in BCNF. This statement is incorrect because Every binary relation is always in BCNF.
Every BCNF relation is in 3NF. Correct.
1 NF, 2 NF, 3 NF and BCNF are based on functional dependencies. Correct.
Multivalued Dependency (MVD) is a special case of Join Dependency (JD).Correct.
So, option (A) is correct.

Question 76

For a database relation R(a,b,c,d) where the domains of a, b, c and d include only atomic values, only the following functional
dependencies and those that can be inferred from them hold
a -> c
b -> d

The relation is in

A First normal form but not in second normal form

B Second normal form but not in third normal form

C Third normal form

D None of the above

Discuss it

Explanation
 Candidate Key of this relation is ab and prime attribute are a and b.
Here a->b is (prime attribute-> Non prime attribute) and b>d is (prime attribute-> Non prime attribute) which is (par
tial dependency) must not present in 2NF. And every relation is already in 1NF.

Option (A) is correct.

Question 77

The set of attributes X will be fully functionally dependent on the set of attributes Y if the following conditions are satisfied.

A X is functionally dependent on Y

B X is not functionally dependent on any subset of Y

C Both (a) and (b)

D None of these

Discuss it

Explanation

The term full functional dependency is used to indicate the minimum set of attributes in a functional dependency. In
other words, the set of attributes X will be fully functionally dependent on the set of attributes Y if the following co
nditions are satisfied:

i) X is functionally dependent on Y and

ii) X is not functionally dependent on any
subset of Y.

Example: In relation
StuGrade
:

{stud_id, course_id, course_name} -> {course_grade}

The values of
stud_id, course_id, course_name
determine a unique value of
course_grade
. However, it is not a full functional dependency because it is sufficient to know only the value of a subset of {stud_i
d, course_id, course_name}, namely, {stud_id, course_id}, to determine the course_grade Thus, the correct full functi
onal dependency can be written as:

{stud_id, course_id} -> {course_grade}

So, option (C) is correct.

Question 78
For a database relation R(a, b, c, d) where the domains of a, b, c and d include only atomic values, and only the following
functional dependencies and those that can be inferred from them hold : a → c b → d The relation is in _________.

A First normal form but not in second normal form

B Second normal form but not in third normal form

C Third normal form

D BCNF

Discuss it

Question 79

Consider a relational table R that is in 3NF, but not in BCNF. Which one of the following statements is TRUE ?

A R has a nontrivial functional dependency X→A, where X is not a superkey and A is a prime attribute

B
R has a nontrivial functional dependency X→A, where X is not a superkey and A is a non-prime attribute and X is not a proper
subset of any key

C
R has a nontrivial functional dependency X→A, where X is not a superkey and A is a non-prime attribute and X is a proper subset of
some key

D A cell in R holds a set instead of an atomic value

Discuss it

Explanation

A relation is in 3NF if at least one of the following condition holds in every non-trivial function dependency X → Y:

1. X is a super key. (This condition is must for BCNF relations.).

2. Y is a prime attribute (each element of Y is part of some candidate key).

But, a relation is in BCNF if, X is superkey for every functional dependency (FD) X → Y in given relation.
Therefore, BCNF relations are subset of 3NF relations. Means every BCNF relation is 3NF but converse may not tru
e.
Option (A) is correct.

Question 80

Consider the following statements S1 and S2 about the relational data model:
S1: A relation scheme can have at most one foreign key.
S2: A foreign key in a relation scheme R cannot be used to refer to tuples of R.
Which one of the following choices is correct?

A Both S1 and S2 are true

B S1 is true and S2 is false

C S1 is false and S2 is true

D Both S1 and S2 are false

Discuss it

Explanation

S1: False, Because more than one Foreign key is possible if there is a requirement.
S2: False, Because foreign key act upon columns and used to identify tuples.

Question 81

Suppose the following functional dependencies hold on a relation U with attributes P,Q,R,S, and T:
P → QR
RS → T

Which of the following functional dependencies can be inferred from the above functional dependencies?
PS → T
R→T
P→R
PS → Q

Discuss it Submit

Explanation

P-> QR
RS-> T

From P->QR we derived P->Q and P->R.

After getting R from P we derive PS->T .
We directly derive Q from P so PS->Q also holds true.
Correct Option A , C, D

Question 82

Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F = {CH -> G, A -> BC, B -> CFH, E -> A, F -
> EG} is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R. How many candidate keys
does the relation R have?

A 3

B 4

C 5

D 6

Discuss it
 Explanation

A+ is ABCEFGH which is all attributes except D. B+ is also ABCEFGH which is all attributes except D. E+ is also ABCEFGH
which is all attributes except D. F+ is also ABCEFGH which is all attributes except D. So there are total 4 candidate keys A
D, BD, ED and FD

Question 83

In RDBMS, different classes of relations are created using __________ technique to prevent modification anomalies.

A Functional Dependencies

B Data integrity

C Referential integrity

D Normal Forms

Discuss it

Explanation

Functional dependency is a constraint that describes the relationship between attributes in a relation. Data integrity ref
ers to the accuracy and consistency of data stored in a database, data warehouse, data mart or other construct. Referen
tial integrity is a relational database concept, which states that table relationships must always be consistent. Normaliza
tion is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & del
etion anomaly. 1NF, 2NF, 3NF and BCNF are types of normal forms. So, option (D) is correct.

Question 84

A relation r(A,B) in a relational database has 1200 tuples. The attribute A has integer values ranging from 6 to 20, and the attribute B has
integer values ranging from 1 to 20. Assume that the attributes A and B are independently distributed. The estimated number of tuples
in the output of σ(A>10)∨(B=18)(r) is ____________.

A 820

B 1200

C 960

D 1000

Discuss it

Explanation

Probability of first condition satisfies, P(A) = 10/15

Probability of second condition satisfies, P(B) = 1/20
Probability of both condition satisfy, P(A∩B) = (10/15) × (1/20) Because of independent.
So, either first or second condition satisfy P(A∪B) = P(A) + P(B) - P(A∩B) = (10/15) + (1/20) - [(10/15) × (1/20)] = 4
1/60
Therefore, estimated number of tuples in the output, = 1200 × (41/60) = 820
Question 85

Relations produced from E - R Model will always be in ________.

A 1 NF

B 2 NF

C 3 NF

D 4 NF

Discuss it

Explanation

As we know that every table is already in 1NF. and relations produced from E – R Model will always be a table whic
h is in 1Nf. Hence (A) is correct.

Question 86

Consider the relation R (ABCDE): FD = { A → B, B → C, C → D, D → E} Find out the highest normal form.

A 1 NF

B 2 NF

C 3 NF

D BCNF

Discuss it

Explanation

Here candidate Key is A and B -> C , C -> D , D -> E all are. (Non prime attribute -> Non prime attribute.). This type of F
D must not be present in 3NF therefore highest normal form of this FDs are 2NF. Option (B) is correct.

Question 87

Let R = ABCDE is a relational scheme with functional dependency set F = {A → B, B → C, AC → D}. The attribute closures of A
and E are

A ABCD, φ

B ABCD, E

C Φ, φ

D ABC, E
Discuss it

Explanation

The attribute closures of A: A+ = ABCD The attribute closures of E: E+ = E. So, option (B) is correct.

Question 88

BCNF is not used for cases where a relation has

A Two (or more) candidate keys

B Two candidate keys and composite

C The candidate key overlap

D Two mutually exclusive foreign keys

Discuss it

Explanation

A relation is in BCNF if all attributes which are determinants are also candidate keys in every relation.
Transformation into BCNF deals with the problem of overlapping keys and there is no problem with two or more C
K.

Option (D) is correct.

Question 89

The Relation Vendor Order (V_no, V_ord_no, V_name, Qty_sup, unit_price) is in 2NF because:

A Non_key attribute V_name is dependent on V_no which is part of composite key

B Non_key attribute V_name is dependent on Qty_sup

C Key attribute Qty_sup is dependent on primary_key unit price

D Key attribute V_ord_no is dependent on primary_key unit price

Discuss it

Explanation

The Relation Vendor Order (V_no, V_ord_no, V_name, Qty_sup, unit_price) is in 2NF because: Non_key attribute V_n
ame is dependent on V_no which is part of composite key. For more information on Normal forms Refer:Database N
ormalization | Normal Forms Option (A) is correct.
Tags: DBMS Quiz

You have completed 79/89 questions .

Your accuracy is 60%.

Take a part in the ongoing discussion View All Discussion

Company Explore Tutorials DSA Data Science & Web

About Us Job-A-Thon Python Data Structures ML Technologies
Corporate & Communications Legal Offline Java Algorithms Data Science With HTML
Address: Privacy Policy Classroom C++ DSA for Python CSS
Careers Program PHP Beginners
A-143, 7th Floor, Machine Learning JavaScript
Sovereign Corporate In Media DSA in GoLang Basic DSA ML Maths TypeScript
Tower, Sector- 136, Noida, Contact Us JAVA/C++ SQL Problems Data Visualisation ReactJS
Uttar Pradesh (201305) Corporate Master R Language DSA Roadmap Pandas NextJS
Solution System Android DSA Interview
Registered Address: NumPy NodeJs
Campus Design Questions
K 061, Tower K, Gulshan NLP Bootstrap
Training Master CP Competitive
Vivante Apartment, Sector Deep Learning Tailwind CSS
137, Noida, Gautam Buddh Program Videos Programming
Nagar, Uttar Pradesh,
201305 Python Computer DevOps System School Databases
Tutorial Science Git Design Subjects SQL

Python GATE CS AWS High Level Mathematics MYSQL

Examples Notes Docker Design Physics PostgreSQL

Django Operating Kubernetes Low Level Chemistry PL/SQL

Tutorial Systems Azure Design Biology MongoDB

Python Computer GCP UML Diagrams Social Science

Projects Network DevOps Interview Guide English Grammar

Advertise with us Python Database Roadmap Design Patterns
Tkinter Management OOAD
Web Scraping System System Design
OpenCV Software Bootcamp
Tutorial Engineering Interview
Python Digital Logic Questions
Interview Design

Question Engineering
Maths

Preparation More Courses Programming Clouds/Devops GATE 2026

IBM Languages DevOps GATE CS Rank
Corner Tutorials
Certification C Programming Engineering Booster
Company- Software
Courses with Data AWS Solutions GATE DA Rank
Wise Development
DSA and Structures Architect Booster
Recruitment Software
Placements C++ Certification GATE CS & IT
Process Testing
Web Programming Salesforce Course - 2026
Aptitude Product
Development Course Certified GATE DA
Preparation Management
Data Science Java Administrator Course 2026
Puzzles Project
Programming Programming Course GATE Rank
Company- Management
Languages Course Predictor
Wise Linux
DevOps & Python Full
Preparation Excel
Cloud Course
All Cheat
Sheets

@GeeksforGeeks, Sanchhaya Education Private Limited, All rights reserved