Goals:

• 물리적 데이터 모델링 과정을 설명할 수 있다. (be able to explain the process of physical database modelling)

• 데이터 저장구조와 제약조건을 기반으로 테이블 명세서를 작성할 수 있다. (be able to create a table specification based on data storage structure and constraints)

Contents:

• 릴레이션 변환 리뷰(Review of Relation Transformation)

• 모델링 과정 리뷰(Review of modeling process)

• 물리적 데이터 모델링(Physical Data Modeling)

• 저장구조 / 제약조건 /접근경로(Storage Structure / Constraints / Access Paths)

---

💡릴레이션 변환 리뷰

(Review of Relation Transformation)

📍개체의 릴레이션 변환 (사원, 부서, 프로젝트, 부양가족)

• 부서의 위치는 다중값이기 떄문에 별도의 릴레이션으로 표현한다.

• 약개체인 부양가족의 릴레이션 변환은 아래의 그림 참조

📍관계의 릴레이션 변환

사원관리 ERD에서 존재하는 관계들: 감독 1:N, 부양 1:N, 소속 N:1, 관리 1:1, 참여 N:M, 수행 1:N

관계의 릴레이션 변환 방법

• 개체와 개체 사이에는 한 개 이상의 관계가 존재할 수도 있다.

• N:M 관계에서는 독립된 릴레이션을 생성한다.

• 1:N 관계에서는 N쪽 릴레이션에 각각의 관계를 표현한다. 즉 각각의 관계에 따라 각각의 외래키를 포함해서 릴레이션을 생성한다.

• 순환관계에 참여하는 개체의 기본키를 바로 그 개체의 외래키로 넣어서 릴레이션을 완성한다.

📍속성 릴레이션 변환

• 다중값 속성은 하나의 속성에 여러 개의 값이 들어간다는 것을 뜻한다.

• 해당 개체의 기본키와 다중값 속성을 합쳐서 복합속성을 만들고 그 복합 속성을 기본키로 하는 별도의 릴레이션을 생성하면 된다.

  

📍 사원관리 시스템 ERD ➡️ 릴레이션 스키마 변환 결과 (논리적 모델링 과정)

---

💡모델링 과정 리뷰

(Review of modeling process)

📍개념적 모델링(Conceptual data modeling)

데이터베이스 설계 및 개발 과정에서 초기 단계로 사용되며, 비즈니스 요구사항을 이해하고 데이터 구조를 명확하게 정의하는 데 도움이 된다. 결과물: 개념적 데이터 모델인 ERD = ER diagram

➡️➡️➡️

📍논리적 모델링(Logical data modeling)

논리적 데이터 모델은 개념적 데이터 모델(ERD)에서 파생된다. 실제 데이터베이스 시스템의 구축(build)에 필요한 세부 정보를 명확하게 제공하여 데이터베이스 개발 및 유지 관리 과정을 지원한다. 결과물: 논리적 데이터 모델인 논리적스키마 = 릴레이션구조 = 릴레이션 스키마 = 논리적 스키마

➡️➡️➡️

📍물리적 모델링(Physical data modeling)

물리적 모델링은 논리적 데이터 모델을 기반으로 하여 실제 데이터베이스 시스템에 구현(implement)할 때 필요한 세부 정보를 정의한다. 이 모델은 실제 데이터베이스 시스템의 구조, 저장 방법, 인덱싱, 파티셔닝 등과 관련된 모든 세부 사항을 포함한다.

"구축(Build)" typically refers to the process of creating or developing a project or system as a whole, while "구현(Implement)" refers to the stage of actually realizing the plans or designs.

"구축"은 주로 프로젝트나 시스템을 전체적으로 만들거나 개발하는 과정을 의미하고, "구현"은 계획이나 설계를 실제로 실현하는 단계를 가리킨다.

---

💡물리적 데이터 모델링

(Physical Data Modeling)

📍목적(Purpose of physical data modeling)

