Transcript PART

Converting ER Diagrams to
Relational Tables
2002.3
참고문헌: “Data Modeling”, G.Lawrence Sanders
1. ER 다이어그램 구성 요소
Attributes
Entities
L_NAME
CREDITS
Relationship
C_NAME
COURSE
CID
ENROLL
STUDENT
SID
F_NAME
2. Semantic Data Model(의미 데이터 모델, semantic network concept, 1968)
Is-part-of
Phone
Street
Amount
Is-part-of
Address
Order Date
Is-part-of
City
Is-part-of
Is-part-of
Name
Salesperson
Is-part-of
Is-part-of
State
Is-part-of
Is-part-of
Is-associated-with
CUSTOMER
ORDER
Isa
Isa
Isa
Spouse Name
Isa
Discount
INSTOCK
Is-part-of
Is-part-of
RETAIL
WHOLESALE
Is-part-of
CUSTOM
Item ID
Is-part-of
Is-part-of
Is-part-of
Is-part-of
Is-part-of
Card Number
Expiration Date
Credit Card Type
Balance
Is-part-of
Is-part-of
Completion Date
Date Started
Color
3. Semantic Model Converted to ER Diagram
Street
City
State
Phone
CUSTOMER
Name
1
Order Date
N
PLACES
Amount
Salesperson
ORDER
ISA
ISA
Discount
RETAIL
WHOLESALE
CUSTOM
INSTOCK
Balance
Spouse
Name
Credit
Card
Type
Expiration
Date
Card
Number
Date
Started
Color
Completion
Date
Item ID
4. 복합속성과 다중속성
Semantic Model
CUSTOMER
Is-part-of
Is-part-of
Address
Is-part-of
Is-part-of
Street
Phone
State
Is-part-of
ER Diagram
CUSTOMER
City
Address
Phone
State
Street
City
5. 복합속성과 다중속성
Simple Attributes for Address and New Entity Phone
(관계형 데이터 모델로 변환을 고려함)
CUSTOMER
CUSTOMER
Address
HAS
Phone
State
Street
City
State
Street
City
PHONE
6. 집단화 (Is-part-of 관계)
COMPUTER
HAS
CONTAINS
CONSISTS-OF
MOTHERBOARD
HARDDRIVE
MONITOR
DIVISION
COMPRISED-OF
HAS
INCLUDES
CONSISTS-OF
ACCOUNTING
HUMAN
RESOURCES
PRODUCTION
MARKETING
7. 일반화/세분화(Is-a 관계)
Name
Address
Sex
Student_id
STUDENT
Age
ISA
Club_name
GRADUATE
UNDERGRADUATE
Passed_Exam
Fraternity
Thesis_Date
Dorm_Room
8. 일반적 관계(Is-associated-with 관계)
An is-associated-with structure
EMPLOYEE
CAR
ASSIGNED
관계이름은 동사나
동사를 복합하여 사용
CUSTOMER
PLACES
ORDER
CUSTOMER
CUST_ORDER
ORDER
CUSTOMER
OBTAINS
LOAN
CUSTOMER
CUST_LOAN
LOAN
9. Cardinality
One-to-One Cardinality
EMPLOYEE
1
ASSIGNED
1
CAR
1:1 Cardinality
Kurt
Brian
Tonya
Fargo
Mustang
Ranger
Scott
Jeep
Nancy
Prizm
9. Cardinality
One-to-Many Cardinality
CUSTOMER
1
RENTS
N
VIDEO
1:N Cardinality
Kyzystoflak
Johnson
Hill
Cerveny
Denton
Gone with the Wind
Terminator
Terminator ll
Cretaceous Park
Sandlot
Son of Flubber
Blade Runner
Bad News Bears
Fride Green Tomatoes
9. Cardinality
One-to-Many and Many-to-Many Cardinality
1
FACULTY
TEACH
N
COURSE
1:N Cardinality
N
ENROLL
M
STUDENT
N:M Cardinality
Colleen
John
MGS 602
Cerveny
Andrew
Jordan
MGS 351
Lotfi
Sean
Jody
MGS 404
Walt
Rieta
MGS 606
Tracey
Doris
Linda
9. Cardinality
One-to-One Recursive Relationship(일대일 재귀적 관계)
1
EMPLOYEE
1
MARRIED-TO
재귀 관계는 개체가 개체 자신과 연결되는 관계를 말함.
Jordan
Mike
Rieta
Colleen
Sean
Jody
Walt
Andrew
Larry
Whitney
Barb
John
EMPLOYEE
MARRIED-TO
EMPLOYEE
Upper bound = maximum cardinality
Lower bound = minimum cardinality
9. Cardinality
1:N 관계의 최소/최대 Cardinality
Upper bound
Lower bound
FACULTY
0:1
Upper bound
Lower bound
TEACH
0:4
COURSE
1:N 관계
N:M 관계의 최소/최대 Cardinality
COURSE
1:7
ENROLL
0:N
N:M 관계
STUDENT
9. Cardinality
1:1 재귀 관계의 최소/최대 Cardinality
Upper bound
Lower bound
0:1
MARRIED-TO
EMPLOYEE
0:1
Lower bound
Upper bound
선택적/의무적 관계(Optional/Mandatory Relationship)
• 선택적 관계(Optional Relationship) - Minimum cardinality가 0인 관계
• 의무적 관계(Mandatory Relationship) - Minimum cardinality가 1인 관계
COURSE
1:7
ENROLL
0:N
강좌에서 학생관계 = 선택적 관계
학생에서 강좌관계 = 의무적 관계
STUDENT
9. Cardinality
다양한 Cardinality 표현방식
A.
COURSE
B.
COURSE
C.
COURSE
D.
COURSE
(0 , N)
1:7
N
ENROLL
ENROLL
ENROLL
ENROLL
(1 , 7)
0:N
7
STUDENT
STUDENT
STUDENT
STUDENT
10. ER 다이어그램을 관계형 테이블로 변환
기본 변환 규칙:
• 1:1 관계의 경우, 모든 속성은 하나의 테이블로 합친다.
• 1:N 관계의 경우, ‘1’에 해당하는 개체의 키속성을 ‘N’에 해당하는 개체에 추가한다.
• N:M 관계의 경우, 두 개체의 키속성들로 이루어진 새로운 테이블을 생성한다.
===> NULL속성 최소화를 보장하지 못함!!!
Optional-Max 변환 원리:
• 기본 변환 규칙의 경우, 최대 대응수만으로 결정함.
• 최소 대응수가 0, 최대 대응수가 1인 개체의 경우, 임시로 ‘Many(N)’으로 취급함.
• 임시 대응수로 변경한 후, 기본변환 규칙에 따라 관계형 테이블로 변환.
===> NULL속성 최소화를 보장!!!
10. ER 다이어그램을 관계형 테이블로 변환
Optional-Max 변환 규칙:
1. ER 다이어그램을 그리고 최대/최소 대응수를 표시한다. 다중값 속성은 개체로 표
시한다.
2. 최소/최대 대응수가 0과 1인 곳은 모두 ‘N’으로 임시 표시한다. 최대 대응수가 1 초
과인 경우는 그대로 둔다.
3. 모든 개체의 이름을 테이블 이름으로 사용한다.
4. 각 테이블의 키 속성을 결정한다.
5. 개체의 모든 속성을 테이블의 컬럼으로 구성한다.
주의:규칙 6,7,8은 최대 대응수에 따른다. 1이상의 최대 대응수는 ‘Many(다, N)’으로 취
급한다
6. 1:1 관계의 경우, 참여하는 개체의 모든 속성을 하나의 테이블로 합쳐 구성한다.
7. 1:N 관계의 경우, ‘1’에 해당하는 개체의 키 속성을 ‘N’에 해당하는 개체의 속성으로
추가한다. 추가된 속성은 외래키(Foreign Key)가 된다.
8. N:M 관계의 경우, 새로운 테이블을 생성하고, 관계명을 테이블 이름으로 사용한다.
새로운 테이블은 교차 테이블(Intersection table)이라 한다. 교차 테이블의 키는 관계에
참여하는 개체들의 키 조합이 된다. 추가된 각 키 속성은 외래키가 된다.
9. 개체나 관계의 추가적인 속성은 같은 이름으로 해당 테이블의 컬럼이 된다. 복합
속성의 경우, 단순 속성으로 분리하여 변환한다.
10. ER 다이어그램을 관계형 테이블로 변환
Optional-Max 변환을 위한 임시 ‘Many(N)’
FACULTY
0:1
N
TEACH
0:4
COURSE
0:7
ENROLL
These are temporary
labels for the upper
cardinality bounds.
0:N
1:1
STUDENT
0:1
N
1:N
HAS
1:1
HOME
ADDRESS
STUD_MAJ
1:1
MAJOR
ASSIGNED
0:1 N
LOCKER
10. ER 다이어그램을 관계형 테이블로 변환
1:1 관계의 변환
L_NAME
F_NAME
STUDENT
1:1
SID
HAS
1:1
H_STREET
HOME
ADDRESS
H_CITY
H_STATE
Attribute posting
STUDENT (SID, F_NAME, L_NAME, H_STREET, H_CITY, H_STATE)
STUDENT 테이블로 합쳐짐.
10. ER 다이어그램을 관계형 테이블로 변환
1:N 관계의 변환
H_CITY
H_STATE
H_STREET
L_NAME
F_NAME
STUDENT
1:N
STUD_MAJ
1:1
MAJOR
DESCRIP
-TION
MAJ_ID
SID
Foreign key posting
STUDENT (SID, MAJ_ID, F_NAME, L_NAME,
H _STREET, H_CITY, H_STATE)
MAJOR (MAJ_ID, DESCRIPTION)
‘1’에 해당하는 테이블의 키속성이 ‘N’에 해당하는 테이블에 추가되어
Foreign Key가 됨.
10. ER 다이어그램을 관계형 테이블로 변환
N:M 관계의 변환
CREDITS
H_STATE
H_CITY
H_STREET
C_NAME
COURSE
1:7
ENROLL
0:N
STUDENT
SID
CID
L_NAME
F_NAME
Foreign key posting
COURSE (CID,C_NAME,CREDITS)
ENROLL (CID, SID)
두 테이블의 키속성으
로 이루어진 신규 테이
블 생성되고, 이 테이블
의 기본키는 CID와 SID
의 조합이다.
STUDENT (SID, MAJ_ID, _NAME,L_NAME,
H _STREET,H_CITY,H_STATE)
10. ER 다이어그램을 관계형 테이블로 변환
선택적(Optional) 관계의 변환
!:N 관계를 임시로 N:M관계로 취급하여 변환.
Lower cardinality
bound of 0
indicates optional
relationship.
Upper cardinality bound of 1 is
temporarily labeled an N for
optional relationships.
CREDITS
L_NAME
F_NAME
FACULTY
N
0:1
TEACH
0:4
FID
COURSE
C_NAME
CID
Foreign key posting
FACULTY (FID,F_NAME,L_NAME)
TEACH (FID,CID) COURSE (CID,C_NAME,CREDITS)
New intersection table
10. ER 다이어그램을 관계형 테이블로 변환
양방향 선택적(Optional) 관계의 변환
H_CITY
H_STATE
H_STREET
L_NAME
F_NAME
STUDENT
SID
N
0:1
ASSIGNED
N
0:1
Foreign key posting
LOCKER
LID
BUILDING
STUDENT (SID,MAJ_ID, F_NAME,L_NAME, ASSIGNED (SID,LID,BUILDING) LOCKER (LID,BUILDING)
H _STREET,H_CITY,H_STATE)
New intersection table
10. ER 다이어그램을 관계형 테이블로 변환
Is-a 관계의 변환
F_NAME
L_NAME
SEX
STUDENT (SID,F_NAME,L_NAME,SEX,AGE)
SID
STUDENT
1:1
Foreign key posting
GRADUATE (SID,PASSED_EXAM,THESIS DATE)
AGE
ISA
N
0:1
UNDERGRADUATE (SID,DORM_ROOM,FRATERNITY)
GRADUATE
PASSED
_EXAM
THESIS
_DATE
N
0:1
Upper cardinality bound is
temporarily labeled an
N for optional relationships.
UNDERGRADUATE
DORM
_ROOM
FRATERNITY
10. ER 다이어그램을 관계형 테이블로 변환
집단 개체의 변환
CID
Indicates foreign key posting
CONTAINS is a new intersection table
COMPUTER (CID, MID, VID, C_DESC)
COMPUTER
0 :N
0 :N
C_DESC
0 :N
CONTAINS (CID, HID)
HAS
CONTAINS
MID
CONSISTS-OF
VID
HID
0:N
1:1
MOTHERBOARD
1:1
HARDDRIVE
M_DESC
MOTHERBOARD (MID, M_DESC)
MONITOR
H_DESC
HARDDRIVE (HID, H_DESC)
G_DESC
MONITOR (VID, G_DESC)
10. ER 다이어그램을 관계형 테이블로 변환
1:1 재귀적 관계의 변환
F_NAME
L_NAME
N
0:1
EMPLOYEE
N
0:1
Upper cardinality
bbound is temporarily
labeled an N for
optional relationships.
MARRIED-TO
SS#
Foreign key posting
EMPLOYEE (SS#, F_NAME, L_NAME)
MARRIED-TO (WIFE_SS#, HUS_SS#)
New intersection table
10. ER 다이어그램을 관계형 테이블로 변환
1:N 재귀적 관계의 변환
F_NAME
L_NAME
1:1
EMPLOYEE
SUPERVISES
0:N
SS#
Foreign key posting
EMPLOYEE (SS#, SUPER_SS#, F_NAME, L_NAME)
10. ER 다이어그램을 관계형 테이블로 변환
N:M 재귀적 관계의 변환
QNTY_ON_
HAND
0:N
PART_DESC
PART
0:M
STRUCTURE
QNTY_
REQUIRED
PART#
Foreign key posting
PART (PART#, PART_DESC, QNTY_ON_HAND)
STRUCTURE (MAJOR_P#, MINOR P#, QNTY_REQUIRED)
New intersection table
10. ER 다이어그램을 관계형 테이블로 변환
삼항 관계의 변환
1. 이항관계로 변환 후, 관계형 테이블로 변환하는 방법.
2. 직접 관계형 테이블로 변환하는 방법.
삼항 이상의 관계는 대부분 N:N:N이므로, 관계를 위한 신규 테이블 생성.
ITEM (IID, DESCRIP)
IID
ITEM
DESCRIP
0:N
ADDRESS
CUSTOMER
0:N
CID
LOCATION
ORDER
Foreign key posting
0:N
WAREHOUSE
WID
ORDER (CID, IID, WID)
New intersection table
CUSTOMER (CID, ADDRESS)
WAREHOUSE (WID, LOCATION)
10. ER 다이어그램을 관계형 테이블로 변환
성능을 위한 고려 사항(1)
관계형 데이터 모델 설계의 상반된 두 가지 목표(Trade-off관계)
1. 설계자 입장 : 데이터 비정상 및 NULL 값의 최소화.
2. DB관리자/사용자 입장 : 생성되는 테이블 수의 최소화.
L_NAME
F_NAME
STUDENT
N:M관계이므로 일반적으로 신규 테이블 STUD_CLUB를 생성함.
1:N
STUD_CLUB
0:3
CID
SID
CLUB
C_DESC
PRESIDENT
Post foreign key three times
STUDENT (SID, F_NAME, L_NAME, CID1,CID2, CID3)
CLUB (CID, C_DESC, PRESIDENT)
NULL 값 발생할 수 있다
10. ER 다이어그램을 관계형 테이블로 변환
성능을 위한 고려 사항(2)
일반적인 경우
STUDENT
0:1
ASSIGNED
STUDENT (SID, MAJOR_ID, F_NAME, L_NAME, ...)
ASSIGNED (SID, LID, BUILDING)
LOCKER (LID, BUILDING)
Case 1.
STUDENT (SID, MAJOR_ID, F_NAME, L_NAME, ...)
LOCKER (LID, BUILDING, SID)
0:1
LOCKER
Case 2.
STUDENT (SID, MAJOR_ID, F_NAME, L_NAME, …, LID, BUILDING)
LOCKER (LID, BUILDING)