Question 1

What is 1NF (First Normal Form) and why is it theoretically important?

Accepted Answer

First Normal Form (1NF) is the foundational rule of database normalization that ensures data atomicity and sets the stage for a true relational model. To satisfy 1NF, a relation must meet several strict conditions:

1. Atomic Attributes: Every attribute (column) must hold indivisible, single values. Arrays, nested records, or comma-separated lists are strictly prohibited.
2. No Repeating Groups: A table cannot have multiple columns representing the same concept (e.g., Phone1, Phone2).
3. Unique Domain: Every column must contain data of the same type/domain.

From a theoretical standpoint, 1NF guarantees that every intersection of a tuple (row) and an attribute (column) contains exactly one scalar value. Without 1NF, basic Relational Algebra operations and SQL aggregates cannot function predictably.

Example:
-- Violates 1NF (repeating group in subjects)
-- INSERT INTO Student VALUES (1, 'John', 'Math, Physics');

-- Conforms to 1NF (atomic values)
INSERT INTO Student (id, name, subject) VALUES (1, 'John', 'Math');
INSERT INTO Student (id, name, subject) VALUES (1, 'John', 'Physics');

Question 2

Explain Partial Dependency and how Second Normal Form (2NF) addresses it.

Accepted Answer

A Partial Dependency is a specific normalization anomaly that only occurs in tables with a Composite Primary Key (a primary key made of two or more columns). It exists when a non-prime attribute (an attribute not part of any candidate key) is functionally dependent on only a portion of the composite primary key, rather than the entire key.

Second Normal Form (2NF) explicitly eliminates this anomaly. For a table to be in 2NF, it must first be in 1NF, and all partial dependencies must be removed. This is achieved by decomposing the original table into smaller tables: one table retains the full composite key, while the partially dependent attributes are moved to a new table where their determinant becomes the sole primary key. This prevents massive data duplication when updating attributes.

Example:
-- Problem: PK is (StudentID, CourseID). 'CourseName' only depends on 'CourseID' (Partial Dependency).

-- 2NF Decomposition Solution:
CREATE TABLE Courses (
  CourseID INT PRIMARY KEY,
  CourseName VARCHAR(100)
);
CREATE TABLE Enrollments (
  StudentID INT,
  CourseID INT,
  PRIMARY KEY (StudentID, CourseID)
);

Question 3

What is Transitive Dependency and how does 3NF resolve it?

Accepted Answer

Transitive Dependency occurs when a non-prime attribute indirectly depends on the primary key through another non-prime attribute. Mathematically, if A is the primary key, B is a non-prime attribute, and C is a non-prime attribute: If A -> B and B -> C, then A -> C transitively.

Third Normal Form (3NF) is designed to eliminate these dependencies. A relation is in 3NF if it is in 2NF and every non-prime attribute is non-transitively dependent on every candidate key. Codd's famous definition of 3NF dictates that every non-key column must depend 'on the key, the whole key, and nothing but the key.' Failing to achieve 3NF results in Update Anomalies, where modifying the 'B' attribute requires scanning and updating multiple rows to maintain the integrity of 'C'.

Example:
-- Violates 3NF: Employee -> DepartmentID -> DepartmentLocation
-- DepartmentLocation transitively depends on Employee.

-- 3NF Solution:
CREATE TABLE Departments (
  DeptID INT PRIMARY KEY,
  DeptLocation VARCHAR(50)
);
CREATE TABLE Employees (
  EmpID INT PRIMARY KEY,
  DeptID INT REFERENCES Departments(DeptID)
);

Question 4

Define Boyce-Codd Normal Form (BCNF). How does it differ from 3NF?

Accepted Answer

Boyce-Codd Normal Form (BCNF) is a stricter refinement of 3NF designed to handle edge cases involving overlapping composite candidate keys.

The defining rule of BCNF states: For every non-trivial functional dependency X -> Y, X must be a superkey.

The critical difference between 3NF and BCNF lies in prime attributes. 3NF allows a prime attribute (part of a candidate key) to be functionally dependent on a non-prime attribute. BCNF strictly forbids this. If a relation has only one candidate key, 3NF and BCNF are equivalent. However, if a table has multiple overlapping candidate keys, it might be in 3NF but violate BCNF.

Example:
-- Scenario: (Student, Course) -> Instructor, and Instructor -> Course
-- 3NF allows this because 'Course' is a prime attribute. 
-- BCNF rejects it because 'Instructor' determines 'Course' but 'Instructor' is NOT a superkey.

-- BCNF Decomposition:
CREATE TABLE InstructorCourse (Instructor VARCHAR(50) PRIMARY KEY, Course VARCHAR(50));

Question 5

Describe the ACID properties of a database transaction in detail.

Accepted Answer

ACID is an acronym representing the four fundamental properties that guarantee reliable transaction processing in a database engine:

1. Atomicity ('All or Nothing'): A transaction is treated as a single, indivisible logical unit of work. If any operation within the transaction fails, the entire transaction is rolled back, leaving the database state unchanged.
2. Consistency: A transaction must transform the database from one valid state to another valid state, strictly enforcing all integrity constraints, cascades, and triggers.
3. Isolation: Concurrent execution of transactions results in a system state that would be equivalent to transactions executing serially (one after another). Prevents dirty reads and lost updates.
4. Durability: Once a transaction is committed, its effects are permanently recorded in non-volatile storage (via Write-Ahead Logging) and will survive subsequent power losses or system crashes.

Example:
BEGIN TRANSACTION;
  UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
  -- If power fails here, Atomicity and Durability ensure the $100 is not permanently lost.
  UPDATE Accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Question 6

What is a Deadlock? How does a DBMS handle it?

Accepted Answer

A Deadlock is a theoretical and practical concurrency state where two or more transactions are waiting indefinitely for one another to release resource locks, forming a circular dependency.

For example, Transaction A holds an exclusive lock on Record X and requests Record Y, while Transaction B holds an exclusive lock on Record Y and requests Record X. Neither can proceed.

DBMS Handling Mechanisms:
1. Deadlock Prevention: Using timestamps to order transactions (e.g., Wait-Die or Wound-Wait protocols) to ensure a cycle can never form.
2. Deadlock Detection & Resolution: The database periodically builds a 'Wait-For Graph'. If a cycle is detected, the DBMS automatically selects a 'Victim Transaction' (usually the one that has done the least amount of work) and violently rolls it back, allowing the other transactions to proceed.

Example:
-- Transaction A:
BEGIN; UPDATE T1 SET val = 1; 
-- (Waits for T2) UPDATE T2 SET val = 1;

-- Transaction B:
BEGIN; UPDATE T2 SET val = 2; 
-- (Waits for T1) UPDATE T1 SET val = 2;
-- DBMS intervenes and kills Transaction B.

Question 7

Explain the Two-Phase Locking (2PL) Protocol and its variants.

Accepted Answer

The Two-Phase Locking (2PL) protocol is a concurrency control mechanism that guarantees serializability by strictly dividing a transaction's execution into two distinct phases:

1. Growing Phase (Phase 1): The transaction can acquire new locks (Shared or Exclusive) on data items but is strictly forbidden from releasing any locks.
2. Shrinking Phase (Phase 2): The transaction can release its held locks but is strictly forbidden from acquiring any new locks.

Variants:
* Strict 2PL: A transaction must hold all its exclusive (write) locks until the transaction commits or aborts. This prevents cascading rollbacks.
* Rigorous 2PL: A transaction must hold ALL locks (both read and write) until commit/abort.
While 2PL guarantees serializability, it does NOT prevent deadlocks.

Example:
-- Growing Phase
LOCK TABLE A IN EXCLUSIVE MODE;
LOCK TABLE B IN EXCLUSIVE MODE;

-- Execute operations
UPDATE A SET val = 1;
UPDATE B SET val = 2;

-- Shrinking Phase (Usually implicit upon COMMIT)
COMMIT;

Question 8

Describe the Dirty Read anomaly and the Isolation Level required to prevent it.

Accepted Answer

A Dirty Read is a severe concurrency anomaly that occurs when Transaction A is allowed to read data that has been modified by Transaction B, but Transaction B has not yet committed.

If Transaction B subsequently encounters an error and rolls back its changes, Transaction A is left holding 'dirty' data that theoretically never existed in the database. If Transaction A makes business decisions based on this ghost data, database consistency is completely compromised.

Prevention: Dirty reads are prevented by setting the database transaction isolation level to READ COMMITTED or higher. In READ COMMITTED, a transaction will only see data that has been fully committed prior to the query's execution.

Example:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
-- This SELECT will block/wait if another transaction is currently writing to id=1
SELECT balance FROM Accounts WHERE id = 1;
COMMIT;

Question 9

Differentiate between a Non-Repeatable Read and a Phantom Read.

Accepted Answer

Both anomalies occur when a transaction reads data twice and gets different results, but they differ in mechanism:

1. Non-Repeatable Read: Occurs when Transaction A reads a specific row, Transaction B UPDATES or DELETES that exact row and commits, and then Transaction A reads the same row again to find the data has changed. Prevented by REPEATABLE READ isolation level (which holds shared locks on read rows).
2. Phantom Read: Occurs when Transaction A executes a range query (e.g., WHERE age > 20), Transaction B INSERTS a new row that satisfies this condition and commits, and Transaction A runs the same range query again, seeing a 'phantom' row appear. Prevented only by the SERIALIZABLE isolation level (which places range locks or predicate locks).

Example:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT COUNT(*) FROM Orders WHERE status = 'Pending';
-- SERIALIZABLE prevents any other transaction from INSERTING new 'Pending' orders.
COMMIT;

Question 10

Differentiate critically between TRUNCATE, DELETE, and DROP commands.

Accepted Answer

These three commands manipulate data removal but operate at fundamentally different levels of the database engine:

1. DELETE (DML - Data Manipulation Language): Removes rows one by one. It records each row deletion in the transaction log, meaning it can be rolled back. It fires triggers and allows WHERE clauses. It is slow for large tables.
2. TRUNCATE (DDL - Data Definition Language): Removes all rows by deallocating the raw data pages on the disk. It logs the page deallocations, not individual rows, making it incredibly fast. It cannot use WHERE clauses, resets auto-increment sequences, and cannot trigger row-level DELETE triggers.
3. DROP (DDL): Utterly destroys the entire table structure, indexes, constraints, and data from the relational schema. The table ceases to exist.

Example:
-- DML (Logged, slow, specific)
DELETE FROM Users WHERE last_login < '2020-01-01';

-- DDL (Unlogged rows, instantaneous, total flush)
TRUNCATE TABLE Cache_Logs;

-- DDL (Destructive structural removal)
DROP TABLE Old_System_Users;

Question 11

What is the architectural difference between a Clustered and Non-Clustered Index?

Accepted Answer

Indexing is crucial for O(log N) retrieval speeds, but clustered and non-clustered indexes operate differently at the hardware storage level:

1. Clustered Index: Dictates the actual physical sorting order of the data rows on the hard disk. Because data can only be physically sorted in one way, a table can possess ONLY ONE clustered index (usually implicitly created on the Primary Key). The leaf nodes of the B-Tree ARE the actual data pages.
2. Non-Clustered Index: A separate logical structure from the data rows. It creates a sorted B-Tree of the indexed columns, where the leaf nodes contain 'pointers' (Row IDs or Clustered Key references) back to the actual data pages. A table can have multiple non-clustered indexes. They are slightly slower than clustered indexes due to the extra pointer lookup step.

