In a B⁺ tree, if the search-key value is 8 bytes long, the block size is 512 bytes and the block pointer size is 2 bytes, then the maximum order of the B⁺ tree is _________.

Which one of the following statements is NOT correct about the B⁺ tree data structure used for creating an index of a relational database table?

Consider the following two statements about database transaction schedules:

• I. Strict two-phase locking protocol generates conflict serializable schedules that are also recoverable.

II. Timestamp-ordering concurrency control protocol with Thomas Write Rule can generate view serializable schedules that are not conflict serializable.

Which of the above statements is/are TRUE?

Consider the following relations P(X,Y,Z), Q(X,Y,T) and R(Y,V).

How many tuples will be returned by the following relational algebra query?

          ∏x(σ(P.Y=R.Y ∧ R.V=V2)(P × R)) - ∏x(σ(Q.Y=R.Y ∧ Q.T>2)(Q × R))

A relational database contains two tables Student and Performance as shown below:

The primary key of the Student table is Roll_no. For the Performance table, the columns Roll_no. and Subject_code together from the primary key. Consider the SQL query given below:

SELECT S.Student_name, sum (P.Marks)
       FROM Student S, Performance P
       WHERE P.Marks > 84
       GROUP BY S.Student_name;

The number of rows returned by the above SQL query is _____.

Let the set of functional dependencies F = {QR → S, R → P, S → Q} hold on a relation schema X = (PQRS). X is not in BCNF. Suppose X is decomposed into two schemas Y and Z, where Y = (PR) and Z = (QRS).

Consider the two statements given below.

• I. Both Y and Z are in BCNF

II. Decomposition of X into Y and Z is dependency preserving and lossless

Which of the above statements is/are correct?

In an Entity-Relationship (ER) model, suppose R is a many-to-one relationship from entity set E1 to entity set E2. Assume that E1 and E2 participate totally in R and that the cardinality of E1 is greater than the cardinality of E2.

Which one of the following is true about R?

Consider the following two tables and four queries in SQL.

Query 1:    SELECT B.isbn, S.copies
            FROM Book B INNER JOIN Stock S
            ON B.isbn = S.isbn;

Query 2:    SELECT B.isbn, S.copies
            FROM Book B LEFT OUTER JOIN Stock S
            ON B.isbn = S.isbn;

Query 3:    SELECT B.isbn, S.copies
            FROM Book B RIGHT OUTER JOIN Stock S
            ON B.isbn = S.isbn;

Query 4:    SELECT B.isbn, S.copies
            FROM Book B FULL OUTER JOIN Stock S
            ON B.isbn = S.isbn;

Which one of the queries above is certain to have an output that is a superset of the outputs of the other three queries?

Consider the following four relational schemas. For each schema, all non-trivial functional dependencies are listed. The underlined attributes are the respective primary keys.

Schema I: Registration(rollno, courses)
          Field ‘courses’ is a set-valued attribute containing the set of 
          courses a student has registered for.
          Non-trivial functional dependency
          rollno → courses

Schema II: Registration (rollno, coursid, email)
          Non-trivial functional dependencies:
          rollno, courseid → email
          email → rollno
 	
Schema III: Registration (rollno, courseid, marks, grade)
          Non-trivial functional dependencies:
          rollno, courseid, → marks, grade
          marks → grade
 	
Schema IV: Registration (rollno, courseid, credit)
          Non-trivial functional dependencies:
          rollno, courseid → credit
          courseid → credit

Which one of the relational schemas above is in 3NF but not in BCNF?

Consider the relations r(A, B) and s(B, C), where s.B is a primary key and r.B is a foreign key referencing s.B. Consider the query

• Q: r⋈(σ

(s))

Let LOJ denote the natural left outer-join operation. Assume that r and s contain no null values.

Which one of the following is NOT equivalent to Q?

The following functional dependencies hold true for the relational schema {V, W, X, Y, Z} :

• V → W

• VW → X

• Y → VX

• Y → Z

Which of the following is irreducible equivalent for this set of functional dependencies?

Consider a database that has the relation schema EMP (EmpId, EmpName, and DeptName). An instance of the schema EMP and a SQL query on it are given below.

The output of executing the SQL query is ___________.

Consider a database that has the relation schemas EMP(EmpId, EmpName, DeptId), and DEPT(DeptName, DeptId). Note that the DeptId can be permitted to a NULL in the relation EMP. Consider the following queries on the database expressed in tuple relational calculus.

• (I) {t│∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∀v ∈ DEPT(t[DeptId] ≠ v[DeptId]))}

• (II) {t│∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∃v ∈ DEPT(t[DeptId] ≠ v[DeptId]))}

