Relational Algebra

1
?????? ??????

• Relational Algebra

2
?????? ??????

• ?????? ?????? ??? ???? ?? ?????? ?? ?????
• ?????? ?? ?? ????? ??? ???
• ????? ???? ?????? ?????? ?? ?????? ?????? ??
  ?????? ?? ?????? ??????, ????? ???? ????? ???????
  ???????
• ?????? ?? ?????? ?????? ????? ???? ?????? ???????
  ?? ???? ?????? ?????

3
??????? ?? ?????? ??????

• ??????? ?????? ??????? ?????
• ????
• ?????
• ????? ??????
• ?????
• ????
• ???? ?? ?????? ?????? ?? "????? ??"
• ?????? ?????? ?????? ?????? ????? ??????? ????????

4
????? ??????

• S
  R
• T

S Students T Teachers R Students, their
courses, and their teachers
5
???? Projection

• ????? ????? ??? ?????? (????? ?? ??? ???)
• ????? ????? ????? ??? ????????
• ?????? ????? ?????? ??????? ???? ?????? ??????
  ???????
• ?????? ???? ??? ????? ?? ??????, ?????? ???? ??
  ???? ??? ??? ?????, ?????? ???? ??????? ????
  ?????
• ??????? ????? ????? ?"? ???????? ??
  ??????? ?? ????? ??????? ??????

6
???? (????)

• ??? ???? ???? ?? ??? R ?? ??????? tid ?- course
  ?????? ?? ??????
• ??????

7
Selection ?????

• ????? ?????? ??? ?????? (????? ?? ??? ???)
• ????? ?????? ????? ??? ?? ???????
• ?????? ?? ???? ??? (??? ?? ????) ???? ?????? ????
  ???? ??????
• ?? ????? ?????? ??? ???? ??????? ????? ??????
• ??????? ?????? ????? ?"? ???????? ??
  ??????? ?? ????? ???? ?????? ??????
  

8
????? (????)

• ??? ????? ????? R ?? ??????? ?? ????? ??-id ???
  ??? 20, ?????? ?? ??????
• ??????

9
????? ??????

• ?????? ??? ???? ?? ????? ??????? ????? ?????
  ??-id ??? ??? 20
• ??????

10
????? ?? ????? ??????

• ????? ?????? ?? ????? ?????? ??? , ???? C
  ??? ?????
• ????? ????? ???????? ???????? ?"? ?????? ??????
  ?? ?- (??? ???? ??????)
• ?????? ??? ??????
• attribute1 op attribute2 ??
• attribute1 op constant

11
?????? ???????

• ?????? ??????? (op) ??
• ????? ???? ???? ?"? ????? ???????
• ?? ????? ????? ??????? (char string), ??? ??????
  ???? ??? ??? ??????, ??????
• 
  

12
????

• ??????? ?????? ??? ?????? ????????? ?? ??????
  ?????, ???? substring, ??? ????? ?????? ?????? ??
  ?????? ???? ???? ???? ????? ????? ??????
  

13
????? ?????? Cross Product

• ????? ?????? ??? ????? ???????, ??????? ?"? ,
  ???? ????? ?? ????? ?? ??? ??? ?? ?? ????? ??
  ???? ????
• ???? ??????? ?????? ???? ????? ????? ???????
  ?????? ????????
• ???? ??????? ?????? ???? ?????? ????? ???????
  ?????? ????????

14
????? ?????? (????)

• S
  R
• T

S Students T Teachers R Students, their
courses, and their teachers
15
(No Transcript)
16
???? ?????? ??? ????? ?????? ?? ??????
????????. ?????? ????? ?????? ???????? ??????
????? ?????????? ???????, ?????? ????? ?? ????
??????????? ????. ??????, ??????? ?? ?????????
?????? ?????? ???????, ??? ????? ?????? ???
??????? ??? ??. ???????, ???? ??????? ??????
???????, ??? ????? ??, ???? ????? ?? ???? ??????
?? ??????? ?????? ??????? ??????? ???????? ?? ??
R ???????? ?? T.
17
????? ?? Renaming

• ?????? ?????? ?"?
• ??????, ????? ?? ??????
• ????? ?? ???? ?? ?????? ???????
  ????? ?????? ?? ????? P(tid1,sid,course,tid2,tnam
  e,dept)
• ?? ????? ?? ?????? ??? P ????????? ??????? ????
  ???????????

