제주에서 자바_Week2_2

2019. 7. 24. 10:31

Day 3

1. 데이터 베이스 설계 프로세스

업무 분석
- 명사, 동사 업무 정확하게 기술
- 업무 파악에 도움되는 어떤 형태의 자료도 확보
- 가능한 UI를 그려 가며 인터뷰 진행(설계에 결정적인 영향 끼침)
- 경험이 중요
엔티티 도출 (Entity)
- 추상 명사 중에서 PK로 구분되어지는 것이 Entity 가 된다
- 기록될 만한 가치가 있는 정보인지 판별
- PK : 단일 필드의 특징이 있어야 한다
  - Primary key (기본키)
    - 레코드를 구분하는 기준으로 사용되는 필드(들)
    - 예 ) 주민번호 , 사번, 군번,...
    - 주민번호처럼 두개의 필드가 결합해서 PK를 이룰 수도 있다
    - 3가지 성격
      - ND (NO DUPLICATE) : 중복되어서는 안된다
      - NN( NOT NULL) : 생략되어서는 안된다
      - NC (NO CHANGE) : 가급적 변경되어서는 안된다.
- 엔티티 : ERD에서 사각형으로 표현
  - 예 ) 회원, 글, 과목, 학생
관계 도출(Relation)
- 엔티티들 사이에서 동사가 어울리게 존재 가능 -> 기록할 만한 가치가 있다면 그것이 관계
- 예 ) 회원은 글을 쓴다(쓴다) - 학생은 과목을 수강한다(수강한다)
- 3가지 종류
  - 1대1 대응 : Be 동사에 해당하는 관계 or 상속관계
    - ex) 사병은 군인이다 / 장교는 군인이다 / 군인은 군번을 구분된다
  - 1대다 대응 :
    - 회원은 글을 쓴다.
  - 다대다 대응
    - 학생은 과목을 수강하고 -> 회원은 글을 읽는다 -> 추천한다
- 관계의 물리적인 구현 방법
  - 일대일 대응 : 조상의 PK를 자손이 PK 이자 FK로 참조한다
  - 일대다 대응 : 다 쪽에서 일 쪽의 PK를 FK로 참조한다
  - 다대다 대응: 새로운 테이블 생성 -> 그 PK는 양쪽의 PK를 참조하는 FK를 결합하여 구성
속성(Attribute) 파악
- 일반 명사 중 자료형과 값으로 표현될 수 있는 것들
  - 예) 성적, 글쓴 시간, 이름, 전화번호, 주소 ---
- 엔티티 , Relation 에 1:1로 매핑하는 곳에 배치한다
- 실제 구현시 필드에 해당
ERD 구성
- 2가지 종류 : 두가지 다 그릴 줄 알아야한다.
  - 분석에 가까운 형태
  - 구현에 가까운 형태
- 엔티티는 사각형, 관계는 마름모
  관계는 화살표 OR 실선 (도착쪽이 PK)
  PK는 꽉찬 사탕 막대기 , 그 외 필드는 텅빈 사탕 막대기
  NOT NULL 필드는 굵은 글씨로 표기한다
- 그릴때 용이한 많은 툴이 있다(유료)
  Microsoft Visio
- 정규화 vs 비정규화

정규화	비정규화
More Table , Less Column	Less Table, More Column
속도 느림	속도 빠름
테이블 구조 안정적	테이블 구조 자주 변경될 여지 O
자료 중복저장 허용 X(성적 있으면 등수 자동)	자료의 중복저장 허용O(속도를 위해 등수 필요)

=> 정규화를 중심으로 적절한 비정규화를 추구하는게 방향 / 경험이 가장 중요하다

6. FK (Foreign Key) :외래키

다른 테이블의 PK로 쓰이는 필드를 내쪽에서 참조해서 쓰는 필드(들)
성적 테이블에 stid , subid - 성적 테이블의 stid는 학생테이블에 쓰이는 의미를 가져다가 쓴다. "
-> 성적테이블의 stid의 10101은 학생테이블 stid의 10101과 동일한 의미이다

7. 비디오 가게 만들기

8. 정규화

mysql> select stid, name, (kor1 + eng1 + mat1)/3 as avg from score2t;
+-------+-----------+---------+
| stid  | name      | avg     |
+-------+-----------+---------+
| 10101 | 홍길동    | 76.6667 |
| 10102 | 고길동    | 93.3333 |
| 10103 | 이기자    | 76.6667 |
| 10104 | 박기자    | 73.3333 |
| 10105 | 김영삼    | 63.3333 |
| 10106 | 김대중    | 66.6667 |
+-------+-----------+---------+
6 rows in set (0.00 sec)

