eff3ct's st0rage Dev Blog

[DBMS] 05. Set Operators & Nested Query

31 Aug 2023
  • 한양대학교 컴퓨터소프트웨어학부 정형수 교수님의 2022년도 데이터베이스시스템및응용 강의를 듣고 정리한 내용임을 밝힙니다.
  • 내용에 오류가 있을 수 있으며, 발견 시 댓글로 알려주시면 감사하겠습니다.

5. Set Operators & Nested Query

  • 집합 연산자와 중첩된 쿼리에 대해 알아보자.

Set Operators

집합 연산하면 떠오르는 그것들을 정확히 SQL에서도 지원한다.

  1. Union (합집합)
  2. Intersect (교집합)
  3. Except (차집합)
  4. Union All (중복을 허용하는 합집합 = 멀티셋 합집합)
  5. Except All (중복을 허용하는 차집합 = 멀티셋 차집합)

멀티셋은 이전에 언급했던것과 같이 중복을 허용하는 집합이다.

Union

  • 두 개의 테이블을 합친다.
  • 중복된 행은 하나만 나오도록 한다.
SELECT * 
FROM A
UNION
SELECT * 
FROM B;

이런식으로 두 쿼리 결과를 합쳐서 표현할 수 있다. 이때, A와 B의 칼럼 개수는 서로 같아야하고 칼럼의 이름도 같아야한다.

a  union  a  =  a
1         3     1
2         5     2  
                3
                5

Intersect

  • 교집합을 구한다.
SELECT *
FROM A
INTERSECT
SELECT *
FROM B;

마찬가지로 칼럼의 개수와 이름이 같아야한다.

a  intersect  a  =  a
1             1     1
2             3     3
3             4     
5

Except

  • 차집합을 구한다.
SELECT *
FROM A
EXCEPT
SELECT *
FROM B;

역시 칼럼의 개수와 이름이 같아야한다.

a  except  a  =  a
1          1     4
4          2     6
5          5
6

Union All

  • 중복을 허용하는 합집합을 구한다.
SELECT *
FROM A
UNION ALL
SELECT *
FROM B;

a  union all  a  =  a
1             1     1
2             2     2
3             3     3
                    1
                    2
                    3

Except All

  • 중복을 허용하는 차집합을 구한다.
  • 개인적으로 가장 Tricky하다고 생각한다. 별로 쓸 일도 없고 지원하지 않는 DB들이 많다.
  • 중복되어 있는 원소가 있으면 그 원소를 제거하지 않고 살려두는 연산인데 쉽지 않다.
SELECT *
FROM A
EXCEPT ALL
SELECT *
FROM B;

a  except all  a  =  a
1              1     1
1                    2
2

나는 무슨 연산인지 아직도 잘 모르겠다.

Nested Query (Sub Query)

쿼리 안에 쿼리가 들어있으면 Nested Query라고 한다.

ALL

ALL은 쿼리 결과의 모든 행에 대해하여 어떤 조건을 만족하는 지 확인하는 것이다. 말이 조금 어려운데, 1반 학생들이 2반 학생들 모두보다 성적이 높은 지 확인하고 싶은 이런 상황에 사용하면 된다.

SELECT *
FROM Student
WHERE Class = 1
    AND Score > ALL (
        SELECT Score
        FROM Student
        WHERE Class = 2
    );

이런식으로 작성하면, 위에서 말한 예시처럼 쿼리를 만들 수 있다. 오호~ 그렇다면, 1반 학생들 중에 2반 학생들 아무나와 성적이 같은 학생들을 찾고 싶다면 어떻게 하면 될까?

ANY

ANY는 그런 상황에 사용할 수 있다. 아무나 하나 존재하기만 하면 될 때 사용하면 된다.

SELECT *
FROM Student
WHERE Class = 1
    AND Score = ANY (
        SELECT Score
        FROM Student
        WHERE Class = 2
    );

IN

IN은 집합론에서 \(\in\)과 같은 의미이다.

SELECT *
FROM Student
WHERE Class = 1
    AND Score IN (
        SELECT Score
        FROM Student
        WHERE Class = 2
    );

위의 ANY 쿼리와 같은 의미를 가지도록 사용할 수 있다.

EXISTS

EXISTS는 쿼리 결과가 존재하는 지 확인하는 것이다.