(III) {t│∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∃v ∈ DEPT(t[DeptId] = v[DeptId]))}

Which of the above queries are safe?

In a database system, unique timestamps are assigned to each transaction using Lamport’s logical clock. Let TS(T₁) and TS(T₂) be the timestamps of transactions T₁ and T₂ respectively. Besides, T₁ holds a lock on the resource R and T₂ has requested a conflicting lock on the same resource R. The following algorithm is used to prevent deadlocks in the database system assuming that a killed transaction is restarted with the same timestamp.

      
           if TS(T2)<TS(T1) then
                T1 is killed
           else T2 waits.

Assume any transaction that is not killed terminates eventually. Which of the following is TRUE about the database system that uses the above algorithm to prevent deadlocks?

Consider a database that has the relation schema CR(StudentName, CourseName). An instance of the schema CR is as given below.

The following query is made on the database.

         T1 ← πCourseName(σStudentName='SA'(CR))
         T2 ← CR ÷ T1

The number of rows in T2 is ____________.

An ER model of a database consists of entity types A and B. These are connected by a relationship R which does not have its own attribute. Under which one of the following conditions, can the relational table for R be merged with that of A?

Consider the following tables T1 and T2.

In table T1, P is the primary key and Q is the foreign key referencing R in table T2 with on-delete cascade and on-update cascade. In table T2, R is the primary key and S is the foreign key referencing P in table T1 with on-delete set NULL and on-update cascade. In order to delete record 〈3,8〉 from table T1, the number of additional records that need to be deleted from table T1 is _________.

Two transactions T₁ and T₂ are given as

• T

• : r

• (X)w

• (X)r

• (Y)w

• (Y)

• T

• : r

• (Y)w

• (Y)r

• (Z)w

(Z)

where r_i(V) denotes a read operation by transaction T_i on a variable V and w_i(V) denotes a write operation by transaction T_i on a variable V. The total number of conflict serializable schedules that can be formed by T₁ and T₂ is ____________.

Consider the following database table named top_scorer.

SELECT ta.player FROM top_scorer AS ta
WHERE ta.goals > ALL (SELECT tb.goals
                  FROM top_scorer AS tb
                  WHERE tb.country = 'Spain')
AND ta.goals > ANY (SELECT tc.goals
                  FROM top_scorer AS tc
                  WHERE tc.country = 'Germany')

The number of tuples returned by the above SQL query is _____.

Which of the following is NOT a superkey in a relational schema with attributes V, W, X, Y, Z and primary key VY?

NOT a part of the ACID properties of database transactions?

A database of research articles in a journal uses the following schema.

(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, YEAR, PRICE)

The primary key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE) and the following functional dependencies exist in the schema.

• (VOLUME, NUMBER, STARTPAGE, ENDPAGE) → TITLE

• (VOLUME, NUMBER) → YEAR

(VOLUME, NUMBER, STARTPAGE, ENDPAGE) → PRICE

The database is redesigned to use the following schemas.

• (VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE)

(VOLUME, NUMBER, YEAR)

Which is the weakest normal form that the new database satisfies, but the old one does not?

Consider the following two phase locking protocol. Suppose a transaction T accesses (for read or write operations), a certain set of objects{O₁,...,O_k}. This is done in the following manner:

• T acquires exclusive locks to O

• ,...,O

• in increasing order of their addresses.

• The required operations are performed.

All locks are released.

B⁺ Trees are considered BALANCED because

Suppose a database schedule S involves transactions T₁, ..., T_n. Construct the precedence graph of S with vertices representing the transactions and edges representing the conflicts. If S is serializable, which one of the following orderings of the vertices of the precedence graph is guaranteed to yield a serial schedule?

Consider the following database schedule with two transactions, T₁ and T₂.

• S = r

• (X); r

• (X); r

• (Y); w

• (X); r

• (Y); w

• (X); a

• ; a

where r_i(Z) denotes a read operation by transaction T_i on a variable Z, w_i(Z) denotes a write operation by T_i on a variable Z and a_i denotes an abort by transaction T_i.

Which one of the following statements about the above schedule is TRUE?

Consider the following database table named water_schemes :

The number of tuples returned by the following SQL query is _________.

with total(name, capacity) as
   select district_name, sum(capacity)
   from water_schemes
   group by district_name
with total_avg(capacity) as
   select avg(capacity)
   from total
select name
   from total, total_avg
   where total.capacity ≥ total_avg.capacity

SELECT operation in SQL is equivalent to

Consider an Entity-Relationship (ER) model in which entity sets E₁ and E₂ are connected by an m:n relationship R₁₂, E₁ and E₃ are connected by a 1:n (1 on the side of E₁ and n on the side of E₃) relationship R₁₃.

