BE Computer Engineering (IOE, TU) Database Management System (IOE, CT 652) Question Paper 2078

A university wants to computerize its academic records. The system must track students (with roll number, name, semester and program), courses (with course code, title and credit hours), teachers (with employee id and name) and departments. A student enrolls in many courses and obtains a grade in each; a course is taught by one or more teachers; every teacher and every course belongs to exactly one department.

(a) Draw a complete Entity-Relationship (ER) diagram for the above system, clearly showing entity sets, attributes, primary keys, relationship sets and the cardinality/participation constraints. [8]

(b) Convert your ER diagram into a set of relational schemas, underlining the primary keys and indicating the foreign keys. [4]

Consider the relation R(A, B, C, D, E) with the set of functional dependencies

(a) Compute the closure $(A)^+$ and $(B)^+$ and determine all the candidate keys of R. [5]

(b) Explain the difference between 3NF and BCNF. Determine the highest normal form satisfied by R and justify your answer. [4]

(c) If R is not in BCNF, decompose it into BCNF relations and state whether your decomposition is lossless and dependency-preserving. [3]

(a) State and explain the ACID properties of a transaction with a suitable example for each. [4]

(b) Given the schedule below for transactions $T_1$ and $T_2$, draw the precedence (serialization) graph and determine whether the schedule is conflict-serializable. If it is, give an equivalent serial schedule.

[4]

(c) Explain how two-phase locking (2PL) guarantees serializability and discuss why basic 2PL can still lead to deadlock and cascading rollback. [4]

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

• Customer(cust_id, name, city)
• Account(acc_no, branch, balance)
• Depositor(cust_id, acc_no)

Write queries for the following:

(a) In relational algebra, find the names of all customers who live in 'Kathmandu' and hold an account with a balance greater than 50000. [3]

(b) In SQL, list each branch with the total balance of all accounts in that branch, showing only branches whose total balance exceeds 1,000,000. [3]

(c) In SQL, find the names of customers who hold more than one account. [4]

Define super key, candidate key, primary key and foreign key. Using a suitable example, explain how a foreign key enforces referential integrity, and describe what happens on a DELETE with the ON DELETE CASCADE option.

(a) Differentiate between a primary (clustered) index and a secondary (non-clustered) index. [3]

(b) Explain why a B+ tree is preferred over a B tree for database indexing, and state one advantage of B+ tree indexing over a simple ordered index file. [3]

Explain static hashing and discuss the problem of bucket overflow. How does dynamic (extendible) hashing overcome the limitations of static hashing? Illustrate with the role of the directory and the global/local depth.

(a) Explain the log-based recovery technique using deferred and immediate database modification. [3]

(b) What is a checkpoint and how does it reduce the work done during recovery after a system crash? [3]

(a) What is a deadlock? State the conditions necessary for a deadlock to occur. [2]

(b) Compare deadlock prevention schemes wait-die and wound-wait, clearly explaining the action taken when an older transaction requests a lock held by a younger one and vice versa. [4]

(a) Explain the different levels at which database security must be enforced. [3]

(b) Describe the use of the SQL GRANT and REVOKE statements for authorization, and explain what the WITH GRANT OPTION clause does. [3]

Explain the JOIN operation in relational algebra. Differentiate between a natural join, a theta join and an outer join with a small example for each.

Explain the following extended-ER concepts with suitable examples: (a) weak entity set and its identifying relationship, (b) specialization and generalization, and (c) the aggregation abstraction.