Database Normalization Flashcards
What is Normalization?
A systematic approach to organizing data in relational databases according to so-called normal forms.
What is the purpose of Normalization?
To reduce data redundancy and improve data integrity.
What is data redundancy?
A condition in which the same piece of data is held in two separate places.
What is data integrity?
Overall accuracy, completeness, and consistency of data.
Name 3 types of anomalies.
Create, Update and Delete
How many Normal Forms are there?
There are six normal forms, but usually, only the first three are used.
What is Functional Dependency (FD)?
A relationship that exists between two attributes. It typically exists between the primary key and non-key attribute within a table.
What are the 2 types of functional dependency?
Trivial and Non-Trivial.
What is Partial Dependency?
Partial Dependency occurs when a non-prime (non-key) attribute is functionally dependent on part of a candidate key.
For example, if we have a table where we store students, courses and grades, the grade is partially dependent on students and partially dependent on courses as well.
2NF eliminates Partial Dependency.
What is Transitive Dependency?
Whenever some indirect relationship happens to cause functional dependency.
Thus, if A -> B and B -> C are true, then A -> C happens to be a transitive dependency.
3NF eliminates Transitive Dependency.
What is Trivial Dependency?
The dependency of an attribute on a set of attributes if the set of attributes includes that attribute.
So, X -> Y is a trivial functional dependency if Y is a subset of X.
What is Non-Trivial Dependency?
In Non-trivial functional dependency, the dependent is strictly not a subset of the determinant.
Example: If X → Y and Y is not a subset of X, then it is called Non-trivial functional dependency.
What is First Normal Form (1NF)?
- A table must not contain composite or multi-valued attributes (eg “Andrew, Jack” or “C/C++”).
- A table must not contain repeating groups of attributes (each row must be unique).
- All of the data items in a column must mean the same thing.
- A table must have no repeating columns.
- Each set of data must be identified with a primary key.
- Each set of separate data must be in a separate table.
Difference between Simple and Single-Valued attributes?
A single-valued attribute can have only a single value. For example, a person can have only one ‘date of birth’, ‘age’ etc. But it can be a simple or composite attribute.
For example, ‘date of birth’ is a composite attribute, and ‘age’ is a simple attribute. But both are single-valued attributes.
How to read X -> Y?
X determines Y or Y is dependent on X.
Example of SID -> SNAME
Name 2 properties of Functional Dependency.
Reflexivity and Transitivity
