Converting ER Diagrams to Relational Tables 2002.3 참고문헌: “Data Modeling”, G.Lawrence Sanders

1. ER 다이어그램 구성 요소 F_NAME Attributes Entities Relationship L_NAME CREDITS L_NAME C_NAME SID CID COURSE STUDENT ENROLL

2. Semantic Data Model(의미 데이터 모델, semantic network concept, 1968) Phone Is-part-of Name CUSTOMER ORDER RETAIL WHOLESALE CUSTOM INSTOCK Address Street City State Order Date Amount Salesperson Is-associated-with Spouse Name Isa Credit Card Type Expiration Date Card Number Balance Discount Date Started Color Completion Date Item ID

3. Semantic Model Converted to ER Diagram CUSTOMER ORDER PLACES Name Phone Street City State Order Date Amount Salesperson Spouse ISA RETAIL WHOLESALE CUSTOM INSTOCK Expiration Date Card Number Credit Type Balance Discount Started Color Completion Item ID 1 N

4. 복합속성과 다중속성 Semantic Model ER Diagram CUSTOMER CUSTOMER Is-part-of Address Is-part-of Phone Street City State ER Diagram CUSTOMER Address Street City State Phone

5. 복합속성과 다중속성 Simple Attributes for Address and New Entity Phone (관계형 데이터 모델로 변환을 고려함) CUSTOMER Address Street City State Phone CUSTOMER Street City State PHONE HAS

6. 집단화 (Is-part-of 관계) COMPUTER MONITOR HARDDRIVE MOTHERBOARD HAS CONTAINS CONSISTS-OF DIVISION MARKETING HUMAN RESOURCES ACCOUNTING HAS INCLUDES CONSISTS-OF COMPRISED-OF PRODUCTION

7. 일반화/세분화(Is-a 관계) STUDENT UNDERGRADUATE GRADUATE ISA Student_id Name Address Sex Age Passed_Exam Thesis_Date Dorm_Room Fraternity Club_name

8. 일반적 관계(Is-associated-with 관계) An is-associated-with structure EMPLOYEE CAR ASSIGNED 관계이름은 동사나 동사를 복합하여 사용 CUSTOMER ORDER PLACES CUST_ORDER LOAN OBTAINS CUST_LOAN

One-to-One Cardinality Kurt Brian Tonya Scott Nancy Fargo Mustang Ranger Jeep Prizm EMPLOYEE CAR ASSIGNED 1:1 Cardinality 1

One-to-Many Cardinality CUSTOMER VIDEO RENTS 1 N 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

One-to-Many and Many-to-Many Cardinality COURSE ENROLL 1 N FACULTY STUDENT TEACH M 1:N Cardinality N:M Cardinality Cerveny Lotfi MGS 602 MGS 351 MGS 404 MGS 606 Colleen John Andrew Jordan Sean Jody Walt Rieta Tracey Doris Linda

One-to-One Recursive Relationship(일대일 재귀적 관계) 9. Cardinality One-to-One Recursive Relationship(일대일 재귀적 관계) 1 MARRIED-TO EMPLOYEE 1 Jordan Mike Rieta Colleen Sean Jody Walt Andrew Larry Whitney Barb John 재귀 관계는 개체가 개체 자신과 연결되는 관계를 말함. EMPLOYEE MARRIED-TO

1:N 관계 N:M 관계 9. Cardinality 1:N 관계의 최소/최대 Cardinality Upper bound = maximum cardinality Lower bound = minimum cardinality Upper bound Upper bound Lower bound Lower bound FACULTY COURSE TEACH 0 : 1 0 : 4 1:N 관계 N:M 관계의 최소/최대 Cardinality COURSE 1 : 7 ENROLL 0 : N STUDENT N:M 관계