SELECT *
FROM Student AS S1
WHERE Class = 1
    AND EXISTS (
        SELECT *
        FROM Student AS S2
        WHERE Class = 2
            AND S1.Score = S2.Score
    );

위의 IN 쿼리와 같은 의미를 가지도록 사용할 수 있다. 이렇게 여러 연산들로 같은 쿼리를 다양하게 표현할 수 있다.

NOT

NOT은 결과를 반대로 바꾸는 연산자이다.

SELECT *
FROM Student AS S1
WHERE Class = 1
    AND NOT EXISTS (
        SELECT *
        FROM Student AS S2
        WHERE Class = 2
            AND S1.Score = S2.Score
    );

이렇게 사용하면, 1반 중 2반 학생들과 성적이 같지 않은 학생들을 찾을 수 있다. 물론 그냥 AND 조건에다가 <>를 넣는것으로 해결할 수 있는데, 예시가 잘 안떠올라서 이렇게 썼다.

Correlated Query

위 예시에서 숨쉬듯 자연스럽게 쿼리를 서로 연관시켜서 써놓은 것을 볼 수 있다. 이게 바로 Correlated Query이다. 메인 쿼리와 서브 쿼리 끼리 서로 연관되어 있는 쿼리들을 말한다.

SELECT *
FROM Student AS S1
WHERE Class = 1
    AND EXISTS (
        SELECT *
        FROM Student AS S2
        WHERE Class = 2
            AND S1.Score = S2.Score
    );

이런 것처럼 S1과 S2가 서로 연관되어 있는 것을 확인할 수 있다.

요약

  • 쿼리는 집합 연산자, 서브 쿼리 같은 강력한 기능을 제공한다.
  • 집합 연산자는 강력하지만 코스트때문에 주의해야함.
  • 서브 쿼리를 잘 쓰면 편리합니다.

[DBMS] 04. Multi Table Query

30 Aug 2023
  • 한양대학교 컴퓨터소프트웨어학부 정형수 교수님의 2022년도 데이터베이스시스템및응용 강의를 듣고 정리한 내용임을 밝힙니다.
  • 내용에 오류가 있을 수 있으며, 발견 시 댓글로 알려주시면 감사하겠습니다.

4. Multi Table Query

  • 여러개의 테이블에서 사용하는 쿼리들을 알아보자.

Foreign Key Constraint

FK 제약조건은 참조되는 테이블의 특정한 칼럼이 참조하는 테이블의 특정한 칼럼과 동일한 값을 가져야 한다는 제약조건이다. 학생이 특정 강의에 등록되는 상황을 생각해보자. 학생 테이블은 Student(sid, sname, major)이고, 등록 테이블은 Enrolled(sid, cid) 라고 생각해보자. (cid는 course id의 줄임)

Enrolled의 sid는 Student를 reference 하고 있어야 하니 여기에 FK를 걸어줄 수 있다.

CREATE TABLE Enrolled (
    sid INTEGER,
    cid INTEGER,
    PRIMARY KEY (sid, cid),
    FOREIGN KEY (sid) REFERENCES Student
);

sid와 cid를 묶어서 PK로 설정하고, sid에 FK를 걸어준다. 이제 Enrolled 테이블에 sid가 Student 테이블에 없는 값이 들어가면 에러가 발생한다. 이런 제약을 걸어주는 것으로 데이터를 조금 더 안전하게 관리할 수 있다.

뜬금없지만 갑자기 어떤 학생이 자퇴를 했다고 생각해보자. 관리자는 이런 경우에 해당 학생이 듣는 모든 강의 데이터를 날리고 싶을 수 있다. 그렇다면, 일일히 삭제해야할까? SQL은 FK로 제약이 걸린 데이터가 날라갈 때 행동 규칙을 제공해준다.

현재 원하는 것은 Student 튜플이 하나 날라가면 Enrolled에 같은 sid가 포함된 모든 튜플을 삭제하고 싶은 것이다. 이런 경우에는 ON DELETE CASCADE를 사용하면 된다.

CREATE TABLE Enrolled (
    sid INTEGER,
    cid INTEGER,
    PRIMARY KEY (sid, cid),
    FOREIGN KEY (sid) REFERENCES Student ON DELETE CASCADE
);

