2024 question paper

DATABASE MANAGEMENT SYSTEM

26 questions

1. Q1a. Which of the following is generally used for performing tasks like creating the structure of the relations or deleting relations? - (i) DML - (ii) Query - (iii) Relational schema - (iv) DDL20242m

   Unit 1: Database System Architecture

   Which of the following is generally used for performing tasks like creating the structure of the relations or deleting relations?

   • (i) DML
   • (ii) Query
   • (iii) Relational schema
   • (iv) DDL

   View this question on its own page →

   CopySearch GoogleAsk AI

   Worked Solution

   Answer: (iv) DDL

   Explanation: DDL (Data Definition Language) is used for structure operations like CREATE, ALTER, DROP, TRUNCATE. DML is for data manipulation (SELECT, INSERT, UPDATE, DELETE). Query is a DML SELECT. Relational schema is the design itself, not the language.

2. Q1b. __________ operations do not preserve non-matched tuples. - (i) Inner join - (ii) Left outer join - (iii) Query - (iv) Relational schema20242m

   Unit 2: Relational Query Languages

   __________ operations do not preserve non-matched tuples.

   • (i) Inner join 
   • (ii) Left outer join 
   • (iii) Query 
   • (iv) Relational schema

   View this question on its own page →

   CopySearch GoogleAsk AI

   Worked Solution

   Answer: (i) Inner join

   Explanation: Inner join returns only matched tuples from both relations where join condition holds, non-matched tuples are discarded. Left outer join preserves all tuples from left relation plus matched from right, filling NULL where no match. Right/Full outer join similarly preserve non-matched. Query and Relational schema are not join operations.

3. Q1c. What do you mean by one-to-many relationships? - (i) One class may have many teachers - (ii) One teacher can have many classes - (iii) Many classes may have many teachers - (iv) Many teachers may have many classes20242m

   Unit 1: Database System Architecture

   What do you mean by one-to-many relationships?

   • (i) One class may have many teachers
   • (ii) One teacher can have many classes
   • (iii) Many classes may have many teachers
   • (iv) Many teachers may have many classes

   View this question on its own page →

   CopySearch GoogleAsk AI

   Worked Solution

   Answer: (ii) One teacher can have many classes

   Explanation: One-to-many (1:N) means one instance of entity A relates to many instances of entity B, but not vice versa. Here, one teacher teaches many classes, but a class is taught by one teacher (in this example). Option (i) is also 1:N but phrased as "One class may have many teachers" which would be many-to-one from other side. Canonical 1:N example is teacher -> classes. Many-to-many would be many teachers to many classes.

4. Q1d. A Database Management System is a type of __________ software. - (i) It is a type of system software - (ii) It is a kind of application software - (iii) It is a kind of general software - (iv) Both (i) and (ii)20242m

   Unit 1: Database System Architecture

   A Database Management System is a type of __________ software.

   • (i) It is a type of system software
   • (ii) It is a kind of application software
   • (iii) It is a kind of general software
   • (iv) Both (i) and (ii)

   View this question on its own page →

   CopySearch GoogleAsk AI

   Worked Solution

   Answer: (i) It is a type of system software (most exam expects (ii) or (iv) - see note)

   Explanation: DBMS is system software that manages databases, provides abstraction over OS. Some classifications call it application software because it runs on OS, but academically it is considered system software / middleware between OS and applications. Given options, system software (i) is closest. Some keys mark (iv) Both system and application as correct because DBMS exhibits both properties - system for DB operations, application for users. Mention both views.

5. Q1e. Which of the following refers to the “data about data”? - (i) Directory - (ii) Warehouse - (iii) Sub Data - (iv) Meta Data20242m

   Unit 1: Database System Architecture

   Which of the following refers to the “data about data”?

   • (i) Directory 
   • (ii) Warehouse 
   • (iii) Sub Data 
   • (iv) Meta Data

   View this question on its own page →

   CopySearch GoogleAsk AI

   Worked Solution

   Answer: (iv) Meta Data

   Explanation: Metadata is data about data - describes structure, schema, types, constraints, provenance. Examples: data dictionary, schema info, column types. Directory is file listing, Warehouse is integrated storage for analytics, Sub Data is not standard term.

