Please briefly describe the experimental paradigm and other design purposes.

Experimental paradigm is a relatively fixed experimental procedure.

In many fields, especially psychology,

In order to test a hypothesis, we found some interesting phenomena. The experimenter will design experiments with verification purpose.

Some experiments are classic and have been used many times by later generations for the same or similar purposes, thus forming an experimental paradigm.

The experimental paradigm includes the purpose, specific process and means of the experiment, and whether it is within or between subjects or mixed experimental design.

In a word:

The experimental paradigm can be used as a template for specific experiments and modified according to its new requirements.

For example, in decision psychology, the classic experimental paradigms are: Iowa game task, Cambridge game task and so on.

Design paradigms (paradigms, database design paradigms, database design paradigms) are a collection of relational paradigms that conform to a certain level. The construction of database must follow certain rules. This rule is an example in a relational database. Relationships in relational databases must meet certain requirements, that is, meet different paradigms. At present, there are six paradigms of relational database: first normal form (1NF), Second Paradigm (2NF), Third Paradigm (3NF), Fourth Paradigm (4NF), Fifth Paradigm (5NF) and Sixth Paradigm (6NF). The paradigm to meet the minimum requirements is first normal form (1NF). On the basis of first normal form, the second paradigm (2NF) is called to further meet more requirements, and other paradigms are analogized. Generally speaking, a database only needs to satisfy the third normal form (3NF). First normal form (1NF), the second normal form (2NF) and the third normal form (3NF) are introduced with examples.

In the process of creating a database, normalization is the process of transforming the database into some tables. This method can make the results obtained from the database clearer. This may lead to duplicate data in the database, which may lead to the creation of redundant tables. Normalization is a detailed process after determining the data elements and relationships in the database and defining the required tables and items in each table.

The following is an example of normalization: the customer's project purchase price Thomas shirt $40 Maria tennisshoes $35 Evelyn shirt $40 Paharo pants $25.

If the above table is used to save the price of goods and you want to delete one of the customers, you must also delete a price. Normalization is to solve this problem. This table can be changed into two tables, one storing the information of each customer and the items he bought, and the other storing the information of each product and its price, so that adding or deleting one table will not affect the other table.

Introduction of several relational database design paradigms

1 first normal form (1NF)

In any relational database, first normal form (1NF) is the basic requirement for relational schema, and a database that does not meet first normal form (1NF) is not a relational database.

First normal form (1NF) means that every column of a database table is an inseparable basic data item, and the same column cannot have multiple values, that is, an attribute in an entity cannot have multiple values or duplicate attributes. If there are duplicate attributes, you may need to define a new entity. The new entity is composed of repeated attributes, and there is a one-to-many relationship between the new entity and the original entity. In first normal form (1NF), each row of a table contains only one instance of information. For example, for the employee information table in Figure 3-2, all employee information cannot be displayed in one column, nor can two or more columns be displayed in one column; Each row of the employee information table only represents the information of one employee, and the information of one employee only appears once in the table. In short, first normal form is a non-repeating column.

2 the second normal form (2NF)

The second normal form (2NF) is based on first normal form (1NF), that is, to satisfy the second normal form (2NF), first normal form (1NF) must be satisfied first. The second normal form (2NF) requires that each instance or row in a database table must be uniquely distinguishable. In order to distinguish, it is usually necessary to add a column to the table to store the unique identification of each instance. As shown in Figure 3-2, the employee number (emp_id) column is added to the employee information table. Because each employee's employee number is unique, each employee can be uniquely distinguished. This unique attribute column is called primary key or primary key and main code.

The second normal form (2NF) requires that the attributes of an entity depend entirely on the primary keyword. The so-called complete dependence means that you can't have an attribute that only depends on a part of the main keyword. If there is, then this attribute and this part of the main keyword should be separated to form a new entity, and the relationship between the new entity and the original entity is one-to-many. In order to distinguish, it is usually necessary to add a column to the table to store the unique identification of each instance. In short, the second paradigm is that non-subjective attributes do not depend in part on primary keywords.

3 The third normal form (3NF)

To satisfy the third normal form (3NF), we must first satisfy the second normal form (2NF). In short, the third normal form (3NF) requires that the database table does not contain non-primary key information already contained in other tables. For example, there is a department information table, in which each department has department number (dept_id), department name, department file and other information. Then after the department number is listed in the employee information table in Figure 3-2, the department-related information such as department name and department file can no longer be added to the employee information table. If there is no department information table, it should be constructed according to the third normal form (3NF), otherwise there will be a lot of data redundancy. In short, the third paradigm is that attributes do not depend on other non-main attributes.

