DATABASE Analysis & Design with ERD

DATABASE Analysis & Design with ERD 2001.09.22 Lecturer : Kim Hyung Su Internet System Administrator (ISA) IT freelancer

What we’re going to do… • Database Review • Database theory • DBMS(DataBase Management System) • Data Dependency • Normalization & ERD • Normalization • ERD (Entity-Relationship Diagram) • Database Modeling Processes • Database Design Process I • Analysis of Requirement • Conceptual Design (ERD 1) • Exercise Presented by Hyung Su Kim (ISA)

What we’re going to do…(Continued) • Database Design Process II • Logical Design (ERD 2) • Physical Design & Development • Exercise • Example materials & Other Issues • Information Technologies relevant to DB • Q&A Presented by Hyung Su Kim (ISA)

Chapter 1 Database Review • 1. Database Theory • Database System • Database vs. File System • Database 의 구성요소 • 2. DBMS (DataBase Management System) • DBMS의 정의 • DBMS의 처리 모형 • DBMS의 기능 • DBMS의 종류 • 3. Data Dependency • Data Dependency의 정의 • Database의 변이성 • 데이터 종속성의 종류 Presented by Hyung Su Kim (ISA)

1. Database Theory Database : 어느 한 조직의 여러 응용 시스템들이 공유할 수 있도록 통합, 저장한 운영 데이터의 집합 Database Presented by Hyung Su Kim (ISA)

1. Database Theory(Continued) Database System Database system data hardware software user programmers End users DB Designer DBA Presented by Hyung Su Kim (ISA)

1. Database Theory(Continued) Store Database vs. File System Integrated Data Share Stored Data DB Operational Data Shared Data DB Integrated DB Operation DB DB DB Integrate Presented by Hyung Su Kim (ISA)

Database 의 구성요소 개체 (Entity) : Database가 표현하려는 유형, 무형의 정보대상 학생, 교수 속성 (Attribute) : 개체를 구성하는 특성, 상태 학번, 이름, 전공, 성적…. 관계 (Relationship) : 개체집합과 개체집합 간의 관계를 표현 수강신청(과목, 학생), 강의(교수, 학생) Presented by Hyung Su Kim (ISA)

2. DBMS(DataBase Management System) • DBMS의 정의 Definition 1 (물리적 정의) 대량의(large amount), 영속적이며 (persistent), 신뢰성(reliable)있으며, 공유되는(shared) 데이터의 관리를 위한 software Definition 2 (논리적 정의)DBMS란 많은 양의 데이터를 체계적으로 공유(systematic sharing) 하고 결합 해 주는(integrating) Software Presented by Hyung Su Kim (ISA)

Application 1 Application 2 Application 3 DBMS Database DB 1 DB 2 DB 3 2. DBMS (Continued) 응용프로그램과 데이터의 중재자로서, 모든 응용 프로그램들이 데이터베이스를 공용할 수 있도록 관리해주는 소프트웨어시스템. Presented by Hyung Su Kim (ISA)

8 1 2 7 6 이용자 프로그램 수행단위 1 이용자 프로그램 수행단위 N 스키마 및 저장스키마 2 DBMS 9 시스템버퍼 3 운영체제 (OS) 서브 스키마 N 서브 스키마 1 5 4 통신영역 1 통신영역 N 데이터베이스 이용자 작업영역 (UWA) N 이용자 작업영역 (UWA) 1 2. DBMS (Continued) • ①데이터요구 • ②데이터요구 분석 • ③물리적 I/O 작업 요구 • ④데이터 획득 • ⑤데이터 이동 • ⑥UWA로 이동 • ⑦상태정보 전달 • ⑧데이터 사용 • 시스템 버퍼 관리 • DBMS의 처리 모형 Presented by Hyung Su Kim (ISA)

2. DBMS (Continued) • DBMS의 기능 • 데이타 저장과 개발 및 유지보수 측면에서 중복성의 통제 • 다사용자 간의 데이타의 공유 • 권한없는 사용자의 데이타 접근을 통제 • 다양한 사용자에게 다양한 형태의 인터페이스를 제공 • 데이타 사이에 존재하는 복잡한 관련성을 표현 • 데이타베이스의 무결성을 보장 • 벡업과 복구 기능을 제공 Presented by Hyung Su Kim (ISA)