효율적, 구현 가능한 물리적 DB 구조 설계하는 것이다. 데이터의 저장 구조나 접근 경로에 대해서 DBMS가 지원하는 방법 중에서 선택하고 응답시간, 저장공간의 효율화, 트랜잭션 처리도(Throughput) 등을 고려해야 한다.

📍물리적 데이터 모델링이 하는 일 (Tasks performed)

1. 저장 구조 설계(Storage Structure Design): 데이터의 물리적 저장 방식을 결정하고 구축한다. 데이터베이스 구조, 테이블 구조, 테이블 분할, 이름 영문화를 포함한다.

2. 제약 조건 지정(Constraint Specification): 데이터의 무결성을 유지하기 위해 제약 조건을 정의한다. 예를 들어, 고유성 제약 조건, 외래 키 제약 조건, 데이터 형 지정, 기본키 및 기본값 정의, 체크와 규칙 정의 등을 지정하여 데이터의 일관성을 보장한다.

3. 레코드 집중의 분석 및 설계(Analysis and Design): 레코드 크기와 물리적 저장 장치의 특성에 의존한다. 테이블 분산, 파티셔닝 등 데이터를 논리적 또는 물리적으로 분할하여 저장 및 관리를 최적화한다.

4. 접근 경로 설계(Access Path Design): 데이터에 효율적으로 액세스할 수 있는 경로를 설계한다. 이는 인덱스를 포함한 인덱스 설정, 뷰 정의 등을 설계하는 것을 의미하며, 데이터베이스의 성능을 향상시키는데 중요한 역할을 한다.

📍용어 정리(Terminology)

1. 테이블(Table): 데이터 저장을 위한 가장 기본적인 단위로 논리모델의 릴레이션에 대응한다.

2. 열(컬럼, 속성, Column): 테이블의 각 열은 특정 유형의 데이터를 저장하는 데 사용된다. 각 열은 열 이름, 데이터 유형, 크기, 제약 조건 등의 속성을 가진다.

3. 행(로우, 레코드, Row): 테이블의 각 행은 데이터 레코드를 나타낸다. 각 행은 열의 값들의 집합으로 구성된다.

📍테이블명과 컬럼명의 영문화(Translating table names and column names into English)

• 

테이블명과 컬럼명을 영문화하는 것은 데이터베이스에서 일반적으로 사용되는 관행이다. 영문화를 통해 데이터베이스의 가독성을 높이고, 데이터베이스를 사용하는 사람들 간의 통일성을 유지한다.

테이블명(Table Name):

1. 의미 전달: 테이블명은 해당 테이블이 무엇을 나타내는지 명확하게 전달해야 한다. 가능한 간결하고 명확한 명칭을 선택하자.

2. 복수형 사용: 테이블명은 주로 복수형으로 작성된다. 예를 들어, 사용자 정보를 저장하는 테이블은 "users"와 같이 복수형으로 명명될 수 있다.

3. 단어 구분: 여러 단어를 포함하는 경우, 각 단어를 밑줄(_) 또는 대문자로 구분하여 가독성을 높일 수 있다. 예를 들어, "user_info" 또는 "UserInfo"와 같이 사용한다.

컬럼명(Column Name):

1. 소문자 사용: 컬럼명은 주로 소문자로 작성되며, 필요한 경우 밑줄(_) 또는 대문자를 사용하여 단어를 구분한다. 예를 들어, "first_name" 또는 "firstName"

2. 의미 있는 이름: 가능한 컬럼명을 직관적이고 의미 있는 이름으로 지정한다. 예를 들어, 사용자 테이블의 이름 컬럼은 "name"이 아닌 "user_name" 또는 "full_name"과 같이 지정한다.

3. 통일성: 테이블 전체에서 일관된 네이밍 규칙을 사용하여 통일성을 유지한다. 모든 컬럼명이 일관되게 작성되도록 한다.

📍결과물(Output)

물리적 모델링의 결과물은 물리적 데이터베이스 스키마로 표현된다.

1. 주변 환경 정보(Environmental Information): 데이터베이스 시스템이나 애플리케이션이 운영될 환경에 대한 정보를 포함한다. 그 예로는 아래와 같다.

   • 하드웨어 자원 현황, 운영체제 현황, DBMS 버전 및 파라미터 정보 파악

