Common understanding of database paradigm

Basic Concepts

To understand the paradigm, you must first know what a relational database is. If you do not know it, I can simply not simply say that a relational database uses a two-dimensional table to store data. (Refer to the database information I sent you.) Then you should understand the following concepts:

L entity: objects that exist objectively and can be differentiated in the real world. For example, "one student", "one book", and "one course. It is worth noting that the "things" mentioned here are not only visible and tangible "things", but also virtual, for example, "relationship between teachers and schools" (this may not be understood at the beginning, but you must first have this awareness that the entity can be tangible or intangible, entities can also be abstract things or links ).

L attribute: in textbooks, it is interpreted as "a certain feature of an object". It can be seen that a property is a logical concept at the beginning. For example, "gender" is an attribute of a person. In relational databases, attributes are a physical concept. attributes can be viewed as "a column of a table ".

L tuples: a row in the table is a tuple.

L component: the attribute value of a tuples. In a relational database, it is an operation atom, that is, when a relational database performs any operation, the attribute is "inseparable ". Otherwise, it is not a relational database.

L Code: The table can uniquely identify an attribute (or attribute group) of a single tuples. If there are more than one such code, everyone is called a candidate code, we pick one from the candidate code to become the boss. It is called the master code. (We can see that the code is actually a special attribute)

L full code: If a Code contains all attributes, this code is full code.

L Primary attribute: if an attribute appears in any candidate code, it is the primary attribute.

L non-primary attribute: In contrast to the above, this attribute has not appeared in any candidate code. This attribute is a non-primary attribute.

L external code: an attribute (or an attribute group). It is not a code, but the code of other tables. It is an external code.

Ii. Paradigm)

First, you must understand the inclusion relationship of the paradigm. If a database design conforms to the second paradigm, it must also comply with the first paradigm. If it complies with the third paradigm, it must also comply with the second paradigm...

L 1nf: the attribute cannot be divided.

We have already introduced the concept of attribute values. We say that it is "inseparable ". The first paradigm requires that attributes cannot be divided. So what is the difference between it and the attribute value? Here is an example:

Name	Phone number		Age
Dabao	13612345678		22
James	13988776655	010-1234567	21

In this table, the attribute value is "Minute. The property of "phone" is divided into two attributes: "James" and "phone.

Name	Phone number		Age
	Mobile phone	Landline
Dabao	13612345678	021-9876543	22
James	13988776655	010-1234567	21

In this table, the attribute "points. That is, "phone" is divided into two attributes: "Mobile Phone" and "landline.

Neither of these situations satisfies the first paradigm. Databases that do not meet the first paradigm are not relational databases! Therefore, such a "table" cannot be created in any relational database management system. You can create a table as follows:

Name	Mobile phone	Landline	Age
Dabao	13612345678		22
James	13988776655	010-1234567	21

2nf: conforms to 1nf, and non-primary attributes depend entirely on codes. (Note that full dependencies cannot be partial dependencies, and function dependencies are set to W → A. If XW exists and X → A is true, W → A is a local dependency, otherwise, it is called W → A as full function dependency)

It sounds mysterious, but it's actually nothing. There may be several primary attributes in a candidate code. If a primary attribute cannot be used as a candidate Code separately, it cannot determine any non-primary attribute. To put it another example: we consider a primary school educational administration system, where students designate a teacher, a teaching material, a classroom, and a period of time to attend classes. There is no problem. So how to design the database? (Student class table)

Name	Course	Instructor	Instructor title	Teaching materials	Classrooms	Course time
James	First-year Chinese (I)	Zhao Hongwei	Associate professor	Primary language 1	101

A student must take a course in a specific classroom. So there are (students, courses)-> classrooms

A student must be taught by a specific teacher. So there are (students, courses)-> teachers

When a student takes a course, his or her teacher's title can be determined. Therefore, there are (students, courses)-> teacher titles

A student must take a certain course. So there are (students, courses)-> teaching materials

A student takes a course at a specific time. So there are (students, courses)-> class time

Therefore (student, course) is a code.

However, a course must have specified a teaching material. First-year Chinese must use "Primary School Chinese 1", so there is a course> teaching material. (Student, course) is a code, but the course determines the teaching material. This is called incomplete dependency or partial dependency. In this case, the second paradigm is not satisfied!

What's wrong? You can think about it:

1. What should I do if the principal wants to add a new course called calculus and the teaching material is college mathematics? Students haven't selected courses yet, while students are the primary attributes. The primary attributes cannot be blank. How can we record the courses? Where can we record the teaching materials? ...... Depressed, right? (Insertion exception)

