Shulou(Shulou.com)06/01 Report--

1 Overview

Generally speaking, when designing a database, we should follow three principles, that is, what we usually call the three paradigms, that is, the first paradigm requires ensuring the atomicity of each column in the table, that is, it is inseparable; the second paradigm requires ensuring that each column in the table is related to the primary key and not only to some part of the primary key (mainly for the federated primary key). The primary key column and the non-primary key column follow the complete functional dependency, that is, the complete dependence. The third paradigm ensures that there is no transitive functional dependency between primary key columns, that is, eliminating transitive dependencies.

This paper will be based on the three paradigm principles, combined with specific examples to do a brief analysis, the difficulty coefficient: the foundation.

2 the first paradigm

2.1 example introduction

Two kinds of data tables are designed according to the following scenario. Please analyze the rationality of the two data tables.

Problem: requirement description: a physical table is needed in the database system, which is used to store user information, in which the "address" attribute requires the province, city and detailed address to be queried.

two

3 specific examples:

4 name: Zhang Hongxin; Sex: male; Age: 26; Tel: 0378-23459876; Province: Henan Province; City: Kaifeng; detailed address: 23 Xinhua Road, Chaoyang District

5 name: Wang Yan; gender: female; Age: 25 years old; Tel: 021-2348768; Province: Guizhou Province; City: Guiyang; detailed address: 6 Shifeng Road, Nanming District, Nanming District

6 name: Wang Mei; gender: female; Age: 21; Tel: 0571-3876450; Province: Zhejiang Province; City: Hangzhou; detailed address: 352 Binkang Road, Binjiang District

The first kind of table design

The second kind of table design

2.2 Analysis

The first table design does not satisfy the first normal form, why not meet the first normal form? Because the region column is not atomic, it can be split into provinces, cities, and specific addresses

3 the second normal form

3.1 example introduction

Two kinds of data tables are designed according to the following scenario. Please analyze the rationality of the two data tables.

Demand description: design an order information table, the order has a variety of goods, the order number and item number as the joint primary key.

The first kind of table design

The second kind of table design

3.2 Analysis

The first table design does not satisfy the second normal form, the order number and the item number are used as the joint primary key, because the columns of commodity name, unit and price are only related to the item number and have nothing to do with the order number, so they have nothing to do with the primary key (joint primary key) and violate the second principle of the paradigm.

The second table design satisfies the second normal form, splitting the first design table, separating the product information into another table, and separating the order item table into another table.

4 the third paradigm

4.1 example introduction

Two kinds of data tables are designed according to the following scenario. Please analyze the rationality of the two data tables.

The following information needs to be stored in the database:

Student number; student card number; user ID number; operator level; operation date; operation time

The first kind of table design

The second kind of table design

4.2 Analysis

The first table design does not satisfy the third normal form, in which an UserID can determine a UserLevel. In this way, UserID depends on StudentNo and CardNo, while UserLevel depends on UserID, which leads to transitive dependencies, which 3NF eliminates.

The second table design satisfies the third normal form, splitting the first table into two tables.

5 references

[01] http://www.cnblogs.com/springside-example/archive/2011/10/06/2530207.html

[02] http://www.cnblogs.com/linjiqin/archive/2012/04/01/2428695.html#undefined

6 copyright

Thank you for your reading. If there are any inadequacies, you are welcome to comment, study together and make progress together. The blogger's website is http://www.cnblogs.com/wangjiming/. A very small number of articles are integrated by reading, reference, quoting, plagiarism, copying and pasting, and most of them are original. If you like, please recommend it; if you have any new ideas, please feel free to put forward them, email: 2016177728@qq.com. You can reprint the blog, but you must have a well-known blog source.

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.