정규화
- Less Column : 테이블당 필드의 개수가 적다 - 5 ~ 12개
- More Table : score2t 가 한개가 되는 걸 우리는 3개를 만들었다
- 필드와 데이터의 중복 저장을 허용안한다(비디오점의 대여 여부)

비정규화 : More Column, Less Table
- 필드와 데이터의 중복 저장을 허용한다(속도 문제)

대부분 정규화를 기본으로 해서 적절한 비정규화를 도입한다.

#테이블의 껍데기만 만드는 것
mysql> create table student_xt as
-> select stid, name, addr from score2t where 0 = 1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

#테이블에 select 결과를 입력하는 것mysql> insert into student_xt select stid, name, addr from score2t where 1 = 1;
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from student_xt;
+-------+-----------+-----------+
| stid  | name      | addr      |
+-------+-----------+-----------+
| 10101 | 홍길동    | 역삼동    |
| 10102 | 고길동    | 개포동    |
| 10103 | 이기자    | 역삼동    |
| 10104 | 박기자    | 한남동    |
| 10105 | 김영삼    | 홍제동    |
| 10106 | 김대중    | 한남동    |
+-------+-----------+-----------+
6 rows in set (0.02 sec)

#score_xt //하나도 흘리지 않고 새로운 테이블에 옮겨갈 때

mysql> create table score_xt as
-> select stid, 'KOR1' as subid, kor1 as score from score2t where 0 =1;
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into score_xt select stid, 'KOR1', kor1 as score from score2t where 1= 1;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into score_xt
    -> select stid, 'ENG1', eng1 as score from score2t where 1=1;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into score_xt
    -> select stid, 'MAT1', mat1 as score from score2t where 1=1;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from score_xt;
+-------+-------+-------+
| stid  | subid | score |
+-------+-------+-------+
| 10101 | KOR1  |    60 |
| 10102 | KOR1  |    90 |
| 10103 | KOR1  |    70 |
| 10104 | KOR1  |    80 |
| 10105 | KOR1  |    50 |
| 10106 | KOR1  |    60 |
| 10101 | ENG1  |    80 |
| 10102 | ENG1  |    90 |
| 10103 | ENG1  |    90 |
| 10104 | ENG1  |    70 |
| 10105 | ENG1  |    60 |
| 10106 | ENG1  |    80 |
| 10101 | MAT1  |    90 |
| 10102 | MAT1  |   100 |
| 10103 | MAT1  |    70 |
| 10104 | MAT1  |    70 |
| 10105 | MAT1  |    80 |
| 10106 | MAT1  |    60 |
+-------+-------+-------+
18 rows in set (0.00 sec)

#비정규화 된 설계에서 정규화 된 설계로 옮길 수도 있고

정규화된 설계에서 비정규화 된 설계로 옮길 수도 있어야 한다.

9. JOIN -> 서브쿼리와 유사

studentt : stid (pk) <- scoret : stid(fk)

-대부분의 join은 pk - fk 사이에서 일어난다

mysql>select * from studentt INNER JOIN scoret ON studentt.stid = scoret.stid;
//studentt에 있는 pk(stid)를 참조 받아 두 테이블을 조인시킴
+-------+-----------+-----------+-------+-------+-------+
| stId  | name      | addr      | stId  | subId | score |
+-------+-----------+-----------+-------+-------+-------+
| 10101 | 홍길동    | 역삼동    | 10101 | KOR1  |   100 |
| 10101 | 홍길동    | 역삼동    | 10101 | ENG1  |   100 |
| 10101 | 홍길동    | 역삼동    | 10101 | MAT1  |   100 |
| 10102 | 고길동    | 개포동    | 10102 | KOR1  |    90 |
| 10102 | 고길동    | 개포동    | 10102 | MAT1  |    90 |
| 10102 | 고길동    | 개포동    | 10102 | ENG1  |   100 |
| 10103 | 이기자    | 역삼동    | 10103 | KOR1  |    70 |
| 10104 | 박기자    | 한남동    | 10104 | KOR1  |    80 |
| 10105 | 김영삼    | 홍제동    | 10105 | KOR1  |    50 |
| 10106 | 김대중    | 한남동    | 10106 | KOR1  |    60 |
| 10103 | 이기자    | 역삼동    | 10103 | ENG1  |    90 |
| 10104 | 박기자    | 한남동    | 10104 | ENG1  |    70 |
| 10105 | 김영삼    | 홍제동    | 10105 | ENG1  |    60 |
| 10106 | 김대중    | 한남동    | 10106 | ENG1  |    80 |
| 10103 | 이기자    | 역삼동    | 10103 | MAT1  |    70 |
| 10104 | 박기자    | 한남동    | 10104 | MAT1  |    70 |
| 10105 | 김영삼    | 홍제동    | 10105 | MAT1  |    80 |
| 10106 | 김대중    | 한남동    | 10106 | MAT1  |    60 |
+-------+-----------+-----------+-------+-------+-------+

