BE Computer Engineering (Pokhara University) Database Management System (PU, CMP 222) Question Paper 2078

A university wants to computerize its examination records. The system must store information about Students (each identified by a registration number, with name, semester and program), Courses (course code, title, credit hours), Teachers (employee id, name, department) and the Exams that students sit for each course. A student enrolls in many courses and a course is taken by many students; each enrollment produces a grade. A teacher may teach several courses but a particular course offering is handled by exactly one teacher.

(a) Design an Entity-Relationship (ER) diagram for the above scenario, clearly showing entity sets, relationship sets, attributes (including any multivalued, composite and derived attributes), primary keys and cardinality/participation constraints. (10)

(b) Explain how you would convert the many-to-many relationship and any weak entity in your design into relational tables, stating the resulting schemas with their primary and foreign keys. (5)

Consider the relation R(A, B, C, D, E) with the set of functional dependencies F = { A → B, C, BC → D, A → E, E → C }.

(a) Compute the closure {A}+ and determine all candidate keys of R. (5)

(b) Identify the highest normal form that R currently satisfies and justify your answer with reference to the definitions of 2NF, 3NF and BCNF. (5)

(c) Decompose R into a set of relations that are in BCNF. State whether your decomposition is lossless-join and dependency-preserving. (5)

(a) Define a database transaction and explain the four ACID properties, giving a banking example for each. (6)

(b) Consider the following schedule S of two transactions T1 and T2 on data items A and B:

T1: R(A)  W(A)            R(B)  W(B)
T2:            R(A) W(A)

Draw the precedence (serialization) graph for S and determine whether S is conflict-serializable. If it is, give an equivalent serial schedule. (4)

(c) Explain how the two-phase locking (2PL) protocol guarantees serializability, and describe the problem of cascading rollback with a suitable example. (4)

Consider the following relational schema for a company database (primary keys underlined):

• EMPLOYEE(eid, ename, salary, dno)
• DEPARTMENT(dno, dname, location)
• PROJECT(pno, pname, dno)
• WORKS_ON(eid, pno, hours)

(a) Write SQL queries for the following: (8) (i) List the names of employees who work in the department located in 'Kathmandu'. (ii) For each department, display the department name and the average salary, only for departments with more than 5 employees. (iii) Find the names of employees who work on every project controlled by department number 10. (iv) Increase by 10% the salary of all employees who have logged more than 100 hours in total across all projects.

(b) Express queries (i) and (iii) above using relational algebra. (4)

Explain the three-schema (ANSI/SPARC) architecture of a DBMS with a neat diagram. Distinguish between logical and physical data independence.

Define the following relational algebra operators and give one example of each: (a) selection (σ), (b) projection (π), (c) natural join (⋈), and (d) the division operator (÷). Why is division useful in answering 'for all' type queries?

Differentiate between a primary (clustered) index and a secondary (non-clustered) index. Explain why a B+-tree is preferred over a B-tree for indexing in database systems, mentioning the role of leaf-node linkage in range queries.

What is a deadlock in a concurrent database system? Explain the wait-die and wound-wait timestamp-based deadlock prevention schemes, clearly stating which transaction is rolled back in each case.

Explain log-based recovery using deferred and immediate database modification. Describe the use of <checkpoint> records and how the UNDO and REDO operations are applied during recovery after a system crash.

Differentiate between DDL and DML in SQL with examples. Explain the purpose of PRIMARY KEY, FOREIGN KEY, UNIQUE and CHECK constraints, and describe the meaning of the ON DELETE CASCADE referential action.

Discuss the main goals of database security. Explain discretionary access control using the SQL GRANT and REVOKE statements, and briefly describe how an SQL injection attack threatens database security and one method to prevent it.

What are insertion, deletion and update anomalies in an un-normalized relation? Using a single example table that stores student, course and instructor data together, illustrate each type of anomaly and explain how normalization removes them.

Explain the concepts of generalization, specialization and aggregation in the Enhanced ER (EER) model. Illustrate specialization with an example showing disjoint versus overlapping and total versus partial constraints.