Showing posts with label GATE questions on DBMS. Show all posts
Showing posts with label GATE questions on DBMS. Show all posts
Monday, March 28, 2022
GATE Questions - DBMS - File Structures and Indexing
No comments:
Labels:
GATE,
GATE questions on DBMS
Previous GATE questions with solutions on DBMS (File Structures and Indexing) - CS/IT
GATE-2013
1. An index is clustered, if
(a) it is on a set of fields that form a candidate key.
(b) it is on a set of fields that include the primary key.
(c) the data records of the file are organized in the same order as the data entries of the index.
(d) the data records of the file are organized not in the same order as the data entries of the index.
Ans: option (c)
Explanation :
With a clustered index the physical records (rows) are stored on the disk in the same order as that of index. Hence every table can have exactly one clustered index.
GATE-2013
1. An index is clustered, if
(a) it is on a set of fields that form a candidate key.
(b) it is on a set of fields that include the primary key.
(c) the data records of the file are organized in the same order as the data entries of the index.
(d) the data records of the file are organized not in the same order as the data entries of the index.
Ans: option (c)
Explanation :
With a clustered index the physical records (rows) are stored on the disk in the same order as that of index. Hence every table can have exactly one clustered index.
GATE - 2015
2. With reference to the B+ tree index of order 1 shown below, the minimum number of nodes (including the root node) that must be fetched in order to satisfy the following query: “Get all records with a search key greater than or equal to 7 and less than 15” is ________.
2. With reference to the B+ tree index of order 1 shown below, the minimum number of nodes (including the root node) that must be fetched in order to satisfy the following query: “Get all records with a search key greater than or equal to 7 and less than 15” is ________.
Ans: 5
Explanation:
In a B+ tree, data pointers are stored only at the leaf nodes. The leaf nodes contain the value of the search field as well as a pointer to the record/block that contains the record.In order to provide ordered access, the leaf nodes have been linked.
Explanation:
In a B+ tree, data pointers are stored only at the leaf nodes. The leaf nodes contain the value of the search field as well as a pointer to the record/block that contains the record.In order to provide ordered access, the leaf nodes have been linked.
In the above question, in order to fetch all records with key values greater than or equal to 7 and less than 15, we first need to locate the leaf node that stores 7.To reach the node with key 7,we start from root with key value 9,follow left pointer to arrive at a node with key 5 (since search key 7 is less than 9). From node with key 5, we follow the right pointer to reach the leaf node with key value 7. Now we move from 7 till 15 sequentially towards right, thus traversing another 2 nodes.
Thus total number of nodes fetched in order to satisfy the query is 5.
Thus total number of nodes fetched in order to satisfy the query is 5.
GATE - 2015
3. A file is organized so that the ordering of data records is the same as or close to the ordering of data entries in some index. Then that index is called
(a) Dense
(b) Sparse
(c) Clustered
(d) Unclustered
Ans: option (c)
Explanation:
With a clustered index the physical records (rows) are stored on the disk in the same order as that of index . Hence every table can have exactly one clustered index.
3. A file is organized so that the ordering of data records is the same as or close to the ordering of data entries in some index. Then that index is called
(a) Dense
(b) Sparse
(c) Clustered
(d) Unclustered
Ans: option (c)
Explanation:
With a clustered index the physical records (rows) are stored on the disk in the same order as that of index . Hence every table can have exactly one clustered index.
Tuesday, December 8, 2015
GATE Questions-DBMS-Functional Dependency
29 comments:
Labels:
GATE,
GATE questions on DBMS
Previous GATE questions with solutions on DBMS (Functional Dependency) - CS/IT
GATE-1999
1. 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 for R?
(a) CD (b) EC (c) AE (d) AC
Ans: option (b)
Explanation:
Find the closure set of all the options given. If any closure covers all the attributes of the relation R then that is the key.
Algorithm to find Closure Set
Step1: Equate an attribute or attributes to X for which closure needs to be identified.
Step2: Take each FD (functional dependency) one by one and check whether the left side of FD is available in X, if yes then add the right side attributes to X if it is not available.
Step3: Repeat step 2 as many times as possible to cover all FD's.
Step4: After no more attributes can be added to X declare it as the closure set.
FDs: C->F, E->A, EC->D, A->B
Find closure set for CD.
X = CD
= CDF {C->F}
No more attributes can be added to X. Hence closure set of CD = CDF
Find closure set for EC.
X = EC
= ECF {C->F}
= ECFA {E->A}
= ECFAD {EC->D}
= ECFADB {A->B}
Closure set of EC covers all the attributes of the relation R.
GATE-1999
1. 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 for R?
(a) CD (b) EC (c) AE (d) AC
Ans: option (b)
Explanation:
Find the closure set of all the options given. If any closure covers all the attributes of the relation R then that is the key.
Algorithm to find Closure Set
Step1: Equate an attribute or attributes to X for which closure needs to be identified.
Step2: Take each FD (functional dependency) one by one and check whether the left side of FD is available in X, if yes then add the right side attributes to X if it is not available.
Step3: Repeat step 2 as many times as possible to cover all FD's.
Step4: After no more attributes can be added to X declare it as the closure set.
FDs: C->F, E->A, EC->D, A->B
Find closure set for CD.
X = CD
= CDF {C->F}
No more attributes can be added to X. Hence closure set of CD = CDF
Find closure set for EC.
X = EC
= ECF {C->F}
= ECFA {E->A}
= ECFAD {EC->D}
= ECFADB {A->B}
Closure set of EC covers all the attributes of the relation R.
Saturday, December 5, 2015
GATE 2015 Questions - DBMS
No comments:
Labels:
GATE,
GATE questions on DBMS
GATE-2015
1. Consider the following transaction involving two bank
accounts x and y.
read (x) ; x := x – 50; write (x); read (y); y := y + 50; write (y)
The constraint that the sum of the accounts x and y should remain constant isthat of
(a) Atomicity
(b) Consistency
(c) Isolation
(d) Durability
(a) Atomicity
(b) Consistency
(c) Isolation
(d) Durability
Answer: option (b)
Sunday, January 20, 2013
GATE Questions - DBMS - Normalization
38 comments:
Labels:
GATE,
GATE questions on DBMS
Previous GATE questions with solutions on DBMS (Normalization) - CS/IT
Normalisation is a technique for producing relational schema with no information redundancy and no anomalies (insert, update or delete anomalies)
GATE-1998
1. Which normal form is considered adequate for normal relational database design?
(a) 2NF (b) 5NF (c) 4NF (d) 3NF
Ans: option (d)
Explanation:
Normalisation is a technique for producing relational schema with no information redundancy and no anomalies (insert, update or delete anomalies)
GATE-1998
1. Which normal form is considered adequate for normal relational database design?
(a) 2NF (b) 5NF (c) 4NF (d) 3NF
Ans: option (d)
Explanation:
A relational database table is often described as "normalized" if it is in the Third Normal Form because most of the 3NF tables are free of insertion, update, and deletion anomalies.
Friday, January 18, 2013
GATE Questions-DBMS-ER Diagram
4 comments:
Labels:
GATE,
GATE questions on DBMS
Previous GATE questions with solutions on DBMS (ER Diagram) - CS/IT
GATE-2008
Linked Answer Questions 1 & 2
1. Consider the following ER diagram.
The minimum number of tables needed to represent M, N, P, R1, R2 is
(a) 2 (b) 3 (c) 4 (d) 5
Ans: confused between option (a) & option (b) { majority of the people refer option (a) as correct }
Explanation:
for option(a)
check explanation of question (2) of this link http://www.geeksforgeeks.org/database-management-systems-set-7/
for option (b)
All strong entities and weak entities will be converted into a table. Therefore we will have 3 tables:
M (M1,M2,M3,P1)
P (P1,P2)
N (N1,N2,P1) =>N is a weak entity and it is modified to include the primary key of P (i.e. P1).
GATE-2008
Linked Answer Questions 1 & 2
1. Consider the following ER diagram.
(a) 2 (b) 3 (c) 4 (d) 5
Ans: confused between option (a) & option (b) { majority of the people refer option (a) as correct }
Explanation:
for option(a)
check explanation of question (2) of this link http://www.geeksforgeeks.org/database-management-systems-set-7/
for option (b)
All strong entities and weak entities will be converted into a table. Therefore we will have 3 tables:
M (M1,M2,M3,P1)
P (P1,P2)
N (N1,N2,P1) =>N is a weak entity and it is modified to include the primary key of P (i.e. P1).
GATE Question - DBMS - SQL
8 comments:
Labels:
GATE,
GATE questions on DBMS
Previous GATE questions with solutions on DBMS (SQL) - CS/IT
GATE-2010
1. A relational schema for a train reservation database is given below.
Passenger (pid, pname, age)
Reservation (pid, class, tid)
What pids are returned by the following SQL query for the above instance of the tables?
Ans: option (c)
Explanation:
GATE-2010
1. A relational schema for a train reservation database is given below.
Passenger (pid, pname, age)
Reservation (pid, class, tid)
Table: Passenger
|
Table : Reservation
|
What pids are returned by the following SQL query for the above instance of the tables?
SELECT pid
FROM Reservation
WHERE class ‘AC’ AND
EXISTS (SELECT *
FROM Passenger
WHERE age > 65 AND
Passenger. pid = Reservation.pid)
(a) 1, 0 (b) 1, 2 (c) 1, 3 (d) 1, 5Ans: option (c)
Explanation:
The above query is an example of synchronized subquery or correlated subquery. A correlated sub-query is a sub-query that uses values from the outer query. The sub-query is evaluated once for each row processed by the outer query.
Subscribe to:
Posts (Atom)