2. 표준 용어집(Standard Glossary) 데이터베이스에서 사용되는 용어와 그 정의를 포함하는 문서이다. 이는 일관된 용어 사용을 촉진하고 의사소통을 원활하게 한다.

3. 뷰 및 인덱스 정의(View and Index Definitions) 데이터베이스의 뷰(View)와 인덱스(Index)에 대한 정의와 사용법을 설명하는 문서이다. 이는 뷰와 인덱스의 목적, 구성 요소, 사용 사례 등을 설명한다.

4. 데이터베이스 운영 정보(Database Operations Information): 데이터베이스 운영 및 관리에 필요한 정보를 포함한다. 그 예로는 아래와 같다.

   • 사용자 관리 정책, 백업/복구 기법 및 정책, 보안관리 정책

5. 테이블 정의서(Table Definitions) 각 테이블의 구조와 속성을 자세히 설명하는 문서이다. 이는 테이블명, 컬럼명, 데이터 유형, 크기, 제약 조건 등을 포함한다.

6. 컬럼 정의서(Column Definitions): 각 컬럼의 의미와 사용법에 대한 설명을 포함하는 문서이다. 이는 각 컬럼의 목적, 데이터 형식, 제약 조건, 예시 등을 설명한다.

7. 아래의 사진은 논리적 모델링의 결과물 2이다.

   

📍과정(Process)

저장 구조 설계(Storage Structure Design) ➡️ 제약 조건 정의 (Constraint Definition) ➡️접근 경로 설계 (Access Path Design)

이러한 과정을 통해 데이터베이스 시스템을 실제로 구축하고 운영할 수 있는 물리적 구조를 정의하게 된다.

---

💡저장구조(Storage Structure)

데이터를 효율적으로 저장하고 관리하기 위한 물리적인 구조를 결정하는 과정이다. 이 단계에서는 구체적인 저장 구조를 설계한다. 예) 해당 데이터를 등록한 날짜, 시스템 번호 등

• 릴레이션으로부터 테이블명과 컬럼명을 도출하여 각각의 테이블 구조를 정의한다.

• DBMS에 맞는 데이터타입을 선택한다.

• 논리적 데이터 모델과 물리적인 DBMS의 특성을 고려하여 최적의 데이터타입을 선택한다.

• 데이터타입의 자세한 설명은 아래 추가 설명 참조

• 관리상 필요한 컬럼이 추가되기도 한다.

📍저장구조 설계 예시(Example of storage structure design)

Student 테이블 데이터 타입을 정의해보자

• 전화번호: 계산에 쓰이는 칼럼이 아니므로 고정길이 문자열로 정의한다(char, 1)

• 소속학과: 학과코드 (2 byte) 가 들어간다. (char, 2)

• 날짜: 대부분 일반적인 날짜 형식을 사용한다. (detetime)

---

💡제약조건(Constraints)

데이터베이스의 무결성(Integrity)을 유지하기 위해 필요한 제약 조건을 정의하는 과정이다. 이러한 제약 조건은 데이터의 일관성과 정확성을 보장한다.

무결성(Integrity)데이터의 정확성, 일관성 및 신뢰성을 보장하는 개념

기본키(Primary Key) 테이블 내의 각 행을 고유하게 식별하는 열(또는 열의 조합)

자연키(Natural Key) 데이터 내에서 이미 존재하는 고유한 식별자 (예: 사람의 주민등록번호, 제품의 일련번호)

인조키(Artificial Key) 데이터베이스 시스템에서 생성된 고유한 식별자로, 일반적으로 정수형 순차적인 값이된다.

NULL 데이터가 없음을 나타내는 특별한 값. 열에 NULL을 허용하도록 설정하면 해당 열에는 데이터가 없을 수 있음.

NOT NULL 해당 열에 NULL 값을 허용하지 않도록 강제한다.

외래키(Foreign Key) 한 테이블의 열이 다른 테이블의 기본키와 관계를 나타낸다.

