[Database] The Relational Model | 관계모델 (3장)

The Relational Model

관계모델

- Conceptual Schema를 설계할 때 사용되는, Simple한 점이 장점인 모델링 방법 중 하나이다.
- IBM, Microsoft, Oracle, Sybase 등과 같은 유수기업들에서 사용되고 있다.
- 관계모델 이외에도, Tree 구조의 IBM IMS, Graph 구조의 Codasyl와 같은 Legacy System에서 구동되는 모델도 있다.
  (Tree(Hierarchical) 구조, Graph(Network) 구조는 비효율적인 구조에 속한다.)
- Conceptual Schema에 위치한 DBMS가 인지할 수 있는 모델링 방법들 중 가장 널리 쓰이는 모델링 방법이다.
- E-R Model은 External Schema(=View)를 설계할 때 쓰이는 모델로, 컴퓨터는 인지할 수 없는 모델이다.)
- Relational Model을 기반으로 한 SQL(특히, DDL)을 통해 Physical Schema를 설계할 수 있다.

※ 즉, E-R Model을 기반으로 DBMS가 인지할 수 있는 Relational Model이 작성되고,
  Relational Model을 통해 설계된 Relational Schema에 기반한 SQL의 DLL을 통해 Physical Schema를 설계할 수 있다.

* Schema = 특정 Model을 채택하여 Domain을 구성한 형태
ex) E-R Model로 구현된 E-R Schema
ex) Relational Model로 구현된 Relational Schema

* E-R Schema \(\in\) External Schema(=View)
* Relational Schema \(\in\) Conceptual Schema
- E-R Schema는 View의 종류 중 하나이며, Relational Schema는 Conceptual Schema의 종류 중 하나이다.

* Object-Oriented Model
- 관계모델의 단점을 개선한 모델이다.
- ObjectStore, Versant, Ontos등이 이에 속한다.

* Object-Relational Model
- 관계모델과 객체지향모델을 혼합한 형태이며, 현재 주류를 이루고 있다.
- Informix Universal Server, UniSQL, O2, Oracle, DB2등이 이에 속한다.

---

Relational Database (관계 데이터베이스)

- Relation*의 집합으로 구성된 DB이다.

* Relation (릴레이션)
- Relation Instance와 Relation Schema, 두 개의 파트로 구성된다.
- Relation Schema는 Record(행)와 Field(열)로 구성된 테이블로, 데이터를 구성하는 틀이다.
- Relation Instance는 Relation Schema(틀)을 이용하여, 구체적인 Domain이 기술된 객체이다.
  (Relation Instance = 붕어빵, Relation Schema = 붕어빵 틀)

ex) Relation Schema = Student(sid:string, name:string, login:string, age:integer, gpa:real).

* Row(행) = Record = Tuple
* Column(열) = Field = Attribute

* Cardinality
- 테이블 내에서, 행의 개수이다.
- 즉, 테이블의 레코드 개수를 의미한다.

* Fields (=Degree, Arity)
- 테이블 내에서, 열의 개수이다.
- 즉, 테이블의 Attribute(속성)의 개수를 의미한다.

Example. "Student" Instance

Rows = Cardinality = 3
Columns = Fields = Degree = Arity = 5

---

Relational Query Language (= SQL)

- 1970년대 IBM 연구소에서 Prototype 형태로 개발한 "System R"이 현대 DBMS의 원조이다.
- SQL은 테이블을 수직분할 혹은 수평분할하여, 원하는 데이터에 빠르게 접근한다.
  (SQL은 데이터를 탐색하는데 프로그램을 필요로 하지 않는다.)
- SQL 또한, 표준이 존재한다. (SQL-86, SQL-89, SQL-92, SQL-99)

1) DDL (Schema 정보를 수정한다.)
CREATE TABLE <만들고자 하는 테이블 이름> (<필드명1>: <필드명1의 Type>, <필드명2>: <필드명2의 Type>, ...)
DROP TABLE <제거하고자 하는 테이블 이름>
ALTER TABLE <스키마를 수정하고자 하는 테이블 이름> ADD COLUMN <추가할 필드명>: <추가할 필드의 Type>

