Practice — try each question first, then expand the answer to check your reasoning.
Work through each question, then click Show answer to check yourself. Review the notes if you get stuck.
🔹 Q1. Match the formal term to the everyday/SQL term: relation, tuple, attribute.¶
Show answer
- **Relation** → table
- **Tuple** → row / record
- **Attribute** → column / field
❓ Q2. Why do textbooks say "relation" while SQL products and everyday conversation say "table"? Are they always describing the same thing?¶
Show answer
"Relation" is the formal term from relational theory, with strict rules (no duplicate rows, atomic cell values, etc.). "Table" is the everyday/SQL term, used loosely — even when a table doesn't fully satisfy every formal rule (e.g., it currently has a non-atomic column), people still call it a "table." Not every table is a perfectly valid relation, but the terms are treated as interchangeable in casual use.
🔹 Q3. List the four defining characteristics of a relation covered in this module.¶
Show answer
1. No duplicate rows.
2. Row order doesn't matter.
3. Column order doesn't matter.
4. Every cell holds a single, atomic value.
🏭 Q4. A table has a Skills column that stores values like "SQL, Excel, Salesforce" for one employee. Which rule of a relation does this break, and how would you fix it?¶
Show answer
This breaks the **atomic value** rule — the cell holds multiple values instead of one. Fix: create a separate `EMPLOYEE_SKILL` table with one row per (employee, skill) pair, linked back to `EMPLOYEE` by a foreign key.
🔹 Q5. What is the most likely primary key of STUDENT? Is Email a candidate key too?¶
Show answer
`StudentID` is the natural choice for primary key (a surrogate key with no business meaning). `Email` is very likely also a **candidate key** — if every student's email is guaranteed unique, it could have been chosen as the primary key instead, making it an **alternate key** since it wasn't chosen.
❓ Q6. What is the primary key of ENROLLMENT? What kind of key is it?¶
Show answer
`(StudentID, CourseID)` — a **composite key**. Neither column is unique alone (one student takes many courses; one course has many students), but the pair is unique, since a student enrolls in a given course only once.
🔹 Q7. In ENROLLMENT, which columns are foreign keys, and what do they reference?¶
Show answer
`StudentID` references `STUDENT.StudentID`, and `CourseID` references `COURSE.CourseID`. Both must match an existing row in their respective parent tables — the referential integrity rule.
🔹 Q8. Is StudentID in STUDENT a natural key or a surrogate key? What about Email?¶
Show answer
`StudentID` is a **surrogate key** — a DBMS/registrar-assigned number with no inherent business meaning. `Email` would be a **natural key** — it's real-world data that happens to (probably) be unique, not an artificial identifier created just to serve as a key.
🏭 Q9. Suppose ENROLLMENT is widened to add a StudentMajor column (copied from STUDENT). Identify the dependency this creates and name it.¶
Show answer
Since the primary key of `ENROLLMENT` is `(StudentID, CourseID)`, but `StudentMajor` only depends on `StudentID` (not on `CourseID`), this is a **partial dependency** — `StudentMajor` depends on only part of the composite key.
❓ Q10. Now suppose COURSE is widened to add a DepartmentOffice column, and DepartmentOffice really depends on a Department column also stored in COURSE, not on CourseID directly. What is this called?¶
Show answer
A **transitive dependency** — `CourseID → Department → DepartmentOffice`. `DepartmentOffice` depends on `Department`, a non-key column, rather than depending directly on the primary key `CourseID`.
🔹 Q11. In the Wedgewood Pacific ASSIGNMENT table (ProjectID, EmployeeNumber, HoursWorked), explain why HoursWorked is a full functional dependency on the key rather than a partial one.¶
Show answer
`HoursWorked` genuinely needs **both** `ProjectID` and `EmployeeNumber` to be determined — it's the number of hours a *specific employee* worked on a *specific project*. Neither column alone determines it, so the dependency is on the whole composite key, not just part of it — making it a full functional dependency, not a partial one.
🏦 Q12. Why is DepartmentName a risky choice of primary key for WP's DEPARTMENT table, and what's the recommended fix?¶
Show answer
If the department is ever renamed, every row in `EMPLOYEE` and `PROJECT` that references the old name as a foreign key would need to be updated too, risking a moment of broken referential integrity (or missed rows). The fix is to add a **surrogate key**, e.g. `DepartmentID`, which never changes even if `DepartmentName` does.
