The paradigm of database design

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.

Although there is no non-main attribute to the candidate key Liao's transmission dependence, but there is the main attribute to the candidate keyword transmission dependence, also can bring trouble. If a new employee assigned to the warehouse work, but temporarily in the internship stage, not independently responsible for certain parts of the management tasks. cannot be inserted into the relationship because it is missing a part of the keyword pno. And if a person changed to no matter the accessories to be responsible for security, the removal of accessories at the same time the workers will also be deleted.

Solution: Divided into Management EP (ENO,PNO,QNT), the key word is (eno,pno) work EW (ENO,WNO) Its keyword is ENO

Disadvantage: The retention of function dependencies is poor after decomposition. In this case, because of the decomposition, the function dependency (wno,pno)-> ENO is lost, thus destroying the original semantics. Does not reflect a part of each warehouse in the hands of the person responsible. It is possible to have a component managed at the same time by two or more than two people. Therefore, the relational schema after the decomposition reduces the partial integrity constraints.

A relationship breaks down into multiple relationships to make the decomposition meaningful, and the minimum requirement is to decompose without losing the original information. This information includes not only the data itself, but also the mutual constraints between the data represented by the function dependencies. The goal of decomposition is to achieve a higher level of normalization, but the decomposition must also consider two issues: lossless connectivity and retention function dependencies. Sometimes it is not possible to achieve both lossless connectivity and functional dependencies completely. Needs to be weighed as needed.

1NF until the bcnf four paradigms have the following relationships:

The bcnf contains a 3NF containing 2NF containing 1NF

Summary:

Objective: The purpose of normalization is to make the structure more reasonable, eliminate storage anomaly, make data redundancy as small as possible, easy to insert, delete and update

Principle: To follow the concept of "one thing, one ground" principle, namely a relational model to describe a relationship between an entity or an entity single. The essence of the norm is the single of the concept.

Methods: The relational schema projection is decomposed into two or more relational patterns.

Requirements: The set of relational patterns after decomposition should be "equivalent" to the original relational pattern, that is, the original relationship can be restored without loss of information through natural joins, and a reasonable relationship between attributes is maintained.

Note: The decomposition of a relational pattern knot can get a set of different relational schemas, i.e. the decomposition method is not unique. The minimum redundancy requirement must be realized on the premise that the decomposed database can express all the information of the original database. The basic goal is to save storage space, avoid data inconsistency, improve the operational efficiency of the relationship, and meet the application requirements. In fact, it does not necessarily require that all modes reach bcnf. Sometimes intentional retention of partial redundancy may be more convenient for data queries. Especially for those database systems with low frequency of updates and extremely high query frequency.

In the relational database, in addition to the function dependence, there are many value dependencies, the connection dependency problem, thus proposed the fourth normal form, the five paradigm and so on higher level of standardization requirements. Here, we'll talk about it later.

Dear friends, What do you think after that, in fact, any book on the basic theory of the database will say these things, considering that many netizens are the halfway monk, to do the database. Special find a book big copy, you have any questions, do not ask me, I have to find a book of relational database theory to see it, perhaps, for you greatly help. Said that is the basic theory of things, please think about it, you do database design when you have not considered the following several paradigms, there is no good in the database design, think about it, compared to the above, in the end is a violation of the first few paradigm?

I have seen the database design, very few people do very well in accordance with the above several paradigms, generally speaking, the first paradigm everyone can comply with, fully comply with the second third form of people are very few, abide by the person must be the master of the design database, bcnf paradigm appears less opportunities, and will destroy integrity, You can do the design without considering it, of course, in Oracle can use triggers to solve their shortcomings. When we do design together, we also hope that we will follow these paradigms.