Database Management Systems

Table of Contents

1. ER Modeling
2. Relational Model Basics
3. Normalization
4. Functional Dependencies
5. Relational Algebra
6. SQL
7. Transaction Management
8. Concurrency Control
9. Deadlock
10. Recovery Techniques
11. File Organization & Indexing
12. Query Optimization

ER Modeling

Key Components

Entity

• Strong Entity: Can exist independently (has its own primary key) — e.g., Employee
• Weak Entity: Depends on another entity for identification (has partial key/discriminator) — e.g., Dependent (depends on Employee)

Attributes

Relationships

• Degree: Number of entity sets participating
• Unary (1): Single entity (e.g., Employee manages Employee)
• Binary (2): Two entities (most common) — e.g., Employee works in Department

• Ternary (3): Three entities — e.g., Supplier supplies Part to Project

• Cardinality Ratio:

• One-to-One (1:1): Each entity associated with at most one other
• One-to-Many (1:N): One entity associated with many others

• Many-to-Many (M:N): Multiple entities associated with multiple others

• Participation Constraints:

• Total: Every entity must participate in the relationship (double line)
• Partial: Some entities may not participate (single line)

ER to Relational Mapping

• Strong Entity → Separate table with primary key
• Weak Entity → Table with foreign key referencing owner + partial key
• 1:1 Relationship → Merge into one table or add foreign key in either table
• 1:N Relationship → Add foreign key in the "many" side table
• M:N Relationship → Create separate relationship table with foreign keys from both entities
• Multi-valued Attribute → Create separate table with foreign key

Relational Model Basics

Key Terminology

• Relation: A table
• Tuple: A row
• Attribute: A column
• Domain: Set of allowed values for an attribute
• Degree: Number of attributes (columns)
• Cardinality: Number of tuples (rows)
• Schema: Structure/definition of a relation
• Instance: Actual data at a point in time

Keys

Integrity Constraints

• Entity Integrity: Primary key cannot be NULL
• Referential Integrity: Foreign key must either be NULL or reference an existing primary key
• Domain Constraint: Values must belong to the attribute's domain
• Key Constraint: Primary key must be unique

Normalization

Normalization is the process of decomposing relations to eliminate redundancy and anomalies (insertion, deletion, update anomalies).

Anomalies

