[기초데이터베이스] 03. The Relational Model

 

관계모델의 소개

 

릴레이션 (Relation)

관계모델에서 데이터를 표현하는 주된 구성자.
한 릴레이션은 릴레이션 스키마와 릴레이션 인스턴스로 이루어짐.

- 인스턴스 (Instance)
 : 테이블.
   레코드라고하는 투플들의 집합.
   * 투플(tuples) : 특성에 대한 값들의 나열

- 스키마 (Schema)
 : 릴레이션의 이름, 각 필드(열 혹은 애트리뷰트)의 이름, 도메인을 명시함

 

 

SQL을 사용한 릴레이션의 생성 및 수정

데이터 정의어 (Data Definition Language, DDL)
: SQL 중에서 테이블의 생성, 삭제, 수정하는 부분

- Creating Relations in SQL

- Destroying and Altering Relations

- Adding and Deleting Tuples

- Updating

 : WHERE 절이 먼저 적용되어 어떤 행들이 수정되어야 하는가를 결정함.
  그 다음으로 SET 절에서 이 행들이 어떻게 수정될 것인가를 결정함.

 

 

---

무결성 제약조건

 

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

데이터베이스 스키마에 명시되는 조건으로서,
데이터베이스의 인스턴스에 저장될 수 있는 데이터를 제한함. 부정확한 정보가 입력되는 것을 방지.

데이터베이스 인스턴스가 그 데이터베이스 스키마에 명시되어 있는 모든 무결성 제약조건들을 만족하면,
그 데이터베이스 인스턴스는 적법한(legal) 인스턴스이므로 데이터베이스에 저장되도록 허용됨.

 

키 제약조건

릴레이션에 속한 필드들의 어떤 최소 부분집합이 각 투플에 대한 고유한 식별자라는 선언임.

이 키 제약조건에 의하여 투플을 유일하게 식별하는 필드 집합을
그 릴레이션에 대한 후보키(candidate key)라고 함(줄여서 키)

-> 만약 name필드가 키로 선언되면,
    DBMS는 같은 이름을 가진 서로 다른 학생을 기술하는 두 투플을 포함하는 것을 허용하지 않음.

-> {sid, name}이라는 필드 집합은 키 {sid}를 진부분집합으로 포함하고 있으므로 키가 아님.(수퍼키임)

모든 릴레이션은 하나의 키를 가지는 것이 보장됨.
한 릴레이션은 여러 개의 후보키를 가질 수 있음.

모든 가능한 후보키들 중에서 기본키(primary key)를 하나 지정할 수 있음.

sid가 기본키이며 cid와 grade의 조합 역시 하나의 키임.

UNIQUE 제약조건을 사용함으로써 한 테이블에 속한 필드들의 부분집합이 키임을 선언.
이 후보키들 중에서 최대로 하나만 PRIMARY KEY 제약조건을 사용함으로써 기본키로 선언.

 

외래키 제약조건

실제로 존재하는 학생들은 과목 등록. E의 sid에 나타나는 값은 반드시 S의 sid에 나타나야함.

Enrolled 릴레이션의 studid 필드는 외래키(foreign key)라고 하며, Students를 참조(refer)함.
참조하는 릴레이션(Enrolled)의 외래키는 참조되는 릴레이션(Students)의 기본키와 부합되어야 함.
즉, Enrolled 테이블의 인스턴스에 나타나는 sid 값은 모두 Students 테이블에 있는 어떤 행의 기본키 열에 나타남.

외래키는 동일한 릴레이션을 참조할 수도 있음!
Student 릴레이션을 확장하여 partner라는 열을 추가하고 이 열이 Students를 참조하는 외래키라고 선언 가능.
partner 필드는 단짝의 sid값을 포함하는데, 만약 아직 단짝이 없다면 널(null)값 사용.
=> 외래키 필드에 null 값이 나타나는 것은 외래키 제약조건을 위배하지 않음.

즉, 외래키 값은 NULL이 될 수도 있음!! but 기본키(한 투플을 유일하게 식별) 필드에는 NULL값 불가능

Enrolled의 모든 sid 값은 Studens의 기본키 필드 sid의 값으로 나타나야 함.

Students 테이블의 sid에 없는 값을 sid로 가지는 Enrolled 투플을 insert하려고하면, 
IC에 위배되므로 삽입 연산 거부당함.
Enrolled에 의해 참조되고있는 Students 투플을 삭제한다면,
삭제된 Students 투플을 참조하고 있는 Enrolled 투플도 삭제하거나 삭제를 불허함.

 

 

---

무결성 제약조건의 집행

