The hottest database design paradigm in simple ter

  • Detail

Database design paradigm in simple terms

certain rules must be observed when designing relational databases. In particular, the database design paradigm now briefly introduces 1NF (the first paradigm), 2NF (the second paradigm), 3NF (the third paradigm) and BCNF, and the fourth and fifth paradigms will be introduced later. When you design a database, if you can conform to these paradigms, you are a master of database design

when designing a relational database, certain rules must be observed. In particular, the database design paradigm now briefly introduces 1NF (the first paradigm), 2NF (the second paradigm), 3NF (the third paradigm) and BCNF, and the fourth and fifth paradigms will be introduced later. When you design a database, if you can conform to these paradigms, you are a master of database design

first normal form (1NF): in each specific relationship r in the relationship pattern R, if each attribute value is the smallest data unit that cannot be subdivided, then R is the relationship of the first normal form. Example: for example, there are three ways to standardize a table composed of employee number, name and number (a person may have an office and a home number) into 1NF:

one is to repeatedly store employee number and name. In this way, the keywords can only be numbers

second, the employee number is the keyword, and the number is divided into two attributes: company and residence.

third, the employee number is the keyword, but each record is forced to have only one number

of the above three methods, the first method is the most undesirable, and the latter two cases are selected according to the actual situation

second normal form (2NF): if all non primary attributes in relation mode R (U, f) completely depend on any candidate keyword, relation R is said to belong to the second normal form

example: course selection relationship SCI (SnO, CNO, grade, credit), where SnO is the student number, CNO is the course number, grade is the score, and credit is the credit. According to the above conditions, the keywords are combined keywords (SnO, CNO)

there are the following problems in using the above relationship mode in the application:

a. data redundancy. Assuming that 40 students take the same course, the credits will be repeated 40 times

b. the update is abnormal. If the credits of a course are adjusted, the corresponding tuple credit values will be updated, and the credits of the same course may be different

c. abnormal insertion. For example, if a new course is planned, because no one elects and no student number keyword, the course and credits can only be saved after someone elects

d. delete exceptions. If the student has completed the course, delete the elective record from the current database. If the freshmen have not taken some courses, the course and credit records cannot be saved

cause: the non keyword attribute credit only depends on CNO when receiving user fault information, that is, credit partially depends on combined keywords (SnO, CNO) rather than completely

solution: it is divided into two relationship modes SC1 (SnO, CNO, grade) and C2 (CNO, credit). The new relationship includes two relationship patterns, which are connected through the foreign keyword CNO in SC1. When necessary, natural connection is performed to restore the original relationship

the third normal form (3NF): if all non primary attributes in relation mode R (U, f) do not have transitive trust on any candidate keyword, relation R is said to belong to the third normal form

example: for example, each attribute of S1 (SnO, sname, dno, dName, location) respectively represents student number,

name, Department, department name and department address

keyword SnO determines each attribute. Since it is a single keyword, there is no partial dependency. It must be 2NF. However, the preparation method of the Mooney viscometer sample and the sample conditioning before the experiment will certainly affect the large amount of redundancy of the Mooney value. Several attributes dno, dName and location of the students will be repeatedly stored, inserted, deleted and modified, which will also produce a situation similar to the above example

cause: it is caused by transitive dependency in the relationship. SnO -> dno. However, dno -> SnO does not exist. Dno -> location. Therefore, the key is that SnO determines the location function by relying on SnO -> location. That is, SnO does not directly determine the non primary attribute location

solution destination: no delivery dependency can be left in each relationship mode

solution: there are two relationships s (SnO, sname, dno) and D (dno, dName, location)

note: relationship s cannot have no foreign keyword dno. Otherwise the two relationships lose contact

bcnf: if all attributes (including primary and non primary attributes) of the relationship mode R (U, f) do not pass any candidate keywords that depend on R, then the relationship r belongs to BCNF. Or relationship mode R. if each determinant contains keywords (not keywords), the relationship mode of rcnf

example: the WPE (wno, PNO, Eno, qnt) of the parts management relationship mode respectively lists the warehouse number, parts number, employee number, and quantity. There are the following conditions

a. a warehouse has multiple employees

b. an employee works in only one warehouse

c. a special person is responsible for one type of accessories in each warehouse, but one person can manage several accessories

d. accessories of the same model can be placed in several warehouses

analysis: qnt cannot be determined from the above PNO, but it is determined by the combination attribute (wno, PNO), and there is functional dependency (wno, PNO) -> eno. As a part in each warehouse is in the charge of a special person, and one person can manage several parts, the person in charge can be determined only when there are combination attributes (wno, PNO) -> eno. Because an employee only works in one warehouse, there is eno -> wno. As a part in each warehouse is in the charge of a specially assigned person, and an employee works in only one warehouse, there are (Eno, PNO) -> qnt

