Design of springs Database Management System

(a) Both S1 and S2 are true (a) S1 is TRUE and S2 is FALSE

(b) S1 is true and S2 is false (b) Both S1 and S2 are TRUE
CHAPTER
(c) S1 is false and S2 is true (c) S1 is FALSE and S2 is TRUE

1
(d) Both S1 and S2 are false (d) Both S1 and S2 are FALSE

6. [NAT] [GATE-2017 : 2M] ER Model Concept

Consider the following tables T1 and T2.
8. [MCQ] [GATE-2020 : 1M]
In table T1, P is the primary key and Q is the foreign
4. [MCQ] [GATE-2011 : 1M] key referencing R in table T2 with on delete cascade Which one of the following is used to represent the
Introduction to RDBMS
and on-update cascade. In table T2, R is the primary supporting many-one relationships of a weak entity
Consider a relational table with a single record for
1. [MCQ] [GATE-2023: 1M] key and S is the foreign key referencing P in table T1 set in an entity-relationship diagram?
each registered student with the following attributes.
Which one of the options given below refers to the with on-delete set NULL and on-update cascade. In (a) Diamonds with double/bold border.
1. Registration_Num: Unique registration number of
degree (or arity) of a relation in relational database order to delete record 3, 8 from table T1, the number (b) Ovals with double/bold border
each registered student
systems? of additional records that need to be deleted from table (c) Ovals that contain underlined identifiers
2. UID: Unique identity number, unique at the T1 is
(a) Number of attributes of its relation schema. (d) Rectangles with double/bold border
national level for each citizen.
(b) Number of tuples stored in the relation.
(c) Number of entries in the relation. 3. Bank Account_Num: Unique account number at 9. [MCQ] [GATE-2018 : 1M]
(d) Number of distinct domains of its relation the bank. A student can have multiple accounts or
In an Entity-Relationship (ER) model, suppose R is a
joint accounts. This attribute stores the primary
schema. many-to-one relationship from entity set E1 to entity
account number.
set E2. Assume that E1 and E2 participate totally in R
Key Concepts 4. Name: Name of the student and that the cardinality of E1 is greater than the
5. Hostel_Room: Room number of the hostel cardinality of E2.
2. [NAT] [GATE-2014 : 1M]
Which of the following option is INCORRECT? Which one of the following is true about R?
Given an instance of the STUDENTS relation as (a) Every entity in E1 is associated with exactly one
(a) BankAccount_Num is a candidate key.
shown below: 7. [MCQ] [GATE-2014 : 1M] entity in E2.
(b) Registration_Num can be a primary key.
Student Student Student Student CPI Given the following statements: (b) Some entity in E1 is associated with more than
ID Name Email Age (c) UID is a candidate key if all students are from the one entity in E2.
same country. S1: A foreign key declaration can always be
2345 Shankar shankar@math X 9.4 (c) Every entity in E2 is associated with exactly one
replaced by an equivalent check assertion in
1287 Swati swati@ee 19 9.5 (d) If S is a super key such that S UID is NULL entity in E1.
SQL.
7853 Shankar shankar@cse 19 9.4 then S UID is also a super key. (d) Every entity in E2 is associated with at most one
9876 Swati swati@mech 18 9.3 S2: Given the table R(a, b, c) where a and b together entity in E1.
form the primary key, the following is a valid
8765 Ganesh ganesh@civil 19 8.7 Foreign Key Concepts table definition.
For (Student Name, Student Age) to be a key for this ER Model Concept and Conversion of
instance, the value X should NOT be equal to ______. 5. [MCQ] [GATE-2021 : 1M] CREATE TABLE S
ER Model to RDBMS
Consider the following statements S1 and S2 about a INTEGER,
3. [MCQ] [GATE-2014 : 1M] the relational data model: 10. [MCQ] [GATE-2017 : 1M]
d INTEGER,
A prime attribute of a relation scheme R is an attribute S1: A relation scheme can have at most one foreign An ER model of a database consists of entity types A
e INTEGER,
that appears key. and B. These are connected by a relationship R which
(a) in all candidate keys of R. S2: A foreign key in a relation scheme R cannot be PRIMARY KEY (d), does not have its own attribute. Under which one of
(b) in some candidate key of R. used to refer to tuples of R. FOREIGN KEY (a) references (R) the following conditions, can the relational table for R
(c) in a foreign key of R. Which one of the following choices is correct? be merged with that of A?
Which one of the following statements is CORRECT?
(d) only in the primary key of R.

1.1 1.2
GATE Wallah CS & IT Topic wise PYQs GATE Wallah CS & IT Topic wise PYQs
 Database Design and ER Model Database Management System

