{"id":587,"date":"2010-08-24T13:41:00","date_gmt":"2010-08-24T05:41:00","guid":{"rendered":"http:\/\/note.systw.net\/note\/?p=587"},"modified":"2023-11-04T13:44:16","modified_gmt":"2023-11-04T05:44:16","slug":"%e9%97%9c%e8%81%af%e5%bc%8f%e4%bb%a3%e6%95%b8","status":"publish","type":"post","link":"https:\/\/systw.net\/note\/archives\/587","title":{"rendered":"\u95dc\u806f\u5f0f\u4ee3\u6578"},"content":{"rendered":"\n

Relational Algebra(\u95dc\u806f\u5f0f\u4ee3\u6578)<\/strong>
\u4f9d\u6027\u8cea\u5206\u70ba\u4ee5\u4e0b
Unary Relational Operation(\u4e00\u5143\u95dc\u806f\u64cd\u4f5c):\u5c0d\u55ae\u4e00\u95dc\u806f\u64cd\u4f5c
Binary Relational Operation(\u4e8c\u5143\u95dc\u806f\u64cd\u4f5c):\u5c0d\u5169\u500b\u95dc\u806f\u64cd\u4f5c,\u4e3b\u8981\u5728\u65bcjoin(\u5408\u4f75)
Set Theory Operation(\u96c6\u5408\u8ad6\u64cd\u4f5c):\u4ee5\u96c6\u5408\u8ad6\u70ba\u57fa\u790e
Aggregate Function(\u805a\u5408\u51fd\u6578)\u8a08\u7b97<\/p>\n\n\n\n

Set Theory Operation<\/strong>
\u5305\u62ec
Intersection Operation(\u4ea4\u96c6\u64cd\u4f5c\uff09
Union Operation(\u806f\u96c6\u64cd\u4f5c)
Difference Operation(\u5dee\u96c6\u64cd\u4f5c)<\/p>\n\n\n\n

Aggregate Functions<\/strong>
\u805a\u5408\u51fd\u6578\u5305\u62ec
\u8a08\u7b97\u52a0\u7e3d\u7684Sum()\u51fd\u6578
\u8a08\u7b97\u5e73\u5747\u7684Average()\u51fd\u6578
\u8a08\u7b97\u7b46\u6578\u7684Count()\u51fd\u6578
\u6700\u5927\u503c\u7684Max()\u51fd\u6578
\u6700\u5c0f\u503c\u7684Min()\u51fd\u6578
\u8868\u793a\u6cd5\u70ba:< \u7fa4\u7d44\u5c6c\u6027> F< \u51fd\u6578\u8868\u5217>(column operation)(R)
ex:
< \u8a02\u55ae\u7de8\u865f> F sum(\u5be6\u969b\u55ae\u50f9\u00d7\u6578\u91cf)(\u8a02\u55ae\u660e\u7d30)<\/p>\n\n\n\n

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

Unary Relational Operation<\/strong>
\u5305\u62ec
SELECT operation(\u9078\u53d6\u64cd\u4f5c)<\/strong>
\u3000\u5f9etable\u4e2d\u9078\u53d6col=value\u7684\u8cc7\u6599
\u3000\u8868\u793a\u6cd5\u70basigma(col=value)(table)
PROJECT operation(\u6295\u5f71\u64cd\u4f5c)<\/strong>
\u3000\u53ea\u986f\u793atable\u4e2dcol1[,col2,…]\u6b04\u4f4d\u7684\u8cc7\u6599
\u3000\u8868\u793a\u6cd5\u70bapi(col1[,col2,…])(table)
RENAME operation(\u66f4\u540d\u64cd\u4f5c)<\/strong>
\u3000\u8868\u793a\u6cd5\u70ba\u66ab\u5b58\u95dc\u806f(new col name1[,new col name2,…])=SELECT or PROJECT operation<\/p>\n\n\n\n

\u66ab\u5b58\u95dc\u806f<\/strong>
\u66ab\u6642\u5132\u5b58\u67d0\u4e9b\u64cd\u4f5c\u5f8c\u6240\u7522\u751f\u7684\u95dc\u806f
\u76ee\u7684:\u5c07\u4e00\u9023\u4e32\u7684\u64cd\u4f5c\u5206\u70ba\u6578\u500b\u7368\u7acb\u7684\u64cd\u4f5c\u5206\u5225\u9032\u884c
ex1:
\u7b2c\u4e00\u7a2e
1\u5148\u7be9\u9078\u8cc7\u6599
\u7537\u6027\u54e1\u5de5 = sigma(\u6027\u5225=\u2018\u7537’)(\u54e1\u5de5)
2\u5728\u7be9\u9078\u6b04\u4f4d
pi(\u54e1\u5de5\u7de8\u865f,\u59d3\u540d,\u8077\u7a31,\u6027\u5225,\u5730\u5740)(\u7537\u6027\u54e1\u5de5)
ex2:
\u7b2c\u4e8c\u7a2e
1\u5148\u7be9\u9078\u6b04\u4f4d
\u66ab\u5b58\u54e1\u5de5= pi(\u54e1\u5de5\u7de8\u865f,\u59d3\u540d,\u8077\u7a31,\u6027\u5225,\u5730\u5740)(\u54e1\u5de5)
2\u5728\u7be9\u9078\u8cc7\u6599
sigma(\u6027\u5225=\u2018\u7537’)(\u66ab\u5b58\u54e1\u5de5)<\/p>\n\n\n\n

\u5c07\u4e00\u9023\u4e32\u7684\u64cd\u4f5c\u6574\u5408\u6210\u55ae\u4e00\u64cd\u4f5c\u9032\u884c<\/strong>
ex1:
\u5148\u6295\u5f71\u64cd\u4f5c,\u5f8c\u9078\u53d6\u64cd\u4f5c
sigma(col=value)(
pi(col1[,col2,…])(table1)
)
ex2:
\u5148\u6295\u5f71\u64cd\u4f5c,\u5f8c\u9078\u53d6\u64cd\u4f5c
pi(col1[,col2,…])(
sigma(col=value)(table2)
)
ps
\u6ce8\u610f\u4e8b\u9805,\u4e0d\u53ef\u4ea4\u63db\u7684\u60c5\u5f62
ex:
\u6295\u5f71\u64cd\u4f5c\u6642\u7121\u6027\u5225\u7684\u6b04\u4f4d,\u56e0\u6b64\u5728\u9078\u53d6\u64cd\u4f5c\u7121\u6cd5\u4f9d\u6027\u5225\u505a\u904e\u6ffe
sigma(\u6027\u5225=\u2018\u7537’)(
pi(\u54e1\u5de5\u7de8\u865f,\u59d3\u540d,\u8077\u7a31,\u5730\u5740)(\u54e1\u5de5)
)<\/p>\n\n\n\n

ex1<\/strong>
\u5217\u51fa\u54e1\u5de5sex(\u6027\u5225)\u70baM,salary(\u85aa\u6c34)>32000\u7684\u54e1\u5de5\u59d3\u540d(EMPLOYEE.fname,EMPLOYEE.lname)<\/strong>
pi(fname,lname)(
sigma(sex=M && salary>32000)(employee)
)
…………………………………………………………………………….<\/p>\n\n\n\n

Binary Relational Operation<\/strong>
\u5305\u62ec
CROSS JOIN<\/strong>
\u3000\u8868\u793a\u6cd5\u70baR X S
THETA JOIN<\/strong>
\u3000\u8868\u793a\u6cd5\u70baR X(\u689d\u4ef6\u60c5\u5f62) S
\u3000\u5305\u542b==,< ,>,!=,=>,=<
EQUIJOIN JOIN <\/strong>\u5c6c\u65bcTHETA JOIN\u7684\u5b50\u96c6\u5408
\u3000\u53ea\u5305\u542b==, \u76f8\u540c\u5c6c\u6027\u4ecd\u7136\u6703\u5728\u51fa\u73fe,\u4e5f\u5c31\u662f\u6703\u67092\u6b04\u4f4d\u540d\u7a31\u4e00\u6a23
\u3000\u8868\u793a\u6cd5\u70baR |X| (\u689d\u4ef6\u60c5\u5f62)S
\u3000ex: (R) |X|(R.a=S.b)(S) \u7559\u4e0bR.a\u548cS.b
NATURE JOIN<\/strong>
\u3000\u53ea\u5305\u542b==,\u76f8\u540c\u5c6c\u6027\u53ea\u6703\u51fa\u73fe\u4e00\u6b21,\u4e5f\u5c31\u662f\u53ea\u7559\u4e0b\u4e00\u6b04\u4f4d
\u3000\u8868\u793a\u6cd5\u70baR * (\u689d\u4ef6\u60c5\u5f62)S = R |X|(r.a=s.b)S
\u3000ex: (R) *(R.a=S.b)(S) \u53ea\u7559\u4e0bR.a
OUTER JOIN<\/strong>
\u3000\u8868\u793a\u6cd5\u5206\u70ba
\u3000left outer join R ]x| (\u689d\u4ef6\u60c5\u5f62)S
\u3000right outer join R |x[ (\u689d\u4ef6\u60c5\u5f62)S
\u3000full outer join R ]x[ (\u689d\u4ef6\u60c5\u5f62)S<\/p>\n\n\n\n

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


ex1
\u627e\u51fajohn smith\u7684\u89aa\u5c6c(depedent_name,relationship)<\/strong>
\u8cc7\u6599\u8868\u5982\u4e0b
employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno)
depandent(essn,depandent_name,sex,bdate,relationship)
1
\u627e\u51faemployee\u70bajohn smith\u7684\u8cc7\u6599
emp_john=pi(fname,lname,ssn) (
sigma(fname=john && lname=smith)(employee)
)
2
john\u89aa\u5c6c\u689d\u4ef6emp.ssn=depadent.ssn
pi(depedent_name,relationship)(
(employee) |X|(emp_john.ssn=depadent.essn)(depandent)
)
ex2
\u5217\u51fa\u6bcf\u500b\u90e8\u9580\u7684\u7d93\u7406\u59d3\u540d(DEPARTMENT.dname,EMPLOYEE.fname,EMPLOYEE.lname)<\/strong>
\u8cc7\u6599\u8868\u5982\u4e0b
employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno)
department(dname,dnumber,mgr_ssn,mgr_start_date)
1
pi(dname,fname,lname)(
(department) |X|(department.mgr_ssn=employee.ssn)(employee)
)
ex3
\u5217\u51fa\u5c08\u6848productx\u76ee\u524d\u6709\u90a3\u4e9b\u54e1\u5de5\u5728\u8ca0\u8cac(employee.fname,employee.lname)<\/strong>
\u8cc7\u6599\u8868\u5982\u4e0b
employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno)
project(pname,pnumber,plocation,dnum)
works_on(essn,pro,hours)
1
ptab=pi(pname,pnumber)(
sigma(pname=projectx)(project)
)
2
pwtab=pi(essn)(
(ptab)|X|(pnumber=pro)(works_on)
)
3
result=pi(fname,lname)(
(pwtab)|X|(essn=ssn)(employee)
)
ex4
\u5217\u51fa\u6bcf\u500b\u5c08\u6848\u76ee\u524d\u6709\u54ea\u4e9b\u54e1\u5de5\u8ca0\u8cac(PROJECT.pname,EMPLOYEE.fname,EMPLOYEE.lname)<\/strong>
\u8cc7\u6599\u8868\u5982\u4e0b
employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno)
project(pname,pnumber,plocation,dnum)
works_on(essn,pro,hours)
\u65b9\u6cd5a
1
pwtab=pi(essn,pname)(
(project)|X|(pnumber=pno)(works_on)
)
2
result=pi(PWTAB.pname,EMPLOOYEE.fname,EMPLOYEE.lname)(
(pwtab)|X|(essn=ssn)(employee)
)
\u65b9\u6cd5b
R1=((project)|X|pnumber=pno(works_on))|X|essn=ssn(employee)
result=pi(pname,fname,lname)(R1)
\u65b9\u6cd5c
result=pi(PROJECT.pname,EMPLOOYEE.fname,EMPLOYEE.lname)(
\u3000(employee)|X|(ssn=essn)(
\u3000\u3000(project)|X|(pnumber=pno)(works_on)\u3000\u3000
\u3000\u3000)
\u3000)
ex5
\u5217\u51fa\u8ca0\u8cac\u5c08\u6848\u6240\u5728\u5730\u70bahouston\u7684\u54e1\u5de5,\u4f46\u90e8\u9580\u4e0d\u5728houston(fname,lname,address)<\/strong>
\u8cc7\u6599\u8868\u5982\u4e0b
employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno)
project(pname,pnumber,plocation,dnum)
works_on(essn,pro,hours)
dept_locations(dnumber,dlocation)
1
R1=(((project)|X|pnumber=pno(works_on))|X|essn=ssn(employee))|X|dno=dnumber(dept_locations)
2
result=pi(fname,lname,address)(
segma(dlocation=houston && plocation=houston)(R1)
)<\/p>\n\n\n\n

