21｜SQL·DDL·DCL·뷰·시스템 카탈로그

관계형 데이터베이스에서는 SQL을 사용하여 데이터를 조회하고 변경할 뿐만 아니라 테이블과 뷰를 정의하고, 사용자 권한과 트랜잭션을 제어합니다. 같은 SQL 명령이라도 수행하는 역할에 따라 데이터 정의어, 데이터 조작어, 데이터 제어어 등으로 구분할 수 있습니다.

이 장에서는 SQL DDL DCL의 역할을 구분하고, DDL의 CREATE·ALTER·DROP과 주요 제약조건을 살펴봅니다. 이어서 COMMIT·ROLLBACK·GRANT·REVOKE의 기능, 뷰의 생성과 변경·삭제, 시스템 카탈로그에 저장되는 메타데이터를 정리합니다.

SQL은 어떤 언어인가?

SQL(Structured Query Language)은 관계형 데이터베이스에서 데이터를 정의하고 조회·삽입·수정·삭제하며, 권한과 트랜잭션을 제어하는 데 사용하는 언어입니다.

시험 교재에서는 SQL을 DDL, DML, DCL로 구분하는 경우가 많습니다. 다만 데이터베이스 이론서나 DBMS 문서에서는 COMMIT과 ROLLBACK을 별도의 트랜잭션 제어어인 TCL로 분류하기도 합니다.

DDL은 무엇을 정의하는가?

DDL(Data Definition Language)은 데이터베이스의 논리적 구조와 물리적 구조를 정의하거나 변경·삭제하는 언어입니다. 스키마, 도메인, 테이블, 뷰, 인덱스와 제약조건 등의 데이터베이스 객체를 대상으로 합니다.

• 데이터베이스 객체의 이름과 구조를 정의합니다.
• 열의 이름, 데이터형과 기본값을 지정합니다.
• 기본키·외래키·고유성·검사 조건 등 무결성 제약조건을 정의합니다.
• DDL로 정의한 객체 정보는 시스템 카탈로그 또는 데이터 사전에 기록됩니다.

CREATE TABLE은 테이블을 어떻게 생성하는가?

CREATE TABLE은 새로운 테이블을 생성하고 각 열의 이름, 데이터형, 기본값과 제약조건을 정의하는 명령입니다.

CREATE TABLE 학생 (
    학번       VARCHAR(10) PRIMARY KEY,
    이름       VARCHAR(30) NOT NULL,
    학과코드   CHAR(3),
    성별       VARCHAR(10) DEFAULT '미정',
    이메일     VARCHAR(100) UNIQUE,
    졸업연도   INTEGER CHECK (졸업연도 >= 2020),

    FOREIGN KEY (학과코드)
        REFERENCES 학과(학과코드)
        ON UPDATE CASCADE
        ON DELETE SET NULL
);

위 예제에서는 학번을 기본키로 지정하고, 이름에는 NULL을 허용하지 않습니다. 성별의 기본값은 ‘미정’이며 이메일에는 중복값을 허용하지 않습니다. 학과코드는 학과 테이블의 학과코드를 참조하는 외래키입니다.

ALTER TABLE은 테이블 구조를 어떻게 변경하는가?

ALTER TABLE은 이미 존재하는 테이블의 구조를 변경하는 명령입니다. 새로운 열을 추가하거나 기존 열의 속성을 변경하고, 필요하지 않은 열이나 제약조건을 제거할 수 있습니다.

열을 추가하는 예

ALTER TABLE 학생
ADD COLUMN 성별 VARCHAR(10) DEFAULT '미정';

열의 기본값을 변경하는 예

ALTER TABLE 학생
ALTER COLUMN 성별 SET DEFAULT '미입력';

열을 삭제하는 예

ALTER TABLE 학생
DROP COLUMN 성별 CASCADE;

DROP은 CASCADE와 RESTRICT를 어떻게 구분하는가?

DROP은 테이블·뷰·스키마·도메인·인덱스 등의 데이터베이스 객체를 삭제합니다. 테이블의 행만 제거하는 DELETE와 달리 DROP TABLE은 테이블 구조 자체를 삭제합니다.

DROP TABLE 학생 RESTRICT;

DROP TABLE 학생 CASCADE;

DROP VIEW 전자과_학생 CASCADE;

DROP SCHEMA 학사관리 CASCADE;

COMMIT과 ROLLBACK은 트랜잭션을 어떻게 제어하는가?

트랜잭션은 데이터베이스에서 하나의 논리적인 작업 단위로 처리되는 명령들의 집합입니다. COMMIT은 트랜잭션의 변경 내용을 확정하고, ROLLBACK은 아직 확정하지 않은 변경 내용을 취소합니다.

BEGIN;

UPDATE 학생
SET 졸업연도 = 2027
WHERE 학번 = 'S001';

COMMIT;

BEGIN;

UPDATE 학생
SET 졸업연도 = 2027
WHERE 학번 = 'S001';

ROLLBACK;

GRANT와 REVOKE는 사용자 권한을 어떻게 제어하는가?

GRANT는 사용자나 역할에 데이터베이스 객체의 사용 권한을 부여하고, REVOKE는 이전에 부여한 권한을 회수합니다.

GRANT SELECT, INSERT
ON 학생
TO 홍길동;

REVOKE INSERT
ON 학생
FROM 홍길동;

• SELECT 권한은 데이터를 조회할 수 있게 합니다.
• INSERT 권한은 새로운 튜플을 삽입할 수 있게 합니다.
• UPDATE 권한은 기존 데이터를 수정할 수 있게 합니다.
• DELETE 권한은 기존 튜플을 삭제할 수 있게 합니다.
• 권한의 종류와 적용 가능한 객체는 DBMS마다 차이가 있을 수 있습니다.

