[SQL#1] Relational Model 구현하기

참고: Database Systems: The Complete Book (2nd edition)

이번 글에서는 관계형 모델(Relational Model)을 SQL로 구현하는 방법에 대해 알아볼 것이다. 먼저 관계형 모델의 attribute에 사용 가능한 데이터 타입을 알아본 후, 표 생성/삭제/수정, primary/foreign 키 선언, 인덱스 선언, tuple 연산과 관련된 SQL 코드를 간단히 살펴볼 것이다. 관계형 모델에 대한 설명은 다음 글을 참고하면 된다.
2024.03.07 - [Database & Big Data] - [DB#2] 관계형 모델(Relational Model)

---

SQL?

SQL이란 Structured Query Language의 약자로, 관계형 데이터베이스를 표현하고 관리하기 위한 언어이다. 데이터베이스 schema 선언을 위한 DDL(Data Definition Language)와 데이터베이스 쿼리 및 수정을 위한 DML(Database Management Language)을 지원한다.
 

가능한 데이터 타입

• CHAR(n), VARCHAR(n)
  • CHAR(n): 글자 개수가 n보다 작으면 뒤에 space로 패딩 채워서 n 크기를 맞춰줌 e.g. n=4라면, 'foo'는 'foo '로 저장됨
  • VARCHAR(n): n보다 작아도 그대로 저장
• BIT(n), BIT VARYING(n)
• BOOLEAN
  • 가능한 값: TRUE, FALSE, UNKNOWN
• INT, SHORTINT, FLOAT, REAL, DOUBLE PRECISION, DECIMAL(n,d)
• DATE e.g. '1948-05-14'
• TIME e.g. '15:00:02.5', '12:00:00-8:00' → UTC-8 (표준시간)
• TIMESTAMP e.g. '1948-05-14 12:00:00'

 

Relational Model 연산

표 생성하기: CREATE TABLE

CREATE TABLE MovieStar (
    name    CHAR(30),
    address    VARCHAR(30),
    gender    CHAR(1),
    birthdate    DATE
                );

표 수정하기: ALTER TABLE/DROP TABLE

DROP TABLE R;
ALTER TABLE MovieStar ADD phone CHAR(16);
ALTER TABLE MovieStar DROP birthdate;

기본값 설정하기: DEFAULT

NULL이 아닌 값을 기본값으로 설정하고 싶은 경우 사용

gender CHAR(1) DEFAULT '?',
Birthdate DATE DEFAULT DATE '0000-00-00'
ALTER TABLE MovieStar ADD phone CHAR(16) DEFAULT 'unlisted';

키 선언하기: PRIMARY KEY / UNIQUE / FOREIGN

특정 attribute가 key가 되도록 선언.

CREATE TABLE MovieStar (
    name    CHAR(30) PRIMARY KEY,
    address VARCHAR(30),
    gender    CHAR(1),
    birthdate    DATE
);

여러 attribute가 key인 경우 다음과 같이 사용.

CREATE TABLE MovieStar (
    name    CHAR(30),
    address    VARCHAR(1),
    gender    CHAR(1),
    birthdate    DATE,
    PRIMARY KEY    (name, address)
);

PRIMARY KEY 대신, 다음과 같이 UNIQUE를 사용할 수도 있음.

• 차이점: primary key는 한 개만 될 수 있는 반면, unique key는 여러 개가 있을 수 있음. 또한, primary key attribute의 경우 NULL 값을 가질 수 없음.

CREATE TABLE MovieStar (
    name    CHAR(30),
    address    VARCHAR(1),
    gender    CHAR(1),
    birthdate    DATE,
    UNIQUE    (name, address)
);

특정 attribute가 foreign key가 되도록 선언하고 싶은 경우, FOREIGN KEY / REFERENCE 사용하여 다음과 같이 선언
주의사항 1: 참조되는 attribute는 UNIQUE 이거나 PRIMARY KEY여야 한다.
주의사항 2: foreign key의 값은 어떤 tuple의 참조된 attribute에 나타나야 한다.

• 예시: Studio의 presidentCert를 MovieExec에서 참조될 수 있는 foreign key로 설정

  CREATE TABLE Studio (
    name    CHAR(30), PRIMARY KEY,
    address    VARCHAR(255),
    presidentCert    INT,
    FOREIGN KEY (presidentCert) REFERENCES MovieExec(cert)
  );

인덱스 선언하기: CREATE/DROP INDEX

인덱스: 특정 필드 값을 가진 tuple을 효율적으로 찾기 위한 데이터 구조. e.g. B-tree

CREATE INDEX YearIndex ON Movies(year);
DROP INDEX YearIndex;

Tuple 연산: INSERT/DELETE/UPDATE

삽입 선언문을 통해 새로운 tuple은 relation에 삽입될 수 있다. 이때 특정 attribute 값이 없는 경우 기본값을 갖게 된다. 모든 attribute에 대한 값을 제공하는 경우, attribute list는 생략될 수 있다.

• e.g. Movies(title,...,studio) 대신 Movies

INSERT INTO Movies(title,year,length,genre,studio) VALUES ('Ponyo',2008,103,'anime','Ghibli');

특정 조건을 만족하는 모든 tuple을 삭제할 수 있다. 삭제할 tuple 조건은 WHERE를 통해 표현한다. 만약 WHERE을 생략하는 경우 해당 표의 모든 tuple을 삭제한다.

DELETE FROM Movies
WHERE year >= 2008
AND length > 100
AND gentre = 'anime';

기존 tuple을 업데이트할 수 있다. 이때 여러 assignment는 comma로 구분된다.

UPDATE Movies
SET length = 110, Producer = 123
WHERE title = 'Ponyo'
AND year = 2008;