Relational pattern Paradigm of database

There are 6 main paradigms: The first paradigm (1NF), the second paradigm (2NF), the third paradigm (3NF), the Badesco paradigm (BCNF), the fourth normal form (4NF), and the V Paradigm (5NF), which is stricter than a requirement in order from left to right. To conform to a certain paradigm must also satisfy all the paradigms in front of it. The database design of the general project reaches 3NF, and the redundancy can be appropriately increased according to the specific situation, without dogmatic adherence to the so-called specification.

The correlation between the four paradigms is shown in the comparison of the function dependencies allowed by the paradigm.

1, the first normal form (1NF) No duplicate columns

The so-called First paradigm (1NF) refers to the fact that each column of a database table is an indivisible basic data item and cannot have multiple values in the same column, that is, an attribute in an entity cannot have multiple values or cannot have duplicate properties . If duplicate attributes are present, you may need to define a new entity, which is composed of duplicate attributes, and a one-to-many relationship between the new entity and the original entity. In the first normal form (1NF), each row of a table contains only one instance of information. In short, the first paradigm is a column with no duplicates.

In any relational database, the first paradigm (1NF) is the basic requirement for relational schemas, and a database that does not meet the first normal form (1NF) is not a relational database. In any current relational database management system (DBMS), it is not possible to make a database that does not conform to the first paradigm because these DBMS do not allow you to divide a column of a database table into two or more columns. Therefore, it is impossible for you to design a database that does not conform to the first paradigm in your existing DBMS.

For example:

A student table student (Stuno,stuname,age,age,sex) is not in accordance with the first paradigm because there is a repeating column of the Age property. The student (stuno,stuname,age,sex) after removing the repeating column age is in accordance with the first paradigm.

2. The second normal (2NF) attribute is completely dependent on the primary key [elimination of partial child function dependencies]

The second paradigm (2NF) is established on the basis of the first paradigm (1NF), i.e. satisfying the second normal form (2NF) must first satisfy the first paradigm (1NF). The second normal form (2NF) requires that each instance or row in a database table must be divided by a unique region. For the implementation of the distinction, it is common to add a column to the table to store unique identities for each instance. For example, the Employee Information table has the employee number (emp_id) column added because each employee's employee number is unique, so each employee can be uniquely differentiated. This unique attribute column is called the primary key or primary key, and the main code.

The second normal form (2NF) requires that the attributes of an entity depend entirely on the primary key . The so-called full dependency is the inability to have a property that depends only on the primary key , and if so, this part of the property and the primary key should be separated to form a new entity, and the new entity is a one-to-many relationship with the original entity. For the implementation of the distinction, it is common to add a column to the table to store unique identities for each instance. In short, the second paradigm is that properties depend entirely on the primary key.

There may not be a single primary keyword here, in some cases there is a federated primary key, or there are multiple properties for the primary key.

For example:

Take the student elective course as an example, each student can choose a course, and have the result of this course, then if this information is placed in a table Stugrade (stuno,stuname,age,sex,courseno,coursename,credit , score).

If we don't look closely, we'll think that the primary key of this table is Stuno, but when we see the last score attribute, think about where there is a student's score if there is no course information. So the primary key of this table is a federated primary Key (Stuno,corseno), which can uniquely determine the score attribute. Then look at other information, such as Stuname only need to Stuno to be able to determine, coursename only need to Courseno can be uniquely determined, so there is a partial dependence , not conform to the second paradigm. If the student's course achievement information satisfies the second paradigm, it is necessary to split the table into multiple tables, a student table studnet (stuno,stuname,age,sex), a curriculum course (Courseno,coursename, Credit), as well as the last Student Course score Table Stugrade (stuno,courseno,score).

3. The third paradigm (3NF): Properties do not depend on other non-principal properties [eliminate transitive dependencies]

Definition: If the relationship mode R is 2NF and each non-primary attribute in R does not pass a candidate keyword that relies on R, then R is called the third normal, and précis-writers is 3NF.

Satisfying the third normal form (3NF) must first satisfy the second normal form (2NF). In short, the third paradigm (3NF) requires that a database table not contain non-primary key information already contained in other tables .

Example 3:

Each employee has a department, if there is an employee information sheet employee (emp_id,emp_name,emp_age,dept_id,dept_name,dept_info).

The primary key for this Employee information table is emp_id, because this property uniquely identifies all other attributes, such as knowing the employee number emp_id, will be able to know the employee's name, department number, department name, and department description. So here dept_id is not the main attribute, but the non-primary property. However, we can also find that the two properties of Dept_name,dept_info can be determined by dept_id, the non-primary attribute, that is, Dept_name relies on dept_id, and dept_id relies on emp_id, so there is a transitive dependency . And we can see that one of the obvious drawbacks of transitive dependencies is that data redundancy is very serious.