6. Q1f. f) To which of the following term “DBA” referred? - (i) Data Bank Administrator - (ii) Data Administrator - (iii) Database Administrator - (iv) None of the above20242m

   Unit 3: Storage and Transaction Processing

   f) To which of the following term “DBA” referred?

   • (i) Data Bank Administrator 
   • (ii) Data Administrator 
   • (iii) Database Administrator 
   • (iv) None of the above

   View this question on its own page →

   CopySearch GoogleAsk AI

   Worked Solution

   Answer: (iii) Database Administrator

   Explanation: DBA = Database Administrator - person responsible for design, implementation, maintenance, security, backup, performance tuning of database. Data Administrator is broader organizational role, Data Bank Administrator is not standard.

7. Q1g. Which of the following is not an example of DBMS? - (i) MySQL - (ii) Microsoft Access - (iii) IBM DB2 - (iv) Google20242m

   Unit 1: Database System Architecture

   Which of the following is not an example of DBMS?

   • (i) MySQL 
   • (ii) Microsoft Access 
   • (iii) IBM DB2 
   • (iv) Google

   View this question on its own page →

   CopySearch GoogleAsk AI

   Worked Solution

   Answer: (iv) Google

   Explanation: MySQL, Microsoft Access, IBM DB2 are RDBMS products. Google is a company/search engine, not a DBMS product (though it uses Bigtable/Spanner internally, Google itself is not marketed as DBMS). Hence Google is not example of DBMS.

8. Q1h. Which of the following is known as a set of entities of the same type that share same properties or attributes? - (i) Relation set - (ii) Tuples - (iii) Entity set - (iv) Entity relation model20242m

   Unit 1: Database System Architecture

   Which of the following is known as a set of entities of the same type that share same properties or attributes?

   • (i) Relation set 
   • (ii) Tuples 
   • (iii) Entity set 
   • (iv) Entity relation model

   View this question on its own page →

   CopySearch GoogleAsk AI

   Worked Solution

   Answer: (iii) Entity set

   Explanation: Entity set is collection of entities of same type sharing same properties/attributes. Example: Student entity set with attributes rollno, name. Relation set is not standard term, Tuples are single rows/instances, Entity relation model is ER diagram, not a set.

9. Q1i. The ability to query data, as well as insert, delete, and alter tuples, is offered by: - (i) TCL (Transaction Control Language) - (ii) DCL (Data Control Language) - (iii) DDL (Data Definition Language) - (iv) DML (Data Manipulation Language)20242m

   Unit 2: Relational Query Languages

   The ability to query data, as well as insert, delete, and alter tuples, is offered by:

   • (i) TCL (Transaction Control Language)
   • (ii) DCL (Data Control Language)
   • (iii) DDL (Data Definition Language)
   • (iv) DML (Data Manipulation Language)

   View this question on its own page →

   CopySearch GoogleAsk AI

   Worked Solution

   Answer: (iv) DML (Data Manipulation Language)

   Explanation: DML provides ability to query and modify data: SELECT, INSERT, UPDATE, DELETE. TCL is for transaction control (COMMIT, ROLLBACK), DCL for access control (GRANT, REVOKE), DDL for structure (CREATE, ALTER, DROP).

10. Q1j. Which command is used to remove a relation from SQL? - (i) Drop table - (ii) Delete - (iii) Purge - (iv) Remove20242m

    Unit 2: Relational Query Languages

    Which command is used to remove a relation from SQL?

    • (i) Drop table 
    • (ii) Delete 
    • (iii) Purge 
    • (iv) Remove

    View this question on its own page →

    CopySearch GoogleAsk AI

    Worked Solution

    Answer: (i) Drop table

    Explanation: DROP TABLE removes relation schema and data completely from database. DELETE without WHERE deletes all rows but keeps structure (DML). PURGE is Oracle extension to permanently remove from recyclebin. REMOVE is not SQL standard.

