2011 Rajasthan Technical University B.Tech 5 Semester (Back) Computer science & engineering "Database Management Systems" question paper

Question Paper Details:

University: Rajasthan Technical University
Course: B.Tech Computer science & engineering 
Subject Database management systems (back)
Exam Year:  Dec 2010/ Jan 2011
Year or Semester: Third year/ Fifth Semester
Paper Code: 5E3161 

a)      What is a DBMS? How is the data stored in DBMS and how data is accessed by users? Explain. [Marks 8]
b)      What is a transaction? How is the transaction managed in DBMS? [Marks 8]
a)      Draw a diagram of database system architecture outlining its various components. [Marks 8]
b)      Define following terms: database schema and database instances, metadata, data dictionary, data independence. [Marks 8]
                Construct an ER diagram for a Car insurance company whose customers own one or more cars. Each car has associated with zero or more number of recorded accidents by different drivers. Convert the ER diagram into relations ( tables). [marks 16]
a)      List the information we obtain from ER diagram about the data stored and relationship among them. [Marks 8]
b)      Discuss the concept of derived attributes and multi valued attributes. What happens to these attributes when ER diagram is converted into tables? [Marks 8]
a)      Discuss various types of outer join operations in relational algebra. Explain with help of examples. [Marks 8]
b)      Differentiate between domain relational calculus and tuple relational calculus. [Marks 8]  
Following tables are maintained by life insurance company.
Agents (AID, aname, branchname)
Policy holder( Pname, paddress, DOB)
Policy (AID, Pname, policy-no, policy-type, amount, nominee name, start-date, duration
Write the following queries in relational algebra:
i)                    List all policy_no held by person “John”
ii)                   List name of all policy holders whose policy was done by an agent working in “EAST” branch.
iii)                 List name and address of persons holding a policy of type “S-5-W” for amount exceeding Rs. 1,00,000.
iv)                 List all policy numbers held by agents themselves.   [Marks 4*4=16]
a)      What were the limitations of SQL which led to development of Embedded SQL?               [Marks 8]
b)      Describe following in SQL: “with” clause, “except” clause, string compression constructs in SQL. [Marks 8]
Consider following database schema:
Employee (EID, Ename, Ephoneno, DID)
Department (DID, Dname, mangeby, budget)
Phones (phoneno, DID).
Write SQL statement for following queries.
i)                    List phone numbers of department “production”. Which do not belong to any employee?
ii)                   Count number of phones for each department.
iii)                 List names it employees managed by “SMITH”.
iv)                 Find out phoneno at all employees managed by “SMITH”.    [Marks 4*4=16]
a)      Define closure at a set of functional dependencies. How do you compute this closure. [Marks 8]
b)      Given the relation r(A, B, C, D, E, F) and the set
F={A->B, CD->A, BC->D, AE->F, CE->D}. Verify that the decomposition
R1(C, D, E), R2(A,B), R3(A, E, F) and R4(A, C, E) is a BCNF loss less decomposition. [Marks 8]
a)      Give a set of FDs over a relation r, how do you find the candidate key for r. [Marks 8]
b)      Define BCNF. How does it differ from 3NF? Why is it considered a stronger form of 3NF? [Marks 8]
Return to Question Paper