Database paradigm | database first paradigm | database second paradigm

Understanding Database paradigm
The system is short-lived.
Data is permanent
The data base paradigm is essential to database design. without understanding the paradigm, it is impossible to design an efficient and elegant database. Even a wrong database is designed. It is not easy to understand and grasp the paradigm. In textbooks, relational algebra is used to explain the database paradigm. Although this can accurately express the database paradigm, it is abstract, not intuitive, easy to understand, and hard to remember.

This article uses a straightforward language to introduce the paradigm, aiming to facilitate understanding and memory. Some inaccurate expressions may appear in this case. But it should be a good entry for beginners. I wrote this article mainly to enhance my memory. In fact, I am also a little bit confused. I hope that when I am unfamiliar with some concepts, I will look back at my notes, you can quickly enter the status. If you find an error, correct it.

Start with the question below:

I. 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. Between tables ...... (10 w words omitted ).

Then you should understand the following concepts:

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. It is better to say "the relationship between teachers and schools ".

 
Attribute: the textbook explains "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 ".

 
Tuples: a row in a table is a tuple.

 
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.

 
Code: The table can uniquely identify an attribute (or attribute group) of a single tuples. If there is more than one such code, it is called a candidate code, we pick one from the candidate code to become the boss. It is called the master code.
 
Full code: If a Code contains all attributes, this code is full code.

 
Primary attribute: a property that appears in any candidate code is the primary attribute.

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

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

 
2. Six paradigms

Now, we have introduced all the basic concepts we need to master the paradigm. Let's talk about the paradigm below. 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...

 
 
 
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
Tel
Age
 
Dabao
13612345678
22
 
James
13988776655
010-1234567
21
 

PS: in this table, the attribute value is "Minute.

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

PS: in this table, the attribute "points.

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.

 
 
 
2nf: conforms to 1nf, and non-primary attributes depend entirely on codes.
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, 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)

Student
Course
Instructor
Instructor title
Teaching materials
Classrooms
Course time
 
James
First-year Chinese (I)
Dabao
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 have chosen this course, which is a huge change! It's exhausting ...... Depressed, right? (Modification exception)

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

Student
Course
Instructor
Instructor title
Classrooms
Course time
 
James
First-year Chinese (I)
Dabao
Associate professor
101

 

New student class table

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

Course table

 
 
3nf: conforms to 2nf and eliminates transmission dependencies.
The above "new student class table" conforms to 2nf, which can be verified as follows: two primary attributes are used independently, and no other four non-primary attributes need to be determined. But it has a transfer dependency!

Where? The problem lies in "teacher" and "Teacher title. A teacher must be able to determine a Teacher title.

Is there any problem? Think about it:

1. The instructor has upgraded and changed to the Professor. I want to change the database. There are n entries in the table, and I have changed them n times ...... (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:

Student
Course
Instructor
Classrooms
Course time
 
James
First-year Chinese (I)
Dabao
101

 

 
Instructor
Instructor title
 
Dabao
Associate professor
 

 
BC Paradigm (bcnf): conforms to 3nf, and the primary attribute does not depend on the primary attribute.
If the relational model belongs to the first paradigm and each attribute does not pass the key code, r belongs to the BC paradigm.

Generally, conditions in the BC paradigm have multiple Equivalent Expressions: The left side of each non-trivial dependency must contain the key code, and each deciding factor must contain the key code.

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.

 
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.
However, in most applications, you do not need to design this level. In some cases, too much normalization may even impede the logic readability and efficiency of the database. A certain degree of redundancy in the database is not necessarily a bad thing. If you are interested in the fourth and fifth paradigm, you can take a look at professional teaching materials, learn from the beginning, and forget what I said, so as not to mislead you.