Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to design the database, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

1. Related concepts

(1) entity: the objects we use to describe in the database can be concrete or abstract. For example, "a student", "a book", "a course" and so on; of course, it can also be "the relationship between students and teachers".

(2) Field (fields): this is the column we see (column), which represents the properties of the entity we want to describe.

(3) record: what we usually call row represents that we want to describe different specific entities.

(4) Code (key): an attribute (or attribute group) of a tuple can be uniquely identified in the table, which we call a key in the data table.

(5) Primary key (primary key): a key that uniquely identifies a record in a table.

(6) Foreign key (foreign keys): a copy of the primary key field of a related record that connects the parent and child tables.

(7) complete functional dependency (full functional dependency): secondary attributes are completely dependent on a primary attribute, or a group of primary attributes.

(8) partial functional dependency (partial functional dependency): a secondary attribute depends on an attribute in the primary attribute group.

(9) dependent tables (dependent table): also known as weak entities (weak entity) are child tables that need to be identified by the parent table.

(10) Association table (associative table): a child table of two parent tables in a many-to-many relationship.

two。 Data design reference paradigm

2.1 first normal form (1NF): attributes are inseparable

Figure 2.1.1 the value of the Mailbox property is divided into two columns, which does not conform to the first paradigm. The attribute of "mailbox" in figure 2.2.2 is segmented and does not conform to the first paradigm. The changed figure 2.1.3 conforms to the first normal form and the attributes are inseparable.

2.2 second normal form (2NF): remove partial dependencies and retain complete dependencies

Name

Course

Score

Teaching material

Price

Xiao Wang

English

ninety-one

English book

ten

Xiao Wei

Mathematics

eighty-five

Math book

nine

Xiao Wang

Mathematics

ninety-six

Math book

nine

Xiao Wei

Language

one hundred

Chinese textbook

eight

Figure 2.2.1: consistent with the first paradigm, but not with the second paradigm

Now we have a table that conforms to the first paradigm, but it will still cause us a lot of trouble.

If you want to add a course, how to do it?

Name

Course

Score

Teaching material

Price

Xiao Wang

English

ninety-one

English book

ten

Xiao Wei

Mathematics

eighty-five

Math book

nine

Xiao Wang

Mathematics

ninety-six

Math book

nine

Xiao Wei

Language

one hundred

Chinese textbook

eight

Physics

Physics book

Physics

Physics book

Physics

Physics book

Figure-2.2.2: insert exception

Insert "Physics Book" every time you insert "Physics". Also, the main name field is empty, which is called an insert exception.

Name

Course

Score

Teaching material

Price

Xiao Wang

English

ninety-one

English book

ten

Xiao Wei

Mathematics

eighty-five

Math book

nine

Xiao Wang

Mathematics

ninety-six

Math book

nine

Figure-2.2.3: delete exception

Since the senior grade no longer needs "Chinese class", then we first delete all the records containing "Chinese". At this time, we also delete the relationship between "curriculum" and "textbook". We don't know what "textbook" will be used in the course of "Chinese". This is called deleting an exception.

Name

Course

Score

Teaching material

Price

Xiao Wang

English

ninety-one

English book

ten

Xiao Wei

Mathematics

eighty-five

Math book

nine

Xiao Wang

Mathematics

ninety-six

Math book

nine

Xiao Wei

Language

one hundred

Chinese textbook

eight

Figure-2.2.4: change exception

If we want to replace the textbook of "Mathematics Book" with "Advanced Mathematics Book", then all the pre-clearance records have to be changed, isn't it troublesome? This is called a change exception.

In order to solve the above three problems, we introduce the second paradigm (2NF).

(name, course) > > (score)

(name, course) > (textbook)

Because the "name" and "course" fields can determine the remaining fields, we call the combination of such fields "codes", and each field in the code is called "main attribute". Non-code fields are called "secondary properties".

We notice that in the relationship (name, course) > (textbook), there exists (curriculum) > > (textbook). If the curriculum can determine the teaching material separately, the dependence of the "teacher" field on the "name" and "course" keys is called partial dependence (because it depends partly on the "course" field in the key combination). On the other hand, "classroom" and "time" depend entirely on the combination of "name" and "course".

By splitting the table, we eliminate "partial dependencies".

Have all the above three problems been solved? In the future study, we will learn how to associate the two tables.

2.3 third normal form (3NF): conforms to 2NF and eliminates "transitive dependency"

The school buys books every semester, and the prices of books are different every year. For example, if the price of "math books" goes up by "11" this year, we have to change the prices of all records containing math books, or is there something unusual?

Here, "course" > "textbook" > > "price", then "price" to "course" is "transmission dependence". At this time, we need to split the "course" table again to eliminate "transfer dependency".

Finally, we get a table that satisfies the three paradigms. This is the table composed of figure 2.3.1 (split 1), figure 2.3.3 (split 3) and figure 2.3.4 (split 4).

Compared with our original watch, is it very neat?

Thank you for reading this article carefully. I hope the article "how to Design a Database" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.