2) DML (Schema 정보는 수정되지 않는다.)
SELECT <출력하고자 하는 Field> FROM <질의할 대상이 되는 Table> WHERE <조건값>
INSERT INTO <레코드를 추가할 테이블 이름> (<추가할 값들에 해당되는 필드명>) VALUES (<추가할 값들>)
DELETE FROM <레코드를 삭제할 테이블 이름> WHERE <삭제할 레코드의 조건>
UPDATE 

3) DCL

---

Example.

SELECT * FROM Students S WHERE S.age=18

: Students S 테이블에서 age 속성값이 18인 레코드들의 모든 정보(*)를 질의

Output:

---

Example.

SELECT S.name, S.login FROM Students S WHERE S.age=18

: Students S 테이블에서 age 속성값이 18인 레코드들의 name 필드값과 login 필드값을 질의

Output:

---

Example. Querying Multiple Relations

SELECT S.name, E.cid FROM Student S, Enrolled E WHERE S.sid=E.sid AND E.grade="A"

: Students S 테이블과 Enrolled E 테이블에서 sid 속성값이 같고, grade 속성값이 "A"인 레코드들의 name 필드값과 cid 필드값을 질의

Schema:

Output:

- 두 테이블이 조건에 맞춰 Join된 결과가 출력된다.

---

Example. Creating Relations in SQL

CREATE TABLE Students (sid: CHAR(20), name: CHAR(20), login: CHAR(10), age: INTEGER, gpa: REAL

: 위에 명시된 5개의 Field들도 구성된 Students 테이블을 만든다.
- 한 레코드 당, 58bytes가 할당된다. (\(\texttt{INTEGER, REAL}\) Type은 4Bytes라 가정한다.)

CREATE TABLE Enrolled (sid: CHAR(20), cid: CHAR(20), grade: CHAR(2))

: 위에 명시된 3개의 Field들도 구성된 Enrolled 테이블을 만든다.
- 한 레코드 당, 42bytes가 할당된다.

---

Example. Destroying and Altering Relations

DROP TABLE Students

: Students 테이블 전체를 삭제한다. (Schema정보까지도 삭제한다. 즉, 아무것도 남지 않는다.)
- DBA와 같이, 권한이 부여된 사람만 실행 가능한 SQL이다.

ALTER TABLE Students ADD COLUMN firstYear: integer

: Students 테이블 스키마에 정수형 필드 "firstYear"을 추가한다.
- 새로운 필드가 추가되고 데이터가 채워지지 않으면, 기본적으로 NULL값으로 초기화된다.

---

Example. Adding and Deleting Tuples

INSERT INTO Students (sid, name, login, age, gpa) VALUES (53688, 'Smith', 'smith@ee', 18, 3.2)

: Students 테이블에 sid, name, login, age, gpa 필드에 53688, 'Smith', 'smith@ee', 18, 3.2 값들로 채워진 레코드를 삽입한다.

DELETE FROM Students S WHERE S.name = 'Smith'

: Students S 테이블에서 name 속성값이 'Smith'인 레코드들을 삭제한다.

---

Integrity Constraints (ICs; 무결성 제약조건)

- DB 설계 시, 데이터에 대한 무결성을 구현하기 위해 DBMS가 검사하는 제약조건들이다.

* Keys

Super Key (수퍼키)

Candidate Key (후보키)

Primary Key (기본키)
- 속성으로 NULL 값을 포함할 수 없다.

Alternate Key (대체키)

Unique Key (고유키)
- 슈퍼키의 범주에 들어가지 않는 키이다.
- 속성으로 NULL 값을 포함할 수 있다.
- 한 테이블에서 여러개의 설정을 할 수 있다.

* Legal Instance
- ICs를 모두 만족하는 레코드를 의미한다.

- ICs는 아래와 같이, 4가지로 구성된다.
1) Domain Constraints
2) Primary Constraints
3) Referential Constraints
4) General Constraints

