[데이터베이스] #2 관계형 데이터 모델

목차

1. 관계형 데이터 모델
   1. 속성
   2. 관계 스키마 및 인스턴스
   3. 관계형 데이터베이스
   4. 대학 데이터베이스 예제
   5. 키
   6. 참조 무결성
   7. 데이터 사전
2. 샘플 대학교 데이터베이스
3. 관계 대수
   1. Select: σ
   2. Project: Π
   3. Union: ∪
   4. Set difference: ㅡ
   5. Cartesian product: ×
   6. Rename: ρ
   7. 관계 대수식
4. 추가 관계 대수
   1. Assignment: ←
   2. Set intersection: ∩
   3. Natural join: ⋈
   4. Join
   5. Inner Join
   6. Outer Join
   7. 나눔 연산
   8. 관계 대수 정리
5. 예제
6. +) 연습문제

---

1. 관계형 데이터 모델

관계 = 테이블

터플 = 레코드

속성 = 칼럼

속성

도메인

• 속성 값으로 허용할 수 있는 값의 집합
• 도메인에 속하는 모든 값은 원자(atomic) 값을 가져야 함
• default = null

관계 스키마 및 인스턴스

 

관계 스키마: student(sID, name, gender, deptName, year, GPA, totalCredit)

관계 인스턴스: 5개 터플

관계형 데이터베이스

관계 + 제약조건으로 구성

무결성 제약(integrity constraint)

• 키 제약 - 주 키는 중복된 값을 가지지 못함
• 엔티티 제약 - 주 키는 널 값을 가지지 못함
• 참조 무결성 제약 등등

대학 데이터베이스 예제

primary key - 속성 밑줄

• course 관계의 cID
  • 주 키는 항상 유일
• teach 관계의 주 키는 4개 속성의 연결
  • 연결한 전체 값은 항상 유일

무결성 제약 - 화살표 표시

• teaches 관계의 cID 속성은 course 관계의 주 키 속성인 cID 속성을 참조하는 외래 키(foreign key)

관계형 데이터베이스에는 관계 간의 상하관계 x, 순서 x

키

• 슈퍼 키: 각 터플를 구분 가능하게 하는 속성의 집합으로 각 집합은 유일
• 후보 키: 가장 적은 수의 속성을 가지는 키
• 주 키: 하나 이상의 후보 키가 존재하면 주 키 지정 필요

참조 무결성

특정 속성에 나타나는 모든 값은 반드시 다른 속성에도 나타나야 한다는 것

특정 속성에 나오는 모든 값은 다른 속성 값의 일부분

• 참조하는 속성 값은 임의의 값을 지닐 수 없다
• 참조하는 속성의 주 키 값을 가져야 한다
• null도 가능하다

참조받는 터플은 참조하는 터플을 가지지 않아도 된다. 이는 참조 무결성 제약과 무관하다.

데이터 사전

• 데이터베이스 시스템이 내부적으로 관리하는 데이터 장소
• 메타 데이터(데이터에 대한 데이터)를 관리
• 사용자는 SQL 언어를 활용하여 데이터 사전에 속하는 데이터에 접근 가능

 

---

2. 샘플 대학교 데이터베이스

 

---

3. 관계 대수

6개의 기본적인 연산 존재

• Select: σ
• Project: Π
• Union: ∪
• Set difference: ㅡ
• Cartesian product: ×
• Rename: ρ

데이터베이스 시스템 내부에서 사용되는 언어이며 사용자에게 직접 보이지는 않음

사용자는 SQL 언어를 통해 관계 대수를 사용 가능

Select: σ

선택 연산

기호 이름: sigma

\(σ_p(r)\)

σ: 선택 연산, p: 선택 조건, r: 관계

p의 조건은 명제 논리이며, 각 항은 and(∧), or(∨), not(ㅡ)으로 연결이 가능

Project: Π

투영 연산

기호 이름: pi

\(Π_{A_1, A_2,..., A_k}(r)\)

관계 r에서 보고자 하는 속성 A만을 보여주는 연산

