Writing in the front

This article belongs to the literacy post, if you are a mobile terminal development, usually less contact with the database, encounter complex storage relationship do not know how to do, I suggest you read this article.

There are some more written knowledge in the article, perhaps not in real combat, but there is no harm in learning a study.

This document mainly introduces some database basic knowledge, paradigm, ER graph, etc. Finally, a general process will be introduced to facilitate the design and establishment of tables. The general design section is at the end of this paper, entitled e-R Diagram Design. This article covers only relational databases and does not cover statements. If you have time, I will cover statements, complex queries, stored procedures, and so on.

Entities (tables)

An entity represents a collection of N attributes, such as name, ID number, gender, etc., and a person is an entity.

Relationships (tables)

A relational table stores the relationship between two entities. The simplest relationship has three columns: entity 1, Entity 2, and associated relational entities. For example: Person A, person B, conjugal relationship.

Relationships in relational databases typically fall into three categories:

1. 1-n (one-to-many)
2. N-1 (many-to-one)
3. N-n (many-to-many)

In general, n-N relationships require the establishment of a relationship table, which will be explained in detail later

Primary Key (Primary Key/Primary Key/Primary Key)

A primary key can be one or more fields in a table that uniquely identifies a single piece of data in the table or represents an entity.

Function:

1. Ensure entity integrity.
2. Speed up database operations (primary key columns add indexes by default).
3. Primary keys cannot be duplicated to avoid data duplication.
4. Sort by primary key by default.

Foreign Key (Foreign code/Foreign Key/Foreign Key)

Definition: Let F be a (group) property in the fundamental relation R, but not the key of that relation, Ks is the primary key of the fundamental relation S, and F is said to be a foreign key of R if F corresponds to Ks.

By definition, we know that a foreign key only represents a mapping of a relationship, and it is not necessary to use the foreign key keyword to define it, which is generally accepted and more flexible.

However, the foreign key can ensure data consistency. When you want to delete an entity, if the primary key of the entity is a foreign key in another table, it cannot be deleted and must be synchronized to avoid dirty data. There are usually two ways to do this.

Prevent implementation

• Inserts data from a table whose foreign key value does not exist in the primary key of the primary table.
• Modify a foreign key value from a table. Block changes if the primary key does not exist in the primary table.
• The primary table deletes rows, preventing the drop if the primary key exists in the secondary table foreign key.
• The primary table modifies the primary key. If the primary key exists in the foreign key of the secondary table, the modification is prevented.

Cascade to perform

• The primary table deletes rows, along with those from the table.
• The primary table modifies the primary key value, along with the foreign key value of the secondary table.
• Congruence with the flags in the master table is achieved by denying that the flags in the slave table are inconsistent with the flags in the master table for the same transaction.

Paradigms (skip some theoretical content if you are not interested)

Definition: A collection of relationship patterns that conform to a certain level, indicating the degree of rationalization of the relationships between attributes within a relationship.

Paradigms are divided into: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF

A higher-level paradigm must conform to a lower-level paradigm. When designing relational databases, BCNF is considered at most, and usually 3NF or less is acceptable for our clients.

We’re only going to talk about 3NF, BCNF and above, and we’ll talk about that at a later opportunity.

First Normal Form (1NF)

Definition: Every attribute conforming to 1NF is non-divisible

For example, the following figure does not conform to 1NF:

In fact, in any RDBMS(Relational Database Management System), existing tables conform to 1NF. We must design as follows:

Obviously, there will be a lot of redundant data and problems in 1NF. Let’s look at the following database, which will have problems:

• Problem 1: Data redundancy Student number, name, department name, department head these data are repeated for many times.
• If a department has been opened but no department head has been assigned, the department cannot be inserted if the column cannot be empty.
• If you want to delete all student records in a department, then the dean and the department’s data are also deleted. This is not correct.
• Problem 4: Modify exception same as above

Second normal Form (2NF)

The strict definition will not be expanded here, but let’s see what the difference is between the second normal form and the first normal form.

On the basis of 1NF, 2NF removes partial function dependence of non-primary attributes on codes.

If there is a uniquely identifiable attribute (primary attribute + non-primary attribute), we eliminate the relationship.

So let me introduce a couple of concepts.

Functional dependencies

If X is determined, Y must be determined. Then Y is called a function dependent on X, written as X → Y. Much like the function y = f(x), where the value of y is determined by x, and the value of x cannot be uniquely determined by y.

For example, in the example above, the name function depends on the student number

Complete function dependence

If X → Y, Z ⊊ Y (Z is a proper subset of Y), X → Z is not true, then Y perfect function is said to depend on X

For example: student number F→ name, (course name, student number) F→ result (the symbol here is hard to type, the symbol is as follows)

Partial function dependence

If X → Y, and Y is an incomplete function dependent on X, then Y is a partial function dependent on X.

For example, X → Y, Z → Y, where the relationship is called incomplete dependence, the symbol is as follows:

Transfer function dependence

If Y does not contain X and X function does not depend on Y, X → Y and Y → Z are called Z transfer function dependent on X

code

Suppose K is an attribute (group) in a table, and all attributes except K are completely functionally dependent on K, then K is called candidate code, or code for short.

In short, if K is determined and all other attributes are determined, then K is the code. There can be more than one code in a table.

The main properties