Application Case Analysis of Three Paradigms of Database Design

The design paradigm of database is the specification that database design needs to meet. The database structure that meets these specifications is simple and clear, and there will be no abnormal operations such as insertion, deletion and update. On the contrary, it is a mess, which not only creates trouble for database programmers, but also looks disgusting and may store a lot of unnecessary redundant information.

Is the design paradigm difficult to understand? No, we were given a bunch of mathematical formulas in college textbooks. Of course, we can't understand them or remember them. So many of us didn't design the database according to the example at all.

In essence, design paradigm can be clearly expressed and understood in very vivid and concise language. This paper will explain the paradigms in a popular way, and take the database of a simple forum designed by the author as an example to illustrate how to apply these paradigms to practical projects.

Paradigm interpretation

First normal form (1NF): The fields in the database table are all single attributes and cannot be subdivided. This single attribute consists of basic types, including integer, real number, character, logic, date and so on.

For example, the following database tables conform to first normal form:

Field 1 Field 2 Field 3 Field 4

And such a database table does not conform to first normal form:

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 first normal form, because these DBMS do not allow one column of a database table to be divided into two or more columns. Therefore, it is impossible for you to design a database that does not conform to first normal form in the existing DBMS.

The second normal form (2NF): non-key fields have no partial functional dependence on any candidate key fields in the database table (partial functional dependence refers to the situation that some fields in the combined keywords determine the non-key fields), that is, all non-key fields are completely dependent on any group of candidate keys.

Assume that the course selection relationship table is SelectCourse (student number, name, age, course name, grades, credits), and the keywords are combined keywords (student number, course name), because there are the following decisive relationships:

(student number, course name) → (name, age, grades, credits)

This database table does not satisfy the second normal form, because there is the following decisive relationship:

(Course Name) → (Credit)

(Student ID) → (Name, age)

That is to say, there are cases where fields in combined keywords determine non-keywords.

Because it does not conform to 2NF, there will be the following problems in this course selection relationship table:

(1) Data redundancy:

The same course is selected by n students, and the "credit" is repeated n- 1 time; The same student took m classes, and his name and age were repeated m- 1 times.

(2) Update exception:

If the credits of a course are adjusted, the "credits" values of all rows in the data table should be updated, otherwise different credits of the same course will appear.

(3) Insert an exception:

Suppose you want to open a new course, and no one has learned it yet. In this way, because there is no "student number" keyword, the course name and credits cannot be recorded in the database.

(4) Delete the exception:

Assuming that a group of students have completed elective courses, these elective records should be deleted from the database table. But at the same time, the course name and credit information have also been deleted. Obviously, this will also lead to insertion exceptions.

Change the course selection relationship table to the following three tables:

Student: student (student number, name, age);

Courses: courses (course name, credits);

Course selection relationship: SelectCourse (student number, course name, grade).

Such a database table conforms to the second normal form, eliminating data redundancy, updating exceptions, inserting exceptions and deleting exceptions.

In addition, all single keyword database tables conform to the second normal form, because it is impossible to have combined keywords.

Third Normal Form (3NF): On the basis of the second normal form, if the non-key fields have no transfer function dependence on any candidate key fields, the data table conforms to the third normal form. The so-called transfer function dependence means that if there is a decisive relationship of "A → B → C", the transfer function of C depends on A ... Therefore, the database table satisfying the third normal form should not have the following dependencies:

Key field → Non-key field x → Non-key field Y

Suppose the student relationship table is a student (student number, name, age, college, college location, college phone number) and the keyword is a single keyword "student number", because there are the following decisive relationships:

(Student ID) → (Name, age, college, college location, college phone number)

The database conforms to 2NF, but not 3NF, because the following decisive relationship exists:

(Student ID) → (College )→ (College location, college phone number)

That is, the non-key fields "College Location" and "College Phone" depend on the transfer function of the key field "Student Number".

It will also have data redundancy, update exceptions, insert exceptions and delete exceptions, which readers can analyze by themselves.

Divide the student relationship table into the following two tables:

Student: (student number, name, age, college);

College: (College, location, telephone).

Such a database table conforms to the third normal form, eliminating data redundancy, updating exceptions, inserting exceptions and deleting exceptions.

Bowes-Cod normal form (BCNF): On the basis of the third normal form, if any field has no transfer function dependence on any candidate key field, the database table conforms to the third normal form.

Suppose that the relational table of warehouse management is StorehouseManage (warehouse ID, storage item ID, administrator ID, quantity), and one administrator only works in one warehouse; A warehouse can store all kinds of articles. There are the following decisive relationships in the database table:

(warehouse ID, storage item ID) → (administrator ID, quantity)