헉! 나는 데이터를 그래도 가만히 두고 싶은데, 이렇게 하면 데이터가 다 날라간다. 그렇다면, 데이터가 날라가도 기록 자체는 냅둘 수 있을까? 이런 경우에는 ON DELETE SET NULL을 사용하면 된다.

CREATE TABLE Enrolled (
    sid INTEGER,
    cid INTEGER,
    PRIMARY KEY (sid, cid),
    FOREIGN KEY (sid) REFERENCES Student ON DELETE SET NULL
);

이렇게 하면, Student 테이블에서 sid가 삭제되면 Enrolled 테이블의 sid는 NULL로 바뀐다. 이렇게 하면 데이터는 날라가지 않고, 기록만 남길 수 있다. 이외에도 ON DELETE NO ACTION, ON DELETE SET DEFAULT 등이 있다. 자세한 내용은 postgreSQL 링크를 참고하자.

Join

다중 테이블을 다룰 때 아주 중요한 쿼리 중 하나이다. 두 개 이상의 테이블을 합쳐서 하나의 테이블처럼 사용하고 싶은 경우가 생긴다면 어떻게 해야할까? 이때, 바로 Join을 사용하면 된다.

Product(Pname, price, manufacturer)와 Company(Cname, country) 가 있다고 생각해보자. 어떤 프로덕트의 제조사가 한국 국적인 것들만 뽑아내고 싶다고 해보자. 기존의 Single Table Query는 해결할 수 없다. 어떻게든 두 테이블을 연관시켜야 가능해보인다. 이런 상황에 Join을 사용한다. 다짜고자 다음의 쿼리를 작성해보자.

SELECT Pname
FROM Product, Company
WHERE manufacturer = Cname 
    AND country = 'Korea';

엥 Join이라는 키워드가 없이 SQL을 작성해도 ‘Join’이라는 연산이 되는건가? 그렇다. 이번에는 명시적으로 Join을 사용해서 SQL을 작성해보자.

SELECT Pname
FROM Product 
JOIN Company ON manufacturer = Cname
WHERE country = 'Korea';

위 두 SQL은 동일한 Join을 수행한다. SQL을 살펴보면 서로 다른 칼럼인 manufacturer와 Cname이 같은 값을 가지는 경우를 만들면서 두 테이블을 연관시키고 있다. 이런식으로 여러 테이블을 연결시키는 것을 Join 연산이라고 한다.

Ambiguity Problem

흠 만약 Product와 Company의 Pname과 Cname이 같은 경우(둘 다 name이라면?)에는 name=name 처럼 해주면 되는 것일까? 이 경우에는 DBMS가 어떤 칼럼을 사용해야할지 모르기 때문에 에러가 발생한다. 이런 경우에는 다음과 같이 칼럼명을 명시해주면 된다.

SELECT Product.name 
FROM Product
JOIN Company ON Product.name = Company.name
WHERE country = 'Korea';

/* OR */

SELECT Product.name
FROM Product, Company
WHERE Product.name = Company.name
    AND country = 'Korea';

나는 테이블 풀 네임을 쓰기 싫은데요? 그런 당신을 위해 AS(alias)라는 키워드가 있다. 다음과 같이 사용하면 된다.

SELECT P.name
FROM Product AS P, Company AS C
WHERE P.name = C.name
    AND country = 'Korea';

개인적으로 나는 이렇게 AS를 사용해 나타내는 것을 선호한다. 이렇게 같은 이름의 칼럼이 존재하는 경우에는 모호함을 없애야한다.

Join의 내부 구현은 어떻게 되어있을까? 뭔가 두 테이블을 합치는 것 같은데, 잘은 모르겠다. 다음 예시를 한 번 생각해보자. Table X(A)와 Y(B, C)가 있고, X.A = Y.B인 X.A를 찾고 싶다고 해보자. 이 경우에는 다음과 같은 쿼리를 작성할 수 있다.

SELECT X.A
FROM X, Y
WHERE X.A = Y.B;

Join 연산은 내부적으로 다음과 같은 의미(실제로 실행하는 순서가 아닐 수 있음에 유의)로 해석된다.

Join 연산의 의미론적 해석

  1. X와 Y의 Cartesian Product를 구한다.
  2. Cartesian Product의 각 튜플에 대해 X.A = Y.B를 만족하는 튜플만 남긴다. (Selection)
  3. X.A만 남긴다. (Projection)

