Top 50 - Part 3

1. What is a Default Constraint?

A DEFAULT constraint automatically provides a value when an INSERT statement does not specify a value for that column.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'active'
);

If we insert:

INSERT INTO employees (id)
VALUES (101);

The database automatically stores:

+-----+--------+
| id  | status |
+-----+--------+
| 101 | active |
+-----+--------+

Remember:

INSERT provides value?  → Use that value
        │
        └── No value? → Use DEFAULT

A DEFAULT constraint does not mean the column cannot be NULL. It only provides a value when the column is omitted from the INSERT (subject to the DBMS’s specific behavior).

---

2. What is a Candidate Key?

A Candidate Key is a minimal set of attributes that uniquely identifies each row in a relation.

It has two important properties:

Candidate Key
     │
     ├── Uniqueness
     │     └── No two rows have the same key value
     │
     └── Minimality
           └── Remove any attribute → uniqueness is lost

Example:

STUDENT
+------------+-------------------+-------+
| Student_ID | Email             | Name  |
+------------+-------------------+-------+
| 101        | a@example.com     | Ali   |
| 102        | b@example.com     | Sara  |
+------------+-------------------+-------+

If both Student_ID and Email are unique:

Candidate Keys:
    {Student_ID}
    {Email}

You choose one as the Primary Key.

Candidate Keys
      │
      ├── Primary Key      ← chosen one
      │
      └── Alternate Keys   ← remaining ones

---

3. What is an Alternate Key?

An Alternate Key is a candidate key that was not selected as the primary key.

Example:

Candidate Keys:
    ├── Student_ID
    └── Email

Choose Student_ID as Primary Key
            ↓
    +----------------+
    | Student_ID     | ← Primary Key
    | Email          | ← Alternate Key
    +----------------+

So:

Candidate Keys
      │
      ├── Primary Key
      │
      └── Alternate Key(s)

The alternate key still has the properties of a candidate key: it uniquely identifies rows and is minimal.

---

4. What is a Super Key?

A Super Key is any set of attributes that uniquely identifies a row.

Unlike a candidate key, a super key can contain unnecessary attributes.

Suppose:

EMPLOYEE(EmpID, Email, Name)

If EmpID uniquely identifies an employee:

Super Keys:
    {EmpID}
    {EmpID, Name}
    {EmpID, Email}
    {EmpID, Email, Name}

But:

{EmpID}

is minimal, so it is a Candidate Key.

Think of the relationship as:

             SUPER KEYS
          /      |       \
       {A}    {A,B}    {A,C}
        │
        └── Minimal one
              ↓
        CANDIDATE KEY

Golden rule:

Candidate Key = Minimal Super Key

Therefore:

Every Candidate Key → Super Key
Every Super Key    → Not necessarily Candidate Key

---

5. What is Referential Integrity?

Referential integrity ensures that relationships between tables remain valid.

A foreign key normally must either:

1. Match an existing referenced key value, or
2. Be NULL if the column allows NULL.

Example:

CUSTOMERS
+-----+-------+
| ID  | Name  |
+-----+-------+
| 101 | Ali   |
| 102 | Sara  |
+-----+-------+
          ↑
          │
          │ FOREIGN KEY
          │
ORDERS    │
+--------+------+
| OrderID| CustID|
+--------+------+
| 1      | 101  | ✓
| 2      | 102  | ✓
| 3      | 555  | ✗
+--------+------+

CustID = 555 is invalid because customer 555 does not exist.

Foreign Key
     │
     └── Must point to a valid referenced key

This prevents orphan records.

What happens when the referenced row is deleted depends on the foreign-key action:

ON DELETE
   ├── RESTRICT / NO ACTION
   ├── CASCADE
   ├── SET NULL
   └── SET DEFAULT

---

6. What distinguishes BCNF from 3NF?

BCNF (Boyce-Codd Normal Form) is stricter than 3NF.

For every functional dependency:

X → Y

3NF

A relation satisfies 3NF if, for every non-trivial functional dependency X → Y:

X is a Super Key
        OR
Y is a Prime Attribute

A prime attribute is an attribute that belongs to at least one candidate key.

BCNF

BCNF is stricter:

For every non-trivial X → Y:

        X MUST be a Super Key

No exception.

3NF
 ├── X is Super Key ✓
 └── OR Y is Prime Attribute ✓

BCNF
 └── X MUST be Super Key ✓

Therefore:

BCNF ⊂ 3NF

Every BCNF relation is in 3NF, but a 3NF relation is not necessarily in BCNF.

Important correction: the example must satisfy the dependencies carefully. A common textbook example is:

R(Student, Course, Instructor)

