Database Management System PU Spring 2023 question paper

• a) Differentiate between database schema and instances. Briefly describe DDL, DML and DCL.
• b) Define relational data model. Draw an E-R diagram for a Library Management System including primary key, weak entity, composite attribute, derived attribute and multivalued attributes in your ER diagram.

• a) Suppose we have the following relation. [<3 | imployee(person_name, street, city) iti diced Bo! tr Works (person_name, company_name, salary) Company (company_name, city) Write relational algebraic expressions for the following queries:
• i) List the name and city of employee who work in “pokhara” and have salary greater than Rs. 50,000.
• ii) Find the names of all employees who work for “ABC bank”.
• iii) Delete all employee who come from “Chitwan”.
• iv) Increase salary of all employee by 15%.
• b) What are different kinds of joins? Explain in brief. i

• a) Write SQL statements for the following queries using the given Employees relation: 1
• i) Create a database named Company and Employees relation.
• ii) Create a view that shows the E_id, Department and Hire_Date of all employees.
• iii) Modify the table such that the Department of Biren is HR now.
• iv) Delete the record of employees whose Lname is “Pandey”.
• b) What is referential integrity? Explain about a trigger with an example.

• a) What is database normalization? Explain in detail about INF, 2NF, 3NF with suitable examples.
• b) What are authorization and authentication? Why are they important? Explain in detail.

• a) What are the steps in query processing? Make an operator tree for the following SQL expression: Select customer_name FROM branch, account, depositor WHERE branch_city=’btl’ AND balance>2000;
• b) What are the benefits of using B Tree index over the sequential and = _g indexed sequential file organization? Explain.

• a) Explain Log based recovery system with an appropriate log record example.
• b) Why should the transactions’ schedule be serialized? Explain conflict and view serializability with example.

Write short notes on: (Any two) 2x5

• a) Data Dictionary kM eroEn otes
• b) Stored procedure ————
• c) Object oriented Database 2