이상 현상

이상 현상(anomaly)은 한 표에 여러 주제를 담아 같은 값이 되풀이될 때, 행을 넣거나 지우거나 고치면서 생기는 문제입니다. 삽입 이상·삭제 이상·갱신 이상 세 가지가 있습니다.

SQLD를 먼저 공부했다면 이 단원 끝의 모의 문제부터 풀어 보고, 틀린 문제가 나온 레슨만 골라 봐도 됩니다.

수강 신청 한 건마다 학번·이름·학과명·과목코드·점수를 한 줄에 적는 표를 생각해 봅시다. 표 하나만 보면 되니 처음에는 편해 보입니다. 하지만 한 학생이 과목을 셋 들으면 그 학생의 이름과 학과명이 세 줄에 적힙니다. 같은 값이 여러 곳에 되풀이되는 것을 중복(redundancy)이라 합니다. 중복이 있는 표는 행을 넣고, 지우고, 고칠 때마다 뜻하지 않은 문제를 일으킵니다.

이 레슨은 행을 다루는 SQL 문 셋으로 이상 현상을 직접 일으켜 봅니다. INSERT는 표에 행을 넣고, DELETE는 조건에 맞는 행을 지우고, UPDATE는 조건에 맞는 행의 값을 고칩니다. 문장의 꼴은 13단원에서 자세히 배웁니다. 더 보기: DML: INSERT·UPDATE·DELETE

그림의 수강신청 표는 수강 기록 12행에 학생 이름과 학과명을 붙인 표입니다. 한 행은 학번과 과목코드를 함께 봐야 구별되므로 기본키는 '학번 + 과목코드'입니다. 학생·학과·수강 세 가지 사실이 한 줄에 섞여 있어서 김민수는 3번, '컴퓨터'는 7번 적혀 있습니다. 아래 네 절은 이 한 표에서 차례로 일어나는 일 셋과, 그 셋을 한꺼번에 만드는 원인입니다.

1삽입 이상 — 신청하지 않은 학생을 넣지 못한다

삽입 이상(insertion anomaly)은 넣고 싶은 정보만으로는 행을 넣을 수 없는 문제입니다. 정하윤(106)은 아직 수강 신청을 하지 않았습니다. 이 표에 넣으려면 과목코드를 비워야 하는데, 과목코드는 기본키의 일부라 비울 수 없습니다. 넣으려면 듣지도 않는 과목을 꾸며 함께 넣어야 합니다. 학생이 없는 경영 학과(BA)도 이 표에는 적을 자리가 없습니다.

2삭제 이상 — 수강을 지우자 학생까지 사라진다

삭제 이상(deletion anomaly)은 행을 지울 때 남겨야 할 정보까지 함께 사라지는 문제입니다. 강도현(107)은 네트워크(NW) 한 과목만 들었습니다. 수강을 취소해 그 행을 지우면 강도현이라는 학생이 있다는 사실까지 표에서 사라집니다.

3갱신 이상 — 한 줄만 고쳐 학과명이 둘이 된다

갱신 이상(update anomaly)은 같은 값이 적힌 행 가운데 일부만 고쳐 값이 서로 어긋나는 문제입니다. 컴퓨터 학과의 이름이 '컴퓨터공학'으로 바뀌면 '컴퓨터'가 적힌 7행을 모두 고쳐야 합니다. 한 행만 고치면 같은 학과의 이름이 둘이 됩니다.

4원인은 중복, 해법은 정규화

세 이상 현상은 모두 한 표에 여러 주제를 담아 같은 값이 되풀이되기 때문에 생깁니다. 학생·학과·수강을 각각 다른 표에 두면 이름과 학과명을 한 번씩만 적게 됩니다. 이렇게 주제마다 표를 나누는 일을 정규화(normalization)라 합니다. 어디를 기준으로 나누는지는 다음 레슨 함수 종속부터 봅니다.

예제

본문 세 절에서 말한 일이 정말 일어나는지 수강신청 표에 직접 넣고 지우고 고쳐 봅니다. 수강신청 표는 예제 데이터의 수강·학생·학과 세 표를 이어 만든 다섯 열 12행 표이고, 기본키는 '학번 + 과목코드'입니다. 표를 만들고 잇는 문법(CREATE TABLE·조인)은 13·14단원에서 배우니, 여기서는 어떤 일이 막히고 무엇이 남는지만 보면 됩니다.

CREATE TABLE 수강신청 AS
SELECT E.학번,
  S.이름,
  D.학과명,
  E.과목코드,
  E.점수
FROM 수강 E
  JOIN 학생 S
  ON S.학번 = E.학번
  LEFT JOIN 학과 D
  ON D.학과코드
    = S.학과코드;
ALTER TABLE 수강신청
  ADD PRIMARY KEY
    (학번, 과목코드);
SELECT *
FROM 수강신청
ORDER BY 학번, 과목코드;

삽입 이상 — 수강하지 않은 학생 넣기

정하윤(106)을 과목 없이 넣으려고 학번·이름·학과명만 적으면, 적지 않은 과목코드가 NULL이 되어 막힙니다. 기본키 열에는 NULL을 넣을 수 없다는 오류(ORA-01400)입니다.

CREATE TABLE 수강신청 AS
SELECT E.학번,
  S.이름,
  D.학과명,
  E.과목코드,
  E.점수
FROM 수강 E
  JOIN 학생 S
  ON S.학번 = E.학번
  LEFT JOIN 학과 D
  ON D.학과코드
    = S.학과코드;
ALTER TABLE 수강신청
  ADD PRIMARY KEY
    (학번, 과목코드);
