triple paradigm (math.)
1NF: Field is not separable.
2NF: There is a primary key, and non-primary key fields depend on the primary key.
3NF: Non-primary key fields cannot depend on each other.
Explanation.
1NF: Atomicity Fields cannot be subdivided, otherwise it is not a relational database.
2NF: Uniqueness A table describes only one thing.
3NF: each column is directly related to the primary key, no passing dependencies; the
First Paradigm (1NF)
That is, the columns of the table have atomicity, can not be decomposed, that is, the information of the columns can not be decomposed, as long as the database is a relational database (mysql/oracle/db2/informix/sysbase/sql server), it automatically meets the 1NF. each column of the database table is an indivisible atomic data items, and can not be a collection, array, record, etc. non-atomic data items. If an attribute of an entity has more than one value, it must be split into different attributes. It is commonly understood that a field stores only one piece of information.
Relational databases: mysql/oracle/db2/informix/sysbase/sql server Non-relational databases: (Characteristics: object-oriented or collection) NoSql databases: MongoDB/redis (Characteristics: document-oriented)
Second Paradigm (2NF)
The Second Paradigm (2NF) builds on the First Paradigm (1NF), i.e., to satisfy the Second Paradigm (2NF) you must first satisfy the First Paradigm (1NF). The second paradigm (2NF) requires that each instance or row in a database table must be uniquely distinguishable. In order to achieve the distinction usually requires us to design a primary key to achieve (here the primary key does not contain business logic).
That is, the first paradigm premise is satisfied and the non-conformity to the second paradigm occurs only when there are multiple primary keys. For example, if there are two primary keys, there cannot exist such an attribute which depends on only one of the primary keys, which is a non-conformity to the second paradigm. The common understanding is that any field depends only on the same field in the table. (Involves splitting the table)
See the student selection form below:
| student number | academic program | grades | course credit |
|---|---|---|---|
| 10001 | math | 100 | 6 |
| 10001 | multilingualism | 90 | 2 |
| 10001 | English (language) | 85 | 3 |
| 10002 | math | 90 | 6 |
| 10003 | math | 99 | 6 |
| 10004 | multilingualism | 89 | 2 |
The primary key in the table is (number, course), which we can represent as (number, course) -> (grade, course credit), which means that all non-primary key columns (grade, course credit) depend on the primary key (number, course). However, there is another dependency in the table: (course) - > (course credits). Thus the non-primary key column 'Course Credits' depends partially on the primary key column 'Course', so the above table does not satisfy the second paradigm.
We split it into 2 tables as follows:
Student Selection Form:
| student number | academic program | grades |
|---|---|---|
| 10001 | math | 100 |
| 10001 | multilingualism | 90 |
| 10001 | English (language) | 85 |
| 10002 | math | 90 |
| 10003 | math | 99 |
| 10004 | multilingualism | 89 |
Course Information Sheet:
| academic program | course credit |
|---|---|
| math | 6 |
| multilingualism | 3 |
| English (language) | 2 |
So the 2 tables above, the Student Selection table has a primary key of (student number, course), the Course Information table has a primary key of (course), and all the non-primary key columns in the table are completely dependent on the primary key. Not only does it conform to the second paradigm, it also conforms to the third paradigm.
Look again at a student information sheet like this:
| student number | name and surname | distinguishing between the sexes | classes or grades in school | teacher in charge of a class |
|---|---|---|---|---|
| 10001 | John Doe | male | first class | Xiao Wang (1935-1978), female revolutionary and martyr |
| 10002 | the fourth child in the family | male | first class | Xiao Wang (1935-1978), female revolutionary and martyr |
| 10003 | Wang May (1905-1975), Mao *'s fifth wife | male | second class | Xiao Li |
| 10004 | John Doe | male | second class | Xiao Li |
In the above table, the primary key is: (student number), all the fields (name, gender, class, class teacher) are dependent on the primary key (student number) and there is no partial dependency on the primary key. So it is satisfying the second paradigm.
Third Paradigm (3NF)
To satisfy the third paradigm (3NF) you must first satisfy the second paradigm (2NF). In short, the third paradigm (3NF) requires that a database table not contain non-primary key fields that are already contained in other tables. That is, the information in the table, if it can be deduced, should not be designed to store a separate field (foreign key join as much as possible, use foreign key join). Many times, we tend to split a table into multiple tables to fulfill the third paradigm.
That is, to satisfy the second paradigm premise, if an attribute depends on other non-primary key attributes, which in turn depend on the primary key, then the attribute is indirectly dependent on the primary key, which is called pass-dependent on the primary attribute. The common interpretation is that a table can only hold at most two levels of the same type of information.
paradigm of reverse trinomialism (math.)
A database without redundancy may not be the best database, and sometimes it is necessary to lower the paradigm standard and retain redundant data appropriately in order to improve operational efficiency and read performance. The specific approach is: in the conceptual data model design to comply with the third paradigm, reduce the paradigm standard work to the physical data model design to consider. Lowering the paradigm is to increase the fields, which reduces the association when querying and improves the querying efficiency, because the proportion of querying in the operation of the database is much larger than the proportion of DML. But anti-paradigm must be moderate, and in the original has to meet the three paradigms on the basis of the adjustment.
This is the whole content of this article.