11. Budget App•POCKETLY

    Pocketly on Android

    Log daily food, travel, and hostel expenses instantly. Set monthly spending limits and see where your money goes without complex spreadsheets.

    Install Pocketly
12. Q2a. Illustrate the database characteristics, How they are different from File systems.20247m

    Unit 1: Database System Architecture

    Illustrate the database characteristics, How they are different from File systems.

    View this question on its own page →

    CopySearch GoogleAsk AI

    Worked Solution

    Database Characteristics vs File System:

    Database Characteristics:

    • Self-describing (catalog/metadata)
    • Data abstraction & independence (physical/logical)
    • Concurrent multi-user access with ACID
    • Controlled redundancy, data integrity constraints
    • Security, backup/recovery, views

    File System vs DBMS Differences:

    1. Redundancy: File system stores duplicate data in separate files leading inconsistency; DBMS controls redundancy via normalization.
    2. Inconsistency: File updates need multiple files, easy inconsistency; DBMS single repository ensures consistency.
    3. Data Dependency: File system program depends on file format (structural dependence); DBMS provides data independence.
    4. Integrity: File system enforces in application code; DBMS enforces declarative constraints.
    5. Atomicity/Concurrency: File system no transaction/ACID; DBMS ensures atomic, isolated concurrent transactions, locking.
    6. Security: File OS-level permissions only; DBMS GRANT/REVOKE fine-grained.
    7. Recovery: File system manual backup; DBMS log-based recovery, crash recovery.
    8. Sharing: File limited; DBMS multi-user with views.

    Example: Banking file system storing customer in two files vs DBMS one Customer table ensures single update propagates, concurrent withdrawals handled via locking.

13. Q2b. Draw and explain three level architecture of database system.20247m

    Unit 1: Database System Architecture

    Draw and explain three level architecture of database system.

    View this question on its own page →

    CopySearch GoogleAsk AI

    Worked Solution

    Three Level Architecture (ANSI/SPARC):

    1. External/View Level (highest): Individual user views - subset of database tailored for user group. Hide irrelevant data. Example: Student view shows grades for adviser, Accounts view hides grades. Provides logical data independence via view mapping.

    2. Conceptual/Logical Level: Community view - entire database structure - entities, relationships, attributes, constraints, semantics. DBA defines using conceptual schema (ER + relational). Example: Tables Student, Course, Enroll with relationships. Physical independence maintained - conceptual unaffected by physical changes.

    3. Internal/Physical Level: How data physically stored - files, indexes, hashing, blocks, storage allocation, compression. Managed by OS.

    Mappings: External-Conceptual mapping translates view query to conceptual; Conceptual-Internal mapping translates conceptual operations to physical storage access.

    Advantages: Data independence (physical and logical), security (views), easier DBA management. Diagram: Three layers with mappings between, users at top accessing views, physical storage at bottom.

14. Q3a. Discuss about different keys used in database design with examples20247m

    Unit 2: Relational Query Languages

    Discuss about different keys used in database design with examples

    View this question on its own page →

    CopySearch GoogleAsk AI

    Worked Solution

    Keys in Database Design with Examples:

    • Candidate Key: Minimal set uniquely identifies tuple. Example: Student {rollno}, {email} - both candidates.
    • Primary Key: Chosen candidate key to uniquely identify row, cannot be NULL. Example: rollno PK for Student.
    • Alternate Key: Candidate keys not chosen as primary. Example: email alternate.
    • Super Key: Any superset of candidate key. Example: {rollno, name}, {rollno, email} - super keys.
    • Foreign Key: Attribute in one table referencing PK of another for referential integrity. Example: Enroll(s_id) references Student(rollno).
    • Composite Key: Key with multiple attributes. Example: Enroll primary key (s_id, c_id).
    • Unique Key: Ensures uniqueness but allows one NULL (varies by DBMS).
    • Natural vs Surrogate: Natural uses real data (rollno), Surrogate artificial (auto-increment id).

    Importance: ensure entity integrity, referential integrity, normalization, indexing.