Example:
-- Clustered index automatically dictates physical disk layout
CREATE TABLE Users (id INT PRIMARY KEY, email VARCHAR(100));

-- Non-Clustered Index builds a separate lookup tree with pointers to the disk
CREATE INDEX idx_user_email ON Users(email);

Question 12

Explain Total Participation vs Partial Participation in Entity-Relationship (ER) models.

Accepted Answer

Participation constraints dictate whether the existence of an entity depends on its relation to another entity via a relationship set.

1. Total Participation (Existence Dependency): Indicated by a double line in an ER diagram. It signifies that every single entity in the entity set MUST participate in at least one relationship instance. For example, if a business rule states 'Every Employee must belong to a Department', the Employee entity has total participation in the 'Belongs-To' relationship.
2. Partial Participation: Indicated by a single line. It signifies that only some entities in the set might participate in the relationship. For example, 'An Employee may or may not manage a Department'. Therefore, Employee has partial participation in the 'Manages' relationship.

In SQL, total participation is usually enforced using NOT NULL foreign keys.

Example:
-- Total Participation (Enforced via NOT NULL)
CREATE TABLE Employee (
  id INT PRIMARY KEY,
  dept_id INT NOT NULL REFERENCES Department(id)
);

-- Partial Participation (Enforced via NULLable foreign key)
CREATE TABLE Employee (
  id INT PRIMARY KEY,
  managed_dept_id INT NULL REFERENCES Department(id)
);

Question 13

What is a Weak Entity? How is it represented and uniquely identified?

Accepted Answer

A Weak Entity is an entity type that does not possess sufficient attributes to form a primary key of its own. It is existentially dependent on a 'Strong Entity' (also called an Identifying Owner).

Characteristics:
1. It must be related to the identifying strong entity via an 'Identifying Relationship' (represented by a double diamond in ER diagrams).
2. It has total participation in the identifying relationship.
3. It possesses a 'Partial Key' or 'Discriminator' (represented by a dashed underline), which uniquely identifies weak entities related to the SAME strong entity, but not globally.
4. The global Primary Key of a weak entity is always a composite key formed by combining the Primary Key of the strong entity with its own Partial Key.

Example:
-- Strong Entity
CREATE TABLE Building (b_id INT PRIMARY KEY);

-- Weak Entity (Room cannot exist without a Building)
-- Room 101 can exist in Building A and Building B. 
CREATE TABLE Room (
  b_id INT,
  room_no INT,
  PRIMARY KEY (b_id, room_no), -- Composite Primary Key
  FOREIGN KEY (b_id) REFERENCES Building(b_id) ON DELETE CASCADE
);

Question 14

Critically analyze the Relational Algebra 'Selection' (σ) and 'Projection' (π) operators.

Accepted Answer

Selection and Projection are the two fundamental unary operators in relational algebra, forming the basis of data retrieval.

1. Selection (σ): Acts horizontally. It filters rows (tuples) from a relation that satisfy a specific propositional logic formula (predicate). The degree (number of columns) of the output is identical to the input relation, but the cardinality (number of rows) is reduced. It maps directly to the SQL WHERE clause.
2. Projection (π): Acts vertically. It extracts specified columns (attributes) from a relation and discards the rest. A critical theoretical aspect of Projection is that it mathematically guarantees the removal of duplicate tuples from the output relation (since relational algebra operates on mathematical sets, not bags). It maps to the SQL SELECT clause, specifically SELECT DISTINCT.

Example:
-- Relational Algebra: π_name,salary(σ_salary>50000(Employee))

-- SQL Equivalent:
SELECT DISTINCT name, salary 
FROM Employee 
WHERE salary > 50000;

Question 15

What is the CAP Theorem in the context of Distributed Database Systems?

Accepted Answer

Formulated by Eric Brewer, the CAP theorem states that any distributed data store can uniquely provide only two of the following three guarantees simultaneously:

1. Consistency (C): Every read operation receives the most recent write or an error. All nodes see the exact same data at the exact same time.
2. Availability (A): Every request (read or write) receives a non-error response, without guaranteeing it contains the most recent write.
3. Partition Tolerance (P): The system continues to operate and process requests despite arbitrary message loss or network failures between nodes.

Because network partitions (P) are an unavoidable reality in distributed systems, architects must choose between prioritizing Consistency (CP systems, like MongoDB) or prioritizing Availability (AP systems, like Cassandra using Eventual Consistency).

Example:
-- RDBMS (like PostgreSQL on a single server) provides CA (Consistency + Availability)
-- NoSQL stores distributed across datacenters usually choose AP or CP.

Question 16

Explain the difference between Heuristic and Cost-Based Query Optimization.

Accepted Answer

When a SQL query is submitted, the DBMS Query Optimizer must determine the most efficient execution plan (the physical sequence of algorithms used to fetch the data). There are two primary paradigms:

1. Heuristic (Rule-Based) Optimization: The optimizer applies strict, hard-coded algebraic rules to transform the query tree. For example, a fundamental rule is to push Selection (σ) operations down the query tree as early as possible. Filtering rows early drastically reduces the size of intermediate tables passed into expensive JOIN operations.
2. Cost-Based Optimization (CBO): The optimizer generates multiple permutation plans and estimates the 'cost' of each using database statistics (like table cardinality, index density, and data distribution histograms). It calculates CPU cycles, Memory footprint, and Disk I/O to select the plan with the lowest overall execution cost.

Example:
-- Developers use EXPLAIN to see the Cost-Based Optimizer's final decision
EXPLAIN ANALYZE 
SELECT * FROM Employees e JOIN Departments d ON e.dept_id = d.id 
WHERE e.salary > 100000;

Question 17

Describe Window Functions in SQL. How do they differ theoretically from GROUP BY?

Accepted Answer

Window functions are advanced analytical functions that perform calculations across a defined subset of table rows (the 'window') that are structurally related to the current row being processed.

Theoretical Difference from GROUP BY:
When you use a traditional aggregate function with GROUP BY, the database collapses multiple physical rows into a single aggregated summary row. You lose access to the individual row-level data.
A Window Function, however, computes the aggregate value without collapsing the rows. It returns the calculated value appended to EVERY row in the original dataset. This allows developers to mix granular row-level data with aggregated metrics (e.g., displaying an employee's salary directly next to their department's average salary).

Example:
-- Window Function: Returns every employee, computing their department's average beside them.
SELECT name, salary, dept_id,
       AVG(salary) OVER(PARTITION BY dept_id) as dept_avg
FROM Employees;

Question 18

Differentiate between OLTP and OLAP architectures.

Accepted Answer

OLTP and OLAP represent the two primary architectural patterns for relational database deployment, tailored for entirely different business needs:

1. OLTP (Online Transaction Processing): Designed to manage day-to-day business operations. Characterized by a massive volume of short, rapid DML transactions (INSERT, UPDATE). The schema is highly normalized (3NF) to guarantee data integrity and speed up write operations. Example: E-commerce checkout systems.
2. OLAP (Online Analytical Processing): Designed for historical data analysis, reporting, and Business Intelligence. Characterized by low volumes of incredibly complex, read-only SELECT queries scanning millions of rows. The schema is purposefully denormalized (using Star or Snowflake schemas) to minimize JOIN overhead and speed up aggregation. Example: A data warehouse analyzing 5 years of sales trends.

Example:
-- OLTP Query: Fast index lookup on a highly normalized table
SELECT balance FROM Accounts WHERE id = 1005;

-- OLAP Query: Massive multi-dimensional aggregation on denormalized fact tables
SELECT region, SUM(sales_amount) FROM FactSales GROUP BY region;

Question 19

Define the Division Operation (÷) in Relational Algebra. When is it used?

Accepted Answer

The Division operation (÷) is a complex derived operation in relational algebra specifically designed to answer queries involving universal quantification, commonly phrased with the words 'ALL' or 'EVERY'.

Mechanism: Given two relations, A and B. Relation A has attributes (X, Y) and Relation B has attribute (Y). The division operation A ÷ B results in a relation containing only the X attributes from A that are associated with EVERY Y attribute present in B.
For instance, if Relation A maps Students to Courses they completed, and Relation B is a list of all Core Courses, the operation (CompletedCourses ÷ CoreCourses) will yield only the names of students who have successfully completed ALL the core courses.

Example:
-- Relational Algebra: CompletedCourses ÷ CoreCourses

-- SQL Equivalent (Using double NOT EXISTS, one of the hardest SQL patterns):
SELECT S.student_name FROM Students S
WHERE NOT EXISTS (
  SELECT course_id FROM CoreCourses
  EXCEPT
  SELECT course_id FROM CompletedCourses C WHERE C.student_id = S.id
);

Question 20

What are Insertion, Deletion, and Update Anomalies? Give examples.

Accepted Answer

Anomalies are critical data inconsistencies that occur when a database schema is improperly normalized (usually stuck in 1NF or 2NF), forcing multiple unrelated concepts into a single table.

1. Insertion Anomaly: The inability to insert a fact about one entity until we have a fact about another entity. Example: In a table Employees(EmpID, Name, DeptName, DeptManager), you cannot record a new Department and its Manager until you physically hire an Employee to put in the EmpID column.
2. Deletion Anomaly: The unintended loss of data when deleting a row. Example: If the only employee in the 'Marketing' department is fired and their row is deleted, the database completely loses the record of who the Marketing DeptManager is.
3. Update Anomaly: Modifying a single logical fact requires updating multiple duplicate rows. If the DeptManager changes, the system must scan and update thousands of employee rows. If one row fails to update, the database falls into an inconsistent state.

Example:
-- To prevent all three anomalies, apply 3NF Decomposition:
CREATE TABLE Departments (DeptName VARCHAR(50) PRIMARY KEY, DeptManager VARCHAR(50));
CREATE TABLE Employees (EmpID INT PRIMARY KEY, Name VARCHAR(50), DeptName VARCHAR(50));

Question 21

[10 Marks] What do you mean by Entity Type and Entity Set? Construct an ER diagram for an airline ticket booking system.

Accepted Answer

Entity Type vs Entity Set:
* Entity Type: Defines the schema or 'blueprint' for a collection of entities that share the same attributes (e.g., the concept of a 'Passenger' with attributes like Name, PassportNo).
* Entity Set: The actual, physical collection of all instances of that entity type currently in the database (e.g., all 50,000 specific passengers stored in the table).

Airline Booking ER Diagram Requirements:
1. Entities: PASSENGER, TICKET, FLIGHT, AIRPORT, DISCOUNT_CODE.
2. Relationships:
   - BOOKED_BY: Relates PASSENGER to TICKET (1:N).
   - ASSIGNED_TO: Relates TICKET to FLIGHT (N:1).
   - DEPARTS_FROM / ARRIVES_AT: Relates FLIGHT to AIRPORT (N:1).
   - APPLIES_TO: Relates DISCOUNT_CODE to TICKET (1:N).
3. Attributes: TICKET will have a composite key or a unique TicketNo, along with Fare and SeatNo. FLIGHT handles traffic management (Capacity, DepartureTime).

Example:
-- Standard Relational Mapping for the ER Diagram:
CREATE TABLE Passenger (PID INT PRIMARY KEY, Name VARCHAR(100));
CREATE TABLE Flight (FlightID INT PRIMARY KEY, Source VARCHAR(50), Dest VARCHAR(50));
CREATE TABLE Ticket (TicketNo INT PRIMARY KEY, PID INT REFERENCES Passenger(PID), FlightID INT REFERENCES Flight(FlightID), Fare DECIMAL);
CREATE TABLE Discount (Code VARCHAR(20) PRIMARY KEY, TicketNo INT REFERENCES Ticket(TicketNo));

