KOSA fullstack 교육(DB 모델링, 정규화)

디비 모델링 

단계	설명	주요 작업
개념 설계	사용자의 요구를 바탕으로 DB의 큰 틀을 설계	엔터티 추출 핵심 속성 식별
논리 설계	개념 모델을 논리적으로 구체화	기본키(Primary Key) 설정 일반 속성 정의 엔터티 간 관계 설정 (1:1, 1:N, N:M 등) 정규화(Normalization) 수행 (중복 제거, 이상 현상 방지)
물리 설계	실제 DBMS에서 구현 가능한 구조로 변환	테이블(Table) 생성  자료형(Data Type) 지정 인덱스, 제약조건 등 최적화 요소 설계

 

 

 

이제 모델링을 직접 해보자!

모델링은 이클립스에 exerd를 추가해서 실습해보았다. 

http://exerd.com/update/exerd/3.x

 

개념 모델링

엔터티를 추출하였다. 

 

논리 모델링

우선 속성을 추출하였다. 

 

신체정보 테이블은?

회사입장에서 봤을 때 사원의 구체적인 정보를 별도로 분리시킨 테이블. 사원의 정보가 많을 때 일반적으로 별도의 테이블을 둔다.

 

하나의 부서에는 여러명의 사원으로 구성되고, 특정 사원은 반드시 하나의 부서에 소속되어진다. 

사원과 부서는 비식별 관계이다. 이유는, 사원에 입력된 부서 번호를 가지고 사원을 식별할 수 없는 관계이기 때문이다. 

그래서 점선으로 관계를 잇는다. 부모에서 자식으로 이으면 된다. 

 

특성을 클릭해서 관계를 설정한다. 

 

자식 테이블과 부모 테이블의 차수를 설정한다. 

 

이제 신체 정보 테이블도 연결시켜보자.

사원과 신체정보는 식별 관계이다. 신체 정보 테이블에 입력된 사원번호를 가지고 신체 정보를 식별할 수 있기 때문이다.

이럴 때엔 외래키가 기본 속성 영역으로 들어오는 것을 볼 수 있다. 

 

위와 같이 특성을 선택해서 관계 차수를 1대 1로 바꾼다. 

 

바탕을 우클릭하여 png로 저장하면 이렇게 최종 결과가 나온다. 

개념 모델링, 논리 모델링을 진행해보았다. 

 

 

 

 

다음으로 다대다일때 어떻게 관계를 해소시키는 지 알아보자

개념 설계 단계에서는 이 관계가 단순히 고객 ↔ 상품으로 표현되지만,

논리 설계 단계에 이르면 이 구조는

정규화를 위해 반드시 중간에 관계(연결) 테이블을 추가해 다대다 관계를 해결해야 한다는 점을 알 수 있다.

 

그러므로 관계를 해소시킬 '구입' 엔터티를 하나 추가해보자. 

 

 

여기서 구입코드 기본키를 추가해준다.

구입코드 PK 이유설명

✅ 유일성 확보	동일 고객-상품 복수 구입을 구분 가능
✅ 확장성	다른 테이블과 연결이 쉬움
✅ 성능	단일 PK가 인덱싱, 조인 효율적
✅ 실무 적용	대부분의 실무 시스템은 단일 ID(PK)를 기준으로 설계

 

 

 

java코드를 생각해보고 짜보자.

Order와 Customer의 관계를 나타낸 코드를 유의해서 보자.

public class Customer {
    private int num;               // 고객 번호
    private String name;          // 고객 이름
    private String address;       // 고객 주소

    private ArrayList<Order> orders;  // 고객이 한 주문 목록

    public Customer(int num, String name, String address) {
        this.num = num;
        this.name = name;
        this.address = address;
        this.orders = new ArrayList<>();
    }

    public void addOrder(Order order) {
        orders.add(order);
    }

    // getter, setter 등 필요한 메서드 추가 가능
}

 

 

정규화

현재처럼 회사 정보와 사원 정보를 하나의 테이블에 저장하면 다음과 같은 문제점이 발생합니다

 

1. 데이터 중복