E₁ has two single-valued attributes a₁₁ and a₁₂ of which a₁₁ is the key attribute. E₂ has two single-valued attributes a₂₁ and a₂₂ is the key attribute. E₃ has two single-valued attributes a₃₁ and a₃₂ of which a₃₁ is the key attribute. The relationships do not have any attributes.

If a relational model is derived from the above ER model, then the minimum number of relations that would be generated if all the relations are in 3NF is ___________.

Consider the following relations:

Consider the following SQL query.

SELECT S. Student_Name, sum(P.Marks)
     FROM Student S, Performance P
     WHERE S.Roll_No = P.Roll_No
     GROUP BY S.Student_Name

The number of rows that will be returned by the SQL query is _________.

Consider the following transaction involving two bank account x and y.

The constraint that the sum of the accounts x and y should remain constant is that of

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 grater than or equal to 7 and less than 15" is ___________.

Consider two relations R₁(A,B) with the tuples (1,5), (3,7) and R₂(A,C) = (1,7), (4,9). Assume that R(A,B,C) is the full natural outer join of R₁ and R₂. Consider the following tuples of the form (A,B,C): a = (1.5,null), b = (1,null,7), c = (3,null,9), d = (4,7,null), e = (1,5,7), f = (3,7,null), g = (4,null,9). Which one of the following statements is correct?  

Consider a simple checkpointing protocol and the following set of operations in the log.

(start, T₄); (write, T₄, y, 2, 3); (start, T₁); (commit, T₄); (write, T₁, z, 5, 7);
(checkpoint);
(start, T₂); (write, T₂, x, 1, 9); (commit, T₂); (start, T₃); (write, T₃, z, 7, 2);

If a crash happens now and the system tries to recover using both undo and redo operations, what are the contents of the undo list and the redo list

Consider the relation X(P, Q, R, S, T, U) with the following set of functional dependencies

F = {
       {P, R} → {S,T},  
       {P, S, U} → {Q, R}
    }

Which of the following is the trivial functional dependency in F+ is closure of F?

Consider the following relation

  Cinema (theater, address, capacity)

Which of the following options will be needed at the end of the SQL query

SELECT P1. address
FROM Cinema P1

Such that it always finds the addresses of theaters with maximum capacity?

The value of is

Suppose X_i for i = 1, 2, 3 are independent and identically distributed random variables whose probability mass functions are Pr[X_i = 0] = Pr[X_i = 1]=1/2 for i = 1, 2, 3. Define another random variable Y = X₁X₂ ⊕ X₃, where ⊕ denotes XOR. Then Pr[Y = 0|X₃ = 0] = _______________.

Consider the relation scheme R = (E, F, G, H, I, J, K, L, M, N) and the set of functional dependencies  {{E,F} → {G}, {F} → {I,J}, {E,H} → {K,L}, {K} → {M}, {L} → {N}} on R. What is the key for R?

Given the following statements:

    S1: A foreign key declaration can always 
        be replaced by an equivalent check
        assertion in SQL.
    S2: Given the table R(a,b,c) where a and
        b together form the primary key, the 
        following is a valid table definition.
        CREATE TABLE S (
            a INTEGER,
            d INTEGER,
            e INTEGER,
            PRIMARY KEY (d),
            FOREIGN KEY (a) references R)

Which one of the following statements is CORRECT?

Consider the following four schedules due to three transactions (indicated by the subscript) using read and write on a data item x, denoted by r(x) and w(x) respectively. Which one of them is conflict serializable?

Given the following two statements:

  
  S1: Every table with two single-valued 
      attributes is in 1NF, 2NF, 3NF and BCNF.

  S2: AB->C, D->E, E->C is a minimal cover 
      for the set of functional dependencies 
      AB->C, D->E, AB->E, E->C.

Which one of the following is CORRECT?

Given the following schema:

     employees(emp-id, first-name, last-name, hire-date, dept-id, salary)
     departments(dept-id, dept-name, manager-id, location-id)

You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query:

SQL> SELECT last-name, hire-date
     FROM employees
     WHERE (dept-id, hire-date) IN ( SELECT dept-id, MAX(hire-date)
                                     FROM employees JOIN departments USING(dept-id)
                                     WHERE location-id = 1700
                                     GROUP BY dept-id); 

What is the outcome?

The maximum number of superkeys for the relation schema R(E, F, G, H) with E as the key is _____.

Given the STUDENTS relation as shown below.

For (StudentName, StudentAge) to be the key for this instance, the value X should not be equal to

Consider the following schedule S of transactions T1, T2, T3, T4:

