CC10 DATABASE MANAGEMENT SYSTEM 2022 PYQ NBU

 

1. What are the various types of keys?

1. Primary Key: Uniquely identifies each record in a table.

2. Foreign Key: Establishes a link between tables by referencing the primary key of another table.

3. Candidate Key: A set of attributes that uniquely identify a record. Any candidate key can be a primary key.

4. Super Key: A set of one or more attributes that uniquely identify a record.

5. Composite Key: A key that consists of two or more attributes to uniquely identify a record.

6. Alternate Key: A candidate key that is not chosen as the primary key.

2. What is a view in DBMS?

A view is a virtual table in a database that provides a specific representation of data from one or more tables. It is defined by a query and does not store data physically.

3. What is DBMS?

A Database Management System (DBMS) is software that provides an interface to create, read, update, and delete data in a database. It ensures data integrity, security, and consistency.

4. What is granularity of a data item?

Granularity refers to the size or level of detail of data items in a database. It can range from coarse granularity (large data items like entire records) to fine granularity (small data items like individual fields).

5. Why do we need concurrency control?

Concurrency control is needed to ensure that database transactions are executed in a safe manner and to prevent conflicts and inconsistencies when multiple transactions occur simultaneously.

6. What is serializability of schedule?

Serializability of a schedule is a concept in concurrency control that ensures a sequence of transactions is equivalent to some serial (one-at-a-time) execution of the transactions, preserving data consistency.

7. What is relational algebra?

Relational algebra is a procedural query language used to query and manipulate data in a relational database. It uses a set of operations (like select, project, union, and join) to produce new relations from existing ones.

8. What is a transaction in DBMS?

A transaction in DBMS is a sequence of one or more SQL operations (such as insert, update, delete) executed as a single unit of work. Transactions follow ACID properties (Atomicity, Consistency, Isolation, Durability) to ensure reliable processing.

10. Discuss Validation Concurrency Control Technique.

- Definition: Also known as optimistic concurrency control, it assumes that conflicts are rare and allows transactions to execute without restrictions until the validation phase.

- Phases:

  1. Read Phase: Transactions read data and perform operations without making permanent changes.

  2. Validation Phase: Before committing, a transaction is validated to ensure it has not conflicted with other concurrent transactions.

  3. Write Phase: If validated, changes are made permanent; otherwise, the transaction is rolled back.

- Advantages:

  - Reduces overhead from locking mechanisms.

  - Suitable for applications with low contention.

- Disadvantages:

  - Higher risk of rollback if conflicts are frequent.

  - Validation can be complex in high-concurrency environments.

- Example:

  - Two transactions, T1 and T2, read and update the same data item. If T1 completes and T2 tries to commit, T2 will be validated to ensure it didn’t interfere with T1’s changes.

11. Explain Why We Need to Recover a Database with Example.

- Purpose: To restore the database to a consistent state after a failure (e.g., system crash, power failure, software error).

- Types of Failures:

  1. Transaction Failure: A specific transaction fails (e.g., logical errors).

  2. System Failure: System crash or power outage.

  3. Media Failure: Disk corruption or hardware failure.

- Recovery Techniques:

  - Rollback: Undo uncommitted transactions using log records.

  - Roll-forward: Redo committed transactions from log records.

- Example:

  - Consider a bank transaction transferring money from account A to B. If a system crash occurs after debiting A but before crediting B, recovery mechanisms ensure either the entire transaction is rolled back (A’s amount is restored) or completed (B is credited).

12. How Do We Deal with Constraint Violation?

1. Check Constraints:

   - Ensure that data meets predefined conditions.

   - Example: Age must be > 0. Violation leads to an error message.

   CREATE TABLE Users (

       Age INT CHECK (Age > 0)

   ); (code-box)

2. Primary Key Violations:

   - Ensure unique and non-null values.

   - Solution: Reject duplicate entries.

   CREATE TABLE Students (

       StudentID INT PRIMARY KEY,

       Name VARCHAR(100)

   ); (code-box)

3. Foreign Key Violations:

   - Ensure referential integrity.

   - Solution: Reject operation, or use CASCADE/SET NULL/SET DEFAULT.

   CREATE TABLE Orders (

       OrderID INT PRIMARY KEY,

       CustomerID INT,

       FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)

       ON DELETE CASCADE

   ); (code-box)

4. Unique Constraints:

   - Ensure all values are distinct.

   - Solution: Reject duplicates.

 

   CREATE TABLE Employees (

       EmployeeID INT UNIQUE,

       Name VARCHAR(100)

   ); (code-box)

5. Not Null Constraints:

   - Ensure columns cannot have null values.

   - Solution: Reject null entries.

   CREATE TABLE Products (

       ProductID INT PRIMARY KEY,

       ProductName VARCHAR(100) NOT NULL

   ); (code-box)

13. Define Normalization, Structural Constraints, Interfaces, and Scheduling.

Normalization:

- Definition: Process of organizing data in a database to reduce redundancy and improve data integrity.

- Levels: 1NF, 2NF, 3NF, BCNF, etc.

- Example: Splitting a table into two to eliminate repeating groups.

Structural Constraints:

- Definition: Rules that enforce the structure and integrity of the data.

- Types: 

  - Domain Constraints: Data type restrictions.

  - Referential Integrity: Foreign key rules.

  - Entity Integrity: Primary key rules.

