Relational Databases 1 — Q&A Bank
From the practice quiz: Given the following relation, List all keys (superkeys).
BOOK(ISBN, TITLE, NUM_PAGES, PUBLISHER).
Note that ISBN stands for "International Standard Book Number." ISBN is a unique number assigned to each edition and variation of a book.
A key is any set of attributes that uniquely identifies a tuple (row) in a relation. YES — they are all superkeys (any set that uniquely identifies a row). If ISBN alone already uniquely identifies a row, then ANY combination that includes ISBN also uniquely identifies a row.
(a) ISBN
(b) ISBN, TITLE
(c) ISBN, TITLE, NUM_PAGES
(d) ISBN, TITLE, NUM_PAGES, PUBLISHER
(e) ISBN, NUM_PAGES
(f) ISBN, NUM_PAGES, PUBLISHER
(g) ISBN, PUBLISHER
(h) ISBN, TITLE, PUBLISHER
(a) Which key should be the primary key? ISBN
(b) Why? ISBN is the only candidate key, as publishers may reissue books.
SECTION 1: What is a Database?
Q1: What is a database?
A: A collection of data, plus a set of programs for managing that data (DBMS).
Q2: What is a DBMS? Give examples.
A: Database Management System — software that lets you store, retrieve, and manipulate data. Examples: MySQL, PostgreSQL, Oracle.
Q3: What was the dominant data model before the relational model?
A: The network or navigational model (1960s-1970s) — data stored as records with pointers between them. Code was written in COBOL.
Q4: What was the problem with the navigational model?
A: Lack of physical data independence — code was written for a specific storage model. If you changed storage or added an index, you had to rewrite your code.
Q5: What is physical data independence?
A: The ability to change how data is physically stored without having to change your code.
Q6: Who proposed the relational model and when?
A: E.F. Codd at IBM in 1970 — landmark paper: "A relational model of data for large shared data banks."
Q7: What award did E.F. Codd win and why?
A: The Turing Award in 1981 (the Nobel Prize of computing) for his work on the relational model.
Q8: What are the core ideas of the relational model?
A:
Data stored in relations (tables)
No pointers — connections via keys
Physical data independence — change storage without rewriting code
SECTION 2: Structure of a Schema
Q9: What is a relation schema?
A: The structure of a table — consists of:
A relation name (e.g., LIKES)
A set of (attribute_name, attribute_type) pairs
Notation: LIKES (DRINKER string, BEER string)
Q10: What is a relation?
A: The actual data — a table (subset of the Cartesian product of attribute domains).
Q11: What is a tuple?
A: A row (also called a record).
Q12: What is an attribute?
A: A column (also called a field).
Q13: What is a domain?
A: The set of allowed values for an attribute (e.g., integer, string, date).
Q14: What is atomicity in the relational model?
A: Each cell must contain exactly one indivisible value — no lists, arrays, or nested structures.
Q15: Give an example of an atomic attribute and a non-atomic attribute.
A:
Atomic: FNAME = "Risa" (one value)
Non-atomic: NAME = "Risa Myers" (two values in one cell — First + Last)
Q16: Why is atomicity important?
A: Enables precise queries, prevents messy data, and follows Codd's design principle: "No repeating groups, no nested structures."
SECTION 3: Data Integrity (Three Rules)
Q17: What is Entity Integrity?
A: Every table must have a Primary Key. The Primary Key cannot be NULL — every row must have a unique, non-null identifier.
Q18: What is Referential Integrity?
A: Tables connect using Foreign Keys. If a table references an ID from another table, that ID must actually exist in the referenced table. No orphan data allowed.
Q19: What is Domain Integrity?
A: Every column only accepts values from its defined domain. You cannot type "banana" into a date column.
Q20: What happens if you violate Referential Integrity?
A: The database will reject the operation (failed insert) or cascade the delete to maintain consistency.
SECTION 4: Keys in Relational Databases
Q21: What is a Primary Key?
A: The main unique identifier for a table. Rules: Unique, NOT NULL, one per table. Denoted with an underline.
Example: STUDENT (NET_ID, FNAME, LNAME)
Q22: What is a Foreign Key?
A: A column that references the Primary Key of another table. Rules: Must exist in referenced table (Referential Integrity).
Example: LIKES (DRN_ID → references DRINKER.DRN_ID)
Q23: What is a Surrogate Key?
A: An artificial key with no real-world meaning — system-generated (e.g., auto-increment ID). Used when no good natural key exists.
Example: STUDENT (STUDENT_ID, NET_ID, FNAME, LNAME)
Q24: What is the difference between a Surrogate Key and a Natural Key?
A:
Surrogate Key: Artificial, system-generated, no real meaning. Example: STUDENT_ID
Natural Key: Has real-world meaning. Example: NET_ID, SSN, Email
Q25: What is an Alternate Key?
A: A candidate key that was NOT chosen as the Primary Key.
Example: STUDENT (NET_ID, STUDENT_ID, FNAME, LNAME) — if NET_ID is primary, STUDENT_ID is alternate.
Q26: What is a Composite Key?
A: Two or more columns working together to uniquely identify a row.
Example: {DRN_ID, BEER} in LIKES — one person can like many beers, one beer can be liked by many, but the pair is unique.
Q27: What is a Candidate Key?
A: A minimal superkey — no extra columns. All keys that COULD be the Primary Key.
Example: STUDENT (NET_ID, STUDENT_ID) — both are candidate keys.
Q28: What is a Superkey?
A: Any set of columns that uniquely identifies a row — may include extra columns (not minimal).
Example: {NET_ID}, {NET_ID, FNAME}, {NET_ID, FNAME, LNAME} — all are superkeys.
Q29: What is the difference between a Superkey and a Candidate Key?
Superkey | Candidate Key |
|---|---|
Uniquely identifies a row | Uniquely identifies a row |
Extra columns allowed | Minimal — NO extra columns |
Example: {NET_ID, FNAME} | Example: {NET_ID} |
Q30: What is the key hierarchy?
A:
SUPERKEY (extra columns allowed)
↓
CANDIDATE KEY (minimal)
↓
┌─────────┴─────────┐
↓ ↓
PRIMARY KEY ALTERNATE KEY
(the chosen) (runner-up)SECTION 5: Keys Practice Questions
Q31: Given BOOK(ISBN, TITLE, NUM_PAGES, PUBLISHER) where ISBN is unique, list all keys.
A:
Superkeys: (a) through (h) — all contain ISBN
Candidate keys: (a) ISBN only
Primary key: (a) ISBN
Why: ISBN is the only candidate key, as publishers may reissue books
Q32: Given STUDENT(NET_ID, STUDENT_ID, FNAME, LNAME) where both NET_ID and STUDENT_ID are unique, what are the candidate keys?
A: NET_ID and STUDENT_ID are both candidate keys.
Q33: In Q32, if NET_ID is chosen as primary key, what is STUDENT_ID called?
A: Alternate key.
Q34: Given LIKES(DRN_ID, BEER) where one person can like many beers and many people can like the same beer, what is the key?
A: Composite key {DRN_ID, BEER} — both columns together uniquely identify a row.
Q35: What is the difference between a composite key and a superkey?
Composite Key | Superkey |
|---|---|
Two or more columns | Any number of columns (including one) |
About number of columns | About minimality (extra columns allowed) |
Example: {DRN_ID, BEER} | Example: {NET_ID, FNAME} |
SECTION 6: Predicate Logic
Q36: What is a predicate?
A: A statement about something that is TRUE or FALSE.
Example: Student(X) = "X is a student"
Q37: What is predicate logic?
A: Predicates + logical operators (AND, OR, NOT, IMPLIES, IFF).
Q38: What is First-Order Logic?
A: Predicate logic + quantification (∀ universal, ∃ existential).
Q39: What is the difference between predicate logic and first-order logic?
Predicate Logic | First-Order Logic |
|---|---|
Predicates + Operators | Predicate Logic + Quantification |
Example: Student(X) ∧ Enrolled(X) | Example: ∀(X)(Student(X) ⇒ Enrolled(X)) |
SECTION 7: Logical Operators
Q40: What does AND (∧) mean?
A: Both conditions must be TRUE.
Q41: What does OR (∨) mean?
A: At least one condition must be TRUE.
Q42: What does NOT (¬) mean?
A: The opposite.
Q43: What does IMPLIES (⇒) mean? When is it FALSE?
A: "If P then q." It is FALSE only when P is TRUE and q is FALSE. Otherwise, it is TRUE.
Q44: What does IFF (⟷) mean?
A: "If and only if" — both directions must be true. It is TRUE when P and q have the SAME value (both TRUE or both FALSE).
Q45: What is the difference between AND (∧) and IFF (⟷)?
AND (∧) | IFF (⟷) |
|---|---|
Both must be TRUE right now | Both must have the SAME value |
TRUE only when T,T | TRUE when T,T OR F,F |
Example: "It's raining AND cloudy today" | Example: "Rain and clouds always go together" |
SECTION 8: Quantification
Q46: What does Universal Quantification (∀) mean?
A: "For all" or "For every" — the condition must be TRUE for EVERY possible value.
Q47: What does Existential Quantification (∃) mean?
A: "There exists" or "For at least one" — the condition must be TRUE for AT LEAST ONE value.
Q48: What does ∀(X)(Student(X) ⇒ Enrolled(X)) mean in plain English?
A: "Every student is enrolled."
Q49: What does ∃(X)(Student(X) ∧ ¬Enrolled(X)) mean in plain English?
A: "There is at least one student who is NOT enrolled."
Q50: What is a bound variable?
A: A variable tied to a quantifier (∀ or ∃). The quantifier provides the values.
Example: In ∀(X)(Student(X) ⇒ Enrolled(X)), X is bound to ∀.
Q51: What is a free variable?
A: A variable NOT tied to a quantifier — must be provided from outside.
Example: In ∃(X)(Friends(X, Y)), X is bound, Y is free.
Q52: What is a zero-arg predicate?
A: A complete statement with no free variables — returns TRUE or FALSE based entirely on the database.
Example: ∀(X)(Student(X) ⇒ Enrolled(X)) — no outside input needed.
Q53: What is the important equivalence from the slides?
A: ∀(X)(P(X)) ≡ ¬∃(X)(¬P(X))
Translation: "For all X, P(X) is true" is the same as "There does NOT exist an X where P(X) is FALSE."
Q54: Convert ¬∃(X)(Student(X) ∧ ¬Enrolled(X)) to ∀ version.
A: ∀(X)(Student(X) ⇒ Enrolled(X))
Q55: Convert ∀(X)(Employee(X) ⇒ HasID(X)) to ∃ version.
A: ¬∃(X)(Employee(X) ∧ ¬HasID(X))
Q56: In the formula ∃(X)(Friends(X, Y)), which variables are bound and which are free?
A: X is bound (to ∃), Y is free (not bound to any quantifier).
Q57: In the formula ∀(X)(Student(X) ⇒ Enrolled(X)), which variables are bound and which are free?
A: X is bound (to ∀), no free variables. This is a zero-arg predicate.
SECTION 9: Relational Calculus vs. Relational Algebra
Q58: What is the difference between Relational Calculus and Relational Algebra?
Relational Calculus (RC) | Relational Algebra (RA) |
|---|---|
Declarative — say WHAT you want | Procedural — say HOW to get it |
Example: "Give me all students" | Example: "Filter rows, then project columns" |
Q59: What is SQL influenced by?
A: Heavily influenced by Relational Calculus, has aspects of Relational Algebra.
Quick Reference — Key Terms
Term | Definition |
|---|---|
Database | Collection of data + programs to manage it |
Relation | A table |
Tuple | A row (record) |
Attribute | A column |
Atomicity | One value per cell |
Physical Data Independence | Change storage without rewriting code |
Entity Integrity | PK cannot be NULL |
Referential Integrity | FK must exist in referenced table |
Domain Integrity | Values must match column type |
Quick Reference — Keys
Key Type | What It Does |
|---|---|
Primary Key | Main unique identifier — one per table, NOT NULL |
Foreign Key | References another table's Primary Key |
Surrogate Key | Artificial, system-generated ID |
Alternate Key | Candidate key NOT chosen as primary |
Composite Key | Two+ columns together to uniquely identify |
Candidate Key | Minimal superkey (no extra columns) |
Superkey | Any set that uniquely identifies (extra columns allowed) |
Quick Reference — Logic
Operator | When TRUE |
|---|---|
P ∧ q | BOTH are TRUE |
P ∨ q | AT LEAST ONE is TRUE |
¬P | P is FALSE |
P ⇒ q | Always except when P=T and q=F |
P ⟷ q | P and q have SAME value |
Quick Reference — Quantification
Symbol | Meaning | When TRUE |
|---|---|---|
∀ | For all | Condition holds for EVERY value |
∃ | There exists | Condition holds for AT LEAST ONE value |
Quick Reference — Important Equivalence
Equivalence |
|---|
∀(X)(P(X)) ≡ ¬∃(X)(¬P(X)) |
More Practice Questions
TOPIC 5: Reading Quantified Formulas Practice
PRACTICE PROBLEM 14:
Translate the following formula to plain English:
∀(X)(Student(X) ⇒ Enrolled(X))
ANSWER 14:
"For all X, if X is a student, then X is enrolled."
Or: "Every student is enrolled."
PRACTICE PROBLEM 15:
Translate the following formula to plain English:
∃(X)(Student(X) ∧ ¬Enrolled(X))
ANSWER 15:
"There exists an X such that X is a student and X is not enrolled."
Or: "There is at least one student who is not enrolled."
PRACTICE PROBLEM 16:
Translate the following formula to plain English:
∀(X)(Employee(X) ⇒ (HasID(X) ∨ HasSSN(X)))
ANSWER 16:
"For all X, if X is an employee, then X has an ID or X has an SSN."
Or: "Every employee has either an ID or an SSN."
PRACTICE PROBLEM 17:
Translate the following formula to plain English:
∃(X)(Employee(X) ∧ ¬HasID(X) ∧ HasSSN(X))
ANSWER 17:
"There exists an X such that X is an employee and X does not have an ID and X has an SSN."
Or: "There is at least one employee who does not have an ID but does have an SSN."
PRACTICE PROBLEM 18:
Translate the following formula to plain English:
∀(X)(Person(X) ⇒ (Student(X) ∨ Employee(X)))
ANSWER 18:
"For all X, if X is a person, then X is a student or X is an employee."
Or: "Every person is either a student or an employee."
PRACTICE PROBLEM 19:
Translate the following formula to plain English:
∃(X)(Student(X) ∧ Likes(X, "Pizza"))
ANSWER 19:
"There exists an X such that X is a student and X likes Pizza."
Or: "There is at least one student who likes Pizza."
PRACTICE PROBLEM 20:
Translate the following formula to plain English:
∀(X)(Course(X) ⇒ ∃(Y)(Student(Y) ∧ Enrolled(Y, X)))
ANSWER 20:
"For all X, if X is a course, then there exists a Y such that Y is a student and Y is enrolled in X."
Or: "Every course has at least one student enrolled."
PRACTICE PROBLEM 21:
Translate the following formula to plain English:
∃(X)(Student(X) ∧ ∀(Y)(Course(Y) ⇒ Enrolled(X, Y)))
ANSWER 21:
"There exists an X such that X is a student and for all Y, if Y is a course, then X is enrolled in Y."
Or: "There is a student who is enrolled in every course."