The Relational Model

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

• The Relational Model

2
??????

• Relation ?????????????
• Key
• Integrity Constraints (IC)
• Relational Algebra

3
The Relational Model

• ????????? Dr. Edgar Frank Codd (E.F. Codd)
  ?????????????????????????? A Relational Model of
  Data for Large Shared Data Banks ???? ?.?. 1970
• ????????????????????????????????
• ????????????????????????????????(????? Set)
• ??????????????????????????????????????????????(Dat
  a Independence)
• ?????????????????????????????????(Relational
  Database Management System RDBMS)
  ????????????????? ???? DB2 , Oracle , Informix ,
  MS SQL Server , MySQL , Ingres , MS Access ???
• ?????? SQL ??????????????????

4
???????(Relation)

• ??????? ???????????(Table) 2 ????
  ????????????????????????(Attribute)
  ???????????(Column) ??? ??????(Tuple) ????
  ???(Row)
• ??????? ???????????????????(Base Relation)
  ?????????????????????????
• ????????? Create Table ??????? SQL
  ????????????????????????????

5
???????????(Attribute)

• ??????? ???????????? ? ??????????? ?
  ???????????????????????????????????????
• ???????????? ??????? ???????? ??????????
  ??????????? ???????????? , ???? , ??????? ,
  ??????? ???????
• ?????????????????????????????? ? ????????
  ?????(Degree)
• ???? ??????? ???????? ??????? ??????? 4

6
??????(Tuple)

• ??????? ??????????????????????????
• ?????????????????????????? ???????????????????
  ???????????? ?? ???????????????????????
• ????????????????????????? ? ????????
  ??????????????(Cardinality)

7
Relation name
Attribute
Student Student Student Student Student Student Student
Std_ID Std_Fname Std_Lname Std_Address Std_Major Std_GPA Fac_ID
1234567801 Bob Norbert USA IT 3.00 Sc
1234567821 Wiliam Pilgrim AUS CS 2.50 Sc
1234567822 Luke Brazzi USA PHY 3.50 Sc
1234567840 Homer Wells AUS CS 2.50 Sc
2345678910 Candy Kendall NZ FIN 2.75 Msc
2345678920 Roberto Morales AUS FIN 3.25 Msc
2345789015 Tess Dodge CAN ECON 3.75 Msc
4567890122 Joe Estrada CAN Art 3.25 En
4567890133 Cristopher Colan USA MUS 2.75 En
5678901235 Wally Kendall NZ GSc 3.50 Ed
5678901240 Mariah Dodge CAN Gsc 2.50 Ed
Cardinality
Tuple
Degree
8
?????(Domain)

• ?????????? ???????????????????????????????????????
  ????????
• ???????????? ?????????????????????
  ?????????????????????? ???????????????????????????
  ??????????????? ? ????????? ??????????????????????
  ???????(Data Integrity)

9
?????(Domain)

• ???????????? ??????????? ??? ?????????????????????
  ????????????????????????? ????????????????????????
  F ???? M

Domain
M,F
EmpID Name Sex Age Salary DeptID
E001 Peter M 24 55,000 D01 E002
Nicole F 30 12,000 D02
10
?????(Domain)

• ???????????? ??????????? ???? ????????????????????
  ?????????????????????????????? ???????????????????
  ??????????? 18 ??

gt 18
Domain
EmpID Name Sex Age Salary DeptID
E001 Peter M 24 55,000 D01 E002
Nicole F 30 12,000 D02
11
?????(Domain)

• ???????????? ??????????? ?????????
  ????????????????????????????????????????????
  ??????????????????????????? 0

gt 0
Domain
EmpID Name Sex Age Salary DeptID
E001 Peter M 24 55,000 D01 E002
Nicole F 30 12,000 D02
12
???????????????????

• ??????????????????????
• ????????????(cell)???????? ?????????????? 1
  ???????????
• ??????????????????????????????????????????????????
  ??
• ??????????????????????????????????????????????????
  ????????????????
• ???????????????????????????????????
• ????????????????????????????????????????????????
• ???????????????????????????????????????????

13
???????????????

