2022.09.16 [MON]
- Day 32-
수업 주요 목차
- ERD
- 정규화
- Intersaction(교차) Entity
🤖Review
정규화(Normalization) : 데이터 중복을 최소화하기 위해 테이블을 분해하는 과정을 말함
데이터베이스 정규화란 데이터 중복을 최소화 하고 이상 현상을 방지하여 무결성을 보장하기 위해 테이블을 분리, 구조화 하는 과정을 말한다.
👾 제 1 정규형 : 1NF(Normalization Form)
- 도메인은 원자값이어야 한다.
- 도메인: 속성(컬럼)이 가질 수 있는 값의 범위 (예-성별에는 남과 여, 계좌타입은 '주택','여행','자기개발')
- 원자값: 더이상 쪼갤 수 없는 값
∴ 컬럼에 복수의 속성(값)이 저장되어서는 안된다.
예 )
하나의 주문서에서 여러 개의 상품을 주문할 수 있다.
주문서의 속성(컬럼)인 상품번호, 상품명, 상품설명, 단가, 주문수량에 다수의 정보가 저장될 수 있다.
도메인은 원자값이어야 하므로(컬럼의 여러개의 값이 들어가면 안되므로) 별도의 주문 상세 테이블로 분리해야한다.
🐶 1 정규화(1NF) 후 주문 상세 테이블의 기본키(Primary key)는 복합키(composite key)로 구성된다.
복합키 : 두 개 이상의 컬럼을 이용해 Primary key를 만드는 것을 말한다.
🧚♂️ 하나의 테이블에는 하나의 primary key만 존재할 수 있음 (단, 여러개의 컬럼으로 구성될 수 있다.)
-- 주문상세 테이블
CREATE TABLE order_detail(
order_id NUMBER,
product_id NUMBER,
order_count NUMBER NOT NULL,
constraint my_order_detail PRIMARY KEY(order_id,product_id)
)
--복합키로 구성
INSERT INTO order_detail(order_id,product_id,order_count) VALUES (1,77,4);
INSERT INTO order_detail(order_id,product_id,order_count) VALUES (1,99,2);
INSERT INTO order_detail(order_id,product_id,order_count) VALUES (1,77,2);
-- ERROR: unique constraint (SCOTT.MY_ORDER_DETAIL) violated ->order_id와 product_id 복합키 제약조건에 위배
INSERT INTO order_detail(order_id,product_id,order_count) VALUES (2,77,2);
-- SUCCESS: 주문번호가 다르므로 성공주문서의 주문번호 1 불닭면 상품번호 77
주문서의 주문번호 1 테라 상품번호 999
복합키(주문번호 + 상품번호)로 구성되면 두 컬럼의 합한 값이 유일해야 한다.
1 77 3봉지-> insert success
1 999 2병-> insert success
1 77 2봉지 -> fail ->이미 존재하므로 insert가 아니라 update 되어야 함
👾 제 2 정규형 : 2NF
- 부분 함수 종속성을 분리하여 완전 함수 종속 상태로 만든다.
- 함수 종속성 : A(결정자)에 의해 B(종속자)가 결정되어지는 관계 (예 : 사원번호 1의 사원명은 아이유다. , 회원아이디 javaking의 회원명은 잭이고 주소는 미국이다.
- 완전 함수 종속 : 기본키를 구성하는 모든 속성에 종속되는 경우
- 예 : 주문번호 1과 상품번호 77이 조합된 복합키가 기본키일 때 기본키를 구성하는 두 속성에 완전 종속되는 주문수량만 완전 함수 종속된다.
- 부분 함수 종속: 기본키를 구성하는 속성의 일부에 종속되는 경우
- 예 : 상품명, 상품설명, 단가는 별도의 상품테이블로 분리되어야 한다.
- 완전 함수 종속 : 기본키를 구성하는 모든 속성에 종속되는 경우
- 함수 종속성 : A(결정자)에 의해 B(종속자)가 결정되어지는 관계 (예 : 사원번호 1의 사원명은 아이유다. , 회원아이디 javaking의 회원명은 잭이고 주소는 미국이다.
예 )
주문상세 테이블의 기본키는 {주문번호,상품번호}의 복합키로 구성되어 있다.
이 때 상품아이디에만 부분 종속되는 상품명, 단가 속성은 분리해서 별도의 테이블로 구성한다.
주문번호와 상품아이디 복함키에 완전 종속되는 주문 수량 속성만 저장하도록 설계한다.
🐶 완전종속처럼 보이더라도 부분종속이 해당되면 완전종속X
- 예 ) {주문번호,상품번호} -> 상품명 과 {주문번호,상품번호} -> 단가 가 완전종속으로 말이 되더라도 상품번호->상품명, 상품번호->단가 처럼 부분종속에 해당하면 완전 종속이 아니다.(즉 전자는 그냥 종속관계)
👾 제 3 정규형 : 3NF
- 이행 함수 종속성을 분리하여 완전 함수 종속 상태로 만든다.
- 일반 속성에 종속적인 속성을 분리한다.(예 : 주문서 테이블의 주문은 담당부서번호만 가지고 있으면 부서명, 부서전화, 부서지역, 관리자 명을 가지고 있을 필요없이 별도의 부서 테이블에서 부서번호로 참조하면 된다.)
3. 이행 함수 종속 : A->B 이고 B->C 이면 A->C이다.
- 예 : 주문서 1의 담당 부서 번호는 10이고 담당부서번호 10의 부서명이 영업부이면 주문서 1의 담당 부서명은 영업부이다.
- 주문서 1은 담당부서번호만 가지고 있으면 된다.
🧚♂️
함수 종속 = 1)부분 종속 2)완전 종속
A -> B
결정자 -> 종속자
=> 결정자 이면 종속자 이다.
분석/설계 마일스톤 :
1. ERD로 DB Modeling
2. UI 설계->데이터가 보임
3. UML의 Class Diagram으로 Application 설계
🔎 ERD + SQL 실습 내용
| 요구사항 주식 거래 시스템을 구축하고자 한다. 주식 정보는 주식명,주당가격으로 구성된다. 현재 공시된 모든 주식정보는 고객에게 리스트로 제공된다. 고객정보는 아이디,패스워드,이름,주소로 구성된다. 고객이 시스템에 로그인하면 주소와 이름이 화면에 제공된다. 또한 고객은 주식을 매수 또는 매도할 수 있다. 고객은 주식을 매수하면 배당받은 주식 정보를 주식명, 주당가격, 보유수량, 총액과 같은 형식의 정보를 화면테이블 형태로 제공받을 수 있다. 위 요구사항을 바탕으로 Database Modeling을 해본다. 필요한 테이블 주요 컬럼 (pk,fk, 일반속성) 관계 |
1.ERD (제 1유형 위배)
주식거래 프로그램에서 필요한 테이블은 고객의 일반정보, 주식의 일반정보, 그리고 거래관련정보가 담긴 각각의 3개의 테이블이 필요하다고 이야기를 먼저 나누었다. 그리고 필요한 속성들을 모두 작성하여 주요 컬럼 (pk,fk, 일반속성)과 관계를 파악해보았다. 우선 주식명이 결정자가 되어 주당가격이 종속될 수 있고 고객 아이디가 결정자가 되어 패스워드, 이름, 주소가 종속될 수 있다. 그래서 PK를 주식명, 고객아이디로 정했다. 위와 같은 테이블의 경우 한 컬럼에 여러 개의 값이 들어가는 제 1유형에 위배되기 때문에 우선 주식의 일반 정보를 담고 있는 테이블을 아래와 같이 분리시켰다.
↓
관계는 부모테이블인 주식 테이블의 PK가 자식 테이블인 주식 거래 테이블의 PK로 전이되었기 때문에 식별관계로 표현했고 주식은 거래량이 0,1,혹은 다수일 수 있고 주식거래는 반드시 1 이상의 주식정보가 필요하다고 표현해주었다.
2. ERD (제 2유형 위배)
PK인 {주식명,고객아이디}에 완전 종속되는 속성인 보유수량을 제외한 패스워드, 이름, 주소는 고객아이디(결정자)에 부분종속 되어 제 2유형에 위배되기 때문에 아래와 같이 정규화 시켰다.
↓
여기에서도 관계는 부모테이블인 고객 테이블의 PK가 자식 테이블인 주식 거래 테이블의 PK로 전이되었기 때문에 식별관계로 표현했고 고객은 0,1,혹은 다수의 주식거래를 할 수 있고 주식거래는 반드시 1 이상의 고객정보가 필요하다고 표현해주었다.
3. ERD (제 3유형 위배) + Intersaction Entity
맨 처음 조원들과 이야기 나누었을 때 조원들이 주식거래 테이블레 '총액'이라는 속성을 넣자고 이야기하여 추가할 까 하였지만 총액은 JDBC에서 다루는 것이 Database를 가볍게 유지할 수 있을 것 같아서 없앴다. 이 후 다른 조의 조원이 제 3유형에 위배된다고 하여 살펴보니 총액의 경우 고객 아이디가 주식명을 통한 주당가격과 일반속성인 보유수량을 가지고 알 수 있는, 즉 주식명->보유수량, 보유수량->총액, 주식명->총액이라는 이행함수가 생겨 제거해주는 것이 맞았다.
강사님 풀이
Intersaction(교차) Entity(or Association(제휴) Entity) : 다 대 다 관계(Many to Many Relationship)를 해소하는 엔티티
고객과 주식은 다 대 다 관계이다.(한명의 고객이 다수의 주식을 매수할 수 있고 하나의 주식은 다수의 고객에게 매수될 수 있다.)
=> 릴레이션 사이에 하나의 릴레이션이 더 필요하여 entity를 추가한다.
고객 -----0|<- 배당 ->|0--------주식
고객의 일반정보 고객과 주식의 관계정보 주식의 일반정보
↓
- logical(논리)
-physical(물리)
4. 테이블 생성
--고객 일반 정보
CREATE TABLE customer(
id VARCHAR2(100) PRIMARY KEY,
password NUMBER NOT NULL,
name VARCHAR2(100) NOT NULL,
address VARCHAR2(100) NOT NULL
)
--주식 일반 정보
CREATE TABLE stock(
symbol VARCHAR2(100) PRIMARY KEY,
price NUMBER NOT NULL
)
--고객과 주식의 관계정보 (교차엔티티)
CREATE TABLE shares(
id VARCHAR2(100),
symbol VARCHAR2(100),
quantity NUMBER NOT NULL,
CONSTRAINT erd_shares PRIMARY KEY(id,symbol),
CONSTRAINT erd_customer_fk FOREIGN KEY (id) REFERENCES customer(id),
CONSTRAINT erd_stock_fk FOREIGN KEY (symbol) REFERENCES stock(symbol)
)
DROP TABLE customer;
DROP TABLE stock;
DROP TABLE shares;
5. 등록
--고객 등록
INSERT INTO customer(id,password,name,address) VALUES ('dlwlrma',486,'아이유','종로');
--주식등록
INSERT INTO stock(symbol,price) VALUES ('삼성',300);
INSERT INTO stock(symbol,price) VALUES ('SK',200);
INSERT INTO stock(symbol,price) VALUES ('LG',400);
INSERT INTO stock(symbol,price) VALUES ('현대',500);
COMMIT
--배당등록
--ERROR : integrity constraint (SCOTT.ERD_STOCK_FK) violated - parent key not found -> Foreign key로 참조하는 id spring 은 Customer 테이블에 존재하지 않으므로 error
INSERT INTO shares(id,symbol,quantity) VALUES ('spring','삼성',2);
--ERROR : integrity constraint (SCOTT.ERD_STOCK_FK) violated - parent key not found -> FK 현대자동차는 stock 테이블에 존재하지 않으므로 error
INSERT INTO shares(id,symbol,quantity) VALUES ('dlwlrma','현대자동차',2);
-- SUCCESS
INSERT INTO shares(id,symbol,quantity) VALUES ('dlwlrma','삼성',2);
INSERT INTO shares(id,symbol,quantity) VALUES ('dlwlrma','LG',3);
--ERROR : unique constraint (SCOTT.ERD_SHARES) violated ->id 와 symbol 복합키 제약조건에 위
INSERT INTO shares(id,symbol,quantity) VALUES ('dlwlrma','LG',3);
--추가매수하려면 UPDATE 해야함
UPDATE shares SET quantity=quantitu+4 WHERE id='dlwlrma' AND symbol='삼성전자';
6. 조회
- dlwlrma 아이디 고객의 보유 주식명과 주당 가격, 수량, 총액을 조회
- 3개 테이블을 조인
--Oracle SQL
SELECT c.name,s.symbol, s.price, sh.quantity, s.price*sh.quantity AS TOTAL
FROM customer c, stock s, shares sh
WHERE c.id='dlwlrma'
AND c.id=sh.id
AND (s.symbol=sh.symbol);
--ANSI SQL
SELECT c.name,s.symbol, s.price, sh.quantity, s.price*sh.quantity AS TOTAL
FROM shares sh
INNER JOIN customer c ON c.id=sh.id
INNER JOIN stock s ON s.symbol=sh.symbol
WHERE c.id='dlwlrma';
//오늘의 숙제
복습+!!예습!!
