Three main paradigms of relational database

Basic concepts: keywords, primary keywords, candidate keywords, non-keywords

If the value of a field or multiple fields uniquely identifies a record, the field or field group is called a keyword .
If a keyword is used to identify the uniqueness of each record and is associated with other table implementations, it is called the primary key (primary key, primary key), or the main code .
A keyword other than the primary keyword is called a candidate keyword .

Words other than keywords that are unexpected are called non-keywords

For example, there is a table field:
ID FirstName LastName Address phone Idcard
Then the ID or Idcard or firstname+lastname (without the same name) can be said to be a keyword.
Where ID is the primary keyword, idcard and firstname+lastname are candidate keywords.

Database design Paradigm

first Normal (1NF): The word in the data table is a single attribute, non-re-divided (atomic). A single attribute consists of a basic type, including Integer, real, character, logical, date, and so on.

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.

second Normal form (2NF): The non-keyword in the data table does not have a partial function dependency on the candidate keyword (some of the function dependencies refer to the case where some of the fields in the composite keyword determine a non-critical field), then the second normal (completely dependent on the primary key) is met. That is, all non-critical fields are completely dependent on any set of candidate keywords.

Example: assume that the selection relationship table is Selectcourse (school number, name, age, course name, score, credit), keyword is a 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 the following decision relationship exists:

(course name) → (Credit)

(study number) → (name, age)

is present in the combo keyword The field that determines the non-keyword condition.

Because it does not conform to 2NF, this class selection relationship table will have 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 the M course, and the name and age were repeated m-1 times.

(2) Update exception:

If you adjust the credits for a course, the "credits" value for all the rows in the datasheet is updated, otherwise the same course credit will be different.

(3) Insert exception:

If you want to open a new course, no one is currently taking it. Thus, the course name and credits cannot be recorded in the database because there is no "learning number" keyword.

(4) Delete exception:

If a group of students has completed the course, these elective records should be removed from the database table. At the same time, however, the course name and credit information were also removed. Obviously, this can also lead to an insertion exception.  

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

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

Course: Course (course name, credits);

Elective course Relationship: S Electcourse (School number, course name, grade). Database tables such as

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 because the combination keyword cannot be present.

The third paradigm (3NF): On the basis of the second paradigm, any non-critical field that has a transitive function dependency on any of the candidate keywords conforms to the third paradigm (independent of other non-principal attributes). The so-called transfer function dependency, refers to if there is a "a→b→c" decision relationship, the C transfer function depends on A. Therefore, a database table that satisfies the third paradigm should not have the following dependencies: key fields → non-critical fields x→ non-critical fields Y

Example: assume that the student relationship table is student (school number, name, age, school, college location, college phone), the keyword is a single keyword "study number" because of the following decision relationship:

(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)

That is, there is a non-critical field "College location", "College phone" to the key field "study number" of the transfer function dependency.

It also has data redundancy, update exceptions, insert exceptions, and delete exceptions, which readers can analyze on their own.

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

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

College: (College, location, telephone).

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

Boyce-Christie's Paradigm (BCNF): on the basis of the third paradigm, there is no field in the data table that is dependent on the transfer function of any of the candidate key fields, then conforms to the Boyce-Christie paradigm (BCNF)

Example: Suppose the Warehouse Management Relationship table is storehousemanage (warehouse ID, store item ID, Administrator ID, number), and an administrator only works in one warehouse; a warehouse can store a variety of items. The following decision relationships exist in this database table:

(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.

(3) Update exception:

If the warehouse has been replaced by an administrator, the administrator ID for all rows in the table is modified.

Break down the Warehouse management relationship table into two relational tables:

Warehouse Management: Storehousemanage (warehouse ID, administrator ID);

Warehouse: Storehouse (warehouse ID, store item ID, quantity).

Such database tables conform to the BCNF paradigm, eliminating deletion exceptions, insert exceptions, and update exceptions.

Three main paradigms of relational database