- INNER JOIN : PK - FK 데이터가 일치하는 레코드를 짜매준다.

mysql>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 국어1 90..이런식으로 나오게
mysql> select stid,name,score from subjectt INNER JOIN scoret ON subjectt.subid = scoret.subid;
+-------+---------+-------+
| stid  | name    | score |
+-------+---------+-------+
| 10101 | 국어1   |   100 |
| 10101 | 영어1   |   100 |
| 10101 | 수학1   |   100 |
| 10102 | 국어1   |    90 |
| 10102 | 수학1   |    90 |
| 10102 | 영어1   |   100 |
| 10103 | 국어1   |    70 |
| 10104 | 국어1   |    80 |
| 10105 | 국어1   |    50 |
| 10106 | 국어1   |    60 |
| 10103 | 영어1   |    90 |
| 10104 | 영어1   |    70 |
| 10105 | 영어1   |    60 |
| 10106 | 영어1   |    80 |
| 10103 | 수학1   |    70 |
| 10104 | 수학1   |    70 |
| 10105 | 수학1   |    80 |
| 10106 | 수학1   |    60 |
+-------+---------+-------+
18 rows in set (0.00 sec)

mysql> select stid,name,score, subjectt.subid from subjectt INNER JOIN scoret ON subjectt.subid = scoret.subid;
//같은이름의 subid 가져오려면 어디서 가져와야하는지 명시 해야한다.
+-------+---------+-------+-------+
| stid  | name    | score | subid |
+-------+---------+-------+-------+
| 10101 | 국어1   |   100 | KOR1  |
| 10101 | 영어1   |   100 | ENG1  |
| 10101 | 수학1   |   100 | MAT1  |
| 10102 | 국어1   |    90 | KOR1  |
| 10102 | 수학1   |    90 | MAT1  |
| 10102 | 영어1   |   100 | ENG1  |
| 10103 | 국어1   |    70 | KOR1  |
| 10104 | 국어1   |    80 | KOR1  |
| 10105 | 국어1   |    50 | KOR1  |
| 10106 | 국어1   |    60 | KOR1  |
| 10103 | 영어1   |    90 | ENG1  |
| 10104 | 영어1   |    70 | ENG1  |
| 10105 | 영어1   |    60 | ENG1  |
| 10106 | 영어1   |    80 | ENG1  |
| 10103 | 수학1   |    70 | MAT1  |
| 10104 | 수학1   |    70 | MAT1  |
| 10105 | 수학1   |    80 | MAT1  |
| 10106 | 수학1   |    60 | MAT1  |
+-------+---------+-------+-------+

//subjectt -> y로 scoret -> x로 각각 줄여서 쓰면 이렇게 가능

mysql> select stid, name, score, y.subid FROM scoret as x INNER JOIN subjectt as y ON y.subid = x.subid;
+-------+---------+-------+-------+
| stid  | name    | score | subid |
+-------+---------+-------+-------+
| 10101 | 국어1   |   100 | KOR1  |
| 10101 | 영어1   |   100 | ENG1  |
| 10101 | 수학1   |   100 | MAT1  |
| 10102 | 국어1   |    90 | KOR1  |
| 10102 | 수학1   |    90 | MAT1  |
| 10102 | 영어1   |   100 | ENG1  |
| 10103 | 국어1   |    70 | KOR1  |
| 10104 | 국어1   |    80 | KOR1  |
| 10105 | 국어1   |    50 | KOR1  |
| 10106 | 국어1   |    60 | KOR1  |
| 10103 | 영어1   |    90 | ENG1  |
| 10104 | 영어1   |    70 | ENG1  |
| 10105 | 영어1   |    60 | ENG1  |
| 10106 | 영어1   |    80 | ENG1  |
| 10103 | 수학1   |    70 | MAT1  |
| 10104 | 수학1   |    70 | MAT1  |
| 10105 | 수학1   |    80 | MAT1  |
| 10106 | 수학1   |    60 | MAT1  |
+-------+---------+-------+-------+
18 rows in set (0.00 sec)