• CREATE TABLE Student
• ( Std_ID CHAR(11),
• Std_FName VARCHAR(20),
• Std_LName VARCHAR(30),
• Std_Address VARCHAR(50),
• Std_Major VARCHAR(15),
• Std_GPA DECIMAL(3,2),
• Fac_ID CHAR(5)
• )

14
???????? Relation
EmployeeNumber FirstName LastName Department Email Phone
100 Jerry Johnson Accounting JJ_at_somewhere.com 236-9987
200 Mary Abernathy Finance MA_at_somewhere.com 444-8898
300 Liz Smarthers Finance LS_at_somewhere.com 777-0098
400 Tom Caruthers Accounting TC_at_somewhere.com 236-9987
500 Tom Jackson Production TJ_at_somewhere.com 444-9980
600 Eleanore Caldera Legal EC_at_somewhere.com 767-09000
700 Richard Bandalone Legal RB_at_somewhere.com 767-0900
15
???????? Table ????????? Relation
EmployeeNumber FirstName LastName Department Email Phone
100 Jerry Johnson Accounting JJ_at_somewhere.com 236-9987
200 Mary Abernathy Finance MA_at_somewhere.com 444-8898
300 Liz Smarthers Finance LS_at_somewhere.com 777-0098
400 Tom Caruthers Accounting TC_at_somewhere.com 236-9987 , 266-9987 , 555-7171
500 Tom Jackson Production TJ_at_somewhere.com 444-9980
600 Eleanore Caldera Legal EC_at_somewhere.com 767-09000
700 Richard Bandalone Legal RB_at_somewhere.com 767-0900 , 767-0011
16
???????? Table ????????? Relation
EmployeeNumber FirstName LastName Department Email Phone
100 Jerry Johnson Accounting JJ_at_somewhere.com 236-9987
200 Mary Abernathy Finance MA_at_somewhere.com 444-8898
300 Liz Smarthers Finance LS_at_somewhere.com 777-0098
400 Tom Caruthers Accounting TC_at_somewhere.com 236-9987
Fax [phone]
Home 555-7171
500 Tom Jackson Production TJ_at_somewhere.com 444-9980
600 Eleanore Caldera Legal EC_at_somewhere.com 767-09000
Fax [phone]
Home 555-7171
700 Richard Bandalone Legal RB_at_somewhere.com 767-0900
17
????????????? (Keys)

• Candidate Key
• Primary Key
• Composite Key
• Alternate key
• Foreign Key

18
Candidate Key

• ??????? ??????????? ???? ???????????????????
  ????????????????????????????????????????????? 1
  ??????
• ???? ??????????? , ??????????????

19
Candidate Key
???????????? ???? ??????? ??????????? ???????????
4804234207 ???????? ???????? 3120100755991 ??????????
4804234208 ????? ??????? 3110211212005 ??????????
4804234210 ????? ????? 3141901485662 ??????????
4804234211 ???????? ????????? 3120233125447 ???????????????????
4804234215 ????? ??????? 3155002551549 ???????????????????
4804234219 ?????? ???????? 3112251445345 ???????????????????
4804234222 ????? ???????? 3122234567657 ?????
4804234225 ????? ??????? 3122343231156 ?????
20
Primary Key

• ??????? Candidate Key ????????????????????????????
  ????????????? ???????
• Primary key ??? ??????????? ????
  ??????????????????? ??????????????????????????????
  ? ??????????????????????????????????????? ?
  ???????????????????

21
Primary Key
???????????? ???? ??????? ??????????? ???????????
4804234207 ???????? ???????? 3120100755991 ??????????
4804234208 ????? ??????? 3110211212005 ??????????
4804234210 ????? ????? 3141901485662 ??????????
4804234211 ???????? ????????? 3120233125447 ???????????????????
4804234215 ????? ??????? 3155002551549 ???????????????????
4804234219 ?????? ???????? 3112251445345 ???????????????????
4804234222 ????? ???????? 3122234567657 ?????
4804234225 ????? ??????? 3122343231156 ?????
Primary Key
22
Composite Key

• ??????? ???????? ? ???????????????????????????????
  ??? 1 ???????????

