My views on Database paradigm

When I went to school, I talked about the database paradigm in the database. Now I suddenly want to review it. I checked it online and found that it was clearer to myself.

First, let's quote a paragraph in Baidu Encyclopedia:

The relationships in relational databases must meet certain requirements, that is, they must meet different paradigms.

Currently, relational databases have six paradigms: 1nf, 2nf, 3nf, 4nf, and 5nf) and the sixth paradigm (6nf ). The first paradigm (1nf) meets the minimum requirements ). The second Paradigm (2nf) that meets more requirements on the basis of the first paradigm, the restParadigm and so on. Generally, databases only need to satisfy the 3nf.

 

Good guy, there are actually six paradigms-Khan. I used to only know the first, second, and third paradigm. Well, it seems that one, two, three are enough to solve the problem. Let's start with one, two, three:

No repeated column in the first paradigm (1nf)

The first paradigm (1nf) means that each column in the database table is an inseparable basic data item. The same Column cannot contain multiple values, that is, an attribute in an object cannot have multiple values or duplicate attributes. If duplicate attributes exist, you may need to define a new object. A new object consists of duplicate attributes.Yes. In the first paradigm (1nf), each row of the table contains only information of one instance. In short, the first paradigm is a non-repeated column.

Note: In any relational database, the first paradigm (1nf) is the basic requirement for the relational model. databases that do not meet the first paradigm (1nf) are not relational databases.

From the above sentence, we can see that as long as the relationship can be saved into the relational database, it is considered to satisfy the first paradigm. For example, if a field is an address, many people on the Internet say this does not meet the first paradigm of the database, I think this is a misunderstanding of the original intent of the first paradigm. Under what circumstances will this magic first paradigm be violated?

 

ID	Name	Address
1	Jim	No. XX, Hongkou District, Shanghai
2	Hanson	Beijing	No. XX, Chaoyang District

Can I store the above tables in a relational database? Obviously, this is not the case. Therefore, the first paradigm is designed to prevent the above situations.

 

2nf attribute

Completely dependent on the primary key[Remove some function dependencies of non-primary attributes on the primary code]

The second Paradigm (2nf) is established on the basis of the first paradigm (1nf), that is, to satisfy the second Paradigm (2nf) must satisfy the first paradigm (1nf) first ).

The second Paradigm (2nf) requires that the attributes of an object fully depend on the primary keyword. The so-called full dependency refers to the fact that there cannot be an attribute that only depends on a part of the primary keyword. If so, this attribute and this part of the primary keyword should be separated to form a new entity, the relationship between the new object and the original object is one-to-multiple. To implement differentiation, you usually need to add a column to the table to store the unique identifier of each instance. In short, the second paradigm is that attributes fully depend on the primary key.

It can be seen from the above that if your table has only one primary key, it must satisfy the second paradigm, because there is no limitation on some function dependencies.

Suppose there is an entity table as follows:

In this example, the departmentdescription field is only dependent on the departmentname field and has no relationship with the employeeid field. Therefore, it violates the second paradigm. How can this problem be changed?

3nf attributes

Independent from other non-primary attributes[Eliminate transfer dependencies]

The third paradigm (3nf) must satisfy the second Paradigm (2nf) first ). The third paradigm (3nf) requires that a database table do not contain information about non-primary keywords already contained in other tables.In short, the third paradigm is that attributes do not depend on other non-primary attributes.

In the preceding table, jobdescription is dependent on the job field, so the third paradigm is not satisfied. The following changes should be made:

The success of the three major database paradigms has been achieved. As for the rest, the fourth, fifth, and sixth are clouds ......

To put it down, the benefits of the paradigm are to reduce data redundancy and make the structure clearer, so that it is easy to maintain in the future. This is the most important thing, if your database is poorly designed, some minor changes won't be completed or you have to pay a lot, which is not worth it.

I think we still have to look at what kind of paradigm should be adopted.Business RequirementsIf a more rigorous system has a high requirement on data, the level of the corresponding paradigm will be improved. However, if it is a small system, the second paradigm will actually work, there is no need to pursue a high paradigm, because enterprises still have to consider costs and other factors, especially as in the above exampleWhen a field passes dependencies, it can be ignored unless it is important.