[공부]RelationalDataBase

Junwoo Ahn·2022년 10월 2일

DB공부

목록 보기

1/1

Relational Model이란?

Relational Model은 1970년 IBM 엔지니어였던 E.F.Codd의 논문에서 처음 소개되었고, Relational Algebra를 근간으로 하는 모델이다.

Entity and Relation

Entity (Set)
- Some identifiable thing that users want to track
- Customers, Computers, Sales, ...
Relation
- RDBMS products store data about entities in relations
- two-dimensional table that has following characteristics

Functional Dependency

하나의 observation(row)에서 특정 attribute(s)가 다른 attribute(s)를 결정하는(specify) 관계인 경우에 함수적 종속이 있다고 함
- Functional Dependency Rules
  1) Decomposition rule
  IF A -> (B, C), then A -> B, A -> C
  2) Union Rule
  IF A -> B and A -> C, then A -> (B,C)
Determinant
- 함수적 종속에서 화살표 기준 왼쪽에 있는 부분을 지칭하는 용어
- Composite Determinants
  - lefthandside가 하나보다 많은 속성들로 구성될 때 지칭하는 용어
- Determinant가 unique하다는 의미
  - Determinant를 제외한 모든 속성들을 determine하는 경우
  - 단지 Column 내의 unique한 values를 관찰하는 것 만으로는 모든 functional dependencies의 determinants를 찾을 수 없다.

Keys

A combination of one or more columns that is used to identify rows in a relation

Composite Key
- A key that consists of two or more columns
Candidate Key
- A key that determines all of the other columns in a relation
Primary Key
- A candidate key identifying rows in a relation
- may be a composite key, only one key per relation
Surrogate Key
- An artificial column added to a relation to serve as a primary key
- Has artificial values that are meaningless to users
- Ex) When the Primary Key contains too many columns.
Foriegn Key
- primary key of one relation that is placed in another relation to form a link between the relations
- Table JOIN 시 활용

Database Integrity

Domain integrity constraint : 속성값의 종류는 동일
- all of the values in a column are of the same kind
Entity integrity constraint : primary key : non-Null and unique value
- A primary key must have unique data values for every row in the table
- unique data values : column is NOT NULL, not allowing a Null value in any row
Refential integrity constraint : about value limits of foreign keys
foreign key로 있는 테이블의 속성값 집합이 primary key로 있는 속성값 집합의 부분집합이어야 하는 개념
- A statement that limits the values of the foreign key to those already existing as primary key values in the corresponding relation
(Example) :
SKUDATA (SKU, SKUDescription, Department, Buyer)
ORDERITEM (OrderNumber, SKU, Quantity, Price)
Where ORDERITEM.SKU must exist in SKUDATA.SKU

Modfication Anomalies

Item Number	Equipment Type	Acquisition Cost	Repair Number	Repair Date	Repair Cost
100	Drill Press	3500	2000	05-05	375
200	Lathe	4750	2100 (deletion anomally)	05-07	255
100	Drill Press	3500	2200	06-19	178
300	Mill	27300	2300	06-19	1975
100	Drill Press	3500	2400	07-05	0
100	Drill Press	3500 (update anomally)	2500	08-17	275

Deletion anomally
- RepairNumber가 2100인 2행의 데이터를 없앤다고 가정했을 때, 테이블의 유일한 정보였던 Equipment Type과 RepairCost의 정보도 함께 사라진다.
Insertion anomally
- 새로운 400이라는 ItemNumber를 갖는 Chain Saw를 추가하려한다고 하자. 하지만 AcquisitionCost에 대한 정보가 없으므로 문제가 발생
Update anomally
- 6번째의 AcquisitionCost를 5500으로 수정하는 경우, 2번과 4번 인덱스에 해당하는 AcquisitionCost도 함께 수정하지 않으면 문제가 발생
- 위의 테이블의 형광펜으로 표시한 부분처럼 data를 duplicates하는 테이블의 경우 update anomally에 민감하다.
- 이러한 inconsistencies를 가지는 경우 data integrity problems가 있다고 한다.