• Insertion Anomaly: Cannot insert data without other data (e.g., can't add a department without an employee)
• Deletion Anomaly: Deleting data unintentionally loses other data (e.g., deleting last employee loses department info)
• Update Anomaly: Redundant data requires multiple updates (e.g., changing department name in many rows)

First Normal Form (1NF)

Rule: All attributes must contain atomic (indivisible) values; no repeating groups or arrays.

Example — Unnormalized:
| Student_ID | Name | Subjects |
|-----------|------|----------|
| 1 | Alice | Math, Science |
| 2 | Bob | English |

1NF Compliant:
| Student_ID | Name | Subject |
|-----------|------|---------|
| 1 | Alice | Math |
| 1 | Alice | Science |
| 2 | Bob | English |

Second Normal Form (2NF)

Rule: Must be in 1NF + No partial dependency (non-prime attribute must depend on the ENTIRE candidate key, not just part of it).

Applies only when there is a composite key.

Example — Not in 2NF:
| Student_ID | Subject_ID | Student_Name | Subject_Marks |
|-----------|-----------|-------------|--------------|
| 1 | S1 | Alice | 85 |
| 2 | S2 | Bob | 90 |

• Candidate Key: {Student_ID, Subject_ID}
• Student_Name depends only on Student_ID (partial dependency) ❌

2NF Compliant:
| Student_ID | Student_Name |
|-----------|-------------|
| 1 | Alice |
| 2 | Bob |

Third Normal Form (3NF)

Rule: Must be in 2NF + No transitive dependency (non-prime attribute should not depend on another non-prime attribute).

Example — Not in 3NF:
| Emp_ID | Emp_Name | Dept_ID | Dept_Name |
|--------|---------|---------|-----------|
| 1 | Alice | D1 | HR |
| 2 | Bob | D2 | Finance |

• Emp_ID → Dept_ID → Dept_Name (transitive dependency) ❌

3NF Compliant:
| Emp_ID | Emp_Name | Dept_ID |
|--------|---------|---------|
| 1 | Alice | D1 |
| 2 | Bob | D2 |

Boyce-Codd Normal Form (BCNF)

Rule: Must be in 3NF + For every non-trivial FD (X → Y), X must be a super key.

BCNF is stricter than 3NF. Every BCNF relation is in 3NF, but not vice versa.

Example — In 3NF but NOT BCNF:
| Student | Subject | Professor |
|---------|---------|-----------|
| Alice | Math | Dr. Smith |
| Bob | Math | Dr. Smith |
| Alice | Science | Dr. Jones |

• Candidate Key: {Student, Subject}
• FD: Professor → Subject (Professor determines Subject)
• Professor is NOT a super key → violates BCNF ❌

BCNF Compliant:
| Professor | Subject |
|-----------|---------|
| Dr. Smith | Math |
| Dr. Jones | Science |

Normalization Summary

Functional Dependencies

A functional dependency (FD) X → Y means: for any two tuples, if they agree on X, they must agree on Y.

Types of Functional Dependencies

• Trivial FD: Y ⊆ X (e.g., {A,B} → {A}) — always holds
• Non-trivial FD: Y ⊄ X (e.g., {A} → {B})
• Completely non-trivial: Y ∩ X = ∅

Armstrong's Axioms

Closure of Attribute Set (X⁺)

The set of all attributes functionally determined by X.

Algorithm to find X⁺:
1. Start with result = X
2. For each FD in F: if LHS ⊆ result, add RHS to result
3. Repeat until no more attributes can be added

Example:
- R = {A, B, C, D, E}
- FDs: A → BC, CD → E, B → D, E → A
- Find {A}⁺:
- Start: {A}
- A → BC: {A, B, C}
- B → D: {A, B, C, D}
- CD → E: {A, B, C, D, E}
- {A}⁺ = {A, B, C, D, E} → A is a candidate key!

Canonical Cover (Minimal Cover)

A canonical cover Fc is a minimal set of FDs equivalent to the original set F, with:
1. No extraneous attributes on either side
2. No redundant FDs
3. Each FD has a single attribute on RHS

Steps to find Canonical Cover:
1. Decompose all FDs to have single attribute on RHS
2. Remove extraneous attributes from LHS (check if removing an attribute still gives same closure)
3. Remove redundant FDs (check if an FD can be derived from remaining FDs)

Example:
- F = {A → BC, B → C, A → B, AB → C}
- Step 1: {A → B, A → C, B → C, AB → C}
- Step 2: Check AB → C: Is A⁺ (using remaining FDs) containing C? A → C, so yes. Remove B from LHS → A → C (already exists)
- Step 3: Check A → C: Can we derive it from {A → B, B → C}? Yes (transitivity). Remove A → C.
- Fc = {A → B, B → C}

Relational Algebra

Relational algebra is a procedural query language that takes relations as input and produces relations as output.

Basic Operations

Selection (σ)

Selects rows (tuples) that satisfy a condition.

σ_condition(R)

Example: σ_salary>50000(Employee) — selects employees with salary > 50000

Projection (π)

Selects specific columns (attributes).

π_attribute_list(R)

Example: π_name, salary(Employee) — shows only name and salary columns

Union (∪)

Combines tuples from two relations (must be union-compatible — same attributes).

R ∪ S

Eliminates duplicates.

Set Difference (−)

Tuples in R but not in S.

R − S

Cartesian Product (×)

Combines every tuple of R with every tuple of S.

R × S

If R has m tuples and S has n tuples, result has m × n tuples.

Rename (ρ)

Renames a relation or attributes.

ρ_new_name(R)  or  ρ_new_name(attr1, attr2,...)(R)

Derived Operations

Intersection (∩)

Tuples common to both R and S.

R ∩ S = R − (R − S)

Join Operations

Theta Join (⋈_θ)

R ⋈_θ S = σ_θ(R × S)

Joins based on any condition θ.

Equi Join
Special case of theta join where condition uses only =.

R ⋈_(R.A=S.B) S

Natural Join (⋈)
- Equi join on all common attribute names
- Duplicate columns eliminated automatically
- Most commonly used join

Outer Joins:
- Left Outer Join (⟕): All tuples from R + matching from S; NULL for non-matching S attributes
- Right Outer Join (⟖): All tuples from S + matching from R; NULL for non-matching R attributes
- Full Outer Join (⟗): All tuples from both; NULL where no match

Division (÷)

R ÷ S

Returns tuples in R that are associated with every tuple in S.

Example: R(Student, Course) ÷ S(Course) → Students enrolled in ALL courses listed in S.

Condition: If R has attributes (A, B) and S has attribute (B), then R ÷ S gives all A values that appear with every B value in S.

Relational Algebra Quick Reference

SQL

SQL Command Categories

DDL Commands

CREATE TABLE Employee (
    Emp_ID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Salary DECIMAL(10,2) DEFAULT 0,
    Dept_ID INT,
    FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID)
);

