SW 심화교육 10일차
데이터베이스 설계 프로세스
1. 업무분석
- 명사, 동사에 주의하여 업무를 정확하게 기술한다.
- 업무 파악에 도움이 되는 어떤 형태의 자료도 확보한다.
- 가능한한 UI를 그려가면서 인터뷰를 진행한다.
(나중에 갈아엎더라도 이 때 그려진 UI는 설계에 결정적인 영향을 끼친다)
- 말 만들기에 따라서 설계에 영향이 갈 수 있다.
2. 엔티티 ( Entity) 도출
- 추상명사 중에서 PK로 구분되어지는 것이 Entity 가 된다.
* 추상명사는 대표하는 성격을 갖고 있다.
ex) 상품, 멘토, 멘티, 관광객, 게시글, 댓글, 사진,
- PK는 단일 필드의 특징이 있어야 한다.
(주민번호의 경우 두개의 필드로 볼 수 있지만 단일한 성격으로 파악하는 것이 바람직하다)
- 기록될 만한 가치가 있는 정보인지 판별해야 한다.
PK ? Primary Key ( 기본키라고 부르기도 한다.)
: 레코드로 구분하는 기준으로 사용되는 필드(들)
예) 주민번호, 사번, 군번, ISBN ...
주민번호처럼 두개의 필드가 결합해서 PK를 이룰 수도 있다.
PK는 3가지의 성격을 가져야 한다.
ND (No Duplicate) : 중복되어서는 안된다.
- 주민등록번호 중복되면 큰일 남
NN (Not Null) : 생략되어서는 안된다.
- 주민번호 없는 국민?
NC (No Change) : 가급적 변경되어서는 안된다.
- 주민번호 한번 바꾸면 모든 기록들에 영향이 간다.
[엔티티는 사각형으로 ERD에서 표현된다]
엔티티 예) 회원, 글, 과목, 학생
3. Relation (관계) 도출
- 엔티티들 사이에서 동사가 '어울리게' 존재 가능하고,
그것이 기록할만한 가치가 있다면 그것이 Relation이다.
예) 회원은 글을 쓴다. (쓴다)
학생은 과목을 수강한다. (수강한다)
Relation 에는 세가지 종류가 있다.
(1) 일대일 대응 : Be 동사에 해당하는 관계 or 상속관계
예) 사병은 군인이다. 장교는 군인이다. 군인은 군번으로 구분된다.
(2) 일대다 대응 : [설명참고]
예) 회원은 글을 쓴다.
(3) 다대다 대응 : [설명참고]
예) 학생은 과목을 수강한다. 회원은 글을 읽는다. 또한 추천한다.
관계의 물리적인 구현방법
(1) 일대일 대응 : 조상의 PK를 자손이 PK 이자 FK로 참조한다.
ex) 장교는 군인이다.(O, 군번) 군인은 장교이다. (X, 군번)
* PK이자 동시에 FK인 필드가 1 or 0 일 때 1:1 대응이 된다.
교수는 회원이다.(O), 교수는 학생이다.(X, 1 or 0) == 1:1 대응관계
학생은 회원이다.(O), 회원은 학생이다.(X, 1 or 0) == 1:1 대응관계
회원은 로그인한다.(O)
(2) 일대다 대응 : '다'쪽에서 '일'쪽의 PK를 FK로 참조한다.
하나의 회원은 여러 개의 글을 작성한다.
(3) 다대다 대응 : 새로운 테이블을 만들고, 그 PK는 양쪽의 PK를 참조하는
FK를 결합하여 구성한다.
여러 회원은 여러 글을 추천한다. (글 추천여부 저장)
4. Attribute (속성) 파악
- 일반명사중에서 자료형과 값으로 표현될 수 있는 것들.
예) 성적, 글쓴시간, 이름, 전화번호, 주소..
- Entity, Relation 에 1:1로 매핑되는 곳에 배치한다.
- 실제 구현시 필드에 해당된다.
5. ERD 구성
- 분석에 가까운 형태, 구현에 가까운 형태 두가지가 있다.
(두가지 다 그릴 줄 알아야 한다)
- 엔티티는 사각형, 관계는 마름모로 그린다.
관계는 화살표 또는 실선으로 그린다. (도착쪽이 PK)
PK는 꽉찬 사탕막대기, 그 외 필드는 텅빈 사탕막대기,
Not Null 필드는 굵은 글씨로 표기한다.
- 그럴때 용이한 많은 툴이 있다. (유료가 많다)
Microsoft Visio 를 강사는 개인적으로 추천한다.
6. 외래키 (Foreign Key) 구성
- 다른 테이블의 PK로 쓰이는 필드를 내 쪼에서 참조해서 쓰는 필드(들)
...
- 정규화 : More Table, Less Column
- 비정규화 : Less Table, More Column
- 비정규화는 속도는 빠르지만 테이블 구조가 자주 변경될 여지가 있다.
- 정규화는 속도는 느릴 수 있지만 테이블 구조가 안정적이다.
- 정규화는 자료의 중복저장을 허용 안한다. (성적만 있으면 등수는 자동)
- 비정규화는 자료의 중복저장을 허용하는 경향 (속도를 위해 등수 필요)
- "결국 정규화를 중심으로 해서 적절한 비정규화를 추구하는 게 방향이지만
정답은 없다. 해서 이 분야는 경험이 절대적으로 중요하다.)
- 엔티티는 테이블로 바껴진다.
# 비정규화
select * from score2;
select stid, name, (kor1 + eng1 + mat1) / 3 as avg from score2t;
→ 데이터 처리 속도가 빠름, 과목의 수가 변화가 있을 때 대책이 심각
score2와 같이 설계된 경우를 비정규화 라고 한다.
- 정규화 : Less Clumn (테이블당 필드의 갯수가 적다 5~12 개)
필드와 데이터의 중복저장을 허용 안한다. (비디오점의 대여여부)
비정규화 : More Colunm, Less Table
필드와 데이터의 중복 저장을 허용한다. (속도 때문에)
* 대부분 정규화를 기본으로 해서 적절한 비정규화를 도입한다
# 테이블 및 자료 복사하기 (정규화 때 사용)
create table student_xt as
select stid, name, addr from score2t where 0 = 1;
insert into student_xt
select stid, name, addr from score2t where 1 = 1;
과제 : sub_xt (score2t 기반으로 만들기)
score_xt (score2t 기반으로 만들기)
select stid, 'kor1' as subid, kor1 from score2t where 1 = 1;
create table score_xt as
select stid, 'KOR1' as subid, kor1 as score from score2t where 0 = 1;
insert into score_xt
select stid, 'ENG1' as subid, eng1 as score from score2t where 1 = 1;
insert into score_xt
select stid, 'MAT1' as subid, mat1 as score from score2t where 1 = 1;
:: 비정규화 된 설계에서 정규화 된 설계로 옮길 수도 있고,
정규화 된 설계에서 비정규화 된 설계로 옮길 수도 있어야 한다.
# 조인 (서브쿼리와 유사한데, 서브쿼리와 다름)
- studentt - stid(PK) ← scoret : stid(FK)
대부분의 Join은 PK - FK 사이에서 일어난다.
select * from studentt INNER JOIN scoret ON studentt.stid = scoret.stid;
- INNER JOIN : PK - FK 데이터가 일치하는 레코드를 짜매준다.
- 2개의 테이블 결합
- ORACLE : select * from studentt , scoret WHERE studentt.stid = scoret.stid;
# INNER JOIN 에서 성적 뽑기
select name, addr, score from studentt INNER JOIN scoret ON
studentt.stid = scoret.stid where subid = 'mat1';
-- JOIN 을 이용하면 흩어진 데이터를 통합해서 보여지게 할 수 있다.
(서브쿼리도 가능, 헌데 성능이 틀리다)
같은 결과를 만들되 성능이 향상되게 만드는 것이 SQL 튜닝의 영역
Q) subjectt 과 scoret 를 inner join 해서, 10101 국어 90... 의 형태로 출력
select * from subjectt inner join scoret on subjectt.subid = scoret.subid;
select stid, name, score, subjectt.subid from subjectt inner join scoret
on subjectt.subid = scoret.subid;
select stid, name, score, y.subid from scoret as x inner join subjectt as y
on y.subid = x.subid;
- From 절의 서브쿼리 INNER JOIN 이 가능하다
select stid, avg(score) from scoret group by stid;
select y.stid, name, avg from (select stid, avg(score) as avg from scoret group by stid)
as X inner join studentt as y on y.stid = x.stid;
-- OUTER JOIN (외부 조인)
select * from subjectt inner join scoret on subjectt.subid = scoret.subid ;
→
insert into subjectt values ('PHY1', '물리');
→ 시험 성적이 없음으로 INNER JOIN 으로 데이터 값이 나타나지 않는다.
회원가입이 되어도 글 쓴게 없으면 조인 걸어도 나타나지 않는다.
이런 식으로 한 쪽 테이블에서만 보여지고 짜매지지 않는 경우라도 한건 보여지게 만드는
형태의 조인이 OUTTER JOIN 이다.
SELECT * FROM subjectt left outer join scoret on subjectt.subid = scoret.subid;
- 부족함이( NULL )이 나타나는 반대편을 명시한다. (LEFT)
SELECT * FROM subjectt left outer join scoret on subjectt.subid = scoret.subid
GROUP BY subjectt.subid;
-- select subjectt.subid, count(score) from subjectt left outer join
scoret on subjectt.subid = scoret.subid group by subjectt.subid;
count(*) 는 레코드의 갯수를 세고,
count( 필드 )는 해당 필드의 null이 아닌 데이터의 갯수를 센다.
-- constraint : 물리적인 제약조건을 필드(들)에 걸어준다.
score : 0 ~ 100 사이의 값을 허용해야 한다.
insert into scoret values( '10101', 'phy1', 120);
delete from scoret where score > 100;
alter table scoret add constraint check_scoret_score chekc(score >=0 and score <= 100);
alter table (테이블 수정) add constraint (제약조건 추가)
제약 조건 : check, unique, primary key, foreign key 4가지를 주로 쓴다.
지울 때는, alter table socret drop constraint check_scoret_score
check constraint 는 where 절의 조건을 이용하여 제약을 걸 수 있다.
(in, not in, =, != .. )
- 제약조건 생성
alter table subjectt add constraint PK_subjectt_subid primary key( subid );
insert into subjectt values('KOR1', '국어');
# 참조 무결성
FK 쪽에서는 PH쪽에 존재하는 데이터만 사용가능
# 참조 무결성 생성
ALTER TABLE scoret Add constraint fk_scoret_subid
FOREIGN KEY ( subid ) references subjectt ( subid );
insert into scoret values('10101', 'phy1', 50);
- 즉 primary 쪽에서 쓰여진 데이터만 foreign으로 쓰일 수 있다.
- 참조무결성 : PK 쪽에서 쓰여진 데이터만 FK 쪽에 쓰일 수 있다.
- 회원가입해야 글을 쓴다.
- 회원 등록 해야 예약한다.
- 등록된 아이템만 대여 가능하다.
UNIQUE CONSTRAINT : NULL 허용하는데 중복은 안된다.
( 테이블 분리할 때 절묘하게 쓴다 )
중복 불가 → 다 : 다 테이블을 구성하고, 필드에 UNIQUE 를 설정하면
1 : 다 테이블로 사용 가능하다.
- create table study5t (
id int not null
);
alter table study5t add constraint UQ_study5t_id unique ( id );
→ 중복 허용 X
- "일대다 상황에서 테이블 분리하는 방법"
1. 일단 다대다로 생각하고 테이블을 생성한다.
2. PK 중 하나에 UNIQUE CONSTRAINT 를 건다.
: 다대다가 일대다로 바뀐다.
: PK를 어떻게 할지를 선택하여 결정한다.
- CONSTRAINT 지울 때 (오라클과 약간 다르다)
MYSQL : ALTER TABLE study5t DROP INDEX UQ_study5t_id;