Cartesian Product는 곱집합을 의미하는데, 그냥 만들 수 있는 모든 조합을 만드는 것과 같다. 따라서, X의 튜플 수가 n개, Y의 튜플 수가 m개라면, Cartesian Product는 n * m개의 튜플을 가진다. 이렇게 Cartesian Product를 구하고, Selection을 수행하고, Projection을 수행하는 것이 Join 연산이다.

join-explain

Cross Product를 구하는 것은 아주 비용이 크기 때문에 Join 연산은 무거운 연산들 중 하나이다. Join 순서를 어떻게 하느냐에 따라 비용이 크게 달라지는 경우도 있다. DBMS는 이런 Join 순서도 Query Optimizer를 통해 최적화해준다. 여튼, 복잡한 연산이기 때문에 남발하는 것은 시스템의 성능에 크게 영향을 미칠 수 있다.

요약

  • FK 제약을 통해 테이블을 서로 연결할 수 있다.
  • ON DELETE [options] 를 통해 삭제 시 행동을 지정할 수 있다.
  • Join을 통해 두 테이블을 연결할 수 있다.
  • Join은 Cartesian Product를 구하고, Selection을 수행하고, Projection을 수행하는 것이다.
  • Join은 비용이 크다.

[DBMS] 03. Single Table Query

30 Aug 2023
  • 한양대학교 컴퓨터소프트웨어학부 정형수 교수님의 2022년도 데이터베이스시스템및응용 강의를 듣고 정리한 내용임을 밝힙니다.
  • 내용에 오류가 있을 수 있으며, 발견 시 댓글로 알려주시면 감사하겠습니다.

3. Single Table Query

  • 하나의 테이블에 대해 사용가능한 쿼리를 알아보자

SFW Query

Select, From, Where로 구성된 쿼리를 약자를 따서 SFW 쿼리라고 한다.

SELECT <attributes>
FROM <tables>
WHERE <conditions>
  • SELECT : 어떤 속성을 고를 것인지
  • FROM : 어떤 테이블에서 가져올 것인지
  • WHERE : 어떤 조건을 만족하는 튜플을 가져올 것인지 SFW 쿼리에는 Selection과 Projection 연산이 필요하다. where문에서 어떤 조건을 만족하는 튜플을 가져오는 것을 Selection 연산이라고 하고, select문에서 어떤 속성을 고르는 것을 Projection 연산이라고 한다.

selection-projection

위 이미지처럼 이 두 연산을 합치면 또 하나의 새로운 테이블이 만들어진다. 즉, SFW 쿼리는 Input Schema에서 Output Schema를 만들어내는 함수라고 생각할 수 있다. 예시를 하나 생각해보자, 이름이 ‘브리기테’인 학생들의 이름과 학번을 가져오는 쿼리를 생각해보자. 학생 테이블이 Student(Sid, Sname, Major) 라고 생각하면 다음과 같은 쿼리로 나타낼 수 있다.

SELECT Sname, Sid
FROM Student
WHERE Sname = '브리기테';

이때, 아래의 쿼리와 위 쿼리는 동치이다.

select Sname, Sid
from Student
where Sname = '브리기테';

즉, SQL 커맨드들은 대소문자 구분 없이 사용할 수 있다. 보통은 혼용하지 않고 한가지만 사용한다. 단, 문자열과 같은 value들은 여전히 대소문자를 구분해야 한다.

Like Query

이름에 ‘철’이 들어가는 학생들을 찾고 싶다고 해보자. 이때, 쿼리는 다음과 같다.

SELECT *
FROM Student
WHERE Sname LIKE '%철%';

%는 아무 문자열을 의미한다. 즉, 아주 긴 문자열이 될 수도 있고 공백이 되어도 된다. 위 쿼리는 문자열 사이에 ‘철’이 들어가는 모든 학생들을 찾아준다. 만약, ‘최’로 시작하는 학생들만 찾고 싶다면 다음과 같이 쿼리를 작성하면 된다.

SELECT *
FROM Student
WHERE Sname LIKE '최%';

그럼, 만약 이름의 두번째에 ‘철’이 들어가는 학생들을 찾고 싶다면 어떻게 해야할까? 이때, 다음과 같이 쿼리를 작성하면 된다.

SELECT *
FROM Student
WHERE Sname LIKE '_철%';