ALTER TABLE Employee ADD Email VARCHAR(100);
ALTER TABLE Employee MODIFY Salary DECIMAL(12,2);
ALTER TABLE Employee DROP COLUMN Email;

DROP TABLE Employee;        -- Removes table structure + data
TRUNCATE TABLE Employee;    -- Removes all data, keeps structure (faster, cannot rollback)

DML Commands

-- INSERT
INSERT INTO Employee (Emp_ID, Name, Salary) VALUES (1, 'Alice', 50000);

-- UPDATE
UPDATE Employee SET Salary = 55000 WHERE Emp_ID = 1;

-- DELETE
DELETE FROM Employee WHERE Emp_ID = 1;

-- SELECT (with clauses)
SELECT Name, Salary FROM Employee
WHERE Salary > 40000
ORDER BY Salary DESC;

SQL Joins

-- INNER JOIN: Only matching rows
SELECT E.Name, D.Dept_Name
FROM Employee E INNER JOIN Department D ON E.Dept_ID = D.Dept_ID;

-- LEFT JOIN: All from left + matching from right
SELECT E.Name, D.Dept_Name
FROM Employee E LEFT JOIN Department D ON E.Dept_ID = D.Dept_ID;

-- RIGHT JOIN: All from right + matching from left
SELECT E.Name, D.Dept_Name
FROM Employee E RIGHT JOIN Department D ON E.Dept_ID = D.Dept_ID;

-- FULL OUTER JOIN: All from both
SELECT E.Name, D.Dept_Name
FROM Employee E FULL OUTER JOIN Department D ON E.Dept_ID = D.Dept_ID;

-- CROSS JOIN: Cartesian product
SELECT E.Name, D.Dept_Name
FROM Employee E CROSS JOIN Department D;

-- SELF JOIN: Join table with itself
SELECT A.Name AS Employee, B.Name AS Manager
FROM Employee A, Employee B
WHERE A.Manager_ID = B.Emp_ID;

Subqueries

-- Single-row subquery
SELECT Name FROM Employee
WHERE Salary = (SELECT MAX(Salary) FROM Employee);

-- Multi-row subquery (IN, ANY, ALL)
SELECT Name FROM Employee
WHERE Dept_ID IN (SELECT Dept_ID FROM Department WHERE Location = 'Mumbai');

-- Correlated subquery (executes once per outer row)
SELECT E1.Name FROM Employee E1
WHERE E1.Salary > (SELECT AVG(E2.Salary) FROM Employee E2 WHERE E2.Dept_ID = E1.Dept_ID);