Question 22

[10 Marks] Library Schema: Write SQL and Relational Algebra for (1) Names of books issued to 'Hari', (2) Books published by 'ABC', (3) Count books by author 'Ram'.

Accepted Answer

Assuming schema: USER(uid, name), USES(uid, bid, issue_date), BOOK(bid, title, author, pid), PUBLISHER(pid, pname).

1. Names of books issued to 'Hari':
* Algebra: π_title (σ_name='Hari'(USER) ⨝ USES ⨝ BOOK)
* SQL: SELECT B.title FROM BOOK B JOIN USES U ON B.bid = U.bid JOIN USER Us ON U.uid = Us.uid WHERE Us.name = 'Hari';

2. Books published by 'ABC publication':
* Algebra: π_title (σ_pname='ABC publication'(PUBLISHER) ⨝ BOOK)
* SQL: SELECT B.title FROM BOOK B JOIN PUBLISHER P ON B.pid = P.pid WHERE P.pname = 'ABC publication';

3. Count books by author 'Ram':
* Algebra: G_COUNT(bid) (σ_author='Ram'(BOOK))  // Using grouping operator G
* SQL: SELECT COUNT(bid) FROM BOOK WHERE author = 'Ram';

Example:
-- Q1: Books issued to Hari
SELECT b.title 
FROM BOOK b 
NATURAL JOIN USES u 
NATURAL JOIN USER user 
WHERE user.name = 'Hari';

Question 23

[10 Marks] Desirable properties of transactions? Explain conflict-serializability with example.

Accepted Answer

Desirable Properties (ACID):
1. Atomicity: The transaction is indivisible; all operations succeed, or none do.
2. Consistency: Database constraints are never violated.
3. Isolation: Concurrent transactions do not interfere (act as if running serially).
4. Durability: Committed changes survive hardware crashes.

Conflict-Serializability:
A schedule of concurrent transactions is 'conflict-serializable' if it can be mathematically transformed into a serial schedule by swapping non-conflicting, adjacent operations. 
Two operations 'conflict' if they belong to different transactions, access the exact same data item, and at least one of them is a WRITE operation (Read-Write, Write-Read, or Write-Write conflict).
If you draw a Precedence Graph mapping these conflicts, the schedule is conflict-serializable ONLY if the graph has absolutely no cycles.

Example:
-- Conflicting Operations Example:
-- T1: Read(X)
-- T2: Write(X)
-- These conflict! T2's write affects what T1 reads if their order is swapped. You cannot safely swap them without changing the final output.

Question 24

[5 Marks] What is Referential Integrity and why is it needed?

Accepted Answer

Referential Integrity is a critical database constraint ensuring that relationships between tables remain logically consistent.

It dictates that any Foreign Key field in a 'child' table must either contain a NULL value or strictly match an existing Primary Key value in the 'parent' table.

Why it is needed:
1. Prevents Orphaned Records: It stops users from adding a child record pointing to a non-existent parent (e.g., placing an Order for a Customer ID that doesn't exist).
2. Safe Deletions: It prevents users from deleting a parent record if child records still rely on it, avoiding catastrophic cascade failures in application logic.

Example:
CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  -- The constraint ensuring referential integrity:
  CONSTRAINT fk_customer 
    FOREIGN KEY (CustomerID) 
    REFERENCES Customers(CustomerID) 
    ON DELETE RESTRICT
);

Question 25

[5 Marks] Explain the ANSI/SPARC Three-Schema Architecture.

Accepted Answer

The ANSI/SPARC architecture guarantees 'Data Independence' by isolating user applications from physical storage mechanisms. It consists of three levels:

1. Internal Schema (Physical Level): Describes the exact physical storage structure of the database (B-trees, hash tables, file paths, byte allocations).
2. Conceptual Schema (Logical Level): The 'DBA View'. Describes the structure of the whole database for a community of users. It defines entities, datatypes, relationships, and constraints, completely hiding physical storage details.
3. External Schema (View Level): Describes the part of the database that a specific user group is interested in, hiding the rest of the conceptual schema (using SQL Views).

Example:
-- 1. Internal: CREATE CLUSTERED INDEX...
-- 2. Conceptual: CREATE TABLE Employees (EmpID INT, Salary DECIMAL)...
-- 3. External: CREATE VIEW PublicEmployees AS SELECT EmpID FROM Employees; (Hides Salary)

Question 26

[5 Marks] What are the informal design guidelines for relational schemas?

Accepted Answer

Before applying rigorous normal forms (1NF, 2NF, 3NF), relational schemas should follow four informal design guidelines:

1. Clear Semantics: Each tuple (row) should represent one distinct entity or relationship instance. Do not mix entity types in one table.
2. Redundancy Reduction: Design schemas to minimize redundant data storage, which directly prevents Insertion, Deletion, and Update anomalies.
3. Null Value Minimization: Avoid placing attributes in a base relation whose values may frequently be NULL. (Move them to a separate linked table).
4. Prevent Spurious Tuples: Design relations so that when they are joined naturally, no 'spurious' (fake/invalid) tuples are generated. This guarantees a 'lossless join'.

Example:
-- Violates Guideline 1 & 2 (Mixed semantics & redundancy):
-- TABLE: Emp_Dept (EmpID, EmpName, DeptID, DeptName, DeptManager)

-- Follows Guidelines (Clear separation):
-- TABLE: Employee (EmpID, EmpName, DeptID)
-- TABLE: Department (DeptID, DeptName, DeptManager)

Question 27

[5 Marks] Classify DBMS based on the Data Model.

Accepted Answer

Database Management Systems are fundamentally classified by how they mathematically model data:

1. Relational Model (RDBMS): Data is stored in two-dimensional tables (relations) connected by common keys. (e.g., MySQL, Oracle).
2. Hierarchical Model: Data is stored in a strict tree-like structure with 1:N parent-child relationships. (e.g., IBM IMS).
3. Network Model: An extension of hierarchical where records can have multiple parents, forming a graph. (e.g., IDMS).
4. Object-Oriented Model: Data is stored as objects mirroring OOP programming languages, complete with encapsulation and inheritance.
5. NoSQL Models: Designed for massive horizontal scale. Includes Document (MongoDB), Key-Value (Redis), Column-Family (Cassandra), and Graph (Neo4j) models.

Example:
-- Relational Model uses strict mathematical relations:
SELECT * FROM Users WHERE id = 1;

Question 28

[5 Marks] Define: Data, Database, DBMS, Database System, Database Catalog.

Accepted Answer

* Data: Raw, unprocessed, isolated facts and figures that have implicit meaning (e.g., the number '42').
* Database (DB): A structured, organized collection of logically related data designed to be easily accessed, managed, and updated.
* DBMS (Database Management System): A complex software application (like PostgreSQL or Oracle) that interacts with end-users, applications, and the database itself to capture and analyze the data.
* Database System: The complete, holistic ecosystem comprising the Database, the DBMS software, and the Application Programs/Users interacting with it.
* Database Catalog (Data Dictionary): The internal 'metadata' repository where the DBMS stores the definitions of all tables, views, indexes, users, and constraints.

Example:
-- Querying the Database Catalog in PostgreSQL to see metadata:
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public';

Question 29

[5 Marks] Explain the Relational Algebra Natural Join with an example.

Accepted Answer

The Natural Join (denoted by ⨝) is a binary relational algebra operation that seamlessly combines the tuples of two relations over all their commonly named attributes.

Mechanically, it is equivalent to performing a Cartesian Product (Cross Join), followed by a Selection (σ) that forces equality on all identically named columns, followed by a Projection (π) that removes the duplicate column names from the final result set.
If Relation R has attribute 'A' and Relation S has attribute 'A', R ⨝ S will only return rows where R.A = S.A, returning column 'A' exactly once.

Example:
-- Algebra: Employee ⨝ Department

-- SQL Equivalent (If both tables share 'dept_id'):
SELECT * FROM Employee NATURAL JOIN Department;

-- Warning: Natural Join is dangerous in SQL if tables unexpectedly share columns like 'created_at'.

Question 30

[5 Marks] What is the Deferred Update approach in database recovery?

Accepted Answer

Deferred Update (also known as the NO-UNDO/REDO algorithm) is a database crash recovery technique designed to guarantee transaction Atomicity.

In this approach, when a transaction modifies data, the changes are written ONLY to the transaction log in memory/disk. The actual physical database data pages on the disk are NOT updated until the transaction reaches its COMMIT point.

Because the database disk is completely shielded from uncommitted changes, if the system crashes before a COMMIT occurs, the database needs NO UNDO operations upon reboot. It simply ignores the uncommitted log entries. However, if a crash occurs immediately after a COMMIT, the system must REDO the log entries to write them to the physical disk.

Example:
-- Transaction starts
UPDATE Accounts SET balance = 500 WHERE id = 1;
-- Change written to Log, but Database Disk remains OLD value.
-- SYSTEM CRASHES HERE -> No UNDO needed.
COMMIT;
-- Post-commit background process flushes new value to Database Disk.

Question 31

[5 Marks] What is a Transaction Deadlock? Provide an example.

Accepted Answer

A deadlock is a specific concurrency failure where two or more transactions form a 'circular wait' dependency. Neither transaction can proceed because each holds an exclusive lock on a resource that the other requires to finish.

Since neither can commit or rollback without the other's resource, they wait infinitely. The DBMS must intervene by scanning the Wait-For graph, detecting the cycle, and aborting one of the transactions (the victim).

Example:
-- Transaction A (Holds Lock on row 1):
UPDATE Inventory SET qty = qty - 1 WHERE item_id = 1;

-- Transaction B (Holds Lock on row 2):
UPDATE Inventory SET qty = qty - 1 WHERE item_id = 2;

-- Transaction A now requests row 2 (BLOCKED by B)
UPDATE Inventory SET qty = qty - 1 WHERE item_id = 2;

-- Transaction B now requests row 1 (BLOCKED by A) -> DEADLOCK CYCLE FORMED.

Question 32

[5 Marks] Explain Specialization and the Disjoint Constraint with an example.

Accepted Answer

Specialization is a top-down ER design process where a single high-level entity (the Superclass) is broken down into multiple specialized lower-level entities (Subclasses) based on distinct characteristics.

The Disjoint Constraint (denoted by a 'd' in the ER diagram circle connecting the subclasses) dictates whether an entity from the superclass can belong to more than one subclass simultaneously.

If the constraint is Disjoint (d), an entity can belong to a MAXIMUM of one subclass. If the constraint is Overlapping (o), an entity can belong to multiple subclasses simultaneously.

Example:
-- Disjoint Example: An 'Employee' can be specialized into 'Manager', 'Technician', or 'Clerk'.
-- Due to the Disjoint constraint, John Doe cannot be both a Manager and a Clerk at the same time.

-- Table Representation (Table-per-Class):
CREATE TABLE Employee (EmpID INT PRIMARY KEY, Name VARCHAR(50));
CREATE TABLE Manager (EmpID INT PRIMARY KEY REFERENCES Employee(EmpID), Bonus DECIMAL);
CREATE TABLE Clerk (EmpID INT PRIMARY KEY REFERENCES Employee(EmpID), TypingSpeed INT);

Question 33

