Database System - B.Tech 5th Semester Exam., 2017

2017•Semester 2•Civil-CA•End Semester
Bihar Engineering University, Patna
B.Tech 5th Semester Exam., 2017

Database System

Time: 3 hoursCode: 051509Full Marks: 70

Instructions:

  1. The marks are indicated in the right-hand margin.
  2. There are NINE questions in this paper.
  3. Attempt FIVE questions in all.
  4. Question No. 1 is compulsory.
Q.1 Fill in the blanks (any seven):[14]

  1. A description of data in terms of a data model is called a _____

  2. Information about the conceptual, external, and physical schemas is stored in the _____

  3. The weak entity set must have participation in the _____ identifying relationship set.

  4. The _____ also called arity, of a relation is the number of fields.

  5. In relational algebra renaming operation is denoted by _____ operator.

  6. SQL queries are optimized by decomposing them into a collection of smaller units, called _____

  7. The set of all functional dependencies (FDs) implied by a given set F of FDs is called the _____ F, denoted by _____

  8. X→→Y is a trivial MVD if _____ or _____

  9. _____ allows us to identify a point in a transaction and selectively roll back operations carried out after this point.

Q.2 Solve both questions :[14]

  1. Design a generalization-specialization hierarchy for a motor-vehicle sales company. The company sells motor-cycles, passenger cars, vans, and buses. Justify your placement of attributes at each level of the hierarchy.

  2. Explain the difference between condition-defined and user-defined constraints. Which of these constraints can the system check automatically? Explain your answer.

Q.3 Solve both questions :[14]

  1. List two reasons why we may choose to define a view.

  2. List two major problems with processing update operations expressed in terms of views.

Q.4 Consider the relations below:[14]

  1. Passengers (Name, Address, Age)
    Reservations (Name, FlightNum, Seat)
    Flights (FlightNum, DepartCity, DestinationCity, DepartureTime, ArrivalTime, MinutesLate)
    Write the following queries in relational algebra:
    (a) Get the names of passengers who had a reservation on a flight that was more than 30 minutes late.
    (b) Get the names of passengers who had reservations on all flights that were more than 60 minutes late.

Q.5 Solve both questions :[14]

  1. What is the dependency closure \( F^+ \) of a set F of FDs? How can you compute the closure of a given set F of FDs?

  2. What is the attribute closure \( X^+ \) of a set of attributes X with respect to a set of FDs F? Give the algorithm for computing the attribute closure of a set X of attributes.

Q.6 Solve this question :[14]

  1. When is the decomposition of a relation schema R into two relation schemas X and Y said to be a lossless-join decomposition? Why is the property so important? Give a necessary and sufficient condition to test whether a decomposition is lossless-join.

Q.7 Solve this question :[14]

  1. Describe how a query block is translated into extended relational algebra. Describe and motivate the extensions to relational algebra. Why are \( \sigma\pi x \) expressions the focus of an optimizer?

Q.8 Solve this question :[14]

  1. When do two actions on the same data object conflict? Explain with example the anomalies that can be caused by conflicting actions.

Q.9 Solve this question :[14]

  1. What transaction characteristics can a programmer control in SQL? Explain in brief the different access modes and isolation levels in particular. How can the isolation level and access mode be set in the transaction? Show with example.