Dependencies:
(Student, Course) → Instructor
Instructor → Course

Candidate keys can be:

(Student, Course)
(Student, Instructor)

The dependency:

Instructor → Course

violates BCNF because Instructor is not a super key.

It can still satisfy 3NF because Course is a prime attribute.

Memory trick:

3NF  → Super Key OR Prime
BCNF → Super Key ONLY

---

7. What is the requirement for a relation to be in 1NF?

A relation is in First Normal Form (1NF) when each attribute contains atomic/single values and there are no repeating groups.

❌ Not 1NF:

+---------+------------------------+
| OrderID | Products               |
+---------+------------------------+
| 1       | Phone, Laptop, Tablet  |
| 2       | Keyboard               |
+---------+------------------------+

Products contains multiple values.

Better:

+---------+----------+
| OrderID | Product  |
+---------+----------+
| 1       | Phone    |
| 1       | Laptop   |
| 1       | Tablet   |
| 2       | Keyboard |
+---------+----------+

Another common violation:

❌ Phone1 | Phone2 | Phone3

Instead:

✓ OrderID | Phone

Memory trick:

1NF = One value per cell

---

8. Which Relational Algebra operation returns tuples present in the first relation but absent in the second?

The operation is Set Difference, represented as:

A − B

Example:

A = {1, 2, 3}
B = {2, 3, 4}

A − B = {1}

Visual:

A:  [1] [2] [3]
B:       [2] [3] [4]
     └───────────┘
       Remove common
       
Result:
     [1]

In SQL:

SELECT city FROM A
EXCEPT
SELECT city FROM B;

Oracle traditionally uses MINUS.

Comparison:

UNION        → A OR B
INTERSECTION → A AND B
A − B        → A but NOT B

For set difference, the two relations must be union-compatible — corresponding columns must have compatible types/domains and the same number of attributes.

---

9. What type of dependency is addressed by 4NF?

Fourth Normal Form (4NF) primarily deals with Multivalued Dependencies (MVDs).

Consider:

Employee
   │
   ├── Skills
   └── Languages

Suppose an employee has:

Skills:
    Java
    Python

Languages:
    English
    Hindi

If skills and languages are independent, storing them together creates:

+-------+--------+----------+
| EmpID | Skill  | Language |
+-------+--------+----------+
| 1     | Java   | English  |
| 1     | Java   | Hindi    |
| 1     | Python | English  |
| 1     | Python | Hindi    |
+-------+--------+----------+

Notice the unnecessary combinations.

Split it:

EMPLOYEE_SKILL
+-------+--------+
| EmpID | Skill  |
+-------+--------+
| 1     | Java   |
| 1     | Python |
+-------+--------+

EMPLOYEE_LANGUAGE
+-------+----------+
| EmpID | Language |
+-------+----------+
| 1     | English  |
| 1     | Hindi    |
+-------+----------+

The normal-form progression is:

1NF → Atomic values
2NF → Partial dependencies
3NF → Transitive dependencies
BCNF → Stronger FD condition
4NF → Multivalued dependencies
5NF → Join dependencies

---

10. What are the four core categories of NoSQL databases?

The four common NoSQL models are:

                 NoSQL
                   │
       ┌───────────┼───────────┐
       │           │           │
   Document    Key-Value   Wide-Column
       │
       └──────────────┐
                      │
                    Graph

1. Document

Stores data as documents, often JSON/BSON-like.

{
  "id": 101,
  "name": "Ali",
  "skills": ["Java", "SQL"]
}

Example: MongoDB

2. Key-Value

Key       → Value
user:101  → "Ali"

Example: Redis

3. Wide-Column / Column-Family

Data is organized around column families and can have flexible columns.

Examples: Cassandra, HBase

4. Graph

Stores nodes and relationships/edges.

(Ali) ──FRIEND_OF──> (Sara)
  │
  └──WORKS_AT──────> (Company)

Example: Neo4j

---

11. What is the purpose of the Three-Schema Architecture?

The Three-Schema Architecture separates a database into three levels:

             USERS / APPLICATIONS
                     │
                     ↓
          ┌─────────────────────┐
          │   EXTERNAL LEVEL    │
          │    (View Schema)    │
          └─────────────────────┘
                     │
                     ↓
          ┌─────────────────────┐
          │  CONCEPTUAL LEVEL   │
          │  (Logical Schema)   │
          └─────────────────────┘
                     │
                     ↓
          ┌─────────────────────┐
          │    INTERNAL LEVEL   │
          │   (Physical Schema) │
          └─────────────────────┘
                     │
                     ↓
                  STORAGE

External Level

What individual users/applications see.