23
Composite Key
???????????? ???? ??????? ??????????? ???????????
4804234207 ???????? ???????? 3120100755991 ??????????
4804234208 ????? ??????? 3110211212005 ??????????
4804234210 ????? ????? 3141901485662 ??????????
4804234211 ???????? ????????? 3120233125447 ???????????????????
4804234215 ????? ??????? 3155002551549 ???????????????????
4804234219 ?????? ???????? 3112251445345 ???????????????????
4804234222 ????? ???????? 3122234567657 ?????
4804234225 ????? ??????? 3122343231156 ?????
Composite Key
24
Alternate Key

• ??????? ???????????(Candidate key)
  ????????????????????????????????(Primary Key)

25
Alternate Key
???????????? ???? ??????? ??????????? ???????????
4804234207 ???????? ???????? 3120100755991 ??????????
4804234208 ????? ??????? 3110211212005 ??????????
4804234210 ????? ????? 3141901485662 ??????????
4804234211 ???????? ????????? 3120233125447 ???????????????????
4804234215 ????? ??????? 3155002551549 ???????????????????
4804234219 ?????? ???????? 3112251445345 ???????????????????
4804234222 ????? ???????? 3122234567657 ?????
4804234225 ????? ??????? 3122343231156 ?????
Primary Key
Alternate Key
26
Foreign Key

• ??????? ??????????????????????????????????????????
  ???????????? ?
• ????????????????????? Foreign key ?? child
  relation ???????????????? ??????????????????
  Primary key ?? Parent relation

27
Foreign Key
Primary key
Faculty Faculty Faculty
Fac_ID Fac_Name Phone
Sc Science 201
Msc Management Science 145
En Engineering 512
Ed Education 301
Foreign key
Student Student Student Student Student Student Student
Std_ID Std_Fname Std_Lname Std_Address Std_Major Std_GPA Fac_ID
1234567801 Bob Norbert USA IT 3.00 Sc
1234567821 Wiliam Pilgrim AUS CS 2.50 Sc
1234567822 Luke Brazzi USA PHY 3.50 Sc
1234567840 Homer Wells AUS CS 2.50 Sc
2345678910 Candy Kendall NZ FIN 2.75 Msc
2345678920 Roberto Morales AUS FIN 3.25 Msc
2345789015 Tess Dodge CAN ECON 3.75 Msc
4567890122 Joe Estrada CAN Art 3.25 En
4567890133 Cristopher Colan USA MUS 2.75 En
5678901235 Wally Kendall NZ GSc 3.50 Ed
5678901240 Mariah Dodge CAN Gsc 2.50 Ed
Parent relation
Child relation
28
????????????????????(Foreign Key)

• 1. ?????????????????????????????????????????
• 2. ?????? FK ?? ??????? ??????????????????
  ???????????? ?????? ??????? ???????????????????
  ??????????????? ????????? FK ?? ??????? ?????
  ????????????????????????????? FK ?????? ???????
  ???????????????????
• 3. ???????? ??????????? ??????????????? FK
  ??????????????????? ??????????? ???????????? CK
  ??? ??????? ???????????????????? ???????????
  ????????????????????????????????? FK

29
???????????? Foreign Key

• 4. Domain ??? ??????????? ???????????? FK
  ???????????????????????? Domain ??? ???????????
  ??????? CK ??? ??????? ??????????????
• 5. ??????????? ???????????????? FK
  ????????????????? CK ??? ???????????
• 6. ??????? ???????????????? FK ?????????????
  Referencing Relation ???? ???????
  ?????????????? CK ?????????????? FK
  ?????????????? Referenced Relation ???? Target
  Relation

30
???????????? Foreign Key

• 7. ?????????? ????????????????? Referenced
  Relation ??? Referencing Relation
• 8. ?????????? ???????????????????????? FK
  ??????????????? (Self-Referencing)

EmpID Ename Esurname EMgr
E001 Peter Corp E002 E002 Nicole Terio - E003
Willy Mac E002
FK
PK
FK
31
Alternate key
Foreign key
Primary key
????????
?????????
???????
???
???????
???????
????
???????????
B005
30000
1 ??. 2516
?
?????????
????????
?????
SUD21
B007
12000
10 ??. 2519
?
???????
????????
???
SNK37
B007
20000
24 ???. 2517
?
???????????
??????????
?????
SNK14
B002
9000
19 ??. 2521
?
???????
??????
????????
SUB09
B003
25000
3 ??. 2518
?
?????????
?????????
??????
SUD04
B004
9000
13 ???. 2520
?
???????
?????????
???????
SNR41
Candidate key
Primary key
32
???????????????????