Which one of the following statements is CORRECT?

Consider a join (relation algebra) between relations r(R) and s(S) using the nested loop method. There are 3 buffers each of size equal to disk block size, out of which one buffer is reserved for intermediate results. Assuming size(r(R)) < size(s(S)), the join will have fewer number of disk block accesses if

SQL allows tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below:

    select * from R where a in (select S.a from S)

What is the optimized version of the relation algebra expression π_A1(π_A2(σ_F1(σ_F2(r)))), where A1, A2 are sets of attributes in  with A1 ⊂ A2 and F1, F2 are Boolean expressions based on the attributes in r?

A prime attribute of a relation scheme is an attribute that appears

Consider the relational schema given below, where eId of the relation dependent is a foreign key referring to empId of the relation employee. Assume that every employee has at least one associated dependent in the dependent relation.

employee (empId, empName, empAge)
dependent(depId, eId, depName, depAge)

Consider the following relational algebra query:

∏empId(employee)-∏empId(employee⋈(empId = eID)∧(empAge ≤ depAge)dependent)

The above query evaluates to the set of empIds of employees whose age is greater than that of

Consider the following relational schema:

  employee(empId, empName, empDept)
  customer(custId, custName, salesRepId, rating)

salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return?

SELECT empName
        FROM employee E
        WHERE NOT EXISTS (SELECT custId
                         FROM customer C
                         WHERE C.salesRepId = E.empId
                         AND C.rating <> `GOOD`); 

An index is clustered, if

Consider the following relation schema

Students(rollno: integer, sname: string)
Courses(courseno: integer, cname: string)
Registration(rollno: integer, courseno: integer, percent: real)

Which of the following queries are equivalent to this query in English?

"Find the distinct names of all students who score more than 90% in the course numbered 107" 

(I) SELECT DISTINCT S.sname
    FROM Students as S,Registration as R
    WHERE R.rollno = S.roll AND R.courseno = 107 AND R.percent > 90
(II) Πsname(σcourseno=107∧percent>90(Registration ⋈ Students))
(III) {T|∃S ∈ Students, ∃R ∈ Registration (S.rollno = R.rollno ∧ R.courseno = 107 ∧ R.percent > 90 ∧ T.sname = S.sname)}
(IV) {〈SN〉|∃SR∃RP(〈SR,SN〉 ∈ Students ∧ 〈SR,107,RP〉 ∈ Registration ∧ RP>90)}

Relation R has eight attributes ABCDEFGH . Fields of R contain only atomic values.

F = {CH→G, A→BC, B→CFG, E→A, F→EG}, is a set of functional dependencies (FDs) so that F⁺ is exactly the set of FDs that hold for R.

How many candidate keys does the relation R have?

Relation R has eight attributes ABCDEFGH . Fields of R contain only atomic values.

F = {CH→G, A→BC, B→CFG, E→A, F→EG}, is a set of functional dependencies (FDs) so that F⁺ is exactly the set of FDs that hold for R.

The relation R is

Which of the following is TRUE?

Given the basic ER and relational models, which of the following is INCORRECT?

Which of the  following statements are TRUE about an SQL query?

P:An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause.
Q:An SQL query can contain a HAVING clause even if it has a GROUP BY clause.
R: All attributes used in the GROUP BY clause must appear in the SELECT clause.
S: Not all attributes used in the GROUP BY clause need to appear in the SELECT clause

Consider the following transactions with data items P and Q initialized to zero:

T1: read (P) ;
    read (Q) ;
    if P = 0 then Q : = Q + 1 ;
    write (Q) ;
T2: read (Q) ;
    read (P) ;
    if Q = 0 then P : = P + 1 ;
    write (P) ;

Any non-serial interleaving of T1 and T2 for concurrent execution leads to

Suppose R₁(A, B) and R₂(C, D) are two relation schemas. Let r₁ and r₂ be the corresponding relation instances. B is a foreign key that refers to C in R₂. If data in r₁ and r₂ satisfy referential integrity constraints, which of the following is ALWAYS TRUE?

Consider the following relations A, B, C.

How many tuples does the result of the following relational algebra expression contain? Assume that the schema of AUB is the same as that of A.

(AUB)⋈A.Id>40∨C.Id<15 C

Consider the following relations A, B, C.

How many tuples does the result of the following SQL query contain?

SELECT A.id 
FROM A 
WHERE A.age > ALL (SELECT B.age 
                   FROM B 
                   WHERE B. name = "arun")

Consider a relational table with a single record for each registered student with the following attributes.