• 같은 회사에 소속된 여러 사원이 등록될 때마다, 동일한 회사 정보를 반복해서 저장해야 함.

2. 수정 시 비효율성

• 회사 주소처럼 회사 공통 정보가 변경되면, 그 회사 소속 사원 모두의 레코드를 일일이 업데이트해야 함.

3. 데이터 불일치 위험

• 한 레코드는 삼성전자 / 수원시
• 다른 레코드는 삼성전자 / 서울시라고 저장되면 ,→ 데이터의 **정합성(Correctness)**이 무너짐.

 

정규화를 하면 회사 정보를 한 곳에서만 관리할 수 있게 되고, 사원이 몇 명이든 중복 없이 깔끔하게 관리할 수 있다.

 

제 1, 2, 3차 정규형을 정리해보았다. 

📍정규화의 개념

정규화의 목표는 테이블 간에 중복된 테이터를 허용하지 않게 만드는 것이다.

중복된 데이터를 허용하지 않음으로서 무결성, 일관성, 유연성을 향상 시킬 수 있고,

DB의 저장 용량도 줄일 수 있다.

모든 정규화는 이전 단계의 정규형을 만족해야 그 다음 단계의 정규화를 진행 할 수 있다.

 

✏️ 제 1 정규화

수강신청 TABLE

학생ID	수강자	수강과목
1	박가현	AI
2	이태란	JAVA,AI
3	곽채연	Java Script

AI라는 과목이 박00 학생한테도 있고, 이00 학생한테도 중복으로 들어가 있다.

💣 문제

1. 데이터 중복 발생
   • 같은 이름을 두 번 이상 저장하니 저장 공간이 낭비된다.
   • 이름을 고치거나 삭제할 때 모든 위치에서 일일이 찾아서 수정 삭제 수행
   • AI라는 과목이 박가현 수강자에게도, 이태란 수강자에게도 반복 저장된다.
2. 데이터 무결성 저해
   • 무결성이란? AI라는 정보가 언제나 정확하고 일관되게 관리되는 상태를 말한다.
   문제 상황
   • 박가현 수강과목에는 AI라고 쓰고 이태란 과목에는 인공지능이라고 입력할 수가 있다. 두 위치가 서로 다르면 ‘AI’가 무엇인지? 어떤 철자가 정확한 지 알 수 없게 된다.
3. 삽입/수정/삭제 이상
   • 수정(갱신)이상
   • → 만약, AI라는 이름을 인공지능으로 바꾸고 싶어서 수정할 때, 두 개 이상의 칸을 모두 찾아가서 일일이 고쳐야 한다.
   • 삭제 이상
   • → 이태란이 JAVA 과목을 삭제하고 싶은데, DELETE를 수행하면 모든 과목이 사라지게 된다.

제 1 정규화 적용 전

학생ID	수강자	수강과목
1	박가현	AI
2	이태란	JAVA,AI
3	곽채연	Java Script

🌟 제 1 정규화 적용 후

학생ID	수강자	수강과목
1	박가현	AI
2	이태란	JAVA
3	곽채연	Java Script
2	이태란	AI

✏️ 제 2 정규화

제1 정규화를 만족하면서, 엔터티의 일반 속성이 기본키 전체에 종속이어야 한다.

즉, 부분종속이 존재하면 안 된다.

부분 종속 (Partial Dependency)이란?

복합 기본키를 가진 테이블에서, 어떤 속성이 그 전체 기본키가 아닌 일부에만 종속되는 것

 

회원 주문 TABLE

회원 ID	회원이름	회원주소	상품코드	상품명	가격
101	곽채연	방배동	S100	노트북	350
102	이태란	경기도	B100	핸드폰	150
103	박가현	혜화동	S100	노트북	350
101	곽채연	방배동	M200	젤리	10

여기서 기본키가 (회원ID, 상품코드)라고 가정하면

• 상품명과 가격은 사실 상품코드에만 의존한다
• 즉, **전체 키(회원ID + 상품코드)**가 아니라, 그 일부인 상품코드에만 종속된 상태

💣  문제

