KoreanFoodie's Study
SQL 기초 - 8. 모델링 (정규화, 참조 무결성, 쇼핑몰 모델링) 본문
이 글은 '김상형의 SQL 정복'의 예제 코드를 설명하는 방식으로 SQL 쿼리 예제를 소개하고 있습니다.
SQL 기초 - 8. 모델링 (정규화, 참조 무결성, 쇼핑몰 모델링)
-- tProject 테이블에서 UPDATE 쿼리를 실행한다.
UPDATE tProject SET 담당자='문종민' WHERE 프로젝트='TV 광고건';
SELECT 프로젝트명 FROM tProject WHERE 담당자 = '김상형';
CREATE TABLE tEmployee
(
name CHAR(10) PRIMARY KEY,
salary INT NOT NULL,
addr VARCHAR(30) NOT NULL
);
INSERT INTO tEmployee VALUES ('김상형', 650, '이천시');
INSERT INTO tEmployee VALUES ('문종민', 480, '대구시');
INSERT INTO tEmployee VALUES ('권성직', 625, '안동시');
CREATE TABLE tProject
(
projectID INT PRIMARY KEY,
employee CHAR(10) NOT NULL,
project VARCHAR(30) NOT NULL,
cost INT
);
INSERT INTO tProject VALUES (1, '김상형', '홍콩 수출건', 800);
INSERT INTO tProject VALUES (2, '김상형', 'TV 광고건', 3400);
INSERT INTO tProject VALUES (3, '김상형', '매출분석건', 200);
INSERT INTO tProject VALUES (4, '문종민', '경영 혁신안 작성', 120);
INSERT INTO tProject VALUES (5, '문종민', '대리점 계획', 85);
INSERT INTO tProject VALUES (6, '권성직', '노조 협상건', 24);
INSERT INTO tProject VALUES (7, '홍길동', '원자재 매입', 900);
DELETE FROM tEmployee WHERE name = '김상형';
-- tProject 테이블을 삭제하고, FOREIGN KEY 옵션을 주어 employee와 name을 연결한다!
CREATE TABLE tProject
(
projectID ... ,
employee ... ,
project ... ,
cost ... ,
CONSTRAINT FK_emp FOREIGN KEY(employee) REFERENCES tEmployee(name)
);
-- 순서가 바뀌면 에러가 난다. 왜냐면 '홍길동'이라는 참조 키가 tEmployee의 name에 아직 없기 때문이다!
INSERT INTO tEmployee VALUES ('홍길동', 330, '장성');
INSERT INTO tProject VALUES (7, '홍길동', '원자재 매입', 900);
-- 그냥 DELETE를 하게되면 참조 무결성이 깨지게 된다!
DELETE FROM tProject WHERE employee = '김상형';
DELETE FROM tEmployee WHERE name = '김상형';
DROP TABLE tEmployee;
-- 참조 무결성을 지키기 위해 ON DELETE CASCADE를 넣는다
-- 이는 해당 키를 참조하는 다른 테이블의 데이터를 같이 지워준다.
CREATE TABLE tProject
(
....
CONSTRAINT FK_emp FOREIGN KEY(employee) REFERENCES tEmployee(name)
ON DELETE CASCADE
);
DELETE FROM tEmployee WHERE name = '김상형';
-- SQL SERVER에서는 ON UPDATE를 지원한다.
CREATE TABLE tProject
(
....
CONSTRAINT FK_emp FOREIGN KEY(employee) REFERENCES tEmployee(name)
ON DELETE CASCADE ON UPDATE CASCADE
);
UPDATE tEmployee SET name = '문사장' WHERE Name = '문종민';
-- 회원 테이블
CREATE TABLE tMember
(
member VARCHAR(20) PRIMARY KEY, -- 아이디
age INT NOT NULL, -- 나이
email VARCHAR(30) NOT NULL, -- 이메일
addr VARCHAR(50) NOT NULL, -- 주소
money INT DEFAULT 1000 NOT NULL, -- 예치금
grade INT DEFAULT 1 NOT NULL, -- 고객등급. 1=준회원, 2=정회원, 3=우수회원
remark VARCHAR(100) NULL -- 메모 사항
);
-- 회원 데이터
INSERT INTO tMember VALUES ('춘향',16,'1004@naver.com','전남 남원시',20000, 2, '');
INSERT INTO tMember VALUES ('이도령',18,'wolf@gmail.com','서울 신사동',150000, 3, '');
INSERT INTO tMember VALUES ('향단',25,'candy@daum.net','전남 남원시',5000, 2, '');
INSERT INTO tMember VALUES ('방자',28,'devlin@ssang.co.kr','서울 개포동',1000, 1, '요주의 고객');
-- 상품 분류 테이블
CREATE TABLE tCategory
(
category VARCHAR(10) PRIMARY KEY, -- 분류명
discount INT NOT NULL, -- 할인율
delivery INT NOT NULL, -- 배송비
takeback CHAR(1) -- 반품 가능성
);
-- 분류 데이터
INSERT INTO tCategory (category, discount, delivery, takeback) VALUES ('식품', 0, 3000, 'n');
INSERT INTO tCategory (category, discount, delivery, takeback) VALUES ('패션', 10, 2000, 'y');
INSERT INTO tCategory (category, discount, delivery, takeback) VALUES ('가전', 20, 2500, 'y');
INSERT INTO tCategory (category, discount, delivery, takeback) VALUES ('성인', 5, 1000, 'n');
-- 상품 테이블
CREATE TABLE tItem
(
item VARCHAR(20) PRIMARY KEY, -- 상품명
company VARCHAR(20) NULL, -- 제조사
num INT NOT NULL, -- 재고
price INT NOT NULL, -- 정가
category VARCHAR(10) NOT NULL, -- 분류
CONSTRAINT item_fk FOREIGN KEY(category) REFERENCES tCategory(category)
);
-- 상품 데이터
INSERT INTO tItem (item,company,num,price,category) VALUES ('노트북', '샘성', 3, 820000, '가전');
INSERT INTO tItem (item,company,num,price,category) VALUES ('청바지', '방방', 80, 32000, '패션');
INSERT INTO tItem (item,company,num,price,category) VALUES ('사과', '문경농원', 24, 16000, '식품');
INSERT INTO tItem (item,company,num,price,category) VALUES ('대추', '보은농원', 19, 15000, '식품');
INSERT INTO tItem (item,company,num,price,category) VALUES ('전자담배', 'TNG', 4, 70000, '성인');
INSERT INTO tItem (item,company,num,price,category) VALUES ('마우스', '논리텍', 3, 90000, '가전');
-- 주문 테이블
MSSQL :
CREATE TABLE tOrder
(
orderID INT IDENTITY PRIMARY KEY, -- 주문 번호
member VARCHAR(20) NOT NULL, -- 주문자
item VARCHAR(20) NOT NULL, -- 상품
orderDate DATE DEFAULT GETDATE() NOT NULL, -- 주문 날자
num INT NOT NULL, -- 개수
status INT DEFAULT 1 NOT NULL, -- 1:주문, 2:배송중, 3:배송완료, 4:반품
remark VARCHAR(1000) NULL -- 메모 사항
);
오라클 :
CREATE TABLE tOrder
(
orderID INT GENERATED AS IDENTITY PRIMARY KEY, -- 주문 번호
member VARCHAR(20) NOT NULL, -- 주문자
item VARCHAR(20) NOT NULL, -- 상품
orderDate DATE DEFAULT SYSDATE NOT NULL, -- 주문 날자
num INT NOT NULL, -- 개수
status INT DEFAULT 1 NOT NULL, -- 1:주문, 2:배송중, 3:배송완료, 4:반품
remark VARCHAR2(1000) NULL -- 메모 사항
);
마리아 :
CREATE TABLE tOrder
(
orderID INT AUTO_INCREMENT PRIMARY KEY, -- 주문 번호
member VARCHAR(20) NOT NULL, -- 주문자
item VARCHAR(20) NOT NULL, -- 상품
orderDate DATE DEFAULT CURDATE() NOT NULL, -- 주문 날자
num INT NOT NULL, -- 개수
status INT DEFAULT 1 NOT NULL, -- 1:주문, 2:배송중, 3:배송완료, 4:반품
remark VARCHAR(1000) NULL -- 메모 사항
);
-- 주문 데이터
INSERT INTO tOrder (member,item,orderDate,num,status) VALUES ('춘향','청바지','2019-12-3',3,2);
INSERT INTO tOrder (member,item,orderDate,num,status) VALUES ('향단','대추','2019-12-4',10,1);
INSERT INTO tOrder (member,item,orderDate,num,status) VALUES ('방자','전자담배','2019-12-2',4,1);
INSERT INTO tOrder (member,item,orderDate,num,status) VALUES ('향단','사과','2019-12-5',5,2);
INSERT INTO tOrder (member,item,orderDate,num,status) VALUES ('흥부','노트북','2019-12-5',2,1);
INSERT INTO tOrder (member,item,orderDate,num,status) VALUES ('방자','핸드폰','2019-11-1',1,3);
member VARCHAR(20) NOT NULL REFERENCES tMember(member), -- 주문자
item VARCHAR(20) NOT NULL REFERENCES tItem(item), -- 상품
DROP TABLE tOrder, tItem, tCategory, tMember;
'Database' 카테고리의 다른 글
| SQL 기초 - 10. 조인 (단순 조인, 외부 조인, 다중 조인 등 SQL 조인 예제) (0) | 2021.10.18 |
|---|---|
| SQL 기초 - 9. 서브쿼리 (서브쿼리 연산자, 인라인 뷰, 테이블 조합, 테이블 병합) (1) | 2021.10.18 |
| SQL 기초 - 7. 제약(제약, 기본키, 시퀀스) (0) | 2021.10.12 |
| SQL 기초 - 6. 데이터 관리(삽입, 삭제, 갱신) (0) | 2021.10.12 |
| SQL 기초 - 5. 데이터 집계 (집계함수, 그룹핑) (0) | 2021.10.12 |
'Database' Related Articles
more
Comments