• Data Integrity

33
?????????? (Integrity Constraints)

• ??????????????????? ???????????????? ????
  ???????? ????????????????????????????????
• ?????????????????? ???
• Domain Constraints
• Entity Integrity
• Referential Integrity

34
Domain Constraints

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

35
Entity Integrity

• ???????????????????????(Entity Integrity Rule)
  ???????? ???????????????????????????????????????(
  Primary key) ????????????????????(not null)
• ???????(Null value) ??? ??????????????????????
  ??????????? ??????????????????????????????????????
  ??????????????????????? ?
• ?????????????????????? ????????????????????????

36
?????????????????????????????

• CREATE TABLE CUSTOMER
• (Customer_ID Char(5) Not Null,
• Customer_Name Varchar(25),
• Customer_Address Varchar(100),
• Primary Key(Customer_ID))

37
Referential Integrity

• ?????????????????????????(Referential Integrity
  Rule) ???????? ??????????????????????????????????
  ????(Foreign key) ????????????????????????????????
  ?????????????????(Primary key) ???????????????????
  ??????? ???? ???????????????????(Null value) ???

38
Referential Integrity
CUSTOMER
Cumtomer_ID
Cumtomer_Name
Cumtomer_Address
ORDERS
Order_ID
Order_Date
Cumtomer_ID
ORDERLINE
Order_ID
Product_ID
Quantity
PRODUCT
Product_ID
Product_Name
Unit_Price
On_Hand
39
????????????????????????????
CUSTOMER
Cumtomer_Name
Cumtomer_Address
Cumtomer_ID

• CREATE TABLE CUSTOMER
• (Customer_ID Char(5) Not Null,
• Customer_Name Varchar(25),
• Customer_Address Varchar(100),
• Primary Key(Customer_ID))

ORDERS
Order_ID
Order_Date
Cumtomer_ID
CREATE TABLE ORDERS (Order_ID Char(5) Not
Null, Order_Date Date Not Null, Customer_ID
Char(5) Not Null, Primary Key(Order_ID),
Foreign Key(Customer_ID) References
CUSTOMER(Customer_ID))
40
?????????? Referential Integrity

• ????????????????? Primary key ?? Parent relation
  ?????????????????? Foreign key ?? Child relation
  ?????????????????????????????????????????????
• ??????????????????????????????????????????????????
  ???????????? Foreign key ???????
  ????????????????????????????????????????

41
?????????? Referential Integrity

• ??????????????????????????? ????????
• Restrict
• Cascade
• Nullify
• Default

42
Restrict

• ????????????????????????????????? ???? ????????
  Primary key ?? Parent relation ???????????????????
  ??????????????? Foreign key ?? Child relation
• ??? DBMS ???????? NO ACTION

43
???????? Resctrict
Primary key

• ?????????????????????????????? Faculty
  ?????????????????????????? Student
• ??????????????????????????????????? Fac_ID ??
  Faculty ???????????????????????????? Fac_ID ??
  Student

Faculty Faculty Faculty
Fac_ID Fac_Name Phone
Sc Science 201
Msc Management Science 145
En Engineering 512
Ed Education 301
Foreign key
Student Student Student Student Student Student Student
Std_ID Std_Fname Std_Lname Std_Address Std_Major Std_GPA Fac_ID
1234567801 Bob Norbert USA IT 3.00 Sc
1234567821 Wiliam Pilgrim AUS CS 2.50 Sc
1234567822 Luke Brazzi USA PHY 3.50 Sc
1234567840 Homer Wells AUS CS 2.50 Sc
2345678910 Candy Kendall NZ FIN 2.75 Msc
2345678920 Roberto Morales AUS FIN 3.25 Msc
2345789015 Tess Dodge CAN ECON 3.75 Msc
4567890122 Joe Estrada CAN Art 3.25 En
4567890133 Cristopher Colan USA MUS 2.75 En
5678901235 Wally Kendall NZ GSc 3.50 Ed
5678901240 Mariah Dodge CAN Gsc 2.50 Ed
Parent relation
Child relation
44
Cascade

