The third paradigm (3NF): In the second paradigm, based on data in the table if there is no section on any non-keyword candidate keyword section of the transfer function depends, is in the third paradigm. The so-called transfer function dependent on that is that if there is "A → B → C" decision between the transfer function C depends on A. Therefore, to meet the third paradigm of database tables should not exist as dependencies:
→ non-keyword segment key fields of non-keyword segment x → y
Table assumes that students Relations Student (student number, name, age, where the college, college location, college phone), the keyword for the single keyword "Student Number", because of the following decisions relationship:
(Student ID) → (name, age, where the college, college location, college phone)
The database is in 2NF, but do not meet 3NF, because of the following decisions relationship:
(Student ID) → (where the Academy) → (University site, the college phone)
Namely, the existence of non-key fields, "Institute of place", "School Phone" section on the keyword "Student Number" of the transfer function depends.
There is also data redundancy, update anomalies, insertion anomaly and deletion anomaly, readers can learn on their own analysis.
Relationship between the student table is divided into the following two tables:
Student: (Student ID, name, age, where the College);
School: (School, location, phone).
This database table is in line with the third paradigm, eliminating data redundancy, update anomalies, insertion anomaly and deletion anomaly.
Beuys - Section may Paradigm (BCNF): In the third paradigm, based on a database table if there is no field of any one candidate keyword section of the transfer function depends, is in the third paradigm.
Suppose warehouse management relations table StorehouseManage (Warehouse ID, store items ID, the administrator ID, number), and there is only one administrator work in a warehouse; a warehouse to store various items. The following decisions exist in the database table relationship:
(Warehouse ID, store items ID) → (administrator ID, quantity)
(Administrator ID, store items ID) → (repository ID, quantity)
Therefore, (warehouse ID, store items ID) and (administrator ID, store items ID) are StorehouseManage candidate keyword, the table only for the number of non-key fields, which is in line with the third paradigm. However, following the decision because of relationship:
(Warehouse ID) → (administrator ID)
(Administrator ID) → (Warehouse ID)
Namely, the existence keyword keyword paragraph to paragraph decision, so it does not meet BCNF paradigm. It will appear as exceptions:
(1) Delete Exception:
When the warehouse was empty, all "store items ID" and "quantitative" information is removed, while "storage ID" and "Manager ID" information has been deleted.
(2) Insert the exception:
When the warehouse does not store any items, not to the warehouse distribution manager.
(3) update anomalies:
If the repository for the administrator, the administrator of all rows in the table ID to be modified.
Relationship between the warehouse management table is divided into two relational tables:
Warehouse Management: StorehouseManage (Warehouse ID, manager ID);
Storage: Storehouse (Warehouse ID, store items ID, quantity).
This database table is in BCNF paradigm, eliminating the exception to delete, insert, update anomalies and abnormalities.
A forum for us to gradually buttoned database, the following information:
(1) User: user name, email, homepage, telephone, contact address (2) Posts: post title, post content, respond to the title, the first time we will return the contents of the database design for the mere existence of the table:
|User name||Home||Phone||Contact Address||Posting Title||Posting content||Reply Title||Reply Content|
The database table consistent with the first paradigm, but there is no candidate for a group of keywords to determine the entire database table row, the only key fields the user name can not be completely determined by the tuple. We need to increase the "Post ID", "Reply ID" field, will table amendments to:
|User name||Home||Phone||Contact Address||Posting ID||Posting Title||Posting content||Reply ID||Reply Title||Back contents|