Understanding Relational Database Concepts and Operations
1. Relational Model Basics
Key Concepts:
- A relation is a set of tuples (no duplicates, no ordering).
- Each tuple conforms to the relation’s schema.
- Primary keys uniquely identify tuples.
- Foreign keys maintain referential integrity.
Practice Question: Consider a library database with Books(ISBN, title, author, year). Which statement is true?
- a) Two books can have the same ISBN if they’re different editions.
- b) The order of book records affects query results.
- c) Each book record must conform to the (ISBN, title, author, year) schema ✓
- d) Books must reference at least one other table.
Answer: C – Every tuple must have exactly the attributes defined in the schema, with appropriate data types.
2. Keys and Superkeys
Definitions:
- Superkey: A set of attributes that can uniquely identify each tuple in a relation (functionally determines all attributes).
- Key (Candidate Key): A minimal superkey – a superkey where no proper subset is also a superkey.
- Primary Key: One candidate key chosen from all possible candidate keys to serve as the main identifier for the relation.
Practice Question: In Students(ID, email, name, major, advisor), if {ID, email} is a key, which is true?
- a) {ID} cannot be a key ✓
- b) {ID, email, major} is definitely a key.
- c) {ID, email, major} is a superkey but not a key.
- d) No two students can have the same major.
Answer: A – Since {ID, email} is given as a key (minimal superkey), neither {ID} nor {email} alone can uniquely identify tuples; otherwise, the key wouldn’t be minimal.
3. NULL Handling in SQL
Three-Valued Logic:
- TRUE
- FALSE
- UNKNOWN
NULL comparisons result in UNKNOWN:
- UNKNOWN AND TRUE = UNKNOWN
- UNKNOWN OR TRUE = TRUE
Practice Question: If salary=NULL, age=25, experience=3, evaluate: ((salary > 50000) OR (age < 30)) AND (experience > 5)
Step 1: (UNKNOWN OR TRUE) = TRUE
Step 2: TRUE AND FALSE = FALSE ✓
Answer: FALSE – Even though the OR evaluates to TRUE, the final AND with FALSE makes the entire expression FALSE.
4. JOIN Operations and Cardinality
Self-Join Analysis: When joining a table with itself, the result size depends on the join condition and data distribution.
Practice Question: Table Employees(ID, department) has 8 tuples. How many tuples result from:
SELECT * FROM Employees E1, Employees E2 WHERE E1.department = E2.department
- a) Exactly 8
- b) Exactly 64
- c) Between 8 and 64, depending on department distribution ✓
- d) Always more than 64
Answer: C – If all employees are in different departments: 8 tuples. If all are in the same department: 64 tuples. Usually somewhere between.
5. Subqueries and Aggregation
Subquery Cardinality Rules:
- Scalar subqueries (=) must return exactly one value.
- Multiple values cause runtime errors.
- Zero values return NULL.
Practice Question: When does this query cause a runtime error?
SELECT product_name FROM Products P
WHERE P.price = (SELECT MAX(price) FROM Products WHERE category = ‘Electronics’);
- a) When no Electronics products exist (returns NULL, query runs).
- b) When multiple products have the maximum price (MAX returns one value).
- c) When the Products table is empty (MAX returns NULL).
- d) This query never causes a runtime error ✓
Answer: D – MAX always returns a single value (or NULL), so the subquery is always scalar-compatible.