relational database paradigm (1)

First Paradigm (1NF)
The definition of the first paradigm is that all attributes in a relational pattern are non-re-divided, that is, atomicity. This is better understood, it is not to deal with redundancy or insert update exception, is to meet the basic needs of structured data. If a student's relationship model has a height and weight of two attributes, if it fits together, now needs to read his height, how should I handle it?
second paradigm (2NF)
On the basis of satisfying the first paradigm, if the non-primary attribute in the relational pattern is completely dependent on the primary key, i.e. there is no partial dependency of the non-primary attribute on the primary key, then the relational pattern satisfies the second normal form.
Partly dependent, if only looking at this word, it has two interpretations. One is that a primary key cannot completely determine a non-primary property, and one part of the primary key can determine a non-primary property. In fact, many people take the previous explanation, which is not correct, because if the primary key cannot determine the non-primary attribute, then the meaning of the primary key does not exist. Or, when we discuss relational patterns, the uniqueness of a record is determined by its primary key, which is the fundamental principle, otherwise it is not a relational pattern.
Illustrate some of the dependencies. Consider such a procurement management system, a supplier can provide multiple products, a product can be purchased from a number of suppliers, the same products at different suppliers may be different prices. Set up a commodity unit price list, with Product ID, supplier ID, supplier address, Unit price, obviously (product ID, vendor ID) is the primary key of this table.
If a vendor provides dozens of items, then the relationship between the vendor ID and the vendor address will appear dozens of times, and in fact only once is meaningful, which is data redundancy.
If the supplier changed the office address, then the dozens of data must be changed, or there will be a vendor corresponding to two addresses, which is the update exception.
If you add a supplier, but do not have its commodity information, it is not input database, because the lack of the product ID of the primary key, which is an insert exception.
If the goods purchased from the current supplier are no longer needed, to be removed, but this supplier has other goods will be provided, but this time has not entered the data, if the deletion of existing products, will delete the supplier information, this is the deletion exception.
The correct solution is to separate the supplier ID and the vendor address to form a new table.
In fact, the second paradigm is for the Federated primary Key, if the primary key is a single attribute, then satisfying the first normal form will inevitably satisfy the second paradigm.
Third paradigm (3NF)
On the basis of satisfying the second normal form, if there is no dependency between the principal attributes, the relational pattern satisfies the third paradigm. Another way to express this is that there cannot be a dependency on a primary key pass by a non-primary property, that is, a primary key (key), a non-primary property, a,a-> non-primary attribute B.
Based on the above example, we consider a warehouse management system. Purchased goods need to be stored in the warehouse, a commodity will only be placed in a warehouse, but a warehouse can put a variety of goods, a warehouse has an administrator. If we create such a table, including the item ID, warehouse, administrator, the primary key is the item ID, it satisfies the second paradigm, but does not satisfy the third paradigm, because the administrator is fully dependent on the warehouse, and it will have the following problems:
If a warehouse has more than one item, then the warehouse and admin relationship will appear in all goods in this warehouse, which is data redundancy.
If the warehouse for the administrator, must be the warehouse all the goods record changes, otherwise there will be warehouse, the administrator corresponding inconsistencies, this is an update exception.
Create a new warehouse, but have not decided what to put the goods, this is not input database, because the lack of product ID, this is an insert exception.
If a warehouse inside all the goods are not purchased after use, but there is no new goods placed in this warehouse, then delete the existing goods information will be the warehouse, the administrator corresponding information also deleted, this is the deletion exception.
The correct solution is to separate the warehouse, the administrator relationship to create a new table.

relational database paradigm (1)