논리 데이터 모델링과 품질 검증

논리 데이터 모델링은 개념 설계에서 그린 E-R 다이어그램을 표와 열, 기본키·외래키로 바꾸고 정규화하는 단계입니다. 바꾼 모델은 정확성·완전성·준거성·최신성·일관성·활용성 여섯 기준으로 검증합니다.

개념 설계의 결과물은 사각형과 마름모로 그린 E-R 다이어그램입니다. 그림은 사람이 보기에는 좋지만 DBMS에 그대로 넣을 수 없습니다. 표 몇 개를 만들고, 어느 열을 기본키로 하고, 어느 열로 표를 이을지 정해야 CREATE TABLE을 쓸 수 있습니다. 이 일을 하는 단계가 논리 설계입니다. 설계 단계 전체는 데이터베이스 설계 단계에서 봤습니다. 바꾸는 길이 하나가 아니라서 관계의 형태마다 규칙이 있고, 다 바꾼 뒤에는 빠진 것과 어긋난 것을 찾는 검증을 합니다.

그림은 왼쪽에 E-R 다이어그램, 오른쪽에 바뀐 표를 나란히 둡니다. 기호는 개체-관계(E-R) 모델 레슨과 같은 피터 첸 표기입니다. 아래 네 절이 그림의 단계 순서를 그대로 따라갑니다.

1개체가 표가 되고 속성이 열이 된다

개체는 표 하나가 됩니다. 학생 사각형이 학생 표가 되고, 타원으로 그린 학번·이름·학년이 열이 됩니다. 밑줄 친 키 속성 학번은 기본키가 됩니다.

2관계는 모양마다 바꾸는 길이 다르다

한쪽만 여럿인 1:N 관계는 여럿인 쪽 표에 상대의 기본키를 외래키로 둡니다. 학과 하나에 학생이 여럿이므로 학생 표에 학과코드 열이 생깁니다. 1 쪽인 학과 표에는 아무 열도 더하지 않습니다. 학생 쪽에 학과코드를 한 번 적으면 되는데, 학과 쪽에 학생 번호를 적으려면 한 칸에 여러 값을 넣어야 해서 1정규형을 깨기 때문입니다.

양쪽 다 여럿인 M:N 관계는 관계 자체가 표 하나가 됩니다. 수강 마름모가 수강 표가 되고, 양쪽의 기본키인 학번과 과목코드를 받아 둘을 합쳐 기본키로 씁니다. 관계에 달려 있던 점수 타원도 이 표의 열이 됩니다. 받은 두 열은 각각 학생 표와 과목 표를 가리키는 외래키이기도 합니다.

양쪽 다 하나인 1:1 관계는 두 길이 있습니다. 어느 한쪽 표에 상대의 기본키를 외래키로 두거나, 두 표를 아예 하나로 합칩니다. 자주 함께 읽으면 합치고, 따로 읽는 일이 많으면 나눠 둡니다.

3다중값 속성과 약한 개체는 표를 하나 더 만든다

값이 여럿일 수 있는 다중값 속성은 표를 하나 더 만듭니다. 전화번호를 집 전화와 휴대폰 둘로 가질 수 있으면 학생전화(학번, 전화번호) 표를 만들고 두 열을 합쳐 기본키로 씁니다. 자기 속성만으로 하나하나를 가려내지 못하는 약한 개체는 기대는 개체의 기본키를 포함해 기본키를 만듭니다. 분반은 과목코드와 분반번호를 합쳐 기본키로 씁니다.

4바꾼 모델을 여섯 기준으로 검증한다

바꾼 스키마를 정규화한 뒤 검증합니다. 논리 데이터 모델 품질 검증 기준은 정확성·완전성·준거성·최신성·일관성·활용성 여섯입니다. 업무 규칙을 틀림없이 담았는지(정확성), 요구한 데이터가 빠지지 않았는지(완전성), 표준과 규정을 지켰는지(준거성), 바뀐 업무가 반영되었는지(최신성), 같은 데이터를 어디서나 같게 적었는지(일관성), 쓰는 사람이 쓰기에 알맞은지(활용성)를 봅니다.

예제

E-R의 모습마다 어떻게 바뀌나

이 과목 예제 데이터의 네 표가 바로 변환 결과입니다. 학생전화와 분반은 규칙을 보이려고 든 표라 예제 데이터에는 없습니다.

• M:N 관계에서 나온 표는 양쪽을 잇는 일만 하는 표라 교차 표라고도 부릅니다.
• 1:N 관계의 외래키는 NULL을 허용할 수 있습니다. 학생 표의 학과코드가 그렇습니다. 그래서 학과가 정해지지 않은 강도현(107)도 학생 표에 들어갑니다.

합친 기본키가 지키는 것

M:N 관계를 표로 바꿀 때 양쪽 키를 합쳐 기본키로 둔 것은 '한 학생이 한 과목을 한 번만 듣는다'는 업무 규칙을 담은 것입니다. 이미 있는 짝을 한 번 더 넣어 봅니다. 김민수(101)는 자료구조(DS)를 이미 듣고 있어서, 같은 짝을 다시 넣으면 값이 겹친다는 오류(ORA-00001)를 내고 들어가지 않습니다. 오류 메시지에 제약 조건 이름 수강_PK와 겹친 값(학번 101, 과목코드 DS)이 나옵니다. 합친 기본키가 한 쌍을 한 번만 받으므로, 같은 과목을 두 번 듣는 일을 담아야 하는 업무라면 모델을 다시 봐야 합니다.

