SQL 11일차 [모델링]

Aquerytool , ERDcloud도 있다 그것도 써보자

 

eXERD - > 이걸로 써보고 프로젝트 할때는 공유하기 좋은 위에 두개를 써보자

개념적 데이터베이스 모델링

테이블이 될 수있는애들 -> entity가 될수있는 애들 뽑고 , 속성을 뽑고 식별자가 될 수있는 애들 뽑음

그림 그려놓은거

논리적 데이터베이스 모델링

매핑룰, 기본키 , 관계->외래키로, 정규화 - > 테이블 쪼개기 

1정규화

중복속성 제거

2정규화

중복키에 의존적인 애들 다 나가기

3정규화

기본키에 의존적이지 않은 애들 나가기

 

 

역정규화

join을 많이 해야하는 select 문의 성능을 올리기 위해서

join을 줄이면 무결성이 깨진다

 

 

 

물리적데이터베이스 모델링

DBMS선정

데이터 타입과 사이즈 정의

사용량 분석! -> 역정규화는 처음부터 못하니까 여기서 함

-->CRUD matrix

https://lipcoder.tistory.com/341

3-2-3장. 트랜잭션 분석 / CRUD 분석 - B

 

 

entity

업무수행을 위해 필요한 데이터를 저장할 테이블

명사를 추출한다

단순하고 유일하게 식별가능

추출시 상상력을 더하면 안된다 - > 사용자의 업무부담만 증가하기 때문에

 

기초속성

원래 갖고 있는 속성 - > 사번, 이름, 부서,,,,

 

추출 속성

기초 속성으로 부터 가공처리(계산)를 통해서 얻어질수 있는 속성을 의미

단가* 속성 : 주문총금액

가상컬럼으로 했었지 ~

단점: 추출속성을 사용하면 수량이 바뀌면 재입력해야함

-> 데이터 무결성과 자료의 중복성문제 최소화

->계산된 컬럼이나 트리거를 사용한다

 

설계속성

실제로 존재하지 않으나 시스템의 효율성 도모하기 위해서 설계자가 임의로 부여하는 속성

주문 상태, 주문 번호

 

주문코드, 상태정보

0                    주문중

1                    배송중

                        ....

 

 

도메인

 

 

식별자

후보식별자 - > 주식별자(기본키) 뽑고나면 -> 그나머지가 부식별자 ->

업무적인 활용도가 높다 - > 주식별자~를 사용해서 select하는그런거

not null 하고 unique 한거를 뽑으면 된다.

 

외부식별자(외래키) - > 부모실체의 주식별자가 자식실체에 전이된 식별자

 

대리 식별자(surrogate key) -> 복합키가 여러개의 컬럼으로 구성된 경우 이를 대체하기 위해 인위적으로 적은 컬럼

여러개의 키대신에 하나의 키를 생성하여 그걸 키로 사용하는 식별자

복합키대신 새로생성한 하나의 기본키를 사용하는 경우를 이야기한다

 

기본으로 제공하는 테이블

 

관계

명명되어진 의미잇는연결이다

n:m의 관계가 많지롱

 

차수성

1:1 하나당 하나

 

1:n 고객은 여러개의 상품을 주문할수있다

 

n:m 상품은 고객에 의해 여러개가 주문될 수잇다.

 

 

부서가 사원 앞에 있는 옵션을 작성

바라보는 쪽 해석!

 

 

draw.io -> 사용자 끼리 공유해서 ERD를 작성하기 좋음

https://app.diagrams.net/

Flowchart Maker & Online Diagram Software

 

 

 

eXERD 사용하기

 

ctrl + enter 속성명 작성

 

 

관계는 테이블 설계에서 다 외래키로 표현된다.

 

식별 관계

전이되는 컬럼이 기본키 속성으로 가는 경우

 

 

 

비식별관계

부서번호가 사원테이블로 전이됨

 

 

데이터 베이스 관계
1:1
1:N
M:N 관계는 의미만 
(개념적 설계에서만 존재)
>> 1: N 으로 풀어진다

 

 

 

자기참조
(self join)
관리자도 우리 회사 사원이니 별도의 테이블을 만들 필요 없다

관계차수 바꾸기

 

 

 

exactly_one 반드시 한개의 신체정보를 가지고 있다. 0을 포함하지 않는 상태를 말한다

하지만 일반적인 1:1의 관계는 0을 포함한다 아니면 입력하고 싶지 않을때 말도 안되는 값도 함께 입력 받아야하기 때문이다

복합키는 성능때문에 순서가 겁나 중요하다

 

성능상 둘다 써야 튜플을 검색할수 잇다

두번째 있는 키를 사용해서 검색하면 풀 스캔하기 때문에 성능이 저하된다

 

 

조건의 분포도나 성능을 보는 표가 있다

1) 검색을 많이 한다! 앞으로

 

 

null값이 들어가는 경우를 제외하기 위해서 교수번호와 강사번호를 갖는테이블을 작성했다

 

 

엔티티 정의서

엔티티 뽑고 정의하는 거

 

기본 속성

걍 개체를 이루는 속성들

 

설계 속성

일련번호, 코드

 

파생 속성(추출속성)

다른 속성으로부터 계산이나 변형되어 생성되는 속성

트리거나 계산된 속성사용하자

 