결과 관계에는 동일한 터플이 나타나지 않음

📌 σ vs. Π 선택 연산은 터플 단위로 원하는 결과를 구함 투영 연산은 속성 단위로 원하는 결과를 구함

Union: ∪

합집합 연산

일반적인 합집합 정의와 동일

첫 번째 관계에서 두 번째 관계의 터플을 모두 더한 터플의 집합

집합 연산을 위해 터플의 속성 개수가 동일해야 함

대응되는 속성의 도메인이 상호 호환적이어야 함

Set difference: ㅡ

차집합 연산

일반적인 차집합 정의와 동일

첫 번째 관계에서 두 번째 관계의 터플을 빼고 남은 터플의 집합

Cartesian product: ×

카티시안곱(곱집합 연산)

가능한 모든 터플의 조합을 결과 관계로 산출

두 개의 입력테이블에 동일 속성이 존재하면 자연 조인(natural join) 연산이 되기 때문에 동일한 속성 이름이 없도록 rename 해줘야 함

곱집합 연산의 결과 속성의 개수는 m + n (두 속성 개수의 합)

터플 개수는 x * y (두 터플 개수의 곱)

Rename: ρ

재명명

기호 이름: rho

단순히 테이블 이름이나 속성 이름을 변경하는 연산

\(ρ_{X(A_1, A_2,..., An)}\)

X는 관계명, A는 속성명

관계 대수식

관계 대수식의 결과는 관계이므로 관계 대수식의 합성이 가능

이를 통하여 복잡한 질의문 작성 가능

 

---

4. 추가 관계 대수

• Assignment
• Set intersection
• Natural join
• Outer join
• Division

기본 관계 대수로 표현 가능

Assignment: ←

할당 연산

복잡한 질의문을 작성할 때 중간 결과 표현을 임시로 저장 가능

재명명 연산자를 함께 사용하면 중간 결과의 관계 및 속성 이름을 원하는 것으로 변경하면서 할당 가능

Set intersection: ∩

교집합 연산

\(r ∩ s = r - (r - s)\)

일반적인 교집합 정의와 동일

Natural join: ⋈

자연 조인

\(r ⋈ s\)

입력 테이블에 대하여 1. 카사디안곱 2. 조인 조건 이용하여 선택 연산 3. 조인 조건에 언급된 속성을 이용하여 투영연산을 수행

선택 연산과 투영 연산 방법 차이에 의하여 3 가지 내부 조인(inner join)으로 구분

겹치는 속성의 값이 같은 가능한 모든 터플을 연산함

지연 조인은 결합성과 교환성이 있음 → 데이터베이스 시스템이서 질의어 최적화 과정에서 사용

\((r⋈s)⋈t=r⋈(s⋈t)=(s⋈t)⋈r\)

Join

• outer join 조인 속성에 서로 관련되지 않는 값이 없는 터플도 결과 관계에 포함
• inner join
  • theta join 일반적인 조인
  • equi-join 세타 조인 중 조인 조건이 동등 조건만으로 구성
  • natural join 동등 조인 중 조인 조건 속성이 조인 결과에 한 번만 나옴

Inner Join

theta join

일반적인 조인

ex) \(r ⋈_{r.a > s.a} s\)

equi-join

세타 조인 중 조인 조건이 동등 조건만으로 구성

ex) \(r ⋈_{r.a=s.a} s\)

natural join

동등 조인 중 조인 조건 속성이 조인 결과에 한 번만 나옴

ex) \(r ⋈ s\)

Outer Join

⟕

왼쪽 외부 조인

왼쪽 관계의 모든 터플이 결과에 포함

조인 값이 없으면 null

⟖

오른쪽 외부 조인

오른쪽 관계의 모든 터플이 결과에 포함

조인 값이 없으면 null

⟗

양쪽 외부 조인

양쪽 관계의 모든 터플이 결과에 포함

조인 값이 없으면 null

나눔 연산

공통 속성이 제거된 속성을 가짐