mysql> SELECT stid, avg(score) as avg FROM scoret group by stid;
+-------+----------+
| stid  | avg      |
+-------+----------+
| 10101 | 100.0000 |
| 10102 |  93.3333 |
| 10103 |  76.6667 |
| 10104 |  73.3333 |
| 10105 |  63.3333 |
| 10106 |  66.6667 |
+-------+----------+
6 rows in set (0.00 sec)

//서브쿼리를 사용해 JOIN 할 수 있다.

mysql> SELECT * from (SELECT stid, avg(score) as avg FROM scoret group by stid) AS x INNER JOIN studentt AS y ON y.stid = x.stid;
+-------+----------+-------+-----------+-----------+
| stid  | avg      | stId  | name      | addr      |
+-------+----------+-------+-----------+-----------+
| 10101 | 100.0000 | 10101 | 홍길동    | 역삼동    |
| 10102 |  93.3333 | 10102 | 고길동    | 개포동    |
| 10103 |  76.6667 | 10103 | 이기자    | 역삼동    |
| 10104 |  73.3333 | 10104 | 박기자    | 한남동    |
| 10105 |  63.3333 | 10105 | 김영삼    | 홍제동    |
| 10106 |  66.6667 | 10106 | 김대중    | 한남동    |
+-------+----------+-------+-----------+-----------+
6 rows in set (0.00 sec)

10. 외부조인(OUTER JOIN)

mysql> SELECT * FROM subjectt INNER JOIN scoret ON subjectt.subid = scoret.subid;
//일단 subjectt 와 scoret를 조인
+-------+---------+-------+-------+-------+
| subId | name    | stId  | subId | score |
+-------+---------+-------+-------+-------+
| KOR1  | 국어1   | 10101 | KOR1  |   100 |
| ENG1  | 영어1   | 10101 | ENG1  |   100 |
| MAT1  | 수학1   | 10101 | MAT1  |   100 |
| KOR1  | 국어1   | 10102 | KOR1  |    90 |
| MAT1  | 수학1   | 10102 | MAT1  |    90 |
| ENG1  | 영어1   | 10102 | ENG1  |   100 |
| KOR1  | 국어1   | 10103 | KOR1  |    70 |
| KOR1  | 국어1   | 10104 | KOR1  |    80 |
| KOR1  | 국어1   | 10105 | KOR1  |    50 |
| KOR1  | 국어1   | 10106 | KOR1  |    60 |
| ENG1  | 영어1   | 10103 | ENG1  |    90 |
| ENG1  | 영어1   | 10104 | ENG1  |    70 |
| ENG1  | 영어1   | 10105 | ENG1  |    60 |
| ENG1  | 영어1   | 10106 | ENG1  |    80 |
| MAT1  | 수학1   | 10103 | MAT1  |    70 |
| MAT1  | 수학1   | 10104 | MAT1  |    70 |
| MAT1  | 수학1   | 10105 | MAT1  |    80 |
| MAT1  | 수학1   | 10106 | MAT1  |    60 |
+-------+---------+-------+-------+-------+
18 rows in set (0.00 sec)

mysql> INSERT INTO subjectt values('PHY1', '물리');

//이렇게 물리가 추가 된다고 해도 성적데이터가 없으니 짜매어줄 대상이 없다

//회원가입이 되어도 글 쓴게 없으면 조인 걸어도 나타나지 않는다

//-> 한쪽 테이블에서만 보여지고 짜매지지 않는 경우라도 한 건 보여지게 만드는 형태의 조인이 OUTER JOIN이다

// 부족함(NULL 로 채움) 이 나타나는 반대편을 명시한다(LEFT)

mysql> SELECT * FROM subjectt LEFT OUTER JOIN scoret ON subjectt.subid = scoret.subid;
+-------+---------+-------+-------+-------+
| subId | name    | stId  | subId | score |
+-------+---------+-------+-------+-------+
| KOR1  | 국어1   | 10101 | KOR1  |   100 |
| ENG1  | 영어1   | 10101 | ENG1  |   100 |
| MAT1  | 수학1   | 10101 | MAT1  |   100 |
| KOR1  | 국어1   | 10102 | KOR1  |    90 |
| MAT1  | 수학1   | 10102 | MAT1  |    90 |
| ENG1  | 영어1   | 10102 | ENG1  |   100 |
| KOR1  | 국어1   | 10103 | KOR1  |    70 |
| KOR1  | 국어1   | 10104 | KOR1  |    80 |
| KOR1  | 국어1   | 10105 | KOR1  |    50 |
| KOR1  | 국어1   | 10106 | KOR1  |    60 |
| ENG1  | 영어1   | 10103 | ENG1  |    90 |
| ENG1  | 영어1   | 10104 | ENG1  |    70 |
| ENG1  | 영어1   | 10105 | ENG1  |    60 |
| ENG1  | 영어1   | 10106 | ENG1  |    80 |
| MAT1  | 수학1   | 10103 | MAT1  |    70 |
| MAT1  | 수학1   | 10104 | MAT1  |    70 |
| MAT1  | 수학1   | 10105 | MAT1  |    80 |
| MAT1  | 수학1   | 10106 | MAT1  |    60 |
| PHY1  | 물리    | NULL  | NULL  |  NULL |
+-------+---------+-------+-------+-------+
19 rows in set (0.00 sec)

