Database Management systems (DBMS) SPPU Insem Question Paper Aug 2025

Questions

1. Q1

   1. a)

      For the database system to be usable, it must retrieve data efficiently. The need of efficiency has led designers to use complex data structures to represent data in the database. Developers hide this complexity from the database system users through several levels of abstraction. Explain those levels of abstraction in detail.

      [5]
   2. b)

      What is Specialization and generalization in Extended E-R diagram? What is the difference between them? Why do we not display the difference in schema diagram?

      [6]
   3. c)

      Explain with example what is physical data independence. Also explain its importance.

      [4]
   OR

2. Q2

   1. a)

      Draw the neat diagram of Database System Structure and explain its components in detail.

      [8]
   2. b)

      A post office has few postmen who go every day to distribute letter. Every morning post office receives a large number of registered letters. the post office intends to create a database to keep track of these letters. i) Every letter has a sender, an origin post office from where it was sent, a destination post office to which it is to be sent, a date of registration, date of arrival at destination post office, receiver and a status. ii) Every sender has a name, an address. iii) Every receiver has a name, an address. 2 iv) Every postman has a designated area where he delivers letters. v) The area consists of a set of streets under the jurisdiction of the post office. vi) Every street consists of a set of buildings. vii) Every building has number and may be name. It may be housing more than one family. viii) The status of the letter can be not yet taken for delivery, delivered, address not available, address not known, addressee did not accept the letter, redirected to the address of address and sent to the sender. Identify the relationship among the entities along with the mapping cardinalities, keys in the E.R. diagram. Construct appropriate tables for E-R diagram designed with above requirements

      [7]

3. Q3

   1. a)

      Consider the following schemas Emp(Emp_no, Emp_name, Dept_no) Dept(Dept_no, Dept_name) Address(Dept_name, Dept_location) Write SQL queries for the following i) Display the location of department where employee ‘Ram’ is working. ii) Create a view to store total no of employees working in each department in ascending order iii) Find the name of the department in which no employee is working.

      [6]
   2. b)

      Explain the concept of Referential and Entity Integrity constraint with example.

      [5]
   3. c)

      Explain cursor in SQL with example.

      [4]
   OR

4. Q4

   1. a)

      What is trigger? How to create it? Discuss various types of triggers.

      [5]
   2. b)

      Write a PL/SQL block of code which accepts the rollno from user. The attendance of rollno entered by user will be checked in student_attendance (RollNo, Attendance) table and display on the screen.

      [5]
   3. c)

      What is sequence? How to create and use sequence in SQL? 

      [5]