[10 Marks] Define DBMS. Explain the advantages of a DBMS over a traditional File Processing System.

Accepted Answer

A Database Management System (DBMS) is specialized software that enables users to create, maintain, and securely query a database.

Differences & Advantages over File Processing Systems:
1. Data Redundancy & Inconsistency: File systems duplicate data across multiple files (e.g., a student's address in 'Library.txt' and 'Fees.txt'). DBMS uses normalization to store data centrally, eliminating redundancy and update anomalies.
2. Data Independence: In file systems, applications are hardcoded to the physical structure of the files. DBMS separates data structure from application logic (Data Independence).
3. Concurrent Access: File systems lock entire files, preventing multiple users from editing simultaneously. DBMS uses granular row-level locking for massive concurrency.
4. Security: File systems rely on basic OS file permissions. DBMS offers granular, row-level and column-level authorization.
5. Crash Recovery: File systems corrupt easily upon power loss. DBMS uses Write-Ahead Logging (WAL) to guarantee ACID Durability.

Example:
-- A DBMS ensures a single source of truth rather than fragmented files.
SELECT address FROM Students WHERE student_id = 101;

Question 34

[5 Marks] What is the difference between a Primary Key and a Foreign Key? Explain with examples.

Accepted Answer

Primary Key (PK) and Foreign Key (FK) are the fundamental constraints that build relational integrity.

Primary Key:
* Purpose: Uniquely identifies every single tuple (row) within a table.
* Rules: It cannot contain NULL values, and every value must be completely unique.
* Example: A 'StudentID' in a Students table.

Foreign Key:
* Purpose: Establishes a mathematical link (relationship) between two tables. It ensures Referential Integrity.
* Rules: A Foreign Key in a 'Child' table must point to an exact Primary Key value in a 'Parent' table (or be NULL). It CAN contain duplicate values (e.g., multiple orders belonging to the same customer).
* Example: A 'CustomerID' column in an Orders table pointing to the 'CustomerID' PK in a Customers table.

Example:
CREATE TABLE Customers (
  CustID INT PRIMARY KEY, -- Primary Key
  Name VARCHAR(50)
);

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustID INT, -- Foreign Key linking back to Customers
  FOREIGN KEY (CustID) REFERENCES Customers(CustID)
);

Question 35

[10 Marks] Draw an ER Diagram for a Hospital system and explain its components.

Accepted Answer

A Hospital ER Diagram consists of the following core components:

1. Entities (Rectangles):
   - DOCTOR (DocID, Name, Specialty)
   - PATIENT (PatID, Name, DOB, BloodGroup)
   - ROOM (RoomNo, Type)
   - TREATMENT (TreatID, Description)

2. Relationships (Diamonds):
   - ADMITTED_TO: A PATIENT is admitted to a ROOM (N:1). A room can hold multiple patients, but a patient occupies one room.
   - TREATES: A DOCTOR treats a PATIENT (M:N). A doctor sees many patients, and a patient sees many doctors.
   - UNDERGOES: A PATIENT undergoes a TREATMENT (M:N).

3. Cardinality & Participation:
   - DOCTOR to PATIENT is Many-to-Many.
   - Every PATIENT must have Total Participation in the TREATES relationship (every patient must be assigned a doctor).

Example:
-- Resolving the M:N TREATES relationship into a mapping table:
CREATE TABLE Doctor_Patient (
  DocID INT REFERENCES Doctor(DocID),
  PatID INT REFERENCES Patient(PatID),
  DateOfVisit DATE,
  PRIMARY KEY (DocID, PatID, DateOfVisit)
);

Question 36

[10 Marks] What is Normalization? Explain the process of normalizing a relation up to 3NF.

Accepted Answer

Normalization is the systematic process of organizing columns and tables in a database to minimize data redundancy and prevent Insertion, Deletion, and Update anomalies.

Process to 3NF:
* 1NF (Atomicity): Eliminate repeating groups. Ensure every cell contains a single, indivisible scalar value. Create a Primary Key.
* 2NF (No Partial Dependencies): The table must be in 1NF. If the table has a composite primary key, ensure no non-prime attribute depends on only a PART of that composite key. If it does, split the table.
* 3NF (No Transitive Dependencies): The table must be in 2NF. Ensure no non-prime attribute depends on another non-prime attribute. 'Every non-key attribute must depend on the key, the whole key, and nothing but the key.' If A->B and B->C, move B and C to a new table.

Example:
-- Unnormalized: Student(ID, Name, [Course1, Course2])
-- 1NF: Student(ID, Course, Name) -- 'Name' duplicates for every course!
-- 2NF/3NF Fix: 
CREATE TABLE Student (ID INT PRIMARY KEY, Name VARCHAR(50));
CREATE TABLE Registration (ID INT, Course VARCHAR(50), PRIMARY KEY(ID, Course));

Question 37

[10 Marks] Explain different types of SQL Joins (Inner, Left, Right, Full, Cross) with examples.

Accepted Answer

SQL Joins are used to mathematically combine rows from two or more tables based on a related column.

1. INNER JOIN: Returns only the rows that have matching values in BOTH tables.
2. LEFT (OUTER) JOIN: Returns all rows from the left table, and the matched rows from the right table. Unmatched right rows become NULL.
3. RIGHT (OUTER) JOIN: Returns all rows from the right table, and the matched rows from the left table.
4. FULL (OUTER) JOIN: Returns all rows when there is a match in EITHER the left or right table. Unmatched rows on either side become NULL.
5. CROSS JOIN (Cartesian Product): Returns the massive combination of every row from the left table with every row from the right table.

Example:
-- INNER JOIN (Intersection)
SELECT * FROM A INNER JOIN B ON A.id = B.id;

-- LEFT JOIN (Includes all of A, even if no match in B)
SELECT * FROM A LEFT JOIN B ON A.id = B.id;

-- FULL JOIN (Everything from both, matching where possible)
SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id;

Question 38

[5 Marks] What is Data Independence? Explain logical and physical data independence.

Accepted Answer

Data Independence is the ability to modify the database schema at one level without forcing a rewrite of the schema or application at the next higher level. It relies on the 3-Schema Architecture.

1. Physical Data Independence: The ability to modify the Internal Schema (physical storage, indexes, disk distribution, hashing) without altering the Conceptual Schema. For example, you can add a B-Tree index to speed up queries, and the SQL written by developers does not need to change.
2. Logical Data Independence: The ability to modify the Conceptual Schema (adding/removing columns or tables) without altering the External Schema (User Views or App queries). For example, splitting a table during normalization shouldn't break an app if a View is created to recreate the original structure. Logical independence is much harder to achieve than Physical.

Example:
-- Logical Data Independence Example: App relies on OldTable
-- We normalize OldTable into T1 and T2.
-- We preserve independence by creating a View that looks like OldTable:
CREATE VIEW OldTable AS SELECT T1.a, T2.b FROM T1 JOIN T2 ON T1.id = T2.id;

Question 39

[10 Marks] Explain Concurrency Control techniques: Locking vs Timestamp Ordering.

Accepted Answer

Concurrency Control ensures transactions execute safely in parallel without violating ACID Isolation.

1. Locking (e.g., Two-Phase Locking - 2PL): A pessimistic technique. Transactions must acquire mathematical 'locks' (Shared for reading, Exclusive for writing) on rows before accessing them. If a row is locked, other transactions must wait in a queue. This guarantees serializability but introduces the massive risk of Deadlocks, requiring deadlock detection algorithms.

2. Timestamp Ordering: An optimistic/non-blocking technique. Every transaction is given a unique monotonically increasing timestamp when it begins. Every row records the timestamp of the last transaction that read it (R-TS) and wrote to it (W-TS). When a transaction tries to access a row, the DBMS compares timestamps. If a transaction arrives 'too late' (trying to read something that was already overwritten by a 'younger' transaction), it is instantly aborted and restarted with a new timestamp. This completely eliminates deadlocks but can cause high abort rates.

Example:
-- Locking (Pessimistic approach requires active waiting)
SELECT * FROM Inventory WHERE id = 1 FOR UPDATE;

-- Timestamp Ordering simply aborts the transaction if:
-- Transaction TS < Read_TS(Row) or Transaction TS < Write_TS(Row)

Question 40

[10 Marks] Design an ER diagram for an Online Auction System (members, items, bids, feedback). Document assumptions and mapping constraints. Include a real-world example.

Accepted Answer

Designing an ER diagram for an Online Auction System requires careful mapping of user roles, temporal bidding data, and trust mechanics.

1. Entities and Attributes:
* MEMBER: Represents a registered user. Attributes: MemberID (Primary Key), Name, Email, RegistrationDate. We assume a Member can act as both a Buyer and a Seller, so we do not need disjoint subclasses unless specific attributes differ.
* ITEM: Represents the product up for auction. Attributes: ItemID (PK), Title, Description, StartingPrice, ReservePrice, EndTime.
* BID: A weak entity or transactional entity. Attributes: BidID (PK), BidAmount, BidTimestamp.
* FEEDBACK: Attributes: FeedbackID (PK), Rating (1-5), Comment, Date.

2. Relationships & Cardinality Constraints:
* LISTS (1:N): One MEMBER (Seller) can list many ITEMs. An ITEM is listed by exactly one MEMBER. Total participation for ITEM.
* PLACES_BID (1:N): One MEMBER (Buyer) can place many BIDs. A BID is placed by exactly one MEMBER.
* RECEIVES_BID (1:N): One ITEM can receive many BIDs. A BID belongs to exactly one ITEM.
* LEAVES_FEEDBACK (1:N): One MEMBER leaves feedback for another MEMBER regarding a specific ITEM. This is a ternary relationship between Member (Rater), Member (Ratee), and Item.

3. Mapping Constraints & Assumptions:
* Assumption: A bid amount must be strictly greater than the current highest bid (enforced via application logic/triggers, not purely ER).
* Assumption: An item cannot receive bids after its EndTime.

Real-World Example:
eBay operates on this exact conceptual model. When a seller lists an iPhone (ITEM), eBay's database tracks the 'LISTS' relationship to their seller ID. When millions of users attempt to buy it, eBay records instances in the BID table. To ensure high performance, the current highest bid is often cached, but the authoritative historical ledger of all bids is strictly maintained in the BID table mapped directly to the ITEM and Buyer MEMBER.

Example:
-- Standard Relational Mapping:
CREATE TABLE Member (MemberID INT PRIMARY KEY, Name VARCHAR(100));
CREATE TABLE Item (ItemID INT PRIMARY KEY, SellerID INT REFERENCES Member(MemberID), Title VARCHAR(100), EndTime TIMESTAMP);
CREATE TABLE Bid (BidID INT PRIMARY KEY, ItemID INT REFERENCES Item(ItemID), BuyerID INT REFERENCES Member(MemberID), Amount DECIMAL(10,2));

Question 41

[10 Marks] Explain query processing steps. Compare Nested Loop Join vs. Block Nested Loop Join. How do equivalence rules help? Include a real-world example.

Accepted Answer

Query Processing is the pipeline a DBMS uses to transform a high-level SQL query into a physical execution plan that retrieves data from disk.

1. Query Processing Steps:
* Parsing & Translation: The SQL string is checked for syntax and semantic errors. It is then translated into an internal relational algebra expression.
* Optimization: The DBMS Query Optimizer evaluates multiple execution strategies. It uses statistical metadata (table sizes, indexes) to estimate the disk I/O and CPU cost of each plan, selecting the cheapest.
* Evaluation: The Execution Engine takes the chosen plan and physically reads/writes the disk blocks to return the result.

