Normalization
To be in 1NF, what is the rule regarding the values in each cell?
They must be Atomic (single values, no lists or sets)
In 3NF, we must eliminate "Transitive Dependencies." What are those?
When a non-key attribute depends on another non-key attribute.
Which JOIN type only returns rows where there is a match in both tables?
Inner Join
What do we call the problem where we store the same name or office multiple times?
Redundancy.
What is a "Foreign Key"?
A column that refers to a Primary Key in another table to link them.
Which normal form focuses on removing "Partial Functional Dependencies"?
2NF (Second Normal Form)
How do we fix a table where Zip Code determines City (a 3NF violation)?
Create a separate table for Zip Codes and link it.
Which JOIN returns all records from the left table and the matching records from the right?
Left Join
If you change a professor’s phone number in one row but not the others, what anomaly is this?
Update Anomaly.
If a Primary Key uses two columns together, what is it called?
A Composite Key.
True or False: If a table has a single-column Primary Key and is in 1NF, it is automatically in 2NF
True
What does BCNF stand for? (Double point if you dance)
Boyce-Codd Normal Form.
If a RIGHT JOIN finds no match in the left table, what does it display?
NULL
If you can't record a new student because they haven't picked a course yet, what anomaly is this?
Insertion Anomaly.
What is a "Candidate Key"?
Any unique column that could have been chosen as the Primary Key.
What must you do with attributes that depend on only part of a composite key?
Move them to a new table with a copy of that part of the key.
What is the strict requirement for every "Determinant" in BCNF?
Every determinant must be a Super Key (or Candidate Key).
What is the result of a FULL OUTER JOIN?
All rows from both tables, whether they match or not.
If you delete a course and accidentally lose the only record of the Professor's office, what anomaly is this?
Deletion Anomaly.
You are lucky! Get 400
400
Explain the phrase: "The Key, the Whole Key, and Nothing but the Key."
It describes the progression: 1NF (The Key), 2NF (The Whole Key), and 3NF (Nothing but the Key)
Why might a table fail BCNF even if it is in 3NF?
Because it has overlapping candidate keys where a non-key determines part of a key.
What is the specific SQL keyword used to link two tables on a shared ID?
The ON keyword.
What is the primary goal of normalization?
To minimize redundancy and prevent anomalies.
Why is a "Big Table" (one giant table) usually considered a bad design?
It leads to massive redundancy and potential data inconsistency.