1. 기본키/유일성 제약(Primary Key/Uniqueness Constraint)

   • 논리 모델의 기본키는 물리 모델의 기본키(Primary Key)이다.

   • 기본키는 개체무결성 제약조건을 만족시키기 위한 조건이다. (Unique + Not Null) = 해당 열에 중복된 값이 없도록 보장한다.

   • 기본키를 제외한 나머지 후보키 들은 기본적으로 Unique 속성을 가진다.

   • Not Null 속성을 갖는 지 여부를 다시 한번 검토한다.

2. 자연키(Natural PK)와 인조키(Artificial PK)

   • 

     기본키가 여러 개의 속성으로 구성되거나 크기가 큰 경우 인조키를 인위적으로 생성한다.

   • 자연키는 쉽게 이해할 수 있으며, 인조키는 쉽게 만들고 사용할 수 있다.

   • 기본키를 구성하는 속성들이 기본키의 조건을 만족시킨다는 확신이 없을 경우 인조키를 만들어서 사용하는 것이 좋다.

3. NULL과 NOT NULL

   • NULL 값은 매우 주의해서 사용해야 한다.

   • 어떤 컬럼의 값이 생략이 가능하다고 해서 NULL을 허용하는 것이 아니라 알지 못하는 값을 표현할 필요가 있는 경우에 사용한다.

   • 단순하게 값이 생략되어도 좋은 경우라면, 공백, 0, -1 등의 값을 사용해서 표현하는 것이 좋다.

   • 특히, 인덱스로 지정이 되는 컬럼에서는 NULL 값을 허용하지 않는 것을 권장한다.

4. 외래키 지정(Foreign Key Constraint)

   • 부모 테이블의 기본키 값에 대한 참조 무결성을 유지하고 자식 테이블 간의 관계를 유지한다.

• 1:M 관계에서 1(One)에 있는 PK를 M(Many)의 FK로 변환한다.

• 1:1 관계에서는 전체가 참여하는 쪽(Mandatory)에 상대편의 PK를 FK로 생성한다.

• N:M 관계는 관계를 표현하는 테이블을 생성 후 이 테이블에 FK를 생성한다.

5. 체크(Check) 제약조건

   

   • 테이블의 값이 올바른 값만 입력되도록 제약조건을 두는 것이 체크 제약 조건임

   • 도메인 무결성을 유지하기 위해서 필요하고 허용 양식과 허용 값을 검사할 수 있다.

   • 일반적으로, 컬럼 단위로 제약조건을 주지만, 여러 컬럼에 걸쳐 제약조건을 줄 수 있다.

   • 예시로 나이가 0보다 커야하는 등의 조건을 설정할 수 있다.

6. 기본값(Default Value)

   • 데이터 삽입 시 값을 생략하면 미리 지정되어 있는 기본값이 대신 삽입하도록 지정한다.

   • 예를 들어, 학과 컬럼에 기본값으로 ‘GE’를 지정하면, 학생의 정보가 입력될 때 학과에 관한 값이 없으면 자동으로 ‘GE’ 라는 값이 들어간다.

   • 값의 생략은 허용하면서, NULL 값은 허용하지 않는 경우에 유용하게 사용된다.

1. NULL에서 Y의 뜻: NULL 값을 Y(허용)한다. = 데이터가 없어도 상관 없다는 뜻. *NULL에 Y 표시가 없다는 뜻은 NOT NULL, 즉 데이터를 무조건 입력해야한다.

2. PK (Primary Key): 아이디가 PK이다. (PK라고 지정되면 NOT NULL과 UK(Unique Key)가 자동으로 지정되기 때문에 별도로 Y표시 하지 않았다.)

3. 전화번호, 주소, 생년월일의 NULL-Y : 1번처럼 데이터가 없어도 상관 없다는 뜻이다.

4. 소속학과는 FK(외래키)이다. 외래키는 참조테이블, 참조컬럼을 항상 포함하기 때문에 이를 통해 디테일을 알 수 있다. 비고는 실제 들어가는 데이터의 예시를 보여준다.

