???????????????????? ER ?????????????? ER-to-Relational Mapping Algorithm

1
???????????????????? ER ??????????????
ER-to-Relational Mapping Algorithm
2
??????????????????? E-R Diagram

• 1. ?????????????????????????
• 2. ???????????????????????????????
• 3. ???????????????????????????????
• 4. ???????????????????????????????????????
• 5. ?????????????

3

• ??????? E-R Model ???? Table

4
Step 1 Entity
Students

• ?????????????? Entity
• ???? Simple Attribute
• - ???????????????? Entity
• - ???????????? ??????? ??????? Entity
• - ?????????????? ?????????????? Attribute ???
  Entity
• - Primary Key ??? ????? ??????? Primary Key ???
  Entity

S_ID
BirthYear
Last_name
First_name
Students Students
S_ID First_name Last_name BirthYear
5
Step 1 Entity
city
street

• ?????????????? Entity
• ???? Composite Attributes
• - ????????????????????? simple
  ???????????????????????????? Composite ??????????

state
Customer
Zip Code
cus_ID
Address
Last_name
First_name
Customer Customer Customer
cus_ID First_name Last_name street city State zipcode
6
Step 1 Entity

• ?????????????? Entity
• ???? Multivalued Attribute
• - ?????????????????????????????????
• - ???????????????????????????????????????????
  Entity ??????? ???????????????????????????
  Multivalued
• - ?????????????????????? 2 ??????????? -
  ????????????????? ????????????????????????????
• - ?????????????? ??? ??????????????????
  Multivalued

Students
S_ID
Tel
Tel
Last_name
First_name
Students Students
S_ID First_name Last_name
Students_Tel Students_Tel
S_ID Tel
7
Multivalued Attributes
Instructor
- Relational Model ??????????????? 1
????????????? attribute - Multivalued attribute
?? ER Model ???????????????????????
???????????????????????
ID
Tel
Degrees
Name
Instructor Instructor
ID Name
Instructor_degree Instructor_degree
ID Degree
8
Step 2 Weak Entity

• ?????????????? Weak Entity
• - ???????????????????? Weak Entity ?? Primary Key
  ??? Master Entity ?????? Primary Key ???????
  Primary Key ??????? Weak Entity
• Attribute ???????????? Foreign Key ??????????????
  ???????? Master Entity

9
Step 2 Weak Entity
Last Name
First Name
emp_firstName
emp_LastName
birthDate
dependentname
emp_ID
gender
M
1
claims
EMPLOYEE
DEPENDENT
EMPLOYEE EMPLOYEE
emp_ID emp_firstName emp_LastName
DEPENDENT DEPENDENT
firstName lastname emp_ID birthDate gender
10
Step 2 Weak Entity
Building_ID
Room_number


1
M
has
Room
Building
Building Building
Building_ID
Room Room
Building_ID Room_number
11
Step 3 One-to-Many Relationship

• ??????????????????? One-to-Many
• - ?? Primary Key ???? One ??????????????????
  ?????? Many
• - ??????? ?????????? Foreign Key ??????? Primary
  Key ?????????????????????????(???? One)
• - ??????? Many-to-One ????????????????????

12
Step 4 Many -to-Many Relationship

• ??????????????????? Many-to-Many
• - ??????????????????????????????? 1
  ?????????????? Primary Key ??????? 2 ?????
• - ????????????????????? 2 ????????????? Foreign
  Key ?????????????????????
• - ??? Entity ???? ???????????????? 2 Entity ????
  Primary Key ????????????????????

13

Step 4 Many -to-Many Relationship

C_ID

S_ID
N
M
takes
STUDENT
COURSE

C_ID

S_ID
M 1
1 M
takes
STUDENT
COURSE
STUDENT_COURSE STUDENT_COURSE
S_ID C_ID
STUDENT STUDENT
S_ID
COURSE COURSE
C_ID
14
Step 4 Many -to-Many Relationship

• ??????????????????????????????????
  ???????????????? ?????????????????????????????????
  ?????????? 2 ????

C_ID

Registration_date
S_ID
N
M
takes
STUDENT
COURSE

C_ID

S_ID
M 1
1 M
takes
STUDENT
COURSE
Registration_date
STUDENT_COURSE STUDENT_COURSE STUDENT_COURSE
S_ID C_ID Registration_date
STUDENT STUDENT
S_ID
COURSE COURSE
C_ID
15
?????????

• ?????????
• ???????????????????????? ??? ?????????????????????
  ?
• ???? ID ??????????? Primary Key ???????? 2 ?????
  1 ?? 2 ???????????????????????????????????????????
  ?????????