Cashier → Orders View
Manager → Sales View
HR      → Employee View

Conceptual Level

The complete logical database:

Tables
Relationships
Constraints
Columns

Internal Level

How data is physically stored:

Files
Pages
Indexes
Blocks
Storage structures

The main benefit is data independence.

Physical change
      ↓
Logical schema unchanged
      ↓
Applications continue working

This is physical data independence.

---

12. How does a DBMS typically handle or resolve a Deadlock?

A DBMS can detect a deadlock using a wait-for graph.

Example:

T1 holds Lock A
T2 holds Lock B

T1 wants B ─────────→ waits for T2
T2 wants A ─────────→ waits for T1

Graph:

       waits
   T1 ───────→ T2
   ↑           │
   │           │
   └───────────┘
       waits

      CYCLE!
       ↓
   DEADLOCK

The DBMS typically:

1. Detect deadlock
       ↓
2. Choose a victim transaction
       ↓
3. Roll back victim
       ↓
4. Release its locks
       ↓
5. Other transaction continues

The victim may be selected based on factors such as rollback cost, amount of work performed, or transaction priority.

Another strategy is lock timeout, where a transaction is aborted if it waits too long.

---

13. What are Database Transactions?

A transaction is a logical unit of work consisting of one or more database operations that should be treated as a single unit.

Example:

BEGIN;

UPDATE accounts
SET balance = balance - 500
WHERE id = 1;

UPDATE accounts
SET balance = balance + 500
WHERE id = 2;

COMMIT;

Conceptually:

        TRANSACTION
             │
      ┌──────┴──────┐
      ↓             ↓
   Debit A       Credit B
      │             │
      └──────┬──────┘
             ↓
          COMMIT

If something goes wrong:

Debit A ✓
Credit B ✗
   ↓
ROLLBACK
   ↓
Debit A undone

Transactions are designed around the ACID properties:

A → Atomicity
C → Consistency
I → Isolation
D → Durability

---

14. What are Database Locks?

A lock is a concurrency-control mechanism that regulates how multiple transactions access the same data.

The most common lock types include:

Shared Lock (S)

Used for reading.

T1 ── READ ──→ Row X
T2 ── READ ──→ Row X

Multiple transactions can generally hold shared locks simultaneously.

S + S = ✓

But a shared lock conflicts with an exclusive lock:

S + X = ✗

Exclusive Lock (X)

Used for modifications.

T1 ── UPDATE ──→ Row X

Other conflicting readers/writers must wait.

X + S = ✗
X + X = ✗

Update Lock (U)

Some DBMSs, notably SQL Server, use update locks to reduce certain conversion/deadlock problems when a transaction intends to update data after reading it.

Intent Locks

Intent locks communicate that a transaction holds or intends to hold lower-level locks.

For example:

Database
   ↓
Table
   ↓
Page
   ↓
Row

An intent lock helps the DBMS efficiently determine whether a higher-level lock conflicts with locks lower in the hierarchy.

Overall idea:

          Concurrent Transactions
                    │
                    ↓
                 LOCKS
                    │
          ┌─────────┴─────────┐
          ↓                   ↓
       Protect             Coordinate
        data                access
                              │
                              ↓
                     Prevent conflicts

Locks provide isolation, but excessive locking can cause:

Blocking
   ↓
Long waits
   ↓
Possible deadlock

---

15. What is the difference between Total Participation and Partial Participation in an ER Diagram?

Participation tells us whether every entity instance must participate in a relationship.

Total Participation

Every entity must participate.

Represented by a double line in the traditional ER notation.

EMPLOYEE ═════════ WORKS_FOR ─────── DEPARTMENT
         ↑
     double line

Example:

Every employee must belong to a department.

Therefore:

Employee
   │
   ├── Employee 1 → Department ✓
   ├── Employee 2 → Department ✓
   └── Employee 3 → Department ✓

No employee can exist without participating in the relationship.

Partial Participation

Only some entities participate.

Represented by a single line.

EMPLOYEE ───────── MANAGES ───────── PROJECT
          ↑
      single line

Example:

Not every employee manages a project.

Employee 1 → Manages Project A ✓
Employee 2 → No project         ✓
Employee 3 → Manages Project B ✓

Both are valid because participation is optional.

Easy way to remember

TOTAL
  ↓
Everyone MUST participate
  ↓
Double line

PARTIAL
  ↓
Participation is OPTIONAL
  ↓
Single line

Important: Participation and cardinality are different concepts.

Participation → Must the entity participate?
Cardinality   → How many entities can participate?

For example:

Employee ─── WORKS_FOR ─── Department

Participation:
Employee → Total

Cardinality:
Many Employees → One Department