고객번호에 따라서 방문일시와 방문내용을 통해 AS 내용을 관리하려고 하면 3번이상 받은  고객은 다음번에 AS를 받을 수 없게 된다 그러면 컬럼을 계속 추가하는 방법을 사용하게 되는데 이러면 AS를 한번도 안받은 고객은 null값으로 채워서 넣게 된다

--> 컬럼이 모두 한 테이블에 있어서 발생하는 문제점

고객과 AS에 대한 방문 테이블을 나눴다. 방문 테이블에 방문일시를 통해 고객이 언제 방문해서 aS를 맡겼는지 기록하도록했다 이렇게 되면 고객번호와 방문일시가 합쳐져서 복합키가 되기 때문에 만약 같은 고객이 당일에 몇번씩 aS를 온다면 기본키 값이 중복된다  따라서 고객들이 하루에 한번만 AS를 받도록 설계한 것이 된다 

해결책 : 방문일시를 시분초까지 받는다.

 

시분초까지 받는 방법 말고 해결책은 순번을 사용하는 것이다. 고객이 몇번째로 방문했고를 복합키로 기록하면 이 두개를 합쳐서 하나의 AS요청을 구별 할 수 있다. 이제 고객은 여러번의 AS를 받을 수 있게 된다. 그리고 한번도 안받는 고객에 대해서는 null값을 따로 입력할 필요도 없다.

위에 사원 과 부양자수는 db초기 설계로 그때는 부양자 수만으로 관리를 하면되었다. 하지만 부양자에 따른 각종 혜택을받으려다보니 부양자 테이블이 필요하게 되었다. 그래서 사원번호와 순번을 복합키로 갖는 부양자 테이블을 통해 사원의 부양자들을 순번을 매겨서 관리하게 되었다.

그랬더니 사원 테이블에 있는 부양자수 컬럼이 문제가 되었다 이전에는 부양자 수가 바뀌면 이 부분만 update하면 됐었는데 이제는 부양자 테이블에 누가 삭제되고 추가 되었는지 까지 관리해주어야한다. 그렇다보니 결국 부양자수 컬럼은 부양자 테이블에서 select로 사원번호로 group by 한 다음에 count(*)로 몇명의 부양자를 갖고 있는지 작성해줘야 하게 되었다.

 

그렇다면 사원 테이블의 부양자수를 바꾸거나 부양자 테이블을 수정하면 다른테이블도 바꿔주지 않으면 무결성을 해치게 되는 문제가 발생하게 되는데 이를 어떻게 해결해야 할까?

우선 사원 테이블에서 부양자수 컬럼을 삭제하는 방법이있다. 하지만 이렇게 삽입한 걸보니 부양자수를 계산하는 조회가 많이 발생하는 것같다. 이럴때는 성능향상을 위해 사원테이블이 부양자수를 갖고 있는 것이 낫다. 이럴 때는 관리 측면을 고려해야하는데 insert,update,delete를 진행하면 실행되는  트리거를 만들어주는 방법과 자동으로 수정되도록 하는 컬럼인 계산된 컬럼을 이용하는 방법도 있다.

 

 

 

책 테이블과 저자 테이블은 n:m관계로 이를 가운데 책 저자라는 테이블을 통해서 연결해줬다.

만약 여기서 책 테이블에 있는 컬럼과 저자 테이블에 있는컬럼 들을 모두 검색하려고 하면, 만약 책 ID, 책이름, 저자ID, 저자이름을 검색하고자 한다면 최소 3개 테이블을 조인해야한다. 책 테이블은 저자 테이블에 있는 내용을 조회하려면 책저자테이블을 먼저 조인하고 이를 통해서 저자테이블을 조인해야하기 때문이다.

 

근데 아래 테이블과 같이 만약에 책저자에 검색을 용이하게 하기 위해 역정규화를 통해 자주 검색되는 컬럼을 한테이블에 전부 넣어준다면 조회 성능은 증가하지만 이는 제 2 정규화에 위배된다. 나머지 컬럼들이 복합키에 의해 구별되지 못하고 책 ID는 책이름을 단독을 구분하고 저자 ID는 저자 이름을 단독을 구분할 수있어 따로 놀기 때문이다.

또한 무결성이 깨질 수있다 책 테이블과 저자테이블에서 바뀐 정보는 똑같이 책저자 테이블에서도 바꿔주어야하기 때문이다.

--> 한 테이블에 모두 다 있으면 insert , update, delete는 불편하지만(무결성 위배) select성능은 향상된다.

 

 

https://cafe.daum.net/DBDB

데이터베이스_최용락

 

 

 

 

이렇게 할 수 있을까? -->좀더 진행해보며 알아보자 아직 모르겠다

강사는 회의록을 작성을 여러과목에 대해서 할 수있고 아예 작성하지 않을 수있다.

수강생은 여러강의를 수강하고 강의는 여러 수강생에 의해서 수강되어질 수있다.

설문지는 강의별 수강생별로 따로 구분되어져야 한다.

강의는 여러명의 강사에 의해서 강의되어질 수있다.(교수는 과목별로 있고 과목에도 여러명의 교수들이있으므로)

과목은 여러개의 강의를 가질 수있다.(교수별로 다른 강의가 만들어진다.)
과목을 수강하기 위한 선수과목이 있을 수있다.

강의실하나가 시간대별로 여러개의 강의를 진행 할 수있다.