_는 임의의 하나의 문자를 의미한다. 따라서 두번째 자리에 ‘철’이 들어가는 학생들을 모두 찾을 수 있다. 만약 세번째 자리에 ‘철’이 들어가는 학생들을 찾고 싶다면 ‘__철%’처럼 두 번 작성하면 된다.

Distinct

학생들의 전공만 궁금한 경우를 한 번 생각해보자.

SELECT Major
FROM Student;

이렇게 작성한다면, 같은 전공을 가진 학생들이 다 같이 선택되어 전공이 중복되어 나올 것이다. 이때, 중복된 튜플을 제거하고 싶다면 다음과 같이 쿼리를 작성하면 된다.

SELECT DISTINCT Major
FROM Student;

DISTINCT를 작성해주는 것으로 중복된 튜플을 제거할 수 있다.

Order By

쿼리 결과를 학번에 대해 오름차순으로 정렬하고 싶다면 어떻게 해야할까?

SELECT *
FROM Student
ORDER BY Sid;

ORDER BY를 사용하면 된다. 만약, 내림차순으로 정렬하고 싶다면 다음과 같이 작성하면 된다.

SELECT *
FROM Student
ORDER BY Sid DESC;

기본적으로 ORDER BY는 오름차순 정렬이기 때문에, 내림차순으로 정렬하고 싶다면 DESC를 작성해주면 된다. 명시적으로 오름차순임을 나타내고 싶다면 ASC를 작성해주면 된다.

요약

  • SFW 쿼리는 Input Schema에서 Output Schema를 만들어내는 함수이다.
  • SFW 쿼리는 Selection과 Projection 연산을 포함한다.
  • SQL 커맨드들은 대소문자 구분 없이 사용할 수 있다.
  • 문자열과 같은 value들은 여전히 대소문자를 구분해야 한다.
  • %는 아무 문자열을 의미한다.
  • _는 임의의 하나의 문자를 의미한다.
  • DISTINCT를 작성해주는 것으로 중복된 튜플을 제거할 수 있다.
  • ORDER BY를 사용하면 쿼리 결과를 정렬할 수 있다.

[DBMS] 02. SQL

30 Aug 2023
  • 한양대학교 컴퓨터소프트웨어학부 정형수 교수님의 2022년도 데이터베이스시스템및응용 강의를 듣고 정리한 내용임을 밝힙니다.
  • 내용에 오류가 있을 수 있으며, 발견 시 댓글로 알려주시면 감사하겠습니다.

2. SQL

DB를 조작하는 언어

사용자와 DB를 연결해주는 인터페이스가 바로 SQL이다. 나는 시퀄이라고 읽는다. SQL(Structured Query Language)는 DB를 조작하고 쿼리를 날리기 위한 표준 언어이다. 일반적인 프로그래밍 언어보다 간단하고 하이 레벨 언어의 특성을 띈다. DBMS 마다 조금씩 문법이 다르지만, 대체로 비슷한 형태를 가지고 있다.

SQL은 두 가지 모습을 가지고 있다.

  1. DDL(Data Definition Language): DB의 스키마를 정의하는 언어
  2. DML(Data Manipulation Language): DB의 데이터를 조작하는 언어

DDL

DDL은 데이터를 정의하기 위한 SQL이다. DB의 테이블을 생성하고, 테이블 내의 속성을 정의하는 등의 작업을 한다.

CREATE TABLE student (
    id INTEGER PRIMARY KEY,
    class INTEGER,
    name VARCHAR(20),
   ....
);

이와 같이 만약 학생 테이블을 생성한다고 하면, 위와 같이 테이블의 이름과 속성을 정의해준다. 학생 테이블에는 학번, 학년, 이름 등의 속성이 있을 것이다. 이를 정의해주는 것이다. 즉, 데이터 자체를 정의해주는 언어가 DDL이다.

DML

DML은 데이터를 조작하기 위한 SQL이다. 데이터를 삽입하고, 삭제하고, 수정하는 등의 작업을 한다.

INSERT INTO student VALUES (2021000001, 1, '캐서디', ...);

학생 테이블에 새로운 학생 데이터를 삽입한다고 하면, 위와 같이 삽입할 수 있다. 즉, 데이터를 조작하는 언어가 DML이다. 데이터를 찾는데도 사용할 수 있다.

