Loading

SET 2


  Question 1

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?


A : {E.F}
B : {E.F,H}
C : {E.F,H,K,L}
D : {E}


  •  
    .

     Correct answer is :B

     Solution :
      R(EFGHI,JKLMN)
    F = {
    EF->G
    F->IJ
    EH->KL
    K->M
    L->N
    }
    (EF)+ = EFGIJ, E&F + = Together functionally derive GIJ and if we observe given FDs, H can’t be determined by any other attributes. So H must be part of all the (candidate) keys. H along with E determines K and L, K & L functionally determine M and N respectively.
    (EFH) + = EFGIJHKLMN
    EFH is the only candidate for key.

  •   Question 2

    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?


    A : S1 is TRUE and S2 is a FALSE
    B : Both S1 and S2 are TRUE
    C : S1 is FALSE and S2 is a TRUE
    D : Both S1 and S2 are FALSE


  •  
    .

     Correct answer is :D

     Solution :
      Check assertions are not sufficient to replace foreign key. Foreign key declaration may have cascade delete which is not possible by just check insertion.
    Foreign key in one table should uniquely identifies a row of other table. In above table definition, table S has a foreign key that refers to field a of R. The field a in table R doesnt uniquely identify a row in table R.

  •   Question 3

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

    A : r1 (x) ; r2 (x) ; w1 (x) ; r3(x) ; w2 (x)
    B : r2 (x) ; r1 (x) ; w2 (x) ; r3(x) ; w1 (x)
    C : r3 (x) ; r2 (x) ; r1 (x) ; w2(x) ; w1 (x)
    D : r2 (x) ; w2 (x) ; r3 (x) ; r1(x) ; w1 (x)


  •  
    .

     Correct answer is :D

     Solution :
      If there is a cycle in precedence graph, then the schedule is not conflict serializable.


  •   Question 4

    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?


    A : S1 is TRUE and S2 is FALSE.
    B : Both S1 and S2 are TRUE.
    C : S1 is FALSE and S2 is TRUE.
    D : Both S1 and S2 are FALSE.


  •  
    .

     Correct answer is :A


  •   Question 5

    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?


    A : It executes but does not give the correct result.
    B : It executes and gives the correct result.
    C : It generates an error because of pairwise comparison.
    D : It generates an error because the GROUP BY clause cannot be used with table joins in a sub-query.


  •  
    .

     Correct answer is :B


  •   Question 6

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



  •  
    .

     Correct answer is :8

     Solution :
      The maximum number of super keys for the relation schema R(E,F,G,H) with E as the key is 23 = 8 as any subset of non key attributes along with key attribute will form the super key of R.
    As we have 3 nonkey all (F, G and H) so subsets will be 23

  •   Question 7

    Given an instance of the STUDENTS relation as shown below For (StudentName, StudentAge) to be a key for this instance, the value X should NOT be equal to____________





  •  
    .

     Correct answer is :19

     Solution :
      For (Student Name, student age) to be a key for given instance of STUDENTS relation, the pair value should not get repeated in any two tuples p and q (uniqueness in forced by the definition of key)
    Output :-
    Shankar age should not b 19
    Shankar 19

  •   Question 8

    Consider the following schedule S of transactions T1, T2, T3, T4
    Which one of the following statements is CORRECT?




    A : S is conflict-serializable but not recoverable
    B : S is conflict-serializable but is recoverable
    C : S is both conflict-serializable and recoverable
    D : S is neither conflict-serializable nor is it recoverable


  •  
    .

     Correct answer is :C

     Solution :
      According to the precedence graph In the schedule S of transactions T1 ,T2 ,T3 and T4 for each pair of transaction Ti and Tj , such that Tj reads a data item previously written by Ti the commit operation of Tj appears after the commit operation of Ti hence the schedule is recoverable schedule.


  •   Question 9

    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

    A : relation r(R) is in the outer loop.
    B : relation s(S) is in the outer loop.
    C : join selection factor between r(R) and s(S) is more than 0.5.
    D : join selection factor between r(R) and s(S) is less than 0.5.


  •  
    .

     Correct answer is :A

     Solution :
      A join between r(R) and s (S) using nested loop method will be as follows.
    For each tuple r in R do
    For each tuple s in S do
    If r and s satisfy the join condition then output the tuple
    Cost estimations for the above loop: – b(R) and b(S) number of blocks in R and in S – Each block of outer relation is read once – Inner relation is read once for each block of outer relation Summing up : IO= b(R)+b(R)*b(S) total IO operations this will be lowest when R is in outer loop.

  •   Question 10

    SQL allows duplicate 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)


    A : Select R. * from R, S where R. a=S. a
    B : Select distinct R. * from R,S where R. a=S. a
    C : Select R. * from R, (select distinct a from S) as S1 where R. a=S1.a
    D : Select R. * from R, S where R.a = S. a and is unique R


  •  
    .

     Correct answer is :C


  •   Question 11

    What is the optimized version of the relation algebra expression, where A1, A2 are sets of attributes in with A1 ⊂ A2 and F1, F2 are Boolean expressions based on the attributes in r?



    A : πA1 ( ∂ (F1 ∧ F2 ) (r) )
    B : πA1 ( ∂ (F1 ∨ F2 ) (r) )
    C : πA2 ( ∂ (F1 ∧ F2 ) (r) )
    D : πA2 ( ∂ (F1 ∨ F2 ) (r) )


  •  
    .

     Correct answer is :A


  •   Question 12

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

    A : in all candidate keys of R.
    B : in some candidate key of R.
    C : in a foreign keys of R.
    D : only in the primary key of R.


  •  
    .

     Correct answer is :B

     Solution :
      A prime attribute or key attribute of a relation scheme R is an attribute that appears in any of the candidate key of R, remaining attributes are known as non-prime or non-key tribute

  •   Question 13

    Consider the transactions T1, T2, and T3 and the schedules S1 and S2 given below.
    T1 : r1 (X) ; r1 (z) ; w1 (X) ; w1 (z)
    T2 : r2 (X) ; r2 (z) ; w2 (z)
    T3 : r3 (X) ; r3 (X) ; w3 (Y)
    S1: r1(X); r3(Y); r3(X); r2(Y); r2(Z); w3(Y); w2(Z); r1(Z); w1(X); w1(Z)
    S2: r1(X); r3(Y); r2(Y); r3(X); r1(Z); r2(Z); w3(Y); w1(X); w2(Z); w1(Z)
    Which one of the following statements about the schedules is TRUE?


    A : Only S1 is conflict-serializable
    B : Only S2 is conflict-serializable.
    C : Both S1 and S2 are conflict-serializable.
    D : Neither S1 nor S2 is conflict-serializable.


  •  
    .

     Correct answer is :A

     Solution :
      Precedence graph for 1 2 S &S are as follows
    Only S1 is conflict serializable.


  •   Question 14

    Consider the relational schema given below, where eId of the relation dependentis 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) ∧ (emp Age <= depAge) dependent )
    The above query evaluates to the set of empIds of employees whose age is greater than that of


    A : some dependent.
    B : all dependents.
    C : some of his/her dependents.
    D : all of his/her dependent


  •  
    .

     Correct answer is :D

     Solution :
      Part A of the above given relational algebra query will give the set of empIds of those employees whose age is less than or equal to the age of some of his/her dependents. Now when set of empIds of all employees minus set of empIds obtained from part A is done, then we get the set of empIds of employees whose age is greater than that of all of his/her dependents.


  •   Question 15

    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’)


    A : Names of all the employees with at least one of their customers having a ‘GOOD’ rating.
    B : Names of all the employees with at most one of their customers having a ‘GOOD’ rating.
    C : Names of all the employees with none of their customers having a ‘GOOD’ rating.
    D : Names of all the employees with all their customers having a ‘GOOD’ rating.


  •  
    .

     Correct answer is :D

     Solution :
      The outer query will return the value (names of employees) for a tuple in relation E, only if inner query for that tuple will return no tuple (usage of NOT EXISTS).
    The inner query will run for every tuple of outer query. It selects cust-id for an employee e, if rating of customer is NOT good. Such an employee should not be selected in the output of outer query.
    So the query will return the names of all those employees whose all customers have GOOD rating.

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