※ ICs들은 Insert, Delete, Update 연산시에 적용되며, 단순 검색 질의에 대해서는 적용되지 않는다.
   (단순 검색 질의는 테이블의 내용에 변화를 주지 않기 때문이다.)

---

1) Domain Constraints (=Type Checking)
- 속성의 Type(데이터형)에 맞는 데이터가 입력되는지를 검사하는 조건이다.

---

2) Primary Constraints
- Key 속성이 Distinct함을 유지시키는 조건이다.
- Key는 여러 필드가 조합되어 구성될 수도 있고, 단일 필드로 구성될 수도 있다.
※ A 테이블에서의 Key가 B 테이블에서도 Key가 된다는 보장은 없음에 유의하자.

* Superkey (Minimal Key + \(\alpha\))
- Key 필드(Minimal Key)에 다른 필드(\(\alpha\))가 추가되어서 구별성을 더욱 제고시킨 Key이다.
- Superkey는 최소성*을 만족시키지 못한다. (최소키에 부수적 역할을 하는 키가 더 붙은 형태이기 때문이다.)
ex) "학번" + "GPA" 조합의 Key
    ("학번" 필드는 그 자체로 Key역할을 하지만, "GPA" 필드와 조합되어 레코드를 더더욱 Distinct하게 구분할 수 있게한다.)
    ("GPA" 필드로 인해, 해당 키는 최소성을 만족시키지 못한다.)

* 최소성
- 키를 구성하는 속성 하나를 제거하면, 유일하게 식별할 수 없도록 꼭 필요한 최소의 속성들로만 구성되어야 한다는 성질이다.

* Primary Key (주요키)
- Key 역할을 하는 필드가 다수로 존재할 경우, Candidate Key들 중 대표가 되는 한 필드를 의미한다.
- Key를 구성하는 필드 중, 주요키가 아닌 필드를 Unique라 부른다. (주요키가 아닌 키도 Distinct하기 때문이다.)

Example.

CREATE TABLE Enrolled (
	sid CHAR(20),
	cid CHAR(20),
	grade CHAR(2),
	PRIMARY KEY (sid),
	UNIQUE(cid, grade)
);

- 위 SQL 구문에서는 Domain Constraints(Type 명시)와 Primary Constraints(주요키 명시)를 부여한 것이다.

---

3) Referential Constraints (참조 무결성 제약조건)
- 두 테이블을 Join할 때, 두 테이블에 공통적으로 존재하는 속성이 있는지를 검사하는 조건이다.
- 즉, 한 테이블과 다른 테이블을 합칠 때, 레코드를 하나로 묶을 때 참조할 수 있는 속성이 있는지 검사하는 조건이다.

* Foreign Key (외래키)
- 현재 테이블의 필드 중, 다른 테이블에서 Key역할을 수행하고 있는 필드를 의미한다.
- 즉, 외래키를 통해 다른 테이블과의 Join이 가능해진다.
- Foreign Key = Logical Pointer
- Foreign Key Constraints가 충족되면, Referential Constraints 또한 충족된다.

* Dangling Pointer
- 외래키가 다른 테이블에는 값이 존재하지 않아 포인팅 할 엔트리가 없는, 참조 무결성 제약조건을 위배하는 포인터(필드값)이다.

Example. Foreign Key

- Enrolled E 테이블에서 sid는 외래키이다.
- Enrolled E 테이블에서 "53831"에 해당되는 sid는 Dangling Pointer이다.
- 만약, Enrolled E 테이블에서 "sid=53831"인 레코드가 없던 상태에서 삽입되려 한다면, DBMS는 이를 Reject할 것이다.

Example. Foreign Key in SQL

CREATE TABLE Enrolled
    (sid CHAR(20), cid CHAR(20), grade CHAR(2),
    PRIMARY KEY(sid, cid),
    FOREIGN KEY(sid) REFERENCES Students)

