[데이터베이스] 트랜잭션과 무결성 제약 조건, 도메인

Transactions

트랜잭션: 하나의 작업 단위로 간주되는 질의 및/또는 갱신 명령문의 순서

시작과 종료

• SQL에서는 명시적인 시작 없이 SQL 문이 실행되면 트랜잭션이 자동으로 시작됨
• 트랜잭션은 아래 중 하나로 종료해야 함:
  • COMMIT WORK: 변경 사항을 영구 반영
  • ROLLBACK WORK: 수행된 변경 사항을 모두 취소

트랜잭션 특성

• Atomic(원자성): 전부 수행되거나, 전혀 수행되지 않아야 함
• Isolation(격리성): 동시에 실행되는 다른 트랜잭션으로부터 독립적으로 수행(concurrent)

Integrity Constraints(무결성 제약 조건)

• 무결성 제약 조건은 데이터베이스 손상 방지를 위해 사용됨
• 데이터 변경 시 일관성(consistency) 을 유지하도록 강제함

예시 제약 조건

• 당좌예금 계좌는 $10,000.00 이상의 잔고를 가져야 함
• 은행 직원의 시급은 최소 $4.00 이상이어야 함
• 고객 정보에는 *전화번호가 반드시 존재(null이 아님) 해야 함

Constraints on a Single Relation

단일 릴레이션(테이블)에 적용되는 주요 제약 조건들은 다음과 같다:

not null

• 해당 속성(attribute)은 null 값을 가질 수 없음.
• 예: 학생 ID나 이름처럼 반드시 값이 존재해야 하는 속성에 사용.

primary key

• 릴레이션에서 튜플을 고유하게 식별할 수 있는 속성 또는 속성들의 집합.
• 각 릴레이션마다 하나의 primary key만 존재 가능.
• 자동으로 not null + unique 제약 조건이 결합됨.

unique

• 해당 속성에 저장된 값들이 중복되지 않아야 함.
• 하나의 릴레이션에서 여러 unique 제약 조건을 둘 수 있음.
• 단, unique는 null 값을 허용할 수 있음.

check (P)

• 사용자 정의 조건을 지정할 수 있는 제약.
• P는 어떤 논리식(Predicate)이며, 해당 조건을 만족해야 삽입이나 갱신이 허용됨.
• 예: check (salary > 0) → salary가 0보다 커야 함.

이러한 제약 조건들은 데이터의 정합성(integrity) 을 보장하기 위해 사용된다.

Not Null Constraints

• not null로 값에 null이 올수 없도록 테이블을 만들때 명시할 수 있다.

name varchar(20) not null
budget numeric(12,2) not null

Unique Constraints

primary key는 값이 필요하므로 null을 허용하지 않으나 후보키는 null을 허용한다. unique ( A1, A2, …, Am)

The check clause

조금더 복잡한 조건을 명시하고 싶을때 사용한다. primary key는 집합으로 명시된 예시이다. 학기가 가을 봄 여름 겨울 중 하나로 제한하고 싶을때 충족하지않는(check 조건에서 false가 나오는) 튜플을 나중에 추가하려고 하면 DBMS가 막아준다.

create table section
(course_id varchar (8),
sec_id varchar (8),
semester varchar (6),
year numeric (4,0),
building varchar (15),
room_number varchar (7),
time_slot_id varchar (4),
primary key (course_id, sec_id, semester, year),
check (semester in ('Fall', 'Winter', 'Spring', 'Summer')))

Referential Integrity

대학교 DB, instructor, department 테이블 있다고 하고 department_name이라는 외래키 있다고 가정 여기서 instructor하나를 추가하고 싶다고하면 referential Integrity가 성립되기 위해서는, 외래 키 department_name이 참조하는 대상 테이블(department)의 해당 컬럼(dept_name)이 기본 키(primary key) 혹은 유일 키(unique key) 여야 한다

foreign key (dept_name) references department

Cascading Actions in Referential Integrity

대학교 DB, instructor, department 테이블 있다고 하고 department에는 A학과, B학과 가 있다.

A학과를 삭제하면? -> A학과에 소속되는 교수가 연관되어 모두 함께 삭제된다 on 키워드를 이용해서 설정할 수 있다. on delete cascade

