Database design paradigm
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 between the external keyword CNOs in SC1. When needed, natural coupling is required, and the original relationship third paradigm (3NF): If the relational mode R (u, f) All non-primary properties of the non-primary attribute do not have a reliability for any candidate key, and 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. Cause: 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 dependence (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 attribute. Because ENO-> WNO, primary attribute ENO is the determinant of WNO, but it is not a keyword, just part of the combined keyword.
