Database Paradigm (1NF 2NF 3NF BCNF)

The first paradigm (1NF ) columns with no Duplicates

For example, the following database tables are in accordance with the first paradigm:

Field 1

Field 2

Field 3

Field 4

Such database tables do not conform to the first paradigm:

Field 1	Field 2	Field 3	Field 4
		Field 3.1	Field 3.2

The second paradigm (2NF )

The second normal form (2NF) is completely dependent on the primary key for the non-primary attribute.

The so-called full dependency is the inability to exist only the attributes that are part of the primary key, eliminating partial dependencies

Assume that the selection relationship table is Selectcourse (school number, name, age, course name, score, credits), keyword for the combination of keywords (study number, course name), because of the following decision relationship:

(School number, course name) → (name, age, score, credits)

This database table does not meet the second normal form because of the following decision relationship:

(course name) → (credits)

(school number) → (name, age)

(1) Data redundancy:

The same course by N students elective, "credit" repeated n-1 times, the same student elective m courses, name and age repeated m-1 times.

(2) Update exception:

If the credit of a course is adjusted, the "credits" value of all the rows in the data sheet should be updated, otherwise the same course credit will be different.

Change the course of the elective selectcourse to the following three tables:

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

Course: Course (course name, credits);

Elective relationship: Selectcourse (School number, course name, score).

Such database tables conform to the second paradigm, eliminating data redundancy, update exceptions, insert exceptions, and delete exceptions.

In addition, all single-key database tables conform to the second normal form, as there is no possible combination of keywords.

The third paradigm (3NF ) property does not depend on other non-primary properties [ Elimination of transitive dependencies ]

A database table that satisfies the third paradigm should not have the following dependencies:

key field → Non-critical field x→ non-critical field Y

For example, there is a departmental information table, where each department has a department number (dept_id), a department name, a department profile, and so on. Then the department number is listed in the Employee Information table, the department name, department profile and other departments related information can no longer be added to the Employee Information table.

In the example

(school number) → (name, age, school, college location, college phone)

This database is 2NF compliant, but does not conform to 3NF because of the following decision relationship:

(school number) → (school) → (college location, college phone)

The Student relations table is divided into the following two tables:

Student: (School number, name, age, school);

College: (College, location, telephone).

Such database tables are in accordance with the third paradigm

(Warehouse ID, store item id) → (Administrator id, quantity)

(Admin ID, store item id) → (warehouse ID, quantity)

So, (warehouse ID, store item ID) and (Administrator ID, store item ID) are the candidate keywords for storehousemanage, the only non-critical field in the table is the number, which is in accordance with the third paradigm. However, the following decision relationship exists:

(warehouse id) → (Administrator id)

(Administrator id) → (warehouse id)

that is, the key field determines the critical field , so it does not conform to the BCNF paradigm. It will have the following exception:

(1) Delete exception:

When the repository is emptied, all "store item ID" and "quantity" information is deleted, and the "Warehouse ID" and "Administrator ID" information are also deleted.

(2) Insert exception:

You cannot assign an administrator to a warehouse when no items are stored in the warehouse.