Views

CREATE VIEW HighSalaryEmp AS
SELECT Name, Salary FROM Employee WHERE Salary > 50000;

-- Views are virtual tables (don't store data)
-- Can be used for security (restrict column/row access)
-- WITH CHECK OPTION prevents inserting rows that don't satisfy view condition

Indexes

CREATE INDEX idx_salary ON Employee(Salary);
CREATE UNIQUE INDEX idx_email ON Employee(Email);
CREATE INDEX idx_name_dept ON Employee(Name, Dept_ID);  -- Composite index

-- Clustered Index: Physically reorders table data (only one per table)
-- Non-clustered Index: Separate structure pointing to data (multiple allowed)

Aggregate Functions

SELECT Dept_ID, AVG(Salary), COUNT(*)
FROM Employee
GROUP BY Dept_ID
HAVING AVG(Salary) > 50000;

SQL Execution Order

FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

Transaction Management

A transaction is a logical unit of work that must be executed completely or not at all.

ACID Properties

Transaction States

1. Active: Transaction is executing
2. Partially Committed: After final statement executed
3. Committed: After successful completion (changes permanent)
4. Failed: Cannot proceed normally
5. Aborted: Rolled back, database restored to prior state
6. Terminated: Either committed or aborted

Serializability

• Serial Schedule: Transactions execute one after another (no interleaving)
• Serializable Schedule: Concurrent schedule that produces same result as some serial schedule

Conflict Serializability

Two operations conflict if:
1. They belong to different transactions
2. They access the same data item
3. At least one is a write operation

Conflict Equivalent: Can transform one schedule into another by swapping non-conflicting operations.

Precedence Graph (Conflict Graph):
- Nodes = Transactions
- Edge Ti → Tj if Ti has an operation that conflicts with and precedes an operation of Tj
- Schedule is conflict serializable iff the graph is acyclic.

View Serializability

More general than conflict serializability. Two schedules are view equivalent if:
1. Same initial reads
2. Same read-after-write dependencies
3. Same final writes

Concurrency Control

Problems with Concurrent Execution

Locking Protocols

Types of Locks

• Shared Lock (S-lock / Read Lock): Multiple transactions can hold simultaneously
• Exclusive Lock (X-lock / Write Lock): Only one transaction can hold; blocks all others
• Compatibility Matrix:

Two-Phase Locking (2PL)

A transaction follows 2PL if it has two phases:
1. Growing Phase: Can acquire locks, cannot release any
2. Shrinking Phase: Can release locks, cannot acquire any

Variants:
- Basic 2PL: Ensures conflict serializability but may cause cascading rollbacks
- Strict 2PL: All exclusive locks held until commit/abort — prevents cascading rollbacks
- Rigorous 2PL: All locks held until commit/abort — simplest to implement

Timestamp Ordering Protocol

• Each transaction assigned a unique timestamp TS(T)
• For each data item X: Read-Timestamp R-ts(X) and Write-Timestamp W-ts(X)
• Rules:
• If T wants to READ(X): if TS(T) < W-ts(X) → reject (T is too old); else allow and update R-ts(X)
• If T wants to WRITE(X): if TS(T) < R-ts(X) → reject; if TS(T) < W-ts(X) → ignore (Thomas' Write Rule); else allow and update W-ts(X)

Multiple Granularity Locking

• Locks at different levels: Database → Table → Page → Row
• Intention Locks: IS (intention shared), IX (intention exclusive), SIX (shared + intention exclusive)
• Reduces locking overhead for large operations

Deadlock

Deadlock Conditions (All must hold simultaneously)

1. Mutual Exclusion: Only one transaction can hold a resource at a time
2. Hold and Wait: Transaction holds one resource while waiting for another
3. No Preemption: Resources cannot be forcibly taken
4. Circular Wait: T1 waits for T2, T2 waits for T3, ..., Tn waits for T1

Deadlock Handling Strategies

Prevention

• Eliminate Hold and Wait: Request all resources at once (conservative 2PL)
• Eliminate Circular Wait: Impose total ordering on resource types; request in order
• Eliminate No Preemption: If request denied, release all held resources

Detection

• Wait-for Graph: Directed graph where edge Ti → Tj means Ti is waiting for Tj
• Cycle in wait-for graph = Deadlock
• Detection frequency: periodic or when wait time exceeds threshold

Recovery from Deadlock

• Victim Selection: Choose transaction to abort (criteria: least work, fewest locks, youngest)
• Total Rollback: Abort and restart the transaction
• Partial Rollback: Rollback to break the cycle

Deadlock Avoidance (Timeout-based)

• If a transaction waits too long, it is aborted
• Simple but may abort non-deadlocked transactions

Recovery Techniques

Log-Based Recovery

A log records all changes made by transactions.

Log Records

Write-Ahead Logging (WAL)

Rule: Log record must be written to stable storage before the actual data modification.

Undo and Redo

• Undo: Restore old value (for uncommitted transactions)
• Redo: Apply new value (for committed transactions)

Checkpointing

A checkpoint pauses all transactions, flushes dirty pages, and writes a checkpoint record to log.

Types:
- Simple Checkpoint: Pause all transactions, flush, record checkpoint
- Fuzzy Checkpoint: Don't pause transactions; record active transaction list

Recovery Process:
1. Start from last checkpoint
2. Redo Phase: Redo all committed transactions after checkpoint
3. Undo Phase: Undo all uncommitted transactions

ARIES Recovery Algorithm

• Analysis: Determine which transactions were active at crash
• Redo: Reapply all changes from checkpoint onward
• Undo: Rollback incomplete transactions
• Uses LSN (Log Sequence Number) for efficient recovery

File Organization & Indexing

File Organization Methods

Indexing

Primary Index

• Index on the ordering key field (the field by which file is sorted)
• One index entry per block (sparse) or per record (dense)

Secondary Index

• Index on a non-ordering field
• Always dense (one entry per record)

Clustering Index

• Index on a non-key ordering field
• File sorted by a non-key field; index on that field

B+ Tree Indexing

Structure:
- Root: Contains keys and pointers to internal nodes
- Internal Nodes: Contain keys and pointers to child nodes
- Leaf Nodes: Contain keys and data pointers; linked together (for range queries)

Properties of B+ Tree of order m:
- Each internal node has at most m children
- Each internal node (except root) has at least ⌈m/2⌉ children
- Root has at least 2 children (if not a leaf)
- All leaves at same level
- Leaf nodes linked in a linked list

Operations:
- Search: O(log_m N) — traverse from root to leaf
- Insert: Find leaf, insert; if overflow, split node and propagate up
- Delete: Find and delete; if underflow, merge or redistribute

Advantages:
- Balanced tree — guaranteed O(log n) operations
- Efficient for range queries (linked leaf nodes)
- Minimizes disk I/O (high fanout = shallow tree)

B Tree vs B+ Tree:
| Feature | B Tree | B+ Tree |
|---------|--------|---------|
| Data in internal nodes | Yes | No (only in leaves) |
| Leaf linked list | No | Yes |
| Range queries | Less efficient | Very efficient |
| Space utilization | Lower | Higher |

Query Optimization

Query Processing Steps

1. Parsing and Translation: Parse SQL → relational algebra expression
2. Optimization: Generate efficient execution plan
3. Evaluation: Execute the plan

Optimization Strategies

Algebraic Optimization

• Apply heuristic rules to transform query tree:
• Push selections down (perform selection as early as possible)
• Push projections down (eliminate unnecessary attributes early)
• Combine selections and Cartesian products into joins
• Identify common subexpressions

Cost-Based Optimization

• Estimate cost of different execution plans
• Cost factors: disk I/O, CPU time, memory, network
• Uses statistics (table cardinality, index availability, data distribution)

Join Optimization

• Nested Loop Join: For each tuple in R, scan all of S — O(|R| × |S|)
• Block Nested Loop: Process in blocks — reduces I/O
• Index Nested Loop: Use index on S — O(|R| × log|S|)
• Sort-Merge Join: Sort both, then merge — O(|R|log|R| + |S|log|S|)
• Hash Join: Hash both on join key — O(|R| + |S|) average

Index Usage

• Use indexes for selection and join operations
• Avoid indexes on low-cardinality columns
• Covering index: Index contains all columns needed by query (no table access needed)

Query Execution Plan

EXPLAIN SELECT * FROM Employee WHERE Salary > 50000;

Shows the execution plan chosen by the optimizer (index scan, full table scan, join method, etc.)

Key Formulas and Points Summary

Exam Tips

1. Normalization: Practice identifying normal forms with examples — most frequently tested
2. Functional Dependencies: Be able to compute closure and canonical cover
3. Relational Algebra: Know the symbols and be able to write expressions for queries
4. SQL Joins: Understand the difference between all join types with examples
5. ACID Properties: Know each property and the mechanism that ensures it
6. 2PL: Understand growing/shrinking phases and strict vs rigorous variants
7. Deadlock: Know all 4 conditions and prevention/detection methods
8. B+ Tree: Understand structure, properties, and why it's preferred for databases
9. Serializability: Be able to check conflict serializability using precedence graph
10. Recovery: Understand WAL, checkpointing, and undo/redo

Practice Questions

10 MCQs for Database Management Systems with detailed explanations.

Q1. Which of the following best describes - If T wants to READ(X): if TS(T) < W-ts(X) → reject (T?

• A. a candidate key!
• B. too old); else allow and update R-ts(X)
• C. executing
• D. sorted)

✅ Correct Answer: Option B

Explanation:
The correct answer is Option B — too old); else allow and update R-ts(X).