So how to solve the transfer dependency problem, in fact, it is very simple, we just need to dept_name,dept_info this even a property to delete it, that is, employee (EMP_ID,EMP_NAME,EMP_AGE,DEPT_ID), Then create a departmental table dept (dept_id,dept_name,dept_info).

So if you want to search for an employee's department information Dept_info, can be implemented through a database connection, the query statement is as follows:

where e.dept_id=d.dept_id

4. Boyce-codd Paradigm (BCNF)

Definition: If the relationship mode R is 1NF, and each function in R is dependent on X→y (yïx), x is the Super keyword of R, then R is the boyce-codd paradigm, précis-writers is bcnf.

From the definition of bcnf, it is clear that the following conclusions can be drawn:

(1) All non-primary attributes are fully functional dependent on the key;

(2) All primary attributes are fully functional dependent on the key that does not contain it;

(3) No attribute full function relies on any combination of properties of a non-key.

Unlike the definition of 2NF,3NF, the definition of BCNF is directly based on the 1NF. But essentially bcnf is an improved form of 3NF. 3NF only considers the dependency of the non-primary attribute on the key, bcnf the dependency of the main attribute on the key. The BCNF requirements are higher than the 3NF requirements. If the relationship mode R is bcnf, then R must be 3NF, and conversely, it is not necessarily true.

"Example 2.43" continued precedent 2.42 (number s#, course number C #, score G, instructor TN, teacher specialty TS), determine whether two 3NF relationship mode R3={c#,tn},r4={tn,ts} is bcnf.

Solution: In the relational pattern R3 has the function dependence c#→tn, the determinant Factor C # is the R3 key;

In the relational pattern R4, there is a function dependent tn→ts, and the determining factor TN is the key of R4;

R3,R4 all satisfy the definition of bcnf, so the two relational patterns are bcnf.

"Example 2.44"Accessories Management Relationship Mode WPE (WNO,PNO,ENO,QNT) Table warehouse number, part number, employee number, number. There are the following conditions a. A warehouse has multiple employees. B. An employee works only in one warehouse. C. One type of accessory is responsible for each warehouse, but one person can manage several parts. D. The same type of accessories can be divided into several warehouses。 Analysis: By the above PNO can not be determined qnt, by the combination of attributes (Wno,pno) to determine, there is a function dependency (wno,pno), ENO. Since one of the accessories in each warehouse is in the hands of someone, and a person can manage several accessories, there are combination attributes (Wno,pno) to determine the person in charge, (wno,pno)-ENO. Because an employee only works 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 only works 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, is a candidate keyword. According to Eno->wno, (eno,pno)->qnt, so (Eno,pno) can also determine the entire tuple, as another candidate keyword. The Eno,wno,pno property is the primary property, and there is only one non-primary property qnt. It is fully functional dependent on any of the candidate keywords and is directly dependent, so the relational pattern is 3NF. Analyze the main attribute. Because Eno->wno, the main attribute ENO is the determinant of WNO, but it is not a keyword in itself, it is only part of the combination keyword. This results in a partial dependency of the main attribute WNO on the other candidate keyword (eno,pno), because (eno,pno)-ENO but not in turn, and P->wno, therefore (eno,pno), WNO is also a transitive dependency. Although there is no non-main attribute to the candidate critical Liao's transitive dependence, but there is a primary attribute to the Candidate keyword transfer dependency, also can cause trouble. such as a new employee assigned to the warehouse work, but temporarily in the internship stage, there is no independent responsibility for certain parts of the management tasks. cannot be inserted into the relationship because a part of the keyword is missing pno. If a person is changed to be responsible for safety regardless of the accessory, then the employee will be removed when the accessory is removed. Workaround: Divided into Management EP (ENO,PNO,QNT), the keyword is (eno,pno) work ew (ENO,WNO) whose keyword is ENO disadvantage: The function dependency is poor after decomposition. In this case, due to decomposition, function dependency (wno,pno)---ENO is lost, thus destroying the original semantics. No one part of each warehouse is in the hands of the person. It is possible for a part to be managed by two or more than two people at the same time. Therefore, the relational pattern after decomposition reduces partial integrity constraints.

Note the point:

1. Database connectivity can result in a fraction of the performance penalty
2. It's not that the database paradigm gets higher.
3. Sometimes there is a tradeoff between data redundancy and paradigm, and in the actual database development process, a portion of data redundancy is often allowed to reduce database connectivity.

Relational pattern Paradigm of database