[DB06] ER schema를 relational schema로 바꾸기

* 10.13 업데이트

 

해당 게시물은 한양대학교 컴퓨터소프트웨어학부 김상욱 교수님 데이터베이스시스템 온라인 강의를 듣고 정리한 자료입니다.

오류가 있다면 언제든 알려주세요!

 

● 해당 강의의 목표

1. Relational database design에 대해 공부한다. (Logical data model)

· ER model to relational model mapping

· ER Schema -> relational Schema

 

◆ Relational Database Design by ER-to-Relational Mapping

· relational database schema를 design한다.

 - conceptual design(conceptual schema, ER schema)를 토대로 한다.

· ER model을 relational model으로 바꾸어야 한다.

 

◆ Mapping of Regular Entity Types

entity type은 regular, weak으로 나눌 수 있다.

해당 부분에서는 먼저 regular entity type에 대해서 다루도록 하겠다.

 

· regular entity type을 하나의 relation으로 만들어준다. (mapping 해준다.)

  이렇게 만들어준 relation은 entity relation이라고 부른다. 

  relation type이 relation으로 바뀌는 경우도 있는데 그것을 relationship relation이라고 부른다.

 

· Attributes

 - entity type의 모든 simple한 속성들을 relation의 속성으로 붙여주면 된다.

    (multivalued, composite 속성과 같은 경우는 다른 방법을 택한다.)

 

· Primary key

 - entity type의 key attribute 중 하나를 선택해서 primary key로 만든다.

 

◆ Mapping of Weak Entity Types

· weak entity type을 하나의 relation으로 만들어준다. (mapping 해준다.)

· Attributes

 - entity type의 모든 simple한 속성들을 relation의 속성으로 붙여주면 된다.

 - weak entity type은 혼자서 key를 형성할 수 없기 때문에 owner entity type이 따로 있다. 그 owner의 primary key attribute를 foreign key로 가져온다.

ex) dependent의 owner는 empolyee이다. employee의 primary key를 dependent에 해당되는 relation의 foreign key로 가져온다.

 

· Primary key

 - weak entity type의 partial key 중 하나를 고르고 그것과 foreign key를 합쳐서 primary key로 만든다.

partial key가 뭔지 깜빡했다.>!

같은 owner을 가지는 weak entity type을 구분하게 해주는 key attribute들을 말한다.

 

◆ Mapping of Multivalued Attributes

· 두 개 이상의 value를 가지는 multivalued attribute를 하나의 relation으로 만들어준다. (mapping 해준다.)

  relational model에서는 attribute이 두 개 이상의 value를 가지는 것을 허용하지 않는다. 

 

· Attributes

 - 하나로 만들어준 multivalued attribute를 속성으로 만들어준다.

 - multivalued attribute를 가지고 있던 entity type에 해당되는 relation의 key attribute을 새로운 relation의 attribute로 가져와서 FK로 정한다.

 

· Primary key

 - 모든 속성들을 합해서 만든다.

    Multivalued attributes + FK

 

갑자기 또 헷갈리는 개념이 있어서 찾아보았다.

primary key: 실제 테이블에서 각 레코드를 고유하게 식별하는 열

key attribute: ER 다이어그램에서 엔터티를 식별하는 데 사용될 수 있는 속성

 

용어들이 너무 많이 나와 순간 헷갈리는 개념들이 많다. .. 

 

◆ Mapping of Composite Attributes

· composite attribute(여러 개의 subattribute으로 구성된 attribute)을 새로운 relation으로 만든다.(Mapping 해준다.)

 

· Attributes

- component attribute의 simple attributes를 속성으로 한다.

- composite attribute이 소속되어 있던 entity type에 해당되는 relation의 key attribute을 새로운 relation의 attribute로 가져와서 FK로 정한다. 이 composite attribute이 어떤 relation의 것이라는 일종의 pointer역할을 하는 것이다.

ex) 이름 attribute를 가지는 employee가 하나의 relation으로 mapping이 되었고 그 relation의 key attribute을 가져와서 FK로 만든다. 나는 저쪽 relation의 어떤 tuple에 해당되는 것이야. 

 

· Primary key

- Foreign key

 

· 다른 방식

- composite attribute이 어떤 entity type에 속해있었을 텐데 그 entity type은 하나의 relation이 되었을 것이다. 그리고 component attribute를 그 relation의 attribute으로 만들 수 있다. 원래의 composite attribute는 없어지는 것이다.

 

◆ Mapping of Binary 1:1 Relationship Types

· relationship type에 참여하는 entity type들을 찾고 그것과 대응되는 relation들을 찾는다.

 