(Administrator ID, storage item ID) → (Warehouse ID, quantity)

Therefore, (warehouse ID, storage item ID) and (administrator ID, storage item ID) are candidate keys for StorehouseManage, and the only non-key field in the table is quantity, which conforms to the third normal form. However, due to the following decisive relationship:

(warehouse ID) → (administrator ID)

(Administrator ID) → (Warehouse ID)

That is, there is a situation that key fields determine key fields, so it does not conform to the BCNF paradigm. It will have the following exceptions:

(1) Delete exception:

When the warehouse is emptied, all the information of "storage item ID" and "quantity" are deleted, and at the same time, the information of "warehouse ID" and "administrator ID" are also deleted.

(2) Insert exception:

You cannot assign an administrator to a warehouse when nothing is stored in the warehouse.

(3) Update exception:

If the warehouse has a new administrator, the administrator ID of all rows in the table will be modified.

The warehouse management relational table is decomposed into two relational tables:

Warehouse management: StorehouseManage (warehouse ID, administrator ID);

Warehouse: warehouse (warehouse identification, storage item identification, quantity).

This database table conforms to the BCNF paradigm, eliminating deletion exceptions, insertion exceptions and update exceptions.

Example application

Let's get a forum database step by step, which contains the following information:

(1) User: user name, email, home page, phone number and contact address.

(2) Post: post title, post content, reply title and reply content.

For the first time, we designed the database as only tables:

User name, email home page, telephone contact address, post title, post content, reply title, reply content.

The database table conforms to first normal form, but no group of candidate key can determine the whole row of the database table, and the unique keyword field user name cannot completely determine the whole tuple. We need to add "Publish ID" and "Reply ID" fields, that is, modify the table to:

User name e-mail home phone contact address post ID post title post content reply ID reply title reply content.

In this way, the keywords (user name, posting ID and reply ID) in the data table can determine the whole line:

(User name, posting ID, reply ID) → (email address, home page, phone number, contact address, posting title, posting content, reply title, reply content)

However, such a design does not conform to the second normal form, because there is the following decisive relationship:

(User Name) → (Email, Home Page, Phone Number, Contact Address)

(Posting ID) → (Posting title, posting content)

(reply ID) → (reply title, reply content)

In other words, some functions of non-key fields depend on candidate key fields. Obviously, this design will lead to a lot of data redundancy and abnormal operation.

We decompose the database table into (underlined keywords):

(1) User information: user name, email address, home page, telephone number and contact address.

(2) Post information: post ID, title and content.

(3) Reply information: reply ID, title and content.

(4) Release: user name, release ID.

(5) reply: posting ID, reply ID

This design meets the requirements of 1, 2,3 paradigm and BCNF paradigm, but is this design the best?

Not necessarily.

It is observed that the relationship between "user name" and "posting ID" in item 4 is 1: n, so we can merge "posting" into item 2 "posting information"; The relationship between "posting ID" and "reply ID" in item 5 "reply" is also 1: n, so we can merge "reply" into item 3 "reply information". This can reduce data redundancy to some extent. The new design is:

(1) User information: user name, email address, home page, telephone number and contact address.

(2) Post information: user name, post ID, title and content.

(3) Reply information: posting ID, reply ID, title and content.

The database table 1 obviously meets the requirements of all paradigms;

The key field "posting ID" in Table 2 of the database has some functional dependencies on non-key fields "title" and "content", which does not meet the requirements of the second normal form, but this design will not lead to data redundancy and abnormal operation.

In database table 3, there are some non-key fields "title" and "content" that depend on the key field "reply ID", which does not meet the requirements of the second normal form, but similar to database table 2, this design will not lead to data redundancy and abnormal operation.

It can be seen that it is not necessary to meet the requirements of formal form. For the relationship of 1: n, when one side of 1 is merged with the other side of n, the other side of n no longer meets the second normal form, but this design is better!

For the relationship of M: N, one side of M or one side of N cannot be merged into the other side, which will lead to non-compliance with the requirements of the normal form, abnormal operation and data redundancy.

For the relationship of 1: 1, we can merge 1 on the left or 1 on the right to the other side. The design will not meet the requirements of the normal form, but it will not lead to abnormal operation and data redundancy.

conclusion

The database design structure that meets the requirements of the paradigm is clear, and data redundancy and abnormal operation can be avoided at the same time. This does not mean that designs that do not meet the requirements of the paradigm must be wrong. In the special case that there is a relationship of 1: 1 or 1: n in the database table, it is reasonable that the merger does not meet the requirements of the normal form.

When we design a database, we must always consider the requirements of the paradigm.