This concept is covered under Database Management Systems in the CBDT Assistant Director Systems syllabus. The answer is established through standard definitions and widely accepted principles in the field.

Why other options are incorrect:
- Option A — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option C — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option D — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.

Q2. Which of the following best describes - {A}⁺ = {A, B, C, D, E} → A?

• A. a candidate key!
• B. too old); else allow and update R-ts(X)
• C. executing
• D. sorted)

✅ Correct Answer: Option A

Explanation:
The correct answer is Option A — a candidate key!.

This concept is covered under Database Management Systems in the CBDT Assistant Director Systems syllabus. The answer is established through standard definitions and widely accepted principles in the field.

Why other options are incorrect:
- Option B — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option C — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option D — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.

Q3. Regarding the following concept: 'Three entities — e.g., Supplier supplies Part to Project

-...', which statement is correct?

• A. This is defined exclusively at the physical layer of system design
• B. Three entities — e.g., Supplier supplies Part to Project

-
- C. This approach has been deprecated in all modern implementations
- D. This concept applies only to analog systems and not digital ones

✅ Correct Answer: Option B

Explanation:
The correct answer is Option B — Three entities — e.g., Supplier supplies Part to Project

-.

This concept is covered under Database Management Systems in the CBDT Assistant Director Systems syllabus. The answer is established through standard definitions and widely accepted principles in the field.