ps:
\u905e\u8ff4\u5c01\u9589\u5f0f\u64cd\u4f5c
\u54e1\u5de5 = pi(\u54e1\u5de5\u7de8\u865f, \u59d3\u540d, \u5831\u544a\u4eba)(\u54e1\u5de5)
\u4e0a\u53f8 = pi(\u54e1\u5de5\u7de8\u865f, \u59d3\u540d, \u8077\u7a31, \u5831\u544a\u4eba)(\u54e1\u5de5)
tmp_result = (\u54e1\u5de5)|X| \u54e1\u5de5.\u5831\u544a\u4eba=\u4e0a\u53f8.\u54e1\u5de5\u7de8\u865f && \u8077\u7a31=’\u696d\u52d9\u7d93\u7406'(\u4e0a\u53f8)
result = pi(\u54e1\u5de5.\u54e1\u5de5\u7de8\u865f, \u54e1\u5de5.\u59d3\u540d, \u4e0a\u53f8.\u54e1\u5de5\u7de8\u865f, \u4e0a\u53f8.\u59d3\u540d)(tmp_result )<\/p>\n\n\n\n

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

\u96c6\u5408\u9664\u6cd5<\/strong>
R \/ S
\u5217\u51faS\u5728R\u4e2d\u540c\u6642\u51fa\u73fe\u7684\u8cc7\u6599<\/p>\n\n\n\n