1:1 재귀 관계의 최소/최대 Cardinality 0 : 1 Upper bound Lower bound EMPLOYEE MARRIED-TO 선택적/의무적 관계(Optional/Mandatory Relationship) 선택적 관계(Optional Relationship) - Minimum cardinality가 0인 관계 의무적 관계(Mandatory Relationship) - Minimum cardinality가 1인 관계 COURSE 1 : 7 ENROLL 0 : N STUDENT 강좌에서 학생관계 = 선택적 관계 학생에서 강좌관계 = 의무적 관계

A. B. C. D. 9. Cardinality 다양한 Cardinality 표현방식 COURSE (0 , N) ENROLL (1 , 7) STUDENT A. COURSE 1 : 7 ENROLL 0 : N STUDENT B. COURSE N ENROLL 7 STUDENT C. COURSE ENROLL STUDENT D.

10. ER 다이어그램을 관계형 테이블로 변환 기본 변환 규칙: 1:1 관계의 경우, 모든 속성은 하나의 테이블로 합친다. 1:N 관계의 경우, ‘1’에 해당하는 개체의 키속성을 ‘N’에 해당하는 개체에 추가한다. N:M 관계의 경우, 두 개체의 키속성들로 이루어진 새로운 테이블을 생성한다. ===> NULL속성 최소화를 보장하지 못함!!! Optional-Max 변환 원리: 기본 변환 규칙의 경우, 최대 대응수만으로 결정함. 최소 대응수가 0, 최대 대응수가 1인 개체의 경우, 임시로 ‘Many(N)’으로 취급함. 임시 대응수로 변경한 후, 기본변환 규칙에 따라 관계형 테이블로 변환. ===> NULL속성 최소화를 보장!!!

10. ER 다이어그램을 관계형 테이블로 변환 Optional-Max 변환 규칙: 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 These are temporary labels for the upper cardinality bounds. FACULTY COURSE TEACH 0 : 1 0 : 4 N STUDENT LOCKER MAJOR HOME ADDRESS ENROLL ASSIGNED STUD_MAJ HAS 0 : 7 0 : N 1 : N 1 : 1

10. ER 다이어그램을 관계형 테이블로 변환 1:1 관계의 변환 Attribute posting L_NAME F_NAME STUDENT 1 : 1 HAS 1 : 1 HOME ADDRESS SID H_STREET H_CITY H_STATE Attribute posting STUDENT (SID, F_NAME, L_NAME, H_STREET, H_CITY, H_STATE) STUDENT 테이블로 합쳐짐.

10. ER 다이어그램을 관계형 테이블로 변환 1:N 관계의 변환 Foreign key posting H_CITY H_STATE H_STREET DESCRIP -TION L_NAME STUDENT 1 : N STUD_MAJ 1 : 1 MAJOR F_NAME SID MAJ_ID 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 관계의 변환 Foreign key posting CREDITS H_STATE H_CITY H_STREET C_NAME COURSE L_NAME 1 : 7 ENROLL 0 : N STUDENT CID F_NAME SID Foreign key posting COURSE (CID,C_NAME,CREDITS) ENROLL (CID, SID) STUDENT (SID, MAJ_ID, _NAME,L_NAME, H _STREET,H_CITY,H_STATE) 두 테이블의 키속성으로 이루어진 신규 테이블 생성되고, 이 테이블의 기본키는 CID와 SID의 조합이다.

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. L_NAME CREDITS N F_NAME FACULTY 0 : 1 TEACH 0 : 4 COURSE C_NAME FID CID Foreign key posting FACULTY (FID,F_NAME,L_NAME) TEACH (FID,CID) COURSE (CID,C_NAME,CREDITS) New intersection table