(a) Relationship R is one-to-many and the (c) In a row of a relational table, an attribute can have
participation of A in R is total. more than one value.
(b) Relationship R is one-to-many and the (d) In a row of a relational table, an attribute can have
participation of A in R is partial. exactly one value or a NULL value.
(c) Relationship R is many-to-one and the 1. (a) 2. (19 to 19) 3. (b) 4. (a)
participation of A in R is total. 13. [MCQ] [GATE-2008 : 2M] 5. (d) 6. (0 to 0) 7. (d) 8. (a)
(d) Relationship R is many-to-one and the Consider the following ER diagram 9. (a) 10. (c) 11. (4 to 4) 12. (c)
participation of A in R is partial. 13. (b) 14. (a)

11. [NAT] [GATE-2015 : 2M]

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 The minimum number of tables needed to represent
(1 on the side of E1 and n on the side of E3) M, N, P R1, R2 is 1. (a) 4. (a)
relationship R13. (a) 2 (b) 3 Arity (Degree): Number of Attributes/fields (a) BankAccount_Num is a candidate key: Incorrect.
E1 has two single-valued attributes a11 and a12 of (c) 4 (d) 5 Cardinality: Number of Tuples/Records Because a student can have multiple account and
which a11 is the key attribute. E2 has two single valued joint account.
attributes a21 and a22 of which a21 is the key attribute. 14. [MCQ] [GATE-2008 : 2M] Example
E3 has two single valued attributes a31 and a32 of which Consider the following ER diagram Assume 2 person Ramesh and Suresh have joint
a31 is the key attribute. The relationships do not have account they both have some account number in
any attributes. a primary account number. So, Bank account
If a relational model is derived from the above ER 2. (19 to 19) number can not uniquely determine and Bank
model, then the minimum number of relations that account cannot be candidate key.
In order for Student Name, Student Age to be the key,
would be generated if all the relations are in 3 NF (b) Registration number can be primary key: True.
Which of the following is a correct attribute set for it must be unique, therefore the value of X 19
is__________. Because registration number is a unique for each
one of the tables for the minimum number of tables satisfies the criteria of Student Name, Student Age
needed to represent M, N, P, R1 and R2 registered student
12. [MCQ] [GATE-2012 : 1M] being the key.
So, it can be uniquely determining each tuple of
(a) {M1, M2, M3, P1} Hence, 19 is the answer.
Given the basic ER and relational models, which of relation.
the following is INCORRECT? (b) {M1, P1, N1, N2} (c) UID is a candidate if all student is from same
(a) An attribute of an entity can have more than one (c) {M1, P1, N1} country: correct.
value. (d) {M1, P1} Because UID is unique at National level
(b) An attribute of an entity can be composite. Example
Indian Aadhar card is unique for each person in
3. (b)
India.
Prime attribute is a attribute that is present in some
(d) If S is a super key, then S UID is also super
candidate key of a relation.
key: correct
Example: Consider for relation R(ABCDE) if the
Because any super set of super keys is also super
candidate keys are AC, and BC, then the set of prime
key.
attributes are {A, B, C}.

1.3 1.4
GATE Wallah CS & IT Topic wise PYQs GATE Wallah CS & IT Topic wise PYQs
 Database Design and ER Model Database Management System

5. (d) 8. (a) 10. (c)

Relationship R is many to one and participation of A

in R is total.

The relationship between strong entity set and weak

entity set is represented by double/bold diamond.
11. (4 to 4)

9. (a)

7. (d) Cardinality of E1 > Cardinality of E2

Statement S1 is false as a foreign key declaration

A relation scheme can have zero or more foreign cannot be replaced by check assertion in SQL,
key. because foreign key may have duplicate and Null
A foreign key in a relation scheme R can be used values and having ON DELETE cascade
to refer to tuples of R. operation then if any primary key deleted from
referential relation then the referential integrity
constraint may not be maintained.
Statement S2: R(a b c) (a) Correct. Every entity of E1 is associated with
R12 is many to many relationships between E1 and E2.
exactly one entity in E2.
Here ab is primary key So, separate table is created
( its total participation and many to one from E1
S(a d e) to E2). E1 (a11 a12) E2 (a21 a22) R12 (a11 a21)
6. (0 to 0)
Primary key : d, foreign key a references R, (b) Incorrect. Because mention some entity in E1 a11 a12 a21 a22
On delete cascade: Whenever primary key is deleted
but here total participation and mention more
from referenced (parent) table then that related value foreign key reference to the primary key of E3 R13(a31 a32 a11) a31 a32a11
than one entity in E2 but here many to one (of E2
(Tuple/Row) (that primary key value) from referenced relation R. But in referenced relation side). 3NF (Third normal form): Let R be the relational
referencing (CHILD table) has to be deleted. ab is primary key, only a is not a primary key so (c) Incorrect. Because there is a many to one from schema and X Y is non trivial FD is in 3NF
here foreign key 'a' not references key (ab) of E1 to E2, not from E2 to E1.
On delete set NULL: Whenever primary key is (d) Incorrect. Because there is a many to one from If X: super key
deleted from reference (parent) table then that related referenced relation R. therefore S2 is false.
E1 to E2. or
value (Tuple/Row) in referencing [CHILD table
(which contain foreign key)] is set to NULL. Y: prime attribute

