MySQL | Data Types | Conditions | Queries | Relationships | Scripts
There are three types of table relationships...
1-1 (one-to-one) - Each instance of an object relates to one instance of another object.
1-M (one-to-many) - Each instance of an object relates to multiple instances of another object.
M-M (many-to-many) - Many instances of an object relate to many instances of another object.
There are two types of integrity rules...
Entity Integrity - Declares that each row in a table is uniquely identified. A table usually has only one PRIMARY KEY. The PRIMARY KEY does not allow duplicate values.
Referential Integrity - Declares that relationships between tables remain consistent. When a FOREIGN KEY column refers to the PRIMARY KEY of another table, rows cannot be added to that other table unless the values match.
Relational database normalization is the process of eliminating redundancy or data anomalies when creating and establishing relationships between tables.
When multiple instances of data occurs in a table, they may not remain consistent when data is modified in a table. A table that is normalized has less problems, because the table declares that multiple instances of the same data are represented by a single instance only.
Functional dependence and the PRIMARY KEY are important to the normalization process. A functional dependency is a constraint between two sets of attributes in a relationship. Column B is functionally dependent on column A, when each value for A is associated with one value of B.
The process of normalization takes an existing table and creates a new updated table, known as a normal form. Different levels of normal forms are first form, second form, third form, etc... The higher the number, the better the normalization. Normal forms are cumulative. For a table to be in second form, it must meet all the criteria of the first form. For a database to be in third form, it must meet all the criteria of the first and second form, and so on...
1NF (First Normal Form):
2NF (Second Normal Form):
3NF (Third Normal Form):
Anomalies fall into four categories...
