First, Second, Third, understanding of the BC paradigm

The design paradigm (paradigm, database design paradigm, and database design paradigm) is a set of relational patterns at a certain level. Database construction must follow certain rules. In relational databases, this rule is a paradigm. 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, and the other paradigms are similar. Generally, databases only need to satisfy the 3nf. The following is an example of the first paradigm (1nf), the second Paradigm (2nf), and the third paradigm (3nf ).
In the process of creating a database, normalization is the process of converting it into some tables. This method can make the results obtained from the database clearer. This may cause duplicate data in the database, resulting in the creation of redundant tables. Normalization is a refined process after identifying the data elements and relationships in the database, and defining the required tables and projects in each table. The following is an example of fanization: Customer item purchased purchase price Thomas shirt $40 Maria tennis shoes $35 Evelyn shirt $40 Pajaro trousers $25 if the table above is used to save the price of the item, if you want to delete a customer, you must delete a price at the same time. To solve this problem, you can convert the table into two tables, one for storing information about each customer and the items they bought, the other is used to store information about each product and its price. Therefore, adding or deleting one table does not affect the other table.
Introduction to several design paradigms of relational databases 1. 1nf in any relational database, 1nf is a basic requirement for Relational Models and does not meet 1nf) is not a relational database. 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. The new object has one-to-multiple relationships with the original object. In the first paradigm (1nf), each row of the table contains only information of one instance. For example, for the employee information table in Figure 3-2, the employee information cannot be displayed in one column or two or more columns in one column; each row in the employee information table only represents the information of one employee. The information of one employee appears only once in the table. In short, the first paradigm is a non-repeated column.
2 The second Paradigm (2nf) Second Paradigm (2nf) is established on the basis of 1nf, that is, to satisfy the second Paradigm (2nf), we must first satisfy the first paradigm (1nf ). The second Paradigm (2nf) requires that each instance or row in the database table be able to be distinguished by a unique region. To implement differentiation, you usually need to add a column to the table to store the unique identifier of each instance. 3-2 The employee ID (emp_id) column is added to the employee information table. Because each employee's employee ID is unique, each employee can be uniquely distinguished. This unique attribute column is called as the primary keyword, primary key, and primary code. 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 non-primary attributes are not partially dependent on primary keywords.
3. 3nf must satisfy 3nf first ). In short, the third paradigm (3nf) requires that a database table do not contain information about non-primary keywords already contained in other tables. For example, there is a department information table, where each department has a department ID (dept_id), department name, Department profile, and other information. After listing the Department numbers in the employee information table in Figure 3-2, you cannot add the Department name, Department profile, and other information related to the department to the employee information table. If the department information table does not exist, it should also be constructed based on the third paradigm (3nf), otherwise there will be a large amount of data redundancy. In short, the third paradigm is that attributes do not depend on other non-primary attributes. Analysis of Three paradigm application examples of Database Design
The design paradigm of databases is the specifications that need to be met by database design. databases that meet these specifications are concise and have clear structures. At the same time, there will be no insert or delete operations) and update operations are abnormal. On the contrary, it is a mess, which not only creates troubles for database programmers, but also features an ugly face. It may store a large amount of unnecessary redundant information. Is the design paradigm hard to understand? No, we certainly cannot understand and remember the mathematical formulas given to us in university textbooks. Therefore, many of us simply do not follow the paradigm to design databases. In essence, the design paradigm can be clearly stated in an image and concise discourse, and it is clear. This article will give a general description of the paradigm, and explain how to apply these paradigms to practical engineering using the database of a simple forum designed by the author as an example.
Paradigm indicates 1nf: fields in the database table are single attributes and cannot be divided. This single attribute is composed of basic types, including integer, real number, complex type, logical type, and date type. For example, the following database tables conform to the first paradigm: Field 1 Field 2 Field 3 field 4 and such database tables do not comply with the first paradigm: field 1 Field 2 Field 3 field 4 field 3.1 field 3.2 obviously, in any current Relational Database Management System (DBMS), it is impossible for a fool to make a database that does not conform to the first paradigm, because these DBMS do not allow you to divide one or more columns of a database table into two or more columns. Therefore, it is impossible for you to design a database that does not conform to the first paradigm in the existing DBMS.
2nf ): the database table does not have some function dependencies between non-Keyword fields and any candidate keyword fields (some function dependencies refer to the condition where some fields in the composite keywords determine non-Keyword fields ), that is, all non-Keyword fields depend entirely on any set of candidate keywords. Assume that the course selection relation table is selectcourse (student ID, name, age, course name, score, and credits), and the keywords are combined keywords (student ID, course name), because the following deciding relation exists: (student ID, course name) → (name, age, score, credits) This database table does not meet the second paradigm because of the following decision relationships: (Course name) → (credits) (student ID) →
(Name, age) indicates that fields in the combined keywords determine non-keywords. Because 2nf is not met, this course selection relation table has the following problems: (1) data redundancy: the same course is selected by N students, and "Credits" are repeated n-1 times; the same student took M courses, and the name and age of the Students repeat the M course. (2) Update exception: If the credits of a course are adjusted, the "Credits" value of all rows in the data table must be updated. Otherwise, the same course credits may vary. (3) insertion exception: If you want to open a new course, no one will take the course for the moment. In this way, the course name and credits cannot be recorded in the database because the "student ID" keyword is not yet available. (4) Deletion exception: if a group of students have completed the electives of the course, These electives should be deleted from the database table. However, the course name and credit information are also deleted. Obviously, this will also cause insertion exceptions.
Change the relation table selectcourse to the following three tables: Student (student ID, name, age); course: Course (Course name, credits); course selection relation: selectcourse (student ID, course name, score ). Such database tables conform to the second paradigm, eliminating data redundancy, update exceptions, insertion exceptions, and deletion exceptions. In addition, all database tables with single keywords comply with the second paradigm, because it is impossible to have a combination of keywords. Third Paradigm (3nf): Based on the second paradigm, if there is no transfer function dependency for any candidate keyword segment in the data table, it complies with the third paradigm. The so-called pass function dependency means that if"
→ B → C ", the C-passing function depends on. Therefore, database tables that meet the third paradigm should not have the following dependencies: keyword segment → non-Keyword segment X → non-Keyword segment Y. Assume that the student relationship table is student (student ID, name, age, school, school location, school phone number), keyword is a single keyword "student ID", because there is a decision relationship: (student ID) → (name, age, school, school location, school phone number) The database conforms to 2nf, but does not comply with 3nf because of the following decision: (student ID) → (school location, school phone number) that is, the transfer function dependency of the non-Keyword section "school location" and "college phone" on the keyword section "student ID" exists.
It can also cause data redundancy, update exceptions, insertion exceptions, and deletion exceptions. You can analyze and learn this information on your own. The student relationship table is divided into the following two tables: Student (student ID, name, age, school); School (school, location, phone number ). Such database tables conform to the third paradigm, eliminating data redundancy, update exceptions, insertion exceptions, and deletion exceptions. Bois-cell Paradigm (bcnf): Based on the third paradigm, if no field in the database table is dependent on the transfer function of any candidate keyword segment, it complies with the third paradigm. Assume that the warehouse management relation table is storehousemanage (warehouse ID, storage item ID, administrator ID, quantity), and one administrator works only in one warehouse. One warehouse can store multiple items. This database table has the following decision relationships:
(Warehouse ID, storage item ID) → (administrator ID, quantity) (administrator ID, storage item ID) → (warehouse ID, quantity) SO (warehouse ID, storage item ID) and (administrator ID, storage item ID) are both candidate Keywords of storehousemanage. The unique non-Keyword segment in the table is the number, which conforms to the third paradigm. However, because of the following decision relationships: (repository ID) → (administrator ID) → (repository ID) There is a condition where a keyword segment determines a keyword segment, therefore, it does not conform to the bcnf paradigm. It will encounter the following exceptions: (1) Deletion exception: When the Warehouse is cleared, all the "Storage item ID" and "quantity" information are deleted, the repository ID and administrator ID are also deleted.
(2) insertion exception: When the Warehouse does not store any items, the warehouse cannot be assigned an administrator. (3) Update exception: If the repository is changed to an administrator, the administrator ID of all rows in the table must be modified. The Warehouse Management relational table is divided into two Relational Tables: Warehouse Management: storehousemanage (warehouse ID, administrator ID); warehouse: storehouse (warehouse ID, storage item ID, quantity ). Such database tables conform to the bcnf paradigm and eliminate deletion, insertion, and update exceptions. The following information is provided for a forum Database: (1) user name, email, home page, phone number, and contact address (2)
Post: post title, post content, reply title, reply content for the first time we design the database as a table only: user name email homepage phone contact address post title post content reply title reply content this database table conforms to the first paradigm, but no one set of candidate keywords can determine the entire row of the database table, the username of the unique keyword segment cannot completely determine the entire tuples. We need to add the "Post ID" and "Reply ID" fields to change the table: user name email homepage phone contact address post ID post title post content reply id reply title reply content such as the keywords in the data table (user name, post ID, reply ID) can decide the entire line: (User Name, post ID, reply ID)
→ (Email, homepage, phone number, contact address, post title, post content, reply title, and reply content) However, this design does not conform to the second paradigm because of the following decision relationships: (User Name) → (email, home page, phone number, contact address) (post ID) → (post title, post content) (reply ID) → (reply title, reply content) that is, some functions of non-Keyword fields depend on the candidate keyword fields. Obviously, this design will cause a large amount of data redundancy and operation exceptions. We break down the database table into (underlined keywords): (1) User information: user name, email, home page, phone number, contact address (2) post information: Post ID, title, content
(3) reply information: reply ID, title, content (4) post: User Name, post ID (5) reply: Post ID, the design such as reply ID meets the requirements of the 1st, 2, 3 and bcnf paradigms. But is this the best design? Not necessarily. We can see that there is a 1: n relationship between the "user name" and "post ID" in the 4th "posts, therefore, we can merge the "post" into the "post information" of the 2nd items; the "Post ID" and "Reply ID" in the 5th items "reply" are also 1: therefore, we can merge the "reply" into the "Reply information" of the 3rd items. In this way, data redundancy can be reduced in a certain amount. The new design is as follows: (1) User information: user name, email, home page, phone number, and contact address
(2) post information: User Name, post ID, title, content (3) reply information: Post ID, reply ID, title, content database table 1 clearly meets the requirements of all paradigms; database Table 2 contains some functional dependencies of non-Keyword segments "title" and "content" on the keyword segment "post ID", that is, it does not meet the requirements of the second paradigm, however, this design does not cause data redundancy and operation exceptions. In database table 3, some function dependencies of non-Keyword segments "title" and "content" on the keyword segment "Reply ID" also exist, it does not meet the requirements of the second paradigm, but similar to database table 2, this design will not cause data redundancy and operational exceptions. From this we can see that it is not necessary to forcibly meet the requirements of the paradigm. For a 1: n relationship, when one side is merged to the other side of N, the other side of N will no longer meet the second paradigm, but this design is better!
For M: N relationships, one or n sides of M cannot be merged to the other, which may result in non-compliance with the paradigm requirements, Operation exceptions and data redundancy. For a relationship, we can merge 1 on the left or 1 on the right to the other side. This design does not meet the requirements of the paradigm, but does not cause operation exceptions and data redundancy. Conclusion The database design that meets the requirements of the paradigm is clear and can avoid data redundancy and operational exceptions. This does not mean that the design that does not meet the requirements of the paradigm must be incorrect. In the case of a or 1: n relationship in a database table, rather than conforming to the requirements of the paradigm, the merger is reasonable. When designing databases, we must always consider the requirements of the paradigm.