고 객 1 N N 점 포 주 문 1 N 주문항목 2. DBMS (Continued) • 계층형 데이터베이스 시스템(Hierarchical Database System) • DB 설계 시Tree 형태의 자료구조도(Data structure Diagram) • Ex) IMS(Information Management System), 1968, IBM • DBMS의 종류(1) Presented by Hyung Su Kim (ISA)

네트워크 데이터베이스 시스템(Network Database System) • DB설계 시 Network 형태의 자료구조도 • CODASYL(Conference on Data Systems Language) DBTG(Data Base Task Group), 1971 처음 모델제시 • Ex) IDMS (Computer Associates), IDS II(Honeywell Information Systems), DMS/1100(UNIVAC) • DBMS의 종류(2) 고 객 출 고 1 1 N N 주 문 1 N 주문항목 Presented by Hyung Su Kim (ISA)

FK 주문번호 주문항목 PK 고객번호 고 객 1 N N 1 PK 주문번호 주 문 FK 고객번호 • 관계형 데이터베이스 시스템(Relational Database System) • DB 설계 시 개체-연관도 (ER-Diagram) • Ex)SQL/DS (1981, IBM), DB2(1983, IBM), Oracle, SQL Server, Sybase… • DBMS의 종류(3) Presented by Hyung Su Kim (ISA)

종업원 클래스 주소 클래스 구성관계 성 명 도, 시 주 소 군, 구 직 종 동 계승관계 직원 클래스 용역 클래스 • 객체지향 데이터베이스 시스템(Object-Oriented Database System) • DB설계 시  UML(Unified Modeling Language) 등 이용 • Ex) Jasmine (Fujitsu, 1998) • DBMS의 종류(4) Presented by Hyung Su Kim (ISA)

객체-관계 통합 데이터베이스 시스템(Object-Relationship Database System) • 객체지향 데이터베이스의 장점과 관계형 데이터베이스의 장점을 취하여 하나의 시스템으로 통합된 모델- (최근 RDB의 추세) • DBMS의 종류(5) Presented by Hyung Su Kim (ISA)

상용 DBMS Data Model • DBMS의 종류(6) 계층형(Hierarchical Modeling) ex) IMS 그물형(Network Modeling) ex) IDMS, ADABAS, TOTAL 관계형(Relational Modeling) ex)ORACLE, INRORM,DB2,SYBASE INGRES, SQL SERVER 객체형(Object Oriented Modeling) ex)OBJECT STORE, OBJECTIVITY, UNISQL, Odysseus 의미형(Semantic Modeling) Presented by Hyung Su Kim (ISA)

3. Data Dependency • Data Dependency의 정의 데이터들간에 존재하는 관계성 또는 제약조건 데이터 종속성 • 데이터 종속성 분석의 의미 정보의 무 손실 표현 탄탄한 DB 스키마의 구성 중복성 감소 Presented by Hyung Su Kim (ISA)

3. Data Dependency(Continued) • 데이터베이스 변이성 한 테이블에서 불필요하거나 중복되는 속성이 존재함에 따라 생성되는 이상현상(Anomaly) 수강 릴레이션 1) 삭제이상 3) 갱신이상 2) 삽입이상 학번 600인 학생이 4학년이다. Presented by Hyung Su Kim (ISA)

3. Data Dependency(Continued) • 데이터베이스 변이성의 원인 여러 종류의 사실들을 하나의 릴레이션으로 표현 하려하기 때문 문제의 해결 애트리뷰트들간의 종속성 분석 기본적으로 하나의 종속성은 하나의 릴레이션으로! Normalization Presented by Hyung Su Kim (ISA)

데이터 종속성의 종류 함수 종속성(Functional Dependency: FD) 어떤 릴레이션 R에서 애트리뷰트 X의 값 각각에 대해 애트리뷰트 Y의 값이 오직 하나만 연관되어 있을 때 Y는 X에 함수 종속 이라 한다. FD A  B: A의 특정한 값에 대해 오직 하나의 B값이 존재. 과목코드  과목명: 한 과목코드 값에 대해 오직 하나의 과목명 존재 학생이름  학년: 한 학생의 값에 대해 하나의 학년 값 존재 교수이름  연구실: 교수이름이 주어지면 연구실 속성값 결정 Presented by Hyung Su Kim (ISA)

