9주차 배운점 느낀점 - 이상현상, 함수 종속성, 정규화

9주차 시작

 

트랜잭션같은 설명 자소서에 반드시 말해야한다.
"100%들어가 ! 이런내용이 없으면 자소서는 매력적이지 않아"
그외에도 이상현상, 정규화 내용을 백앤드개발자 지원할때 이 내용 굉장히 중요하다.

 

이상현상 종류 (삭제,삽입,수정)와 그를 방지하기 위해 간단한 테이블분리를 배웠다.
1,2,3 정규화에 대해서 난잡한 데이터 테이블을 중복속성제거, 부분함수적종속성 제거, 이행함수적종속성 제거를 해주는 실습을 했다.

 

배운점

 

이상현상

 

데이터 조작 작업시 이상현상이 발생할 수 있다.
이상현상은 데이터의 일관성을 손상시켜 데이터의 무결성을 깨뜨린다.
이 문제를 해결하려면, 속성간의 함수 종속성을 파악하여 테이블을 규칙에 의해 분해하는데
이 과정을 정규화라고 한다.
(A->B a는 b의 결정자 b는 a에 종속되어 있다.)

 

정규화란 잘못 설계된 테이블을 수정하여 데이터의 일관성과 무결성을 회복하는 프로세스 (이 부분 어필 할 것 -> 친구 정리좀 했는데?)
중복성과 종속성을 제거 (최소화) 데이터베이스의 효율성과 유지보수의 구조를 개선하고 데이터베이스의 성능 향상을 최적화하는데 의미가 있다.
(이런내용, 정규화를 하는 의미를 자소서에 적어라)

 

 

삭제이상(deletion anomly) : 투플 삭제 시 같이 저장된 다른 정보까지 연쇄적으로 삭제되는 현상
→ 연쇄삭제(triggered deletion) 문제 발생

 

수강만 취소할려고했는데 김연경 정보까지 싹다 없어짐,-> 학생정보랑, 수강정보랑 분리해야겠다! , 연쇄삭제현상

 

delete from 학생수강 where 강좌이름 = '스포츠경영학';

 

삽입이상(insertion anomly) : 투플 삽입 시 특정 속성에 해당하는 값이 없어 NULL 값을 입력해야하는 현상 → NULL 값 문제 발생

 

안정환 학생이 체육학과에 입학했다. 학생번호, 학생이름, 학과, 주소 / 신입이라 강좌이름과 강의실은 null

 

insert into 학생수강 values(600,'안정환','체육학과','대한민국 전북',null,null);

 

학생수강 조회하는데 몇명의 학생이 강좌이름으로 수강하는지 조회 ? -> null체크안됨, 삽입이상

 

select 강좌이름, count(*) from 학생수강 group by 강좌이름;
select count(*) from 학생수강; 

 

현재 학생은 모두 6명이지만, 안정환 학생은 null로 제외됨(숫자 속성에서 null은 0이 아니므로 count에서 제외)

 

수정이상(update anomly) : 투플 수정 시 중복된 데이터의 일부만 수정되어 데이터의 불일치 문제가 일어나는 현상 → 불일치(inconsistency) 문제 발생

update 학생수강 set 주소 = '대한민국 서울' where 학생이름 = '박지성';
update 학생수강 set 주소 = '대한민국 서울' where 학생번호 = '박지성';
update 학생수강 set 주소 = '대한민국 서울' where 강좌이름 = '데이터베이스'; 

 

수정이상 발생 강좌이름 '자료구조'의 박지성과 불일치 발생

 

==> 모호함들 정규화 처리시켜줘야한다 !!

학생테이블과 강의테이블로 나누고 양쪽 테이블의 pk를 외래키로 가지는 수강신청테이블을 만들었다.

CREATE TABLE 학생 (
    학생번호 INT PRIMARY KEY,
    학생이름 VARCHAR(45),
    학과 VARCHAR(45),
    주소 VARCHAR(45)
);
CREATE TABLE 강의 (
    강좌번호 INT AUTO_INCREMENT PRIMARY KEY,
    강좌이름 VARCHAR(45),
    강의실 VARCHAR(45)
);
CREATE TABLE 수강신청(
    학생번호 INT,
    강좌번호 INT,
    FOREIGN KEY (학생번호) REFERENCES 학생(학생번호),
    FOREIGN KEY (강좌번호) REFERENCES 수강신청(강좌번호),
    PRIMARY KEY (학생번호, 강좌번호)
);

 

계절학기 테이블 예시