SELECT * 
FROM student 
WHERE id = 2021000001;

학생 테이블에서 학번이 2021000001인 학생을 찾는다고 하면, 위와 같이 찾을 수 있다. 요약해서 말하면, DDL로 정의된 데이터에서 DML로 데이터를 조작한다고 생각하면 된다.

테이블(Table)

그렇다면, 위에서 말한 테이블이 뭔지 알아보자. 테이블은 DB의 기본적인 데이터 저장 단위이다. 테이블은 때로는 릴레이션(Relation)이라고도 불린다. 테이블끼리 서로 관계를 형성하기 때문이다. 이전 글에서 얘기한 ‘학생’, ‘교수’, ‘강의’ 관계를 생각해보면 된다. 조금 더 형식적인 얘기를 해보자.

테이블은 tuple들의 multiset이다. (multiset은 집합과 비슷하지만, 원소의 중복을 허용한다.)

테이블의 속성은 스키마에 의해 결정된다.

tuple은 속성의 값들의 집합이다. (실제 데이터)

테이블은 행과 열로 이루어져 있다. 행은 튜플, 열은 속성이라고 생각하면 된다. 행이 실제 데이터 하나이고, 열은 데이터의 속성(이름, 학번, 학년 등…)을 의미한다. 테이블의 행은 레코드(Record)라고도 불린다. 또한, 테이블의 열은 때론 필드(Field)라고도 불린다.

SQL의 데이터 타입

Atomic Types

Atomic Types은 더 이상 나눌 수 없는 데이터 타입이다. 정수 1을 생각해보자 이걸 뭐 더 어떻게 다른 타입들로 나눌 수 없는 것처럼 가장 기본적인 단위들을 의미한다.

INTEGER, FLOAT, CHAR(n), VARCHAR(n), BOOLEAN, DATE, ...

이처럼 숫자, 문자를 포함해서 다양한 타입들이 있다. 테이블의 모든 칼럼은 Atomic Types이어야 한다. 즉, 테이블의 칼럼은 Atomic해야 한다.

테이블의 스키마

테이블의 스키마는 곧 테이블의 이름과 속성 및 타입을 의미한다.

Product(Pname: string, Price: float, Category: string, Manufacturer: string)

위의 예시와 같이 Product라는 테이블의 스키마를 정의할 수 있다. Product라는 테이블은 Pname, Price, Category, Manufacturer라는 속성을 가지고 있다. 각 속성의 타입은 string, float, string, string이다.

이때, 칼럼 중 하나는 primary key(PK)라고 불린다. 이는 테이블의 튜플을 구분하는데 사용된다. key는 아주 중요하고 데이터를 구분하는데 사용되기 때문에, 테이블의 스키마에 반드시 포함되어야 한다. 구분되어야 하는 특성을 가지기 때문에, 테이블의 레코드들은 모두 다른 key 값을 가져야 한다. PK로 사용할 수 있는 칼럼이 항상 존재하는 것은 아니다. 또한, 하나의 테이블에는 단 하나의 PK가 존재할 수 있다.

또한, 칼럼에는 제약조건을 걸 수 있다. 예를 들어, 칼럼의 값이 NULL이 될 수 없다거나, 칼럼의 값이 유일해야 한다거나 등의 제약조건을 걸 수 있다. 이 제약 조건을 위반할 시에는 DBMS가 에러를 발생시키고 데이터를 삽입하지 않는다.

NULL

NULL은 값을 모르는 경우에 사용한다. 값이 없다는 것과는 조금 달라서 c/cpp 같은 프로그래밍 언어에서의 null과는 의미에 차이가 있다. 만약 이번 학기 학점을 언젠가 알게 되지만 현재는 모른다면, 이번 학기 학점은 NULL이다. 이런 식으로 생각하면 된다. Student(id, name, grade)라는 테이블이 있다고 하자. Student(2021000001, ‘캐서디’, NULL)이라고 하면, 캐서디 학생의 학점은 아직 모른다는 의미이다.

Foregin Key