양방향 선택적(Optional) 관계의 변환 10. ER 다이어그램을 관계형 테이블로 변환 양방향 선택적(Optional) 관계의 변환 H_CITY H_STATE H_STREET N N L_NAME STUDENT 0 : 1 ASSIGNED 0 : 1 LOCKER LID BUILDING F_NAME SID Foreign key posting 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 관계의 변환 L_NAME F_NAME SEX STUDENT (SID,F_NAME,L_NAME,SEX,AGE) SID STUDENT AGE 1 : 1 Foreign key posting Upper cardinality bound is temporarily labeled an N for optional relationships. ISA GRADUATE (SID,PASSED_EXAM,THESIS DATE) N N 0 : 1 0 : 1 GRADUATE UNDER- GRADUATE UNDERGRADUATE (SID,DORM_ROOM,FRATERNITY) PASSED _EXAM THESIS _DATE DORM _ROOM FRATERNITY

10. ER 다이어그램을 관계형 테이블로 변환 집단 개체의 변환 COMPUTER (CID, MID, VID, C_DESC) 집단 개체의 변환 CID Indicates foreign key posting CONTAINS is a new intersection table COMPUTER (CID, MID, VID, C_DESC) COMPUTER C_DESC 0 :N 0 :N 0 :N CONTAINS (CID, HID) HAS CONTAINS CONSISTS-OF HID VID MID 1 : 1 0 : N 1 : 1 MOTHER- BOARD HARDDRIVE MONITOR M_DESC H_DESC G_DESC MOTHERBOARD (MID, M_DESC) HARDDRIVE (HID, H_DESC) MONITOR (VID, G_DESC)

10. ER 다이어그램을 관계형 테이블로 변환 1:1 재귀적 관계의 변환 Foreign key posting 1:1 재귀적 관계의 변환 Upper cardinality bbound is temporarily labeled an N for optional relationships. F_NAME L_NAME N 0 : 1 MARRIED-TO EMPLOYEE N 0 : 1 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 SUPERVISES EMPLOYEE 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 STRUCTURE QNTY_ REQUIRED PART 0 : M PART# Foreign key posting PART (PART#, PART_DESC, QNTY_ON_HAND) STRUCTURE (MAJOR_P#, MINOR P#, QNTY_REQUIRED) New intersection table

10. ER 다이어그램을 관계형 테이블로 변환 삼항 관계의 변환 1. 이항관계로 변환 후, 관계형 테이블로 변환하는 방법. 삼항 관계의 변환 1. 이항관계로 변환 후, 관계형 테이블로 변환하는 방법. 2. 직접 관계형 테이블로 변환하는 방법. 삼항 이상의 관계는 대부분 N:N:N이므로, 관계를 위한 신규 테이블 생성. CUSTOMER ITEM WAREHOUSE ORDER WID LOCATION CID ADDRESS IID DESCRIP Foreign key posting ITEM (IID, DESCRIP) 0 : N ORDER (CID, IID, WID) New intersection table CUSTOMER (CID, ADDRESS) WAREHOUSE (WID, LOCATION)

10. ER 다이어그램을 관계형 테이블로 변환 성능을 위한 고려 사항(1) 관계형 데이터 모델 설계의 상반된 두 가지 목표(Trade-off관계) 1. 설계자 입장 : 데이터 비정상 및 NULL 값의 최소화. 2. DB관리자/사용자 입장 : 생성되는 테이블 수의 최소화. STUDENT SID F_NAME L_NAME N:M관계이므로 일반적으로 신규 테이블 STUD_CLUB를 생성함. STUD_CLUB 1 : N 0 : 3 CLUB CID PRESIDENT C_DESC Post foreign key three times STUDENT (SID, F_NAME, L_NAME, CID1,CID2, CID3) CLUB (CID, C_DESC, PRESIDENT) NULL 값 발생할 수 있다

10. ER 다이어그램을 관계형 테이블로 변환 성능을 위한 고려 사항(2) 일반적인 경우 Case 1. Case 2. STUDENT LOCKER ASSIGNED 0 : 1 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) Case 2. STUDENT (SID, MAJOR_ID, F_NAME, L_NAME, …, LID, BUILDING) LOCKER (LID, BUILDING)