지울 필요는 없을때 casecade 대신에 set null, set default

create table course (
(...
dept_name varchar(20),
foreign key (dept_name) references department
on delete cascade
on update cascade,
...)

Integrity Constraint Violation During Transactions

아래와 같이 self-reference 테이블이 있다고 할때, 사람 하나를 삽입하는것도 말이 안되고, father, mother not null로 할수가 없다.

자신을 참조하는 테이블은 잘못 설계된 것이다.

create table person (
ID char(10),
name char(40),
mother char(10),
father char(10),
primary key ID,
foreign key father references person,
foreign key mother references person)

Complex Check Conditions

CHECK 절 안의 조건(predicate)은 임의의 조건식이 될 수 있으며, 서브쿼리(subquery)도 포함할 수 있다.

CHECK (time_slot_id IN (SELECT time_slot_id FROM time_slot))

이 CHECK 제약 조건은 section 테이블에 들어가는 각 튜플의 time_slot_id가 실제로 time_slot 테이블에 존재하는 값이어야 한다는 것을 의미한다. 이 조건은 단순히 section 테이블에 데이터가 삽입되거나 수정될 때만 검사하는 게 아니라,

time_slot 테이블의 데이터가 변경될 때도 함께 검사되어야 해. section 테이블에 새로운 시간 슬롯을 할당할 때 → 그 슬롯이 실제로 존재하는지 검사 • 기존의 time_slot 테이블에서 어떤 시간 슬롯을 삭제하거나 수정할 때 → section 테이블에서 그 값을 참조 중이면 위반이 발생함

 

Assertions

Assertion(단언) 은 데이터베이스가 항상 만족해야 하는 조건을 표현하는 술어(predicate) 이다. 즉, 어떤 논리적인 조건을 정의하고, 데이터베이스의 모든 상태가 그 조건을 항상 만족하도록 보장한다.

→ 각 student 튜플의 tot_cred 값은 해당 학생이 성공적으로 이수한 과목들의 학점 총합과 같아야 한다. → 하나의 교수는 한 학기 동안 같은 시간대에 서로 다른 강의실에서 수업을 할 수 없다. 이와 같은 복잡한 조건은 단순한 테이블 수준 제약(CHECK 등)으로는 표현하기 어려우며, Assertion을 사용하여 전체 데이터베이스 수준에서 검사해야 한다.

SQL에서는 CREATE ASSERTION ... CHECK (...) 구문으로 정의한다.

CREATE ASSERTION <assertion-name>
CHECK (<predicate>);

일반적인 CHECK 제약 조건은 테이블 수준에 적용되지만, Assertion은 데이터베이스 전체 수준에서 동작한다는 점에서 다르다.

 

Built-in Data Types in SQL

• date: 연도(4자리), 월, 일을 포함한 날짜 정보
  • 예시: date '2005-7-27'
• time: 하루 중 시간 정보를 나타내며, 시(hour), 분(minute), 초(second)를 포함함
  • 예시: time '09:00:30', time '09:00:30.75' (초 뒤의 .75는 밀리초 등 정밀 시간 표현)
• timestamp: 날짜와 시간 정보를 모두 포함
  • 예시: timestamp '2005-7-27 09:00:30.75'
• interval: 일정한 시간의 기간을 나타냄
  • 예시: interval '1' day

연산 관련 설명

• 두 개의 date, time, 또는 timestamp 값을 뺄 경우 interval 값이 된다.
• interval 값은 date, time, timestamp 값에 더할 수 있다.

Large-Object Types

대용량 객체는 사진, 비디오, CAD 파일 등과 같이 크기가 큰 데이터를 저장할 때 사용된다. 따로 형태를 만들지 않고 객체 그대로 저장한다.

데이터 타입

• blob (Binary Large Object)
  • 해석되지 않은 이진 데이터(binary data)의 큰 집합
  • 데이터베이스 시스템 외부의 애플리케이션이 그 해석을 담당함
  • 예: 이미지, 비디오, 오디오 파일 등
• clob (Character Large Object)
  • 문자(character) 데이터의 큰 집합
  • 예: 방대한 텍스트 문서, XML 파일 등

쿼리와 대용량 객체

• 쿼리가 대용량 객체를 반환할 때는 객체 자체가 아니라 포인터(pointer) 가 반환된다. → 실제 데이터는 외부에서 접근하거나, 필요한 경우에만 가져오도록 처리된다.

User-Defined Types

데이터 타입을 만들수 있다 -> alias를 이용한다

• SQL에서 create type 구문을 사용하여 **사용자 정의 타입(User-Defined Type)**을 생성할 수 있다.

구문 예시

create type Dollars as numeric(12,2) final;

• Dollars라는 이름의 사용자 정의 타입을 생성함
• numeric(12,2)는 소수점 아래 2자리까지 포함하는 최대 12자리 숫자
• final 키워드는 이 타입이 더 이상 상속 또는 변경될 수 없는 최종 타입임을 의미함

사용자 정의 타입을 사용하는 테이블 예시

create table department (
  dept_name varchar(20),
  building varchar(15),
  budget Dollars
);

• budget 속성은 사용자가 정의한 Dollars 타입을 사용함
• 코드의 가독성과 일관성을 높일 수 있음

Domains

유저 정의 타입과 똑같이 자료형을 만들수 있는데, 제한 조건을 지정할수 있다.

• SQL-92부터 create domain 구문을 사용하여 사용자 정의 도메인(domain) 을 생성할 수 있다.
• 도메인(Domain) 은 특정 속성이 가질 수 있는 값의 범위와 제약 조건을 지정하는 데이터 타입의 확장 개념 이다.

기본 구문 예시

create domain person_name char(20) not null;

• person_name이라는 사용자 정의 도메인을 생성한다.
• 이 도메인은 char(20) 타입이며, null 값을 허용하지 않는다.

---

제약 조건이 포함된 예시

create domain degree_level varchar(10)
constraint degree_level_test
check (value in ('Bachelors', 'Masters', 'Doctorate'));

• degree_level이라는 도메인을 생성한다.
• 이 도메인은 varchar(10) 타입이며,
• check 제약을 통해 오직 'Bachelors', 'Masters', 'Doctorate' 값만 허용한다.
• degree_level_test는 이 제약 조건의 이름이다.

도메인 vs 타입

항목	설명
도메인(Domain)	기존 데이터 타입에 이름을 붙이고 제약 조건을 부여한 것
타입(Type)	완전히 새로운 사용자 정의 데이터 타입 (예: 구조체 형태, 상속 가능 등)

도메인의 장점

• 재사용성: 동일한 제약 조건을 여러 테이블에서 일관되게 적용 가능
• 가독성 향상: 의미 있는 이름을 부여함으로써 설계 의도를 명확히 표현
• 무결성 보장: 중앙에서 값의 범위를 제어할 수 있음

---

Index Creation

• 많은 쿼리는 테이블에 존재하는 모든 레코드 중 일부만 참조한다.
• 특정 값을 가진 레코드를 찾기 위해 모든 레코드를 일일이 읽는 방식은 비효율적이다.

인덱스란?

• 인덱스(index) 는 릴레이션(테이블)의 속성(attribute)에 대해 생성되는 데이터 구조이다.
• 인덱스를 사용하면, 속성의 특정 값을 가지는 튜플(레코드)을 전체 테이블을 탐색하지 않고 효율적으로 찾을 수 있다.
• 예를 들어 도서관 책 목록에서 책 이름으로 찾을 때, 인덱스를 사용하면 정렬된 책 제목 리스트에서 빠르게 원하는 책을 찾을 수 있다.

 

인덱스 생성 구문

create index <인덱스이름> on <테이블이름> (속성이름);

예시

create index student_name_index on student(name);

• student 테이블의 name 속성에 대해 student_name_index라는 인덱스를 생성함
• 이후 name을 조건으로 하는 검색이 훨씬 빠르게 수행될 수 있다

항목	설명
목적	특정 값을 가진 튜플을 빠르게 찾기 위함
장점	전체 테이블을 순차 탐색하지 않아도 되어 검색 성능 향상
생성 구문	create index <이름> on <테이블> (속성);
주의점	인덱스는 검색 성능은 높이지만, 삽입/삭제/갱신 시 오버헤드가 발생할 수 있음