15. Q3b. Define a database and explain its importance in modern software systems.20247m

    Unit 1: Database System Architecture

    Define a database and explain its importance in modern software systems.

    View this question on its own page →

    CopySearch GoogleAsk AI

    Worked Solution

    Database: Organized collection of related, integrated data stored to serve multiple applications with minimal redundancy, shared and managed by DBMS. Includes data plus metadata.

    Importance in Modern Software Systems:

    • Centralized data management avoids duplication, ensures consistency
    • Efficient query processing (SQL, indexing) for applications (web, mobile,ERP)
    • Concurrency, transaction support (ACID) for multi-user systems like banking, e-commerce
    • Security, authorization, privacy for sensitive data
    • Scalability, handling big data, distributed access, cloud
    • Integration across microservices via single source of truth
    • Analytics, BI, ML pipelines depend on curated database
    • Recovery, durability prevents loss

    Examples: E-commerce orders DB powers catalog/cart/payments; social network stores graphs; IoT sensor DB enables real-time monitoring. Without DBMS, file-based chaos, no integrity.

16. Q4a. Distinguish strong entity set with weak entity set. Draw an E-R diagram to illustrate weak entity set.20247m

    Unit 1: Database System Architecture

    Distinguish strong entity set with weak entity set. Draw an E-R diagram to illustrate weak entity set.

    View this question on its own page →

    CopySearch GoogleAsk AI

    Worked Solution

    Strong vs Weak Entity Set:

    • Strong Entity: Exists independently, has primary key. Represented by single rectangle. Example: Customer (cust_id), Car (car_id).

    • Weak Entity: Cannot be uniquely identified by own attributes alone, depends on strong entity, no PK, has partial key (discriminator). Existence dependent, total participation. Represented by double rectangle, identifying relationship double diamond. Example: Accident depending on Car, Dependent depending on Employee, Loan Payment depending on Loan.

    Difference table: Strong has PK, existence independent, single rectangle; Weak needs owner, partial key + owner PK forms PK, double rectangle, always total participation.

    E-R example: Employee (strong) -- Employs -- Dependent (weak, attributes: dep_name (partial key), age, relationship). Dependent PK = emp_id + dep_name. Relationship is identifying, cardinality 1:N, double lines from weak to relationship.

17. Q4b. Write an SQL query to retrieve all employees from an "Employees" table with specific criteria (e.g., department, salary range).20247m

    Unit 2: Relational Query Languages

    Write an SQL query to retrieve all employees from an "Employees" table with specific criteria (e.g., department, salary range).

    View this question on its own page →

    CopySearch GoogleAsk AI

    Worked Solution

    SQL query to retrieve employees with criteria (example department='IT' and salary range 40000-60000):

    SELECT * FROM Employees WHERE department = 'IT' AND salary BETWEEN 40000 AND 60000;

    SELECT emp_id, name, department, salary FROM Employees WHERE department = 'Sales' AND salary > 50000 ORDER BY salary DESC;

    With pattern: SELECT * FROM Employees WHERE department IN ('IT','HR') AND salary >= 40000 AND salary <= 80000;

    Explain: SELECT chooses columns, WHERE filters rows, BETWEEN for range, AND combines conditions. Can add ORDER BY, LIMIT.

    If parameterized: SELECT * FROM Employees WHERE department = ? AND salary BETWEEN ? AND ?;

18. Q5a. Define the process of database normalization and its importance in maintaining d integrity20247m

    Unit 2: Relational Query Languages

    Define the process of database normalization and its importance in maintaining d integrity

    View this question on its own page →

    CopySearch GoogleAsk AI

    Worked Solution

    Database Normalization Process and Importance:

    Process: Organizes relation to minimize redundancy and anomalies via normal forms using functional dependencies.

    Steps:

    • 1NF: Remove repeating groups, ensure atomic values. Example: Split multivalued phone numbers into separate rows.
    • 2NF: Remove partial dependency (non-prime depends on part of candidate key). Decompose.
    • 3NF: Remove transitive dependency (non-prime depends on another non-prime).
    • BCNF: Every determinant is superkey (stronger 3NF).
    • 4NF: Remove multi-valued dependencies, 5NF join dependencies.

    Importance for Data Integrity:

    • Eliminates insertion anomaly (cannot insert without key), deletion anomaly (loss of data on delete), update anomaly (inconsistency).
    • Reduces redundancy, saves storage, ensures consistency.
    • Enforces integrity, improves query performance after proper design.
    • Example: Student(Course, Instructor) unnormalized duplicates instructor per student; 3NF splits into StudentCourse and CourseInstructor ensures one instructor update suffices.
