Three Paradigms of database

1. Paradigm Note 1.1 The first normal form (1NF) cannot be re-divided

The so-called First paradigm (1NF) refers to the fact that each column of a database table is an indivisible basic data item and cannot have multiple values in the same column, that is, an attribute in an entity cannot have multiple values or cannot have duplicate properties . If duplicate attributes are present, you may need to define a new entity, which is composed of duplicate attributes, and a one-to-many relationship between the new entity and the original entity. In the first normal form (1NF), each row of a table contains only one instance of information. In short, the first paradigm is a column with no duplicates.

In any relational database, the first paradigm (1NF) is the basic requirement for relational schemas, and a database that does not meet the first normal form (1NF) is not a relational database. In any current relational database management system (DBMS), it is not possible to make a database that does not conform to the first paradigm because these DBMS do not allow you to divide a column 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 your existing DBMS.

1.2 Second normal form (2NF) property fully dependent on primary key [eliminate partial child function dependency]

The second paradigm (2NF) is established on the basis of the first paradigm (1NF), i.e. satisfying the second normal form (2NF) must first satisfy the first paradigm (1NF). The second normal form (2NF) requires that each instance or row in a database table must be divided by a unique region. For the implementation of the distinction, it is common to add a column to the table to store unique identities for each instance. For example, the Employee Information table has the employee number (emp_id) column added because each employee's employee number is unique, so each employee can be uniquely differentiated. This unique attribute column is called the primary key or primary key, and the main code.

The second normal form (2NF) requires that the attributes of an entity depend entirely on the primary key. The so-called full dependency is the inability to have a property that depends only on the primary key , and if so, this part of the property and the primary key should be separated to form a new entity, and the new entity is a one-to-many relationship with the original entity. For the implementation of the distinction, it is common to add a column to the table to store unique identities for each instance. In short, the second paradigm is that properties depend entirely on the primary key.

There may not be a single primary keyword here, in some cases there is a federated primary key, or there are multiple properties for the primary key.

Example:

Take the student elective course as an example, each student can choose the course, and have the result of this course, then if this information is put in a table Stugrade (Stuno,stuname,age,sex,courseno,coursename,credit,score).

If we don't look closely, we'll think that the primary key of this table is Stuno, but when we see the last score attribute, think about where there is a student's score if there is no course information. So the primary key of this table is a federated primary Key (Stuno,corseno), which can uniquely determine the score attribute. Then look at other information, such as Stuname only need to Stuno to be able to determine, coursename only need to Courseno can be uniquely determined, so there is a partial dependence , not conform to the second paradigm. If the student's course achievement information satisfies the second paradigm, it is necessary to split the table into multiple tables, a student table studnet (stuno,stuname,age,sex), a curriculum course (Courseno,coursename,credit) , as well as the last Student Course score Table Stugrade (Stuno,courseno,score).

This is in line with the second paradigm.

1.3 Third normal form (3NF) property does not depend on other non-primary properties [eliminate transitive dependencies]

Satisfying the third normal form (3NF) must first satisfy the second normal form (2NF). In short, the third paradigm (3NF) requires that a database table not contain non-primary key information already contained in other tables .

Example:

Each employee has a department, if there is an employee information sheet employee (emp_id,emp_name,emp_age,dept_id,dept_name,dept_info).

The primary key for this Employee information table is emp_id, because this property uniquely identifies all other attributes, such as knowing the employee number emp_id, will be able to know the employee's name, department number, department name, and department description. So here dept_id is not the main attribute, but the non-primary property. However, we can also find that the two properties of Dept_name,dept_info can be determined by dept_id, the non-primary attribute, that is, Dept_name relies on dept_id, and dept_id relies on emp_id, so there is a transitive dependency . And we can see that one of the obvious drawbacks of transitive dependencies is that data redundancy is very serious.

So how to solve the transfer dependency problem, in fact, it is very simple, we just need to dept_name,dept_info this even a property to delete it, that is, employee (EMP_ID,EMP_NAME,EMP_AGE,DEPT_ID), Then create a departmental table dept (dept_id,dept_name,dept_info).

So if you want to search for an employee's department information Dept_info, can be implemented through a database connection, the query statement is as follows:

Select E.emp_id,e.emp_name,d.dept_name from Employee e,dept D where e.dept_id=d.dept_id

Note the point:

1. Database connectivity can result in a fraction of the performance penalty
2. It's not that the database paradigm gets higher.
3. Sometimes there is a tradeoff between data redundancy and paradigm, and in the actual database development process, a portion of data redundancy is often allowed to reduce database connectivity.

1.4 Induction:1NF: field is not divided;
2NF: Primary key, non-primary key field depends on primary key;
3NF: Non-primary key fields cannot depend on each other;

Explanation:
1NF: Atomic field can not be re-divided, otherwise it is not a relational database;
2NF: Uniqueness A table shows only one thing;
3NF: Each column is directly related to the primary key, there is no transitive dependency;

Three Paradigms of database