Normal Form

어떤 modification anomalies 혹은 문제에 관련된 것인지를 기준으로 분류됨

Source of Anomally Normal Forms Design Principles
Functional Dependencies 1NF, 2NF, 3NF, BCNF BCNF : every determinant is a candidate key

Multivalued Dependencies 4NF Move each multivalued dependency to a table of its own

Data constraints and oddities 5NF, DK/NF Make every constraint a logical consequence of candidate keys and domains

Source of Anomally	Normal Forms	Design Principles
Functional Dependencies	1NF, 2NF, 3NF, BCNF	BCNF : every determinant is a candidate key
Multivalued Dependencies	4NF	Move each multivalued dependency to a table of its own
Data constraints and oddities	5NF, DK/NF	Make every constraint a logical consequence of candidate keys and domains

1NF : primary key의 존재 여부에 대해서는 다양한 의견이 존재하는 상황
- A table that meets the set of conditions for a relation
- Has a defined primary key
2NF : 모든 nonkey 속성들이 모든 primary keys에 대해 dependent할 때
- primary key가 아닌 경우에 종속되는 것은 무관함
- nonkey가 primary key의 일부에 의해 종속되는 경우 위배
- Example
  StudentActivity (StudentID, Activity, ActivityFee)
  (StudentID, Activity) -> (ActivityFee)
  (Activity) -> (ActivityFee)
- To be a 2NF
  StudentActivity (StudentID, Activity)
  Activity (Activity, ActivityFee)
  와같이 두 개의 테이블로 나누어 표현해준다.
3NF : 2NF이고, non-key attributes가 another non-key attribues에 영향받지 않음
- Example
  StudentHousing (StudentID, Building, BuildingFee)
  (StudentID) -> (Building, BuildingFee)
  (Building) -> (BuildingFee)
- To be a 3NF
  위의 경우 non-key attribute인 Building이 BuildingFee의 determinant 역할을 하므로 3rd Normal Form이 되지 못한다. 이를 해결하기 위해서는
  StudentHousing (StudentID, Building)
  Building (Building, BuildingFee)
  기존 테이블에서는 새로운 테이블의 primary key 부분만 외래키로 지정해 남기고 나머지 attributes는 삭제해준다.
Boyce-Codd Normal Form
- 3NF이고, 모든 determinant가 candidate key인 경우
- Example
  Table : StudentAdvisor(StudentID, Subject, AdvisorNumber)
  Functional Dependencies
  - (StudentID, Subject) -> (AdvisorName)
  - (StudentID, AdvisorName) -> (Subject)
  - (AdvisorName) -> (Subject)
- 3NF 이지만 BCNF는 아님
  nonkey가 primarykey의 전체에 의해 결정되고(2NF),
  nonkey가 nonkey를 결정하지 않지만(3NF),
  AdvisorName은 Determinant이지만 단독으로 observation을 unique하게 결정하는 candidate key가 되지 못함(BCNF 위배)
- 3NF to BCNF
  StudentAdvisor(StudentID, AdvisorName)
  AdvisorSubject(AdvisorName, Subject)
  - Both are in BCNF

Multivalued Dependency

Occurs when a determinant is matched with a particular set of values:
Employee ->-> Degree
Employee ->-> Sibling
PartKit ->-> Part

Multivalued dependency의 determinant는 절대 primary key가 될 수 없다.
Solution
Multivalued dependencies are not a problem, if they're in a separate relation.
Thus, always put multivalued dependencies into their own relation!
결과적으로 Multivalued Dependency까지 없는 relation은 4NF이다.

Multivalued Dependencies Examples

언제 이러한 문제가 발생할 지 고민해보자.
지금 다루는 예제에서는 한 명의 관리자가 특정 속성의 여러 값들을 동시에 관리하는 등의 경우에 동일한 BuyerName에 대해 SKUmanaged와 CollegeMajor에 등장하는 값이 여러 개가 될 수 있는 점에서 출발한다.

