Test 1 - Databases | Computer Science(CS)
Description: GATE Previous year Topic Wise Questions and Answers | Databases | |
Number of Questions: 27 | |
Created by: Aliensbrain Bot | |
Tags: Databases GATE CS |
Consider three data items D1, D2 and D3, and the following execution schedule of transactions T1, T2 and T3. In the diagram, R(D) and W(D) denote the actions reading and writing in the data item D respectively.
Which of the following statements is correct?
Consider the following SQL query. select distinct a1, a2, ..., an from r1, r2, ..., rm where P
For an arbitrary predicate P, this query is equivalent to which of the following relational algebra expressions?
Consider the set of relations shown below and the SQL query that follows. Students: (Roll_number, Name, Date_of_birth) Courses: (Course number, Course_name, Instructor) Grades: (Roll_number, Course_number, Grade) select distinct Name from Students, Courses, Grades where Students. Roll_number = Grades.Roll_number and Courses.Instructor = Korth and Courses.Course_number = Grades.Course_number and Grades.grade = A Which of the following sets is computed by the above query?
Which of the following scenarios may leave the transaction vulnerable to dirty reads, phantom reads, etc. in a database system?
Consider the relation account (customer, balance) where customer is a primary key and there are no null values. We would like to rank customers according to decreasing balance. The customer with the largest balance gets rank 1. Ties are not broken but ranks are skipped: if exactly two customers have the largest balance they each get rank 1 and rank 2 is not assigned.
Consider these statements about Query 1 and Query 2.
- Query 1 will produce the same row set as Query 2 for some but not all databases.
- Both Query 1 and Query 2 are correct implementation of the specification.
- Query 1 is a correct implementation of the specification but Query 2 is not.
- Neither Query 1 nor Query 2 is a correct implementation of the specification.
- Assigning rank with a pure relational query takes less time than scanning in decreasing balance order assigning ranks using ODBC.
Which two of the above statements are correct?
A clustering index is defined on the fields which are of the type
Consider the following functional dependencies in a database.
Date_of_Birth $\rightarrow$ Age Age $\rightarrow$ Eligibility Name $\rightarrow$ Roll_number Roll_number $\rightarrow$ Name Course_number $\rightarrow$ Course_name Course_number $\rightarrow$ Instructor (Roll_number, Course_number) $\rightarrow$ Grade
The relation (Roll_number, Name, Date_of_Birth, Age) is
Consider the following log sequence of two transactions on a bank account, with initial balance of Rs. 12,000, that transfers 2000 to a mortgage payment and then applies a 5% interest.
- T1 start
- T1 B old = 1200 new = 10000
- T1 M old = 0 new = 2000
- T1 commit
- T2 start
- T2 B old = 10000 new = 10500
- T2 commit
Suppose the database system crashes just before log record 7 is written. When the system is restarted, which statement is true for the recovery procedure?
Information about a collection of students is given by the relation studinfo (studId, name, sex). The relation enroll (studId, courseId) gives which student has enrolled for (or taken) what course(s). Assume that every course is taken by at least one male and at least one female student. What does the following relational algebra expression represent?
$\pi {courceId}\left(\left(\pi{\text{studId}}\left(\sigma_{sex="female"}\left(\text{studInfo}\right)\right) \times \pi_{courseId}\left(\text{enroll}\right)\right) -\text{enroll}\right)$
Consider the table employee (empId, name, department, salary) and the two queries Q1, Q2 below. Assuming that department 5 has more than one employee, and we want to find the employees who get higher salary in department 5, which one of the statements is TRUE for any arbitrary employee table? Q1 : Select e. empId From employee Where not exists (Select * From employee s where s. department = “5” and s. salary > = e. salary) Q2 : Select e. empId From employee e Where e. salary > any (select distinct salary From employee s where s. Where s. department = “5”)
Consider the following E-R diagram
The minimum number of tables needed to represent M, N, P, R1, R2 is
Consider a file of 16384 records. Each record is 32 bytes long and its key field is of size 6 bytes. The file is ordered on a non-key field and the file organisation is unspanned. The file is stored in a file system with block size 1024 bytes and the size of a block pointer is 10 bytes. If the secondary index is built on the key field of the file and a multi-level index scheme is used to store the secondary index, then the numbers of first-level and second-level blocks in the multi-level index are respectively
Consider the following schedules involving two transactions. Which one of the following statements is TRUE?
S1 : r1 (X); r1 (Y); r2 (X); r2 (Y); w2 (Y); W1 (X) S2 : r1 (X); r2 (Y); r2 (X); w2 (Y); r1 (Y); W1 (X)
Let R and S be two relations with the following schema R (P, Q, R1, R2, R3) S(P, Q, S1, S2) Where {P, Q} is the key for both schemas. Which of the following queries are equivalent?
I. $\Pi_P \left(R \bowtie S\right)$ II. $\Pi_P \left(R\right) \bowtie \Pi_P\left(S\right)$ III. $\Pi_P \left(\Pi_{P, Q} \left(R\right) \cap \Pi_{P,Q} \left(S\right) \right)$ IV. $\Pi_P \left(\Pi_{P, Q} \left(R\right) - \left(\Pi_{P,Q} \left(R\right) - \Pi_{P,Q} \left(S\right)\right)\right)$
Consider the following relational schemes for a library database: Book Title, Author, Catalog_ no, Publisher, Year, Pr ice Collection Title, Author, Catalog_ no within the functional dependencies: I. Title Author $\rightarrow$ Catalog_no II. Catalog_no $\rightarrow$ Title Author Publisher Year III. Publisher Title Year $\rightarrow$ Pr ice
Assume {Author, Title} is the key for both schemes. Which of the following statements is true?
Consider the following E-R diagram
Which of the following is a correct attribute set for one of the tables for the correct answer to the above question?
The following key values are inserted into a B+ - tree in which order of the internal nodes is 3, and that of the leaf nodes is 2, in the sequence given below. The order of internal nodes is the maximum number of tree pointers in each node, and the order of leaf nodes is the maximum number of data items that can be stored in it. The B+ - tree is initially empty. 10, 3, 6, 8, 4, 2, 1 The maximum number of times leaf nodes would get split as a result of these insertions is
Consider the relation employee (name, sex, supervisor Name) with name as the key; supervisor Name gives the name of the supervisor of the employees under consideration. What does the following Tuple Relational Calculus query produce? { e. name| employee (e) $\land$ $\forall X$[$\neg$employee (x) $\lor$ x.supervisorName $\ne$e.name $\lor$ x.sex = “male”] }
Consider two transactions T1 and T2 and four schedules S1, S2, S3 and S4 of T1 and T2 as given below: T1 : R1 [x] W1 [x] W1 [y] T2 : R2 [x] R2 [y] W2 [y] S1 : R1 [x] R2 [x] R2 [y] W1 [x] W1 [y] W2 [y] S2 : R1 [x] R2 [x] R2 [y] W1 [x] W2 [y] W1 [y] S3 : R1 [x] W1 [x] R2 [x] W1 [y] R2 [y] W2 [y] S4 : R2 [x] R2 [y] R1 [x] W1 [x] W1 [y] W2 [y]
Which of the above schedules is/are conflict-serializable?
Let R and S be relational schemes such that R={a,b,c} and S={c}. Now consider the following queries on the database:
I. $\pi_{R-S}(r) - \pi_{R-S} \left (\pi_{R-S} (r) \times s - \pi_{R-S,S}(r)\right )$ II. $\left\{t \mid t \in \pi_{R-S} (r) \wedge \forall u \in s \left(\exists v \in r \left(u = v[S] \wedge t = v\left[R-S\right]\right )\right )\right\}$ III. $\left\{t \mid t \in \pi_{R-S} (r) \wedge \forall v \in r \left(\exists u \in s \left(u = v[S] \wedge t = v\left[R-S\right]\right )\right ) \right\}$ IV.
Select R.a, R.b
From R,S
Where R.c=S.c
Which of the above queries are equivalent?
Which one of the following statements is FALSE?
The order of a leaf node in a B+ - tree is the maximum number of (value, data record pointer) pairs it can hold. Given that the block size is 1K bytes, data record pointer is 7 bytes long, the value field is 9 bytes long and a block pointer is 6 bytes long, what is the order of the leaf node?
Consider the following functional dependencies: AB$\rightarrow$CD, AF $\rightarrow$ D,DE $\rightarrow$F,C $\rightarrow$G, F $\rightarrow$E,G $\rightarrow$A. Which one of the following options is false?
Consider the relation enrolled (student, course) in which (student, course) is the primary key, and the relation paid (student, amount) where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Given the following four queries: Query1:select student from enrolled where student in (select student from paid) Query2:select student from paid where student in (select student from enrolled) Query3:select E.student from enrolled E, paid P where E.student = P.student Query4:select student from paid where exists (select * from enrolled where enrolled. student = paid. student) Which one of the above statements is correct?
Consider the relation enrolled (student, course) in which (student, course) is the primary key, and the relation paid (student, amount) where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Assume that amounts 6000, 7000, 8000, 9000 and 10000 were each paid by 20% of the students. Consider these query plans (Plan 1 on left, Plan 2 on right) to “list all courses taken by students who have paid more than x”
A disk seek takes 4ms, disk data transfer bandwidth is 300 MB/s and checking a tuple to see if amount is greater than x takes 10Zs. Which of the following statements is correct?
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 dependency is implied other than those implied by primary and candidate keys.
Which of the following statements is TRUE about the above schema?
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) Consider the following relational query on the above database: SELECT S.sname FROM Suppliers S WHERE S.sid NOT IN (SELECT C.sid FROM Catalog C WHERE C.pid NOT (SELECT P.pid FROM Parts P WHERE P.color<> 'blue'))
Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?