[데이터베이스] 7. Relational Model 기본 개념

지난 글에서 정리한 것까지는 데이터베이스를 개념적으로 설계하는 방법을 다루어보았다. (conceptual design)

이제 이번 글부터는 ERD를 통해 개념적으로 설계한 데이터베이스를 논리적으로 설계해보고자 한다.

즉, SQL을 사용하여 엔티티 셋과 관계 셋을 실제 데이터베이스 테이블로 만들어보려고 한다.

그에 앞서 이번 글에서는 먼저 데이터베이스의 논리적 설계를 위한 Relational Model 의 기본 개념들을 간단하게 정리해본다.

 

 

Relation

관계형 데이터베이스 (relational database) 는 relation의 집합으로 이루어져 있다.

(그래서 영어 이름이 relational database 라고 생각한다. 한국어로는 relationship과 따로 구분짓지 않고 관계라고 하지만..)

 

relation은 2가지 부분으로 구성된다.

 

1. Instance : 행과 열로 이루어진 테이블 하나

2. Schema : 릴레이션의 이름, 각 컬럼의 이름과 타입을 정의한다.

 

즉, 릴레이션은 rows 또는 tuples 의 집합이라고 생각할 수 있다.

집합에는 중복된 원소가 들어가지 않으므로 각 튜플은 모두 distinct 해야 한다.

하지만 column 하나로 놓고 봤을 때는 각 컬럼에 들어가는 값이 반드시 distinct 할 필요는 없다.

(물론 PK 로 사용되는 단일 컬럼이 있다면 그 컬럼의 값은 distinct 해야한다.)

 

이때 테이블을 구성하는 행의 개수를 cardinality, 열의 개수를 degree 또는 arity 라고 한다.

 

 

주어진 학생 테이블은 3개의 행으로 구성되어 있으므로 cardinality = 3

5개의 열로 이루어져 있으므로 degree 또는 arity = 5 이다.

 

 

Relational Query Language

Relational Model 로 구성한 데이터에 대해 질의를 날릴 때 사용하는 언어를 말한다.

이 언어는 1970년대 IBM에 의해 표준이 만들어졌다.

 

SQL은 Turing Complete 하지 않다.

튜링 머신은 세상에 존재하는 모든 것을 계산할 수 있는 범용 목적의 가상 컴퓨팅 기계를 말하며, 이를 현대화한 것이 폰 노이만 아키텍처 구조의 현대 컴퓨터이다.

따라서 turing complete 하다는 것은 파이썬이나 C와 같은 언어로 표현할 수 있는 모든 알고리즘을 표현할 수 있다는 의미이다.

(참고로 html 도 튜링 컴플릿한 언어가 아니다.)

 

 

구체적인 쿼리를 하나씩 살펴보자.

 

Select

 

학생 테이블에서 나이가 18세 이상인 모든 학생을 조회하는 쿼리는 다음과 같이 작성할 수 있다.

SELECT *
  FROM Student S
 WHERE S.age >= 18

 

select * 은 모든 컬럼(속성)을 선택해서 조회하겠다는 의미이고, (데이터를 모두 가져온다는 의미가 아니라, 모든 컬럼을 조회하겠다는 뜻이다!)

from 은 어떤 테이블에서 데이터를 조회할 것인지를 나타낸다.

where 은 테이블에서 데이터를 가져올 때 어떤 데이터를 가져올 지 조건을 거는 부분이다.

 

만약 학생의 이름과 아이디를 선택해서 조회하고 싶다면 아래와 같이 작성하면 된다.

SELECT S.name, S.login
  FROM Student S
 WHERE S.age >= 18

 

 

 

예시를 확장해보자.

학생 테이블과 학생이 등록한 수업에 대한 관계를 나타내는 테이블이 있다.

 

From 절에는 두 테이블을 나열한다.

이렇게하면 두 테이블에 속한 모든 데이터의 cross product (데카르트 곱)를 만들어내며 이를 '조인' 이라고 한다.

 

이때 S.sid = E.sid 로 조건을 걸면 데카르트 곱연산을 한 결과물에서 sid 가 같은 레코드를 걸러내고, 추가적으로 E.grade = 'A' 인 컬럼을 골라낸다.

따라서 이 쿼리는 성적이 'A' 인 학생-수업 관계를 조회하도록 동작한다.

 

 

 

Create

릴레이션(테이블)을 생성할 때는 다음과 같은 SQL 문으로 만들 수 있다.

CREATE TABLE <table name> (
    column_name column_type,
    column_name column_type,
    ...
);

 

