Simple and simple: Examples of database design patterns

A relational database is designed to conform to certain rules. In particular, database design paradigm is briefly introduced 1NF (first normal form), 2NF (second normal form), 3NF (third normal form) and BCNF, another fourth normal form and fifth normal form are left to be introduced later. When you design a database, if you can meet these paradigms, you are the master of database design.

The first paradigm (1NF): In each specific relationship in the relational mode R, if each attribute value is a minimum data unit that is not to be divided, it is said that R is the relationship of the first normal form. Example: such as employee number, name, telephone number composed of a table (a person may have an office phone and a home phone number) The specification becomes 1NF there are three ways:

One is to store the employee number and name repeatedly. This way, the keyword can only be a phone number.

Second, the employee number is the key word, the telephone number is divided into unit telephone and residential telephone two attributes

Third, the employee number is the keyword, but it is mandatory to have only one phone number per record.

The above three methods, the first method is the most undesirable, according to the actual situation after the selection of two cases.

Second Normal form (2NF): If all the u,f attributes in the relational schema R (s) are completely dependent on any candidate keyword, then the relation R is called the second normal form.

Example: Elective course relationship SCI (Sno,cno,grade,credit) in which SNO for the study number, CNO for the course number, Gradege for the results, credit for credits. By the above condition, the keyword is the combination keyword (SNO,CNO)

Using the above relational pattern in your application has the following issues:

A. Data redundancy, assuming that the same course is elective by 40 students, and the credits are repeated 40 times.

B. Update the exception, if you adjust the credits of a course, the corresponding META group credit value should be updated, there may be the same course credits different.

C. Insert an exception, such as the plan to open a new course, because no one elective, no number of key words, can only wait for someone to take courses and credits deposit.

D. Delete the exception, if the student has graduated, delete elective records from the current database. The curriculum and credit records cannot be saved if some of the new courses are not yet elective.

Reason: non-keyword attribute credit only the function relies on CNO, which is the credit partial dependency combination keyword (sno,cno) rather than full dependency.

Workaround: Divide into two relational patterns SC1 (Sno,cno,grade), C2 (Cno,credit). The new relationship includes two relational patterns, which are connected by the external key word CNO in the SC1, and then natural joins are needed to restore the original relationship.

Third paradigm (3NF): If all the u,f attributes in the relational schema R (s) do not exist for any candidate keywords to pass trust, then the relation R is called the third normal form.

Examples: for example S1 (sno,sname,dno,dname,location) Each attribute represents the school number respectively,

Name, department, line, department, address.

The keyword SNO determines each property. Because it is a single keyword, there is no partial reliance on the problem, it must be 2NF. But there must be a lot of redundancy in this relationship, and the students ' several attributes dno,dname,location will be stored repeatedly, inserted, deleted and modified will also produce similar cases.

Cause: There is a transitive dependency in the relationship. namely Sno-> DNO. But the DNO-> SNO does not exist, DNO-> LOCATION, so the key Liao SNO the LOCATION function decision is realized by the transmission dependence SNO->. In other words, Sno does not directly determine the non-primary attribute location.

Solution: No transitive dependencies can be left in each relational pattern.

Solution: Divided into two relationships S (Sno,sname,dno), D (dno,dname,location)

Note: There can be no external keyword DNO in the relationship s. Otherwise two relationships lose touch.

BCNF: If all of the properties of the relational schema R (U,F), including both primary and non-primary properties, do not pass any candidate keywords that depend on R, then the relationship R is called BCNF. or relational schema R, if each determinant contains keywords (rather than being included by the keyword), then the RCNF relational schema.

Example: Accessory Management Relationship Mode WPE (WNO,PNO,ENO,QNT) Separately table warehouse number, accessory number, employee number, quantity. Have the following conditions

A. A warehouse has multiple employees.

B. A worker works only in one warehouse.

C. One type of accessory in each warehouse is manned by a person, but one can manage several accessories.

D. Accessories of the same type can be divided into several warehouses.

Analysis: By the above PNO can not determine qnt, by the combination of attributes (Wno,pno) to determine the existence of functional dependencies (Wno,pno)-> ENO. Because one of the accessories in each warehouse is responsible for a person, and one can manage several accessories, so there is a combination of attributes (Wno,pno) to determine the person in charge, there is (wno,pno)-> ENO. Because a worker works only in a warehouse, there are Eno-> wno. Since one of the accessories in each warehouse is in the hands of a person, and a worker works only in one warehouse, there are (Eno,pno)-> QNT.

Find the Candidate keyword because (wno,pno)-> QNT, (wno,pno)-> ENO, so (wno,pno) can determine the entire tuple, which is a candidate keyword. According to Eno->wno, (eno,pno)->qnt, therefore (Eno,pno) can also determine the entire tuple for another candidate keyword. Property eno,wno,pno are all primary properties and only one qnt property is not. It is fully functional dependent on any candidate keyword and is directly dependent, so the relational pattern is 3NF.

Analyze the main properties. Because of Eno->wno, the primary attribute Eno is a wno determinant, but it is not a keyword in itself, it is just part of a combination of keywords. This results in a partial dependency of the main attribute wno on another candidate keyword (eno,pno), because (eno,pno)-> ENO but not in turn, and P->wno, so Eno,pno-> is also a transitive dependency.