2. Nested Loop Join vs. Block Nested Loop Join:
* Nested Loop Join: The simplest algorithm. For EVERY single row in the outer table, the engine scans the ENTIRE inner table looking for matches. If Table A has 10,000 rows and Table B has 10,000 rows, this requires 100,000,000 row comparisons. It is computationally disastrous for large datasets.
* Block Nested Loop Join: A massive optimization. Instead of fetching one row at a time, the DBMS loads an entire 'Block' (or Page) of the outer table into RAM. It then scans the inner table, comparing each inner row against the ENTIRE block of outer rows simultaneously in memory. This drastically reduces expensive Disk I/O fetches.

3. Equivalence Rules:
Equivalence rules allow the optimizer to rewrite queries into mathematically equivalent forms that run faster. The most critical rule is 'Pushing Selections Down' (performing WHERE clauses before JOINs) to minimize the dataset size early.

Real-World Example:
Imagine Netflix executing a query to find all users in 'New York' who watched 'Stranger Things'. 
SELECT * FROM Users U JOIN WatchHistory W ON U.id = W.user_id WHERE U.city = 'New York' AND W.show = 'Stranger Things';
The optimizer uses equivalence rules to filter the Users table down to just 'New York' BEFORE joining it with the massive multi-billion-row WatchHistory table. If it didn't push the selection down, it would join billions of rows in memory and then filter them, crashing the database server.

Example:
-- EXPLAIN allows developers to see if a Nested Loop or Hash Join was chosen
EXPLAIN ANALYZE SELECT * FROM Users U JOIN WatchHistory W ON U.id = W.user_id;

Question 42

[10 Marks] Explain types of DBMS failures. Differentiate Shadow Paging and Log-Based Recovery. Explain Shadow Paging deeply. Include a real-world example.

Accepted Answer

Database systems must survive unpredictable catastrophes. Recovery Manager subsystems ensure the Durability and Atomicity of transactions.

1. Types of Failures:
* Transaction Failures: Logical errors (e.g., division by zero, insufficient funds) or concurrency deadlocks that force a transaction to abort.
* System Crashes: Hardware power loss, OS panics, or RAM corruption. The volatile memory is lost, but the physical disk remains intact.
* Disk Failures: Catastrophic physical damage to the hard drive (head crashes). Requires RAID or off-site backups to recover.

2. Log-Based Recovery vs. Shadow Paging:
* Log-Based Recovery (WAL): The standard approach (used by PostgreSQL/MySQL). Every modification is sequentially appended to a transaction log on disk BEFORE the actual database table is updated. If a crash occurs, the system replays the log to REDO committed transactions and UNDO uncommitted ones.
* Shadow Paging: A completely different approach that requires NO transaction log for UNDO/REDO. It relies on manipulating disk pointers.

3. Deep Dive into Shadow Paging:
In Shadow Paging, the database maintains two page tables: the 'Current Page Table' (used by active transactions) and the 'Shadow Page Table' (the authoritative state on disk). 
When a transaction wants to modify a page, it does NOT overwrite the original page on disk. Instead, it copies the page to a new, empty location on disk (the Shadow Page), modifies the copy, and updates the Current Page Table to point to this new location.
Crucially, the Shadow Page Table still points to the old, unmodified data. 
When the transaction COMMITS, the DBMS simply performs a single atomic operation: it overwrites the pointer of the Shadow Page Table to match the Current Page Table. 
If the system crashes BEFORE commit, the database just throws away the Current Page Table. Upon reboot, the Shadow Page Table is still pointing at the perfectly intact original data. No UNDO is required!

Real-World Example:
While traditional RDBMS engines use WAL, the Shadow Paging concept (also known as Copy-On-Write) is the foundational architecture of the ZFS File System and Apple's APFS. When you save a massive video file on a Mac, APFS writes the new edits to a shadow location. Only when the save is 100% successful does it flip the master pointer. This is why pulling the plug on a modern Mac rarely corrupts the file system.

Example:
-- Shadow Paging is a hardware/disk-level architectural implementation.
-- As a developer, you do not write SQL for it; it happens transparently during:
COMMIT WORK;

Question 43

[10 Marks] Hotel Schema: Write SQL for guests who booked at Everest Hotel, create a view, find total cost after discount. Include a real-world example.

Accepted Answer

Assuming a standard schema:
HOTEL(HotelNo, Name, City)
ROOM(RoomNo, HotelNo, Type, Price)
BOOKING(HotelNo, GuestNo, DateFrom, DateTo, RoomNo)
GUEST(GuestNo, Name, Address)

In academic exams, large SQL questions test a student's ability to navigate multiple JOINs, generate virtual tables (Views), and perform inline mathematics.

1. Guests who booked at 'Everest Hotel':
To find this, we must traverse from GUEST -> BOOKING -> HOTEL.
We join these three tables based on their primary/foreign key relationships and apply a WHERE clause filter for the hotel name.

2. Creating a View:
A View acts as a saved, virtual query. If the hotel manager frequently needs to see booking details without writing complex joins, we encapsulate the logic in a CREATE VIEW statement.

3. Total Cost after Discount:
This requires understanding SQL aggregate functions (SUM) and basic arithmetic applied directly to a column. If the hotel gives a 10% discount, the mathematical formula applied to the row is `Price * 0.90`. We must JOIN the BOOKING table to the ROOM table to access the Price column, calculate the total days stayed (DateTo - DateFrom), multiply by the Price, apply the 0.90 multiplier, and SUM the results.

Real-World Example:
Marriott International uses massive enterprise Property Management Systems (PMS). When a front-desk agent searches for 'Guests at the New York Marriott', the application fires a 4-way JOIN query matching Guest IDs to Reservation IDs to Property IDs. To prevent the front-desk app from running these massive queries directly, database administrators wrap them in highly optimized SQL Views, ensuring the application code remains clean and the database engine caches the execution plan.

Example:
-- 1. Guests at Everest Hotel:
SELECT DISTINCT G.Name 
FROM GUEST G 
JOIN BOOKING B ON G.GuestNo = B.GuestNo 
JOIN HOTEL H ON B.HotelNo = H.HotelNo 
WHERE H.Name = 'Everest Hotel';

-- 2. Create a View for Active Bookings:
CREATE VIEW Active_Bookings AS
SELECT G.Name, H.Name AS HotelName, B.DateFrom 
FROM BOOKING B JOIN GUEST G ON B.GuestNo = G.GuestNo JOIN HOTEL H ON B.HotelNo = H.HotelNo;

-- 3. Find Total Revenue after 10% discount for a specific guest (e.g. GuestNo = 1):
SELECT SUM((R.Price * (B.DateTo - B.DateFrom)) * 0.90) AS Total_Discounted_Cost
FROM BOOKING B JOIN ROOM R ON B.RoomNo = R.RoomNo AND B.HotelNo = R.HotelNo
WHERE B.GuestNo = 1;

Question 44

[10 Marks] Define the Conceptual Data Model. Explain the different types of attributes in an ER diagram with examples. Include a real-world example.

Accepted Answer

Conceptual Data Model:
The conceptual data model is the highest-level blueprint of a database. It hides all physical storage details (like B-trees or data types) and focuses purely on identifying the core business Entities, their Attributes, and the Relationships between them. The ER (Entity-Relationship) Model is the most popular conceptual model.

Types of Attributes in ER Diagrams:
1. Simple (Atomic) Attribute: An attribute that cannot be logically subdivided. Example: A 'Gender' or 'Age' attribute.
2. Composite Attribute: An attribute that can be divided into smaller sub-parts with independent meanings. Example: A 'Name' attribute broken into 'FirstName', 'MiddleName', and 'LastName'.
3. Single-Valued Attribute: An attribute that holds only one value for a specific entity instance. Example: A person's 'Social Security Number'.
4. Multi-Valued Attribute (Double Oval): An attribute that can hold multiple values simultaneously. Example: A 'Phone_Numbers' attribute for a user who has a home, work, and mobile phone. (These violate 1NF and must be normalized into separate tables in SQL).
5. Derived Attribute (Dashed Oval): An attribute whose value is not physically stored but is dynamically calculated from other related attributes. Example: 'Age' derived from 'Date_Of_Birth'.
6. Key Attribute (Underlined): An attribute that uniquely identifies an entity within its entity set. Example: 'EmployeeID'.

Real-World Example:
In Amazon's customer database, the 'Delivery Address' is originally modeled as a Composite Attribute (Street, City, State, Zip) to ensure frontend forms capture data cleanly. 'Previous Orders' is a Multi-Valued attribute, and a customer's 'Lifetime Value' is a Derived attribute calculated dynamically on the dashboard by summing the total of all previous orders.

Example:
-- Normalizing a Multi-Valued Attribute (Phone_Numbers)
CREATE TABLE User (UserID INT PRIMARY KEY, Name VARCHAR(50));
CREATE TABLE UserPhone (UserID INT REFERENCES User(UserID), Phone VARCHAR(20), PRIMARY KEY(UserID, Phone));

Question 45

[10 Marks] Explain Tuple Relational Calculus (TRC). How does it differ from Relational Algebra? Include a real-world example.

Accepted Answer

Tuple Relational Calculus (TRC) is a non-procedural (declarative) query language used to mathematically define the exact data you want to retrieve from a relational database, without specifying the step-by-step algorithms required to get it.

Differences from Relational Algebra:
* Relational Algebra is Procedural: It provides a strict sequence of mathematical operations (Select, Project, Join) detailing exactly HOW the database should retrieve the data.
* Relational Calculus is Declarative: It uses First-Order Predicate Logic to describe WHAT data is desired. It uses variables that represent entire tuples (rows) rather than specific attributes.

Mathematical Formulation:
The basic syntax is `{ t | P(t) }`, which reads as: 'Retrieve the set of all tuples t such that the predicate P is true for t'.
For example, `{ t | t ∈ Employees ∧ t.salary > 50000 }` retrieves all entire employee records where the salary exceeds $50k.
It also utilizes universal quantifiers (∀ - For All) and existential quantifiers (∃ - There Exists) to handle complex cross-table queries.

Real-World Example:
Modern SQL is heavily inspired by TRC. When a data scientist at Netflix writes a SQL query (`SELECT * FROM Users WHERE country = 'USA'`), they are writing declarative TRC. They simply state the 'Predicate' (country = USA), and they trust the database's internal Query Optimizer to translate that TRC into procedural Relational Algebra (like pushing selections down and executing a Hash Join) to physically fetch the bytes from the SSD.

Example:
-- Declarative TRC (What to get):
-- { t.name | t ∈ Employees ∧ t.department = 'IT' }

-- SQL Implementation of the TRC statement:
SELECT name FROM Employees WHERE department = 'IT';

Question 46

[10 Marks] Explain different types of indexes (Ordered vs Hash). Differentiate Static vs Dynamic Hashing. Include a real-world example.

Accepted Answer

Indexes are auxiliary data structures used by the DBMS to achieve O(log N) or O(1) query performance instead of disastrous O(N) sequential table scans.

1. Ordered Indexes (B-Trees / B+ Trees):
Data entries are sorted mathematically. This is the default index type in almost all RDBMS (PostgreSQL, MySQL). 
* Strengths: Excellent for Range Queries (<, >, BETWEEN), sorting (ORDER BY), and prefix matching (LIKE 'A%').
* Weaknesses: O(log N) lookup is slightly slower than a direct hash lookup.

