| Server IP : 134.29.175.74 / Your IP : 216.73.216.160 Web Server : nginx/1.10.2 System : Windows NT CST-WEBSERVER 10.0 build 19045 (Windows 10) i586 User : Administrator ( 0) PHP Version : 7.1.0 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : C:/nginx/html/MichaelMalz/CST1600/Resources/ |
Upload File : |
Never forget that the primary purpose of database normalization is to:
1) Reduce duplicate entry of data.
2) Prevent insert anomalies. Insertion of data that would cause duplication of data.
2) Prevent update anomalies. Updating date that would leave data that conflicts with the new value.
2) Prevent delete anomalies. Deletion of a record for one entity loses data about another.
Remember the phrase: "The data depends on key [1NF], the whole key [2NF], and nothing but the key [3NF]."
3NF rules from the class lectures:
Every attribute in a table is dependent on the PK and there are no INSERT, UPDATE, or DELETE anomolies.
1) To be in third normal form, the relation must be in second normal form. Also all transitive dependencies must be
removed; a non-key attribute may not be functionally dependent on another non-key attribute.
Eliminate all dependent attributes in transitive relationship(s) from each of the tables that have a transitive relationship.
Create new table(s) with removed dependency.
Check new table(s) as well as table(s) modified to make sure that each table has a determinant and that no table contains inappropriate dependencies.
2) Third normal form is a normal form that is used in normalizing a database design to reduce the duplication of data and ensure referential integrity by
ensuring that (1) the entity is in second normal form, and (2) all the attributes in a table are determined only by the candidate keys of that relation
and not by any non-prime attributes. 3NF was designed to improve database processing while minimizing storage costs. 3NF data modeling was ideal for online
transaction processing (OLTP) applications with heavy order entry type of needs.
3) Codd's definition states that a table is in 3NF if and only if both of the following conditions hold:
The relation R (table) is in second normal form (2NF)
Every non-prime attribute of R is non-transitively dependent on every key of R.
4) Third Normal form applies that every non-prime attribute of table must be dependent on primary key, or we can say that, there should not be the case that
a non-prime attribute is determined by another non-prime attribute.
So this transitive functional dependency should be removed from the table and also the table must be in Second Normal form.
5) A table design is said to be in 3NF if both the following conditions hold:
Table must be in 2NF
Transitive functional dependency of non-prime attribute on any super key should be removed.
An attribute that is not part of any candidate key is known as non-prime attribute.
6) A relation is in third normal form (3NF) if it is in second normal form and it contains no transitive dependencies.
Consider relation R containing attributes A, B and C. R(A, B, C)
If A → B and B → C then A → C
Transitive Dependency: Three attributes with the above dependencies.
7) A table is in 3NF when it is in 2NF and it contains no transitive dependencies.
EXAMPLE: data that is not related to the primary key.
Given a table:
student(studentID*, studentName, MajorCode, MajorName)
And sample data: 34673656, Avram Hinton, NSA, Network Systems Administration
83681840, Hayes Gibson, CST, Computer Support Technician
94086534, Kristen Acosta, CP, Computer Programmer
Is the table in 1NF?
The studentName is not atomic, it contains a first and last name. This is a 1NF violation.
So, first we will make the table 1NF compliant:
student(studentID*, firstName, lastName, MajorCode, MajorName)
And sample data: 34673656 Avram, Hinton, NSA Network Systems Administration
83681840 Hayes, Gibson, CST Computer Support Technician
94086534 Kristen, Acosta, CP Computer Programmer
Now we can see if the table is 2NF.
Since the table has only one primary key, it is in 2NF.
Now we can see if the table is 3NF.
You can see that the MajorName is not dependent on the studentID, instead it depends on the MajorCode.
Solution: This can be fixed by moving the major data to a new table and adding a relationship table to relate the student to the major.
New table:
student(studentID*, firstName, lastName)
Sample data: 34673656, Avram, Hinton
83681840, Hayes, Gibson
94086534, Kristen, Acosta
New table:
major(MajorCode*, MajorName)
Sample data: NSA, Network Systems Administration
CST, Computer Support Technician
CP, Computer Programmer
New table:
student_major(studentID*@, MajorCode*@)
Sample data: 34673656, NSA
83681840, CST
94086534, CP
Note: The solution is to:
Reduce the entity table to only hold data related to the first primary key.
Create a new table or tables to store data that is not related to the entire primary key.
The create a new table or tables table to relate the moved data back to the entity table.
The phrase: "The data depends on key [1NF], the whole key [2NF], and nothing but the key [3NF]."
Not only solves all 1NF and 2NF issues but most 3NF issues as well.
Each non-primary field in a table must be functionally dependent on the entire primary key.