본 게시글은
서울대학교 데이터사이언스대학원 이상원 교수님의
데이터사이언스 응용을 위한 빅데이터 및 지식관리시스템 수업을
학습을 목적으로 재구성하였습니다
중간고사를 직전에 두고
오늘의 수업은 DB Design에 관한 내용이었다
주요하게 ER diagram과 정규화에 대해 다룬다
가보작오
ER Data Model에 대해서 알아보자
ER 모델은 DB 설계의 한 방법론이다
보통 ER model과 Relational model을 착각하는데
ER은 DB 설계의 방법론이지
database 혹은 query language가 아니다
ER model은 database의 schema를
논리적으로 디자인하는 방식이며
high level한 그림의 형식으로 표현하여
직관적인 형태이다
ER은 high level model인 반면
뒤에서 배울 정규화(Normalization)은
low level로 사람이 이해하기엔 조금 어렵다
우리가 앞에서 배웠던 DB의 예시들은
다 예시들이라서 table 개수도 별로 없고
column들도 간단했겠지만
실제로 사용되는 DB의 table은 개수가 어마어마하게 많고
그 관계도 굉장히 복잡하다
따라서 처음에 DB를 만들 때 부터
어떤 table이 필요하며
어떤 column이 pk, fk인지를 명확하게 설계해야한다
이를 위해 가장 처음에 어떤 것들이 필요할 것이라는
requirement를 정의하는데,
이 requirement를 기반으로 ER 다이어그램을 그리고
DB를 개념적으로 설계한다
이 ER 다이어그램을 그릴 때는 보통
UML이나 ORM을 사용한다
이를 DB의 개념적 설계(conceptual design)이라고 한다
그 다음 실제로 데이터를 저장하는 시스템은 RDBMS이기 때문에
RDB 스키마로 정확하게 만들어야한다
이게 DB의 논리적 설계(logical design)이다
마지막으로 실제 DB에서
page의 크기, index, table의 partitioning 여부 같은 것들을
정의해주는 것이
DB의 물리적 설계(physical design)이다
개념적 설계에 대해서 자세하게 알아보자
보통 자주 사용하는 것이 ER Model이다
어떤 Entites를 DB에 담고, 어떤 Relationships들을 담고
어떤 constraint를 만족시킬지를 설계하는 것이다
좀 더 구체적으로 말하면
어떤 table을 만들고 그 안에 어떤 attribute들을 담을 것이며
이들이 어떤 integrity constraint를 지키게 할 것인지
어떤 buiness rules들을 따를 것인지를 설계하는 것이다
이러한 개념적인 것들을 diagram으로 표현한다
또한, ER diagram을 relational schema로 표현하는건
요즘은 semi-automatic이라고 한다
이게 ER diagram의 예시이다
각 사각형이 table이고 table의 이름과
각 attribute들과 이들의 IC가 명시되어있다
Entity는 실세계의 object를 말한다
그리고 각각의 Entity는 attribute를 갖고 있다
위 ppt slide에서 예시를 살펴보자
Employees라는 entity가 있다
employee의 집합은 entity set이고
같은 entity set에 있는 모든 entites들은 같은 attribute를 갖는다
Employees는 ssn, name, lot, phones라는 attribute를 갖고있고
ssn은 key
그 다음에 각 attribute들은 속성이 가질 수 있는 범위인 domain을 갖고있다
위 Phones의 경우 *로 표기가 되어있는데
이는 복수 개가 있을 수 있다는 뜻이다(multiple values)
원래 relational model에서는 한 개의 attribute가
복수 개의 데이터를 가지는 것을 금지하지만
ER 모델에서는 가능하다고 한다
이제 Relationship에 대해서 알아보자
위 ppt slide에서 관계성은 다이아몬드로 표시하고
사각형은 각 Entity들이다
관계성도 본인의 attribute를 가질 수 있다
또한 위의 Employees와 Reports_to처럼
동일한 entity끼리도 관계 표현이 가능하다
relationships은 두 entity 사이의 관계이기때문에
binary relationship인데(동일한 entity끼리의 관계도 마찬가지)
ternary도 존재할 수 있다
또한 하나의 entity가 두 개 이상의 relationship에 참여할 수 있다
위 예시는 M:N 관계를 ER 다이어그램으로 그린 것이다
Employees와 Department는
Many to Many 관계이다
왜냐하면 각각의 직원들이 여러 부서에 속할 수도 있기 때문이다
앞에서 잠깐 언급했던 ternary relationship이다
어느 직원이 어느 부서에서 일을 하는데
어디에서 일을 하는지까지 표현하면
위와같이 표현할 수 있다
하지만 일반적으로 binary까지만 많이 쓴다
위에서 본 works_in 관계같은 경우에는
employee는 여러 개의 department에 속할 수 있다
이는 M:N(다대다) 관계이다
반면에 각각의 부서는 딱 한명의 manager만 가질 수 있다
따라서 이는
department -> manage방향으로 Key constraints를 가진다고 할 수 있다
key constraint의 표기는 위와같이 화살표로 표시한다
다음으로 participation constraint를 알아보자
위 ppt slide에서 가는 실선과 굵은 실선이 있다
모든 employees는 manage에 참여할 수도 있고 아닐 수도 있다
하지만 모든 department의 엔티티는 반드시 manage에 참여해야한다
왜냐하면 모든 부서에는 반드시 부서장이 있기 때문이다
또한 모든 employees는 다 works_in에 참여해야한다
따라서
department -> manage로 total participation,
employees -> works_in로 total participation,
employees -> manage로 partial participation임을 알 수 있다
total participation은 굵은 선으로
partial participation은 얇은 선으로 표기한다
class 계층 구조에 대해 살펴보자
위 ppt의 예시는 Employee 중에서
일반 정규직과 시간제를 구분한 모습이다
정규직도 시간제로 모두 employee이므로
employee entity를 상속받아 사용하여
Hourly_emps와 contract_emps가 subclass 된다
이렇게 되면 attribute도 그대로 상속이 되게 되고
만약 각 subclass에서 추가로 필요한 attribute가 있다면
추가로 설정할 수 있다
oracle도 ORDBMS 중 하나이기 때문에
객체지향의 개념도 지원한다
따라서 실제로 이런 식으로 클래스 계층 구조를
RDB의 형태로 define해서 사용할 수 있다
따라서 개념적 설계에 대해 다시 정리해보자면
어떤 개념들을
entity인지 attribute인지
또한 그 관계가 binary인지 ternary인지 등을
결정해서 나타내는 것을 말한다
ER 모델은 주로 DB 설계 용도로 사용한다
ER 모델을 직접 지원하는 DBMS도 개발 시도를 했었다고한다
논리적 설계에 대해서 살펴보자
entity set은 2차원의 table이다
근데 아까 phones는 복수의 데이터를 가질 수 있다고 했다
이런 경우 실제 Table에서 어떻게 표현할 수 있을까?
VARCHAR로 설정해서
; 혹은 |와 같은 구분자를 둬서
그냥 string 형태를 사용자가 구분할 수 있을 것이다
또 string 타입으로 저장해서 구분하고 하는게 귀찮다면
RDB마다 차이가 있지만 array type이 있다고 한다
위 ppt에 나와있는 것처럼
character varying(100)[]와 같이 정의할 수 있으며
배열형태 최대 100개까지의 값을 저장할 수 있는
character array이다
우리가 개념적 설계에서 봤던 ER모델을 이용해서
table로 변경해보자
M대 N의 관계를 표현하기 위해서는
works_in이라는 table을 새로 만들어야한다
각 관계가 있는 entity에서
ssn과 did를 works_in table의 attribute로 사용하고
각각은 fk가 된다
하지만 위 ER diagram을 저런식으로 table로 설계하는게 최선일까?
RDB의 표현을 ER diagram으로 완벽하게 할 수도 없고
ER diagram의 표현을 RDB로 완벽하게 할 수도 없다고 한다
쌍방 100%가 아니라고 한다
그럼 아까 봤듯이
1대1, 1대다, 다대1, 다대다와 같이
다양한 관계들이 있는데
이걸 어떻게 RDB에 표현하나?
뭐 우선 이런식으로 표현하는 방법이 있다고한다
Manage의 경우
department와 employee와 1대다 관계였다
(partial participation)
따라서 ssn과 did를 가져와서
fk로 설정해주는데
여기서 ssn은 nullable하게 설정을 해줘야한다
또한 아래의 Dept_Mgr와 같이
한 개의 table로 그냥 합쳐서 표현해 줄 수도 있다
그렇다면 employee와 works_in
department와 works_in의 관계처럼
M:N의 관계인 total participation의 관계는 어떻게 표현할 수 있을까
모든 부서는 manager를 갖고 있어야 하므로
앞에서 정의해준 Dept_Mgr에서
ssn을 NOT NULL로 해준다
또한 fk가 삭제될 때도 어떻게 액션을 취할지 정의해준다
하지만 위와 같은 방법은 너무 복잡해서 좋은 방법은 아니다
그렇다면 이제 앞에서 봤던
class의 계층구조를 RDB table로 만들어보자
각각의 class별로 3개의 table을 만들 수도 있고
subclass들을 employee table에 집어넣을 수도 있다
거기에 hourly_enployee라는 테이블을 만들고
fk형태로 연결할 수도 있다
아니면 그냥 다 employee table이라는
큰 테이블에 만들어서 정보들을 추가할 수도 있다
DB를 설계할 때
table과 column들의 네이밍은
NL2SQL의 성능 향상에 중요한 요소이다
column name이나 metadata들을
llm이 좀 더 잘 알 수 있도록 명확하게 규정하는 것이 중요하다
마지막으로 정규화에 대해 알아보자
정규화의 개념은 오른쪽 위 사진의
박사님께서 제안하신 개념이다
정규화는 우리 교수님께서 설명하시길
DB 설계에 대한 관점이 좀 다른데
motivation은 "데이터 중복을 줄이자"라고 한다
따라서 본 수업에서
그렇게 깊이 있게는 다루지 않는다고 한다
다른 DB 수업이나 혹은 정보처리기사 시험에서
정규화는 굉장히 중요하게 다루는 내용이라서
깊게 다루실 줄 알았는데
신기하게도 교수님이 간단하게 보고 넘어가자하셔서 놀라웠다..
아무튼 위 table 예시를 보면
rating과 hourly_wages에 중복이 존재한다
같은 데이터인데 여러 row에 여러 번 들어가면
space의 낭비가 생기고
update에 불편한다는 것인데
이걸 table을 쪼갬으로써 해결하고자 한 것이
바로 정규화이다
위 예시에서 rating과 hours_wages가 계속해서 반복됐으니
이렇게 table을 decomposition해서
최대한 중복을 줄이자는 것이다
위와 같이 1개의 테이블을 쪼개는 것을
decomposition 혹은 normalization이라고 한다
하지만 이런 decomposition에는 당연히 trade off가 존재한다
중복이 발생할 수 있는 table을 최대한 쪼개서
space의 낭비를 줄이자는 것이 정규화의 핵심인데
당연히 table을 많이 쪼갤 수록 join이 증가하게 된다
이러한 join의 증가는 쿼리 성능을 저하시키게 된다
따라서 사실상 decomposition을 하지 않는게
더 쿼리 성능에는 좋을 수 있다
그 뒤 information과 dependency는 복잡한 개념이라
생략한다고한다(...)
아무튼 저러한 문제들을 최소화하며
최대한 잘 table을 쪼개는 것이 정규화의 핵심이다
잘 쪼개는 핵심 방법 중에 하나가
functional dependency(함수적 종속성)을 고려하는 것이다
이는 사용자가 DB를 설계할 때
추가적으로 정보를 주어야하는 것인데
만약 어떤 튜플에서
column X의 값이 같으면 column Y의 값이 항상 같다면
X -> Y
즉, X는 Y를 함수적으로 종속시킨다고 말한다
따라서 pk는 모든 column에 대해서 함수적으로 종속시킨다고 할 수 있다
위 테이블의 예시에서는
AB -> C가 함수적 종속 관계라고 볼 수 있다
일종의 integrity constraint의 개념이다
위는 정규화의 종류이다
1형, 2형, 3형, BC, 4형, 5형까지 존재한다
이 전체의 과정을 정규화라고 한다
시험때문에 급하게 마무리한
이번 강의 내용 정리는 여기까지..
중간고사 화이팅 ㅎㅎ