관계모델의 소개
릴레이션
한 릴레이션은 릴레이션 스키마(relation schema)와 릴레이션 인스턴스(relation instance)로 이루어진다.
릴레이션 스키마
각 필드의 이름과 도메인을 명시한다.▼
Student (
sid: STRING,
name: STRING,
login: STRING,
age: INTEGER,
gpa: REAL
)
릴레이션 인스턴스
레코드(record)라고 하는 튜플(tuple)들의 집합을 말한다.
도메인 제약조건(domain constraints) : 해당 릴레이션의 각 인스턴스가 만족해야하는 중요 조건을 말한다.
차수(degree, arity) : 필드들의 수를 말한다.
카디날리티(cardinality) : 해당 릴레이션에 원소로 있는 튜플들의 수를 말한다.
관계 데이터베이스 (relational database) : 서로 다른 릴레이션 이름을 가진 릴레이션들의 모임을 말한다.
관계 데이터베이스 스키마 (relational database schema) : 해당 데이터베이스에 속한 릴레이션들을 위한 스키마들의 모임을 말한다.
SQL을 사용한 릴레이션의 생성 및 수정
SQL 생성문
CREATE TABLE Students ( sid CHAR(20),
name CHAR(20),
login CHAR(20),
age INTEGER,
gpa REAL)
SQL 제거문
DELETE
FROM Students S
WHERE S.name='smith'
SQL 수정문
UPDATE Students S
SET S.age = S.age + 1
WHERE S.sid = 53688
SQL 삽입문
INSERT
INTO Students (sid, name, login, age, gpa)
VALUES (53688, 'smith', 'smith@ee', 19, 3.8)
무결성 제약조건
무결성 제약조건(integrity constrint : IC)
데이터베이스의 인스턴스에 저장될 수 있는 데이터를 제한한다.
모든 무결성 제약조건들을 만족하면, 적법한(legal) 인스턴스라는 것과 같다.
DBMS는 무결성 제약조건들을 집행하여 적법한 인스턴스만 저장한다.
무결성 제약조건들은 서로 다른 시점에 명시되고 집행된다.
- 스키마를 정의할 때,
그 DB의 어떠한 인스턴스라도 지켜야 할 IC들을 명시한다.
- DB응용이 실행될 때,
DBMS는 위반사항들을 체크하고 명시된 IC들을 위반하는 데이터의 변경을 불허한다.
데이터 변경을 하는 문장과 트랜잭션에 대해 무결성 제약조건들이 검사되는 시점을 정확하게 명시하는 것은 매우 중요하다.
키 제약조건(key constraint)
(후보) 키에 대한 조건
- 적법한 인스턴스에서 서로다른 두 튜플은 키에 속하는 모든 필드 전체를 통하여 동일한 값을 가질 수 없다.
- 키를 구성하는 필드들의 집합의 어떠한 부분집합도 튜플에 대한 유일한 식별자가 될 수 없다.
SQL에서 키 제약조건 명시
CREATE TABLE Students ( sid CHAR(20),
name CHAR(20),
login CHAR(20),
age INTEGER,
gpa REAL
UNIQUE (name, age),
CONSTRAINT StudentsKey PRIMARY KEY(sid))제약조건에 이름을 붙임(`StudentKey`)으로 조건이 위반됐을 때 어떤 제약 조건을 어겼는지 알 수 있다.
외래키 제약조건(foreign key constraint)
가끔 한 릴레이션에 저장된 정보가 다른 릴레이션에 저장된 정보로 링크될 수 있다.
릴레이션들 중 하나가 수정되면 정보의 일관성을 위해 DBMS는 점검을 해야한다.
점검을 위해서는 두 릴레이션을 모두 포함하는 하나의 IC가 명시 되어야 한다.
가장 일반적인 IC 형태가 외래키 제약조건이다.
`Student` 릴레이션에 추가하여 두 번째 릴레이션이 있다고 해보자.▼
Enrolled (studid: String, cid: String, grade: String)
실제로 존재하는 학생들만 추가하기 위해 `Studid` 는 `Students` 릴레이션의 `sid` 값을 가져와야 한다.
`Enrolled` 외래키는 `Students` 의 기본 키와 부합되어야 한다.
즉, 열들의 수가 같아야 하며 대응되는 열의 이름은 다르더라도 호환성이 있는 데이터 타입을 가져야한다.▼
studid와 sid를 매칭해보자.
SQL에서 외래키 제약조건 명시
CREATE TABLE Enrolled ( studid CHAR(20),
cid CHAR(20),
grade CHAR(20),
PRIMARY KEY (name, age),
FOREIGN KEY (studid),
PREFERENCES Students )
일반적인 제약조건
도메인, 기본키, 외래키 제약조건들은 관계 데이터 모델의 기초적인 부분으로 취급된다.
그러나 때로는 이들보다 좀 더 일반적인 제약조건들을 명시할 필요가 있다.
예를 들면, 16세 이상만 받겠다는 등의 값의 범위 제한과 같은 것들이 있다.
현재는 테이블 제약조건(table constraint)과 단언(assertion)의 형식으로 지원한다.
무결성 제약조건의 집행
일반적인 제약조건의 집행
위배를 야기하는 삽입, 삭제, 갱신 명령은 거부된다.
일반적으로 SQL문 마지막에 검사된다.
그러나 이 검사는 트랜잭션의 마지막까지 연기될 수 있다.
기본키 제약조건에 위배되게 삽입하는 경우
sid 53688이 이미 있다.▼
INSERT
INTO Students (sid, name, login, age, gpa)
VALUES (53688, 'mike', 'mike@ee', 17, 3.4)
null 값을 넣는 경우
INSERT
INTO Students (sid, name, login, age, gpa)
VALUES (null, 'mike', 'mike@ee', 17, 3.4)
기본키 제약조건에 위배되게 수정하는 경우
sid 50000이 이미 있다.▼
UPDATE Students S
SET S.sid = 50000
WHERE S.sid = 53688
(삭제는 도메인, 기본키, 유일성을 위배하는 원인이 되지 않는다.)
외래키 제약조건의 적용 : 참조 무결성 집행단계(referential integrity enforcement step)
외래키 제약조건의 경우, 위배될 시 단순 거부가 아니라 교정하려고 할 때가 있어서 조금 더 복잡하다.
게다가 외래키 제약조건의 경우엔 삭제문도 문제가 될 수 있다.
Students에는 없는 sid이므로 거부한다.▼
INSERT
INTO Enrolled (cid, grade, studid)
VALUES ('Hindi101', 'B', 51111)
외래키 제약조건의 위배 처리를 위한 세가지 기본적인 질문
(이 예에서는 studid가 기본키라 이 옵션은 쓸 수 없지만 일반적으로는 잘 사용된다.)
외래키 제약조건의 적용 : SQL
CREATE TABLE Enrolled ( studid CHAR(20),
cid CHAR(20),
grade CHAR(20),
PRIMARY KEY (name, age),
FOREIGN KEY (studid) PREFERENCES Students
ON DELETE CASCADE
ON UPDATE NO ACTION )
옵션들
- `CASCADE`: 이를 참조하려는 모든 행을 삭제한다.
- `NO ACTION` : 삭제나 업데이트를 금지한다. (외래키 선언 시 기본으로 설정된다.)
- `SET DEFAULT` : 명시된 DEFAULT 값으로 설정된다.
`sid CHAR(20) DEFAULT '53666'` - `SET NULL` : null 값으로 설정한다.
트랜잭션과 제약조건
CREATE TABLE Students ( sid CHAR(20),
name CHAR(20),
login CHAR(20),
age INTEGER,
honors CHAR(10) NOT NULL,
gpa REAL,
PRIMARY KEY (sid),
FOREIGN KEY (honors) REFERENCES Courses (cid),CREATE TABLE Courses( cid CHAR(20),
cname CHAR(20),
credits CHAR(20),
grader CHAR(20) NOT NULL,
PRIMARY KEY (cid),
FOREIGN KEY (grader) REFERENCES Students (sid),
이렇게 서로를 참조하는 경우, 제약조건 검사를 SQL문이 끝날 때 바로 해버리면 가장 첫번째 데이터를 넣을 수 없게 된다.
그래서 이런 경우에는 검사를 연기(defer)한다.▼
SET CONSTRAINT ConstraintFoo DEFERED
SQL은 제약조건이 연기(DEFERED) 혹은 즉시(IMMEDIATE) 모드로 설정되는 것을 허락한다.
관계 데이터 질의
관계 데이터베이스 질의
SQL은 관계 DBMS에서 가장 많이 사용되고 있는 상용 질의어(query language)이다.
SELECT *
FROM Students S
WHERE S.age < 18
`SELECT` : 보여줄 필드 선택
`FROM` : 어떤 릴레이션으로부터
`WHERE` : 조건
예제1 ▼
SELECT *
FROM Students S
WHERE S.age < 18
왼쪽 테이블에 오른쪽 질의문을 보내면 아래와 같은 결과가 나온다.▼
예제2 ▼
SELECT S.name, S.login
FROM Students S
WHERE S.age < 18
이번에는 SELECT 문을 조금 바꾸어 * 와일드카드에서 S.name, S.login으로 바꿔서 보내면 아래와 같은 결과가 나온다.▼
질의어를 보면 S를 정의한 질의문이 S를 사용한 질의문 보다 아래에 있음을 알 수 있다.
C언어와는 다르게 질의어의 작성 순서는 크게 상관이 없다.
허나 질의의 셀렉션(Selection)과 필드 제거의 순서는 중요하다.
셀렉션 → 필드 제거 의 순서로 가야하는데 만약 그렇지 않으면, age필드가 제거되어 제대로 된 셀렉션을 할 수 없어진다.
논리적 데이터베이스 설계 : ER을 관계모델로
개체집합을 테이블로
(추후 추가)
(제약조건 없음)관계집합을 테이블로
키 제약조건이 있는 관계집합의 변환
참여 제약조건이 있는 관계집합의 변환
약개체 집합의 변환
클래스 계층구조의 변환
뷰의 개요
뷰(View)란?
뷰는 일종의 테이블로서 이에 속해있는 행들이 실제 DB에 저장되지 않고 뷰 정의(View Definition)를 이용하여 필요한 대로 계산된다.
쉽게 말하면 휘발적인 임시 DB 테이블이다.
필요에 의해 생성하고 다 쓰면 버리는 그런 임시적인 테이블이라고 생각하면 쉽다.
CREATE VIEW B - Students (name, sid, course)
AS SELECT S.name, S.sid, E.cid
FROM Students S, Enrolled E
WHERE S.sid = E.studid AND E.grade = 'B'
뷰, 데이터 독립성, 보안
뷰는 독립적인 존재로 원본 릴레이션들의 스키마가 수정되어도 지장을 받지 않는다.
수정된 스키마에 대한 뷰가 필요하면 새롭게 정의해버리면 그만이다.
보안적인 측면에서도 좋다.
사용자에게 뷰를 통해 제한된 정보를 제공할 수도 있다.
뷰에 대한 갱신
독립성 때문에 뷰를 아무리 건드려도 원본 릴레이션은 변경되지 않는다.
하지만 갱신 가능한 뷰(Updatable View)를 이용하면 갱신이 가능해진다.
아래의 두 조건을 포함해야 갱신 가능한 뷰가 된다.
- 하나의 베이스 테이블로부터 SELECT 및 PROJECT 연산을 통해 정의된 뷰이어야 한다.
- 뷰 내의 한 속성이 베이스 테이블의 기본키 속성을 포한해야 한다.
테이블과 뷰의 제거 및 변경
제거 명령어
`DROP TABLE` 명령을 통해 제거할 수 있다.
- `DROP TABLE Students RESTRICT`
어떤 뷰나 제약조건이 Students를 참조하고 있지 않다면, Students를 지운다.
- `DROP TABLE Students CASCADE`
Students와 이를 참조하고 있는 어떤 뷰나 제약조건도 같이 연쇄적으로 지운다.
두 조건 중 하나는 반드시 명시되어야 한다.
VIEW에도 같은 방식으로 사용할 수 있다.
수정 명령어
`ALTER TABLE` 명령을 통해 수정할 수 있다.
ALTER TABLE Students
ADD COLUMN maiden - name CHAR(10)
이 열의 값은 전부 NULL로 채워진다.