• ????????????????? ?? Parent relation
  ??????????????????? Child relation ??????????????
  ??????????????????????? Child relation
  ?????????????????????????
• ????????????????? Primary key ??? Parent relation
  ????? Foreign key ?? Child relation ??????????
  ???????????????? Foreign key ????????????????
  Primary key ??????

45
???????? Cascade
Primary key

• ????????????? Faculty ?????????????????????
  Student ??????????????? Faculty ??????????????
• ??????????? Fac_ID ?? Faculty ????? Fac_ID ??
  Student ??????????????????????????????????

Faculty Faculty Faculty
Fac_ID Fac_Name Phone
Sc Science 201
Msc Management Science 145
En Engineering 512
Ed Education 301
Foreign key
Student Student Student Student Student Student Student
Std_ID Std_Fname Std_Lname Std_Address Std_Major Std_GPA Fac_ID
1234567801 Bob Norbert USA IT 3.00 Sc
1234567821 Wiliam Pilgrim AUS CS 2.50 Sc
1234567822 Luke Brazzi USA PHY 3.50 Sc
1234567840 Homer Wells AUS CS 2.50 Sc
2345678910 Candy Kendall NZ FIN 2.75 Msc
2345678920 Roberto Morales AUS FIN 3.25 Msc
2345789015 Tess Dodge CAN ECON 3.75 Msc
4567890122 Joe Estrada CAN Art 3.25 En
4567890133 Cristopher Colan USA MUS 2.75 En
5678901235 Wally Kendall NZ GSc 3.50 Ed
5678901240 Mariah Dodge CAN Gsc 2.50 Ed
Parent relation
Child relation
46
Nullify

• ????????????????? ???? ????????????? Primary key
  ??? Parent relation ????? Foreign key ?? Child
  relation ?????????? ???????????????? Foreign key
  ?????????????? Null value

47
???????? Nullify
Primary key

• ????????????? Faculty ???? ???????? Fac_ID ??
  Faculty ????? Fac_ID ?? Student
  ??????????????????????????????? Null value

Faculty Faculty Faculty
Fac_ID Fac_Name Phone
Sc Science 201
Msc Management Science 145
En Engineering 512
Ed Education 301
Foreign key
Student Student Student Student Student Student Student
Std_ID Std_Fname Std_Lname Std_Address Std_Major Std_GPA Fac_ID
1234567801 Bob Norbert USA IT 3.00 Sc
1234567821 Wiliam Pilgrim AUS CS 2.50 Sc
1234567822 Luke Brazzi USA PHY 3.50 Sc
1234567840 Homer Wells AUS CS 2.50 Sc
2345678910 Candy Kendall NZ FIN 2.75 Msc
2345678920 Roberto Morales AUS FIN 3.25 Msc
2345789015 Tess Dodge CAN ECON 3.75 Msc
4567890122 Joe Estrada CAN Art 3.25 En
4567890133 Cristopher Colan USA MUS 2.75 En
5678901235 Wally Kendall NZ GSc 3.50 Ed
5678901240 Mariah Dodge CAN Gsc 2.50 Ed
Parent relation
Child relation
48
Default

• ????????????????? ???? ????????????? Primary key
  ??? Parent relation ????? Foreign key ?? Child
  relation ?????????? ???????????????? Foreign key
  ?????????????????????????(Default)

49
???????? Default
Primary key

• ????????????? Faculty ???? ???????? Fac_ID ??
  Faculty ????? Fac_ID ?? Student
  ??????????????????????????????? Default value