1. Registration_Num: Unique registration number of each registered student
2. UID: Unique identity number, unique at the national level for each citizen
3. BankAccount_Num: Unique account number at the bank. A student can have multiple accounts or join accounts. This attribute stores the primary account number.
4. Name: Name of the student
5. Hostel_Room: Room number of the hostel

Which one of the following option is INCORRECT?

Consider a relational table r with sufficient number of records, having attributes A₁, A₂,…, A_n and let 1≤p≤n. Two queries Q1 and Q2 are given below.

Q1: π_A1,...,A_p(σ_Ap=c(r)) where c is a constant
Q2: π_A1,...,A_p(σ_c1≤Ap≤c2(r)) where c₁ and c₂ is a constants

The database can be configured to do ordered indexing on A_p or hashing on A_p. Which of the following statements is TRUE?

Database table by name Loan_Records is given below.

Borrower    Bank_Manager   Loan_Amount
 Ramesh      Sunderajan     10000.00
 Suresh      Ramgopal       5000.00
 Mahesh      Sunderajan     7000.00

SELECT Count(*) 
FROM  ( 
      (SELECT Borrower, Bank_Manager FROM Loan_Records) AS S 
       NATURAL JOIN 
      (SELECT Bank_Manager, Loan_Amount FROM Loan_Records) AS T 
);

Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record (X=1, Y=1) is inserted in the table. Let MX and My denote the respective maximum values of X and Y among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after the insertion, values of MX and MY change. What will be the output of the following SQL query after the steps mentioned above are carried out?

SELECT Y FROM T WHERE X=7;

Consider a B⁺-tree in which the maximum number of keys in a node is 5. What is the minimum number of keys in any non-root node?

A relational schema for a train reservation database is given below. Passenger (pid, pname, age) Reservation (pid, class, tid)

Table: Passenger
pid   pname   age
-----------------
 0    Sachin   65
 1    Rahul    66
 2    Sourav   67
 3    Anil     69

Table : Reservation
pid  class  tid
---------------
 0    AC   8200
 1    AC   8201
 2    SC   8201
 5    AC   8203
 1    SC   8204
 3    AC   8202

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)

Which of the following concurrency control protocols ensure both conflict serializability and freedom from deadlock?

I. 2-phase locking
II. TIme-stamp ordering

Consider the following schedule for transactions T1, T2 and T3:

Which one of the schedules below is the correct serialization of the above?

Which of the following functional dependencies hold for relations R(A, B, C) and S(B, D, E):

B -> A
A -> C

The relation R contains 200 tuples and the rel ation S contains 100 tuples. What is the maximum number of tuples possible in the natural join of R and S (R natural join S)

Consider two transactions T1 and T2, and four schedules S1, S2, S3, 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]
S1 = R1[X] R2[Y]R2[X]W1[X] W1[Y] W2[Y]

Which of the above schedules are conflict-serializable?

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

  10, 3, 6, 8, 4, 2, 1

The maximum number of times leaf nodes would get split up as a result of these insertions is

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. πR-S(r)-πR-S(πR-S(r)×s-πR-S,S(r))
II. {t|t∈ πR-S(r) ∧ ∀u ∈ s(∃v ∈ r(u = v[s] ∧ t = v[R - S]))}
III. {t|t∈ πR-S(r) ∧ ∀v ∈ r(∃u ∈ s(u = v[s] ∧ t = v[R - S]))}  
IV. Select R.a, R.b
    from R, S 
    where R.c = S.c

Which of the above queries are equivalent?

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 IN (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?

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 dependencies are implied other than those implied by primary and candidate keys. Which one of the following is TRUE about the above schema??

Which of the following tuple relational calculus expression(s) is/are equivalent to ∀t ∈ r(P(t))?

I. ¬∃t ∈ r(P(t))
II. ∃t ∉ r(P(t))
III. ¬∃t ∈ r(¬P(t))
IV. ∃t ∉ r(¬P(t))

A clustering index is defined on the fields which are of type

A B-tree of order 4 is built from scratch by 10 successive insertions. What is the maximum number of node splitting operations that may take place?

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. Π_P(R ⨝ S)
II. Π_P(R) ⨝ Π_P(S)
III. Π_P(Π_P,Q(R) ∩ Π_P,Q(S))
IV. Π_P(Π_P,Q(R) - (Π_P,Q(R) - Π_P,Q(S)))

Consider the following relational schemes for a library database:

Book(Title, Author, Catalog_ no, Publisher, Year, Price)
Collection (Title, Author, Catalog_no)

with in the following functional dependencies:

I. Title Author → Catalog_no
II. Catalog_no → Title Author Publisher Year
III. Publisher Title Year → Price

Assume {Author, Title} is the key for both schemes. Which of the following statements is true?

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 organization 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, the number of first-level and second-level blocks in the multi-level index are respectively

Consider the following ER diagram

The minimum number of tables needed to represent M, N, P, R1, R2 is

Consider the following ER diagram

Which of the following is a correct attribute set for one of the tables for the correct answer to the above question?

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) that 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?

