CSC265 · TU past paper
Database Management System 2076 question paper
The complete TU 2076 exam paper for Database Management System (CSC265), all 12 questions with solved model answers written to the mark scheme.
Tap a question to open its answer.
- 110 marksAdvantages of Using the DBMS ApproachHideAnswer
What are the advantages of using Database Management System over traditional filing system? Explain different data models with example.[10]
--- A Database Management System (DBMS) is a software system that enables users to define, create, maintain, and control access to a database. Traditional file systems store data in flat files managed by individual application programs, ...
- 210 marksTwo-Phase Locking TechniqueHideAnswer
What is concurrency control? Name various methods of controlling the concurrency control? Differentiate between Binary lock and shared/Exclusive lock.[10]
Concurrency Control is the management procedure required for controlling the concurrent execution of operations that take place on a database. It is a procedure of managing simultaneous operations without conflicting with each other. - T...
- 310 marksNormal Forms Based on Primary KeysHideAnswer
What is normal form? Explain their types. Explain about loss-less join decomposition.[10]
The normal form of a relation refers to the highest normal form condition that it meets, and hence indicates the degree to which it has been normalized. Normal forms are used to eliminate redundancy, insertion anomalies, deletion anomali...
- 45 marksThree-Schema Architecture and Data IndepenHideAnswer
What is data abstraction? What are three levels of data abstraction? Explain. [5]
Data abstraction refers to the hiding mechanism of details of data organization and storage, while highlighting the essential features for an improved understanding of data. It allows users to interact with the database without needing t...
- 55 marksEntity Types, Entity Sets, Attributes, andHideAnswer
What is difference between Entities and Entity sets? Explain with example. [5]
An entity is a single, distinct real-world object or thing that exists independently and can be distinguished from other objects. It is a specific instance or occurrence of something in the real world that has some properties (attributes...
- 65 marksData Definition and Data TypesHideAnswer
Create two table Courses (CID, Course, Dept) and HoD (Dept, Head) using SQL language with all constraints [Primary key, Foreign key and Referential Integrity]. Assume the types of attributes by your own. [5]
- Courses (CID, Course, Dept) - HoD (Dept, Head) --- Attribute Type Reason ------------------------- CID INT Numeric course identifier Course VARCHAR(50) Course name as string Dept VARCHAR(30) Department name as string Head VARCHAR(50) H...
- 75 marksRelational Model Constraints and RelationaHideAnswer
Differentiate between Integrity and Security with example. [5]
Database integrity refers to the correctness, accuracy, and consistency of data stored in the database. It ensures that the data follows certain rules (called integrity constraints) so that the data remains valid and meaningful at all ti...
- 85 marksCharacterizing Schedules Based on SerializHideAnswer
Define schedule and serializability. How can you test the serializability? [5]
Schedule and Serializability
Schedule
A schedule (also called a history) is an ordering of the operations from a set of concurrent transactions, where the operations of each individual transaction appear in the same order as they do in that transaction. A schedule defines the sequence in which operations (read, write, commit, abort) from multiple transactions are interleaved and executed by the DBMS.
Example: If T1 and T2 are two transactions, a possible schedule S could be:
S: R1(X) → R2(X) → W1(X) → W2(X)
Serializability
A concurrent schedule is said to be serializable if its final result (effect on the database) is totally the same as the final result produced by some serial schedule (where transactions execute one after another without interleaving).
Since serial schedules are always considered correct (no concurrency issues), any serializable schedule is also considered correct.
There are two types of serializable schedules:
1. Conflict Serializable
A schedule is conflict serializable if it can be transformed into a serial schedule by swapping non-conflicting operations.
2. View Serializable
A schedule is called view serializable if its view equals that of a serial schedule (no overlapping transactions). A conflict serializable schedule is always view serializable, but if the schedule contains blind writes, it may be view serializable without being conflict serializable.
Testing for Serializability (Precedence Graph Method)
The most common method to test conflict serializability uses a Precedence Graph (also called a serialization graph).
Algorithm:
- Look at only
read_Item(X)andwrite_Item(X)operations in the schedule. - Construct a precedence graph -- a directed graph where each transaction Ti is a node.
- Draw a directed edge from Ti to Tj if one of the operations in Ti appears before a conflicting operation in Tj. Two operations conflict if:
- They belong to different transactions
- They access the same data item X
- At least one of them is a write
- The schedule is conflict serializable if and only if the precedence graph has NO cycles.
Example:
Consider Schedule S with transactions T1 and T2:
S: R1(X) → W2(X) → W1(X) → R2(Y) → W2(Y)Conflict Detected Edge R1(X) before W2(X) T1 → T2 W2(X) before W1(X) T2 → T1 Precedence Graph:
T1 -----> T2 T2 -----> T1 (CYCLE EXISTS)Since a cycle exists, this schedule is NOT conflict serializable.
Summary Table
Property Meaning Serial Schedule Transactions execute one after another (always correct) Serializable Schedule Equivalent in result to some serial schedule Test Method Precedence graph -- no cycle = serializable - Look at only
- 95 marksTwo-Phase Locking TechniqueHideAnswer
What is Granularity of data items? How does it effect in concurrency control? [5]
A database is basically represented as a collection of named data items. The size of a data item is called its granularity. A data item can be defined at different levels of size, for example: Granularity Level Example ------ Fine (Small...
- 105 marksTwo-Phase Locking TechniqueHideAnswer
Explain 2 phase locking technique in brief. [5]
Locking is an operation which secures permission to read or permission to write a data item. Two Phase Locking (2PL) is a concurrency control protocol that ensures serializability by regulating when transactions may lock and unlock data ...
- 115 marksRecovery ConceptsHideAnswer
What are the different approaches of Database recover? What should log file maintain in log-based recovery? [5]
Database recovery techniques are methods used to restore the database to a correct consistent state after failures such as system crashes, transaction errors, viruses, or incorrect command execution. The main approaches are: --- A log (a...
- 125 marksRelational Model Constraints and RelationaHideAnswer
Explain the use of primary and foreign key in DBMS with example. What is the role of foreign key? [5]
A primary key is an attribute (or a set of attributes) that uniquely identifies each tuple (row) in a relation (table). It must satisfy two properties: - Uniqueness: No two rows can have the same primary key value. - Not Null: The primar...