Faculty Faculty Faculty
Fac_ID Fac_Name Phone
Sc Science 201
Msc Management Science 145
En Engineering 512
Ed Education 301
Foreign key
Student Student Student Student Student Student Student
Std_ID Std_Fname Std_Lname Std_Address Std_Major Std_GPA Fac_ID
1234567801 Bob Norbert USA IT 3.00 Sc
1234567821 Wiliam Pilgrim AUS CS 2.50 Sc
1234567822 Luke Brazzi USA PHY 3.50 Sc
1234567840 Homer Wells AUS CS 2.50 Sc
2345678910 Candy Kendall NZ FIN 2.75 Msc
2345678920 Roberto Morales AUS FIN 3.25 Msc
2345789015 Tess Dodge CAN ECON 3.75 Msc
4567890122 Joe Estrada CAN Art 3.25 En
4567890133 Cristopher Colan USA MUS 2.75 En
5678901235 Wally Kendall NZ GSc 3.50 Ed
5678901240 Mariah Dodge CAN Gsc 2.50 Ed
Parent relation
Child relation
50
???????????????????????????????????????????? SQL
Create table Faculty ( Fac_ID Char(5) Not
null, Fac_Name Varchar(50),
Phone Varchar(5), Primary key(Fac_ID) )
Fac_ID Fac_Name Phone
Std_ID Std_Fname Std_Lname Std_Address Std_Major Std_GPA Fac_ID
Create table Student ( Std_ID Char(11) Not
null, Std_Fname Varchar(25),
Std_LName Varchar(35), Std_Address Varchar(100),
Std_Major Varchar(15) Std_GPA decimal,
Fac_ID Char(5), Primary key(Fac_ID)
Foreign key(Fac_ID) References Faculty(Fac_ID)
On Update Cascade On Delete Restict)
51
Relational Algebra

• Restrict ???? Selection
• Project
• Product
• Union
• Intersect
• Difference
• Join
• Divide

52
Restrict ???? Select

• ???????????? Selection ??????????????????????
  ??????????????????????????????????????????????????
  ????????????? Selection ????????????????????
  ??????????????????????????????????????????????????
  ?
• ???????????? ????????? (???????????)

53
Restrict ???? Select

• ???????????? Restrict ?????????? Relation
  ???????????????????????????????????????????????

54
Restrict ???? Select

• ????????????????????????????????????????? 10000
  ??? ??????????????????????????
• ?????????? gt 10000(???????)

??????????? ???? ??????? ??????? ??? ??????? ????????? ????????
SUD21 ????? ???????? ????????? ? 1 ??. 2516 30000 B005
SNK37 ??? ???????? ??????? ? 10 ??. 2519 12000 B007
SNK14 ????? ?????????? ??????????? ? 24 ???. 2517 20000 B007
SUD04 ?????? ????????? ????????? ? 3 ??. 2518 25000 B003
55
Project

• ???????????? Projection ??????????????????????????
  ???? ?????????????????????????????????????????????
  ?????????????????????????? ??????????????????????
  ????????????????????????
• ????????????
• ? ???????????1 ,.., ???????????n (???????????)

56
Project

• ???????????? Project ?????????????????????????????
  ?????????? ?????????????????????????????????

57
Project

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

??????????? ???? ??????? ?????????
SUD21 ????? ???????? 30000
SNK37 ??? ???????? 12000
SNK14 ????? ?????????? 20000
SUB09 ???????? ?????? 9000
SUD04 ?????? ????????? 25000
SNR41 ??????? ????????? 9000
58
Union

• ???????????????????????????????????????
  ?????????????????????????????????????????????
  Union ??? ???????????????????? Union
  ??????????????????????????????????(???????????????
  ????????????????????????????)
• ???????????? ???????????1 ? ???????????2

59
Union

• ?????????????? ??????????????????????????????????
  2 Relation

60
Union

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

???????
????????
???????
???????????
??????????
???????
????????
???????
???????????
??????????
???????
???????????
???????
????????
61
Intersect

• ???????????????????????????????????????
  ?????????????????? 2 ??????? ?????????????????????
  ??? Intersection ???????????????????? Union
  ??????????????????????????????????(???????????????
  ????????????????????????????)
• ???????????? ???????????1 ? ???????????2

62
Intersect

• ?????????????? ??????????????????????????????? 2
  Relation

63
Intersection Operation

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

???????
????????
???????
???????????
??????????
???????
????????
???????
???????????
???????
???????????
???????
????????
64
Difference

• ???????????????????????????????????
  ?????????????????????????? ???????????????????????
  