DROP TABLE IF EXISTS Summer; /* 기존 테이블이 있으면 삭제 */
CREATE TABLE Summer
( sid INTEGER,
class VARCHAR(20),
price INTEGER
);
INSERT INTO Summer VALUES (100, 'FORTRAN', 20000);
INSERT INTO Summer VALUES (150, 'PASCAL', 15000);
INSERT INTO Summer VALUES (200, 'C', 10000);
INSERT INTO Summer VALUES (250, 'FORTRAN', 20000);
/* 생성된 Summer 테이블 확인 */
SELECT *
FROM Summer;

 

삭제 이상

 

수강신청만 취소하고싶지만, 튜플 전체삭제

/* 200번 학생의 수강신청 취소 */
DELETE FROM Summer
WHERE sid=200;

 

조회 불가능

/* C 강좌 수강료 다시 조회 */ -- => C 수강료 조회 불가능!!
SELECT price "C 수강료"
FROM Summer
WHERE class='C';

 

삽입 이상

 

NULL을 삽입해야 한다. NULL 값은 문제가 있을 수 있다.

INSERT INTO Summer VALUES (NULL, 'JAVA', 25000);

 

결과가 다 다름,

SELECT COUNT(*) "수강인원"   -- 5명
FROM Summer;
SELECT COUNT(sid) "수강인원"  -- 4명
FROM Summer;
SELECT count(*) "수강인원"   -- 5명
FROM Summer
WHERE sid IS NOT NULL;

 

수정 이상

FORTRAN 수강료를 바꾸려고한다. FORTRAN은 두개지만 sid 100인것만 바뀜

UPDATE Summer
SET price=15000
WHERE class='FORTRAN' AND sid=100;

 

분리

 

학생은 summer table에서 선택해서 수강할 수 있다.

 

스튜던트 아이디(sid)와 class 테이블, class와 price 테이블로 나누고 중간에 join한 테이블을 만들어 주었다.

create table summerprice(
	class varchar(15) primary key,
    price int
);

insert into summerprice values ('FORTRAN',20000);
insert into summerprice values ('PASCAL',15000);
insert into summerprice values ('C',10000);

create table summerenroll(
	sid int,
    class varchar(15)
    );
    
insert into summerenroll values (100,'FORTRAN');
insert into summerenroll values (150,'PASCAL');
insert into summerenroll values (200,'C');
insert into summerenroll values (250,'FORTRAN');

SELECT sid, class, price
from summerprice p natural join summerenroll e

 

함수 종속성

 

학생수강성적 릴레이션의 각 속성 사이에는 의존성이 존재함
어떤 속성 A의 값을 알면 다른 속성 B의 값이 유일하게 정해지는 의존 관계를 ‘속성 B는 속성 A에 종속한다(dependent)’ 혹은 ‘속성 A는 속성 B를 결정한다(determine)’라고 함
‘A → B’로 표기하며, A를 B의 결정자라고 함

 

릴레이션(수학=구조를 중요시) = 테이블(실무=>데이터 강조)
함수 종속성 릴레이션 구조에 관한 이론적 내용이 포함
릴레이션 튜플(row), 속성(column)

 

강좌이름으로 강의실을 알 수 있지만 강좌이름으로 학생이름을 알 수 없다.

 

학생수강성적 릴레이션에서 종속관계에 있는 예
(학생번호를 통해 학생이름을 확정할 수 있다.)
학생번호 → 학생이름
학생번호 → 주소
강좌이름 → 강의실
학과 → 학과사무실

 

종속하지 않는 예
(학생이름으로 강좌이름을 결정할 수 없다.)
학생이름 → 강좌이름
학과 → 학생번호

 

종속하는 것처럼 보이지만 주의 깊게 보면 그렇지 않은 예
(교양 같은거 교차 수강할 수 있다., 학생번호면 학과를 정할 수 있다.)
학생이름 → 학과

 

함수 종속성(FD, Functional Dependency)

릴레이션 R과 R에 속하는 속성의 집합 X, Y가 있을 때, X 각각의 값이 Y의 값 한 개와 대응이 될 때 ‘X는 Y를 함수적으로 결정한다’라고 하고 X→Y로 표기함. 이때 X를 결정자(determinant)라고 하고, Y를 종속 속성 (dependent attribute)이라고 함. 함수 종속성은 보통 릴레이션 설계 때 속성의 의미로부터 정해짐.

 

함수 종속성 다이어그램(functional dependency diagram)은 함수 종속성을 나타내는 표기법

 