이때 컬럼 타입(도메인)과 ERD로 나타낸 제약조건들을 잘 나타내야 한다.

위 예시에 등장했던 학생 테이블은 아래와 같이 생성할 수 있다.

 

CREATE TABLE Student (
    sid: char(20),
    name: char(20),
    login: char(20),
    age: integer
    gpa: real
);

 

강의록에서는 컬럼 이름과 컬럼 타입 사이에 콜론(:) 기호를 넣었지만, 책에서의 표현과 MySQL 과 같은 언어에서는 붙이지 않는다.

디테일한 내용들은 언어마다 다르니 큰 형태만 기억하면 될 것 같다.

 

 

Drop, Alter

릴레이션(테이블)을 삭제할 때는 아래와 같이 작성한다.

DROP TABLE Student;

 

다만 테이블을 지울 때는 꼭 다양한 안전장치를 걸어두어야 한다.

이 테이블 데이터를 참조하고 있는 다른 테이블이 있을 수 있기 때문이다.

 

릴레이션(테이블)을 수정할 때는 아래와 같이 수정한다.

ALTER TABLE Student
	ADD COLUMN firstYear: integer

 

이렇게 새로 추가된 컬럼에 대해서는 그 값이 null 로 채워져있기 때문에 적절하게 업데이트를 해줄 필요가 있다.

만약 특정 컬럼에 null 값이 들어가지 않기를 원한다면 not null 제약조건을 컬럼에 명시해줄 수 있다.

 

 

 

Insert, Delete, Update

릴레이션(테이블)에 튜플을 추가할 때는 다음과 같은 문법으로 추가한다.

INSERT INTO Student (sid, name, login, age, gpa)
VALUES (1, "kim", "kim@ce", 20, 4.5);

 

릴레이션(테이블)에 튜플을 삭제할 때는 다음과 같이 삭제한다.

DELETE
  FROM Student S
 WHERE S.name = 'Smith'

 

릴레이션(테이블)에 있는 튜플을 수정할 때는 다음과 같이 수정한다.

UPDATE Student
   SET S.gpa = S.gpa - 0.1
 WHERE S.gpa >= 3.3

 

학점이 3.3을 넘는 모든 학생 데이터에 대해 학점을 0.1 감소시키도록 업데이트한다.

 

 

 

---

 

무결성 제약조건 (Integrity Constraint : IC)

 

무결성 제약조건은 말 그대로 데이터에 결함이 없음을 보장하도록 만들기 위한 제약을 말한다.

이 제약조건은 데이터베이스 스키마에 명시되며, 데이터베이스에 저장될 수 있는 데이터의 형태를 제한한다.

 

키 제약조건 (Key Constraint)

ERD 에서 키 제약조건은 관계에 최대 1번만 참여할 수 있음을 나타내는 데 사용되었다면,

관계 모델에서 키 제약조건은 하나의 릴레이션 안에서 특정 어트리뷰트의 최소 부분 집합이 여러번 나타날 수 없음을 나타내는데 사용된다.

즉, 이 최소 부분 집합이 하나의 튜플을 식별하는 키로 동작하게 된다. (최대 1번만 등장해야 함을 명시하는 점에서 서로 비슷하다.)

 

예를 들어 학생 릴레이션 안에서 어떤 두 학생도 동일한 학번을 가질 수 없다는 제약조건을 나타낸다면, 학번은 중복될 수 없으며 이 제약조건이 키 제약조건이 된다.

 

이때 어떤 튜플을 식별하는 최소 개수의 어트리뷰트 집합을 가리켜 '후보키' 라고 한다. (candidate key)

그리고 이 후보키 중에서 기본적으로 튜플을 식별하는데 사용할 키를 정하면 그 키가 '기본키' 가 된다. (primary key)

그런데 최소 어트리뷰트 집합이 아님에도 불구하고 튜플을 식별할 수 있는 경우도 있을 것이다.

이런 키를 '슈퍼키' 라고 한다. (super key)

 

SQL 에서 primary key constraint 를 나타낼 때는 릴레이션(테이블)을 생성할 때 다음과 같이 명시한다.

CREATE TABLE Student (
    sid: char(20),
    name: char(20),
    login: char(20),
    age: integer,
    gpa: real,
    
    PRIMARY KEY (sid)
)

 

슈퍼키를 명시적으로 정의할 때는 UNIQUE 키워드를 사용할 수 있다.

CREATE TABLE Enrolled (
    sid: char(20),
    cid: char(20),
    grade: char(2),
    
    PRIMARY KEY (sid),
    UNIQUE (cid, grade)
);

 

학생 한명이 최대 하나의 수업을 들을 수 있다면 위와 같이 제약조건을 걸 수 있다.

