UGC NET · PAPER 2
UGC NET Paper 2 Computer Science – Unit 4: Database Management Systems PYQ (September 2022)
Directions (Q31–35): Consider the relational schema of Sailors S, Reserves R and Boats B.
Table 1: Sailors
| Sid | Sname | Rating | Age |
|---|---|---|---|
| 22 | Dustin | 7 | 45.0 |
| 29 | Brutus | 1 | 33.0 |
| 31 | Lubber | 8 | 55.5 |
| 32 | Andy | 8 | 25.5 |
| 58 | Rusty | 10 | 35.5 |
| 64 | Horatio | 7 | 35.5 |
| 71 | Zorba | 10 | 16.0 |
| 74 | Horatio | 9 | 35.0 |
| 85 | Art | 3 | 25.5 |
| 95 | Bob | 3 | 63.5 |
Table 2: Reserves
| Sid | Bid | Day |
|---|---|---|
| 22 | 101 | 10/10/98 |
| 22 | 102 | 10/10/98 |
| 22 | 103 | 10/8/98 |
| 22 | 104 | 10/7/98 |
| 31 | 102 | 11/10/98 |
| 31 | 103 | 11/6/98 |
| 31 | 104 | 11/12/98 |
| 64 | 101 | 9/5/98 |
| 64 | 102 | 9/8/98 |
| 74 | 103 | 9/8/98 |
Table 3: Boats
| Bid | Bname | Color |
|---|---|---|
| 101 | Interlake | blue |
| 102 | Interlake | red |
| 103 | Clipper | green |
| 104 | Marine | red |
Q31. Which relational algebra query computes the sids of sailors with age over 20 who have not reserved a red boat?
- πsid(σage>20 Sailors) − πsid((σcolor='red' Boats) ⋈ Reserves ⋈ Sailors)
- πsid(σcolor='red' ∧ age=20(Boats ⋈ Sailors ⋈ Reserves))
- πsid(σage<20 Sailors) − πsid((σcolor='red' Boats) ⋈ Reserves ⋈ Sailors)
- πsid(σage>20 Sailors) ∧ πsid((σcolor='red' Boats) ⋈ Reserves ⋈ Sailors)
Q32. Which query/queries compute the names of sailors who have reserved a red boat?
Q1: πsname((σcolor='red' Boats) ⋈ Reserves ⋈ Sailors)
Q2: πsname(πsid((πbid σcolor='red' Boats) ⋈ Reserves) ⋈ Sailors)
Q3: πsname((σcolor='red' Reserves) ⋈ Boats ⋈ Sailors)
Q1: πsname((σcolor='red' Boats) ⋈ Reserves ⋈ Sailors)
Q2: πsname(πsid((πbid σcolor='red' Boats) ⋈ Reserves) ⋈ Sailors)
Q3: πsname((σcolor='red' Reserves) ⋈ Boats ⋈ Sailors)
- Both Q1 and Q2
- Both Q2 and Q3
- Only Q1
- Only Q2
Q33. Which query/queries compute the names of sailors who have reserved boat 103?
Q1: πsname((σbid=103 Boats) ⋈ Sailors)
Q2: πsname(σbid=103(Reserves ⋈ Sailors))
Q3: πsname((σbid=103 Reserves) ⋈ Sailors)
Q1: πsname((σbid=103 Boats) ⋈ Sailors)
Q2: πsname(σbid=103(Reserves ⋈ Sailors))
Q3: πsname((σbid=103 Reserves) ⋈ Sailors)
- Both Q1 and Q3
- Both Q2 and Q3
- Only Q3
- Only Q2
Q34. Which query computes the names of sailors who have reserved all boats?
- ρ(Tempsids, (πbid Reserves) ÷ (πbid Boats)); πsname(Tempsids ⋈ Sailors)
- ρ(Tempsids, (πsid, bid Reserves) ÷ (πbid Boats)); πsname(Tempsids ⋈ Sailors)
- ρ(Tempsids, (πsid Sailors) ÷ (πbid Boats)); πsname(Tempsids ⋈ Sailors)
- ρ(Tempsids, (πsid Reserves) ÷ (πbid Boats)); πsname(Tempsids ⋈ Boats)
Q35. Which query computes the names of sailors who have reserved a red and a green boat?
- ρ(Tempred, πsid((σcolor='red' Boats) ⋈ Reserves)); ρ(Tempgreen, πsid((σcolor='green' Boats) ⋈ Reserves)); πsname((Tempred ∩ Tempgreen) ⋈ Sailors)
- ρ(Tempboats2, (σcolor='red' Boats) ∩ (σcolor='green' Boats)); πsname(Tempboats2 ⋈ Reserves ⋈ Sailors)
- πsname(((σcolor='red' Boats) ∩ (σcolor='green' Boats)) ⋈ Reserves ⋈ Sailors)
- ρ(Tempboats2, σcolor='red' ∧ color='green' Boats); πsname(Tempboats2 ⋈ Reserves ⋈ Sailors)
Q61. Let R(ABCDEFGH) be a relation schema and F = {A → B, ABCD → E, EF → G, EF → H, ACDF → EG} the set of dependencies. The minimal cover of F is
- A → B, ACD → E, EF → G, EF → H
- A → B, ACD → E, EF → G, EF → H, ACDF → G
- A → B, ACD → E, EF → G, EF → H, ACDF → E
- A → B, ABCD → E, EF → H, EF → G
Q62. A trigger is
- a statement that enables the DBMS to start
- a statement that is executed by the user when debugging an application program
- a condition the system tests for the validity of the database user
- a statement that is executed automatically by the system as a side effect of a modification to the database
Q75. In reference to Big Data, consider the following databases:
(A) Memcached
(B) CouchDB
(C) InfiniteGraph
Choose the most appropriate answer from the options given below:
(A) Memcached
(B) CouchDB
(C) InfiniteGraph
Choose the most appropriate answer from the options given below:
- (A) and (B) only
- (B) and (C) only
- (C) and (A) only
- (A), (B) and (C)
Q76. Match List I with List II.
Choose the correct answer from the options given below:
Choose the correct answer from the options given below:
| List I | List II |
|---|---|
| A. BCNF | I. It removes multivalued dependency. |
| B. 3NF | II. It is not always dependency preserving. |
| C. 2NF | III. It removes transitive dependency. |
| D. 4NF | IV. It removes partial functional dependency. |
- A-III, B-II, C-IV, D-I
- A-II, B-IV, C-I, D-III
- A-II, B-III, C-IV, D-I
- A-II, B-I, C-IV, D-III
Q88. Consider the following statements:
Statement I: Conservative 2PL is a deadlock-free protocol.
Statement II: Thomas's write rule enforces conflict serializability.
Statement III: The timestamp ordering protocol ensures serializability based on the order of transaction timestamps.
Which of the following is correct?
Statement I: Conservative 2PL is a deadlock-free protocol.
Statement II: Thomas's write rule enforces conflict serializability.
Statement III: The timestamp ordering protocol ensures serializability based on the order of transaction timestamps.
Which of the following is correct?
- Statements I and II true, Statement III false
- Statements I and III true, Statement II false
- Statements I and II false, Statement III true
- Statements I, II and III true
Q89. Consider the following statements:
Statement I: Composite attributes cannot be divided into smaller subparts.
Statement II: A complex attribute is formed by nesting composite attributes and multivalued attributes in an arbitrary way.
Statement III: A derived attribute is an attribute whose values are computed from other attributes.
Which of the following is correct?
Statement I: Composite attributes cannot be divided into smaller subparts.
Statement II: A complex attribute is formed by nesting composite attributes and multivalued attributes in an arbitrary way.
Statement III: A derived attribute is an attribute whose values are computed from other attributes.
Which of the following is correct?
- Statements I, II and III are true
- Statement I true and Statements II, III false
- Statements I, II true and Statement III false
- Statement I false and Statements II, III true