Interfaces:

- Definition: Points of interaction between different systems, software, or components.

- Types: 

  - User Interface (UI): How users interact with the database (forms, queries).

  - Application Programming Interface (API): How applications interact with the database.

Scheduling:

- Definition: Method of determining the order in which transactions are executed in a database to ensure consistency and isolation.

- Types:

  - Serial Scheduling: Transactions are executed one after another.

  - Concurrent Scheduling: Transactions are executed concurrently, using techniques like locking, timestamp ordering.

14. Consider the following tables:

Part(p_id, p_name, p_cost)

Customer(c_id, p_id, c_name)

Supplier(s_id, s_name, p_id, p_city)

Shop(sh_id, p_id, c_id, s_id, sh_city) (code-box)

(a) Insert into table Part a new column named part_details.

ALTER TABLE Part ADD COLUMN part_details VARCHAR(255); (code-box)

(b) List the name of customers who are from the same city.

SELECT c1.c_name 

FROM Customer c1 

JOIN Shop sh1 ON c1.c_id = sh1.c_id 

JOIN Customer c2 ON c1.c_id != c2.c_id AND sh1.sh_city = sh2.sh_city; (code-box)

(c) List the customers who have been supplied with minimum number of parts.

SELECT c.c_name 

FROM Customer c 

JOIN Shop sh ON c.c_id = sh.c_id 

GROUP BY c.c_id 

HAVING COUNT(DISTINCT sh.p_id) = (

    SELECT MIN(part_count) 

    FROM (SELECT COUNT(DISTINCT p_id) AS part_count FROM Shop GROUP BY c_id) AS part_counts

); (code-box)

15. What is Normalization? Why do we need to Normalize Our Database? Explain Various Normal Forms by Taking Proper Examples.

Normalization:

- Definition: Process of organizing data to minimize redundancy and improve data integrity.

- Need:

  - Eliminates redundancy.

  - Ensures data consistency.

  - Simplifies data maintenance.

Normal Forms:

1. First Normal Form (1NF):

   - Definition: No repeating groups or arrays.

   - Example:

     Before 1NF:

     Orders(OrderID, ProductID1, ProductID2)

     

     After 1NF:

     Orders(OrderID, ProductID) (code-box)

2. Second Normal Form (2NF):

   - Definition: 1NF + no partial dependency (every non-key attribute is fully functionally dependent on the primary key).

   - Example:

   

     Before 2NF:

     Orders(OrderID, ProductID, ProductName)

     

     After 2NF:

     Orders(OrderID, ProductID)

     Products(ProductID, ProductName) (code-box)

3. Third Normal Form (3NF):

   - Definition: 2NF + no transitive dependency (non-key attributes should not depend on other non-key attributes).

   - Example:

     Before 3NF:

     Orders(OrderID, ProductID, SupplierName)

     

     After 3NF:

     Orders(OrderID, ProductID)

     Suppliers(SupplierID, SupplierName) (code-box)

4. Boyce-Codd Normal Form (BCNF):

   - Definition: 3NF + every determinant is a candidate key.

   - Example:

    

     Before BCNF:

     Enrollment(StudentID, CourseID, Instructor)

     

     After BCNF:

     Enrollment(StudentID, CourseID)

     Courses(CourseID, Instructor) (code-box)

16. Discuss the Importance of the Following with Respect to DBMS:

(a) Data Independence

- Definition: Ability to change the schema at one level without affecting the schema at the next higher level.

- Importance:

  - Logical Data Independence: Changing logical schema without changing the external schema.

  - Physical Data Independence: Changing physical schema without changing logical schema.

  - Example: Adding a new index to improve query performance without changing application logic.

(b) Classification

- Definition: Categorizing data to facilitate data management and access.

- Importance:

  - Enhances data retrieval.

  - Simplifies data administration.

  - Example: Classifying customers as 'regular', 'premium', and 'VIP' for personalized services.

(c) Granularity

- Definition: Level of detail or fineness of data.

- Importance:

  - Affects performance and storage.

  - Determines data access control and security.

  - Example: Granularity of a transaction log (individual records vs. batch).

17. Explain Concurrency Control with Locking Methods.

Concurrency Control:

- Definition: Mechanism to ensure multiple transactions can execute concurrently without conflicting.

- Importance:

  - Maintains data integrity.

  - Ensures isolation and consistency.

Locking Methods:

1. Binary Locks:

   - Description: Each data item can be either locked or unlocked.

   - Example: If a transaction locks a data item, no other transaction can access it until it is unlocked.

2. Shared (S) and Exclusive (X) Locks:

   - Shared Lock (S): Allows multiple transactions to read a data item but not modify it.

   - Exclusive Lock (X): Allows one transaction to read and modify a data item.

   - Example: Transaction A can place a shared lock to read data, while transaction B must wait to place an exclusive lock to write data.

3. Two-Phase Locking (2PL):

   - Growing Phase: Transactions acquire locks and do not release any.

   - Shrinking Phase: Transactions release locks and do not acquire any new locks.

   - Example: Ensures serializability by preventing conflicting operations.

4. Deadlock Handlin:

   - Detection and Resolution: System detects deadlock and aborts one of the transactions.

   - Prevention: Ensures transactions do not enter a deadlock state by acquiring all locks upfront or ordering resource acquisition.

   - Example: Timeout mechanism to detect and resolve deadlocks.