POKHARA UNIVERSITY

Level: Bachelor                                  Semester: Spring                                   Year: 2023

Programme: BBA/BI/TT/BCIS/BHCM/BHM                                              Full Marks: 100

Course: Database Management System                                                 Pass Marks: 45

Time: 3 hrs.

Candidates are required to answer in their own words as far as practicable The figures in the margin indicate full marks.

                                                                 Section "A"

                                                Very Short Answer Questions

Attempt all the questions. [10×2] 

1,Write any four objectives of DBMS.

2. Define multivalued attributes with examples.

3. Define Query Language.

4. Define Integrity Constraints.

5. What are the types of database anomalies? 

6. What do you mean by ACID properties?

7. What do you mean by Validation based protocols?

8. Define the concept of recovery algorithms.

 9. Explain the concept of roles in a database system.

10. Briefly explain about Function and Procedure.

                                                              Section "B"

                                               Descriptive Answer Questions

Attempt any six questions. [6×10] 

11. Explain the applications of database management systems in various industries.

12. Consider the following relations:

 Project(P_ID,P_Name,P_Location, Type) 

 Employee(Emp_ID,Emp_Name, Address,Salary,Post,Date_Join)

 Works(P_ID,Emp_ID,Shift) 

Write SQL statements to:

a. Create relation Employee.

b. List the employee whose salary is average above the average salary. 

c. To retrieve the name and address of the employee who work in "morning" shift at a project located in "Pokhara". 

d. Change address and post of the employee "Ram" to "Pokhara" and "Lecturer".

é. Find all Employees whose name ends with 'S'. 

13. Explain the concepts of INSERT, UNION, GRANT, NATURAL JOIN, and COMMIT using SQL examples.                                                                  

14. Why do we need Normalization? Explain INF, 2NF & 3NF along with examples. 

15. a) What do you mean by Serializability? Explain types of serializability. 

      b) Compare Lock based & Timestamp based protocols.

16. Explain how concurrency control protocols ensure data consistency. Discuss the significance of remote backup systems in recovery systems. [5+5]

17. R= (A,B,D,H,I,J)

       F= {AB,AD, DHI, DH→J,B→ I}

  a) Determine closure of set of functional dependencies F*. 

  b) Determine attribute set closure (AH)*

                                                                 Section "C"

                                                              Case Analysis

18. Read the case situation given below and answer the questions that follow:

 Design a database for a worldwide package delivery company (e.g., DHL or FedEx). The database must be able to keep track of customers who ship items and customers who receive items; some customers may do both. Each package must be identifiable and trackable, so the database must be able to store the location of the package and its history of locations. Locations include trucks, planes, airports, and warehouses. 

You have been hired to develop the database system for this delivery company.

 a) Identify entity , attributes and relationships. [5]

 b) Draw E-R Diagram.  [10]

c) Construct schema diagram using above information.[5]