INSERT INTO 수강신청
  (학번, 이름, 학과명)
VALUES (106, '정하윤',
    '전자');

삭제 이상 — 하나뿐인 수강 행 지우기

강도현(107)의 수강 행을 지운 뒤 이름이 강도현인 행을 세면 0행입니다. 지운 것은 수강 기록 하나인데, 강도현이라는 학생이 있다는 사실까지 이 표에서 사라졌습니다.

CREATE TABLE 수강신청 AS
SELECT E.학번,
  S.이름,
  D.학과명,
  E.과목코드,
  E.점수
FROM 수강 E
  JOIN 학생 S
  ON S.학번 = E.학번
  LEFT JOIN 학과 D
  ON D.학과코드
    = S.학과코드;
ALTER TABLE 수강신청
  ADD PRIMARY KEY
    (학번, 과목코드);
DELETE FROM 수강신청
WHERE 학번 = 107;
SELECT COUNT(*) AS 행수
FROM 수강신청
WHERE 이름 = '강도현';

갱신 이상 — 한 행만 고치기

학과 이름이 '컴퓨터공학'으로 바뀌었는데 김민수의 자료구조(DS) 행 하나만 고치면, 같은 김민수의 세 행에 학과명이 '컴퓨터공학'과 '컴퓨터' 둘로 남습니다.

CREATE TABLE 수강신청 AS
SELECT E.학번,
  S.이름,
  D.학과명,
  E.과목코드,
  E.점수
FROM 수강 E
  JOIN 학생 S
  ON S.학번 = E.학번
  LEFT JOIN 학과 D
  ON D.학과코드
    = S.학과코드;
ALTER TABLE 수강신청
  ADD PRIMARY KEY
    (학번, 과목코드);
UPDATE 수강신청
SET 학과명 = '컴퓨터공학'
WHERE 학번 = 101
  AND 과목코드 = 'DS';
SELECT 학번,
  과목코드,
  학과명
FROM 수강신청
WHERE 학번 = 101
ORDER BY 과목코드;

같은 김민수의 행인데 학과명이 둘입니다. 어긋나지 않게 고치려면 WHERE 학과명 = '컴퓨터'로 7행을 한꺼번에 고쳐야 합니다. 같은 사실을 일곱 곳에 적어 두는 한, 고칠 때마다 일곱 곳을 모두 찾아야 합니다.

시험 포인트

• 필기: 이상 현상은 삽입·삭제·갱신 세 가지입니다. 넣고 싶은 정보만으로는 넣을 수 없으면 삽입 이상, 지울 때 다른 정보까지 사라지면 삭제 이상, 일부 행만 고쳐 값이 어긋나면 갱신 이상입니다. 원인은 한 표에 여러 주제를 담아 생긴 중복이고, 없애는 방법은 정규화입니다. 사례를 보고 이름을 고르는 문제로 나옵니다.
• 실기: 삽입 이상(Insertion Anomaly)·삭제 이상(Deletion Anomaly)·갱신 이상(Update Anomaly).

확인 문제

1. 학생명부 표(학번, 이름, 학년, 학과명, 건물)는 학생 표와 학과 표를 한 표로 합친 것입니다. 기본키는 학번이고, 전자 학과 학생은 박서연·최민준·정하윤 세 행입니다. 다음 가운데 갱신 이상이 생기는 것을 고르세요.

• ① 학생이 아직 한 명도 없는 경영 학과를 이 표에 적으려 하니 학번 자리를 비울 수 없어 막힌다
• ② 정하윤의 학년을 3에서 4로 고친다
• ③ 전자 학과가 새 건물로 옮겨 세 행 가운데 박서연의 행만 건물을 고친다
• ④ 전자 학과 학생 셋이 모두 졸업해 세 행을 지우니 전자 학과가 공학관에 있다는 사실도 표에서 사라진다

2. 도서관이 대출 표(회원번호, 회원이름, 책번호, 제목, 대출일) 하나에 대출 기록을 적습니다. 기본키는 '회원번호 + 책번호'입니다. 다음 (가)~(다)에 맞는 이름을 한글 이름과 영문으로 함께 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 새로 들어온 책을 목록에 올리려는데 아직 빌린 회원이 없어 회원번호 자리를 비워야 해서 올리지 못합니다.
• (나) 이름이 바뀐 회원의 대출 행 여러 개 가운데 하나만 손대, 같은 회원의 이름이 행마다 다릅니다.
• (다) 한 회원만 빌렸던 책의 대출 기록을 치우니 그 책의 제목까지 표에서 찾을 수 없습니다.

3. 예제의 DELETE 조건을 과목코드 = 'DS'로 바꿔 자료구조 행을 모두 지웠습니다. 몇 행이 지워지고, 표에서 사라지는 사실은 무엇일까요?

CREATE TABLE 수강신청 AS
SELECT E.학번,
  S.이름,
  D.학과명,
  E.과목코드,
  E.점수
FROM 수강 E
  JOIN 학생 S
  ON S.학번 = E.학번
  LEFT JOIN 학과 D
  ON D.학과코드
    = S.학과코드;
ALTER TABLE 수강신청
  ADD PRIMARY KEY
    (학번, 과목코드);
DELETE FROM 수강신청
WHERE 과목코드 = 'DS';
SELECT DISTINCT 과목코드
FROM 수강신청
ORDER BY 과목코드;

더 자세히: SQLD의 정규화 레슨은 사원·부서·프로젝트 표로 같은 이상 현상과 정규화를 한 번에 봅니다.