스키마 설계 방법론

• 이게 정말 어려운 것 같다. 어떻게 하면 사용자의 요구사항에 맞게 데이터 베이스 스키마를 설계 할 수 있을까? 오늘은 데이터 베이스 개론이라는 책에 나와 있는 그 방법론에 대해 공부해 보았다.
• 일단 관계형 데이터베이스 모델을 설계할때는 보통 두가지 방법으로 설계 하는 것 같다. E-R Model And Relation 변환 규칙 또는 정규화를 이용한 데이터베이스 설계 방식

요구 사항 분석

• 해당 조직에서 어떤 사람들이 데이터베이스를 요구하는 지 조사한다.
• 사용자의 범위가 결정되면 조직에서 해당 사용자가 수행하는 업무를 조사한다.
• 조사가 끝난다면 결과로 요구 사항 명서세로 보통 문서화된다.
• 이 과정에서 올바르게 설계 되어야 후에 고생하지 않을 확률이 높으므로 여기서 시간이 오래걸리더라도 오랜시간 검토해보라고 말한다. 사실 이게 맞는 말이다. 데이터 베이스를 응용프로그래머들이 이용할텐데, 설계가 잘못되어 있다면 데이터베이스를 바꾸는 일이 일어날 것이고, 그것은 결국 프로그래머들의 코드도 바뀔 수 있다는 이야기가 된다.

도출된 요구사항

1. Roach 마트에 가입하려면 고객은 회원아이디, 비밀번호, 나이, 직업을 입력해야한다.
2. 가입한 회원에게는 등급과 적립금이 부여된다.
3. 회원은 회원아이디로 식별한다.
4. 상품에 대한 상품번호, 상품명, 재고량, 단가정보를 유지해야 한다.
5. 상품은 상품번호로 식별한다.
6. 회원은 여러 상품을 주문할 수 있고, 하나의 상품을 여러 회원이 주문할 수 있다.
7. 회원이 상품을 주문하면 주문에 대한 주문번호, 주문수량, 배송지, 주문일자 정보를 유지해야 한다.
8. 각 상품은 한 제조업체가 공급하고, 제조업체 하나는 여러 상품을 공급할 수 있다.
9. 제조업체가 상품을 공급하면 공급일자와 공급량 정보를 유지해야 한다.
10. 제조업체에 대한 제조업체명, 전화번호, 위치, 담당자 정보를 유지해야 한다.
11. 제조업체는 제조업체명으로 식별한다.
12. 회원은 게시글을 여러개 작성할 수 있고, 게시글 하나는 한 명의 회원만 작성할 수 있다.
13. 게시글에 대한 글번호, 글제목, 글내용, 작성일자 정보를 유지해야 한다.
14. 게시글은 글 번호로 식별한다.

개념적 설계

• 요구사항 중 데이터베이스에 저장해둘 필요가 있다고 판단되는 데이터 요소를 추출하고, 데이터 요소간의 관계를 파악하여 이를 표현한 것이다. 일반적으로 개념적 데이터 모델로 E-R 모델식의 표현법을 많이 사용한다고 한다.
• 보통 명사를 찾는게 좋다고 한다. 그러면 우리가 적어놓은 1~3 까지에서 Entity 로 뽑을 만한 요소들이 무엇이 있을까?
  • 1. Roach 마트에 가입하려면 고객은 회원아이디, 비밀번호, 나이, 직업을 입력해야한다.
• 근데 여기서 보면 마트와 고객은 따로 Entity 로서 추출해낼 수 있지만 회원 아이디, 비밀번호, 나이, 직업은 Attribute 가 된다.
  • 2. 가입한 회원에게는 등급과 적립금이 부여된다.
  • 3. 회원은 회원아이디로 식별한다
• 2 번도 등급의 경우 따로 분리할 수 있겠다. 만약 등급에 따른 적립금이 퍼센티지가 있다면 등급 테이블의 주 키를 등급으로 두고, 적립금을 속성 해당 등급에 종속시키면 될것이다. 그리고 고객마다 해당 등급을 외래키로 가지며, 적립금에 대한 퍼센티지는 등급 테이블에서 참조하면 될것이다.
• 3 번은 고객 테이블의 unique 키를 회원아이디로 두어라 라고 말하고 있다.

추출 후

일단은 위처럼 요구사항 명세에 적혀있는 부분들을 개체로 빼낸 상태에서 표를 만들었다.

관계 추출

• 이제 여기서 관계를 추출해 내야한다. 어떤 관계를 가질 수 있는지를
  • 6. 회원은 여러 상품을 주문할 수 있고, 하나의 상품을 여러 회원이 주문할 수 있다.
  • 위의 문장이 상품과 회원사이에 N:M 의 관계를 가질 수 있음을 나타내고 있다.
    • 회원과 상품은 모두 주문이라는 하나의 개체에 선택적으로 참여하고 있다. 그러므로 주문이라는 개체에 상품과 회원을 인지할 수 있는 어떠한 속성이 추가되야 할것이다.
  • 8. 각 상품은 한 제조업체가 공급하고, 제조업체 하나는 여러 상품을 공급할 수 있다.
    • 여기서 제조업체와 상품의 관계는 1:N 임을 알 수 있다.
  • 12. 회원은 게시글을 여러개 작성할 수 있고, 게시글 하나는 한 명의 회원만 작성할 수 있다.
    • 여기서 회원과 게시글의 관계는 1:N 임을 알 수 있다.

