Concise and clear! Analysis of Three paradigm application examples of Database Design

(Reprint http://bbs.database.ccidnet.com/read.php? Tid = 325895) concise and clear! Analysis of Three paradigm application examples of Database Design Introduction Ol ~ Er {q
; IC, $ VZ 0}
The design paradigm of databases is the specifications that need to be met by database design. databases that meet these specifications are concise and have clear structures. At the same time, there will be no insert or delete operations) and update operations are abnormal. On the contrary, it is a mess, which not only creates troubles for database programmers, but also features an ugly face. It may store a large amount of unnecessary redundant information. Gmma
Zxkm: W -[
Is the design paradigm hard to understand? No, we certainly cannot understand and remember the mathematical formulas given to us in university textbooks. Therefore, many of us simply do not follow the paradigm to design databases. } WGN ),,
> $ Mt 'd ~>
In essence, the design paradigm can be clearly stated in an image and concise discourse, and it is clear. This article will give a general description of the paradigm, and explain how to apply these paradigms to practical engineering using the database of a simple forum designed by the author as an example. T '?! A] If {
W % N % Q;) 3u
Paradigm description K % G <
_ GM) 3/x
1nf: fields in the database table are single attributes and cannot be divided. This single attribute is composed of basic types, including integer, real number, complex type, logical type, and date type. + Ykv \ 2ij)
M & iwo9} 0
+ Iv5o
Obviously, in any current Relational Database Management System (DBMS), dummies cannot make databases that do not conform to the first paradigm, because these DBMS do not allow you to divide one or more columns of a database table into two or more columns. Therefore, it is impossible for you to design a database that does not conform to the first paradigm in the existing DBMS. W'uzixqt $
To! 5 MB \ & Z
2nf ): the database table does not have some function dependencies between non-Keyword fields and any candidate keyword fields (some function dependencies refer to the condition where some fields in the composite keywords determine non-Keyword fields ), that is, all non-Keyword fields depend entirely on any set of candidate keywords. <_ Egsocaz
J7 'jsq ^'
6; n "/; N:
[K Io |? % |
Assume that the course selection relation table is selectcourse (student ID, name, age, course name, score, and credits), and the keywords are combined keywords (student ID, course name), because the following deciding relation exists: P xhsjnff
Xc1kr \ y''
(Student ID, course name) → (name, age, score, credits) W ^ gnem
R & L "wut3a
This database table does not meet the second paradigm because of the following decision relationships: ''? I + "z =
E>: kzoiz
(Course name) → (credits) | $3, %: V5
@ OO? Ti: \] |
(Student ID) → (name, age) II "Al &
Igka <o
That is, fields in the combined keywords determine non-keywords. 7tz0py5
Lgunr1r;
Because 2nf is not met, this course selection relation table has the following problems: (# 2lr
Abru1.
(1) data redundancy: | MZ @"
Ga->] r
The same course is selected by N students, and "Credits" are repeated for n-1 times. The same student takes m courses, and the name and age are repeated for m-1 times. O [3hwyy8r
^ AGR "kJ @
(2) Update exception: B! O y1k
OIV,. # qbg
If the credits of a course are adjusted, the "Credits" value of all rows in the data table must be updated. Otherwise, different credits may occur for the same course. Ttiw91 ~ $
| N43hosk
(3) insertion exception: :} B64 {z
VK kx ~ 8
For example, if you want to open a new course, no one will take the course. In this way, the course name and credits cannot be recorded in the database because the "student ID" keyword is not yet available. Ay (] Id2
WZ & v |} y;
(4) Deletion exception: Tasc \ f] J
X 0 GZ
Assuming that a group of students have completed their electives, These electives should be deleted from the database table. However, the course name and credit information are also deleted. Obviously, this will also cause insertion exceptions. ~ [} |: FMD
CW {; PIM (
Change the selectcourse table to the following three tables: Ad_g [s'
Qyzug3
Student: Student (student ID, name, age ); Yvbo @ G0
M7h72 = z
Course: Course (Course name, credits ); B {. E [)
5gy-h \ + T
Course Selection relationship: selectcourse (student ID, course name, score ). E {er3itl
> J71ke6
Such database tables conform to the second paradigm, eliminating data redundancy, update exceptions, insertion exceptions, and deletion exceptions. ~ X' 0? NLC
+ Z? Q = K7
In addition, all database tables with single keywords comply with the second paradigm, because it is impossible to have a combination of keywords. C + A \ 0 + Xn
. [, K1wd @ P
Third Paradigm (3nf): Based on the second paradigm, if there is no transfer function dependency for any candidate keyword segment in the data table, it complies with the third paradigm. The so-called pass function dependency refers to the existence of "A → B → C" decision relationship, then the C transfer function depends on. Therefore, database tables that meet the third paradigm should not have the following dependency: WBU ,~. Z
V * X: nuab,
Keyword field → non-Keyword field X → non-Keyword field y Ulej
2, A. ZG]
Assume that the student relationship table is student (student ID, name, age, school, school location, school phone number), and the keyword is single keyword "student ID", because the following decision relationship exists: #3 emtj <GC
} Sajko "U3
(Student ID) → (name, age, school, school location, school phone number) \ 5r889
Ffd9 = J _ ^
This database complies with 2nf but does not comply with 3nf because of the following decision relationships: Q | # l \}
Cj_w 'x:
(Student ID) → (school location, school phone number) Gr? /: +
1w-h kynt
That is, the transfer function dependency of the non-Keyword section "school location" and "college phone" on the keyword section "student ID" exists. (GT <
4 $ B] ksx
It can also cause data redundancy, update exceptions, insertion exceptions, and deletion exceptions. You can analyze and learn this information on your own. My5] 90d
Sam1p @ 5
The student relationship table is divided into the following two tables: "Q" "X) ST
N = AQ? 5D-
Student (student ID, name, age, school ); GTPs) d y
RRA =} NX'
School: (school, location, phone number ). @ U) [EK ">
} AE] H |
Such database tables conform to the third paradigm, eliminating data redundancy, update exceptions, insertion exceptions, and deletion exceptions. . M 0y
Zz) RJ \ bn
Bois-cell Paradigm (bcnf): Based on the third paradigm, if no field in the database table is dependent on the transfer function of any candidate keyword segment, it complies with the third paradigm. M.6 ^ ENN
} Ah &? Ve; G
Assume that the warehouse management relation table is storehousemanage (warehouse ID, storage item ID, administrator ID, quantity), and one administrator works only in one warehouse. One warehouse can store multiple items. This database table has the following decision relationships: &; V, coedn
Ygi_6 -'
(Repository ID, storage item ID) → (administrator ID, quantity) GD 'o2zt
"Tzboyib
(Administrator ID, storage item ID) → (warehouse ID, quantity) Hpbf) NF
-| 2n-,/94
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: Zqr: %. m
Llqtc1z @ C
(Repository ID) → (administrator ID) Tps9smz8
Red], GN}
(Administrator ID) → (repository ID) YW $ 9kl
K7 @ 6 {I
That is, the keyword segment determines the keyword segment, so it does not conform to the bcnf paradigm. It has the following exceptions: _! Y {5
Znjuy"
(1) Deletion exception: $ F! KV S % (
Pnhm "'d: W
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. E> 2 P "7
P &.> Y, c
(2) insertion exception: 8nrqjkq | G
E # @ _ VRR <o
When a Warehouse does not store any items, an administrator cannot be assigned to the warehouse. Z4yy @ q |
Bpvuc ;;:
(3) Update exception: Ki 'uko '8
Jtftnd'
If the repository is changed to an administrator, the administrator ID of all rows in the table must be modified. ;!~ (KS ~ E
; E3! 0g
Break down the warehouse management relationship table into two Relational Tables: Brbfg86/
R! ZMP C
Warehouse Management: storehousemanage (warehouse ID, administrator ID ); X/I] 4pa _
Yvxt? 8 ~
Repository: storehouse (warehouse ID, storage item ID, quantity ). URC | X? T
1ji_w ^ 4
Such database tables conform to the bcnf paradigm and eliminate deletion, insertion, and update exceptions. Tipcu z11
Zez = nyn
Xqudaih
C "E.2
Paradigm Application _ V & dt1}
Lo? B # mxsj
Let's get a forum database step by step, with the following information: WV; va4 (n
# <NV & B
(1) User: user name, email, home page, phone number, and contact address S) Shu {
B} t [fo! H
(2) post: post title, post content, reply title, reply content Fujt % br
H} & gs6re
For the first time, we designed the database to only exist tables: (,) 43_e t
A5y! (8ng
User name email homepage phone contact address post title post content reply title reply content 2a # d0j
50l 5} ^
This database table conforms to the first paradigm, but no set of candidate keywords can determine the entire row of the database table. The username of the unique keyword segment cannot completely determine the entire tuples. We need to add the "Post ID" and "Reply ID" fields to change the table: ZC "Vy (F ~ He
? EE @ vevm
User name email homepage phone contact address post ID post title post content reply id reply title reply content TN #} Z \@
"Opjsh
In this way, the keywords (username, post ID, and reply ID) in the data table can determine the entire line: # Ovzae =
@ # Ljgmu -*
(User name, post ID, reply ID) → (email, home page, phone number, contact address, post title, post content, reply title, reply content) > %-EH + UC
]:? Kejt:
However, such a design does not conform to the second paradigm because of the following decision relationships: CQ @ py # yq
! Vrx St
(User Name) → (email, home page, phone number, contact address) # 'V WCMS
I7w4 @ 6d
(Post ID) → (post title, post content) XP "c6sujb
X? PW> ^ _ g
(Reply ID) → (reply title, reply content) F ~ + &?
{? Ay {el
That is, some functions of non-Keyword fields depend on the candidate keyword fields. Obviously, this design will cause a large amount of data redundancy and operation exceptions. 'Yoj1' S (, h
+ 7 T-^ {T
> = MT % QX, se
'Vbiox
We break down a database table into (underlined keywords ): Lf % 3C:
Axtpmaz
(1) User information: user name, email, home page, phone number, and contact address 7 K $ C? K
<H [s! ''W
(2) post information: Post ID, title, content E; BQ $ r'9c
J [& 2lb
(3) reply information: reply ID, title, content X <! NJW <Y
] Iisj '37y
(4) post: User Name, post ID _ 'Ikr3 *
: @ Fkc
(5) reply: Post ID and reply ID (Yatw * Z2 %
B0psuk \ 7E
This design meets the requirements of the 1st, 2, 3 and bcnf paradigms. But is this the best design? Not necessarily. = (CG ['(WD
W ~ G %/r>]
We can see that there is a 1: n relationship between the "user name" and "post ID" in the 4th "posts, therefore, we can merge the "post" into the "post information" of the 2nd items; the "Post ID" and "Reply ID" in the 5th items "reply" are also 1: therefore, we can merge the "reply" into the "Reply information" of the 3rd items. In this way, data redundancy can be reduced in a certain amount. The new design is as follows: F (91.u>
S0im | ox (
(1) User information: user name, email, home page, phone number, and contact address Kvyzr CP <8
Zg8vt: DPF
(2) post information: User Name, post ID, title, content VJ? 94]
D * n @ <UX
(3) reply information: Post ID, reply ID, title, content LLO ^ w F. $
ZFW | ZF
Mi !, GEP
From this we can see that it is not necessary to forcibly meet the requirements of the paradigm. For a 1: n relationship, when one side is merged to the other side of N, the other side of N will no longer meet the second paradigm, but this design is better! {Rskuteg \
P5 ^ 9i # u
For M: N relationships, one or n sides of M cannot be merged to the other, which may result in non-compliance with the paradigm requirements, Operation exceptions and data redundancy. 56or9e8 {*
For a relationship, we can merge 1 on the left or 1 on the right to the other side. This design does not meet the requirements of the paradigm, but does not cause operation exceptions and data redundancy. : Yy = y/q0
4nes ."
Conclusion It> Z [ig
Q! H'smfo =
The database design that meets the requirements of the paradigm is clear in structure, while avoiding data redundancy and operational exceptions. This means that the design that does not meet the requirements of the paradigm must be incorrect. In the case of a database table with a or 1: n relationship, rather than conforming to the requirements of the paradigm, the merger is reasonable. 3ara. B <t
Ze] "E ~ *
When designing databases, we must always consider the requirements of the paradigm.