Someone described normalization rule as "a non-key column depends on the key, the whole key, and nothing but the key, so help me Codd." Key refers a primary or other candidate key. If the key has multiple columns, then "whole key" means all columns together and not some of the columns. Explain in your words what 3rd normal form is and why it is important.
3rd normal form consists of three different levels of normalization.
1st normal form:
- Each row in the table must be unique.
- Each column in the table can only contain one value per row.
2nd normal form:
- The table is in 1NF.
- The table does not contain partial functional dependencies, i.e., non-key columns must not depend on a subset of the primary key.
- A table that is in 1NF and that only has a single primary key is automatically in 2nd normal form.
3rd normal form:
- The table is in 2NF.
- The table does not contain transitive functional dependencies, i.e., non-key columns must not functionally depend on other non-key columns.
In practice, these rules of normalization ensure that a table does not contain redundant, or duplicated data. This is important to avoid insertion, deletion, and modification anomalies:
- Insertion anomaly: When inserting data regarding a real-world entity requires inserting data from unrelated entities.
- Deletion anomaly: When deleting data regarding a real-world entity requires deleting data from unrelated entities.
- Modification anomaly: When modifying a value requires modifying said value multiple times. This occurs when an attribute does not fully depend on the primary key of a table, and thus is copied every time the attribute it depends on is referenced.
What is an SQL view. How is it similar to a table? In what ways is it different?
A view is a kind of virtual table. In reality, a view is simply a standard SQL statement that can be executed to generate a table. Views are used to simplify queries, as instead of having to copy the view’s statement every time the relevant data is desired, the view itself can be referenced. In this way, a view is an abstraction in which a potentially complex statement is saved, and then referred to and executed by name. The result of this execution is syntactically treated as a table, and is eligible to be used as such in the containing statement.