2. Hash Indexes:
Uses a mathematical Hash Function to instantly calculate the exact physical disk block (bucket) where a record lives based on its key. 
* Strengths: Provides blazing fast O(1) lookup speeds.
* Weaknesses: Absolutely useless for Range Queries or Sorting. It only works for exact equality matching (=).

3. Static vs Dynamic Hashing:
* Static Hashing: The database allocates a fixed number of disk buckets. If a bucket gets full, an overflow bucket is chained to it. As the database grows, overflow chains become massive, degrading O(1) performance back to O(N).
* Dynamic (Extendible) Hashing: The hash table dynamically grows or shrinks. The directory of buckets doubles in size when a bucket overflows, gracefully handling massive data scaling without performance degradation.

Real-World Example:
Redis, the world's most popular in-memory caching database, relies almost entirely on Dynamic Hash Indexing. Because caching operations usually involve exact key lookups (e.g., `GET session_id_12345`), the Hash Index guarantees constant O(1) microsecond retrieval times regardless of whether the cache holds a thousand or a billion sessions.

Example:
-- B-Tree Index (Great for the BETWEEN clause)
CREATE INDEX idx_user_age ON Users USING BTREE(age);
SELECT * FROM Users WHERE age BETWEEN 20 AND 30;

-- Hash Index (Great for EXACT MATCH user logins)
CREATE INDEX idx_user_email ON Users USING HASH(email);
SELECT * FROM Users WHERE email = 'admin@example.com';

Question 47

[10 Marks] Explain RAID levels. Critically compare RAID 5 vs RAID 6. Include a real-world example.

Accepted Answer

RAID (Redundant Array of Independent Disks) is a hardware or software technology that combines multiple physical hard drives into a single logical unit to increase Data Redundancy (Fault Tolerance) and I/O Performance.

Standard RAID Levels:
* RAID 0 (Striping): Spreads data across disks for massive speed. Zero redundancy. If one disk dies, everything is lost.
* RAID 1 (Mirroring): Exact copies of data on two disks. High read speed, 100% redundancy, but wastes 50% of storage capacity.
* RAID 10 (Striping + Mirroring): Combines RAID 1 and RAID 0. Expensive but extremely fast and safe. Ideal for heavy OLTP databases.

RAID 5 vs RAID 6:
* RAID 5 (Striping with Parity): Uses mathematical parity data distributed across all drives. Requires a minimum of 3 drives. It can survive the complete failure of exactly ONE drive. It is highly efficient, utilizing (N-1) storage capacity.
* RAID 6 (Striping with Double Parity): Uses two independent parity blocks per stripe. Requires a minimum of 4 drives. It can survive the simultaneous failure of exactly TWO drives. It utilizes (N-2) storage capacity.
* Comparison: RAID 6 has much slower write speeds than RAID 5 because it must calculate and write two parities. However, RAID 5 is considered dangerous for massive modern hard drives (10TB+) because rebuilding a failed RAID 5 array takes days, during which a second drive failure (called a URE - Unrecoverable Read Error) will destroy the entire database. RAID 6 protects against this rebuild failure window.

Real-World Example:
AWS RDS (Amazon Relational Database Service) does not use RAID 5 because of the unacceptably high risk of a second disk failing during an enterprise rebuild. Enterprise SAN (Storage Area Network) arrays hosting massive Oracle or SQL Server databases almost exclusively use RAID 10 for performance or RAID 6 for archival storage safety.

Example:
-- RAID is implemented at the hardware/OS level, below the DBMS.
-- The database simply sees an incredibly fast, highly reliable single mount point.
-- e.g., PostgreSQL data directory mounted on /dev/md0 (a RAID 10 array)

Question 48

[10 Marks] What is Distributed DBMS Transparency? Briefly explain Spatial Databases. Include a real-world example.

Accepted Answer

Distributed DBMS Transparency:
A Distributed Database consists of multiple interconnected databases spread across different geographical locations/servers, yet appearing to the user as a single, unified database. 'Transparency' is the ultimate goal of a DDBMS—it means hiding the implementation complexities from the end-user.

Key forms of Transparency:
1. Location Transparency: The user queries data (e.g., `SELECT * FROM Employees`) without needing to know if the data resides on a server in Tokyo or New York.
2. Fragmentation Transparency: The user doesn't know that the 'Employees' table has been split (fragmented) horizontally across three different servers based on their department.
3. Replication Transparency: The system automatically duplicates data across servers for high availability, but the user only sees one logical copy of the table.

Spatial Databases:
A spatial database is a specialized DBMS designed to store, query, and index multi-dimensional geometric data (points, lines, polygons) rather than standard text or numbers. Standard B-Trees fail here; spatial databases use specialized R-Trees (Rectangle Trees) to rapidly index bounding boxes.

Real-World Example:
Uber runs one of the largest Spatial and Distributed databases in the world. When you open the Uber app, the system queries a Spatial Database (like PostgreSQL with PostGIS extensions) to calculate the geometric distance between your GPS 'Point' and thousands of surrounding driver 'Points'. Because Uber is global, it uses a highly transparent Distributed DBMS (like Cassandra) to route your query seamlessly to the nearest datacenter without the mobile app knowing where the servers are located.

Example:
-- Spatial Database Query (PostGIS in PostgreSQL)
-- Finding all available drivers within a 5-kilometer radius (5000 meters) of the user's GPS point.
SELECT driver_name 
FROM Drivers 
WHERE ST_DWithin(
  driver_location, 
  ST_MakePoint(-122.4194, 37.7749), 
  5000
);

Question 49

[10 Marks] Explain the roles of the Database Administrator (DBA), Database Designer, and other end-users in a database system.

Accepted Answer

A robust Database System requires distinct human roles to function, each operating at a different level of the 3-Schema Architecture.

1. Database Administrator (DBA):
The DBA is the chief technical authority responsible for the physical and operational health of the database (Internal Schema).
* Roles: Installing/upgrading the DBMS software, provisioning hardware storage, monitoring performance/bottlenecks, executing backups and disaster recovery protocols, and managing security (creating users and granting granular access privileges).
* Real-World Example: A DBA at Netflix noticing that a specific query is causing CPU spikes, and resolving it by analyzing the execution plan and adding a composite B-Tree index.

2. Database Designer:
The Designer is the architect responsible for the logical structure of the database (Conceptual Schema) before a single line of data is ever inserted.
* Roles: Communicating with business stakeholders to understand data requirements, drawing massive Entity-Relationship (ER) diagrams, applying Normalization rules (1NF to BCNF) to prevent anomalies, and defining primary/foreign key constraints.

3. Application Programmers / Software Engineers:
They interact with the External Schema. They write the application logic (in Java, Python, Node.js) that connects to the database via drivers (JDBC/ODBC) and executes dynamic SQL queries or stored procedures to feed data to the end-users.

4. End Users:
* Naive/Parametric Users: The vast majority of users. They interact with the database entirely through GUI applications (e.g., a bank teller scanning a check). They never see SQL.
* Sophisticated Users: Data analysts and business intelligence professionals who write complex, ad-hoc SQL queries directly against the database to generate custom reports.

Example:
-- DBA Role: Managing Security
CREATE USER analyst WITH PASSWORD 'secure123';
GRANT SELECT ON FinancialReportingView TO analyst;

Question 50

[10 Marks] Distinguish between Super Key, Candidate Key, Primary Key, and Foreign Key with examples.

Accepted Answer

Keys are the mathematical bedrock of the relational model, ensuring data uniqueness and establishing inter-table relationships.

1. Super Key:
A Super Key is ANY set of one or more attributes that can uniquely identify a row in a table. It is the broadest definition of a key. It may contain redundant attributes that are completely unnecessary for uniqueness.
* Example: In an Employee table, {EmpID}, {EmpID, Name}, and {EmpID, Name, DOB} are ALL Super Keys, because the presence of EmpID mathematically guarantees the entire set is unique.

2. Candidate Key:
A Candidate Key is a 'minimal' Super Key. It is a Super Key with absolutely no redundant attributes. If you remove even a single attribute from a Candidate Key, it loses its ability to uniquely identify a row.
* Example: A table might have two Candidate Keys: {EmpID} and {SocialSecurityNumber}. Both uniquely identify an employee, and neither can be reduced further.

3. Primary Key:
A Primary Key is the single Candidate Key explicitly chosen by the database designer to act as the primary means of identifying rows. A table can have many Candidate Keys, but only ONE Primary Key. It absolutely cannot contain NULL values.
* Example: The designer chooses {EmpID} as the Primary Key over {SocialSecurityNumber} because it is a shorter, auto-incrementing integer (faster for indexing).

4. Foreign Key:
An attribute (or set of attributes) in one table that perfectly matches the Primary Key of another table. It enforces Referential Integrity, ensuring that a child record cannot point to a non-existent parent.
* Example: 'DeptID' inside the Employee table acting as a Foreign Key pointing to 'DeptID' in the Department table.

Example:
CREATE TABLE Employee (
  EmpID INT PRIMARY KEY, -- Chosen Primary Key (was a Candidate Key)
  SSN VARCHAR(11) UNIQUE NOT NULL, -- Another Candidate Key
  DeptID INT REFERENCES Department(DeptID) -- Foreign Key
);
-- {EmpID, DeptID} is a Super Key, but NOT a Candidate Key.

Question 51

[10 Marks] Explain the different Transaction States in a DBMS.

Accepted Answer

A database transaction is an atomic logical unit of work. From the moment it begins until it finally terminates, a transaction transitions through specific states managed by the DBMS Transaction Manager.

1. Active State:
The initial state. The transaction enters this state the moment it begins executing its read/write instructions. As long as it is processing operations, it remains 'Active'.

2. Partially Committed State:
The transaction enters this state after the very last operation has been executed, but BEFORE the final COMMIT is written to disk. The transaction has finished its logic in RAM, but the Write-Ahead Log (WAL) has not yet confirmed that the data is safely persisted to the hard drive.

3. Committed State:
The successful terminal state. The transaction enters this state only after the WAL confirms the commit record is permanently flushed to non-volatile disk storage. Once committed, the ACID property of 'Durability' guarantees the changes cannot be lost, even if the power fails 1 millisecond later.

4. Failed State:
A transaction enters this state if a fatal error occurs during the Active or Partially Committed states. This could be a logical error (e.g., dividing by zero, violating a UNIQUE constraint) or a system intervention (e.g., the DBMS killing the transaction to resolve a deadlock).

5. Aborted State:
The unsuccessful terminal state. After a transaction fails, the Recovery Manager must execute an UNDO operation, rolling back any partial changes to ensure 'Atomicity'. Once the rollback is complete and the database is restored to its pristine pre-transaction state, the transaction is marked as 'Aborted'.

Example:
BEGIN TRANSACTION; -- Enters Active State
  UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
  -- If an error happens here -> Enters Failed State -> Enters Aborted State
COMMIT; -- Enters Partially Committed -> Then Enters Committed State

Question 52

[10 Marks] Differentiate between Tuple Relational Calculus (TRC) and Domain Relational Calculus (DRC).

Accepted Answer