By default foreign key references to the primary key E1 (a11 a12) a11 a12
of referenced relation. E2 (a21 a22) a21 a22

1.5 1.6
GATE Wallah CS & IT Topic wise PYQs GATE Wallah CS & IT Topic wise PYQs
 Database Design and ER Model Design of springs

R12 (a11a21) 13. (b)

E3R13 (a31 a32 a11)a31 a32a11
CHAPTER
X is a super key

2
So, R is in 3NF
Minimum 4 table required

Finding Number of Candidate Key 5. [MCQ] [GATE-2013 : 2M]

Relation R has eight attributes ABCDEFGH.
1. [NAT] [GATE-2022 : 1M]
12. (c) Fields of R contain only atomic values.
Consider a relation R(A, B, C, D, E) with the
(a) Statement given is correct with respect to ER F = {CH G, A BC, B CFH, E A, F EG}
following three functional dependencies.
diagram. MR1P many to one and total participation at is a set of functional
(b) Statement given is correct with respect to ER AB C ; BC D;C E; dependencies (FDs) so that F+ is exactly the set of FDs
many side
diagram. The number of super keys in the relation R is that hold for R.
, here P1 is a foreign key
_________. How many candidate keys does the relation R have?
P(P1 P2)
(a) 3 (b) 4
, here P1 is a foreign key 2. [MCQ] [GATE-2016 : 1M] (c) 5 (d) 6
Which of the following is NOT a super key in a
relational schema with attributes V, W, X, Y, Z and Membership Set and Equality between FD Sets
primary key VY ?
6. [MSQ] [GATE-2021 : 2M]
(a) V X Y Z (b) V W X Z
Suppose the following functional dependencies hold
(c) V W X Y (d) V W X Y Z
on a relation U with attributes P, Q, R, S, and T:

14. (a) 3. [NAT] [GATE-2014 : 1M] P QR

The maximum number of superkeys for the relation RS T

schema R (E, F, G, H) with E as the key is Which of the following functional dependencies can
(c) In ER diagram multivalued attribute and ______________. be inferred from the above functional dependencies?
composite attribute both are allowed, but in
(a) PS T (b) R T
RDBMS Multivalued attribute and composite
4. [MCQ] [GATE-2014 : 1M] (c) P R (d) PS Q
attribute are not allowed.
Each Tuple in RDBMS can have only one value MR1(M1 M2 M3 P1), here P1 is a foreign key Consider the relation scheme R = (E, F, G, H, I, J, K,
or NULL for each field (Attribute exactly) P(P1 P2), P1 is primary key L, M, N ) and the set of functional dependencies 7. [MCQ] [GATE-2015 : 1M]
So, option (c) is incorrect. NR2(N1N2P1), here P1 is a foreign key {{E, F}} {G}, {F} {I, J},{E, H} {K, L}, Consider the relation X(P, Q, R, S, T, U) with the
{K} {M},{L} {N}} on R. following set of functional dependencies
What is the key for R? F = { {P, R} {S, T}; {P, S, U} {Q, R};}
(a) {E, F} Which of the following is the trivial functional
(b) {E, F, H} dependency in F+ is closure of F ?
(c) {E, F, H, K, L} (a) {P, R} {S, T} (b) {P, R} {R, T}
(d) {E} (c) {P, S} {S} (d) {P, S, U} {Q}

1.7 1.8
GATE Wallah CS & IT Topic wise PYQs GATE Wallah CS & IT Topic wise PYQs
 Functional Dependency and Normalization Database Management System

