Week 9 - Normalisation Flashcards
(30 cards)
What is a candidate key in a table?
A collection of attributes that is unique across all records.
What does a candidate key do in terms of functional dependencies?
It uniquely determines every other attribute in the record.
Are all candidate keys primary keys?
No, primary keys are candidate keys, but there can be other candidate keys as well.
What is an unwanted functional dependency?
A functional dependency that is not on a candidate key and should be eliminated.
What does “redundancy” mean in the context of a database table?
It refers to the same logical information being stored in multiple places within the table.
What is one immediate drawback of redundancy in a table?
It leads to unnecessary waste of storage space.
Why is redundancy more problematic than just space usage?
Because it requires maintaining consistency across all copies of the same data.
What can go wrong if redundant data is not updated consistently?
It can lead to data inconsistencies—for example, conflicting answers about who the manager of a department is.
What does the term “anomaly” refer to in database design?
It refers to unusual or problematic situations where the database gives incorrect or inconsistent results.
Why are anomalies particularly dangerous in a database?
Because they can cause queries to return wrong results, often without any obvious signs that something is wrong.
What makes anomalies difficult to handle?
Even if detected, writing queries to work around anomalies can be complex and unreliable.
What is an update anomaly?
It occurs when updating a piece of information requires updating multiple dependent attributes, risking inconsistency if any are missed.
What is an insertion anomaly?
It happens when inserting a new record requires checking and maintaining consistency with existing dependent attributes—often a lot of effort.
What is an aggregation anomaly?
This occurs when using functions like count, sum, or average gives incorrect results due to data duplication.
What is a deletion anomaly?
It occurs when deleting a record, causing the unintentional loss of important dependent data.
When is a table schema said to be in Boyce-Codd Normal Form (BCNF)?
When all functional dependencies in the table are only on the candidate keys.
What does it mean for a functional dependency to be “on the candidate key”?
The left-hand side (LHS) of the dependency must be a superset of a candidate key.
What kind of functional dependencies are not allowed in BCNF?
Any dependency where the LHS is not a candidate key or a superset of one—these are considered “unwanted.”
How do you convert a table to BCNF?
By decomposing tables that contain unwanted functional dependencies not based on candidate keys.
What triggers the decomposition process in normalization?
The presence of an unwanted functional dependency U→X.
What is the first step in the decomposition method?
Find all attributes functionally dependent on the left-hand side
𝑈 using the closure algorithm.
In the decomposition process, what is set 𝑊?
The collection of attributes functionally dependent on 𝑈.
What is set 𝑉 in the decomposition method?
The remaining attributes not in
𝑈 or 𝑊.
What is the Losslessness property of a decomposition?
The decomposition must allow for the original relation to be reconstructed from the decomposed tables without losing any data or introducing spurious tuples.