- 위 SQL 구문에서는 Domain Constraints(Type 명시)와 Primary Constraints(주요키 명시), Referential Constraints(외래키 명시)를 부여한 것이다.

* 참조 무결성을 해치지 않는 레코드 처리 방법
1) Cascade Delete
- 한 테이블에서 레코드를 삭제하면, 외래키로 연동된 다른 테이블의 레코드도 함께 삭제하는 방법이다.

2) Set NULL
- 한 테이블에서 레코드를 삭제하면, 외래키로 연동된 다른 테이블의 레코드의 외래키 필드에 NULL값을 대입하는 방법이다.

3) Set Default
- 한 테이블에서 레코드를 삭제하면, 외래키로 연동된 다른 테이블의 레코드의 외래키 필드에 기존에 정해놓은 값을 대입하는 방법이다.

4) No Action (Default)
- 삭제를 하지 않는 방법이다.
- 레코드 삽입, 수정, 삭제 연산 시 옵션을 따로 명시하지 않을 경우 No Action 방식으로 설정된다.
- 다수의 테이블이 서로 연관되어, 복잡성이 커져버린 경우에 사용하는 방식이다.

Example.

CREATE TABLE Enrolled
	(sid CHAR(20), cid CHAR(20), grade CHAR(2),
	PRIMARY KEY (sid, cid),
	FOREIGN KEY (sid) REFERENCES Students
	ON DELETE CASCADE
	ON UPDATE SET DEFAULT)

"Students" 테이블에서 삭제연산은 Cascade Delete 방식으로 진행하고, 수정연산은 Set Default 방식으로 진행하도록 한다.

---

4) General Constraints
- 질의 시에, 사용자에 의해 세워지는 조건이다.
- "나잇값이 10 이상, 70미만"과 같은 조건들이 이에 해당된다.

---

Logical DB Design (E-R Model → Relational Model)

1) Entity Set → Table

CREATE TABLE Employees (
	ssn CHAR(11),
	name CHAR(20),
	lot INTEGER,
	PRIMARY KEY (ssn)
);

- 개체집합과 그에 대한 속성들은 SQL DDL로 오른쪽 코드와 같이 표현할 수 있다.

2) Relationship Set → Table

a) M:N Relationship

CREATE TABLE Works_In (
	ssn CHAR(11),
	did INTEGER,
	since DATE,
	PRIMARY KEY (ssn, did),
	FOREIGN KEY (ssn) REFERENCES Employees,
	FOREIGN KEY (did) REFERENCES Departments
);

- 관계집합의 테이블에는 개체집합의 키속성과 Descriptive Attribute(관계 속성)를 속성으로 갖는다.
- Many to Many 관계이므로, 관계집합의 키는 개체집합의 키들의 조합으로 구성된다. (ssn + did)
- 관계집합의 입장에서, 개체집합들의 키는 Foreign Key이므로, SQL을 통해 이를 명시해야 한다.

b) 1:1 Relationship

CREATE TABLE Works_In (
	ssn CHAR(11),
	did INTEGER,
	since DATE,
	PRIMARY KEY (did),
	FOREIGN KEY (ssn) REFERENCES Employees,
	FOREIGN KEY (did) REFERENCES Departments
);

- 1 to 1 관계에서 관계집합의 키는 1 to 1으로 관계되어 있는 개체집합의 키 만으로 충분하다. (ssn)
- 만약 관계집합의 키를 (ssn + did)로 구성할 경우, 우리가 원하는 1 to 1 관계를 표현할 수 없게 된다.
- 즉, 관계집합과 한 개체집합의 키가 동일하기 때문에, 두 테이블을 Merge할 수 있다. (Works_In + Departments)

※ 관계집합과 어떤 개체집합이 1 to 1으로 관계될 경우, 두 테이블을 Merge하여 하나의 테이블로 구성할 수 있다.
   (관계집합과 개체집합의 키가 동일하기 때문에, Merge가 가능한 것이다.)