19. Q5b. Explain two-phase locking protocol & conflict serializability. | Unit 3 – Transaction Management | Concurrency control.20247m

    Unit 3: Storage and Transaction Processing

    Explain two-phase locking protocol & conflict serializability. | Unit 3 – Transaction Management | Concurrency control.

    View this question on its own page →

    CopySearch GoogleAsk AI

    Worked Solution

    Two-Phase Locking Protocol (2PL) & Conflict Serializability:

    2PL: Transaction has growing phase (acquire locks, no unlock) and shrinking phase (release locks, no new locks). Ensures serializability but may deadlock. Strict 2PL holds exclusive locks till commit. Example: T1 locks A, B in growing, then unlocks.

    Conflict Serializability: Schedule is conflict serializable if its conflict graph (precedence graph) is acyclic. Two operations conflict if they are from different transactions, on same item, at least one is write (RW, WR, WW). Graph has edge Ti -> Tj if operation of Ti conflicts before Tj.

    Relationship: 2PL guarantees conflict serializability. Proof via lock points - transactions ordered by lock point (when last lock acquired). If Ti -> Tj in conflict, Ti's lock point before Tj's, no cycle. Rigorous 2PL holds.

    Example: Schedule R1(A) W2(A) is not allowed under 2PL without proper lock ordering, ensures serial schedule T1->T2 or T2->T1.

    Conflict serializable schedule can be transformed to serial via swapping non-conflicting operations.

20. Q6a. Difference between Relational Algebra & Calculus.20247m

    Unit 2: Relational Query Languages

    Difference between Relational Algebra & Calculus.

    View this question on its own page →

    CopySearch GoogleAsk AI

    Worked Solution

    Relational Algebra vs Calculus:

    Relational Algebra: Procedural language - specifies sequence of operations (select, project, join) to compute result. Operations on relations produce relations (closed). Example: pi_name(sigma_age>20(Student)). Implementable, defines how to get data.

    Relational Calculus: Declarative/non-procedural - specifies what to retrieve using logic predicate, not how. Tuple calculus: {t | P(t)} where P is formula using quantifiers. Domain calculus similar. Example: {t.name | Student(t) AND t.age>20}. No order specified, optimizer decides.

    Differences:

    • Algebra procedural, calculus declarative.
    • Algebra has operators; calculus has predicates, quantifiers (exists, forall).
    • Both relationally complete (equivalent expressive power for safe queries), but calculus allows non-safe infinite results unless domain restricted.
    • SQL borrows from both: SELECT-FROM-WHERE is calculus style but execution uses algebra.

    Tuple vs Domain calculus variant distinction included.

21. Q6b. Explain ACID properties.20247m

    Unit 3: Storage and Transaction Processing

    Explain ACID properties.

    View this question on its own page →

    CopySearch GoogleAsk AI

    Worked Solution

    ACID Properties (Transaction):

    • Atomicity: Transaction is all-or-nothing indivisible. Either all operations done or none upon failure, via log/undo. Example: Transfer money debit and credit both or none.

    • Consistency: Transaction transforms database from one consistent state (satisfying integrity constraints, invariants) to another. If constraints violated, abort.

    • Isolation: Concurrent transactions appear isolated, intermediate states hidden, final result as if serial. Via concurrency control (2PL, MVCC). Prevents dirty/read phenomena.

    • Durability: Once committed, results persist despite failures (via write-ahead logging, stable storage, checkpoints). Committed data not lost on crash.

    Together ensure reliable transaction processing despite concurrency and failures. Example: Banking transaction respects ACID: atomic transfer, consistent balance >=0, isolated from others, durable after commit.