이때 추가적으로 하나의 수업에서 각 성적은 최대 한명의 학생들에게 주어진다면 (cid, grade) 조합이 중복될 수 없다고 만들어 위와 같이 UNIQUE 키워드로 명시할 수 있다. (예시가 조금 이상하긴 하다..)

 

 

 

참조 무결성

어떤 두 테이블 사이의 관계를 나타낼 때, 다른 테이블의 PK(Primary Key)를 자신의 테이블의 어트리뷰트로 가져와서 관계를 나타낼 수 있다.

이렇게 다른 테이블에서 관계를 나타내기 위해 가져온 키를 외래키 (Foreign Key) 라고 한다.

 

 

이 그림에서 Enrolled 테이블에 있는 sid 는 Students 테이블의 PK를 가져온 FK 이다.

이때 모든 외래키 필드의 값이 가리키는 데이터가 모두 존재해야 한다는 제약조건이 있다면, '참조 무결성' 을 달성할 수 있다.

예를 들어 Enrolled 테이블에서 53666 이라는 sid 를 가리키고 있는데, 이 sid 가 실제로 Students 테이블에 없다면 참조 무결성이 깨진 것이다. (이런 경우를 dangling reference 라고 한다.)

 

참조 무결성을 지키기 위해서는 데이터를 함부로 삭제하거나 변경하면 안되고, 삭제/변경 시에는 그 데이터를 참조하는 다른 테이블이 여전히 이전의 데이터를 가리키지 않도록 적절하게 조치를 해주어야 한다.

그래서 데이터베이스에서는 기본적으로 다른 테이블이 참조하고 있는 데이터는 지울 수 없도록 막아둔다.

 

다만 하나의 데이터가 지워질 때 그 데이터와 연결된 다른 데이터도 모두 함께 지워져야 하는 경우가 있다.

바로 약개체이다.

약개체는 다른 데이터의 PK에 의존하여 식별되므로, 해당 PK를 갖는 튜플이 삭제되면 더 이상 식별될 수 없으므로 함께 사라지는 것이 맞다.

 

CREATE TABLE Enrolled (
    sid: char(20),
    cid: char(20),
    grade: char(2)
    
    PRIMARY KEY (sid, cid),
    FOREIGN KEY (sid) REFERENCES Studnets
);

 

외래키 제약조건을 명시하는 SQL은 위와 같이 작성할 수 있다.

Foreign key (외래키 필드) references (해당 외래키를 pk 로 갖는 테이블) 형식으로 작성하면 된다.

 

DBMS는 외래키로 등록하는 필드 값은 반드시 다른 테이블에 존재하는 값이어야 하도록 제한을 걸어둔다.

(만약 다른 테이블에 PK가 없다면 그런 제한을 걸 수 없겠지만..)

 

 

한번 그림 속 문제 상황을 살펴보자.

만약 학생 튜플 하나가 삭제된다면 이 튜플을 참조하는 Enrolled 테이블 내 튜플은 어떻게 될까?

 

- 그 튜플도 삭제되거나 (CASCADE)

- 애초에 삭제할 수 없도록 막거나 (DBMS가 기본적으로 취하는 정책, NO ACTION)

- FK인 sid 값을 기본 sid 값으로 바꾸거나 (SET DEFAULT)

- null 값으로 설정해버릴 수도 있다. (SET NULL)

 

이 4가지 방법 중 어떤 것을 적용할 지 역시 테이블을 생성하면서 조건을 정할 수 있다.

 

CREATE TABLE Enrolled (
    sid: char(20),
    cid: char(20),
    grade: char(2),
    
    PRIMARY KEY (sid, cid),
    FOREIGN KEY (sid) REFERENCES Students ON DELETE CASCADE ON UPDATE SET DEFAULT
)

 

위와 같이 쿼리를 작성한다면, 학생 테이블의 데이터가 삭제될 때는 Enrolled 테이블에서 참조하는 데이터도 함께 삭제되고,

학생 테이블의 데이터가 변경될 때는, Enrolled  테이블에서 참조하는 데이터의 참조값이 기본 값으로 설정된다.

 

 

 

제약조건을 판단할 때는 특정 몇 개 데이터만 보고서 판단할 수 없다.

예를 들어서 '중복을 허용한다' 라는 제약조건을 확인하려면 중복이 되는 데이터가 1개라도 등장하는 순간 확인할 수 있겠지만

'중복을 허용하지 않는다' 라는 제약조건을 확인하려면 모든 데이터를 다 확인해서 중복이 없음을 확인해야 한다.