R(a ,b)
a1 b1
a1 b2
a2 b1
a2 b3
a3 b1
a3 b2
a3 b3<\/td>		S(b)
b1
b2<\/td>		R\/S
S\u4e2db\u7684b1,b2\u5728R\u4e2da1,a3\u8cc7\u6599\u4e2d
(a)
a1
a3<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n


ex1
\u5217\u51fa\u540c\u6642\u5728\u6bcf\u500b\u5c08\u6848\u4e2d\u5de5\u4f5c\u7684\u54e1\u5de5(EMPLOYEE.fname,EMPLOYEE.lname)<\/strong>
\u8cc7\u6599\u8868\u5982\u4e0b
employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno)
project(pname,pnumber,plocation,dnum)
works_on(essn,pro,hours)
1
R(pno)=pi(pnumber)(project)
2
\u5728works_on\u4e2d\u53d6\u5f97\u64c1\u6709\u5168\u90e8R.pno(=WORKS_ON.pno)\u7684essn
R1=(work_on)\/(R)
3
result=pi(fname,lname)(
(EMPLOYEE)|X|ssn=essn(R1)
)<\/p>\n\n\n\n

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

\u96c6\u5408\u6e1b\u6cd5<\/strong>
R – S<\/p>\n\n\n\n

R(ssn)
1
2
3
4
5<\/td>		S(ssn,pno)
1 a
2 a
3 a
4 a
1 b
2 b
3 b
1 c
3 c <\/td>		R-S
R\u4e2d\u76845\u6c92\u6709\u51fa\u73fe\u5728S\u7684ssn\u8cc7\u6599\u4e2d
(ssn)
5<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

