Download DU (University of Delhi) B-Tech 4th Semester 2330 Database Management Systems Question Paper

FirstRanker.com

A Firstranker's choice

FirstRanker.com

--- Content provided by FirstRanker.com ---

[This question paper contains 6 printed pages.]

Sr. No. of Question Paper : 2330 F—4 Your Roll No.......................

Unique Paper Code : 12341403

Name of the Course : B.Tech in Computer Science

Name of the Paper : Database Management Systems (DBMS)

--- Content provided by FirstRanker.com ---

Semester : IV

Duration : 3 Hours Maximum Marks : 75

Instructions for Candidates

1. Write your Roll No. on the top immediately on receipt of this question paper.
2. Q. 1 is compulsory.

--- Content provided by FirstRanker.com ---

3. Attempt any four questions from Q. 2 to Q. 7.
4. Parts of a question must be answered together.

1. 1. What does defining, manipulating and sharing of a database mean ? (3)
   2. Differentiate between the following :
      1. Intension and extension of a schema
      2. Weak and strong entities

      --- Content provided by FirstRanker.com ---

      3. Primary key and foreign key (2x3)
   3. For the relation schemas: R(A, B, C) and S(D, E, F), write an expression in relational algebra that is equivalent to the following SQL queries :
      1. Select * from R where B = 17
      2. Select A, E from R, S (3)

   --- Content provided by FirstRanker.com ---

   4. What is a key ? Why should the key have time-invariant property ? (3)
   5. Give one example each of insertion and deletion anomaly in the relation schema EMP_DEPT(Ename, Ssn, Bdate, Address, Dno, Dname, Dlocation). How can these anomalies be removed from this relation ? (1+1+4)
   6. Explain with the help of an example specialization of a class. How is it represented diagrammatically in an EER diagram ? (4)
   7. Map the following ER diagram into relational tables, specifying the table names and their attribute names. (4)
      Author m @ 1 Book

   --- Content provided by FirstRanker.com ---

   8. Which constraint can be violated by delete operation in relational model ? What are the options available if deletion causes a violation ? (4)
   9. Explain the ACID properties of transactions. (4)
2. 1. Briefly explain the following type of DBMS users :
      1. database administrators
      2. naive end users

      --- Content provided by FirstRanker.com ---

      3. sophisticated end users (6)
   2. What operators form the complete set of relational algebra operations ? Define join and intersection in terms of these operators. (4)
3. 1. What is the difference between logical and physical data independence ? Which is harder to achieve and why ? (3+2)

   --- Content provided by FirstRanker.com ---

   2. Consider the following tables T1 and T2:
      T1 T2
      P R P S T
      10 a 5 10 b 6
      15 b 8 25 c 3
      

      --- Content provided by FirstRanker.com ---

      25 a 6 10 b 9
      Show the result of the following operations :
      1. T1 U T2
      2. T1 Right Outer Join T2 (2+3)

--- Content provided by FirstRanker.com ---

4. Consider the following relations containing airline flight information (keys are underlined) :
   Flight(Flight No, From, To, Distance, Depart Time, Arrival Time, AircraftId)
   Aircraft(AircraftId, Aircraft Name, Range)
   Certified(PilotId, AircraftId)
   Pilot(PilotId, Pname, Salary)
   

   --- Content provided by FirstRanker.com ---

   Note that pilots can fly only those aircrafts which they are certified to fly.
   1. Write relational algebra queries for the following :
      1. Find PilotIds of pilots who are certified to fly all aircrafts having range more than 3000.
      2. Find the names of pilots who earn more than average salary. (2x2)
   2. Write SQL statements for the following :
      1. Create table Flight. Define suitable constraints for the attributes.
      2. Display names of pilots who have flown aircraft with id = 123 from “Delhi’ to ‘Mumbai’.

      --- Content provided by FirstRanker.com ---

      3. Remove the certification of pilot with id =“P123’ to fly aircraft with id = 286. (2x3)
5. 1. Consider the relation REFRIG(MODEL#, YEAR, PRICE, MANUF_PLANT, COLOR), which is abbreviated as REFRIG(M, Y, P, MP, C), and the following set F of functional dependencies : F= {M -> MP, {M,Y} ->P, MP -> C}
      Evaluate each of the following as a candidate key for REFRIG, giving reasons why it can or cannot be a key: {M} and {M,Y} (3)

   --- Content provided by FirstRanker.com ---

   2. Indicate in what normal form is R in? Normalize it till 3NF. (3)
6. 1. Consider the following relation :
      A B C
      a1 b1 c1
      

      --- Content provided by FirstRanker.com ---

      a1 b1 c2
      a2 b1 c1
      a2 b1 c3
      For the given relation state, do the following functional dependencies hold (justify your answer) :
      1. A->B
      2. A -> C

      --- Content provided by FirstRanker.com ---

      3. C -> B
      4. B-> A
7. 1. Draw an ER diagram for the following situation :
      

      --- Content provided by FirstRanker.com ---

      There are various departments in the store. One department sells many items. Some items may be sold by more than one department. A manager is an employee who may look after more than one department but a department may be looked after by only one manager. A supplier may supply more than one item. All departments sell certain items. Every department has employees working in it. All items are supplied by suppliers.
      Think of the appropriate attributes for entities involved. Clearly specify cardinality ratios and participation constraints. State any assumptions that you make for drawing ER diagram. (6)
   2. Which of the following EER diagram is incorrect and why ? Clearly state any assumption if any. (4)
      E1
      E o
      

      --- Content provided by FirstRanker.com ---

      1 —N— E3
      E2
8. 1. Draw the query tree for the following expression:
      πname, ssn ((πname, ssn (EMP) — ρname, ssn (πname, ssn (DEPENDENT))) * EMP) (5)

   --- Content provided by FirstRanker.com ---

   2. Comment on the following :
      1. Every relation has at least one key.
      2. Every primary key is a superkey but not vice-versa. (2+3)

FirstRanker.com

--- Content provided by FirstRanker.com ---