· 두 개의 relation들 중 하나를 택해 그 relation에다가 다른 한 쪽의 relation의 primary key를 가져와 FK로 넣는다.

 - ex) manages. employee & department : employee's relaion의 FK가 department's relation의 PK인 DNO가 된다.

 - 둘 중 하나를 선택해서 가져올 수 있는데 total participation하는 쪽에 FK를 두는 것이 좋다. 모든 department가 manager를 가지기 때문에 department의 FK의 값에 NULL값이 생기지 않는다. 반면 Dnumber를 employee에 가져오면 대부분의 employee는 manager가 아니기 때문에 NULL이 많아진다. 따라서 공간을 낭비하게 된다.

 

· relationship type의 simple attribute을 FK가 생긴 relation의 속성으로 넣는다.

 

◆ Mapping of Binary 1:N Relationship Types

· relationship type에 참여하는 entity type들 중 N쪽 side와 대응되는 relation을 찾는다.

  ex) employee & department: 하나의 department에 여러 명의 employee가 일한다. 따라서 employee가 N이고 department가 1이다. employee에 대응되는 relation를 찾는다.

 

· N쪽 side에 해당되는 entity type의 FK에 1에 해당되는 entity type의 PK를 가져온다. 

  ex) DNO를 employee의 FK로 가져온다. 어떤 employee가 몇번 department에서 일하는지 알 수 있다.

 

· 원래 relationship type에 해당하는 attribute들은 FK쪽(employee 쪽)으로 가져온다.

 

Q) 반대로 하면 안 되나요?

A) Not recommanded. 1 side에 해당되는 entity type의 FK에 N에 해당되는 entity type의 PK를 가져온다. 

이는 department쪽에 employee의 primary key를 가져오려고 하는 상황 즉, SSN을 department의 FK로 가져오는 상황이다. 그러면 하나의 department에 일하는 사람이 2명 이상이면 employee 115, 117 이렇게 set로 넣어야되는데 relational model에서는 simple attribute만 허용한다.

그러면 일하는 사람의 attribute를 여러개 하면 안 돼? 되지만 너무 많아질 수 있다. 

 

◆ Mapping of Binary M:N Relationship Types

ex) Works_On 하나의 employee도 여러 project에서 일할 수 있고 하나의 Project도 여러 employee가 할 수 있다. 

· M:N relationship type과 대응되는 하나의 independent relation을 만든다. 별도로!

 

· Attributes

 - 참여한 entity types들의 PK를 모두 FK로 가져온다.

    ex) employee 105가 3, 4번 프로젝트에서 일했으면 (105, 3), (105, 4) 이렇게 tuple로 만들어진다. 

- 원래 relationship type에 속했던 simple attribute를 가져온다.

 

· Primary key

 - 양쪽 entity type에서 가져온 foreign key의 집합이 PK가 된다.

 

◆ Mapping of N-ary Relationship Types

이는 M:N relationship type과 같은 방법으로 한다.

· N-ary relationship type을 독립적인 relation으로 만든다.

 

· Attributes

 - 참여한 entity types들의 PK를 모두 FK로 가져온다.

 - 원래 relationship type에 속했던 simple attribute를 가져온다.

 

· Primary key

 - 모든 entity type에서 가져온 foreign key의 집합이 PK가 된다.

 

 

◆ Company Database Example

 

① Mapping of Regular entity types

1) Employee

- Employee relation을 만든다.

- Simple Attribute을 포함한다. (SSn, Bdate, Address, Sex, Salary)

- SSn을 PK로 잡는다.

 

2) Department

- Department relation을 만든다.

- Simple Attribute을 포함한다. (Name as Dname, Number as Dnumber)

- Dnumber을 PK로 만든다. Department entity type's key attribute가 name, number 두 가지가 있었는데 그 중 number를 primary key로 잡았다. 보통 숫자로 primary key를 선택하고 싶어하고 가변길이 String을 잘 선택하지 않기 때문이다.

 

3) Project

- Project relation을 만든다.

- Simple Attribute을 포함한다. (Name as Pname, Number as Pnumber, Location as Plocation) 이름을 살짝씩 바꾸는 이유는 다른 relation들과 attribute name이 같으면 SQL문을 만들 때 번거롭기 때문이다. ambiguous가 생긴다.

- Pnumber을 PK로 잡는다.

 

② Mapping of Weak entity types

weak entity type: primary key를 자신이 가지고 있는 attribute만으로 해결할 수 없다. owner와 연결해서 key를 형성한다.

 

1) Dependent

- Dependent relation을 만든다.

- Dependent entity type이 원래가지고 있었던 Simple Attribute을 포함한다. (Name as Dependent_name, Sex, Birth_date as Bdate, Relationship)