결과 관계는 S관계의 공통 속성에 속하는 모든 터플을 가지는 R 관계 터플

나눔 연산의 의미는 모든 조건을 포함하는 질의를 구하는 것임

관계 대수 정리

• σ: select(선택)
• Π: project(투영)
• ∪: union(합집합)
• −: set difference(차집합)
• ×: Cartesian product(곱집합)
• ρ: rename(재명명)
• ∩: intersection(교집합)
• ←: assignment(할당)
• ⋈: natural join(자연 조인)
• ⟗: outer join(외부 조인)
• ÷: division(나눔)

 

---

5. 예제

account.balance에 10 40 20의 값을 지닌 터플이 하나씩 있다고 가정하자

account 자기 자신에 대하여 카사디안 곱을 구하면 <10, 10>, <10, 40>, <10, 20> <40, 10>, <40, 40>, <40, 20> <20, 10>, <20, 40>, <20, 20>

왼쪽 값이 오른쪽 보다 작은 터플을 선택하면 <10, 40>, <10, 20>, <20, 40>

account.balance에서 세 터플의 왼쪽 값에 대한 차집합을 구하면

{10, 40, 20} - {10, 10, 20} 이므로 40이 나온다

 

---

+) 연습문제

기호 모음: Π σ ⋈ ρ ×

1.

Consider the schema below. company(companyName, asset, netIncome, locationCity) The underline attribute denotes the primary key of the relation. Is it possible for a company to be located more than one city? Justify your answer.

 

답

불가능하다. 회사명은 주 키이므로 한 터플에 하나가 존재하며 그 값은 유일하다.

2.

Considerthe following relational schema.

• employee(personName, city)
• works(personName, companyName, salary)
• company(companyName, city)
• manager(personName, managerName) Give an expression in the relational algebra to express each of the following queries. Note that we have following referential integrity constraints:
• works.personName → employee.personName
• works.companyName → company.companyName
• manager.personName → employee.personName
• manager.managerName → employee.personName

1. Find the names of employees who work for First Bank.
2. Find the names and cities of residence of all employees who work for First Bank.
3. Find the names and cities of residence of all employees who work for First Bank and earn more than $40,000 a year.
4. Find the names of all employees who live in the same city as the company for which they work.
5. Find the employee names who do not work for First Bank.
6. Find the names of all employees who live the same city as do their managers.
   [hint - employee.personName과 manager 간에 두 번 조인(한 번은 personName으로, 다른 한 번은 managerName으로)이 필요하다]

답

1. \(Π_{personName}(σ_{companyName="First Bank"}(works)\)
2. \(Π_{personName, city}(σ_{companyName="First Bank"}(works ⋈ employee))\)
3. \(Π_{personName, city}(σ_{companyName="First Bank"∩salary>40000}(works ⋈ employee))\)
4. \(Π_{personName}(works ⋈ employee ⋈ company)\)
5. \(Π_{personName}(works-σ_{companyName="First Bank"}(works))\)
6. \(Π_{personName}(employee ⋈ manager ⋈ ρ_{employee(managerName, city)})\)

3.

Consider the following relational schema.

employee(empno, name, age) book(isbn, title, author, publisher) loan(empno, isbn, date)

Write the queries in relational algebra. Note that we have following referential integrity constraints:

loan.empno→employee.empno loan.isbn→book.isbn

1. List employee names with book titles he/she have borrowed.
2. Find book titles that employees over 50 years old have borrowed.
3. Find the names of employees who have borrowed any books published by "McGraw-Hill".
4. Find the names of employees who have borrowed all books published by "McGraw-Hill".

 답

1. \(Π_{name, title}(loan⋈book⋈employee)\)
2. \(Π_{title}(loan⋈book⋈σ_{age>50}(employee))\)
3. \(Π_{name}(loan⋈σ_{publisher="McGraw-Hill"}(book)⋈employee)\)
4. \(Π_{name}(employee⋈loan\%σ_{publisher="McGraw-Hill"}(book))\)