Final - Normalization Flashcards
What should the design of a relational schema accomplish?
It should be easy to explain its meaning, reduce redundant information in tuples, and reduce NULL values in tuples.
A better schema design minimizes storage use and helps avoid update anomalies.
What are update anomalies?
Insertion, deletion, and modification issues that can lead to inconsistent data in the database.
Examples include difficulties in adding new employees or departments, losing departments upon deletion of the last instructor, and needing to update multiple tuples for consistency.
What is normalization?
A top-down design process of decomposing unsatisfactory relations by breaking up their attributes into smaller relations to achieve guidelines.
Normalization aims to reduce redundancy and improve data integrity.
What is the First Normal Form (1NF)?
The domain of an attribute must include only atomic values, and any attribute in a tuple must be a single value from the domain.
Composite or multivalued attributes are banned in 1NF.
What is a functional dependency?
A constraint between two sets of attributes where a set X functionally determines a set Y if the value of X uniquely determines Y.
Written as X → Y, it indicates that if K is a key of R, then K functionally determines all attributes in R.
What are Armstrong’s Axioms?
Inference rules used to determine functional dependencies that are logically implied from a set of given functional dependencies.
Includes rules like trivial FD where X → Y is trivial if Y is a subset of X.
What defines a non-prime attribute?
An attribute that is not part of any candidate key of the table.
What is full functional dependency?
A functional dependency Y → Z where removal of any attribute from Y means the dependency does not hold anymore.
What is the Second Normal Form (2NF)?
A relation R is in 2NF if every non-prime attribute A in R is fully functionally dependent on every candidate key of R.
No non-prime attribute should depend on any proper subset of any candidate key.
What is the Third Normal Form (3NF)?
A relation R is in 3NF if no non-prime attribute of R is transitively dependent on the primary key.
What is transitive functional dependency?
An FD X → Z that can be derived from two FDs: X → Y and Y → Z.
What is denormalization?
The process of reversing normalization to store relations in a lower normal form for speed, while ensuring they are updated.
This can lead to more joins in database queries.
What is a remedy for achieving 1NF?
Break up multivalued or composite attributes into columns or across rows.
What happens if a department’s last instructor is deleted?
The department is lost from the database.
What should be done if a relation is not in 2NF?
Decompose each partial key and its dependent attributes into a new relation.
Fill in the blank: A relation R is in 2NF if every non-prime attribute A in R is ______ on every candidate key of R.
fully functionally dependent
Fill in the blank: A functional dependency Y → Z where removal of any attribute from Y means the FD does not hold anymore is called ______.
full functional dependency