Both TRC and DRC are declarative, formal query languages based on mathematical predicate logic. They specify WHAT data to retrieve without explaining HOW to retrieve it. However, they differ entirely in the variables they use to evaluate the logic. 1. Tuple Relational Calculus (TRC): * Variable Type: In TRC, variables represent entire tuples (rows). * Mechanism: The TRC engine evaluates the predicate against whole rows at a time. * Syntax: `{ t | P(t) }` where 't' is a tuple variable and P is the predicate condition. * Example: To find the names of employees in the IT department, you define a tuple variable 't' that iterates over the Employees relation. The query asks for `t.name` where `t.department = 'IT'`. 2. Domain Relational Calculus (DRC): * Variable Type: In DRC, variables represent specific domains (columns/attributes) rather than whole rows. * Mechanism: The DRC engine evaluates predicates against specific column values. * Syntax: `{ | P(x1, x2, ..., xn) }` where x1, x2 are domain variables. * Example: To find the names of employees in the IT department, you define domain variables for Name (N) and Department (D). The query asks for `{ | ∈ Employees }`. Significance: TRC forms the theoretical foundation for modern SQL (where you select whole rows and filter them with WHERE). DRC forms the theoretical foundation for Query-By-Example (QBE), a visual database querying language where users fill out blank column templates to filter data. Example: -- TRC-style approach (Iterating over rows): SELECT e.name FROM Employees e WHERE e.department = 'IT'; -- DRC-style approach is not directly written in SQL, -- but conceptually matches binding conditions directly to columns in QBE grids.

Question 53

[10 Marks] Explain the components of an ER model: Entity types, attributes, keys, relationships, cardinality, and participation.

Accepted Answer

The Entity-Relationship (ER) model is a high-level conceptual tool used to visually architect a database before writing SQL.

1. Entity Types:
The primary objects or concepts of interest. Drawn as Rectangles. Example: 'STUDENT' or 'COURSE'. A Weak Entity (drawn as a double rectangle) cannot exist independently and relies on a strong entity (e.g., 'ROOM' relying on 'BUILDING').

2. Attributes:
The descriptive properties of an entity. Drawn as Ovals attached to the entity. 
* Simple: Cannot be broken down (Age).
* Composite: Can be subdivided (Name -> First, Last).
* Multi-valued (Double Oval): Holds multiple values (PhoneNumbers).
* Derived (Dashed Oval): Calculated dynamically (Age derived from DateOfBirth).

3. Keys:
Attributes that uniquely identify an entity instance. Drawn as Ovals with underlined text. Example: `StudentID`.

4. Relationships:
The associations between entities. Drawn as Diamonds. Example: An 'ENROLLS_IN' relationship connects STUDENT to COURSE.

5. Cardinality Constraints:
Defines the numerical mapping between entities in a relationship:
* 1:1 (One-to-One): A Manager heads exactly one Department.
* 1:N (One-to-Many): A Department employs many Employees.
* M:N (Many-to-Many): A Student takes many Courses, and a Course has many Students.

6. Participation Constraints:
* Total Participation (Double Line): Every entity in the set MUST be involved in the relationship. (e.g., Every Employee MUST belong to a Department).
* Partial Participation (Single Line): An entity MAY OR MAY NOT be involved. (e.g., An Employee MAY OR MAY NOT be a Manager).

Example:
-- M:N Cardinality forces the creation of a Junction Table in SQL:
CREATE TABLE Student_Course (
  StudentID INT REFERENCES Student(ID),
  CourseID INT REFERENCES Course(ID),
  PRIMARY KEY (StudentID, CourseID)
);

Question 54

[10 Marks] Define Schema and Instance in the context of DBMS. Explain the different levels of data abstraction.

Accepted Answer

Schema vs. Instance:
* Schema: The overall logical design and blueprint of the database. It defines the tables, columns, data types, and constraints. The schema is highly static; once defined by the Database Designer, it rarely changes. (Analogy: The architectural blueprint of a house).
* Instance: The actual data stored in the database at a specific moment in time. The instance is highly dynamic and changes constantly as users execute INSERT, UPDATE, and DELETE operations. (Analogy: The furniture and people currently inside the house).

Levels of Data Abstraction:
To shield end-users from the complex reality of how data is physically stored on SSDs, DBMS uses three levels of abstraction:
1. Physical (Internal) Level: The lowest level of abstraction. It describes EXACTLY how the data is stored in hardware. It deals with byte allocations, file organization, B-Tree indexes, and hash tables. Only system administrators and DBA's operate here.
2. Logical (Conceptual) Level: The middle level. It describes WHAT data is stored in the database and the relationships between those data entities. It defines tables (like 'Employee') and constraints. Application developers operate at this level, completely unaware of physical disk sectors.
3. View (External) Level: The highest level. It describes only a specific PORTION of the database relevant to a specific user group. It completely hides the rest of the database for security and simplicity. For example, a Payroll Clerk might have a 'View' showing salaries, but they are physically prevented from seeing the same database's medical records table.

Example:
-- Creating the Schema (Logical Level):
CREATE TABLE Users (id INT PRIMARY KEY, name VARCHAR(50));

-- Modifying the Instance (Data at a point in time):
INSERT INTO Users VALUES (1, 'Alice');
UPDATE Users SET name = 'Bob' WHERE id = 1;

Question 55

[5 Marks] Differentiate critically between Data and Information.

Accepted Answer

Data and Information are the foundational concepts of computing, but they represent entirely different stages in the data processing lifecycle.

Data:
* Definition: Raw, unorganized, and isolated facts, figures, or symbols. 
* Characteristics: Lacks context, meaning, and structure. It is the raw input that a computer system captures.
* Example: The number '101', the string 'Apple', or a completely random timestamp like '2023-01-01'. Without context, you do not know if '101' is a temperature, a room number, or an ID.

Information:
* Definition: Data that has been processed, structured, contextualized, and organized to provide immediate meaning and value to a human decision-maker.
* Characteristics: It answers questions (Who, What, Where, When). It is the output of a data processing system.
* Example: 'Room 101 reached a critical temperature of 101 degrees Fahrenheit on 2023-01-01.' Here, the raw numbers have been linked together to provide actionable meaning.

The core purpose of a Database Management System (DBMS) is to securely store massive volumes of raw Data and provide powerful query tools (like SQL Aggregations) to instantly transform it into Information.

Example:
-- RAW DATA (Meaningless on its own):
-- 5000, 4500, 6000

-- INFORMATION (Contextualized via SQL):
SELECT department, AVG(salary) AS Average_Income 
FROM Employees 
GROUP BY department;

Question 56

[10 Marks] Compare a DBMS with a traditional File Processing System point-by-point.

Accepted Answer

Before the invention of RDBMS in the 1970s, organizations relied on traditional File Processing Systems (like flat .txt or .csv files). The limitations of file systems necessitated the invention of the DBMS.

1. Data Redundancy & Consistency:
* File System: Highly redundant. The exact same data (e.g., student address) is often duplicated across multiple files (Library file, Registrar file). If a student moves, updating one file but forgetting the other causes severe Data Inconsistency.
* DBMS: Uses Normalization to store data in a single central location, completely eliminating redundancy and guaranteeing consistency.

2. Data Independence:
* File System: The physical structure of the data file is hardcoded into the application logic (e.g., a C program reading a file expects the 'Name' starting exactly at byte 10). If the file structure changes, the application completely breaks.
* DBMS: Implements Data Independence. The application writes abstract SQL (`SELECT Name FROM Users`). The DBMS handles the physical bytes, allowing the physical disk layout to change without breaking the app.

3. Concurrency Control:
* File System: Only allows one user to write to a file at a time (File-level locking). Massive bottleneck.
* DBMS: Uses Row-level locking and ACID transactions, allowing thousands of users to safely edit the database simultaneously.

4. Security & Integrity:
* File System: Relies on basic OS permissions (Read/Write). Cannot enforce logic like 'Age must be > 18'.
* DBMS: Features granular column-level authorization, Views, and built-in CHECK constraints to mathematically guarantee data integrity.

Example:
-- A DBMS strictly enforces logic, which a File System cannot do:
ALTER TABLE Employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

Question 57

[5 Marks] Deep Dive: Explain the concepts of the Database Catalog and Metadata.

Accepted Answer

The Database Catalog (often referred to as the Data Dictionary or System Catalog) is the highly-protected internal repository where the DBMS stores all of its critical operational knowledge.

Metadata:
* Definition: Simply put, it is 'Data about Data'. 
* In a relational database, metadata is not the actual user records (like 'John Doe'), but rather the structural definitions of the tables where 'John Doe' lives.
* It includes table names, column names, exact data types (VARCHAR, INT), primary/foreign key definitions, trigger logic, index structures, user access privileges, and crucially, statistical data (like how many rows a table currently holds, which the Query Optimizer relies on to calculate fast execution paths).

The Catalog:
* The catalog is actually just a set of specialized, hidden system tables maintained exclusively by the DBMS software itself. 
* When a developer executes a `CREATE TABLE` command, the DBMS does not just allocate disk space; it explicitly inserts a new row into its internal Catalog tables to permanently remember that the new table exists. 
* If the Catalog is corrupted, the DBMS completely forgets how to read its own physical data files, resulting in catastrophic failure.

Example:
-- Querying the Database Catalog (Metadata) in PostgreSQL:
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'users';

Question 58

[10 Marks] Draw an ER diagram for the Procurement Department of ABC Organization (tracking furniture/equipment in buildings/rooms). Document assumptions and constraints.

Accepted Answer

Architecting an ER Diagram for a complex Procurement & Asset Tracking System requires a strict hierarchical approach.

1. Entities & Attributes:
* BUILDING: Represents a physical location. Attributes: BuildingID (Primary Key), Name, Address.
* ROOM: A physical space inside a building. Modeled as a Weak Entity because a 'Room 101' cannot exist independently of a Building. Attributes: RoomNo (Partial Key), Capacity.
* VENDOR: The supplier of equipment. Attributes: VendorID (PK), Name, ContactInfo.
* EQUIPMENT (Asset): The core tracked entity. Attributes: AssetTag (PK), Category (e.g., Furniture, IT), PurchaseDate, Cost.

2. Relationships & Cardinality:
* CONTAINS (1:N): A BUILDING contains many ROOMs. A ROOM belongs to exactly one BUILDING. (Identifying relationship with Total Participation for the Room).
* HOUSED_IN (N:1): Many EQUIPMENT items are housed in exactly one ROOM. An EQUIPMENT item must belong to a room (Total Participation).
* PURCHASED_FROM (N:1): Many EQUIPMENT items are purchased from exactly one VENDOR.

3. Mapping Constraints & Assumptions:
* Assumption: Equipment is strictly tracked per room. An item cannot span multiple rooms simultaneously.
* Assumption: If a Building is demolished (deleted), all associated Rooms are destroyed via a CASCADE constraint, but the Equipment might be moved beforehand (handled via application logic before deletion).

Example:
-- Weak Entity Mapping for ROOM:
CREATE TABLE Room (
  BuildingID INT,
  RoomNo VARCHAR(20),
  PRIMARY KEY (BuildingID, RoomNo),
  FOREIGN KEY (BuildingID) REFERENCES Building(BuildingID) ON DELETE CASCADE
);
-- Asset Tracking Mapping:
CREATE TABLE Equipment (
  AssetTag VARCHAR(50) PRIMARY KEY,
  BuildingID INT,
  RoomNo VARCHAR(20),
  VendorID INT REFERENCES Vendor(VendorID),
  FOREIGN KEY (BuildingID, RoomNo) REFERENCES Room(BuildingID, RoomNo)
);

Question 59

[10 Marks] Construct an ER diagram for a University System (Courses, Students, Instructors, Enrollments, Offerings).