Consider the relation employee(name, sex, supervisorName) with name as the key. supervisorName gives the name of the supervisor of the employee under consideration. What does the following Tuple Relational Calculus query produce?

{e.name|employee(e)∧}
     (∀x)[¬employee(x) ∨ x.supervisorName ≠ e.name ∨ x.sex = "male"]}

Which one of the following statements if 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 schedules involving two transactions. Which one of the following statements is TRUE?

S₁: r₁(X); r₁(Y); r₂(X); r₂(Y); w₂(Y); w₁(X)
S₂: r₁(X); r₂(X); r₂(Y); w₂(Y); r₁(Y); w₁(X)

Consider the following log sequence of two transactions on a bank account, with initial balance 12000, that transfer 2000 to a mortgage payment and then apply a 5% interest.

         
            1. T1 start
            2. T1 B old=12000 new=10000
            3. T1 M old=0 new=2000
            4. T1 commit
            5. T2 start
            6. T2 B old=10000 new=10500
            7. T2 commit

Suppose the database system crashes just before log record 7 is written. When the system is restarted, which one statement is true of the recovery procedure?

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 broke but ranks are skipped: if exactly two customers have the largest balance they each get rank 1 and rank 2 is not assigned.

Query1:
  select A.customer, count(B.customer)
  from account A, account B
  where A.balance <=B.balance
  group by A.customer

Query2:
  select A.customer, 1+count(B.customer)
  from account A, account B
  where A.balance < B.balance
  group by A.customer

Consider these statements about Query1 and Query2.

1. Query1 will produce the same row set as Query2 for 
   some but not all databases.
2. Both Query1 and Query2 are correct implementation 
   of the specification
3. Query1 is a correct implementation of the specification
   but Query2 is not
4. Neither Query1 nor Query2 is a correct implementation
   of the specification
5. 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?

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 following 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 10 micro-seconds. Which of the following statements is correct?

The following functional dependencies are given:

  AB → CD, AF → D, DE → F, C → G, F → E, G → A.  

Which one of the following options is false?

Which one of the following is a key factor for preferring B⁺- trees to binary search trees for indexing database relations?

Which one of the following statements about normal forms is FALSE?

Let r be a relation instance with schema R = (A, B, C, D). We define r₁ = Π_A,B,C(R) and r₂ = Π_A,D(R). Let s = r₁*r₂ where * denotes natural join. Given that the decomposition of r into r₁ and r₂ is lossy, which one of the following is TRUE?

The following table has two attributes A and C where A is the primary key and C is the foreign key referencing A with on-delete cascade.

          A   C
          -----
          2   4
          3   4
          4   3
          5   2
          7   2
          9   5
          6   4

The set of all tuples that must be additionally deleted to preserve referential integrity when the tuple (2,4) is deleted is:

     select title
     from book as B
     where (select count(*)
        from book as T
        where T.price > B.price) < 5 

Consider a relation scheme R = (A, B, C, D, E, H) on which the following functional dependencies hold: {A → B, BC → D, E → C, D → A}. What are the candidate keys of R?

Let R₁(A,B,C) and R₂(D,E) be two relation schema, where the primary keys are shown underlined, and let C be a foreign key in R₁ referring to R₂. Suppose there is no violation of the above referential integrity constraint in the corresponding relation instances r₁ and r₂. Which one of the following relational algebra expressions would necessarily produce an empty relation?

Consider the following relation schema pertaining to a students database:

   Student (rollno, name, address)
   Enroll (rollno, courseno, coursename) 

Where the primary keys are shown underlined. The number of tuples in the Student and Enroll tables are 120 and 8 respectively. What are the maximum and minimum number of tuples that can be present in (Student * Enroll), where '*' denotes natural join ?

The relation scheme Student Performance (name, courseNo, rollNo, grade) has the following functional dependencies:

   name, courseNo → grade
   rollNo, courseNo → grade
               name → rollNo
             rollNo → name 

The highest normal form of this relation scheme is

Consider the relation Student (name, sex, marks), where the primary key is shown underlined, pertaining to students in a class that has at least one boy and one girl. What does the following relational algebra expression produce? (Note: p is the rename operator).

The condition in join is "(sex = female ^ x = male ^ marks ≤ m)"

The order of an internal node in a B⁺ tree index is the maximum number of children it can have. Suppose that a child pointer takes 6 bytes, the search field value takes 14 bytes, and the block size is 512 bytes. What is the order of the internal node?