Minimal Cover X = (PQRS). X is not in BCNF. Suppose X is (a) R has a non-trivial functional dependency Which one of the relational schemas above is in 3 NF
decomposed into two schemas Y and Z, where Y = (P X A, where X is not a superkey and A is a but not in BCNF?
8. [MCQ] [GATE-2017 : 2M] R) and Z = (Q R S). prime attribute. (a) Schema I (b) Schema II
The following functional dependencies hold true for Consider the two statements given below. (b) R has a non-trivial functional dependency (c) Schema III (d) Schema IV
the relational schema R {V, W, X, Y, Z}: I. Both Y and Z are in BCNF X A, where X is not a superkey and A is a non-
[V W; VW X; Y VX; Y Z] II. Decomposition of X into Y and Z is dependency prime attribute and X is not a proper subset of any 15. [MCQ] [GATE-2014 : 2M]
preserving and lossless key.
Which of the following is irreducible equivalent for Given the following two statements:
Which of the above statements is/are correct? (c) R has a non-trivial functional dependency
this set of functional dependencies?
(a) Both I and II (b) I only S1: Every table with two single-valued attributes is in
(a) (b) X A, where X is not a superkey and A is a non-
(c) II only (d) Neither I nor II 1 NF, 2 NF, 3 NF and BCNF.
prime attribute and X is a proper subset of some
key. S2: AB C, D E, E C is a minimal cover for
11. [MCQ] [GATE-2008 : 2M] (d) A cell in R holds a set instead of an atomic value. the set of functional dependencies AB C,
Let R(A, B, C, D) be a relational schema with the D E, AB E, E C.
(c) (d) following functional dependencies: 14. [MCQ] [GATE-2018 : 2M] Which one of the following is CORRECT?
A B, B C, C D and D B Consider the following four relational schemas. For (a) S1 is TRUE and S2 is FALSE.
The decomposition of R into (A, B),(B, C), (B, D) each schema, all non-trivial functional dependencies (b) Both S1 and S2 are TRUE.
(a) Gives a lossless join, and is dependency are listed. The underlined attributes are the respective
(c) S1 is FALSE and S2 TRUE.
preserving primary keys.
(b) Gives a lossless join, but is not dependency (d) Both S1 and S2 are FALSE.
Schema I:
preserving Registration (rollno, courses)
Properties of Decomposition (c) Does not give a lossless join, but is dependency 16. [MCQ] [GATE-2013 : 2M]
Field 'courses' is a set-valued attribute containing the
9. [MCQ] [GATE-2021 : 2M] preserving Relation R has eight attributes ABCDEFGH.
set of courses a student has registered for.
(d) Does not give a lossless join and is not Fields of R contain only atomic values.
Consider the relation R(P, Q, S, T, X, Y, Z, W) with Non-trivial functional dependency:
dependency preserving.
the following functional dependencies F = {CH G, A BC, B CFH, E A, F E
rollno courses
PQ X;P YX ; Q Y;Y ZW G} is a set of functional dependencies (FDs) so that
Normal Form Schema II:
Consider the decomposition of the relation R into the F+ is exactly the set of FDs that hold for R.
Registration (rollno, courseid, email)
constituent relations according to the following two 12. [MCQ] [GATE-2022 : 1M] The relation R is
Non-trivial functional dependencies:
decomposition schemes In a relational data model, which one of the following (a) in 1 NF, but not in 2 NF.
D1: R = [(P, Q, S, T); (P, T, X); (Q, Y); (Y, Z, W)] statements is TRUE? rollno, courseid email
(b) in 2 NF, but not in 3 NF.
D2: R = [(P, Q, S); (T, X); (Q, Y); (Y, Z, W)] (a) A relation with only two attributes is always in email rollno
(c) in 3 NF, but not in BCNF.
BCNF. Schema III:
Which one of the following options is correct? (d) in BCNF.
(b) If all attributes of a relation are prime attributes, Registration (rollno, courseid, marks, grade)
(a) D1 is a lossless decomposition, but D2 is a lossy
then the relation is in BCNF.
decomposition. Non-trivial functional dependencies: 17. [MCQ] [GATE-2012 : 1M]
(c) Every relation has at least one non-prime
(b) D1 is a lossy decomposition, but D2 is a lossless rollno, courseid marks, grade
attribute. Which of the following is TRUE?
decomposition.
(d) BCNF decompositions preserve functional marks grade (a) Every relation in 3 NF is also in BCNF
(c) Both D1 and D2 are lossless decompositions.
dependencies. Schema IV: (b) A relation R is in 3 NF if every non-prime
(d) Both D1 and D2 are lossy decompositions.
Registration (rollno, courseid, credit) attribute of R is fully functionally dependent on
13. [MCQ] [GATE-2020 : 2M] every key of R
10. [MCQ] [GATE-2019 : 2M] Non-trivial functional dependencies:
Consider a relational table R that is in 3 NF, but not in
Let the set of functional dependencies F = {QR S, rollno, courseid credit (c) Every relation in BCNF is also in 3 NF
BCNF. Which one of the following statements is
R P, S Q} hold on a relation schema courseid credit (d) No relation can be in both BCNF and 3 NF
TRUE?

1.9 1.10
GATE Wallah CS & IT Topic wise PYQs GATE Wallah CS & IT Topic wise PYQs