According to Date's definition of 1NF, a table is in 1NF if and only if it is "isomorphic to some relation", which means, specifically, that it satisfies the following five conditions:
1. There's no top-to-bottom ordering to the rows.
2. There's no left-to-right ordering to the columns.
3. There are no duplicate rows.
4. Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).
5. All columns are regular i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps.
Example of 1NF:
The designer has a requirement to record multiple telephone numbers for some customers.
A design that complies with 1NF
A design that is unambiguously in 1NF makes use of two tables: a Customer Name table and a Customer Telephone Number table.
Customer_Name
| CustomerID | FirstName | Surname |
| 123 | Robert | Ingram |
| 456 | Jane | Wright |
| 789 | Maria | Fernandez |
Customer_Telephone_Number
| CustomerID | TelephoneNumber |
| 123 | 555-861-2025 |
| 456 | 555-403-1659 |
| 456 | 555-776-4100 |
| 789 | 555-808-9633 |
We use the following SELECT statement:
SELECT Customer_Name.FirstName, Customer_Name.Surname,
Customer_Telephone_Number.TelephoneNumber
FROM Customer_Name
LEFT JOIN Customer_Telephone_Number
ON Customer_Name.CustomerID=Customer_Telephone_Number.CustomerID
ORDER BY Customer_Name.Surname
The LEFT JOIN keyword returns all the rows from the left table (Customer_Name), even if there are no matches in the right table (Customer_Name).
More about LEFT JOIN, RIGHT JOIN, FULL JOIN, INNER JOIN can be found:
http://www.w3schools.com/sql/sql_join_left.asp
Second Normal Form
Where the First Normal Form deals with atomicity of data, the Second Normal Form (or 2NF) deals with relationships between composite key columns and non-key columns. As stated earlier, the normal forms are progressive, so to achieve Second Normal Form, your tables must already be in First Normal Form.The second normal form (or 2NF) any non-key columns must depend on the entire primary key. In the case of a composite primary key, this means that a non-key column cannot depend on only part of the composite key.
Multiple candidate keys occur in the following table:
| Manufacturer | Model | Model Full Name | Manufacturer Country |
|---|---|---|---|
| Forte | X-Prime | Forte X-Prime | Italy |
| Forte | Ultraclean | Forte Ultraclean | Italy |
| Dent-o-Fresh | EZbrush | Dent-o-Fresh EZBrush | USA |
| Kobayashi | ST-60 | Kobayashi ST-60 | Japan |
| Hoch | Toothmaster | Hoch Toothmaster | Germany |
| Hoch | X-Prime | Hoch X-Prime | Germany |
| Manufacturer | Manufacturer Country |
|---|---|
| Forte | Italy |
| Dent-o-Fresh | USA |
| Kobayashi | Japan |
| Hoch | Germany |
| Manufacturer | Model | Model Full Name |
|---|---|---|
| Forte | X-Prime | Forte X-Prime |
| Forte | Ultraclean | Forte Ultraclean |
| Dent-o-Fresh | EZbrush | Dent-o-Fresh EZBrush |
| Kobayashi | ST-60 | Kobayashi ST-60 |
| Hoch | Toothmaster | Hoch Toothmaste |
| Hoch | X-Prime | Hoch X-Prime |
Third Normal Form
Third Normal Form (3NF) requires that all columns depend directly on the primary key. Tables violate the Third Normal Form when one column depends on another column, which in turn depends on the primary key (a transitive dependency).One way to identify transitive dependencies is to look at your table and see if any columns would require updating if another column in the table was updated. If such a column exists, it probably violates 3NF.
An example of a 2NF table that fails to meet the requirements of 3NF is:
| Tournament | Year | Winner | Winner Date of Birth |
|---|---|---|---|
| Indiana Invitational | 1998 | Al Fredrickson | 21 July 1975 |
| Cleveland Open | 1999 | Bob Albertson | 28 September 1968 |
| Des Moines Masters | 1999 | Al Fredrickson | 21 July 1975 |
| Indiana Invitational | 1999 | Chip Masterson | 14 March 1977 |
The breach of 3NF occurs because the non-prime attribute Winner Date of Birth is transitively dependent on the candidate key {Tournament, Year} via the non-prime attribute Winner. The fact that Winner Date of Birth is functionally dependent on Winner makes the table vulnerable to logical inconsistencies, as there is nothing to stop the same person from being shown with different dates of birth on different records.
In order to express the same facts without violating 3NF, it is necessary to split the table into two:
|
|
Update anomalies cannot occur in these tables, which are both in 3NF.