5. 체크(check)제약 조건: 길이는 1, 비고의 F,M은 두개의 옵션중에 하나를 선택해야 한다는 뜻

1. PK(Primary Key)이다.

2. UK(Unique Key): 중복은 허용하지 않는다.

3. NULL 값을 허용한다 = 데이터가 없어도 된다.

4. NULL값을 허용하지 않는다 = 데이터를 입력해야한다.

1. int = 정수

2. FK이고 참조테이블, 참조컬럼에서 디테일을 확인한다.

3. PK이다.

4. NULL 값을 허용한다.

5. 시험결과 점수가 101점, -5점이 될수 없기 때문에 제약조건을 비고란에 넣어준다.

더 깊게 공부할 수 있지만 이번 과목에서는 저장구조, 제약조건, 접근경로 까지만 공부하도록 한다.

---

접근경로(Access Paths)

📍접근 경로(access paths)란?

물리적 데이터베이스 설계에서는 인덱스를 어떻게 구성하고 저장할지, 인덱스와 테이블의 데이터를 어떻게 조합할지 등을 고려한다. 이 과정 중 하나를 '접근 경로'라고 하는데 데이터베이스 시스템이 쿼리를 실행할 때 데이터를 접근하는 방법을 결정하는 것을 말한다.

📍인덱스(Index)란?

데이터베이스에서 효율적인 데이터 검색을 지원하기 위해 사용되는 자료구조이다. 특정 열(또는 열의 조합)에 대한 정렬된 구조로, 해당 열의 값과 해당하는 행의 위치(또는 주소)를 기록한다. 적절한 인덱스 디자인과 스캔 전략을 통해 데이터 엑세스의 속도를 향상시킬 수 있다.

📍인덱스(Index)의 종류

(Details of index type will be covered in the advanced course - probably next semester)

📍인덱스(Index)의 장단점(pros and cons)

장점(Pros)

1. 검색 속도 향상: 인덱스를 사용하면 데이터베이스 시스템은 테이블을 전체적으로 스캔하는 대신 인덱스를 통해 데이터를 빠르게 찾을 수 있다. 검색 속도를 향상시킨다

2. 중복 데이터 제거: 유일성 인덱스로 만들면 특정 열(또는 열의 조합)의 값이 중복되지 않도록 보장할 수 있다. (유일성 제약 조건 강화)

단점(Cons)

1. 저장 공간 사용: 인덱스는 데이터베이스의 추가적인 저장 공간을 사용한다. 특히 대규모 테이블에서 인덱스를 생성하면 데이터베이스 크기가 크게 증가할 수 있다.

2. 업데이트 및 삽입 성능 저하: 인덱스를 유지하려면 데이터의 변경(업데이트 또는 삭제)이 발생할 때마다 인덱스도 업데이트해야 한다. 이는 데이터의 업데이트 또는 삭제 작업 시 성능을 저하시킬 수 있다.

3. 인덱스 관리 오버헤드: 인덱스를 생성하고 유지하는 데는 관리 오버헤드가 발생한다. 데이터의 변경이 있을 때마다 인덱스를 재구축해야 하거나 통계를 업데이트해야 할 수 있다.

단점도 있지만 데이터에 빠르게 액세스하고 검색하는 데 사용되는 매우 유용한 도구이기 때문에 많이 사용된다.

📍테이블(Table)이란?

관련된 데이터를 구조화하여 저장하는 데이터베이스의 기본적인 구성 요소 중 하나이다. 테이블은 열과 행의 형태로 이루어진 이차원 구조를 가지며, 각 열은 특정 유형의 데이터를 나타내는 속성이고, 각 행은 특정 레코드를 나타낸다. 일반적으로 테이블은 비슷한 종류의 데이터를 그룹화하고 저장하기 위해 사용된다.

📍인덱스와 테이블의 공통점(common ground of Index and Table)

1. 데이터베이스 구성 요소: 인덱스와 테이블은 모두 데이터베이스의 구성 요소이다. 데이터베이스 시스템 내에서 데이터를 저장하고 관리하는 데 사용된다.