Why other options are incorrect:
- Option A — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option C — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option D — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.

Q4. Which of the following best describes 3. At least one?

• A. too old); else allow and update R-ts(X)
• B. a candidate key!
• C. executing
• D. a write operation

✅ Correct Answer: Option D

Explanation:
The correct answer is Option D — a write operation.

This concept is covered under Database Management Systems in the CBDT Assistant Director Systems syllabus. The answer is established through standard definitions and widely accepted principles in the field.

Why other options are incorrect:
- Option A — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option B — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option C — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.

Q5. Which of the following best describes - Wait-for Graph: Directed graph where edge Ti → Tj?

• A. too old); else allow and update R-ts(X)
• B. Ti is waiting for Tj
• C. executing
• D. a candidate key!

✅ Correct Answer: Option B

Explanation:
The correct answer is Option B — Ti is waiting for Tj.

This concept is covered under Database Management Systems in the CBDT Assistant Director Systems syllabus. The answer is established through standard definitions and widely accepted principles in the field.

Why other options are incorrect:
- Option A — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option C — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option D — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.

Q6. Regarding the following concept: 'Two entities (most common) — e.g., Employee works in Department

-...', which statement is correct?

• A. This is defined exclusively at the physical layer of system design
• B. This concept applies only to analog systems and not digital ones
• C. Two entities (most common) — e.g., Employee works in Department
  -
