B.TECH - Semester 4 database design Question Paper 2022 (dec)
Practice authentic previous year university questions for better exam preparation.
Sample Questions
- Discuss the capabililies that should be provided by a DBMS.
- Discuss the naming conventions used for ER schema diagrams.
- How are the OUTER JOIN operations difierent from the INNER JOIN operations? How is the OUTER UNION operation difierent from UNION?
- What is the losstess join property of a decomposition? Why is it imporiant?
- Explain heuristic based query opilmization. $$ \text { ( } 5 \times 4=20 \text { Marks) } $$ Answer any one question from each module. Each question carries 20 marks.
- (a) What are the responsibilities of the DBA and the database designers? 6
- (b) Discuss the main characterislics of the dalabase approach and how it difiers from tradilional file systems.
- (c) Explain the stlualions in which a DBMS may involve unnecessary overhead cosls that would nol be incurred in traditional file processing.
- (a) Explain with example (for each) reduction of the following E-R diagram realures to Relational Dalabase Schema.
- (i) Weak Entity (ii) Many to One Relationship.
- (b) Design an ER schema for keeping lrack of information aboul votes laken in the U.S.House of Represemalives during the current wo-year congressional session. The dalabase needs to keep lrack of each U.S. STATE's Name (e.g. 'Texas', 'New York', Cali...
- (b) Consider the following library dalabase schema, Give an expression in the relational algebra to express each of the following queries: Book(Book_id. Tille, Publisher) Publishor(Name,Address,phone) Book_copies(Book_id,Branch_id.No_of_copies) Libra...
- (i) How many copies of the book titled The Lost Tribe are owned by the library branch whose name is "Sharpslown"? (ii) How many copies of the book illed The Losl Tribe are owned by each library branch? (iii) Retrieve the names, addresses, and number ...
- (a) Discuss how Trigger construcls is used in SOL, and discuss the various options for the construct. Specify whal each construct is useful for.
- (b) Conslder the following airline database, Frights (lno, from, lo, distance, departs) Aircraff (aid, aname, range) Certilied (eid, aid) Employees (eid, ename, salary) Write SQL for each of the following queries.
- (i) Find names of pilots who are cerlined on some Boeing. (ii) Find eid of employee(s) with the second highest salary. (iii) Find the aircraft id of the aircraft for which every pilat is eligible whose salary is aver $\$ 100,000$. (iv) Find the emplo...
- (a) Discuss insertion, delelion, and modificalion anomalies. Why are they considered bad? Illustrale with examples.
- (b) Consider the relation $R(P, Q, R, S, T, U)$ and the sel $F$ of functional dependencies $F=\{P \rightarrow Q, R \rightarrow S U, P R \rightarrow T, S \rightarrow U\}$
- (i) What is the key of the relation and why? (ii) What is the highest normal form. Prove it: (iii) If il is nol in 3NF then lind a decomposition that is lossless and dependency preserving OR 3
- (a) Wrile an algorithm lo check if a dependency $\alpha \rightarrow \beta$ is preserved. Given $R=(A, B, C) F=\{A \rightarrow B B \rightarrow C\}$ Key $=\{A\}$. Check whether the rollowing decomposition is lossless and dependency preserving.
- (i) $R_{1}=(A, B), R_{2}=(B, C)$ (ii) $R_{1}=(A, B), R_{2}=(A, C)$.
- (b) Give an algorithm To compute the closure of a sel of functional dependencies F: Given $\quad R=(A, B, C, G, H, 1) F=(A \rightarrow B, A \rightarrow C, C G \rightarrow H) . \quad C G \rightarrow I, B \rightarrow H\}$ Compute $\mathrm{F}^{+}$.
- (c) State the informal guidelines for relation schema design. Illustrale how violation of these guidelines may be harmful.
- (a) What are the differences among primary, secondary, and clustering indexes? How do these differences affect the ways in which these indexes are implemented? Which of the indexes are dense, and which are not?
- (b) Discuss the reasons for converting SQL queries into relational algebra queries belore oplimizalion is done, What is a query execulion plan?
- (c) What are the reasons for Iransaction lailures and why recovery is required? 6
- (a) Show that the two-phase locking prolocol ensures conflict serializability, and that transactions can be serialized according to their lock points.
- (b) How B Iree diffirsion from $B^{-}$sec. Explain B Iree in detail