Foreign Key(FK)는 다른 테이블의 PK를 참조하는 칼럼이다. 예를 들어, 학생 테이블과 강의 테이블이 있다고 하자. 학생 테이블의 PK는 학번이고, 강의 테이블에 학생의 학번을 참조하는 칼럼(누가 강의를 수강하는 지 알기 위해)이 있다고 하자. 이때, 강의 테이블의 학생 학번 칼럼은 학생 테이블의 PK를 참조하는 칼럼이다. 이런 식으로 다른 테이블의 PK를 참조하는 칼럼을 Foreign Key라고 한다.

CREATE TABLE student (
    id INTEGER PRIMARY KEY,
    name VARCHAR(20),
    grade INTEGER
);

CREATE TABLE course (
    id INTEGER PRIMARY KEY,
    name VARCHAR(20),
    student_id INTEGER REFERENCES student(id)
);

위의 예시를 보면 course 테이블이 student 테이블을 참조하고 있음을 알 수 있다. 이렇게 해놓으면, course 테이블에 존재하지 않는 학생의 학번을 입력할 수 없다.

요약

  • 테이블은 행과 열로 이루어져 있다.
  • 테이블의 행은 튜플/레코드, 열은 속성/필드이다.
  • SQL은 임의의 제약 조건을 지원한다.
    • 이때, 제약 조건 중 PK와 FK가 가장 중요하다.
  • 스키마와 제약조건을 통해 DBMS가 데이터의 의미를 이해하고 처리해줄 수 있다.
    • 테이블을 잘 설계하면 최적화에도 도움이 된다.

[DBMS] 01. 데이터베이스 시스템이란?

29 Aug 2023
  • 한양대학교 컴퓨터소프트웨어학부 정형수 교수님의 2022년도 데이터베이스시스템및응용 강의를 듣고 정리한 내용임을 밝힙니다.
  • 내용에 오류가 있을 수 있으며, 발견 시 댓글로 알려주시면 감사하겠습니다.

1. 데이터베이스 시스템이란?

데이터베이스 (Database / DB)

DBMS를 말하려면 먼저 DB라는 것을 알아야 한다. 간단하게 말하면 데이터베이스(DB)는 ‘데이터’의 집합이다. ‘데이터’는 어떤 의미있는 정보라고 생각할 수 있고, 그런 데이터들이 많이 모여있는 집합이 바로 DB다. DB는 보통 하드디스크와 같은 저장장치에 실제로 기록되고, 구조화되어서 저장된다.

데이터베이스 매니지먼트 시스템
(Database Management System / DBMS)

DBMS는 바로 이런 DB를 관리하고 DB가 필요한 유저들에게 접근 인터페이스를 제공해주는 하나의 시스템이다. 여러 유저들이 한 번에 하나의 DB에 접근하는 경우를 생각해보자. 동시에 똑같은 데이터에 읽고 쓰기를 한다면, 무결성에 문제가 생길 수 있다. 가령, A가 1번 데이터에 쓰고 있는데 B가 1번 데이터를 읽어간다면? 값이 제대로 보장이 안된다. 이런 상황에서 DBMS는 동시성 컨트롤(Concurrency Control)을 보장해주어 한 번에 여러 명이 접근하는 상황도 안전하게 관리해준다.

그렇다면, 운영체제는 DBMS일까? 운영체제는 파일 시스템을 사용해서 파일들을 저장장치에 기록한다. 이때, 이 파일시스템은 일반적으로 동시성 컨트롤을 해주지 않는다. 또한, DBMS에서는 데이터를 작성하다 시스템이 비정상적으로 종료되는 상황에서 복구하는 기능을 보장 하고있지만, 파일시스템은 그렇지 않다. 따라서, DBMS는 운영체제와는 다른 개념이다.

DBMS에는 여러가지 종류가 있다. 그 중에서도 현재 시장을 점령하고 있는 것은 RDBMS이다. Relational DBMS의 약자인데, 말 그대로 데이터 간 관계가 있을 때 사용되는 DBMS이다. RDBMS 이외에도 Key-Value, Document, Graph 등등 다양한 DBMS가 있지만, 이 글에서는 RDBMS에 대해서만 다룬다.

관계가 있는 데이터의 예시를 들어보자. 이를테면, 대학에서 교수가 학생들을 강의하는 상황을 생각해보자. 그럼 ‘교수’, ‘학생’, ‘강의’라는 개체(entity)간 관계가 있다는 것을 알 수 있다. ‘학생’들이 ‘교수’에게 ‘강의’를 듣는다라는 관계가 있다는 것을 알 수 있다. 이런 관계가 있는 데이터들을 저장하고 관리하는 것이 바로 RDBMS이다.