18
???????

• ???? ????? ?? ??? ???? ??????? ????? ???? ??? ?"?
  ?????? ????
• ?????? ????? (?????? ????) ???????? ??????
  ??????? ??? ????????
• ?????? ?? ????? ??? ??? ?? ???? ?????? ???? (???,
  ??? ??? ?????? ?? ???? ??)

19
(No Transcript)
20
????? ?????

• ?????, ???? ???????? ??? ???? ????????? ???
• ?? ?????? ????? ??? ??? ???? ?????? ????

21
(No Transcript)
22
????

• ?? ?????? ?????? ??????? ?????? ??????, ?? ????
  ???? ?????? ?"? ??????
• ?????? ?????? 1 ?- 4
• ?????? ????? 1 ?????
• ???????, ?????? ???? ???? ?????? ?"? 1, 4
  ???', ??????

23
?????Union

• ????? ??? ?????? ????? ???? ????, ????? ???
  ??????, ?????
• ???? ?????? ???? ???? ?????? ????? ???? ???????,
  ??????
• ?????? ????? ???? ?? ??? ????? ???? type (???? ??
  ??????? ?? ????? type ??? ?????)
• ?? ?? ???????? ????? ?? ??????? (?? ???? ????),
  ?? ?????? ???
• ???? ?????? ???? ???? ??????, ??????
• ?????? ????? ???? ????? ??? ????? ???? type

24
?????

25
????Difference

• ????? ??? ?????? ???? ???? ???? ????? ???? ???
  ?????, ????? ??? ??????, ??? ?????? ????? ??
  ????? ??????

26
?????

27
????? Intersection

• ????? ???? ???? ?????? ????? ?? ?????
• ??? ????? ????? ?????? ????? ?????? ????? ??????
  ?????

28
?????

29
????? Join

• ????? ?????? ??? ????? ????? ?? ????? ??????
  ?????? (??????? ??????? ?? ????)
• ????? ?????
• condition join ?? theta join (????? ?????)
• equi-join (??? ??????)
• natural join

30
Condition Join

• ????? ????? ??? ????? ?????? ????
• C ??? ????? ?????? ??????? ?????? ( , )
  ????????? ??????
• ????? ?? ?????? ??? ??? ???????? ????? A ?? R
  ???? ???????? ????? B ?? S
• ???? ?? ??? ???? ?????????? ?? ???????, ???
• ??????

31
???? ??????

• ??????
• ???? ????? ?????? (??????) ??? C, ???? ???????
  ?????? (???? ???? ?? ????????) ????? ??? C?
• ?? ?- C ?????? ???????
• ?? ?- C ????? ?????? ???????
• i ??? ?????? ?? ?????? A ?- R
• kjn, ???? j ??? ?????? ?? ?????? B ?- S ?- n
  ??? ???? ??????? ?- R

32
Equi-Join

• ????? ??????? ??? ????? ?????? ????
• ???? C ??? ????? (??????????) ?? ??????? ??????
• ?????, ?? ?????? ??? ?????? ??? ???????? ????? A
  ?? R ???? ???????? ????? B ?? S
• ??????

33
?????? ???? ?? Equi-Join

• ????? ?????? ??? ????? ?????? ????
• ???? C ??? ????? (??????????) ?? ??????? ??????
• R.A ????? ???????? A ?? R, ?- S.A ????? ????????
  A ?? S (???? ??????????? ???? ???)
• ??????

34
???? ??????

• ??????
• ???? ??? C ???? C ??? ??? ????? ?????
• L ????? ?? ???? ??? (???, ????? ???) ??? ???
  ?????????? ???????? ?- C ?????, L ????? ?? ??
  ??????????? ?????? (???, ??? ??? ??????? ????? C
  ?? ??????)

35
??? ????? ?????? (??? ????)

• ????? ?????? ???? ?? ????? ????? ????
• L ??? ????? ??????????? ???????? ????? C ?????,
  ?? ???????? A ????? ?????? L, ?? ????? C ??
  ?????? ???? ?? ???????

36
????? ???? Natural Join

• ?? ?????? L ??????? ?? ??????????? ???????? ?- R
  ??- S, ?? ?????? ????? natural join, ??????? ????
  ??? ??? ??????
• ?????, ?????? ???? ?????? ?? ?? ????? ???????????
  ???????? ?- R ??- S ???????? ?? ???? ??? ??? ???
  ?????? ??????