{"id":591,"date":"2010-08-24T13:44:00","date_gmt":"2010-08-24T05:44:00","guid":{"rendered":"http:\/\/note.systw.net\/note\/?p=591"},"modified":"2023-11-04T13:47:04","modified_gmt":"2023-11-04T05:47:04","slug":"%e6%ad%a3%e8%a6%8f%e5%8c%96","status":"publish","type":"post","link":"https:\/\/systw.net\/note\/archives\/591","title":{"rendered":"\u6b63\u898f\u5316"},"content":{"rendered":"\n

\u6b63\u898f\u5316<\/strong>
\u76ee\u7684:\u53bb\u9664\u95dc\u806f\u7570\u52d5\u7684\u7570\u5e38\u73fe\u8c61<\/p>\n\n\n\n

\u6b63\u898f\u5316\u904e\u7a0b<\/strong>
1NF > 2NF > 3NF > BCNF > 4NF > 5NF
1-3NF\u70ba\u4e09\u6b63\u898f\u5316,\u7531codd\u65bc1977\u5e74\u63d0\u51fa,\u4e5f\u662f\u6700\u5e38\u7528\u7684
ps:
BCNF\u70ba\u4e09\u6b63\u898f\u5316\u52a0\u5f37\u578b,\u7531boyce,codd\u63d0\u51fa
4NF,\u4f9d\u64daMulti-Valued Dependency(\u591a\u91cd\u503c\u76f8\u4f9d\u6027\u7406\u8ad6)\u6240\u63d0\u51fa
5NF,\u4f9d\u64daJoin Dependency(\u5408\u4f75\u76f8\u4f9d\u6027\u7406\u8ad6)\u6240\u63d0\u51fa<\/p>\n\n\n\n

……………………………………..<\/p>\n\n\n\n

\u6b63\u898f\u5316\u6703\u4f7f\u7528\u7684\u7279\u6027<\/strong>
F.D(full functional dependency,\u5b8c\u5168\u529f\u80fd\u76f8\u4f9d)
P.D(partial functional dependency,\u90e8\u4efd\u529f\u80fd\u76f8\u4f9d)
T.D(Transitive Functional Dependency,\u905e\u79fb\u76f8\u4f9d\u6027)<\/p>\n\n\n\n

Functional Dependence(\u529f\u80fd\u76f8\u4f9d\u6027)<\/strong>
\u82e5\u6b04\u4f4da\u53ef\u4ee5\u6c7a\u5b9a\u6b04\u4f4db,\u4e5f\u5c31\u662f\u6b04\u4f4db\u76f8\u4f9d\u65bc\u6b04\u4f4da
\u8868\u793a\u6cd5\u70baa->b
ex
\u5b78\u751f(\u5b78\u865f<\/u>,\u59d3\u540d,\u8840\u578b)<\/strong>
\u5b78\u865f\u53ef\u6c7a\u5b9a\u59d3\u540d,\u8868\u793a\u6cd5\u70ba \u5b78\u865f>\u59d3\u540d
\u5b78\u865f\u53ef\u6c7a\u5b9a\u8840\u578b,\u8868\u793a\u6cd5\u70ba \u5b78\u865f>\u8840\u578b
ex:
\u5546\u54c1(\u8a02\u55ae\u7de8\u865f<\/u>,\u8a02\u8ca8\u65e5\u671f,\u5ba2\u6236,\u7522\u54c1\u4ee3\u865f<\/u>,\u7522\u54c1\u540d\u7a31,\u55ae\u50f9,\u6578\u91cf)<\/strong>
\u8a02\u55ae\u7de8\u865f\u53ef\u4ee5\u6c7a\u5b9a\u8a02\u8ca8\u65e5\u671f,\u5ba2\u6236,\u7522\u54c1\u4ee3\u865f,\u7522\u54c1\u540d\u7a31,\u55ae\u50f9
\u3000\u8868\u793a\u6cd5\u70ba \u8a02\u55ae\u7de8\u865f>\u8a02\u55ae\u65e5\u671f.\u5ba2\u6236.\u7522\u54c1\u4ee3\u865f.\u7522\u54c1\u540d\u7a31.\u55ae\u50f9
\u7522\u54c1\u4ee3\u865f\u53ef\u4ee5\u6c7a\u5b9a\u7522\u54c1\u540d\u7a31,\u55ae\u50f9
\u3000\u8868\u793a\u6cd5\u70ba \u7522\u54c1\u4ee3\u865f>\u7522\u54c1\u540d\u7a31,\u55ae\u50f9
\u8a02\u55ae\u7de8\u865f,\u7522\u54c1\u4ee3\u865f\u53ef\u4ee5\u6c7a\u5b9a\u6578\u91cf
\u3000\u8868\u793a\u6cd5\u70ba \u8a02\u55ae\u7de8\u865f,\u7522\u54c1\u4ee3\u865f>\u6578\u91cf<\/p>\n\n\n\n

\u529f\u80fd\u76f8\u4f9d\u5206\u70ba<\/strong>
\u5b8c\u5168\u529f\u80fd\u76f8\u4f9d
\u90e8\u4efd\u529f\u80fd\u76f8\u4f9d<\/p>\n\n\n\n

F.D(full functional dependency,\u5b8c\u5168\u529f\u80fd\u76f8\u4f9d)<\/strong>
\u82e5x>y , \u5247x\u6703\u7528\u5230\u6240\u6709KEY
ex
(a,b,c)\u3000a->b,c \u5c6c\u65bcF.D
(a,b,c)\u3000a,b->c \u5c6c\u65bcF.D
(a,b,c)\u3000a->b\u5c6c\u65bcF.D<\/p>\n\n\n\n

P.D(partial functional dependency,\u90e8\u4efd\u529f\u80fd\u76f8\u4f9d)<\/strong>
\u82e5 x>y ,\u5247x\u53ea\u7528\u90e8\u4efdKEY
ex:
(a,b,c)\u3000a->c \u5c6c\u65bcP.D
(a,b,c,d)\u3000a->b,d \u5c6c\u65bcP.D<\/p>\n\n\n\n

T.D(Transitive Functional Dependency,\u905e\u79fb\u76f8\u4f9d\u6027)<\/strong>
\u7528\u7684\u4e0d\u662fKEY
ex:
(a,b,c)\u3000b->c\u5c6c\u65bcT.D<\/p>\n\n\n\n


…………………..<\/p>\n\n\n\n


1NF(first normal form,\u7b2c\u4e00\u6b63\u898f\u5316)<\/strong>
\u53bb\u9664\u91cd\u8907\u7fa4\u7d44
ps:\u91cd\u8907\u7fa4\u7d44\u8207multi attribute(\u591a\u91cd\u503c\u5c6c\u6027)\u53cacomposite attribute(\u8907\u5408\u578b\u5c6c\u6027)\u662f\u4e0d\u540c\u7684
ps:\u6b64\u968e\u6bb5\u50c5\u50c5\u628a\u8cc7\u6599\u586b\u6eff,\u9084\u672a\u6298\u8868\u683c
ex:
\u5c07\u5177\u6709a,b,c,d,e\u5c6c\u6027\u7684\u8cc7\u6599\u8f49\u63db\u6210\u4ee5\u4e0b\u683c\u5f0f
(a<\/u>,b,c<\/u>,d,e)<\/strong>
a,c->b,d,e F.D
a->b P.D
c->d P.D<\/p>\n\n\n\n

2NF(second normal form,\u7b2c\u4e8c\u6b63\u898f\u5316)<\/strong>
\u5206\u96e2\u90e8\u4efd\u76f8\u4f9d\u6027
\u6240\u6709\u7684\u5c6c\u6027,\u5b8c\u5168\u529f\u80fd\u76f8\u4f9d\u65bcprimary key
ps:\u4e5f\u5c31\u662f\u53ea\u53ef\u4ee5\u6709\u5b8c\u5168\u76f8\u4f9d\u6027
ex:
\u4ee51NF\u7684(a,b,c,d,e)\u70baexample,2NF\u5982\u4e0b
(a,c<\/u>,e)<\/strong> a,c->e F.D
(a<\/u>,b)<\/strong> a->b F.D
(c<\/u>,d)<\/strong> c->d F.D<\/p>\n\n\n\n

3NF(third normal form,\u7b2c\u4e09\u6b63\u898f\u5316)<\/strong>
\u5206\u96e2\u905e\u79fb\u76f8\u4f9d\u6027
ex:
\u5047\u59821NF\u5982\u4e0b
(a<\/u>,b,c,d)<\/strong>
a->b,c,d F.D
b->c,d T.D
\u52473NF\u5982\u4e0b
(b<\/u>,c,d)<\/strong> b->c,d F.D
(a<\/u>,b)<\/strong> a->b F.D<\/p>\n\n\n\n

BCNF<\/strong>
\u539f\u672c(a,b,c,d) \u76f8\u4f9d\u95dc\u4fc2\u70ba a,b->c,d FD \u548c c->b PD(\u7279\u6b8aTD)
\u63a8\u5c0e\u5982\u4e0b
\u3000step1, a,b->c,d = a,b->b,c,d
\u3000step2, \u56e0c->b ,\u6240\u4ee5\u53ef\u4ee5\u6539\u6210 a,c -> b,c,d
\u3000step3, a,c -> b,c,d = a,c -> b,d
\u56e0\u6b64\u7d50\u8ad6\u70baa,b->c,d \u548c c->b = a,c->b,d \u548c c->b
ex:
(stu_id<\/u>,staff_id<\/u>,class_code,grade)<\/strong>
stu_id,staff_id->class_code,grade FD
class_code->staff_id PD(\u7279\u6b8aTD)
\u8f49\u63dbBCNF\u5982\u4e0b
(stu_id<\/u>,class_code,grade)<\/strong> stu_id,class_code->,grade
(class_code<\/u>,staff_id)<\/strong> class_code->staff_id<\/p>\n\n\n\n

4NF<\/strong>
\u53bb\u9664\u591a\u503c\u5c6c\u6027
ps:\u5e38\u898b\u7684\u591a\u503c\u5c6c\u6027\u6709\u5b78\u6b77,\u7d93\u6b77,\u96fb\u8a71,…\u7b49
ex
\u4ee5\u4e0b\u8cc7\u6599\u8868\u7684\u5b78\u6b77\u548c\u7d93\u6b77\u70ba\u591a\u503c\u5c6c\u6027
(\u5b78\u865f<\/u>,\u5b78\u6b77,\u7d93\u6b77)<\/strong> \u5b78\u865f->>\u5b78\u6b77,>\u7d93\u6b77
99001 \u548c\u5e73\u570b\u5c0f,\u548c\u5e73\u570b\u4e2d,\u548c\u5e73\u9ad8\u4e2d \u548c\u5e73\u516c\u53f8,\u548c\u5e73\u5de5\u5ee0
99002 \u5e0c\u671b\u570b\u5c0f,\u5e0c\u671b\u570b\u4e2d \u5e0c\u671b\u4f01\u696d
\u8f49\u63db4NF\u5982\u4e0b,\u53bb\u9664\u591a\u503c\u5c6c\u6027
(\u5b78\u865f<\/u>,\u5b78\u6b77)<\/strong> \u5b78\u865f->\u5b78\u6b77
99001 \u548c\u5e73\u570b\u5c0f
99001 \u548c\u5e73\u570b\u4e2d
99001 \u548c\u5e73\u9ad8\u4e2d
99002 \u5e0c\u671b\u570b\u5c0f
99002 \u5e0c\u671b\u570b\u4e2d
(\u5b78\u865f<\/u>,\u7d93\u6b77)<\/strong> \u5b78\u865f->\u7d93\u6b77
99001 \u548c\u5e73\u516c\u53f8
99001 \u548c\u5e73\u5de5\u5ee0
99002 \u5e0c\u671b\u4f01\u696d
ex:
1NF,2NF,3NF,BCNF
(a,b,c,d) a->>b,>c,>d FD
\u8f49\u63db4NF\u5982\u4e0b
(a<\/u>,b)<\/strong> a->b
(a<\/u>,c)<\/strong> a->c
(a<\/u>,d)<\/strong> a->d<\/p>\n\n\n\n

…………….<\/p>\n\n\n\n

1-3NF\u7bc4\u4f8b<\/p>\n\n\n\n

1NF<\/strong>
(proj-num<\/u>,proj-name,emp_num<\/u>,emp_name,job_class,chg_hour,hours)<\/strong>
proj_num,emp_num->proj_name,emp_name,job_class,chg_hour,hours FD
proj_num->proj-name PD
emp_num -> emp_name,job_class,chg_hour PD
job_class -> chg_hour TD<\/p>\n\n\n\n

2NF<\/strong>
(proj_num<\/u>,emp_num<\/u>,hours)<\/strong>
proj_num,emp_num->hours FD
(proj_num<\/u>,proj-name)<\/strong>
proj_num->proj-name FD
(emp_num<\/u>,emp_name,job_class,chg_hour )<\/strong>
emp_num -> emp_name,,job_class,chg_hour FD
job_class -> chg_hour TD<\/p>\n\n\n\n

3NF<\/strong>
(proj_num<\/u>,emp_num<\/u>,hours)<\/strong>
proj_num,emp_num->hours FD
(proj_num<\/u>,proj-name)<\/strong>
proj_num->proj-name FD
(emp_num<\/u>,emp_name,job_class)<\/strong>
emp_num -> emp_name,,job_class FD
(job_class<\/u>,chg_hour)<\/strong>
job_class -> chg_hour FD<\/p>\n\n\n\n

\u5be6\u505a<\/strong>
assign(proj_num.fk,emp_num.fk,hours)
project(proj_num,proj-name)
employee(emp_num,emp_name,job_class.fk)
salary(job_class,chg_hour)
\u722a\u5716\u8868\u793a\u6cd5<\/strong>
project(proj_num,proj-name)1——< assign(proj_num.fk,emp_num.fk,hours)
assign(proj_num.fk,emp_num.fk,hours) >—— 1employee(emp_num,emp_name,job_class.fk)
employee(emp_num,emp_name,job_class.fk)>——-1salary(job_class,chg_hour)<\/p>\n\n\n\n

…………………………………………..<\/p>\n\n\n\n

1-4NF\u7bc4\u4f8b<\/p>\n\n\n\n

1NF<\/strong>
(a<\/u>,b,c<\/u>,d,e<\/u>,f,g,h,i, j)<\/strong>
a,c,e->b,d,f,g,h,>i,>j FD
a,c->b PD
c,e->f,g PD
f->e PD(\u7279\u6b8aTD)
c->d PD
g->h TD<\/p>\n\n\n\n

2NF<\/strong>
(a<\/u>,b,c<\/u>)<\/strong> a,c->b FD
(c<\/u>,d)<\/strong> c->d
(c<\/u>,e<\/u>,f,g,h)<\/strong>
c,e->f,g FD
f->e PD(\u7279\u6b8aTD)
g->h TD
(a<\/u>,c<\/u>,e<\/u>,i,j)<\/strong> a,c,f->>i,>j<\/p>\n\n\n\n

3NF<\/strong>
(a<\/u>,b,c<\/u>)<\/strong> a,c->b FD
(c<\/u>,d)<\/strong> c->d
(g<\/u>,h)<\/strong> g->h FD
(c<\/u>,e<\/u>,f,g)<\/strong>
c,e->f,g FD
f->e PD(\u7279\u6b8aTD)
(a<\/u>,c<\/u>,e<\/u>,i,j)<\/strong> a,c,f->>i,>j<\/p>\n\n\n\n

BCNF<\/strong>
(a<\/u>,b,c<\/u>)<\/strong> a,c->b FD
(c<\/u>,d)<\/strong> c->d
(g<\/u>,h)<\/strong> g->h
(c<\/u>,f<\/u>,g)<\/strong> c,f->g
(f<\/u>,e)<\/strong> f->e
(a<\/u>,c<\/u>,e<\/u>,i,j)<\/strong> a,c,f->>i,>j<\/p>\n\n\n\n

4NF<\/strong>
(a<\/u>,b,c<\/u>)<\/strong> a,c->b
(c<\/u>,d)<\/strong> c->d
(g<\/u>,h)<\/strong> g->h
(c<\/u>,f<\/u>,g)<\/strong> c,f->g
(f<\/u>,e)<\/strong> f->e
(a<\/u>,c<\/u>,f<\/u>,i)<\/strong> a,c,f->i
(a<\/u>,c<\/u>,f<\/u>,j)<\/strong> a,c,f->j<\/p>\n","protected":false},"excerpt":{"rendered":"

\u6b63\u898f\u5316\u76ee\u7684:\u53bb\u9664\u95dc\u806f\u7570\u52d5\u7684\u7570\u5e38\u73fe\u8c61 \u6b63\u898f\u5316\u904e\u7a0b1NF &gt …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"","fifu_image_alt":"","_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[16],"tags":[],"class_list":["post-591","post","type-post","status-publish","format-standard","hentry","category-database"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/posts\/591","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/comments?post=591"}],"version-history":[{"count":0,"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/posts\/591\/revisions"}],"wp:attachment":[{"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/media?parent=591"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/categories?post=591"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/tags?post=591"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}