Loading

SET 5


  Question 1

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?
πcourseid (( πstudid ( σsex="female" (studInfo)) * πcourseId(enroll) - enroll )


A : Courses in which all the female students are enrolled
B : Courses in which a proper subset of female students are enrolled.
C : Courses in which only male students are enrolled
D : None of the above


  •  
    .

     Correct answer is :B

     Solution :
      The expression given in question does following steps in sequence.
    a) Select studids of all female students and selects all courseids of all courses.
    b) Then the query does a Cartesian Product of the above select two columns from different tables.
    c) Finally it subtracts enroll table from the result of above step (b). This will remove all the (studid, courseid) pairs which are present in enroll table.

  •   Question 2

    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" ] }


    A : Names of employees with a male supervisor.
    B : Names of employees with no immediate male subordinates.
    C : Names of employees with no immediate female subordinates.
    D : Names of employees with a female supervisor.


  •  
    .

     Correct answer is :C

     Solution :
      The query selects all those employees whose immediate subordinate is “male”. In other words, it selects names of employees with no immediate female subordinates

  •   Question 3

    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 than anyone in the department 5, which one of the statements is TRUE for any arbitrary employee table?

    Q1 : Select e.empId
    From employee e
    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.department = “5”)


    A : Q1 is the correct query
    B : Q2 is the correct query
    C : Both Q1 and Q2 produce the same answer
    D : Neither Q1 nor Q2 is the correct query


  •  
    .

     Correct answer is :D

     Solution :
      Consider the following example table.

    empid name department salary
    1 a 4 90k
    2 b 5 30k
    3 c 5 50k
    4 d 5 80k
    Q1 will give empid 1
    Q2 will give empid 1, 3, 4
    But the correct answer is 4

  •   Question 4

    Which one of the following statements if FALSE?

    A : Any relation with two attributes is in BCNF
    B : A relation in which every key has only one attribute is in 2NF
    C : A prime attribute can be transitively dependent on a key in a 3 NF relation.
    D : A prime attribute can be transitively dependent on a key in a BCNF relation.


  •  
    .

     Correct answer is :D


  •   Question 5

    The order of a leaf node in a tree 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?

    A : 63
    B : 64
    C : 67
    D : 68


  •  
    .

     Correct answer is :A

     Solution :
      Disk Block size = 1024 bytes
    Data Record Pointer size, r = 7 bytes
    Value size, V = 9 bytes
    Disk Block ptr, P = 6 bytes
    Let order of leaf be m. A leaf node in B+ tree contains at most m record pointers, at most m values, and one disk block pointer. r*m + V*m + p <= 1024 16m <= 1018 m =< 63

  •   Question 6

    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(X);r2(Y);w2(Y);r1(Y);w1(X)


    A : Both S1 and S2 are conflict serializable.
    B : S1 is conflict serializable and S2 is not conflict serializable.
    C : S1 is not conflict serializable and S2 is conflict serializable
    D : Both S1 and S2 are not conflict serializable.


  •  
    .

     Correct answer is :C

     Solution :
      S1 is not conflict serializable, but S2 is conflict serializable
    Schedule S1
       T1            T2
    ---------------------
      r1(X)
      r1(Y)
                    r2(X)
                    r2(Y)
                    w2(Y)
      w1(X)
    The schedule is neither conflict equivalent to T1T2, nor T2T1.
    
    Schedule S2
       T1            T2
    ---------------------
      r1(X)
                    r2(X)
                    r2(Y)
                    w2(Y)
      r1(Y)
      w1(X)

    The schedule is conflict equivalent to

  •   Question 7

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




    A : I only
    B : II only
    C : III only
    D : III and IV only


  •  
    .

     Correct answer is :C


  •   Question 8

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

    A : non-key and ordering
    B : non-key and non-ordering
    C : key and ordering
    D : key and non-ordering


  •  
    .

     Correct answer is :A

     Solution :
      A clustering index determines how rows are physically ordered.

  •   Question 9

    The data blocks of a very large file in the Unix file system are allocated using

    A : contiguous allocation
    B : linked allocation
    C : indexed allocation
    D : an extension of indexed allocation


  •  
    .

     Correct answer is :D


  •   Question 10

    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?

    A : 3
    B : 4
    C : 5
    D : 6


  •  
    .

     Correct answer is :C


  •   Question 11





    A : Only I and II
    B : Only I and III
    C : Only I,II and III
    D : Only I, III and IV


  •  
    .

     Correct answer is :D

     Solution :
      In I, Ps from natural join of R and S are selected.
    In III, all Ps from intersection of (P, Q) pairs present in R and S.
    IV is also equivalent to III because (R – (R – S)) = R ∩ S.
    II is not equivalent as it may also include Ps where Qs are not same in R and S.

  •   Question 12

    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?


    A : Both Book and Collection are in BCNF
    B : Both Book and Collection are in 3NF only
    C : Book is in 2NF and Collection is in 3NF
    D : Both Book and Collection are in 2NF only


  •  
    .

     Correct answer is :C

     Solution :
      Table Collection is in BCNF as there is only one functional dependency “Title Author –> Catalog_no” and {Author, Title} is key for collection. Book is not in BCNF because Catalog_no is not a key and there is a functional dependency “Catalog_no –> Title Author Publisher Year”. Book is not in 3NF because non-prime attributes (Publisher Year) are transitively dependent on key [Title, Author]. Book is in 2NF because every non-prime attribute of the table is either dependent on the key [Title, Author], or on another non prime attribute.

  •   Question 13

    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

    A : 8 and 0
    B : 128 and 6
    C : 256 and 4
    D : 512 and 5


  •  
    .

     Correct answer is :C


  •   Question 14

    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


  •  
    .

     Correct answer is :B

     Solution :
      Answer is B, i.e, 3 minimum tables. M, P are strong entities hence they must be represented by separate tables. Many-to-one and one-to-many relationship sets that are total on the many-side can be represented by adding an extra attribute to the “many” side, containing the primary key of the “one” side. ( This way no extra table will be needed for Relationship sets ) M table is modified to include primary key of P side(i.e. P1). N is weak entity, and is modified to include primary key of P (i.e, P1). Therefore there would be minimum of 3 tables with schema given below : M ( M1, M2, M3, P1) P ( P1, P2 ) N ( P1, N1, N2 ) Note: This modification of a table in the case of one-many or many-one to include relationship set at the many side works well, but only in the case when the relationship set doesn't have its own attributes. If the relationship set has its own attribute then we need to make a separate table for the relationship set also.

  •   Question 15

    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?



    A : {M1, M2, M3, P1}
    B : {M1, P1, N1, N2}
    C : {M1, P1, N1}
    D : {M1, P1}


  •  
    .

     Correct answer is :A


  • MY REPORT
    TOTAL = 15
    ANSWERED =
    CORRECT / TOTAL = /15
    POSITIVE SCORE =
    NEGATIVE SCORE =
    FINAL SCORE =