위 예시에서 설명한 것 처럼 어떤 관계형 데이터 모델을 가지고 데이터를 표현할 수 있는 것이다. 이런 모델들은 스키마(Schema)라는 것으로 정의된다. 스키마는 DB의 구조와 데이터들의 제약조건을 말한다. 이 스키마를 통해서 데이터를 표현하고, 관리할 수 있다. 스키마는 관점에 따라 크게 3가지로 볼 수 있다.

  1. 외부 스키마 (External Schema)
  2. 개념 스키마 (Conceptual Schema)
  3. 내부 스키마 (Internal Schema)

말이 너무 어렵다. 어디에 External 한 것이고 어디에 Internal 하다는 얘기일까? 쉽게 말하면, 사용자의 눈에 보이는 것이 External Schema이고, 보이지 않는 부분이 Internal Schema이다. 개념 스키마는 이 둘 사이의 중간 정도로 생각하면 된다. 이제 각각의 스키마에 대해서 알아보자.

외부 스키마 (External Schema)

외부 스키마는 사용자의 입장에서 보이는 스키마이다. 사용자가 DB에 접근할 때, 사용자가 보는 것이 바로 외부 스키마이다. SQL 같은 언어로 조작하는 부분에 해당된다고 생각할 수 있다. DB를 사용하는 유저가 보는 View라고 생각할 수 있다.

개념 스키마 (Conceptual Schema)

개념 스키마는 데이터의 특징, 관계를 기술하는 스키마이다. “이 데이터는 정수여야만 해, 이런 데이터들은 저런 데이터들과 요런 관계를 가지고 있어~” 같은 메타데이터들을 서술하는 스키마라고 생각할 수 있다. 디비 스키마라고 한다면 일반적으로 이쪽을 말하는 것이다.

내부 스키마 (Internal Schema)

디스크와 같은 저장장치에 어떤 형식으로 직접 쓰여지나를 나타내는 스키마이다. 가령, ‘1’이라는 정보를 저장하고 싶은데, 실제로는 어떻게 저장되어야지 시스템이 잘 꺼내쓰고 할 수 있을 지를 나타내어 주는 것이다. 이 스키마는 사용자에게 보이지 않고 DBMS 내부 구조에 해당된다. 시스템 프로그래머가 열심히 고민해야하는 부분이라 일반 사용자는 잘 모르는 부분.

schema-explain

오호~ 이제 스키마가 무엇인지 조금 알겠다.

데이터 독립이란?

우리가 어떤 프로그램이나 서비스를 이용할 때, 데이터가 어떻게 저장되든 상관없이 그 데이터를 사용할 수 있어야 한다. 데이터가 실제로 저장되는 방식이나 조직되는 방식은 사용자가 신경 쓸 부분이 아니다. 그런 부분까지 신경쓰며 서비스를 사용해야 한다면, 인생이 정말 어려울 듯 하다. 즉, 사용자 입장에서는 이 데이터가 디스크 몇 번 트랙에 저장되는 지 어떤 형태로 저장되는 지 이런 세부적인 내용을 신경쓰지 않고 데이터가 있구나~ 하고 사용할 수 있어야 한다. 구체적으로는 데이터의 논리적인 구조의 변화(어떤 속성을 작성한다고 해서 어플리케이션의 구조를 처음부터 싹 엎어야하는게 아닌 것)로 부터 보호하는 것을 Logical Data Independence라고 하고, 데이터의 물리적인 구조의 변화(어느 디스크의 어느 인덱스에 어떻게 저장하는지)로 부터 보호하는 것을 Physical Data Independence라고 한다. DBMS를 사용하는 큰 이유 중 하나가 이런 데이터 독립성을 보장하기 때문이다.

요약

  • DBMS는 데이터를 관리하는 시스템이다.
  • DBMS는 데이터를 저장하고, 관리하고, 조작할 수 있도록 해준다.
  • DBMS는 데이터를 저장하는 방법을 스키마라는 것으로 정의한다.
  • 스키마는 크게 외부 스키마, 개념 스키마, 내부 스키마로 나뉜다.
  • DBMS는 동시성 제어, 데이터 독립성, 장애 발생 시 복구 등을 지원한다.