Database Fundamentals: Normalization, Storage, and SQL Queries

This document summarizes key concepts and solutions from previous database exam questions.

Relational Schema with Functional Dependencies

• Schema: R(A, B, C, D, E, F)
• Dependencies: C → F, E → A, EC → D, A → B

1. Candidate Keys: CE
2. Prime Attributes: C, E
3. Second Normal Form (2NF):
   • No, because of partial dependencies:
     • C → F (partial dependency)
     • E → A, B (partial dependency)

EMP_DEPT Table Analysis

1. Functional Dependencies:
   • Ssn → Ename, Bdate, Address, Dnumber, Dname, Dmgr_ssn
   • Dnumber → Dname, Dmgr_ssn
   • Dmgr_ssn → Dnumber, Dname
   • Dname → Dnumber, Dmgr_ssn
2. First Normal Form (1NF):
   • Split names into first_name and last_name.
3. Second Normal Form (2NF):
   • Already in 2NF because there are no partial dependencies.

EMP_PROJ Table Normalization

1. Functional Dependencies:
   • Ssn, Pnumber → hours, Ename, Pname, Plocation
   • Pname → Plocation
   • Ssn → Ename
   • Ename → Ssn
   • Pnumber → Pname, Plocation

1. Normal Forms:
   • 1NF: Yes
   • 2NF: No, because:
     • Pnumber → Pname, Plocation
     • Ssn → Ename
   • Converted to 2NF:
     • Table1: {Ssn, Pnumber, hours}
     • Table2: {Pnumber, Pname, Plocation}
     • Table3: {Ssn, Ename}
   • 3NF: No, because of transitive dependency (Pname → Plocation).
   • Converted to 3NF:
     • Table4: {Pname, Plocation}

Department Table and 1NF Conversion

• Problem: Dlocations has non-atomic values.
• Conversions to 1NF:
  1. Divide into multiple rows.
  2. Add more columns for each Dlocation.
  3. Separate Dlocations into a new table.

Hash Buckets and Block Storage Calculations

1. Rows per Bucket:
   • Block size = 8 KB = 8192 bytes
   • Row size = 200 bytes
   • Rows per block = 8192 / 200 = 40 rows.
2. Maximum Inserts (Best-Case):
   • Buckets = 13
   • Total rows = 13 × 40 = 520 rows.
3. Worst-Case Scenario:
   • 1,000,000 rows / 13 buckets = ~76,923 blocks per bucket.

Column-Oriented Storage Analysis

1. Compute Average Income by Postal Code:
   • Block size = 4 KB = 4096 bytes
   • Column size = 8 bytes
   • Values per block = 4096 / 8 = 500
   • For 1,000,000 rows:
     • Blocks needed = 1,000,000 / 500 = 2000 blocks.

Employee Table Normalization Status

1. Candidate Key: EmployeeID.
   • True
2. Non-Key Columns: Name, PostalCode.
   • True
3. 3NF: False; transitive dependencies exist.
4. BCNF Conversion: Eliminate transitive dependencies using dependency diagrams.

SQL Trigger Example

• Trigger: Updates available quantity in Product table after insertion in SaleItem table.

CREATE TRIGGER updateAvailableQuantity
AFTER INSERT ON SaleItem
FOR EACH ROW
BEGIN
    UPDATE Product
    SET available_quantity = available_quantity - NEW.quantity
    WHERE Product.barcode = NEW.barcode;
END;

SQL Query for Actors in 'Annie Hall'

SELECT a.act_id, a.act_fname, a.act_lname, a.act_gender
FROM actor a
INNER JOIN movie_cast mc ON a.act_id = mc.act_id
INNER JOIN movie m ON m.mov_id = mc.mov_id
WHERE m.mov_title = 'Annie Hall';

This summary condenses essential database concepts and solutions for quick reference.