위배를 야기하는 삽입, 삭제, 갱신 명령은 거부됨.
제약조건의 위배 여부는 일반적으로 각 SQL문의 실행의 마지막에 검사되는데,
그 문장을 실행하는 트랜잭션의 마지막까지 연기(defer)될 수 있음.

참조 무결성 집행 단계 (referential integrity enforcement step) 논의
Enrolled 투플들의 삽입(S.sid 없는거)이나 Students 투플의 삭제(E.sid에 있는거)는 참조 무결성 위배를 야기할 수 있음.

NO ACTION : 디폴트 옵션. 실행(delete/update)이 거부됨.
CASCADE : Students 행이 삭제되면, 이를 참조하는 모든 Enrolled 행들 역시 삭제됨.
SET NULL : ON DELETE  SET NULL 처럼 명시해서, 디폴트 값으로 널의 사용도 허용됨.

 

트랜잭션과 제약조건

트랜잭션
 : 데이터베이스를 대상으로 실행하는 프로그램.
   데이터베이스에 접근하는 여러 문장들(질의, 삽입, 갱신 등)을 포함할 수 있음.

기본적으로는 하나의 제약조건은 각 SQL 문의 마지막에 검사됨.

상대방 투플이 존재해야 삽입할 수 있는 경우 처음에 어느 누구도 삽입 불가능
-> 제약조건의 검사 연기(defer)하기

연기(DEFERRED) 혹은 즉시(IMMEDIATE)모드로 설정 가능

SET CONSTRAINT ConstraintFoo DEFERRED
연기모드로 설정된 제약조건은 완료 시점에 확인됨.

 

 

---

관계 데이터의 질의

 

관계 데이터베이스 질의 (relational database query)

데이터에 관한 질문이며 그 질문에 대한 답은 그 결과를 포함하는 새로운 릴레이션으로 구성됨.

질의어 (query language)

질의를 작성하기 위한 특수 언어

SQL

관계 DBMS에서 가장 많이 사용되고 있는 상용 질의어임.

'*'는 질의의 결과에 선택된 투플들의 모든 필드를 유지한다는 의미임.

 

 

---

논리적 데이터베이스 설계: ER을 관계모델로

 

개체집합을 테이블로 (Entity sets to tables)

 

(제약조건이 없는) 관계집합을 테이블로 (Relationship Sets to Tables)

ex1.

참여하는 각 개체집합의 기본키에 속하는 애트리뷰트들(외래키 역할)과
관계집합을 서술하는 애트리뷰트들을 포함해야 함.

기본키(ssn, did)의 각 필드는 널 값을 가질 수 없음.
NOT NULL 제약조건이 묵시적으로 적용되어 있음. (유일하게 식별 보장)

ex2. Ternary Relationship to Tables

삼진관계 집합

ex3.

참조하는 필드와 참조되는 필드의 이름이 다르기 때문에
Employees 테이블의 참조되는 필드를 분명히 지명하는 것이 필요함.

 

키 제약조건이 있는 관계집합의 변환 (Translating ER Diagrams with Key Constraints)

각 부서가 많아야 한 명의 관리자를 두므로, 어떠한 두 투플도 did값이 같으면서 ssn값이 다른 경우가 없음.
그러므로 did가 Manages의 키가 될 수 있음!

sol1) Manages를 위하여 별도의 테이블 사용

외래키가 2개 -> 어떤 질의들은 두 릴레이션(M과 E 또는 M과 D)의 정보를 조합하는 것이 필요함
=> Slow!!!


sol2) Departments가 Manages 정보 포함

ssn은 널값을 취할 수 있음

별도의 Manages 릴레이션 만들 필요가 없고,
어떤 부서의 관리자에 대한 질의가 두 릴레이션을 조합하지 않고 응답될 수 있음.
but 관리자가 없는 경우에 공간이 낭비될 수 있음!!! (널값으로 채움)

 

참여 제약조건이 있는 관계집합의 변환 (Participation Constraints in SQL)

참여 제약조건에 의하여 각 부서는 반드시 관리자가 있어야 하며,
키 제약조건에 의하여 각 부서는 최대 한 명의 관리자를 둘 수 있음.

NOT NULL : 전체참여이므로 ssn이 널값을 취할 수 없음.
NO ACTION : Employees 투플이 Dept_mgr 투플에 의해 참조되고 있는 한
                       그 Employees 투플은 삭제될 수 없음을 보장함.


만약 Employees 투플을 삭제하기를 원한다면,
Dept_mgr 투플을 먼저 변경하여 새로운 직원이 관리자가 되도록 해야함.

부적절한 예시