2. If no student learns the first-year Chinese Language (upper) or the first-year Chinese Language (lower) in the next semester, there will be no first-year Chinese (upper) in the table, and no "primary language 1" will be available. At this time, the principal asked: what teaching materials are used in the first-year Chinese Language (I ?...... Depressed, right? (Deletion exception)

3. The principal said: change the first-year Chinese (on) teaching materials to "University Chinese". 10000 of the students chose this course, which is a huge change! It's exhausting ...... Depressed, right? (The modification/update is abnormal. Here, you may feel that you can simply replace the teaching material "Primary School Chinese 1" with "Emy Chinese", but the replacement operation is fast, however, after all, we need to replace it with 10000 times, resulting in a large amount of time overhead)

What should we do? Projection decomposition: divides a table into two or more tables.

Name	Course	Instructor	Instructor title	Classrooms	Course time
James	First-year Chinese (I)	Zhao Hongwei	Associate professor	101

 

Course	Teaching materials
First-year Chinese (I)	Primary language 1

 

Third Paradigm (3nf): conforms to 2nf and eliminates transmission dependencies (that is, each non-master attribute does not pass dependent on the candidate key to determine the transfer function dependency, if the "A → B → C" Deciding relation exists, the C-passing function depends on .)

The above "new student class table" conforms to 2nf, but it has transmission dependencies! Where? The problem lies in "teacher" and "Teacher title. A teacher must be able to determine a Teacher title. (Student, course)-> teacher-> title.

Is there any problem? Think about it:

1. The instructor has upgraded and changed to the Professor. To change the database, there are n entries in the table, and N changes have been made ...... (Modification exception)
2. I have no candidates for this teacher's class, and I have no record of the teacher's title ...... (Deletion exception)
3. A new teacher has not assigned any courses yet. Where can he remember his title ?...... (Insertion exception)
What should we do? Like above, projection decomposition:

Name	Course	Instructor	Classrooms	Course time
James	First-year Chinese (I)	Zhao Hongwei	101

 

Instructor	Instructor title
Zhao Hongwei	Associate professor

 

BC Paradigm (bcnf): conforms to 3nf, and the primary attribute does not depend on the primary attribute (that is, there is no passing function dependency on any candidate key field)

The BC paradigm checks both non-primary attributes and primary attributes. When only checking for non-primary attributes, it becomes the third paradigm. The relationship that satisfies the BC paradigm must satisfy the third paradigm.

It can also be said that if a link reaches the third paradigm and has only one candidate code, or every candidate code is a single attribute, the relationship naturally reaches the BC paradigm.

For example, suppose that the warehouse management relation table (warehouse ID, storage item ID, administrator ID, quantity), and one administrator only works in one warehouse; A repository can store multiple items.

This database table has the following decision relationships:

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

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

Therefore, both (repository ID, storage item ID) and (administrator ID, storage item ID) are candidate Keywords of storehousemanage, and the unique non-Keyword segments in the table are quantity, it conforms to the third paradigm. However, the following decision relationships exist:

(Repository ID) → (administrator ID)

(Administrator ID) → (repository ID)

That is, the keyword segment determines the keyword segment, so it does not conform to the bcnf paradigm. It has the following exceptions:

(1) Deletion exception:

When the respiratory is cleared, all the "Storage item ID" and "quantity" information are deleted, and the "warehouse ID" and "administrator ID" information are also deleted.

(2) insertion exception:

When a Warehouse does not store any items, an administrator cannot be assigned to the warehouse.

(3) Update exception:

If the repository is changed to an administrator, the administrator ID of all rows in the table must be modified.

Break down the warehouse management relationship table into two Relational Tables:

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

Repository: storehouse (warehouse ID, storage item ID, quantity ).

Such database tables conform to the bcnf paradigm and eliminate deletion, insertion, and update exceptions.

Generally, a database can be designed with 3nf or bcnf. There are also the fourth and fifth paradigms above the BC paradigm.

Fourth paradigm: It is required to delete multiple-to-multiple relationships in the same table.

Fifth paradigm: Re-establish the original structure from the final structure.

In fact, the database design paradigm focuses on 1cf-2cf-3cf-bcnf.

There is a relationship between the four paradigms:

 

 

Here, we mainly distinguish 3nf from bcnf. In a word, 3nf must satisfy the function dependency that does not have a non-primary attribute on the candidate code, bcnf is a function dependency for passing candidate codes that does not have any attribute (including non-primary and primary attributes.