릴레이션의 속성 : 직사각형
속성 간의 함수 종속성 : 화살표
복합 속성 : 직사각형으로 묶어서 그림

 

화살표 따라가서 학생번호를 통해 학과 사무실도 결정가능
종속성관계를 그림으로 표현 , 증가 규칙, 이행규칙.. 등 적용하여 설계할 것

함수 종속성과 기본키

 

릴레이션의 함수 종속성을 파악하기 위해서는 우선 기본키를 찾아야 함
기본키가 함수 종속성에서 어떤 역할을 하는지 알면 이상현상을 제거하는 정규화
과정을 쉽게 이해할 수 있음

 

함수 종속성과 기본키
릴레이션 R(K, A1, A2, A3, ..., An)에서 K가 기본키면, K → R이 성립.
즉 기본키는 릴레이션의 모든 속성에 대해 결정자(determinant)임.

 

이상현상과 결정자

이상현상은 한 개의 릴레이션에 두 개 이상의 정보가 포함되어 있을 때 나타남
기본키가 아니면서 결정자인 속성이 있을 때 발생함
학생수강성적 릴레이션의 경우 학생 정보(학생번호, 학생이름, 주소, 학과)와 강좌
정보(강좌이름, 강의실)가 한 릴레이션에 포함되어서 이상현상이 나타남
(학과, 학생번호, 강좌이름은 기본키가 아니면서 결정자인 예이다)

->

이상현상을 없애려면 릴레이션을 분해한다.
(학과, 학과사무실) 속성을 학생수강성적 릴레이션에서 분리하는 예 ++

 

분해

학생수강성적 릴레이션에서 부분 릴레이션을 분해하기
분해할 때 부분 릴레이션의 결정자는 원래 릴레이션에 남겨두어야 함. 그래야 분해된 부분
릴레이션이 원래 릴레이션과 관계를 형성할 수 있음

 

[1단계] 학생수강성적 릴레이션에서 (강좌이름, 강의실)을 분리
학생수강성적1(학생번호, 학생이름, 학과, 주소, 강좌이름, 성적, 학과사무실)
강의실(강좌이름, 강의실)

 

[2단계] 학생수강성적1 릴레이션에서 (학생번호, 강좌이름, 성적)을 분리
학생학과(학생번호, 학생이름, 학과, 주소, 학과사무실)
학생성적(학생번호, 강좌이름, 성적)
강의실(강좌이름, 강의실)

 

[3단계] 학생학과 릴레이션에서 (학과, 학과사무실)을 분리
학생(학생번호, 학생이름, 학과, 주소)
학과(학과, 학과사무실)
학생성적(학생번호, 강좌이름, 성적)
강의실(강좌이름, 강의실)

 

결정자는 원래 릴레이션에 남겨둠

문제

1. 5-> 9, 5->2 가 나오기때문에 결과가 다르므로 결정짓지못함
2. 같은값을 매핑하고 있음 , 9-> 6 ( o)
3. (9,6) 의 매핑이 5, 7 로 다르다
4. (a,b)가 다 다르므로 결과가 어떻든 가능 (c 의 6같아도됨) ( o)

함수는 한값만 가진다. !!
3번은 b,c 9,6으로 다른 두개의 결과값이 나온다.
4번에서 a,b 가 다 다르다. 결과가 같아도 된다. 한 값이 다 나온 것임
=> 결정자가 식별가능하면 됨 , 만약 같은게 똑같은 결정자가 있으면 결과값 같으면 함수종속!

동아리번호 -> 동아리명
동아리번호 -> 동아리개설일
{동아리 번호, 학번} -> 동아리 가입일

학번->이름
학번->학과번호
학과번호->학과명

 

완전함수적종속성:
기본키가 복합키인 경우에 속성집합을 완전함수적종속성 관계라고한다.
동아리가입일 결정하기 위해 동아리번호, 학번 필요하다.

 

부분함수적종속성
기본키가 아닌속성들이 , 두개중에 하나사용해서라도 종속관계가 이루어 지는것

이행함수적종속성
세가지 속성에서 연쇄적으로 종속성이 전파되서 결정이 될 수 있는 형태

 

정규화

이상현상이 발생하는 릴레이션을 분해하여 이상현상을 없애는 과정
이상현상이 있는 릴레이션은 이상현상을 일으키는 함수 종속성의 유형에 따라 등급을 구분 가능
릴레이션은 정규형 개념으로 구분하며, 정규형이 높을수록 이상현상은 줄어듦

 