• ???????????? ???????????1 - ???????????2

65
Difference

• ?????????????? ????????????????????????????????Rel
  ation ???

66
Difference

• ??????????????????????????????????????????????????
  ??????????????????????????????????????
  ????????????????????
• ? ???????(????) - ? ???????(?????????????)

???????
??????????
???????
????????
???????
???????????
??????????
???????
???????????
???????
????????
67
Cartesian Product

• ???????????? Cartesian Product ???????????????????
  ??? 2 ??????? ????????????????????????????????????
  ???????????????????????

R X S
R
S
a b
1 2 3
a a a b b b
1 2 3 1 2 3

68
Cartesian Product

• ?????????????? ?????????? Relation
  ??????????????????????????????????????????????????
  ??????????? 2 Relation ????????

Product
a b c
x y
a a b b c c
x y x y x y
69
??????????? ???????? ???????? (? ?????????? ,
???? , ??????? (??????)) x (? ?????????? ,
???????????????????????????,???????????
(?????????))
(? ?????????? , ???? , ??????? (??????))
(? ?????????? , ???????????????????????????,??????
????? (?????????))
?????????? ???? ???????
CR76 ???? ????????
CR56 ????????? ??????
CR74 ?????? ???????
CR62 ???? ???????
?????????? ????????????????? ???????????
CR56 UD36 ??????????????
CR76 UD16 ?????????
CR56 UD16
CR62 UD36 ????????????????
CR56 UB15
X
70
(? ?????????? , ???? , ??????? (??????)) x (?
?????????? , ???????????????????????????,?????????
?? (?????????))
??????.?????????? ???? ??????? ?????????.?????????? ????????????????? ???????????
CR76 ???? ???????? CR56 UD36 ??????????????
CR76 ???? ???????? CR76 UD16 ?????????
CR76 ???? ???????? CR56 UD16
CR76 ???? ???????? CR62 UD36 ????????????????
CR76 ???? ???????? CR56 UB15
CR56 ????????? ?????? CR56 UD36 ??????????????
CR56 ????????? ?????? CR76 UD16 ?????????
CR56 ????????? ?????? CR56 UD16
CR56 ????????? ?????? CR62 UD36 ????????????????
CR56 ????????? ?????? CR56 UB15
CR74 ?????? ??????? CR56 UD36 ??????????????
CR74 ?????? ??????? CR76 UD16 ?????????
CR74 ?????? ??????? CR56 UD16
CR74 ?????? ??????? CR62 UD36 ????????????????
CR74 ?????? ??????? CR56 UB15
CR62 ???? ??????? CR56 UD36 ??????????????
CR62 ???? ??????? CR76 UD16 ?????????
CR62 ???? ??????? CR56 UD16
CR62 ???? ??????? CR62 UD36 ????????????????
CR62 ???? ??????? CR56 UB15
71
Join

• ????? Product ????????????????????????????????????
  ?????????????????? Attribute ??????? 2 Relation
  ??????????????????? ????????????????? Join
  ????????????????? 2 ??????????????????????????????
  ????

Join
a1 a2 a3
b1 b1 b2
b1 b2 b3
c1 c2 c3
a1 a2 a3
b1 b1 b2
c1 c1 c2
72
Alternate key
Foreign key
Primary key
????????
?????????
???????
???
???????
???????
????
???????????
B005
30000
1 ??. 2516
?
?????????
????????
?????
SUD21
B007
12000
10 ??. 2519
?
???????
????????
???
SNK37
B007
20000
24 ???. 2517
?
???????????
??????????
?????
SNK14
B002
9000
19 ??. 2521
?
???????
??????
????????
SUB09
B003
25000
3 ??. 2518
?
?????????
?????????
??????
SUD04
B004
9000
13 ???. 2520
?
???????
?????????
???????
SNR41
Candidate key
Primary key
73
Divide

• ?????????? Relation ????? Relation ?????
  Attribute ?????????????????? Relation ?????
  Attribute 2 ??? ??????????????????? Attribute
  ??????????? Attribute ?????? 2 ????????????
  Attribute ????????? Relation ??????????

Divide
x y z
a
a a a b c
x y z x y