데이터 종속성의 종류 다치(다중 값) 종속성( Multi-Valued Dependency : MVD) ① 한 세트의 속성 값들과 다른 세트의 속성 값들 사이에 존재하는 종속성 R(A,B,C) : A  B: 속성 A의 값에 대해 속성 B와 속성 C의 값은 서로 독립적이다. 예) 업무분장(부서,직원,프로젝트) Presented by Hyung Su Kim (ISA)

데이터 종속성의 종류 조인(결합) 종속성( Join Dependency : JD) ① 독립된 함수 종속성을 갖는 속성들끼리 결합하여 비 상실결합, 즉 결합된 속성간에도 함수 종속성이 형성되는 특성 예) 직원(직원번화, 성명, 사무실번호) * 사무실(사무실번호, 전화번호) Presented by Hyung Su Kim (ISA)

Chapter 2 Normalization & ERD • 1. Normalization • 정규화의 단계 • 정규형의 필요성 • 제 1 ~제 3 정규형, 보이스 코드 정규형 • 고급 정규형 • 2. ERD • ERD의 소개 • 개체, 연관, 값 타입 • ERD의 기본적인 표현 • 기수성 • 3. Database Design Process Presented by Hyung Su Kim (ISA)

1. Normalization • 정규형(Normal Form) 관계형 데이터베이스 릴레이션의 정형적 형태 • 정규화(Normalization) 릴레이션들이 특정 정규형(1NF,2NF,3NF etc.)에서 정의한 조건들을 충족시킬 수 있도록 릴레이션을 설계하는 과정 Presented by Hyung Su Kim (ISA)

1. Normalization(Continued) 비정규 릴레이션 중복되는 속성 및 Group 속성 제거 모든 Domain값을 원자값으로 • 정규화의 단계 함수적 종속성 제거 (부분함수 종속성 제거, ·특정 항목이 기본 Key의 일부분에 대해 통제 받는 구조제거) 1NF 2NF 이행함수 종속제거 ·Ｘ→ＹＹ→Ｚ인 경우 Ｘ→Ｚ는 불가 기초적 정규화 3NF 결정자 제거 ·결정자 후보 Key가 아닌 함수종속제거 진보된 정규화 (사용자의 요구상황에 따른 정규화 진행) BCNF 다치종속제거 (Key들간 종속성 제거) 4NF 후보 Key를 통한 Join 종속제거 5NF Presented by Hyung Su Kim (ISA)

1. Normalization(Continued) 자료구조의 안전성 최대화 • 정규화의 필요성 자료의 불일치성 최소화: Data의 일관성 유지 자료의 갱신 및 삭제에 따른 이상현상 (Anomalies) 제거 저장공간 최소화 저장장소 중복제거 Presented by Hyung Su Kim (ISA)

1. Normalization(Continued) • 정규화 절차의 핵심적 사안 • 하나의 릴레이션에는 하나의 주제(theme)만이 포함되도록 함 • (이상 현상은 한 릴레이션의 여러 개의 주제가 섞여 발생하는 것으로 볼 수 있음 ) • 한 릴레이션에는 그 릴레이션의 기본 키에 의해 결정되는 • 속성만을 포함시키도록 함 • 더 이상의 이상 현상이 발생하지 않음이 증명된 정규형 Presented by Hyung Su Kim (ISA)

1. Normalization(Continued) • 속성들 중에 반복 그룹이 있다. • 모든 속성들이 원자값(Atomic Value)을 갖지 못한다. • 한 개 이상의 속성들이 배열로 선언되어 있다. • 기본 키가 없다. • 비정규형 Presented by Hyung Su Kim (ISA)

1. Normalization(Continued) 정규화의 기본 아이디어 서로 독립적인 관계는 별개의 릴레이션으로 표현 제 1 정규형 조건 제 2 정규형 조건 제 3 정규형 조건 1NF 2NF 3NF Presented by Hyung Su Kim (ISA)

