Homechevron_rightEngineeringchevron_rightRPSC Programmerchevron_rightProgrammer DBMSchevron_right<p class="MsoNormal" style="margin left:0in;text ind...

...

  • Q.

    Consider a relational database containing the following schemes.

    Catalogue

    sno

    pno

    Cost

    S1

    P1

    150

    S1

    P2

    50

    S1

    P3

    100

    S2

    P4

    200

    S2

    P5

    250

    S3

    P1

    250

    S3

    P2

    150

    S3

    P5

    300

    S3

    P4

    250

     

    Suppliers

    sno

    sname

    location

    S1

    M/s Royal furniture

    Delhi

    S2

    M/s Balaji furniture

    Bangalore

    S3

    M/s Premium furniture

    Chennai

     

    Parts

    pno

    Pname

    Part_spec

    P1

    Table

    Wood

    P2

    Chair

    Wood

    P3

    Table

    Steel

    P4

    Almirah

    • filter_dramaExplanation
      Answer is : A

      Inner Query: SELECT AVG (cost) FROM Cataloque WHERE pno = ‘P4’ GROUP BY pno

      The execution of the inner query gives the average of the cost of parts with part-id P4

      Output:

      Avg (cost)

      225

       

      Outer Query:

      SELECT s.sno, s.sname FROM Suppliers s, Cataloque c WHERE s.sno = c.sno AND Cost > (225)

      The execution of the entire query output the following table:

      sno

      sname

      S2

      M/s Balaji furniture

      S3

      M/s Premium furniture

      S3

      M/s Premium furniture

      S3

      M/s Premium furniture

       

      Hence, there are 4 rows in the resultant table. 

    Discussion

      No one started the discussion yet. Break the ice and start the conversation.
      Please Login to be part of the discussion.

    Similar Questions

    • 1. A transaction can proceed only after the concurrency control manager ________ the lock to the transaction
    • filter_dramaExplanation
      Answer is : A
      A transaction can proceed only after the concurrency control manager grants the lock to the transaction.
    • 2.

      Consider the following partial Schedule S involving two transactions T1 and T2. Only the read and the write operations have been shown. The read operation on data item P is denoted by read(P) and the write operation on data item P is denoted by write(P)

      Time Instance

      Transaction-id

      T1

      T2

      1

      read(A)

       

      2

      write(A)

       

      3

       

      read(C)

      4

       

      write(C)

      5

       

      read(B)

      6

       

      write(B)

      7

       

      read(A)

      8

       

      Commit

      9

      read(B)

       

       

      Schedule S

       

      Suppose that the transaction T1 fails immediately after time instance 9. Which one of the following statements is correct?

    • filter_dramaExplanation
      Answer is : B

      Concept:

      Recoverable schedule: A schedule is said to be recoverable in which for every transaction Ti and Tj , if Tj is reading a data item that is written by Ti then the commit operation of Ti must before the commit operation of Tj.

      Aborting: Aborting means to disrupt the action by killing the current ongoing transaction.

      Explanation:

      Option 1: Incorrect

      As, it is not giving any surety about T1, there is no commit in T1.  Rolling back of T2 may not be required in this.

      Option 2: Correct

      In given schedule, T2 is reading data item A that is written by T1 so it means commit of T1 must before the commit of T2, but here in this schedule it is not happening. So, this is non – recoverable schedule. In this after T1 fails, if we want to undo all the operations, still we cannot undo a committed transaction. So, it cannot ensure transaction atomicity.

      Option 3: Incorrect

      It is redoing all the operations of T2. But in this, it is reading data item A that is written by T1 and T1 is not committed. So, it results in dirty read problem.

      Option 4: Incorrect

      Since Schedule S is non – recoverable. So, it is incorrect. Also, it cannot ensure atomicity.
    • 3.

      Consider the relation R(X, Y, Z, W, V) in which X, Y, Z, W, and V are the attributes and the following set of functional dependencies

      A = { {X, Y} → {Z, W},  {X, W, V} → {Y, Z}  }

      Which of the following is the trivial functional dependency in A+, where A+ is closure of A?

    • filter_dramaExplanation
      Answer is : B

      Concept:

      The closure of F, denoted as F+, is the set of all regular FD, that can be derived from.

      For trivial functional dependency,

      Let A and be two sets consists of attributes of a relation

      A → B

      \(\supseteq\) B 

      Explanation:

      Option 1

      {X, Z} → {Z, W}

      {X, Z}  \(\nsupseteq\) {Z, W}

      Not a trivial functional dependency

      Option 2: 

      {X, V} → {V}

      {X, V} \(\supseteq\) {V}

      It is a trivial functional dependency

      Option 3

      {X, W, V} → {Y}

      {X, W, V} \(\nsupseteq\)  {Y}

      Not a trivial functional dependency

      Option 4

      {Y, W } → {Y, X}

      {Y, W } \(\nsupseteq\) {Y, X}

      Not a trivial functional dependency

      NOTE:

      \(\supseteq\) → superset

      \(\nsupseteq\) → not superset

    • 4. A router that connects a Local Area Network (LAN) with the internet is called _____.
    • filter_dramaExplanation
      Answer is : C
      • Interior router → router in LAN of an organization
      • Exterior router → router operated in the Internet backbone
      • Gateway router → router that connects a LAN with the Internet or WAN.
    • 5. The advantage of optimistic locking is
    • filter_dramaExplanation
      Answer is : A

      A locking policy is an important component of any multiuser application. When users share objects in an application, a locking policy ensures that two or more users do not attempt to modify the same object or its underlying data simultaneously.

      The two models of locking data in a database ‘or’ locking whatever is accessed in a transaction are:

      Optimistic locking:

      • All users have read access to the object.
      • When a user attempts to write a change, the application checks to ensure that the object has not changed since the last read.
      • It is when you check if the record was updated by someone else before you commit the transaction.
      • Optimistic locking assumes a manual intervention to decide whether to proceed with the update.

       

      Pessimistic locking:

      • When a user accesses an object to update it, the database locks the object until the update is completed.
      • No other user can read or update the object until the first user releases the lock. The database offers this locking type.
      • It is when you take on exclusive lock so that no one else starts modifying the record.
      • The advantage of optimistic locking is the lock transactions are best suited with a lot of activity.

    Programmer DBMSTopics

    leaderboardLeaderboard
    • Rahul Kumar

      191 Points

    • VIKRAM JEET

      54 Points

    • GEETHIKA CHOWDARY

      53 Points

    • sunita saini

      52 Points

    • Zain

      49 Points