NO ACTION 대신 CASCADE를 쓸 수도 있겠지만, 이렇게하면
어떤 부서의 관리자가 해고되었기 때문에 그 부서에 대한 모든 정보를 삭제한다는 극단적인 방법임.


참고로 Manages 별도의 테이블을 사용하는 방식으로는 표햔 불가능.

부적절한 예시

sid와 did가 NOT NULL 이라면, 관리자의 해고를 방지할 수는 있겠지만
초기에 각 부서의 관리자가 지명되는 것을 보장하지는 않음.


따라서 Manages와 같이 일-대-다 관계에 있으면서 해당 개체집합이
키 제약조건과 전체참여 제약조건을 모두 가지고 있을 경우에 Dept_Mgr 방식이 선호하여 사용됨.

cf) 교재 p.77

테이블 제약조건이나 단언을 제외하고는 SQL로 표현할 수 없는 참여 제약조건들

 

약개체집합의 변환 (Translating Weak Entity Sets)

약개체는 부분키만 가지고 있음.(E 개체의 키와 그 D 개체의 pname을 취해야 유일하게 식별됨)
한 소유자 개체(E)가 삭제되면, 이 소유자에게 소속된 모든 약개체(D)들도 삭제되어야 함.

Dependents는 약개체이므로 기본키는 <pname, ssn>임.

Dependents의 전체참여 제약조건에 의하여
각 Dependents 개체는 하나의 Employees 개체(소유자)와 연관되어 있다는 것을 확실히 해야함.
즉, ssn은 널이 될 수 없음. (ssn이 기본키의 일부이므로 이 점은 보장됨)

CASCADE 옵션은 한 Employees 투플이 삭제되면
그 직원에 대한 보험증권과 부양자에 관한 정보도 삭제되는 것을 보장함.

 

클래스 계층구조의 변환 (Translating ISA Hierarchies to Relations)

sol1) 3 relations: Employees, Hourly_Emps and Contract_Emps
• Hourly_Emps 릴레이션 
 - hourly_wages와 hours_worked 애트리뷰트들을 포함함
 - 슈퍼클래스의 키 애트리뷰트(ssn)을 포함하는데, 이 애트리뷰트는
   Hourly_Emps 릴레이션의 기본키이며, 또한 슈퍼클래스(Employees)를 참조하는 외래키임
 - 슈퍼클래스 투플이 삭제되면, 그 삭제가 Hourly_Emps에 연쇄적으로 적용되어야 함.
  즉, Hourly_Emps (hourly_wages, hours_worked, ssn)

sol2) Just Hourly_Emps and Contract_Emps
Hourly_Emps에 대한 릴레이션은 Employees의 모든 애트리뷰트뿐만 아니라
Hourly_Emps의 모든 애트리뷰트를 포함함.
즉, Hourly_Emps (ssn, name, lot, hourly_wages, hours_worked).

 

집단화가 있는 ER 다이어그램의 변환

교재 p.80

 

ER에서 관계모델로: 추가 예제 (Binary vs. Ternary Relationships)

한 직원의 삭제가, 그 직원에 의하여 소유된 모든 보험증권들의 삭제와
이 보험증권들의 수혜자인 모든 피부양자들의 삭제를 유도함.

만약 plicyid는 단지 부분키이고 Policies는 약개체집합으로 모델링된다면??
Policies의 기본키는 <policyid, ssn>이 되며 Dependents 정의에는 ssn이 추가되어 아래처럼 됨.

 

 

---

뷰

 

뷰 (Views)

뷰는 일종의 테이블로서 이에 속해있는 행들이 실제로 데이터베이스에 저장되지 않고
뷰 정의(view definition)를 이용하여 필요한 대로 계산됨.

뷰는 새로운 질의나 뷰를 정의할 때 기본테이블(base table: 실제로 저장된 테이블)처럼 사용될 수 있음.

 

뷰에 대한 갱신을 제한하는 필요성

ActiveStudents : 적어도 하나의 클럽에 소속해 있으면서 평균 평점이 3보다 큰 값을 가진 학생들의 뷰.

이때, <Smith, smith@ee, Hiking, 1997>을 삭제하고 싶다면? Students 혹은 Clubs를 어떻게 변경해야 할까??
-> Students에서 행 <53688, Smith, smith@ee, 18, 3.2>을 삭제한다면
    뷰에서 <smith, smith@ee, Rowing, 1998>도 삭제됨
-> Clubs에서 <Hiking, 1997, Smith>을 삭제한다면
   뷰에서 <Smith, smith@math, Hiking, 1997>도 삭제됨

따라서 유일한 해결책은 뷰에 대한 갱신을 불허하는 것임.
삽입/삭제는 기본 테이블에서만 하자.