What is a functional dependency (FD) in a database? Flashcards
A functional dependency π β π XβY exists if for any two tuples with the same values for π X, they must have the same values for π Y. (18 cards)
Q: What is a functional dependency (FD) in a database?
A functional dependency
π
β
π
XβY exists if for any two tuples with the same values for
π
X, they must have the same values for
π
Y.
: Give an example of a functional dependency.
In a table with StudentID, StudentName, CourseID, and CourseName, StudentID β StudentName means StudentID determines StudentName.
Q: What is the first normal form (1NF)?
A: 1NF ensures that the domain of each attribute contains only atomic (indivisible) values and that each value in a column is of the same data type.
What is the second normal form (2NF)?
2NF ensures that a table is in 1NF and that all non-key attributes are fully dependent on the primary key, eliminating partial dependencies.
What is the third normal form (3NF)?
3NF ensures that a table is in 2NF and that all attributes are only dependent on the primary key, eliminating transitive dependencies.
What is the Boyce-Codd Normal Form (BCNF)?
BCNF is a stricter version of 3NF where every determinant must be a candidate key, ensuring no anomalies remain.
What is an insertion anomaly?
An insertion anomaly occurs when you cannot insert data due to missing related data, or you have to insert incomplete data due to table structure.
: Why are functional dependencies important in normalization?
A: FDs help identify redundancy and anomalies, guiding the decomposition of tables to achieve higher normal forms.
Give an example of an insertion anomaly.
In a table with StudentID, StudentName, CourseID, and CourseName, you might not be able to add a new student until they enroll in a course.
: What is a deletion anomaly?
A deletion anomaly occurs when deleting a record results in the unintended loss of additional data that should have been retained.
Give an example of a deletion anomaly.
Deleting a studentβs enrollment in a course might also remove all information about the student if it is stored in the same table.
What is a modification anomaly?
A modification anomaly occurs when changing data in one place requires multiple updates or leads to inconsistent data.
Give an example of a modification anomaly.
Changing a studentβs address in a table where it is stored redundantly may require multiple updates, leading to potential inconsistencies.
What is denormalization in a database?
Denormalization is the process of intentionally introducing redundancy into a database to improve read performance.
: What are the benefits of denormalization?
Denormalization can improve read performance, simplify queries, and optimize performance in read-heavy systems.
What are the trade-offs of denormalization?
Denormalization increases redundancy, storage costs, and complexity in data maintenance, which can lead to data inconsistencies.
When is denormalization particularly useful?
Denormalization is useful in data warehousing, reporting systems, and read-heavy applications where read performance is critical.
Give an example of denormalized data structure.
Combining student and course enrollment information into a single table to avoid joins and improve read performance.