BuyerName	SKUmanaged	CollegeMajor
Pete Hansen	100100	Buisiness Administration
Pete Hansen	100200	Buisiness Administration
Nancy Meyers	101100	Art
Nancy Meyers	101100	Info Systems
Nancy Meyers	101200	Art
Nancy Meyers	101200	Info Systems
Cindy Lo	201000	History
Cindy Lo	202000	History
Jenny Martin	301000	Buisiness Administration
Jenny Martin	301000	English Literature
Jenny Martin	302000	Buisiness Administration
Jenny Martin	302000	English Literature

Multivalued Dependencies
- BuyerName ->-> SKU_Managed
- BuyerName ->-> CollegeMajor

위 테이블에서 Multivalued Dependencies를 없애보자.
그러기 위해서는 Multivalued Dependency로 나타난 변수끼리 테이블을 분리해주어야 한다.

ProductBuyerSKU (BuyerName, SKUmanaged)
ProductBuyerMajor (BuyerName, CollegeMajor)

Database Design Using Normalization

하나 혹은 이상의 데이터 테이블이 있을 때, 데이터를 받은 그대로 저장해야 할지 혹은 저장을 위해 형태를 변형해야 할지 판단해보자.

Table Structure를 평가하는 가이드라인

1) Count rows and examine columns
2) Examine data values and interview users to determine:
- Multivalued Dependencies
- Functional Dependencies
- Candidate Keys
- Primary Keys
- Foreign Keys
3) Assess validity of assumed referential integrity constraints

Counting Rows in a Table : 총 몇개의 행으로 되어 있는지 확인하기
SELECT COUNT(*) AS SKU_DATA_NumRows
FROM SKU_DATA;

Examining the Columns : 상위 5개만 불러오기
SELECT *
FROM SKU_DATA
LIMIT 5;

Checking Validity of Assumed Referential Integrity Constraints

Given two tables with an assumed foreign key constraint:
SKU_DATA (SKU, SKU_Description, Buyer)
BUYER (Buyer, Department)
Where SKU_DATA.Buyer must exist in BUYER.Buyer
"An empty set for the query result below indicates that no foreign key values violate the foreign key constraint"

SQL Query

SELECT Buyer
FROM SKU_DATA
WHERE Buyer NOT IN
	(SELECT SKU_DATA.Buyer
	 FROM SKU_DATA, BUYER
     WHERE SKU_DATA.BUYER = BUYER.Buyer);

Type of Database

Updatable or Read-only? is updatable?

Updatable Database의 경우 Real-time database를 의미한다.
이 경우에 Boyce-Codd Normal Form의 Table로 바꾸어주어야 한다.
주로 기업의 Operational Databases에 해당하고, 예시로 Online Transaction Processing (OLTP) system이 있다.
이러한 유형의 DB를 구축하려면 modification anomalies와 inconsistent data를 고려해야 한다. 정규화 원칙 또한 고려해야 한다.

Read-only Database의 경우 BCNF table이 필요하지 않을 수 있다.
이 형태는 분석을 위해 정지시킨 DB로 바뀌지 않는 table이다. 또한 분석을 위해서는 여러 Table을 JOIN해야 하는데, BCNF를 만족시키기 위해 더 많은 Table을 만들 수록 JOIN이 복잡해져 쿼리 시간이 증가하는 테이블 수와 쿼리 시간 사이의 trade-off 관계도 고려가 필요하다. 이로 인해 애플리케이션의 속도가 느려질 수도 있다.

Junwoo Ahn

[공부]RelationalDataBase

DB공부

Relational Model이란?

Entity and Relation

Functional Dependency

Keys

Database Integrity

Modfication Anomalies

Normal Form

Multivalued Dependency

Multivalued Dependencies Examples

Database Design Using Normalization

Table Structure를 평가하는 가이드라인

Counting Rows in a Table : 총 몇개의 행으로 되어 있는지 확인하기

Examining the Columns : 상위 5개만 불러오기

Checking Validity of Assumed Referential Integrity Constraints

SQL Query

Type of Database

0개의 댓글

관련 채용 정보