제 1정규형
(1NF) 테이블의 각 속성 값은 반복그룹(Repeating group)이 없는 원자값으로만 구성되도록 중복되는 속성이나 속성그룹을 별도의 테이블로 분리한다.

 

제 2정규형
(2NF) 1NF를 만족하면서 부분 함수적 종속성을 제거해야 한다. 기본키 속성에 완전 함수적 종속성을 가지도록 테이블을 분할하거나 기본키를 변경하거나 속성간의 종속성을 조정

 

제 3정규형
(3NF) 2NF를 만족하면서 이행 함수적 종속성을 제거해야 한다. 테이블의 모든 비(Non-key)속성은 기본키에만 종속되어야 하고, 비-키 속성간에는 종속성이 존재하지 않아야한다. 종속성이 존재한다면 테이블 분할해야한다.

보이스코드정규형(Boyce-codd NF,BCNF) - 제4정규형(4NF) - 제5정규형(5NF)

 

반정규화(Denormalization)
중복 데이터를 하나 이상의 테이블에 추가하는 데이터베이스 최적화 기술 이를 통해 관계형 데이터베이스에서 비용이 많이 드는 조인의 수를 줄일 수 있다. 반정규화는 정규화를 되돌린다거나 정규화되지 않음을 의미하는 것이 아니라, 정규화 후에 적용되는 최적화 기법

 

장점: 더 적은 수의 테이블을 사용하면서 조인 개수를 줄일 수 있고, 이로 인해 검색 쿼리가 간단해지고 빠른 일기 작업의 수행으로 검색성능이 업업!

 

단점: 중복저장으로 더 많은 스토리지가 요구된다. 또한 데이터 불일치가 발생할 수 있으며, 쓰기 작업에 추가적인 비용이 발생할 수 있다.

 

동아리가입학생학과 테이블 정규화

CREATE TABLE 동아리가입학생학과 (
   동아리번호 char(2) not null,
   동아리명 varchar(20) not null,
   동아리개설일 date,
   학번 int not null,
   이름 varchar(20) not null,
   동아리가입일 date,
   학과번호 char(2) not null,
   학과명 varchar(20) not null,
   primary key (동아리번호,학번)
);
   
insert into 동아리가입학생학과 values ('c1','세익스피어의 페르소나','2000-02-01',231001,'최문석','2023-03-01','D1','화학공학과');
insert into 동아리가입학생학과 values ('c2','게임좋아','2000-02-01',231002,'백종훈','2023-03-01','D4','경영학과');
insert into 동아리가입학생학과 values ('c3','풍물깡패','2010-05-04',232001,'정휘제','2023-03-01','D1','컴퓨공학과');
insert into 동아리가입학생학과 values ('c4','만화좋아','2020-03-01',231004,'김소진','2023-03-01','D2','컴퓨터공학과');
insert into 동아리가입학생학과 values ('c5','댄스배틀','2003-02-01',23109,'이다혜','2023-03-01','D4','경영학과');
insert into 동아리가입학생학과 values ('c4','만화좋아','2020-03-01',231108,'구희진','2023-03-01','D4','경영학과');
insert into 동아리가입학생학과 values ('c2','게임좋아','2000-02-01',231001,'김태진','2023-03-01','D1','화학공학과');

 

1NF 적용
학생과 동아리 분리, 원자성, 데이터 중복 방지
한 속성에 데이터가 "a,b,c" 처럼 여러개 들어가 있으면 안된다.

 

2NF 적용
부분함수적종속성을 제거하기위해 동아리, 학번(학생학과)을 따로 떼어 냈다.
위 그림에서 완전함수적종속성은 동아리 가입일 밖에없다.
동아리 테이블과 동아리가입학생학과 테이블로 나뉘었다.
학생학과를 떼어내고 원래 이어져있었던 동아리가입과 관계를 이어준다.

 

3NF 적용
이행함수적종속성을 제거해야한다. 학번 -> 학과번호 -> 학과명을 제거한다.
'학과'테이블로 따로 떼어 냈다.

 

 

 

회고

 

정처기 공부할때 각각 함수정종속을 글로만 외우고 정규화 과정도 각각 무엇을 해야하는지만 외웠다. 정확하게 머릿속으로 알지는 못했지만 문제는 풀렸었다.
오늘 수업을통해 잘못된 예시부터해서 정규화를 하나씩 거쳤다. 각각 어떤테이블을 왜 떼어 주었는지, 부분 완전 이행 함수적 종속이 무엇인지 표를 통해 잘 알았다. 직접예시를 실습할 수 있어서 궁금증이 풀린 수업이었다.