2. 데이터 구조화: 인덱스와 테이블은 모두 데이터를 구조화하여 저장하는 데 사용된다. 테이블은 데이터를 열과 행의 형태로 구조화하고 저장하는 반면, 인덱스는 데이터의 특정 열에 대한 검색 경로를 구조화하여 저장한다.

📍인덱스와 테이블의 차이점(Different between Index and Table)

1. 목적: 테이블은 실제 데이터를 저장하는 데 사용되는 구조로, 데이터를 그룹화하고 관리하는 데 중점을 둔다. 반면에 인덱스는 데이터를 빠르게 검색하고 액세스하기 위한 목적으로 사용된다.

2. 데이터 저장: 테이블은 실제 데이터를 저장하는 곳으로, 각 열은 특정 유형의 데이터를 나타내는 속성이고 각 행은 레코드를 나타낸다. 반면에 인덱스는 특정 열에 대한 검색 경로를 저장하는 자료구조이다.

3. 용도: 테이블은 데이터를 삽입, 수정, 삭제 및 검색하는 데 사용된다. 데이터베이스의 주요 작업은 테이블을 통해 이루어진다. 반면에 인덱스는 데이터 액세스를 최적화하는 데 사용된다. 데이터를 검색할 때 인덱스를 사용하여 데이터를 빠르게 찾을 수 있다.

요약하면, 테이블은 데이터를 저장하고 구조화하는 데 사용되는 주요 구성 요소이며, 인덱스는 데이터에 빠르게 액세스하고 검색하는 데 사용된다. 두 구조는 데이터베이스에서 서로 보완적인 역할을 하며 데이터의 효율적인 관리와 검색을 위해 함께 사용된다.

📍인덱스 스캔(Index scan) & 테이블 스캔(Table scan)

인덱스 스캔(Index scan)과 테이블 스캔(Table scan)은 데이터베이스 시스템에서 쿼리 실행에 사용되는 두 가지 주요 방법이다. 인덱스와 테이블을 사용하는 방식에 따라 구분된다.

공통점(common ground)

1. 데이터 액세스: 둘 다 데이터베이스에서 데이터를 읽어오는 과정이다.

2. 쿼리 실행 시간: 두 방법 모두 쿼리 실행 시간에 영향을 미친다.

차이점(difference)

1. 액세스 방법:

   • 테이블 스캔: 전체 테이블을 처음부터 끝까지 순차적으로 읽어온다. 인덱스를 사용하지 않고 전체 테이블을 스캔하는 것이기 때문에 데이터가 많을 경우 성능이 저하될 수 있다.

• 인덱스 스캔: 인덱스를 사용하여 특정 조건을 만족하는 레코드를 찾는다. 인덱스는 데이터베이스 테이블의 특정 열에 대한 정렬된 구조이다. 쿼리가 인덱스를 활용하면 데이터베이스 엔진은 인덱스를 스캔하여 원하는 레코드를 찾는다.

2. 응답 시간:

   • 테이블 스캔: 전체 테이블을 스캔하므로 인덱스 스캔에 비해 더 많은 시간이 소요될 수 있다.

• 인덱스 스캔: 인덱스를 사용하므로 데이터를 빠르게 찾을 수 있다. 특히 검색 조건이나 정렬에 사용되는 열에 대한 인덱스가 있는 경우에 유용하다.

3. 자원 사용:

   • 테이블 스캔: 전체 테이블을 스캔하므로 보다 많은 양의 디스크 I/O 및 CPU 리소스를 사용한다.

• 인덱스 스캔: 보다 적은 양의 디스크 I/O 및 CPU 리소스를 사용할 수 있다.

---

저장구조 설계 추가설명(further reference regarding to storage structure design)

데이터타입(Data type) 크게 문자(열) 타입, 숫자 타입, 날짜 타입 등으로 분류되며, DBMS에 따라 세부적으로 더 많은 종류의 타입을 지원한다. (DBMS 종류: ORACLE, MySQL 등)

문자열형 데이터 타입

숫자 데이터 타입

날짜 데이터 타입