제 1 정규형 (First Normal Form) • 어떤 릴레이션 R의 모든 도메인이 원자값(Atomic Value)만으로 되어 있다면 제 1 정규형에 속한다. 제 1 정규형의 예) 수강지도 릴레이션 Presented by Hyung Su Kim (ISA)

학번 지도교수 성적 학과 과목번호 • 제 1 정규형 (First Normal Form) 수강지도 릴레이션의 함수 종속성 키가 아닌 속성들이 기본 키에 완전 함수 종속되지 못하고 부분 함수 종속  삽입, 삭제, 갱신 이상(Anomlay) 발생 Presented by Hyung Su Kim (ISA)

제 1 정규형 (First Normal Form) 2. 삭제이상 학번이 200인 학생이 교과목 C123을 취소하였다. 3. 갱신이상 학번이 400인 학생의 지도교수가 P3로 변경되었다. 1. 삽입이상 학번이 500인 학생의 지도교수가 P4이다 Presented by Hyung Su Kim (ISA)

제 1 정규형 (First Normal Form) 문제의 해결 부분 함수 종속성의 제거 지도 교수 학 번 학 번 성 적 과목 번호 학 과 지도 릴레이션 수강 릴레이션 Presented by Hyung Su Kim (ISA)

제 2 정규형 (Second Normal Form) • 어떤 릴레이션 R이 1NF이고 키에 속하지 않은 애트리뷰트 모두가 기본 키에 완전 함수 종속이면, 제 2 정규형에 속한다. 제 2 정규형의 예) 수강 릴레이션과 지도 릴레이션 지도 수강 Presented by Hyung Su Kim (ISA)

제 2 정규형 (Second Normal Form) 지도 및 수강 릴레이션의 함수 종속성 지도 교수 학 번 학 번 성 적 과목 번호 학 과 지도 릴레이션 수강 릴레이션 이행적 함수 종속(Transitive FD) R.AR.B와 R.BR.C가 성립하면 R.AR.C가 성립(이행적 함수 종속)  삽입, 삭제, 갱신 이상(Anomlay) 발생 Presented by Hyung Su Kim (ISA)

제 2 정규형 (Second Normal Form) 2. 삭제이상 학번이 200인 학생이 지도교수 P2의 지도를 취소하였다. 지도 1. 삽입이상 P4인 지도교수는 화공과에 속한다. 3. 갱신이상 P1교수의 학과가 전자과로 변경되었다. Presented by Hyung Su Kim (ISA)

제 2 정규형 (Second Normal Form) 문제의 해결 이행적 함수 종속성의 제거 지도 교수 지도 릴레이션 학 번 학 과 학 번 지도 교수 지도 교수 학 과 학생지도 릴레이션 지도교수 학과 Presented by Hyung Su Kim (ISA)

지도 교수 학 과 P1 P2 P3 P1 컴퓨터 전 기 컴퓨터 컴퓨터 • 제 3 정규형 (Third Normal Form) • 어떤 릴레이션 R이 2NF이고 키에 속하지 않은 모든 애트리뷰트들이 기본 키에 이행적 함수 종속이 아닐 때 제 3 정규형에 속한다. 제 3 정규형의 예) 학생 지도 릴레이션과 지도교수 학과 릴레이션 학생지도 지도교수 학과 Presented by Hyung Su Kim (ISA)

제 3 정규형 (Third Normal Form) 복수의 후보 키를 가지고 있거나 후보 키들이 복합 애트리뷰트들로 구성되었거나 후보 키들이 서로 중첩되는 경우에 제 3 정규형의 정의를 적용할 수 없다. • 보이스/코드 정규형 (BCNF) Presented by Hyung Su Kim (ISA)

지도 교수 학 과 P1 P2 P3 P1 컴퓨터 전 기 컴퓨터 컴퓨터 보이스/코드 정규형 (BCNF) • 릴레이션 R의 모든 결정자(determinant) 가 후보 키(candidate key)이면 릴레이션 R은 보이스/코드 정규형에 속한다. BCNF의 예) 수강 릴레이션(2NF과정을 통해 생성) BCNF의 예) 학생지도 릴레이션 지도교수 학과(3NF과정을 통해 생성) Presented by Hyung Su Kim (ISA)

