Three Paradigms of database

If you do not want to see the theoretical number, but also want to revisit the database knowledge, but also the Japanese anime fans, you can look at:

The idea of combining comics and expertise is very creative and interesting to read.

The database is designed by E-r,entity-relationship model, according to the specific relationship.

A one-to-many, one clerk to multiple customers. If there is only one clerk.

Many to one, in turn.

Many to many, multiple employees to multiple customers.

After determining the relationship, it is necessary to normalization, standardize and divide the three paradigms.

First look at non-normalized:

You will find that there are two more columns in a kingdom, and you will not be doing this when you design the database.

What is the use of normalization? You change the price of the same fruit, it is necessary to change all, if the normalization, the fruit separated separately, it will not cause contradictions.

First paradigm:

In the first paradigm, goods and orders are separated. If the price of Apple changes, you do not have to change the prices of the non-canonical table containing apples.

Each column is indivisible, removing the duplicate record in the non-canonical style (the record is a row).

However, the problem is that if the product has not been sold, then the number is 0, there is no report code, but we do need the product name, unit price and product code.

So, continue to look at the second paradigm.

Second paradigm:

The name of the commodity, the unit price, and the commodity code are made into a single table, which determines the value of the other two fields through the Commodity encoding column, which becomes a function dependency (functionally dependent) that determines the values of other columns by the primary key.

Look back at the report code, export Country Code This table, the second paradigm.

The report code determines the export country code, determines the export country code, and then determines the name of the exporter.

The problem is that, while the export country code can confirm other values through a column, the so-called commodity codes do not exist if the exporting countries have not imported them at all.

The third paradigm:

Other column values can only be determined by the primary key, and in the second paradigm, the export country code can be determined by the report encoding, and the exporter country's name is determined . This indirectly determines the value of another column by the value of one column, we become transitive dependent, and the third paradigm removes the transitive dependency.

In fact, the report code, product code, quantity of this table, and product code, commodity name, unit price This table not only satisfies the second paradigm, but also satisfies the third paradigm.

However, in the second paradigm, the report code, the port country code, the name of the exporter's table does not satisfy the third paradigm, because there is transitive dependence, after the segmentation to meet the third paradigm.

To summarize:

First paradigm: Columns are not divided and there are no duplicate records in the row.

Second paradigm: The value of other columns is determined by the primary key, and the values of other columns are determined by the other columns, and there is a transitive dependency.

The third paradigm: only through the primary key to determine the value of other columns, there is no transitive dependency, in fact, the third paradigm is a special second paradigm.

Three Paradigms of database