Attributes contained in any code are called primary attributes.

Second normal Form (2NF)

Now that these concepts have been introduced, we are ready to look at 2NF using very complex steps.

1. Find all the codes in the database.
2. Follow Step 1 to find all primary and non-primary attributes.
3. See if there are partial function dependencies on the code that are not the primary property.

The above steps can be converted to:

1. Look at all single attributes and determine if all other attributes can be determined when its value is determined.
2. View an attribute group containing two attributes to see if, when its value is determined, all remaining attribute values can be determined.
3. To increase the number of attributes in the attribute group, repeat 2.

There is A simple way to do this: if A is code, then the rest of the attribute set containing A, such as (A, B), (A, B, C), etc., are not codes because there is A complete function dependency in the code requirement.

In table [1], there is only one code, namely (student id, class name), so the main attributes are: Student ID, class name; The non-primary attributes are: name, department name, department head, and grade. Then look at the partial function dependencies of non-primary attributes to the code.

• (Student id, class name) P→ name, there is student ID → name.
• (Student id, course name) P→ Department name, student ID → department name.
• (Student id, course name) P→ Dean, with student ID → Dean

Therefore, table [1] only conforms to 1NF, but not 2NF. We must eliminate these partial function dependencies. The only way is to split the big data table into many smaller data tables, and meet the higher normal form requirements in the process of splitting.

This process is called pattern decomposition. There is no unique method of schema decomposition. Here is one:

• Course selection: Student ID, course name, grade
• Student list: Student ID, name, department name, department head

If you split it like this, you can see that it all meets the requirements of 2NF, and you can try to verify it yourself.

The split table looks like this:

Let’s see if this improves 1NF

1. If Zoe transfers to the Law Department, we only need to modify one record in the student table to make an improvement
2. Department name, student name, department head, redundant data reduction has been improved
3. When deleting student information, department information is lost without improvement
4. Insert a new department without a dean, unable to insert without improvement

Therefore, it can be seen that only conforming to the second normal form cannot meet our needs, and we need to improve to meet the requirements of the third normal form.

Third normal Form (3NF)

On the basis of 2NF, 3NF eliminates the transfer dependence of non-primary attributes on codes. I’ll skip over the similarities between 3NF and 2NF.

With the concept in mind, let’s see if the table [2] conforms to the third normal form.

1. Course selection table: the code is (student NUMBER, course name), and the non-main attribute is only grade, so there is no transfer function dependence, so it conforms to 3NF.
2. Student table: code is (Student ID), student ID → Department name, department name → Dean, so student ID T→ Dean, there is transfer function dependence, does not meet the requirements of 3NF.

So we need to resolve the transfer function dependency here and split the table further:

After this design, the table [3] is in compliance with 3NF, you can verify whether to comply with 3NF.

E – R diagram design

E-r diagram can help us clarify the connection between entities, relations and attributes. When we get a requirement, we will design the E-R diagram first. When the e-R diagram is designed, the above table structure can be designed basically.

In e-R diagram design, different shapes represent different meanings, which can be divided into three types:

1. Entity (noun) : rectangle
2. Attribute (noun) : ellipse
3. Relation (verb) : diamond

For the student example above, let’s expand a bit and see what the e-R diagram looks like:

We also add some additional attributes to the course, such as the course number, as well as the department. The process of drawing ER diagram is as follows:

1. Identify entities, in this case students, courses, departments, represented by rectangular boxes.
2. Identify the attributes of an entity, represented by an elliptical box, and associated with the corresponding entity.
3. Determine the verbs between entities, which are available and optional here.
4. Determine the correspondence between solid verbs. For example, 1 student can have 1 department, and 1 department can have N students, so the relationship between students and departments is N-1.

The tricky part here is choosing a verb, like why you can’t have a department — there’s a department name. And just to explain, this one-to-one relationship, we usually wrap it up in a table as a property of one of the entities.

Build a table according to the E-R diagram

Once we have drawn the e-R diagram, we can build the table according to the following rules.

1. Based on attributes, create entity tables.
2. According to the corresponding relationship, the relationship table is established according to the following rules:

• The primary key of the entity table 1 is the foreign key of the entity table N. Do not generate relational tables. For example, add a department number attribute to the student table and a foreign key to the primary key of the department table.
• Create a new relational table with two foreign keys, the primary keys of the entity table, and add additional corresponding columns.
• 1-1 relation, optimization of e-R graph.

The following table is constructed for the above E-R diagram.

Note: primary keys are marked in red, * are associated primary keys, and green are foreign keys.

This is one of the most common third normal form table design processes.

Read More — A raunchy treatment of relationship lists

Without further ado, let’s take an example:

The relationship between the above students and the course should be N-N, but why is there no relationship table? Because as we observe, there are only eight courses.

Int (0); int (0); int (0); int (0); int (0); The 7th.

The NTH digit is 1, which means the student is enrolled in the course. Otherwise, if the NTH bit is 0, it means that the student did not choose the course.

References:

[1] [rounding out the first paradigm, the second paradigm, the third paradigm, BCNF paradigm] blog.csdn.net/wenco1/arti…

Write in the back

Some contents in the middle, such as the progression of paradigms, are also training for thinking. Knowing what it is and why it is are two different concepts.

If there are any mistakes in the article, please point them out.