step11-정규화-연습-ERD-다대다관계 (many-to-many relation)-교차 엔티티 (Association Entity).sql

1. 다 대 다 관계 (many-to-many realation)

 

: 多 대 多 관계는 Association Entity -  교차 엔티티를 생성하여, 해소한다

* 논리 ently / 물리 table

 

(1) 고객과 상품은 다 대 다 관계이다

 

고객: 아이유 - 주식: 삼성전자

- 아이유 고객이 주식을 0 or  1 or 多 가질 수 있다

- 삼성전자 주식은 0 or 1 or 多 매수될 수 있다

 

=> 교차 엔티티로 다 대 다 관계를 해소

 

2. 교차 엔티티

고객일반정보 ----0|< 배당관계정보 >|0---- 주식일반정보

고객의 일반정보-고객과 주식의 관계정보-주식의 일반정보

 

고객과 주식의 교차(연관) 정보를 저장하는 테이블(=교차 엔티티)을 별도로 생성한다

ERD 설계

 

 

3. 교차 엔티티를 이용해 주식 거래 시스템 구축하기

 

(1) 조건

주식 정보는 주식명,주당가격으로 구성된다 
현재 공시된 모든 주식정보는 고객에게 리스트로 제공된다 
고객정보는 아이디,패스워드,이름,주소로 구성된다 
고객이 시스템이 로그인하면 주소와 이름이 화면에 제공된다 
또한 고객은 주식을 매수 또는 매도 할 수 있다 

고객은 주식을 매수하면 배당받은 주식 정보를 주식명,주당가격,보유수량,총액과 같은 형식의 정보를 화면테이블 형태로 제공받을 수 있다  

 

(2) ERD로 DB Modeling 하기 (분석/설계 마일스톤)

 

위 요구사항을 바탕으로 Database Modeling 을 해본다 

 

- 조건 정리해보기

필요한 테이블 ?  
주요 컬럼 ( pk, fk , 일반속성)   ?
관계 ?

 

ERD로 DB Modeling 

 - UI 설계 -> 데이터가 보임 
 - UML 의 Usecase Diagram 으로 분석 
 - UML의  Class Diagram으로 Application 설계 

 

 

(3) SQL 구현하기

 

* 고객 일반 정보 테이블 생성

CREATE TABLE customer(
	id VARCHAR2(100) PRIMARY KEY,
    password VARCHAR2(100) 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_pk PRIMARY KEY(id, symbol),
    CONSTRAINT erd_customer_fk FOREIGN KEY(id) REFERENCES customer(id),
    CONSTRAINT erd_stock_fk FOREIGN KEY(symbol) REFERENCES stock(symbol)
    
)

 

* 고객 등록

INSERT INTO customer(id, password, name, address) VALUES('java', 'abcd', '아이유', '오리');

// select table

 

 

* 주식 등록

INSERT INTO stock(symbol, price) VALUES('삼성전자', 200);
INSERT INTO stock(symbol, price) VALUES('SK텔레콤', 150);
INSERT INTO stock(symbol, price) VALUES('기아자동차', 250);
COMMIT

// select table

 

 

* 배당 등록

INSERT INTO shares(id, symbol, quantity) VALUES('sptirng', '삼성전자', 5);

// error:  Foreign key 로  id  spring 은 Customer 테이블에 존재하지 않으므로 error 

 

INSERT INTO shares(id,symbol,quantity) VALUES('java','현대자동차',1);

// error: FK symbol  현대자동차는 stock 테이블에 존재하지 않으므로 error 

 

INSERT INTO shares(id,symbol,quantity) VALUES('java','삼성전자',4);

// success

 

* 만약 java 아이디 고객이 삼성전자를 4주 더 매수하려 한다면 update를 해야 한다

UPDATE shares SET quantity = quantity + 4 WHERE id = 'java' AND symbol = '삼성전자';

 

 

* java 아이디 고객의 고객명과 보유 주식명과 주당 가격 , 수량 , 총액을 조회 
- customer , shares , stock  3 개 table 을 조인 

 

* ORACLE SQL 조인

SELECT 컬럼명, 컬럼명
FROM 테이블명 별칭, 테이블명 별칭
WHERE 별칭.컬럼명 = 별칭.컬럼명

 

SELECT c.name, s.symbol, s.price, sh.quantity, s.price*sh.quan
FROM customer c, stock s, shares sh
WHERE c.id = sh.id AND sh.symbol = s.symbol
AND c.id = 'java';

 

* ANSI SQL 조인

 

SELECT c.name, s.symbol, s.price, sh.quantity, s.price*sh.quan
FROM shares sh
INNER JOIN customer c ON  c.id = sh.id
INNER JOIN sock s ON sh.symbol = s.symbol
WHERE c.id = 'java';

 

// select table