The employee information in a company is stored in the relation

Employee (name, sex, salary, deptName)
Consider the following SQL query
         Select deptName
                    From Employee
                    Where sex = 'M'
                    Group by deptName
                    Having avg (salary) > 
                       (select avg (salary) from Employee)

It returns the names of the department in which

Which of the following scenarios may lead to an irrecoverable error in a database system?

Consider the following SQL query

   select distinct al, a2,........., an
   from rl, r2,........, rm
   where P 

For an arbitrary predicate P, this query is equivalent to which of the following relational algebra expressions?

Consider the following functional dependencies in a database:

  Data_of_Birth → Age
  Age → Eligibility
  Name → Roll_number
  Roll_number → Name
  Course_number → Course_name
  Course_number → Instructor
  (Roll_number, Course_number) → Grade 

The relation (Roll_number, Name, Date_of_birth, Age) is:

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?

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 the data item D respectively.

Which of the following statements is correct?

Relation R with an associated set of functional dependencies, F, is decomposed into BCNF. The redundancy (arising out of functional dependencies) in the resulting set of relations is

With regard to the expressive power of the formal relational query languages, which of the following statements is true?

A B⁺- tree index is to be built on the Name attribute of the relation STUDENT. Assume that all student names are of length 8 bytes, disk blocks are of size 512 bytes, and index pointers are of size 4 bytes. Given this scenario, what would be the best choice of the degree (i.e. the number of pointers per node) of the B⁺-tree?

Relation R is decomposed using a set of functional dependencies, F, and relation S is decomposed using another set of functional dependencies, G. One decomposition is definitely BCNF, the other is definitely 3NF, but it is not known which is which. To make a quaranteed identification, which one of the following tests should be used on the decompositions? (Assume that the closures of F and G are available).

From the following instance of a relation scheme R(A,B,C), we can conclude that:

A university placement center maintains a relational database of companies that interview students on campus and make job offers to those successful in the interview. The schema of the database is given below:

  COMPANY (cname, clocation)     STUDENT (scrollno, sname, sdegree)
   INTERVIEW (cname, srollno, idate)  OFFER (cname,srollno, osalary) 

The COMPANY relation gives the name and location of the company. The STUDENT relation gives the student’s roll number, name and the degree program for which the student is registered in the university. The INTERVIEW relation gives the date on which a students is interviewed by a company. The OFFER relation gives the salary offered to a student who is successful in a company’s interview. The key for each relation is indicated by the underlined attributes.

(a) Write relational algebra expressions (using only the operator ⨝,σ,π,∪,−) for the following queries:

(i) List the rollnumbers and names of those students who attended at least one interview but did not receive any job offer.
(ii) List the rollnumbers and names of students who went for interviews and received job offers from every company with which they interviewed.

(b) Write an SQL query to list, for each degree program in which more than five students were offered jobs, the name of the degree and the average offered salary of students in this degree program.

For relation R = (L, M, N , O, P), the following dependencies hold:

   M → O NO → P P → L and L → MN 

R is decomposed into R₁ = (L, M, N , P) and R₂ = (M, O).

(a) Is the above decomposition a lossless-join decomposition? Explain.
(b) Is the above decomposition dependency-preserving? If not, list all the dependencies that are not preserved.
(c) What is the highest normal form satisfied by the above decomposition?

(a) The following table refers to search times for a key in B-trees and B⁺-trees.

A successful search means that the key exists in the database and unsuccessful means that it is not present in the database. Each of the entries X₁, X₂, X₃ and X₄ can have a value of either Constant or Variable. Constant means that the search time is the same, independent of the specific key value, where Variable means that it is dependent on the specific key value chosen for the search.

Give the correct values for the entries X₁, X₂, X₃ and X₄ (for example X₁ = Constant, X₂ = Constant, X₃ = Constant, X₄ = Constant).

(b) Relation R(A,B) has the following view defined on it:

 CREATE VIEW V AS
 (SELECT R1.A, R2.B
 FROM R AS R1, R AS R2
 WHERE R1.B = R2.A)

(i) The current contents of relation R are shown below. What are the contents of the view V?

(ii) The tuples (2,11) and (11,6) are now inserted into R. What are the additional tuples that are inserted in V?

Consider a schema R(A,B,C,D) and functional dependencies A → B and C → D. Then the decomposition of R into R₁(AB) and R₂(CD) is

Suppose the adjacency relation of vertices in a graph is represented in a table Adj(X,Y). Which of the following queries cannot be expressed by a relational algebra expression of constant length?

