Soft Test details --- three paradigms, soft test --- paradigm

Soft Test details --- three paradigms, soft test --- paradigm

Relational databases are widely used nowadays. The design of relational databases is the process of organizing and structuring data. We can easily process small-scale databases, but as the database grows, we will find that the SQL statements used to manipulate the Database become clumsy and complex. Worse, data may be incomplete and inaccurate. Therefore, it is necessary to make the data design more compliant. How can we make our database more standardized? In the database world, we have summarized a total of five paradigms, three of which are commonly used. Today, let's simply summarize the three paradigms, the content of the three paradigm is also mandatory in the soft examination. I hope to help my friends. The small editor will first briefly introduce the concept of each paradigm, next, we will give a small example to illustrate each paradigm.

First paradigm

First, let's look at the definition of the first paradigm: (1NF) indicates that each column in the database table is an inseparable basic data item, and the same column cannot have multiple values, that is, an attribute in an object cannot have multiple values or duplicate attributes. Let's look at an example:

Example: The employee number, name, and phone number form a table (a person may have an office phone number and a home phone number), and how to standardize it as 1NF. Let's analyze this example. The employee number, name, and phone number form a table. A person may have an office phone number and a home phone number. We need to standardize this table as the first paradigm, there are three methods. First, we need to propose that this table does not conform to the first paradigm. Why is his phone number assigned to another subitem, nie, the telephone number is divided into office phone numbers and home phone numbers. In this way, the telephone number is not an inseparable item. We have three methods to normalize this table into the first paradigm, the first is to repeat the employee ID and name, that is, each person's phone number occupies a record. In this way, the keyword can only be a phone number. The second method is to use the employee number as the keyword. The telephone number is divided into the unit number and residential phone number. The third method is keyword, but each record can only have one phone number. Three methods can be standardized as the first paradigm. The first method is the least desirable, the last two cases can be selected based on the actual situation. Let's take a look at an example of the first paradigm of standardization, as shown in:

The data table above has two fields: department name and number of senior titles, and the number of senior titles is divided into two, obviously not in line with the first paradigm, because there are split data items, if we want to standardize it, we can split the data items and divide the number of senior titles into two attributes: Professor and Associate Professor, as shown below:

Second paradigm

Let's look at the concept of the second paradigm: 2NF. When and only when entity E is the first paradigm, and each non-primary attribute fully depends on the primary key (not completely dependent ), entity E is the second paradigm. Let's look at an example:

In the Course Selection relation SC1 (SNO, CNO, GRADE, CREDIT), SNO indicates the student ID, CNO indicates the course number, GRADEGE indicates the score, and CREDIT indicates the CREDIT, the keywords of the above conditions are combined keywords (SNO, CNO). Let's analyze the problem above. There is a course selection relation sc1, which contains four fields, the primary key of this relationship is the combination of sno and cno, which means that this combination can determine the score and credits. We write all functions dependent on sno cno --- GRADE/CREDIT. In addition, is there anything else? We can find that GREDIT is a credit, but the credits of a course can be determined as long as the course number determines the credit, because the credits of the course are fixed, therefore, there is also a function dependency. cno can determine the credit. Therefore, some function dependencies exist here, because some of the primary keys can determine the credit attribute, thus generating some function dependencies, in practical application, such a link has problems as follows:

Let's take a look at an example, as shown below:

Analyze the internal structure of the data. This table contains four attributes. The Warehouse Number and device number may be the primary key in this table. We found that the Warehouse number has repeated elements, therefore, she cannot be the primary key, and the device number also exists. Therefore, she can only combine the two as the primary key. We find that there are no repeated data items, and each item can determine the quantity and location, therefore, we have selected these two combinations as the primary keys of the data table. We are looking at other mappings to determine whether the second method is satisfied and whether there is an association between the Warehouse number and the location, for example, if wh1 is located in Beijing, we can get a function dependency. The Warehouse number corresponds to the location and some function dependencies are generated, we have just determined that the combination of the Warehouse number and the device number is the primary key. At this time, the Warehouse number can be used to determine the location number, so it does not meet the second paradigm.

Third paradigm

First, let's take a look at the concept of the third paradigm: When and only when entity E is the second paradigm and there is no non-primary attribute transfer dependent on code in Entity E, entity E is the third paradigm. Let's look at an example:

For example, each attribute of S1 (SNO, SNAME, DNO, DNAME, and LOCATION) represents the student ID, name, Department, Department name, and system address respectively. The SNO keyword determines each attribute. Because it is a single keyword and there is no part of the dependency, it must be 2NF, but this relationship will have a lot of redundancy, the attributes DNO, DNAME, and LOCATION of the student's system are retained, which are similar to those of the previous example during insertion, deletion, and modification. Let's analyze this problem. If the primary key of a relational pattern is a single keyword rather than a combination of multiple keywords, this relational pattern is at least the second paradigm, this is because it is a single attribute, so there is no function dependency.
Cause of the above error: There is a dependency in the link, that is, SNO --> DNO, but DNO --> SNO does not exist, DNO --> LOCATION, therefore, the SNO keyword determines the LOCATION function through the function dependency SNO --> LOCATION. That is to say, SNO does not directly determine the non-primary attribute LOCATION. The reason is that there is a transfer dependency in the relationship. For example, in such a data table, sno determines dno. This is because the non-master attribute depends on the student's student ID after we know it, we can know the student ID of his department, and the student ID, but we know that a department number cannot determine the student's student ID, because there are multiple people in a department, at the same time, the system number can determine the location of the system. In this way, some conditions are combined to meet a condition for passing function dependencies, so a function dependency is formed between sno and loction, that is to say, Sno does not directly determine the location of the non-primary attribute, so that this relational mode does not conform to the third paradigm. Now we are looking for a solution to eliminate data redundancy and possible errors during insertion, deletion, and modification, all the dependencies of the passing functions must be eliminated. We can split the relational mode into two, which should look like this: S (SNO, SNAME, DNO ), the relationship modes D (DNO, DNAME, and LOCATION) both conform to the third paradigm, but we must note that the relationship S cannot contain the foreign keyword DNO, otherwise, the relationship between the two links will be lost. In the relational database, in addition to function dependencies, there are also multi-value Attributes and connection dependencies, which puts forward the fourth paradigm, higher standardization requirements such as the fifth paradigm. Let's take a look at an example, as shown below:

Let's analyze the functional dependencies in this warehouse table with four fields. We can see that there is a correlation between a province and a city, if I know the city, I can determine the province. This is the only thing that is determined. However, if we know the Warehouse number, we need to know the city where the warehouse is located, but you know the city where the warehouse is located. You do not necessarily know the Warehouse number, because the city where the warehouse is located has three records, but the city of Wuhan has three warehouses. In this case, the city where the data is located is not sure enough. Based on these conditions, a transfer function dependency is generated, that is, the province where the data is located transmits the dependent function warehouse number, to normalize it into a third paradigm, we need to break this situation. What should we do with nie, as shown below:

Note:This blog post briefly introduces the content of the three paradigm. For the content of the three paradigm, the editor has already met in the principle of the self-testing database system, however, the three paradigms have always been confusing. In this soft exam, I once again met the three paradigms of the database. I will summarize the relevant knowledge into a blog post, hope you can help your friends in need, but you can still take the test ......