22. Budget App•POCKETLY

    Pocketly on Android

    Log daily food, travel, and hostel expenses instantly. Set monthly spending limits and see where your money goes without complex spreadsheets.

    Install Pocketly
23. Q7a. Illustrate disadvantages of file processing systems.20247m

    Unit 1: Database System Architecture

    Illustrate disadvantages of file processing systems.

    View this question on its own page →

    CopySearch GoogleAsk AI

    Worked Solution

    Disadvantages of File Processing Systems:

    • Data Redundancy and Inconsistency: Same data duplicated across files, updates require multiple places, inconsistency.
    • Data Dependence: Programs tied to file format, change in structure requires program change.
    • Difficulty in Accessing Data: Need custom programs for each query, no declarative query language.
    • Data Isolation: Scattered files, different formats hard to integrate.
    • Integrity Problems: Constraints enforced in application code, not centrally, error-prone.
    • Atomicity Problems: No transaction support, partial updates on failure leave inconsistent state.
    • Concurrent Access Anomalies: No locking, simultaneous updates lost update problem.
    • Security Problems: Difficult fine-grained authorization, OS-level only.
    • Recovery Difficult: No log-based recovery, difficult to restore after crash.

    Example: University storing student data in separate files for library, fees, academics leads to all above issues which DBMS solves.

24. Q7b. Explain three levels of data abstraction.20247m

    Unit 1: Database System Architecture

    Explain three levels of data abstraction.

    View this question on its own page →

    CopySearch GoogleAsk AI

    Worked Solution

    Three Levels of Data Abstraction:

    1. Physical Level: Lowest - how data stored physically - files, blocks, indexes, hashing, compression. Hidden from users.

    2. Logical/Conceptual Level: What data stored, relationships, constraints, semantics. DBA defines schema, e.g., tables Student(id, name). Provides logical independence.

    3. View/External Level: User-specific views - subset of logical level, hides irrelevant data, provides security. Example: Faculty view shows student grades, Student view hides.

    Benefits: Data abstraction achieves independence - physical changes without affecting logical, logical changes without affecting views - easing application development, security.

25. Q8a. Find the highest normal form of a relation R(ABCD) with FD set (B→A, A→C, BC→D, AC→BE).20247m

    Unit 2: Relational Query Languages

    Find the highest normal form of a relation R(ABCD) with FD set
    (B→A, A→C, BC→D, AC→BE).

    View this question on its own page →

    CopySearch GoogleAsk AI

    Worked Solution

    Find highest normal form of R(ABCD) with FDs B->A, A->C, BC->D, AC->BE. Note E not in R - likely typo, assume R is ABCDE or FD is AC->B. Proceed with typical interpretation R(ABCD) with B->A, A->C, BC->D. Compute candidate keys:

    Closure: B+ = B -> A -> C, BC -> D, so B+ = ABCD. So B is candidate key (single attribute). Also BC is superkey etc. Only B is CK.

    Check 1NF: assume atomic, so 1NF.
    2NF: No partial dependency because CK is single attribute B, no proper subset, so automatically 2NF.
    3NF: Check each FD: B->A where B is superkey so OK. A->C where A not superkey and C non-prime -> transitive violation, so not 3NF.
    Hence highest is 2NF.

    If considering FD AC->BE with E present and R as ABCDE: CK still B (B+= ABCDE via AC->BE gives B->AC->BE then BC->D), same violation A->C makes not 3NF, highest 2NF. If strict, R(ABCD) typo, answer remains 2NF. Mention anomaly if E included needs BCNF check fails.

    Provide steps for closure, CK, normal form tests.