CREATE TABLE Works_IN_Dept (
	did INTEGER,
	dname CHAR(20),
	budget REAL,
	ssn CHAR(11),
	since DATE,
	PRIMARY KEY (did),
	FOREIGN KEY (ssn) REFERENCES Employees
);

- 관계집합과 두 개체집합이 모두 1 to 1으로 관계된다 하더라도, 3개의 테이블을 1개의 테이블로 합치면 안된다.
  (반드시 관계집합과 하나의 개체집합만 Merge하고, 나머지 개체집합의 키는 외래키로 들여와야 한다.)

c) Total Participation

CREATE TABLE Dept_Mgr (
	did INTEGER,
	dname CHAR(20),
	budget REAL,
	ssn CHAR(11) NOT NULL,
	since DATE,
	PRIMARY KEY (did),
	FOREIGN KEY (ssn) REFERENCES Employees,
	ON DELETE NO ACTION
);

- NULL 값을 허용하면 안되므로, \(\texttt{NOT NULL}\) 옵션을 명시해야 한다.

d) Weak Entity

 

CREATE TABLE Dep_Policy (
	pname CHAR(20),
	age INTEGER,
	cost REAL,
	ssn CHAR(11) NOT NULL,
	PRIMARY KEY (pname, ssn),
	FOREIGN KEY (ssn) REFERENCES Employees,
	ON DELETE CASCADE
);

- 약개체와 관계된 관계집합은 약개체의 테이블과 Merge된다.
  (약개체의 키(ssn + pname)와 관계집합의 키(ssn + pname)기 서로 같기 때문이다.)

e) ISA Hierarchy

- ISA 관계에서 Overlap Constraints와 Covering Constraints에 주의하여 테이블상에 구현해야한다.
- 일반적으로 Employees 테이블, Hourly_Emps 테이블, Contract_Emps 테이블 3개로 구성된다.
- Covering Constraints = Yes일 경우, Hourly_Emps 테이블과 Contract_Emps 테이블 2개로 구성된다.

Example.

- 위 E-R 모델은 1:1 관계로 연결된 관계집합과 개체집합의 테이블을 하나로 병합하여,
  단 3개의 테이블로 구성이 가능하다.

CREATE TABLE Dependents (
	pname CHAR(20),
	age INTEGER,
	policyid INTEGER NOT NULL,
	PRIMARY KEY (pname, policyid),
	FOREIGN KEY (policyid) REFERENCES Policies,
	ON DELETE CASCADE
);

CREATE TABLE Policies (
	policyid INTEGER,
	cost REAL,
	ssn CHAR(11) NOT NULL,
	PRIMARY KEY (policyid),
	FOREIGN KEY(ssn) REFERENCES Employees,
	ON DELETE CASCADE
);

CREATE TABLE Employees (
	ssn CHAR(11),
	name CHAR(20),
	lot INTEGER,
	PRIMARY KEY (ssn)
);

---

View (Virtual Table)

- 다른 테이블의 레코드만을 이용하여 구성된 테이블을 의미한다.
- 즉, 독자적인 데이터는 갖지 않으므로, View는 Schema 정보(정의)만 있고, 자신만의 레코드는 갖지 않는다.
- View에 대해서는 추가, 수정, 삭제 연산이 불가능하다. (다른 테이블의 레코드들이기 때문이다.)

Example. View

CREATE VIEW YoungActiveStudents (name, grade)
    AS SELECT S.name, E.grade
    FROM Students S, Enrolled E
    WHERE S.sid = E.sid and S.age < 21

* View and Security
- View에는 실질적인 레코드가 직접적으로 저장되어 있지 않다.
- View에 질의가 요청되면, View 내에서는 해당 데이터가 있는 테이블에 데이터를 요청하는 방식으로 연산이 진행된다.
- 즉, View에서는 데이터에 간접적으로 접근하기 때문에 보안 측면에서 유리하다.
  (레코드가 저장되어 있는 테이블을 Hiding하는 효과가 있다.)

---

Reference: Database Management Systems 3E (Raghu Ramakrishnan, Johannes Gehrke 저, McGrawHill, 2003)