INSERT INTO 수강
VALUES (101, 'DS', 90);

ORA-00001: unique
constraint
(SQLD.수강_PK) violated on
table SQLD.수강 columns
(학번, 과목코드)
ORA-03301: (ORA-00001
details) row with column
values
(학번:101, 과목코드:'DS')
already exists

외래키가 지키는 것

1:N과 M:N 변환으로 생긴 외래키는 가리키는 표에 없는 값을 막습니다. 학생 표에 없는 학번 108로 수강 행을 넣으면 가리키는 쪽에 그 값이 없다는 오류(ORA-02291)로 막힙니다. 오류 메시지의 제약 조건 이름은 수강_학생_FK이고, 'parent key not found'가 그 뜻입니다.

INSERT INTO 수강
VALUES (108, 'DS', 90);

E-R에서 선으로만 그렸던 관계가 논리 모델에서는 이렇게 지켜집니다.

품질 검증 여섯 기준

아래 표를 보기 전에, 다음 세 지적이 어느 기준에 걸린 것인지 먼저 골라 보세요.

• (가) 수강 신청 화면에 '신청 취소 일시'를 보여 달라는 요구가 있었는데 모델에 그 열이 없습니다.
• (나) 지난달에 두 학과가 하나로 합쳐졌는데 모델에는 아직 옛 학과가 둘로 남아 있습니다.
• (다) 성적 조회 화면 하나를 그리려고 표 여섯을 이어야 해서 쓰는 사람이 불편합니다.

(가)는 요구한 것이 모델에 없으니 완전성입니다. (나)는 모델이 지금 업무를 따라오지 못했으니 최신성입니다. (다)는 틀린 것은 없지만 쓰기에 불편하니 활용성입니다.

시험 포인트

• 필기: 논리 설계는 E-R 다이어그램을 릴레이션 스키마로 바꾸고 정규화하는 단계입니다. 개체는 표가 되고, 속성은 열, 키 속성은 기본키가 됩니다. 1:N은 N 쪽 표에 외래키를 두고, M:N은 관계가 표가 되어 양쪽 기본키를 합쳐 기본키로 씁니다. 다중값 속성은 표를 하나 더 만들고, 약한 개체는 기대는 개체의 기본키를 포함해 기본키를 만듭니다. 품질 검증 기준은 정확성·완전성·준거성·최신성·일관성·활용성 여섯입니다. E-R을 주고 표와 기본키를 고르는 문제, 기준 이름과 뜻을 짝짓는 문제로 나옵니다.
• 실기: 품질 검증 기준 이름을 한글로 씁니다 — 정확성·완전성·준거성·최신성·일관성·활용성. E-R 다이어그램을 보고 표와 기본키·외래키를 씁니다.

확인 문제

1. 한 책을 작가 여럿이 함께 쓰고, 한 작가도 책 여럿을 씁니다. 작가(작가번호, 작가명)와 책(책번호, 제목)을 잇는 '집필' 관계에는 그 작가가 맡은 장을 적는 담당장 속성이 있습니다. 이 M:N 관계를 표로 바꾼 결과로 알맞은 것을 고르세요.

• ① 작가 표에 책번호 열을 외래키로 더한다
• ② 책 표에 작가번호 열을 외래키로 더한다
• ③ 집필(작가번호, 책번호, 담당장) 표를 만들고 '작가번호 + 책번호'를 기본키로 한다
• ④ 작가책(작가번호, 작가명, 책번호, 제목, 담당장) 한 표로 합친다

2. 논리 데이터 모델을 검증하다가 나온 지적 넷입니다. (가)~(라)가 어느 기준에 걸린 것인지 기준 이름을 한글로 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 학사 규정은 한 학생이 같은 과목을 다시 들을 수 있게 하는데, 모델은 한 쌍을 한 번만 받게 만들어 두 번 들은 기록을 담지 못합니다.
• (나) 성적표에 등급을 함께 보여 달라는 요구가 있었는데 모델에 등급 열이 없습니다.
• (다) 개인정보 보호 규정은 주민등록번호를 모아 두지 못하게 하는데, 모델에 주민번호 열이 있습니다.
• (라) 학기마다 바뀌는 학점 기준이 지난 학기 값으로 남아 있습니다.

3. 다음 (가)·(나)에 맞는 기준 이름을 한글로 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 같은 고객 등급을 한 표에서는 '등급', 다른 표에서는 '고객레벨'이라는 이름으로 두었습니다. 자료형도 한쪽은 문자, 한쪽은 숫자입니다.
• (나) 틀린 곳은 없지만, 가장 자주 쓰는 목록 화면이 표 여섯을 이어야 열려 느리고 불편합니다.

4. 예제의 INSERT에서 학번만 106으로 바꿔 돌렸습니다. 정하윤(106)은 학생 표에 있지만 수강 기록은 없습니다. 이 문장은 어떻게 될까요?

INSERT INTO 수강
VALUES (106, 'DS', 90);
SELECT COUNT(*) AS 행수
FROM 수강;