- owner entity type의 PK를 FK로 가져온다. weak entity type이기 때문에 혼자만으로는 key를 생성할 수 없기 때문이다. (Ssn as Essn)

- Essn + Dependent_name(weak entity type의 partial key)를 PK로 한다.

 

③ Mapping of multivalued attributes

하나의 속성에 여러 개의 값이 들어갈 수 있는 형태이다. 이를 특별하게 취급해야 하는 이유는 relation model에서는 속성이 단 하나의 값만 가질 수 있기 때문이다.

 

1) Locations in DEPARTMENT

- DEPT_LOCATIONS relation을 만든다.

- multivalued attribute를 갖는다. (Locations as Dlocation) 자기 자신을 갖는다.

- DEPARTMENT relation의 PK를 FK로 갖는다. (Dnumber)

- Dnumber + Dlocation을 PK로 한다.

 

ex) 3번 department의 location이 서울, 대전, 부산이라고 하자. 그러면 새로운 DEPT_LOCATIONS relation은 attribute로 Dlocation, Dnumber를 가지게 되고 attribute value로 (서울, 3), (대전, 3), (부산, 3)을 가지게 된다. 3개의 서로 다른 tuple로 표현이 되는 것이다. 

 

④ Mapping of composite attributes

하나의 attribute이 여러 개의 component attribute이 달라 붙은 형태이다.

ex) Employee의 Name에는 Fname, Minit, Lname이 있다.

 

1) EMPLOYEE

- composite attribute인 Name의 component attribute를 갖는다. (Fname, Minit, Lname) 즉, Name은 사라지고 Employee relation에 component attribute 3개가 attribute로 들어간다.

 

⑤ Mapping of binary 1:1 relationship types

1) MANAGES

pariticipation entity type은 employee, department이고 어떤 employee가 어떤 department를 manage하고 있었다. 모든 department는 관리자에게 관리를 받아야하지만 일부의 employee만 manage한다. 1:1관계이면 상대방의 PK를 나의 FK로 가져오면 되지만 기왕이면 total participation에 다른 partial participation의 PK를 FK로 가져오는 것이 좋다. employee의 PK를 department의 FK로 가져온다.

- DEPARTMENT은 total participation이다. 

- EMPLOYEE의 PK를 DEPARTMENT의 FK로 가져온다. (Ssn as Mgr_ssn)

- MANAGES의 simple attribute를 FK가 있는 쪽으로 가져온다. (Start_date as Mgr_start_date)

 

⑥ Mapping of binary 1:N relationship types

1) WORKS_FOR

Employee와 Department가 WORKS_FOR의 participating entity type이다. 하나의 employee는 하나의 department에서만 일할 수 있고 하나의 department는 여러 명의 employee가 일할 수 있다.

- employee가 N side entity이다. 

- Employee의 FK로 DEPARTMENT의 PK를 가져온다. (Dnumber as Dno)

 

2) CONTROLS

department와 project의 관계이다. 하나의 Department가 여러 개의 project를 control할 수 있다. 반면에 하나의 project는 단 하나의 department에 control 받을 수 있다. 따라서 project가 N side, department 1 side이다.

- PROJECT가 N-side entity type이다.

- project의 FK로 department의 PK를 가져온다. (Dnumber as Dnum)

 

3) SUPERVISION

supervision은 recursive relationship type이다. 하나의 employee가 다른 employee의 상급자 혹은 하급자이다.

- relationship에 참여하는 supervisor와 supervisee가 모두 EMPLOYEE이다.

- Employee의 PK를 employee의 FK로 가져온다. (Ssn as Super_ssn)

 

⑦ Mapping of binary M:N relationship types

employee와 project 간의 관계이다. 하나의 employee는 여러 개의 project에 참여할 수 있고 한 project에 여러 명이 참여할 수 있다. 이런 경우 FK를 가져오면 하나의 attribute안에 여러 개의 value들을 넣어야한다. 하지만 이는 허용되지 않는다. 따라서 FK만으로는 이러한 relationship type을 mapping할 수 없다.

 

1) WORKS_ON

- WORKS_ON relation을 만든다.

- EMPLOYEE와 PROJECT의 PK를 가져온다. (Ssn as Essn, Pnumber as Pno)

- WORKS_ON의 simple attribute를 가져온다. (Hours)

- Essn + Pno, 양쪽의 PK를 합쳐서 PK를 만든다.

 

ER Schema -> Relation Schema

 

Summary

1. Map conceptual schema design from the ER schema to relational database schema

   Algorithm for ER-to-relational mapping

   Illustrated by example form the COMPANY database