look for candidate keywords because (wno, PNO) -> qnt, (wno, PNO) -> eno. Therefore, (wno, PNO) can determine the entire tuple and is a candidate keyword. According to eno->wno, (Eno, PNO) ->qnt, so (Eno, PNO) can also determine the entire tuple as another candidate keyword. Attributes Eno, wno and PNO are all primary attributes, and there is only one non primary attribute qnt. It is fully functional and directly dependent on any candidate keyword, so the relationship pattern is 3NF

the higher the hardness value, analyze the main attribute. Because eno->wno, the primary attribute eno is the determinant of wno, but it is not a keyword itself, but only a part of the combined keyword. This causes the primary attribute wno to partially depend on another candidate keyword (Eno, PNO). Because (Eno, PNO) -> eno is not true in reverse, and p->wno, so (Eno, PNO) -> wno is also a transitive dependency

although there is no transfer dependency of non primary attributes on candidate key words, there is a transfer dependency of primary attributes on candidate keywords, which will also cause trouble. For example, a new employee is assigned to work in the warehouse, but is temporarily in the internship stage and is not independently responsible for the management of some accessories. Unable to insert into the relationship because a part of the PNO of the keyword is missing. In addition, if a person changes to be responsible for safety regardless of accessories, the employee will also be deleted when deleting accessories. The waiting time is too long tension machine

solution: divide into management EP (Eno, PNO, qnt), and the keyword is (Eno, PNO) work EW (Eno, wno). The keyword is eno

disadvantage: the retention of function dependency after decomposition is poor. In this example, due to decomposition, functional dependencies (wno, PNO) -> eno are lost, which destroys the original semantics. It doesn't show that a part in each warehouse is in the charge of a special person. It is possible that a part is managed by two or more people at the same time. Therefore, the decomposed relational schema reduces the partial integrity constraints

a relationship is decomposed into multiple relationships. To make the decomposition meaningful, the minimum requirement is that the original information is not lost after decomposition. This information includes not only the data itself, but also the mutual constraints between the data represented by functional dependencies. The goal of decomposition is to achieve a higher level of normalization, but two issues must be considered at the same time: lossless connectivity and maintaining functional dependency. Sometimes it is impossible to achieve both lossless connectivity and complete functional dependency. Trade offs need to be made as needed

The four paradigms from

1nf to BCNF have the following relationships:

bcnf includes 3NF includes 2NF includes 1nf


purpose: the purpose of standardization is to make the structure more reasonable, eliminate storage exceptions, minimize data redundancy, and facilitate insertion, deletion and update

principle: follow the principle of "one thing, one place" of concept simplification, that is, a relationship pattern describes an entity or a relationship between entities. The essence of norms is the simplification of concepts

method: decompose the relational schema projection into two or more relational schemas

requirements: the decomposed relationship pattern set should be "equivalent" to the original relationship pattern, that is, the original relationship can be restored without losing information through natural connection, and the reasonable connection between attributes can be maintained

note: the decomposition of a relational schema node can result in different sets of relational schemas, that is, 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. Its fundamental goal is to save storage space, avoid data inconsistency, improve the operation efficiency of relationships, and meet application requirements. In fact, not all modes are required to reach BCNF. Sometimes it may be more convenient to query data by deliberately retaining partial redundancy. Especially for those database systems with low update frequency and high query frequency

in the relational database, besides the function dependency, there are also the problems of multi value dependency and connection dependency, which puts forward the fourth normal form, the fifth normal form and other higher-level standardization requirements. Here, we'll talk about it later

dear friends, what do you think after reading it? In fact, any book on the basic theory of databases will talk about these things. Considering that many friends are monks halfway to become databases. I am looking for a book to copy. If you have any questions, don't ask me. Go and read a book on relational database theory. It may be of great help to you. It means that the above is the basic theory. Please think about it. Have you ever considered following the above paradigms when you are designing a database? Have you ever thought about how many paradigms are violated when you are not doing a good job in database design

in the database design I have seen, few people have done well in line with the above paradigms. Generally speaking, everyone can follow the first paradigm. Few people fully follow the second and third paradigms. Those who follow the second and third paradigms must be experts in database design. BCNF paradigm has few opportunities to appear and will destroy integrity. You can ignore it when designing. Of course, you can solve its shortcomings in Oracle through triggers. In the future, when we work together on design, we also hope that you will follow the above paradigms. (end)

Copyright © 2011 JIN SHI