뷰는 어떤 가상 테이블인가?

뷰(View)는 하나 이상의 기본 테이블이나 다른 뷰에 대한 질의를 바탕으로 정의되는 가상 테이블입니다. 일반적인 뷰는 결과 데이터를 별도의 저장 공간에 물리적으로 보관하지 않고, 사용자가 뷰를 조회할 때 정의된 질의를 실행합니다.

사용자에게 기본 테이블 전체를 공개하지 않고 필요한 행과 열만 보여 줄 수 있으며, 복잡한 질의를 하나의 논리적인 테이블처럼 사용할 수 있습니다.

CREATE VIEW는 뷰를 어떻게 정의하는가?

CREATE VIEW는 SELECT 질의를 이용하여 새로운 뷰를 정의합니다. 다음 예제는 학생 테이블에서 전자과 학생의 학번·이름·졸업연도만 보여 주는 뷰를 생성합니다.

CREATE VIEW 전자과_학생 AS
SELECT 학번, 이름, 졸업연도
FROM 학생
WHERE 학과 = '전자과';

생성한 뷰는 일반 테이블처럼 SELECT문의 FROM 절에서 사용할 수 있습니다.

SELECT 학번, 이름
FROM 전자과_학생
WHERE 졸업연도 = 2027;

뷰의 장점과 한계는 무엇인가?

뷰의 정의 변경과 삭제는 어떻게 수행하는가?

뷰를 변경하는 방법은 DBMS마다 다릅니다. 일부 교재에서는 뷰 정의를 변경할 수 없으므로 DROP VIEW로 삭제한 뒤 다시 생성한다고 설명합니다. 그러나 현대 DBMS에서는 CREATE OR REPLACE VIEW 또는 ALTER VIEW를 지원하는 경우가 있습니다.

뷰의 질의 정의를 교체하는 예

CREATE OR REPLACE VIEW 전자과_학생 AS
SELECT 학번, 이름, 졸업연도
FROM 학생
WHERE 학과 = '전자과'
  AND 졸업연도 >= 2027;

뷰의 이름을 변경하는 예

ALTER VIEW 전자과_학생
RENAME TO 전자과_졸업예정자;

뷰를 삭제하는 예

DROP VIEW 전자과_졸업예정자 RESTRICT;

DROP VIEW 전자과_졸업예정자 CASCADE;

기본 테이블이나 종속 뷰를 삭제하면 어떻게 되는가?

뷰는 기본 테이블이나 다른 뷰에 의존하므로 관련 객체를 삭제할 때 종속 관계를 확인해야 합니다. 기본 테이블을 삭제한다고 해서 항상 뷰가 자동으로 삭제되는 것은 아닙니다.

뷰에서 삽입·수정·삭제가 가능한가?

뷰에 대한 INSERT·UPDATE·DELETE 가능 여부는 뷰의 정의와 DBMS의 기능에 따라 달라집니다. 단일 기본 테이블의 열을 단순히 선택한 뷰는 갱신할 수 있는 경우가 많지만, 집계나 그룹화가 포함된 복잡한 뷰는 일반적으로 갱신이 제한됩니다.

시스템 카탈로그는 어떤 정보를 저장하는가?

시스템 카탈로그(System Catalog)는 DBMS가 데이터베이스 객체와 운영 상태에 관한 정보를 저장하고 관리하는 시스템 데이터베이스 또는 시스템 테이블의 집합입니다.

시스템 카탈로그에 저장된 정보는 실제 업무 데이터가 아니라 데이터를 설명하는 데이터이므로 메타데이터라고 합니다. 데이터 사전(Data Dictionary)이라는 표현도 함께 사용됩니다.

• 시스템 카탈로그 자체도 테이블이나 뷰 형태로 구성될 수 있습니다.
• SQL을 이용하여 허용된 시스템 카탈로그 정보를 조회할 수 있습니다.
• 사용자가 시스템 카탈로그를 직접 수정하기보다 DBMS가 DDL 실행 결과에 따라 자동으로 갱신합니다.
• 사용자에게 공개되는 카탈로그의 범위는 권한과 DBMS에 따라 달라질 수 있습니다.

시험에서 자주 혼동되는 개념

이번 장 핵심 요약

1. SQL은 관계형 데이터베이스를 정의·조회·변경·제어하는 언어입니다.
2. DDL의 대표 명령은 CREATE·ALTER·DROP입니다.
3. CREATE TABLE에서는 열의 데이터형과 무결성 제약조건을 정의합니다.
4. ALTER TABLE은 열과 제약조건의 추가·변경·삭제에 사용합니다.
5. DROP은 데이터베이스 객체의 구조 자체를 삭제합니다.
6. CASCADE는 종속 객체까지 처리하고 RESTRICT는 종속 객체가 있으면 삭제를 제한합니다.
7. COMMIT은 트랜잭션의 변경을 확정하고 ROLLBACK은 미확정 변경을 취소합니다.
8. GRANT는 권한을 부여하고 REVOKE는 권한을 회수합니다.
9. 뷰는 기본 테이블이나 다른 뷰의 질의를 바탕으로 정의되는 가상 테이블입니다.
10. 일반 뷰는 결과 데이터를 별도로 물리적으로 저장하지 않습니다.
11. 단순 뷰는 갱신 가능한 경우가 있지만 복잡한 뷰는 갱신이 제한될 수 있습니다.
12. 뷰의 변경과 종속 객체 처리 방식은 DBMS에 따라 차이가 있습니다.
13. 시스템 카탈로그는 데이터베이스 객체에 관한 메타데이터를 저장합니다.
14. 시스템 카탈로그는 DDL과 권한 명령의 실행 결과에 따라 DBMS가 자동으로 갱신합니다.

복습 문제