논리적 설계

• 논리적 설계는 위의 개념적 설계에서 도출된 E-R 모델(혹은 다른 방법으로 표기된 관계도) 를 통해서 논리적 스키마를 설계하는 단계이다.
• 여기서 설명하는 방식을 따라서 개념적 설계로 도출된 모델을 논리적 스키마로 바꿔볼 것이다

도출된 E-R 모델을 Relation 으로 변환하라.

상품(상품번호(PK), 상품명, 재고량, 단가)
고객(고객번호(PK), 이름, 등급, 주소)

• 명심할 점은 우리는 아직 어떤 DBMS 로 구현할지 정하지 않았다는 거다!

N:M 관계는 릴레이션으로 변환한다.

주문(주문번호(PK), 고객번호(FK), 상품번호(FK), 주문수량)

1:N 관계는 외래키로 표현한다.

제조업체(제조업체명(PK), 전화번호, 위치, 담당자)
상품(상품번호(PK), 상품명, 재고량, 단가, 제조업체명(FK), 공급 수량)

• 릴레이션 규약상 N 측 기본키를 외래키로 이용할 수 없다. 외래키가 다중값을 가져서 규약에 위배된다.

1:1 관계는 외래키로 표현한다

• 1:1 관계에서는 외래키를 서로 주고받을 수 있다.
  • 이 부분은 음 나한테 귀속된 아이템은 내 정보를 지니고, 난 아이템 정보를 지니고 간단하게 이정도로.. 아이템이 1:N 이지 않나요? 이러면 그게 맞는말인데, 그니까 문맥상으로 저런 느낌이란거다.
  • 실제 예시를 찾아보면 더 쉽게 이해할 수 있을 것이다.
• 하지만 똑같이 한곳의 키만 주고 받을 수 잇다. 이런 경우는 자신이 잘 판단하자!

주의할 점

• 릴레이션이 많이 늘어날 수록 DBMS 에 가해지는 부담이 클 수가 있다. 따라서 불필요한 릴레이션을 생성한 것이 아닌지 꼭 생각하면서 설계해야 한다. 솔직히 처음 단계부터 이번 단계까지가 정말 중요한 단계라고 생각한다.

DB 구현체 고르기

• 나는 MySQL 을 사용할 것이다. 그렇다면 우리의 릴레이션의 추상화 해둔걸 직접 MySQL 에 맞게 구현해보자!

고객(고객번호(PK), 이름, 등급, 우편번호, 주소)
주문(주문번호(PK), 고객번호(FK), 상품번호(FK), 주문수량)
제조업체(제조업체명(PK), 전화번호, 위치, 담당자)
상품(상품번호(PK), 상품명, 재고량, 단가, 제조업체명(FK), 공급 수량)

create table Product(
	id VARCHAR(20),
	name VARCHAR(20),
	count INT,
	prince INT,
	company_id VARCHAR(20),
	supply_quantity INT,
	CONSTRAINT product_id PRIMARY KEY(id),
	CONSTRAINT FOREIGN KEY(company_id) REFERENCES Company(id)
);

create table Customer(
	id VARCHAR(20),
	name VARCHAR(20),
	grade VARCHAR(10),
	adresss VARCHAR(30),
	CONSTRAINT customer_id PRIMARY KEY(id)
);

create table Orders(
	id VARCHAR(20),
	customer_id VARCHAR(20),
	product_id VARCHAR(20),
	count int,
	CONSTRAINT orders_id PRIMARY KEY(id),
	CONSTRAINT FOREIGN KEY(customer_id) REFERENCES Customer(id),
	CONSTRAINT FOREIGN KEY(product_id) REFERENCES Product(id)
);

create table Company(
	id VARCHAR(20),
	phone_number VARCHAR(20),
	address VARCHAR(30),
	manager VARCHAR(20),
	CONSTRAINT company_id PRIMARY KEY(id)
);

마무리

• 하지만 이건 완성된게 아니다 이제 정규화를 이용해서 이상현상이 일어날 수 있는지를 검증해야 한다.
• 이번 글에서 다루기에는 너무 양이 많으니, 다음 챕터에 정규화를 이용해서 검증하는걸 적어봐야겠다.😎

느낀점

• 항상 하면서 느끼는 거지만 예전에는 이런거 인터넷 보면서 적고 이랬는데, 요즘에는 공부를 좀 하다보니 머리속에서 자연스럽게 나오는 것 같다. 정규화는 아직 마음 속으로 잘 이해하지 못했는데, 이렇게 내가 직접 생각해보면서 설계하고, 이 과정에 정규화 하는 과정까지 얹으면 이해가 잘될꺼라고 생각한다.