3NF BCNF 보이스/코드 정규형 (BCNF) 3NF와 BCNF의 포함관계 3NF이지만 BCNF가 아닌 릴레이션의 문제는 ? Presented by Hyung Su Kim (ISA)

보이스/코드 정규형 (BCNF) 3NF이지만 BCNF가 아닌 릴레이션의 문제는 ? 수강과목 릴레이션 • 제약조건 • 각 과목에 대해 한 학생은 오직 한교수의 강의만 수강 • 각 교수는 한 과목만 담당 • 한 과목은 여러 교수가 담당할 수 있다. Presented by Hyung Su Kim (ISA)

학 번 교 수 과 목 보이스/코드 정규형 (BCNF) 3NF이지만 BCNF가 아닌 릴레이션의 문제는 ? 후보 키 : <학번, 과목>, <학번, 교수> 기본 키 설정 : <학번, 과목> 교수 Or <학번, 교수> 과목 키에 속하지 않는 애트리뷰트가 없다2NF 이행종속이 없다3NF 결정자인 교수 애트리뷰트는 후보 키가 아니다.BCNF(X) 기타 함수 종속 : 교수  과목 수강과목 릴레이션의 함수 종속 다이어그램 Presented by Hyung Su Kim (ISA)

보이스/코드 정규형 (BCNF) 3NF이지만 BCNF가 아닌 릴레이션의 문제는 ? 2. 삭제이상 학번이 100인 학생이 자료구조 과목을 취소하였다. 3. 갱신이상 P1교수의 과목이 자료구조로 변경되었다. 1. 삽입이상 교수 P5가 자료구조론을 담당하였다 Presented by Hyung Su Kim (ISA)

보이스/코드 정규형 (BCNF) 문제의 해결 원인: “교수” 애트리뷰트와 같은 결정자 이면서 후보 키가 아닐 경우  수강교수 릴레이션과 과목교수 릴레이션으로 분할 수강교수 릴레이션 과목교수 릴레이션 교 수 과 목 학 번 교 수 Presented by Hyung Su Kim (ISA)

고급 정규형 제 4 정규형 (Fourth Normal Form) • 릴레이션 R에 MDV(다치 종속) AB가 존재할 때 R의 모든 애트리뷰트들도 또한 A에 함수 종속(즉, R의 모든 애트리뷰트 X에 대해 AX이고 A가 후보 키)이면 릴레이션 R은 4NF에 속한다. 제 5 정규형 (Fifth Normal Form) • 릴레이션 R에 존재하는 모든 조인 종속(JD)이 릴레이션 R의 후보 키를 통해서만 성립된다면 릴레이션 R은 제 5 정규형(5NF) PJ/NF(Projection-Join Normal Form)에 속한다. Presented by Hyung Su Kim (ISA)

1. Normalization(Summary) (1) 원래의 1NF 릴레이션에 부분 함수 종속을 제거하기 위해 프로젝션. 2NF (2) 2NF 릴레이션에 이행 함수 종속을 제거하기 위해 프로젝션. 3NF (3) 3NF 릴레이션에 결정자가 후보 키가 아닌 함수 종속을 제거하기 위해 프로젝션 BCNF (4) BCNF 릴레이션에 함수 종속이 아닌 다치 종속을 제거하기 위해 프로젝션. 4NF (5) 4NF 릴레이션에 후보 키를 통하지 않는 조인 종속을 제거하기 위해 프로젝션. 5NF Presented by Hyung Su Kim (ISA)

어느 단계까지 정규화를 시켜야 하나… 제 5 정규형까지 ? 학생주소 릴레이션 (학번, 이름, 주소, 전화번호) 존재하는 함수종속: 학번 이름, 주소, 전화번호 전화번호주소 학생 전화(학번, 이름, 전화번호) 전화 주소(전화번호,주소) ? 과연 유용한가 Presented by Hyung Su Kim (ISA)

DATABASE Analysis & Design with ERD