//count(*) 는 레코드의 갯수를 센다.

//count(score) 은 해당 필드의 null 이 아닌 데이터의 갯수를 센다

mysql> SELECT subjectt.subid, count(score) FROM subjectt LEFT OUTER JOIN scoret ON subjectt.subid = scoret.subid GROUP BY subjectt.subid;
+-------+--------------+
| subid | count(score) |
+-------+--------------+
| ENG1  |            6 |
| KOR1  |            6 |
| MAT1  |            6 |
| PHY1  |            0 |
+-------+--------------+
4 rows in set (0.00 sec)

11. CONSTRAINT - 물리적인 제약 조건을 필드에 걸어준다

-> 제약 조건 : CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY 4가지를 주로 쓴다 .

-> score 값을 (0 - 100) 사이의 값만 허용해야한다

mysql>ALTER TABLE scoret ADD CONSTRAINT CHECK_scoret_score CHECK (score >= 0 AND score <= 100);

mysql>insert into scoret valus ('10101', 'PHY1', 80);
ERROR 1264 (22003): Out of range value for column 'score' at row 1

지울떄는

mysql>ALTER TABLE scoret DROP CONSTRAINT CHECH_scoret_score;

-CHECK CONSTRAINT 는 WHERE 절의 조건을 이용하여 제약을 걸 수 있다.

(IN, NOT IN, = != ... 을 사용할 수 있다

mysql> ALTER TABLE subjectt ADD CONSTRAINT PK_subjectt_subid
-> PRIMARY KEY(subid);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> INSERT INTO subjectt VALUES('KOR1','국어');
ERROR 1062 (23000): Duplicate entry 'KOR1' for key 'PRIMARY'

-참조 무결성 : PK 쪽에서 쓰여진 데이터만 FK 쪽에서 쓰여질 수 있다

회원 가입 해야 글 쓴다
회원 등록 해야 예약한다
등록된 아이템만 대여 가능하다 ...

#PRIMARY (subjectt) 쓰인적 있기 때문에 -> FOREIGN KEY부분에서 잘 쓸 수 있음

mysql> ALTER TABLE scoret ADD CONSTRAINT FX_scoret_subid
-> FOREIGN KEY(subid) REFERENCES subjectt(subid);
Query OK, 20 rows affected (0.05 sec)
Records: 20 Duplicates: 0 Warnings: 0

mysql> INSERT INTO scoret VALUES ('10101', 'XXXX', 50);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`study`.`scoret`, CONSTRAINT `FX_scoret_subid` FOREIGN KEY (`subId`) REFERENCES `subjectt` (`subId`))

12. UNIQUE CONSTRAINT : NULL 허용하는데 중복은 안된다 -> 테이블 분리할 때 사용

다대다로 만들고 -> UNIQUE 걸어서 -> 일대일처럼 동작

mysql> create table study05t(
-> id int not null
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER TABLE study05t ADD CONSTRAINT UQ_study05t_id UNIQUE(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

#일대일 상황에서 테이블 분리하는 방법

-일단 다대다로 생각하고 테이블을 생성한다

-pk 중 하나에 unique constraint를 건다

-> 다대다 => 일대일로 바뀐다 .

#constraint 지우는 법

mysql> ALTER TABLE study05t DROP INDEX UQ_study05t_id;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0

문제

짜장면집 결제 DB

저작자표시 변경금지

'Language Study > Java' 카테고리의 다른 글

제주에서 자바_Week3_1 (0)	2019.07.29
제주에서 자바_Week2_3 (0)	2019.07.25
제주에서 자바_Week2_1 (0)	2019.07.22
제주에서 자바_Week1 (0)	2019.07.20
4. 클래스 (0)	2019.05.29

exp_blog