Let r and s be two relations over the relation schemes R and S respectively, and let A be an attribute in R. Then the relational algebra expression σ_A=a(r⋈s) is always equal to

R(A,B,C,D) is a relation. Which of the following does not have a lossless join, dependency preserving BCNF decomposition?

Which of the following relational calculus expressions is not safe?

Consider a relation geq which represents “greater than or equal to”, that is, (x,y) ∈ geq only if y >= x.

create table geq
(  Ib integer not null
   ub integer not null
   primary key 1b
   foreign key (ub) references geq on delete cascade ) 

Which of the following is possible if a tuple (x,y) is deleted?

Consider the relation examinee (regno, name, score), where regno is the primary key to score is a real number.
(a) Write a relational algebra using (∏,σ,ρ,×) to find the list of names which appear more than once in examinee.
(b) Write an SQL query to list the regno of examinees who have a score greater than the average score.
(c) Suppose the relation appears (regno, centr_code) specifies the center where an examinee appears. Write an SQL query to list the centr_code having an examinee of score greater than 80.

We wish to construct a B⁺ tree with fan-out (the number of pointers per node) equal to 3 for the following set of key values:

             80, 50, 10, 70, 30, 100, 90   

Assume that the tree is initially empty and the values are added in the order given.
(a) Show the tree after insertion of 10, after insertion of 30, and after insertion of 90. Intermediate trees need not be shown.
(b) The key values 30 and 10 are now deleted from the tree in that order. Show the tree after each deletion.

B⁺-trees are preferred to binary trees in databases because

Given the relations

   employee (name, salary, deptno) and
   department (deptno, deptname, address)  

Which of the following queries cannot be expressed using the basic relational algebra operations (σ, π, ×, ⋈, ∪, ∩, -)?

Given the following relation instance.

          x  y  z
          1  4  2
          1  5  3
          1  6  3
          3  2  2  

Which of the following functional dependencies are satisfied by the instance?

Given relations r(w, x) and s(y, z), the result of

    select distinct w, x
    from r, s  

is guaranteed to be same as r, provided

In SQL, relations can contain null values, and comparisons with null values are treated as unknown. Suppose all comparisons with a null value are treated as false. Which of the following pairs is not equivalent?

(a) Suppose you are given an empty B⁺-tree where each node (leaf and internal) can store up to 5 key values. Suppose values 1,2,….. 10 are inserted, in order, into the tree, Show the tree pictorially
(i) After 6 insertions, and
(ii) After all 10 insertions
Do NOT show intermediate stages.

(b) Suppose instead of splitting a node when it is full, we try to move a value to the left sibling. If there is no left sibling, or the left sibling is full, we split the node. Show the tree after values, 1, 2,….., 9 have been inserted. Assume, as in (a) that each node can hold up to 5 keys.

(c) In general, suppose a B⁺-tree node can hold a maximum of m keys, and you insert a long sequence of keys in increasing order. Then what approximately is the average number of keys in each leaf level node.
(i) In the normal case, and
(ii) With the insertion as in (b).

Consider a bank database with only one relation
transaction (transno, acctno, date, amount)
The amount attribute value is positive for deposits and negative for withdrawals.

(a) Define an SQL view TP containing the information.
(acctno, T1.date, T2.amount)
for every pair of transactions T1, T2 such that T1 and T2 are transaction on the same account and the date of T2 is ≤ the date of T1.

(b) Using only the above view TP, write a query to find for each account the minimum balance it ever reached (not including the 0 balance when the account is created). Assume there is at most one transaction per day on each account, and each account has had atleast one transaction since it was created. To simply your query, break it up into 2 steps by defining an intermediate view V.

Consider the join of a relation R with a relation S. If R has m tuples and S has n tuples then the maximum and minimum sizes of the join respectively are

The relational algebra expression equivalent to the following tuple calculus expression:

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 of R?

Which of the following is correct?

For the schedule given below, which of the following is Correct?

1   Read A
2                               Read B
3   Write A
4                               Read A
5                               Write A
6                               Write B
7   Read B
8   Write B 

Consider the schema R = (S T U V) and the dependencies S → T, T → U, U → V and V → S. Let R = (R1 and R2) be a decomposition such that R1 ∩ R2 ≠ ∅ . The decomposition is

Consider the circuit shown below. In a certain steady state, the line Y is at '1'. What are the possible values of A, B and C in this state?

Which of the following sets of component(s) is/are sufficient to implement any arbitrary Boolean function?

Which of the following is/are correct?

Given two union compatible relations R₁(A,B) and R₂(C,D), what is the result of the operation R₁A = CAB = DR₂?

Which normal form is considered adequate for normal relational database design?