• D. This approach has been deprecated in all modern implementations

✅ Correct Answer: Option C

Explanation:
The correct answer is Option C — Two entities (most common) — e.g., Employee works in Department
-.

This concept is covered under Database Management Systems in the CBDT Assistant Director Systems syllabus. The answer is established through standard definitions and widely accepted principles in the field.

Why other options are incorrect:
- Option A — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option B — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option D — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.

Q7. Which of the following best describes *Applies only when there?

• A. a candidate key!
• B. too old); else allow and update R-ts(X)
• C. a composite key.*
• D. executing

✅ Correct Answer: Option C

Explanation:
The correct answer is Option C — a composite key.*.

This concept is covered under Database Management Systems in the CBDT Assistant Director Systems syllabus. The answer is established through standard definitions and widely accepted principles in the field.

Why other options are incorrect:
- Option A — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option B — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option D — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.

Q8. Regarding the following concept: 'Single entity (e.g., Employee manages Employee)

-...', which statement is correct?

• A. Single entity (e.g., Employee manages Employee)
  -
• B. This concept applies only to analog systems and not digital ones
• C. This is defined exclusively at the physical layer of system design
• D. This approach has been deprecated in all modern implementations

✅ Correct Answer: Option A

Explanation:
The correct answer is Option A — Single entity (e.g., Employee manages Employee)
-.

This concept is covered under Database Management Systems in the CBDT Assistant Director Systems syllabus. The answer is established through standard definitions and widely accepted principles in the field.

Why other options are incorrect:
- Option B — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option C — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option D — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.

Q9. Regarding the following concept: 'Every entity must participate in the relationship (double line)

-...', which statement is correct?

• A. This approach has been deprecated in all modern implementations
• B. This concept applies only to analog systems and not digital ones
• C. This is defined exclusively at the physical layer of system design
• D. Every entity must participate in the relationship (double line)
  -

✅ Correct Answer: Option D

Explanation:
The correct answer is Option D — Every entity must participate in the relationship (double line)
-.

This concept is covered under Database Management Systems in the CBDT Assistant Director Systems syllabus. The answer is established through standard definitions and widely accepted principles in the field.

Why other options are incorrect:
- Option A — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option B — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option C — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.

Q10. Regarding the following concept: '- Covering index: Index contains all columns needed by query (no table access ne...', which statement is correct?

• A. This concept applies only to analog systems and not digital ones
• B. This is defined exclusively at the physical layer of system design
• C. This approach has been deprecated in all modern implementations
• D. - Covering index: Index contains all columns needed by query (no table access needed)

✅ Correct Answer: Option D

Explanation:
The correct answer is Option D — - Covering index: Index contains all columns needed by query (no table access needed).

This concept is covered under Database Management Systems in the CBDT Assistant Director Systems syllabus. The answer is established through standard definitions and widely accepted principles in the field.

Why other options are incorrect:
- Option A — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option B — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.
- Option C — This option is factually incorrect or describes a concept from a different domain, making it an invalid choice for this question.