Accepted Answer

A University ER Model revolves around the critical distinction between an abstract 'Course' and a specific 'Course Offering' (or Section) taught in a specific semester.

1. Entities & Attributes:
* STUDENT: Attributes: StudentID (PK), Name, Major, EnrollmentYear.
* INSTRUCTOR: Attributes: FacultyID (PK), Name, Department, Title.
* COURSE: The abstract catalog definition. Attributes: CourseCode (PK), Title, Credits (e.g., 'CS101', 'Intro to DB', 3).
* OFFERING (Section): A Weak Entity depending on the Course. It represents a physical class taking place. Attributes: SectionNo (Partial Key), Semester, Year, RoomNo.

2. Relationships & Cardinality:
* HAS_OFFERING (1:N): One abstract COURSE has many OFFERINGs over time. An OFFERING belongs to exactly one COURSE. (Identifying relationship).
* TEACHES (1:N): One INSTRUCTOR teaches many OFFERINGs. An OFFERING is taught by one INSTRUCTOR. (Note: Can be M:N if co-teaching is allowed).
* ENROLLS_IN (M:N): A STUDENT enrolls in many OFFERINGs. An OFFERING has many STUDENTs. This relationship has an important descriptive attribute: 'Grade'.

3. Assumptions:
* The 'Grade' attribute cannot belong to the Student (a student has many grades) nor the Offering (an offering has many grades). It must reside on the ENROLLS_IN relationship diamond itself.

Example:
-- Mapping the M:N ENROLLS_IN relationship with the descriptive 'Grade' attribute:
CREATE TABLE Enrollment (
  StudentID INT REFERENCES Student(StudentID),
  CourseCode VARCHAR(10),
  SectionNo INT,
  Semester VARCHAR(10),
  Year INT,
  Grade VARCHAR(2),
  PRIMARY KEY (StudentID, CourseCode, SectionNo, Semester, Year),
  FOREIGN KEY (CourseCode, SectionNo, Semester, Year) 
    REFERENCES Offering(CourseCode, SectionNo, Semester, Year)
);

Question 60

[10 Marks] Explain Specialization vs. Generalization in EER Modeling. Discuss disjoint and overlapping constraints with examples.

Accepted Answer

Enhanced Entity-Relationship (EER) modeling introduces Object-Oriented concepts (Inheritance) into database design.

1. Specialization (Top-Down Approach):
The process of taking a high-level entity (Superclass) and breaking it down into specialized sub-entities (Subclasses) based on distinct, unique attributes. 
* Example: You have an 'Account' superclass. You specialize it into 'Savings_Account' (attribute: InterestRate) and 'Checking_Account' (attribute: OverdraftLimit).

2. Generalization (Bottom-Up Approach):
The exact reverse process. You notice multiple distinct entities share common attributes, so you synthesize a new Superclass to hold those commonalities.
* Example: You have 'Car' and 'Truck' entities. You generalize them into a 'Vehicle' superclass to hold the common 'LicensePlate' and 'VIN' attributes.

3. EER Constraints:
* Disjoint Constraint (d): An entity from the superclass can belong to a MAXIMUM of one subclass. Example: An 'Account' must be EITHER a Savings OR a Checking account. It cannot be both simultaneously.
* Overlapping Constraint (o): An entity from the superclass can belong to MULTIPLE subclasses simultaneously. Example: A 'Person' at a university can be BOTH an 'Alumni' AND a 'Faculty' member at the exact same time.

Example:
-- Implementing Specialization using the 'Table-per-Subclass' architecture:
CREATE TABLE Vehicle (VIN VARCHAR(50) PRIMARY KEY, Year INT);
CREATE TABLE Truck (VIN VARCHAR(50) PRIMARY KEY REFERENCES Vehicle(VIN), TowingCapacity INT);
CREATE TABLE Car (VIN VARCHAR(50) PRIMARY KEY REFERENCES Vehicle(VIN), TrunkVolume INT);

Question 61

[10 Marks] Draw an ER diagram for a Banking System. Ensure you model Customers, Accounts, Branches, and Transactions.

Accepted Answer

A Banking ER Model is a classic academic requirement because it heavily tests Many-to-Many relationships and transactional ledger tracking.

1. Entities & Attributes:
* CUSTOMER: Attributes: CustID (PK), Name, Address, SSN (Unique).
* ACCOUNT: Attributes: AccNo (PK), Balance, AccountType.
* BRANCH: Attributes: BranchID (PK), Name, City, Assets.
* TRANSACTION: A Weak Entity depending on an Account. Attributes: TransID (Partial Key), Timestamp, Amount, Type (Credit/Debit).

2. Relationships & Cardinality:
* HOLDS_ACCOUNT (M:N): A CUSTOMER can hold multiple ACCOUNTs (e.g., Checking, Savings). A single joint ACCOUNT can be held by multiple CUSTOMERs (e.g., a married couple).
* MAINTAINED_BY (N:1): Many ACCOUNTs are maintained by exactly one BRANCH.
* HAS_TRANSACTION (1:N): One ACCOUNT has many TRANSACTIONs. A TRANSACTION belongs strictly to one ACCOUNT. (Identifying relationship).

3. Design Justification:
Transactions must be modeled as a Weak Entity. A transaction ID like '001' has no meaning globally. It only makes mathematical sense when paired with the specific Account Number it acted upon.

Example:
-- Creating the M:N Joint Account Mapping:
CREATE TABLE Customer_Account (
  CustID INT REFERENCES Customer(CustID),
  AccNo INT REFERENCES Account(AccNo),
  AccessLevel VARCHAR(20), -- e.g., 'Primary', 'Joint'
  PRIMARY KEY (CustID, AccNo)
);

-- Weak Entity for Transactions:
CREATE TABLE TransactionLog (
  AccNo INT REFERENCES Account(AccNo),
  TransID INT,
  Amount DECIMAL(15,2),
  PRIMARY KEY (AccNo, TransID)
);

Question 62

[10 Marks] Draw an ER diagram for a large-scale Library Management System (Books, Authors, Publishers, Members, Issuance).

Accepted Answer

A Library Management ER Diagram must distinguish between the abstract concept of a 'Book Title' and the physical 'Book Copy' sitting on a shelf.

1. Entities & Attributes:
* MEMBER: Attributes: MemberID (PK), Name, ExpiryDate, UnpaidFines.
* BOOK_TITLE: The catalog entry. Attributes: ISBN (PK), Title, Genre.
* BOOK_COPY: The physical asset. A Weak Entity depending on the Title. Attributes: CopyNo (Partial Key), ShelfLocation, Condition.
* AUTHOR: Attributes: AuthorID (PK), Name.
* PUBLISHER: Attributes: PubID (PK), Name, HQ_City.

2. Relationships & Cardinality:
* WRITTEN_BY (M:N): A BOOK_TITLE is written by many AUTHORs. An AUTHOR writes many BOOK_TITLEs.
* PUBLISHED_BY (N:1): Many BOOK_TITLEs are published by exactly one PUBLISHER.
* HAS_COPIES (1:N): One BOOK_TITLE has multiple BOOK_COPY instances. (Identifying Relationship).
* BORROWS (M:N): A MEMBER borrows many BOOK_COPYs over time. A physical BOOK_COPY is borrowed by many MEMBERs over its lifetime. This M:N diamond holds descriptive attributes: IssueDate, DueDate, and ReturnDate.

3. Normalization Note:
If you attach 'DueDate' to the MEMBER, it implies a member only ever has one due date. If you attach it to the BOOK_COPY, it overwrites historical data. Therefore, 'DueDate' must reside on the BORROWS relationship to log historical transactions accurately.

Example:
-- Implementing the crucial BORROWS historical ledger:
CREATE TABLE Borrow_Ledger (
  MemberID INT REFERENCES Member(MemberID),
  ISBN VARCHAR(20),
  CopyNo INT,
  IssueDate DATE,
  DueDate DATE,
  ReturnDate DATE NULL, -- NULL indicates the book is currently out
  PRIMARY KEY (MemberID, ISBN, CopyNo, IssueDate),
  FOREIGN KEY (ISBN, CopyNo) REFERENCES Book_Copy(ISBN, CopyNo)
);

Question 63

[10 Marks] Explain Referential Integrity and its importance. How is it maintained during Delete operations?

Accepted Answer

Referential Integrity is a core relational database concept ensuring that relationships between tables remain mathematically valid and consistent.

Definition & Importance:
* Rule: If Table A contains a Foreign Key pointing to the Primary Key of Table B, every value in Table A's Foreign Key MUST either exist in Table B or be strictly NULL.
* Importance: It prevents 'Orphan Records'. For example, if a customer is deleted, but their 50 previous orders remain in the database pointing to a non-existent CustomerID, the database is mathematically corrupted.

Maintenance during DELETE Operations:
When a user attempts to DELETE a parent record (e.g., a Customer) that has dependent child records (e.g., Orders), the DBMS relies on explicitly defined referential constraint actions:
1. RESTRICT (Default): The DBMS instantly rejects the deletion and throws an error, protecting the child records.
2. CASCADE: The DBMS automatically finds and deletes all child records that pointed to the parent. (e.g., Deleting the Customer silently deletes all 50 of their Orders).
3. SET NULL: The DBMS allows the parent to be deleted, but automatically updates the Foreign Key in the child records to NULL. (e.g., Deleting a Manager sets the 'ManagerID' of their employees to NULL).
4. SET DEFAULT: The DBMS allows the deletion but sets the child records' Foreign Key to a predefined default value.

Example:
-- Implementing Referential Integrity with ON DELETE CASCADE
CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustID) ON DELETE CASCADE
);

Question 64

[10 Marks] Worker Schema SQL: Given Worker, Title, and Bonus tables, write SQL to find unique departments, managers, and the second-highest salary.

Accepted Answer

Assuming the schema:
WORKER(Worker_ID, First_Name, Salary, Department)
TITLE(Worker_Ref_ID, Worker_Title, Affected_From)
BONUS(Worker_Ref_ID, Bonus_Amount, Bonus_Date)

This question tests intermediate SQL skills, particularly DISTINCT, Subqueries, and JOINs.

1. Find Unique Departments:
To eliminate duplicates, we use the `DISTINCT` keyword. It forces the DBMS to return only unique values from the Department column.

2. List All Managers:
We must JOIN the WORKER table with the TITLE table based on the Worker_ID and filter where the Title is 'Manager'.

3. Find the Second Highest Salary:
This is a classic interview and exam question. There are two main ways to solve it:
* Method A (Subquery with MAX): First, find the absolute maximum salary. Then, find the maximum salary that is strictly less than the absolute maximum.
* Method B (LIMIT/OFFSET or TOP): Sort the salaries in descending order, skip the first one, and return the second. (Note: Syntax varies by DBMS; MySQL/PostgreSQL use `LIMIT 1 OFFSET 1`).

Example:
-- 1. Unique Departments:
SELECT DISTINCT Department FROM WORKER;

-- 2. List Managers:
SELECT W.First_Name, W.Department 
FROM WORKER W 
JOIN TITLE T ON W.Worker_ID = T.Worker_Ref_ID 
WHERE T.Worker_Title = 'Manager';

-- 3. Second Highest Salary (Subquery Method - Standard SQL):
SELECT MAX(Salary) AS SecondHighestSalary 
FROM WORKER 
WHERE Salary < (SELECT MAX(Salary) FROM WORKER);