ex1
\u5217\u51fa\u6c92\u6709\u5c08\u6848\u7684\u54e1\u5de5<\/strong>
\u8cc7\u6599\u8868\u5982\u4e0b
employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno)
project(pname,pnumber,plocation,dnum)
works_on(essn,pro,hours)
1
R1(ssn)=pi(essn)(works_on)
2
R2(ssn)=pi(ssn)(employee)
3
R3=R2-R1
4
result=pi(fname,lname)(
(employee)*(R3)
)
ex2
\u5217\u51fa\u7121\u89aa\u5c6c\u7684\u7d93\u7406<\/strong>
\u8cc7\u6599\u8868\u5982\u4e0b
employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno)
depandent(essn,depandent_name,sex,bdate,relationship)
1
R(ssn)=pi(super_ssn)(employee)
2
S(ssn)=pi(essn)(dependent)
3
T=R-S<\/p>\n","protected":false},"excerpt":{"rendered":"

Relational Algebra(\u95dc\u806f\u5f0f\u4ee3\u6578)\u4f9d\u6027\u8cea\u5206\u70ba …<\/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-587","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\/587","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=587"}],"version-history":[{"count":0,"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/posts\/587\/revisions"}],"wp:attachment":[{"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/media?parent=587"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/categories?post=587"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/tags?post=587"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}