1. 데이터 중복 발생
   • 같은 회원 정보(곽채연, 방배동)가 여러 행에 반복된다.
   • 동일 상품 정보(노트북, 350)도 반복 저장된다.
   • → 중복이 많아질수록 저장 공간 낭비가 커지고 수정 작업 시 관리 포인트가 증가하게 된다.
2. 데이터 무결성 저해
   • 예를 들어, ‘노트북’의 가격이 변경 되었을 때 모든 행의 가격을 일일이 찾아서 수정해야 한다.
   • 한 행에선 350원, 다른 행에선 실수로 360원으로 쓰일 수 있다.
   • → 데이터 일관성 무너진다.
3. 삽입/수정/삭제 이상
   • 수정(갱신) 이상
   • → 한 회원의 주소가 변경되면, 그 회원이 주문한 모든 행의 주소를 전부 수정해야 한다.
   • 삭제 이상
   • → 회원이 마지막으로 주문한 상품을 삭제하면, 회원 정보까지 같이 사라질 수 있다.
   • 삽입 이상
   • → 상품 정보만 미리 등록하고 싶은데, 주문이 없으면 상품을 테이블에 추가할 수 없다.

🌟 제 2차 정규화 적용 후

구매 TABLE

회원 ID	회원이름	회원주소
101	곽채연	방배동
102	이태란	경기도
103	박가현	혜화동

 

상품TABLE

상품코드	상품명	가격
S100	노트북	350
B100	핸드폰	150
M200	젤리	10

 

구매TABLE

회원ID	상품코드
101	S100
102	B100
103	S100
101	M200

✏️ 제 3 정규화

제2 정규화를 완료한 테이블에서, 이행적 함수 종속성을 제거하는 것

이행적 함수 종속이란,

A → B 종속 B → C 종속일때, A → C 종속인 관계

 

수강신청 TABLE

학생ID	학생이름	강의ID	강의명	교수ID	교수이름
S001	이태란	L001	수학	P001	이은택
S001	이태란	L002	영어	P002	김진희
S002	곽채연	L001	수학	P001	이은택
S003	박가현	L003	과학	P003	박현정

이행적 함수 종속

• 학생ID → 학생이름
• 강의ID → 교수ID
• 교수ID → 교수이름
• 따라서 강의ID → 교수이름이 이행적 종속

💣  문제

1. 데이터 중복 발생

• 예시 테이블에서 강의ID가 같으면 교수ID, 교수이름도 항상 같음 → 같은 정보가 반복 저장된다.
• 김영희 외 다른 학생들이 같은 강의를 들을 경우, 이은택 교수의 이름이 계속 반복된다.
• 중복 저장은 저장 공간 낭비를 유발하고, 수정 시 실수 확률을 높인다.

2. 데이터 무결성 저해

• 누군가는 P001 교수 이름을 이은택,→ 같은 교수인데 이름이 다르게 들어간다.
• 이로 인해 일관성이 깨지고, 무결성(정확성)이 보장되지 않는다.
• 다른 레코드에는 실수로 이윤택, 은택 이로 잘못 기입할 수 있다.

3. 삽입/수정/삭제 이상

갱신 이상 (Update Anomaly)

• 교수 이름을 변경할 일이 생기면 → 해당 교수의 강의를 듣는 모든 학생 레코드의 이름을 전부 수정해야 한다.

삽입 이상 (Insert Anomaly)

• 아직 수강한 학생이 없더라도 교수 정보를 미리 저장해두고 싶으면 → 이 구조에서는 불가능

삭제 이상 (Delete Anomaly)

• 만약 김영희 학생이 수강 취소하여 삭제되면 → 해당 강의 및 교수 정보도 같이 삭제될 위험 있다.

🌟 제 3차 정규화 적용 후

학생 TABLE

학생ID	학생이름
S001	이태란
S002	곽채연
S003	박가현

강의 TABLE

강의ID	강의명	교수ID
L001	수학	P001
L002	영어	P002
L003	과학	P003

 

수강신청 TABLE

학생ID	강의ID
S001	L001
S001	L002
S002	L001
S003	L003

교수 TABLE

교수ID	교수이름
P001	이은택
P002	김진희
P003	박현정