• ??????????????? ???????????????????? Entity ????
  ?? ???? Attribute ???? ???? Student_id,
  Instructor_id

16
?????????
17
Step 6 ?????????????????????? Unary

• ??????????????? Unary ????????????????????????????
  ????????? ???????????? ?????????????????????????
• ????????????
• ??????????????? Unary ????????????????? (1M)
• ??????????????? Unary ????????????????? (MN)

18
Step 6 ?????????????????????? Unary

• ??????????????? Unary ????????????????? (1M)

emp_ID
emp_Name
birthDate
M
EMPLOYEE
1
manages
EMPLOYEE EMPLOYEE
emp_ID emp_Name birthDate managerID
19
Step 6 ?????????????????????? Unary

• ??????????????? Unary ????????????????? (MN)

item_Name

• ??????????????????? 2 ???????????????
• ??????????????????????????????????????????????????
  ?????
• ????????????????????????? 2 ??????????????????????
  ??????? ??????????????????? 2 ????????????????????
  ??????????????????????????? ??????????????????????
  ?????

item_ID
unitCost
N
ITEM
M
quantity
contains
ITEM ITEM
item_ID item_Name unitCost
ITEM_Component ITEM_Component
item_ID Component_NO quantity
20
Step 7 ?????????????????????? Ternary (and
n-ary)

• ??????????????? Ternary ??????????????????????????
  ?????? 3 ??????????

physician _Name
patient_Name
physician_ID
patient_ID
M
M
Patient_ Treatment
PHYSICAIN
PATIENT
time
results
date
M
TREATMENT
description
treatmentCode
21
Step 7 ?????????????????????? Ternary (and
n-ary)

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

physician _Name
patient_Name
physician_ID
patient_ID
1 M
M 1
Patient_ Treatment
PHYSICAIN
PATIENT
M 1
time
results
date
TREATMENT
description
treatmentCode
22
Step 7 ?????????????????????? Ternary (and
n-ary)

• ??????????????????? 4 ???????????????
• ????????????????????????????????????? 3
  ????????????????????
• ??????????????????????????? 3?????????????????????
  ?? ??????????????????????????

• ??????? 2 ?????????

PATIENT PATIENT
patient_ID patient_Name
PHYSICIAN PHYSICIAN
physician_ID physicianName
PATIENT_TREATMENT PATIENT_TREATMENT PATIENT_TREATMENT PATIENT_TREATMENT
patient_ID physician_ID treatmentCode date time results
TREATMENT TREATMENT
treatmentCode description
23
Step 8 ?????????????????????? Supertype ???
Subtype

• ??????????????????????????????????????????????????
  ???????????? Supertype/Subtype ???????????????
  ??????????????????????????????????????????????????
  ??????????????
• ????????????????????????????? ????????????????????
  ????????????
• ??????????????????????????????????????????????????
  ?????????????????? ???????????????????????????????
  ?????
• ??????????????????????????????????????????????
  ???????????????????????????????????
  ??????????????????????????????????????
• ????? Subtype Discriminator ?????? Supertype

24
Step 8 ?????????????????????? Supertype ???
Subtype
name
address
empNo
dataHired
EMPLOYEE
d
CONSULTANT
SALARIED_EMP
HOURLY_EMP
hourlyRate
salary
contractNo
bonus
BillingRate
25
Step 8 ?????????????????????? Supertype ???
Subtype
EMPLOYEE EMPLOYEE EMPLOYEE EMPLOYEE
empNo name address dataHired
SALARIED_EMP SALARIED_EMP
sempNo salary bonus
HOURLY_EMP HOURLY_EMP
hempNo hourlyRate
CONSULTANT CONSULTANT
cempNo contractNo BillingRate
26
?????????

• 1. ???????? ER Diagram ???????????????????????????
  ??? ??????????
• ??????????(member) ????????????? ?????????? ,
  ???? , ???????,????????
• ??????????????(title) ????????????? ??????????,
  ?????????? , ???????? , ??????? (Mutivalue
  attribute)
• ??????????(category) ?????????????
  ??????????,??????????
• ???????????? ER Diagram ???? Table
  ???????????????? Table
• ??????? 3 ????????

27
2. ?????? ER Diagram ????????????????????
1
N
M
M
M
1
???????????????? Table ??????? 3 ????????
28
3. ?????? ER Diagram ????????????????????
???????????????
???
??????????
???????
????????
????????????
????
1
1
N
M
??
?????????
????????
???????????????
????
M
??????
????????
?????????
???????
??
????????
???????????????? Table ??????? 3 ????????
1
????????????????
???????????
???????????
???????
29
4. ?????? ER Diagram ????????????????????
M
N
1
M
N
M
???????????????? Table ??????? 3 ????????