In this article you will get DBMS GATE Questions 2020 with answers.
1. Consider a relational database containing the following schema.
The primary key of each table is indicated by underlying the constituent fields.
SELECT s.sno, s.sname
FROM Suppliers s, Catalogue c
WHERE s.sno = c.sno AND
Cost > (SELECT AVG (cost)
FROM Catalogue
WHERE pno = ‘P4’
GROUP BY pno);
The number of rows returned by the above SQL query is :
a. 0
b. 5
c. 4
d. 2
Answer : c)
2. Which one of the following is used to represent the supporting many-one relationships of a weak entity set in an entity-relationship diagram?
a. Ovals that contain underlined identifiers
b. Diamonds with double/bold border
c. Ovals with double/bold border
d. Rectangles with double/bold border
Answer : b)
3. Consider a schedule of transactions T1 and T2:
| T1 | RA | RC | WD | WB | Commit | |||||
| T2 | RB | WB | RD | WC | commit |
Here, RX stands for “Read(X)” and WX stands for “Write(X)”. Which one of the following schedules is conflict equivalent to the above schedule?
a.
| T1 | RA | RC | WD | WB | Commit | |||||
| T2 | RB | WB | RD | WC | commit |
b.
| T1 | RA | RC | WD | WB | Commit | |||||
| T2 | RB | WB | RD | WC | commit |
c.
| T1 | RA | RC | WD | WB | Commit | |||||
| T2 | RB | WB | RD | WC | commit |
d.
| T1 | RA | RC | WD | WB | Commit | |||||
| T2 | RB | WD | RD | WC | commit |
Answer: b)
4. Consider a relational table R that is in 3NF, but not in BCNF. Which one of the following statements is TRUE?
a. A cell in R holds a set instead of an atomic value.
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 a proper subset of some 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 not a proper subset of any key.
d. R has a nontrivial functional dependency X–>A, where X is not a superkey and A is a prime attribute.
Answer : d)
5. Consider a database implemented using B+ tree for file indexing and installed on a disk drive with a block size of 4 KB. The size of the search key is 12 bytes and the size of the tree/disk pointer is 8 bytes. Assume that the database has one million records. Also, assume that no node of the B+ tree and no records are present initially in the main memory. Consider that each record fits into one disk block. The minimum number of disk accesses required to retrieve any record in the database is ______.
a. 4
b. 5
c. 6
d. 7
Answer : a)