The relationship database is designed to comply with certain rules. In particular, the database design paradigm is now briefly introduced 1NF (first paramour), 2nf (second paradigm), 3NF (third paradigm) and BCNF, and other fourth paradigms and fifth paradigms are later described later. When you design the database, you can match these paradigms, you are the master of the database design.
The first paradigm (1NF): In each of the specific relationship r in the relational mode R, if each attribute value is a minimum data unit that is uncomfortable, R is the relationship of the first paradigm. Example: If the employee, the name, the phone number makes up a table (one may have an office phone and a home phone number) specification has become 1NF. There are three ways: First, repeat the staff number and name. In this way, the keyword can only be a telephone number. Second, employees are keywords, telephone numbers are divided into unit phones and residential phones. The three attributes of the telephone and residential calls are the employee membership, but forced each record only one phone number. The above three methods, the first method is the least, and the latter case is selected according to the actual situation.
The second paradigm (2NF): If all non-primary properties in the relational mode R (U, F) are completely dependent on any candidate keyword, the relational R is called a second paradigm. Example: Class Sno, CNO, GRADE, CREDIT, Sno, CNO as a course number, gradege as a score, Credit as credits. From the above conditions, the keyword is combined keyword (SNO, CNO) is used in the application. The above relationship model is used in the application: a. Data redundancy, assume that the same class is elected by 40 students, and credits repeated 40 times. b. Update an exception, if the credits of a course are adjusted, the corresponding tuple Credit value is updated, and there is a difference between the same cluster. c. Insert anomalies, such as planning new courses, because no one is elected, there is no keyword keyword, only some people can choose to deposit the course and credits. d. Delete exceptions, if the students have completed, remove the elective record from the current database. Some doors have not been repaired, and this course and credit records cannot be saved. Cause: Non-Keyword Properties CREDIT only functions depend on CNO, that is, the CRedit part relies on combined keywords (SNO, CNO) instead of completely dependent. Solution: Divided into two relational modes (SNO, CNO, GRADE), C2 (CNO, CREDIT). The new relationship includes two relational patterns, and they are connected to the external keyword CNO in SC1, and naturally joints are required, and the original relationship is restored.
Third Paradigm (3NF): If all non-primary properties in the relational mode R (U, F) do not pass trust on any candidate keyword, the relational R is a third paradigm. Example: If S1 (Sno, Sname, DNO, DNAME, LOCATION) each attribute represents a student number, name, a system, a system name, a system address. Keyword SNO determines each attribute. Since it is a single keyword, there is no partial dependence, it is definitely 2nf. However, this relationship has a large amount of redundancy, and several attributes of the student are located, DNAME, Location will also generate a case where the above examples will be generated when it is repeatedly stored, inserted, deleted, and modified. The original because there is a conversion dependence in the relationship. SNO -> DNO. DNO -> SNO does not exist, DNO -> Location, so key Liao SNO is implemented by transmitting dependence on Sno -> Location by passing. That is, the SNO does not directly determine non-primary property Location. Solution: There is no transfer dependence in each relational mode. Solution: Divided into two relationships S (Sno, Sname, DNO), D (DNO, DNAME, LOCATION) Note: There is no keyword DNO in relation to relationship s. Otherwise, there is a loss between the two relationships.
BCNF: If all attributes of relational mode R (U, F) (including primary properties and non-primary properties) do not pass any candidate key that relies on R, the relational R is BCNF. Or the relationship mode R, if each decision is included in the keyword (instead of being included in the keyword), the RCNF relationship mode. Example: Accessories Management Relationship mode WPE (WNO, PNO, ENO, QNT) separate table warehouse number, part number, staff number, quantity. There are the following conditions a. A warehouse has multiple employees. b. A employee works only in a warehouse. c. A model of a model in each warehouse is being responsible by a person, but a person can manage several accessories. d. Accessories of the same model can be pained in several warehouses. Analysis: The above PNO cannot determine the QNT, determined by the combination attributes (WNO, PNO), existential dependencies (WNO, PNO) -> ENO. Since a part in each warehouse is responsible by a special person, and one person can manage several accessories, there is a combination attribute (WNO, PNO) to determine the person in charge, (WNO, PNO) -> ENO. Because a worker works only in a warehouse, there is an ENO -> WNO. Since a part in each warehouse is being responsible by a special person, a employee is only working in a warehouse, has (ENO, PNO) -> qnt. Find a candidate keyword, because (WNO, PNO) -> QNT, (WNO, PNO) -> ENO, therefore, (WNO, PNO) can determine the entire tuple, a candidate keyword. According to ENO-> WNO, (ENO, PNO) -> QNT, so (ENO, PNO) can also determine the entire tuple, which is another candidate keyword. Attributes ENO, WNO, and PNO are both primary attributes, only one non-primary attribute QNT. It is completely functional depending on any candidate keyword and is directly dependent, so the relationship mode is 3NF. Analyze the primary properties. Because ENO-> WNO, primary attribute ENO is the determinant of WNO, but it is not a keyword, just part of the combined keyword. This causes the primary attribute WNO to depends on another candidate keyword (ENO, PNO), because (ENO, PNO) -> ENO but in turn is not established, and P-> WNO, so (ENO, PNO) -> WNO Also transmits dependencies. Although there is no non-primary attribute to converate the candidate key Liao, there is a problem that the main attribute is dependent on the candidate keyword, and it will also bring trouble. Such as a new employee is assigned to the warehouse work, but is temporarily in the internship stage, not independently responsible for the management tasks for certain accessories. From a part of the PNO missing the keyword, it cannot be inserted into the relationship. As another person changed to the accessories, the employee will be deleted while deleting the accessory.
Workaround: Divided into management EP (ENO, PNO, Qnt), keyword (ENO, PNO) Work EW (ENO, WNO) The keyword is an ENO shortcoming: the ability to rely on function dependence is poor. In such an example, due to the decomposition, the function is dependent (WNO, PNO) -> ENO is lost, it is destroyed for the original semantics. There is no embodying a part of each warehouse being responsible for it. There may be a part of two people or more than two people to manage. Therefore, the relationship mode after decomposition reduces partial integrity constraints. A relationship is decomposed into multiple relationships, so that the decomposes make sense, the code requires that the original information is not lost after decomposition. This information includes not only the data itself, but also the mutual constraint between the data represented by the function. The goal of decomposition is to achieve a higher level of normalization, but decomposition must consider two problems: lossless coupling and retention of function dependence. Sometimes it is often impossible to do both lossless coupling, but also keep the function dependence. It is necessary to trade out as needed.
1NF is related to the four paradigms of BCNF: BCNF contains 3NF contains 2nF contains 1NF
Small knot: Vocation: The standardization purpose is to make the structure more reasonable, eliminate storage exceptions, so that data redundancy is small, easy to insert, delete, and update principles: Compliance concept SLD "one-leg" principle, ie a relational mode describes an entity or One connection between the entity. The essence of the norm is a single system of the concept. Method: The relational mode is projected into two or more relationship modes. Requirements: The collection of relationship patterns after decomposition should be "equivalent" with the original relationship mode, that is, the natural connection can restore the original relationship without losing information, and maintain a reasonable relationship between attributes.
Note: A relational mode knot decomposition can get a collection of different relational patterns, which means that the decomposition method is not unique. The minimum redundancy requires that the decomposed database can express the original database all credits for premise. Its fundamental goal is to save storage space, avoid data inconsistency, improve the operational efficiency of relationship, while meeting application requirements. In fact, it is not necessarily required that all modes have reached BCNF. Sometimes some redundancy may be more convenient for data queries. This is especially true for database systems with high update frequencies and extremely high query frequencies.
In the relational database, in addition to the functionality, there are multi-valued dependencies, link-dependent issues, and propose higher levels of standardization requirements such as fourth paradigm, fifth paradigm. Here, I will talk later.
