Mysql database design three paradigm examples analysis

The primary key in the table is (student number, course), which can be expressed as (student number, course) -> (grade, course credits), indicating that all non-primary key columns (grade, course credits) are dependent on the primary key (student number, course). However, there is another dependency in the table: (Course)->(Course Credits). In this way, the non-primary key column 'Course Credits' depends on the partial primary key column 'Course', so the above table does not satisfy the second normal form.

We split it into the following two tables:

Student course selection table:

Course Information Sheet:

For the above two tables, the primary key of the student course selection table is (student number, course), and the primary key of the course information table is (course). All non-primary key columns in the table are completely dependent on the primary key. It not only conforms to the second paradigm, but also conforms to the third paradigm.

Let’s look at this student information sheet:

In the above table, the primary key is: (student number), all fields (name, gender, class, head teacher) are dependent on the primary key (student number), and there is no partial dependence on the primary key. So it satisfies the second normal form.

Third Normal Form (3NF)

To satisfy the third normal form (3NF), the second normal form (2NF) must be satisfied first. In short, the third normal form (3NF) requires that a database table does not contain non-primary key fields that are already included in other tables. That is to say, if the information of the table can be deduced, a separate field should not be designed to store it (use foreign key join if possible). Often, in order to meet the third normal form, we often divide a table into multiple tables.

That is, it satisfies the premise of the second normal form. If an attribute depends on other non-primary key attributes, and other non-primary key attributes depend on the primary key, then this attribute is indirectly dependent on the primary key, which is called transitive dependence on the primary attribute. The simple explanation is that a table can only store two layers of the same type of information at most.

Anti-three paradigms

A database without redundancy is not necessarily the best database. Sometimes, in order to improve operational efficiency and read performance, it is necessary to lower the paradigm standard and appropriately retain redundant data. The specific approach is: comply with the third normal form when designing the conceptual data model, and consider lowering the paradigm standard when designing the physical data model. Lowering the paradigm means adding fields, reducing the associations during query, and improving query efficiency, because the proportion of queries in database operations is much greater than that of DML. However, de-paradigmization must be moderate, and adjustments should be made based on the original satisfaction of the three paradigms.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.