3장, The Enhanced ER Model에 이어 이번 포스팅에서는 Logical Database Design에 대해서 알아본다.
Logical Database Design의 단계는 그림으로 알아보자면 다음과 같다.
앞선 포스팅에서 알아보았던 2장, 3장의 주제는 Conceptual Data Modeling의 단계이다.
목차는 다음과 같다.
목차
1. Logical Database Design
2. Relational Data Model
3. Transforming from ER model to Relational model
Logical Database Design
Conceptual data Modeling에서는 ER Model을 이용하여 사용자 요구사항들을 표시했다. 이 과정에서는 어떻게 정확한 User Requirements들을 ER Model에 담아낼것이냐가 주된 목적이였다.
이번 Logical Database Design에서는? 데이터베이스 기술 유형에서 데이터를 구성하는 방법에 대해 다룬다.
Logical data Modeling의 도구의 지배적인 툴로는 Relational data Model이 있다.
Relational Data Model
Relational data model의 구성요소에는 다음과 같은 것들이 있다.
Data Structure : 데이터는 Relations(열과 행으로 이루어진 Table을 뜻함)들로 구성된다.
Data Integrity: 데이터 저장 및 조작 시 데이터 정확성과 일관성, 비즈니스 규칙 준수를 보장하는 데 사용할 수 있는 규칙 및 메커니즘을 지원한다.
Data Manipulation: 서로 다른 테이블에 저장된 데이터를 검색하고 조작할 수 있도록 하는 작업들을 의미한다.
정리하자면 다음과 같다.
Data Structure란 어떻게 데이터를 조직하느냐, Data Integrity란 어떻게 비즈니스 규칙에 따라 무결성을 유지하는지, Data Manupulation이란 어떻게 데이터들을 조작하는지에 대한 것이다.
Data Structure는 Relation으로 구성되는데 Relation은 명명된 컬럼들의 집합, 명명되지 않은 행들의 집합으로 구성된다. 각 컬럼들은 테이블집합의 속성이나 관계집합의 특성을 나타낸다. 또한, 각각 행들은 테이블집합이나 관계집합의 인스턴스를 뜻한다. 관계 데이터 모델의 측면에서, 데이터베이스는 상호 관련 관계의 모음으로 구성된다.
다음과 같은 테이블은 Employee1 ( Emp_ID, Name, Dept_Name, Salary)과 같이 표기한다.Relation의 공식 용어는 다음과 같다.
그렇다면, 예시를 통해 Data Structrue에 대해 자세히 알아본다.
다음의 두 테이블은 같은 테이블인가?
다음의 두 테이블은 같은 테이블이다. 행이 삭제될때마다 다른 Relation은 아니다.
뿐만아니라, 다음과 같은 컬럼의 순서가 바뀌었다고 해도 다른 Relation은 아니다.
다음으로, Relation(Table)의 특성에 대해 알아본다.
- Relation은 데이터베이스에서 Unique한 이름을 가져야 한다.
- 모든 속성들의 값은 Atomic해야 한다(Multivalued, Compostite이 아니여야 한다.)
- 모든 컬럼들은 반드시 고유해야 한다.(모든 필드에 대해 정확히 동일한 값을 가진 두 행을 가질 수 없다는 뜻이다.) 행이 같다는건 프라이머리키의 값도 같다는 이야기이기 때문이다.
- 컬럼들은 반드시 Relation에서 Unique한 이름을 가져야 한다.
- 컬럼의 순서가 중요하지 않다.
- 행의 순서가 중요하지 않다.
키는 두가지 목적을 가지는데, Primary key, Foreign key이다.
그렇다면 여기서 Primary key, Foreign key란 무엇일까?
Primary key : Primary key는 해당 테이블의 고유한 식별자이다. 예를 들어 사원 번호나, 사회 보장 번호와 같은 것들이 있다.
Foreign key : Foreign key는 종속 테이블이 상위 테이블을 참조할 수 있도록 하는 식별자이다.
key는 Simple컬럼일 수 있고, Composite 컬럼일 수 있다. (Primary key도 Simple, Composite일 수 있다. 1개, 혹은 2개이상일 수 있는 것을 의미한다.)
key가 인덱스에 걸려 있다면 속도가 빨라진다.
Primary key의 예시는 다음과 같은 것이 있다.
Customer 테이블에서는 Customer_ID가 Primary key , Order테이블에서는 Order_ID가 Primary key이다. 또한, Order_line테이블에서는 Order_ID, Product_ID가 Primary key이다. Product테이블에서는 Product_ID가 Primary key이다.
Foreign key의 예시는 다음과 같다.
Customer테이블의 Customer_ID를 참조하는 Order테이블의 Customer_ID컬럼은 Foreign key이다.
Order테이블의 Order_ID를 참조하는 Order_line테이블의 Order_ID는 Foreign key이다.
Product테이블의 Product_ID를 참조하는 Order_line테이블의 Product_ID는 Foreign key이다.
다음으로, Data Integrity Constraints에 대해서 알아본다.
데이터 무결성 제약조건이란, 일종의 안전장치이다. 데이터베이스에 있는 데이터의 정확성과 무결성을 쉽게 유지하는 것이다.
Data Integrity Constraints에는 Domain Constraint, Entity Integrity, Referential Integrity가 있다.
Domain Constraint란 속성들은 모두 정의된 도메인으로부터 가져와야 한다는 것이다. 즉, 테이블의 컬럼에 나타나는 모든 값은 동일한 도메인에서 온 값이여야 한다는 뜻이다. 예시로, 어떤 사람의 학번은 숫자고, 어떤 사람의 학번은 문자면 안된다. 또한, 어떤 사람은 학번이 5자리 어떤 사람은 학번이 10자리면 안된다는 것의 예시가 있다.
Null이란? 다른 값이 적용되지 않거나 해당 값을 알 수 없는 경우, 속성에 할당된 항목이다. N/A(Not Available)이다. Null은 값의 부재 혹은 결측값을 의미한다. Null은 0이 아니라 비어있는 걸 이야기한다.
이 Null을 알아야 Entity Integrity를 이해할 수 있다.
Entity Integrity란 Primary Key 는 NULL 일 수 없다는 것이다. 만약 두 개 이상의 Composite Primary key일 경우 두 개 이상 중에 어떠한 것도 null이면 안된다. 각 행은 고유한 사실을 나타내고 Primary key가 없으면 식별할 수 없다.
다음으로 Referential Integrity에 대해서 알아본다.
Referential Integrity란 각 Foreign key의 값이 다른 테이블의 Primary key의 값과 일치하거나 Foreign key가 null이여야 한다는 것이다.
SQL Table Definition은 다음과 같다.
또한, Delete Rule을 통해 데이터를 안전하게 유지하는 데 다음과 같은 것들이 있다.
- RESTRICT, 관련행이 종속적인쪽에 있는 경우 상위 행을 삭제하는 것을 금지한다.
- CASCADE, 삭제할 상위 행에 해당하는 종속쪽 행을 같이 삭제한다(Cascade)
- 상위 측에서 삭제할 경우 종속 측에서 외래키값을 null로 설정한다.
정리하자면, Referential Integrity란 Relational Model이 비일관성을 처리하는 방법이다.
Referential Integrity의 예시에 대해 알아본다.
다음과 같은 상황에서 S4행을 지운다면 어떻게할 것인가?
첫번째로, ON DELETE CASCADE이 있다.
특정 행을 지울때 연관된 행을 같이 지운다. 이렇게 지우면? -> 참조 무결성에 위배되지 않는다.
두번째로, ON DELETE RESTRICT이 있다.
특정 행을 지울때 제약조건을 건다 지우는 걸 막는다면, 참조 무결성에 위배되지 않는다.
세번째로, ON DELETE NO ACTION이 있다.
아예 액션을 못하게 막는다.
Transforming from ER model to Relational model
ER Model을 Relational Model로 바꾸는 절차에 대해서 알아본다.
1. Mapping each entity type to a relation. The relation includes the following attributes
1. 모든 Entity Type의 Simple Attribute들은 매핑된다.
2. 모든 Entity Type의 Composite Attribute안에 있는 Simple Attribute들은 매핑된다.
3. Identifier Attribute -> Primary key로 매핑된다.
* Entity Type의 Multi-valued Attribute들은 Relation에 포함되지 않는다.
1번 과정의 예시는 다음과 같다.
2. Each weak entity type
1. Weak Entity Type은 Relation이 된다.
2. Relation의 Primary key는 다음과 같이 구성된다.
- Weak Entity Type의 Partial key A
- Identifying owner의 Identifier Attribute B
3. 여기서 B는 Identifying owner로부터 변환된 Relation을 참조하는 Foreign key가 된다.
매핑 예시는 다음과 같다.
위의 Relational Data Model에서 Dependent테이블의 Primary key는 총 4개(First_Name, Middle_Initial, Last_Name, Employee_ID)이다. Employee_ID는 Primary key이면서 Foreign key이다.
-> 즉, Primary key이면서 Foreign key일 수 있다.
3. Each binary 1-M relationship type
Many Side에 속하는 Relation에서 Foreign key를 가진다. 또한, Relationship Type에 속하는 컬럼들도 M쪽에서 속성으로 포함한다.
예시로 다음과 같다.
위의 그림에서 ORDER 테이블의 Customer_ID는 Foreign Key이다.
4. Each binary M-M relationship type
분리된 테이블을 가진다, 또한 분리된 테이블은 M-M관계에 참여하는 Entity Type의 식별자 속성으로 구성된다. 뿐만 아니라, Relationship Type에 포함된 속성들도 분리된 테이블에 포함된다.
예시로 다음과 같다.
새로만들어진 CERTIFICATE테이블에서 Primary key 로 Employee_ID, Coures_ID를 갖는 Composite Primary key이고, 각각은 다른 테이블을 참조하는 Foreign key가 된다.
5. Each binary 1-1 relationship type
한쪽에 Foreign key를 포함시킨다. 더 바람직한 건, Total Participation쪽에 Foreign key를 포함시킨다. 또한, Relationship Type Attribute도 포함한다.
예시로 다음과 같다.
Total Participation쪽에 포함시키지 않으면 안될건 없지만 Total Participation에 포함시키는게 더 바람직하다는 의미이고, Data_Assigned까지 포함된 것에 주의한다.
6. For each unary relationship type, applying step 3, 4, or 5.
다음과 같이, Unary Relationship Type인 경우, 3,4,5 번의 단계에 따른다.
7. Each n-ary (n>2) relationship type is converted into a relation
만약, Relationship Type이 2개보다 많을땐? 다음 그림과 같이 구성된다.
Suppler (SID, )
Supply (SID, Part_ID, PJID, Quantity)
Part (Part_ID, )
Project (PJID, )
8. Mapping Supertype/subtype
supertype은 하나의 Relation, subtype도 하나의 Relation으로 변환된다. supertype의 속성들이 supertype relation으로 들어간다. subtype 속성들이 subtype relation으로 들어간다. 또한, subtype에는 supertype의 primary key가 foreign key이면서 primary key이다.
예시를 통해 알아본다.
이때, subtype primary key의 이름이 뭔지는 상관없다.
9. For each multi-valued attribute, create a relation A. The relation A include
예시를 통해 알아본다.
Multi-valued 속성은 이 속성을 포함하는 테이블로 만들어지고 다음과 같은 Primary key를 가진다.
다음 포스팅에서는 Relational Data Model이 잘 만든 것인지에 대해 알아보는 시간을 갖는다.
'Dev > Database' 카테고리의 다른 글
| [Database] 5. Physical Database Design (0) | 2022.02.25 |
|---|---|
| [Database] 4-2. Logical Database Design (0) | 2022.01.30 |
| [Database] 3. The Enhanced E-R Model (0) | 2022.01.25 |
| [Database] 2. Modeling Data in the Organization (0) | 2022.01.24 |
| [Database] 1. The Database Environment and Development Process (0) | 2022.01.23 |