26. Q8b. A relation R(A,C,D,E,H) is having two functional dependencies sets F={A→C, AC→D, E→AD, E→H} and set G {AC→D, E→AH}, then check F and Gare equivalent or not.20247m

    Unit 2: Relational Query Languages

    A relation R(A,C,D,E,H) is having two functional dependencies sets F={A→C, AC→D, E→AD, E→H} and set G {AC→D, E→AH}, then check F and Gare equivalent or not.

    View this question on its own page →

    CopySearch GoogleAsk AI

    Worked Solution

    Check if FD sets F and G equivalent over R(A,C,D,E,H):

    F = {A->C, AC->D, E->AD, E->H}
    G = {A->C, AC->D, E->AH}

    Two sets equivalent if F+= G+ (each implies the other). Check via closure.

    Compute F+ check G:

    • A->C in F holds (yes)
    • AC->D in F holds (yes)
    • E->A? In F: E+ = E -> AD -> C (via A->C) -> H (E->H), so E+ = ACDEH includes A, and AC->D gives D, so E->A and E->H hold. Also E->AD uses E->AD in F gives A and D. For G's E->AH: E->A and E->H derived above, so G subset of F+.

    Compute G+ check F:

    • A->C in G yes
    • AC->D yes
    • E->AD? Need E->A and E->D: G: E+ = E -> AH -> C (via A->C) -> D (via AC->D) => E+ = ACDEH? H via? Wait H not derived? Actually E->AH gives A and H, A->C gives C, AC->D gives D, so E->AD holds (A and D). So E->AD in G+.
    • E->H in G via E->AH gives H, yes.

    Thus every FD in F is implied by G and vice versa, so F+ = G+ => equivalent.

    Formally F covers G and G covers F via Armstrong axioms. Hence equivalent.

    Provide closure tables to demonstrate.

27. Q9a. What do you mean by concurrency control techniques?20247m

    Unit 3: Storage and Transaction Processing

    What do you mean by concurrency control techniques?

    View this question on its own page →

    CopySearch GoogleAsk AI

    Worked Solution

    Concurrency Control Techniques: Methods to ensure isolation and serializability when multiple transactions execute concurrently.

    Techniques:

    • Lock-Based: 2PL (growing/shrinking), Strict 2PL, Shared (S) and Exclusive (X) locks, prevents dirty read/write. Example: before read acquire S, before write acquire X.

    • Timestamp Ordering: Assign timestamp TS(T), ensure conflict order follows timestamp via W-timestamp/R-timestamp, Thomas write rule for obsolete writes.

    • Validation/Optimistic: Read phase, validation (check conflicts), write phase - no locking, abort if conflict (good for low contention).

    • Multiversion (MVCC): Keep multiple versions for reads without locking, readers read snapshot.

    • Granularity: Multiple granularity locks (IS, IX, SIX) for hierarchy.

    Goal: Prevent anomalies: lost update, dirty read, non-repeatable read, phantom. Provide serializable schedule.

28. Q9b. Describe the importance of data integrity in a database. How can constraints (such as primary keys and foreign keys) help enforce integrity?20247m

    Unit 2: Relational Query Languages

    Describe the importance of data integrity in a database. How can constraints (such as primary keys and foreign keys) help enforce integrity?

    View this question on its own page →

    CopySearch GoogleAsk AI

    Worked Solution

    Importance of Data Integrity and Constraints:

    Data Integrity ensures accuracy, consistency, reliability of data - prevents invalid entries, maintains business rules. Without integrity, database contains garbage, queries wrong, application fails.

    Constraints enforce integrity declaratively:

    • Primary Key (Entity Integrity): Uniquely identifies row, prevents duplicates/NULL. Example: Student rollno PK ensures no duplicate student.

    • Foreign Key (Referential Integrity): Ensures reference exists or NULL, prevents orphan. Example: Enroll(s_id) FK references Student, cannot enroll non-existent student, cascade delete maintains.

    • NOT NULL, UNIQUE, CHECK, DEFAULT, Domain constraints: Enforce valid ranges, e.g., CHECK (age>0), salary >0.

    • Benefits: Centralized enforcement, not reliant on app code, prevents anomalies, automatic validation, easier maintenance. Example without PK, duplicate inserts cause inconsistency.