본문 바로가기
ICIA 수업일지

2021.07.08 수업일지

by 주성씨 2021. 7. 10.

- SQL REVIEW

- 집합(SET)

- 우리는 두 개의 테이블을 집합연산 할 수 있다.
- UNION 은 중복 확인을 위해 전체 점색 후 정렬하여 검사를 수행 하기 때문에 성능에 좋지 않다.

UNION, UNION ALL은 기억해보자.

 

- 정규화(Nomalization)와 역정규화(Denormalization)

1. 정규화의 개념

- 관계형 DBMS 설계에서 중복을 최소화하게 데이터를 구조화 하는 프로세스
- 자료 중복으로 인해 이상 현상(Anomaly)이 발생 할 수 있어 이를 최소화 하는 것이 정규화의 목적

Anomaly Description
삭제 이상 튜플 삭제시 원치 않는 데이터도 삭제되어 연쇄삭제 문제가 발생하는 것.
삽입 이상 튜플 삽입시 특정 속성에 해당 값이 없어 NULL값을 입력해야 하는 현상.
갱신 이상 튜플 수정시 데이터 일부만 수정(갱신)되어 데이터 불일치(Inconsistency) 문제 발생

 

2. 정규화의 목적

- 데이터 구조의 안저엉 및 무결성 유지
- 어떤 릴레이션이라도 DB내에서 표현 가능
- 효과적인 검색 알고리즘 생성
- 데이터 중복을 배제하여, 이상발생 방지 및 자료 저장 공간의 최소화 가능
- 데이터 삽입 시, 릴레이션 재구성할 필요성을 줄인다.
- 데이터 모형의 단순화 가능
- 속성의 배열 상태 검증 가능
- 개체와 속성의 누락 여부 확인 가능
- 자료 검색과 추출의 효율성 추구

3. 정규화의 과정

- 정규화 과정은 총 여섯 과정이 있다.
- 그 중 실제로는 3NF까지만 사용 한다.
- 3NF가 되면 정규화가 된 것으로 간주 한다.
- 각 정규형은 이전 단계들을 기본적으로 모두 만족 해야만 한다.
- 정규화는 이상 현상을 최소화 하기 위해 테이블을 쪼개는 작업 이다.
- 하지만 지나친 정규화는 쿼리를 복잡하게 만들고 성능을 저하 시킨다.
- 그래서 테이블을 합하는 작업을 역 정규화라고 한다.

1) 1NF(NORMAL FORM)
- 모든 항목에 도메인이 원자값 이어야 한다.
- 중복되는 데이터가 없어야 한다.

2) 2NF(NORMAL FORM)
- 개체의 속성이 한 식별자에 종속 되어야 한다.
- 이를 부분 함수적 종속 제거라고 한다.

3) 3NF(NORMAL FORM)
- 연도로 인해 우승국이 결정되고, 우승국으로 인해 감도깅 결정되는 경우
- 이 경우 이행 함수 종속성 이라고 한다.
- 우승국이 변경되면 불필요 하게 감독까지 변경 되어야 한다.
- 우승국만 변경하면 갱신 이상 발생.

 

- 동물원 관리 DB 생성 실습해보기

-- 동물원 관리 DB

-- 1. 동물분류(AC)

CREATE TABLE Animal_Categories (
    AC_CODE NCHAR(1), --PK
    AC_NAME NVARCHAR2(10) NOT NULL, --UQ
    CONSTRAINT PK_AC_CODE PRIMARY KEY(AC_CODE),
    CONSTRAINT UQ_AC_NAME UNIQUE(AC_NAME)
);

 

-- 제약조건 설정 연습(위에서 이미 설정했으니 안해도 된다.)

ALTER TABLE Animal_Categories
ADD CONSTRAINT PK_AC_CODE PRIMARY KEY(AC_CODE);
ALTER TABLE Animal_Categories
MODIFY AC_NAME NOT NULL;
ALTER TABLE Animal_Categories
ADD CONSTRAINT UQ_AC_NAME UNIQUE(AC_NAME);

 

-- 동의어(SYNONYM) 생성
-- 동의어는 DML에서만 사용 가능하다. DDL에서는 사용 불가능
-- DDL : ALTER, CREATE, DROP

CREATE SYNONYM AC FOR Animal_Categories;
SELECT * FROM AC; -- 확인

 

-- 2. 동물(ANIMAL) : AN

CREATE TABLE ANIMAL(
    AN_CODE NCHAR(2),
    AN_NAME NVARCHAR2(10) NOT NULL,
    AN_QTY NUMBER(2,0) DEFAULT 1 NOT NULL,
    AN_ACCODE NCHAR(1) NOT NULL,
    -- 테이블단에 기본키, 왜래키 설정
    CONSTRAINT PK_AN_CODE PRIMARY KEY(AN_CODE),
    CONSTRAINT UQ_AN_NAME UNIQUE(AN_NAME),
    CONSTRAINT FK_AN_ACCODE FOREIGN KEY(AN_ACCODE) 
    REFERENCES Animal_Categories(AC_CODE)
    -- 부모컬럼은 PK이거나 UQ해야한다.
);

 

-- 동의어(SYNONYM) 생성

CREATE SYNONYM AN FOR ANIMAL;
SELECT * FROM AN;

 

-- DEFAULT(위에서 했으니 MODIFY 인것을 참고만 하자)

ALTER TABLE ANIMAL
MODIFY AN_QTY DEFAULT 1;

 

-- DUMMY DATA INSERT TO AC

INSERT INTO AC VALUES(1,'포유류');
INSERT INTO AC VALUES(2,'조류');
INSERT INTO AC VALUES(3,'양서류');
INSERT INTO AC VALUES(4,'어류');
SELECT * FROM AC;

 

-- DUMMY DATA INSERT TO AN

INSERT INTO AN VALUES(1,'원숭이',10,1);
INSERT INTO AN VALUES(2,'꼬끼리',2,1);
INSERT INTO AN VALUES(3,'타조',3,2);
INSERT INTO AN VALUES(4,'사자',5,1);
INSERT INTO AN VALUES(5,'하이에나',4,1);
INSERT INTO AN VALUES(6,'도마뱀',3,3);
INSERT INTO AN VALUES(7,'공작새',5,2);
INSERT INTO AN VALUES(8,'양',23,1);
INSERT INTO AN VALUES(9,'펭귄',10,2);
SELECT * FROM AN;
COMMIT;

 

-- 3. 먹이분류(FeedCategories) : FC

CREATE TABLE Feed_Categories(
    FC_CODE NCHAR(1),
    FC_NAME NVARCHAR2(10) NOT NULL,
    CONSTRAINT PK_FC_CODE PRIMARY KEY(FC_CODE),
    CONSTRAINT UQ_FC_NAME UNIQUE(FC_NAME)
);

 

- 동의어 생성

CREATE SYNONYM FC FOR Feed_Categories;

 

-- DUMMY DATE INSERT INTO Feed_Categories

INSERT INTO FC VALUES(1,'과일');
INSERT INTO FC VALUES(2,'육류');
INSERT INTO FC VALUES(3,'여류');
INSERT INTO FC VALUES(4,'음료');
INSERT INTO FC VALUES(5,'풀');

SELECT * FROM FC;

 

-- 4. 먹이 공급 회사(벤더) (FeedVender) : FV

CREATE TABLE Feed_Vender(
    FV_CODE NCHAR(1),
    FV_NAME NVARCHAR2(10) NOT NULL,
    CONSTRAINT PK_FV_CODE PRIMARY KEY(FV_CODE)
);

 

-- 동의어 생성

CREATE SYNONYM FV FOR Feed_Vender;

 

--  DUMMY DATE INSERT INTO Feed_Vender

INSERT INTO FV VALUES(1,'양마나');
INSERT INTO FV VALUES(2,'마시써');
INSERT INTO FV VALUES(3,'신선해');

SELECT * FROM FV;

 

-- 5. 동물먹이(AnimalFeed) : AF

CREATE TABLE Animal_Feed(
    AF_CODE NCHAR(1),
    AF_NAME NVARCHAR2(10) NOT NULL,
    AF_COST NUMBER(6,0) DEFAULT 1000 NOT NULL,
    AF_QTY NUMBER(4,0) DEFAULT 1000 NOT NULL,
    AF_UNIT NVARCHAR2(5) DEFAULT 'kg' NOT NULL,
    AF_FVCODE NCHAR(1) NOT NULL,
    AF_FCCODE NCHAR(1) NOT NULL,
    CONSTRAINT PK_AF_CODE PRIMARY KEY(AF_CODE),
    CONSTRAINT FK_AF_FVCODE FOREIGN KEY(AF_FVCODE)
    REFERENCES Feed_Vender(FV_CODE),
    CONSTRAINT FK_AD_FCCODE FOREIGN KEY(AF_FCCODE)
    REFERENCES Feed_Categories(FC_CODE)
);

 

-- 동의어 생성

CREATE SYNONYM AF FOR AnimalFeed;

 

-- 테이블명 변경

ALTER TABLE AnimalFeed RENAME TO Animal_Feed;
SELECT * FROM AF;

 

-- 이전 시노님 삭제 및 시노님 생성

DROP SYNONYM AF;
CREATE SYNONYM AF FOR Animal_Feed;

 

 

--  DUMMY DATE INSERT INTO Animal_Feed

INSERT INTO AF VALUES(1,'바나나',2000,DEFAULT,DEFAULT,1,1);
INSERT INTO AF VALUES(2,'생닭',3000,DEFAULT,'마리',2,2);
INSERT INTO AF VALUES(3,'건초',10000,DEFAULT,DEFAULT,3,5);
INSERT INTO AF VALUES(4,'물고기',3000,DEFAULT,'마리',2,3);
INSERT INTO AF VALUES(5,'우유',2500,DEFAULT,'L',1,4);
INSERT INTO AF VALUES(6,'사과',5000,DEFAULT,DEFAULT,3,1);

 

-- 중간 데이터 INSERT 확인

SELECT * FROM AC;
SELECT * FROM FC;
SELECT * FROM AN;
SELECT * FROM FV;
SELECT * FROM AF;

 

- 물리적 저장

COMMIT;

 

-- 6. 사육사(ZooKeeper) : ZK

CREATE TABLE Zoo_Keeper(
    ZK_CODE NCHAR(2),
    ZK_NAME NVARCHAR2(10) NOT NULL,
    ZK_HIREDATE DATE DEFAULT SYSDATE NOT NULL,
    CONSTRAINT PK_ZK_CODE PRIMARY KEY(ZK_CODE)
);

 

-- 동의어 생성

CREATE SYNONYM ZK FOR Zoo_Keeper;

 

-- DUMMY DATA INSERT INTO Zoo_Keeper

INSERT INTO ZK VALUES(1,'이주성',DEFAULT);
INSERT INTO ZK VALUES(2,'이순신',DEFAULT);
INSERT INTO ZK VALUES(3,'이성계','2020/01/01');
INSERT INTO ZK VALUES(4,'이방원','2019/12/31');

SELECT * FROM ZK;

 

-- 7. 담당동물(AnimalManager) : AM

CREATE TABLE Animal_Manager(
    AM_ZKCODE NCHAR(2),
    AM_ANCODE NCHAR(2),
    CONSTRAINT PK_AM_ZK_ANCODE PRIMARY KEY(AM_ZKCODE, AM_ANCODE),
    CONSTRAINT FK_AM_ZKCODE FOREIGN KEY(AM_ZKCODE)
    REFERENCES Zoo_Keeper(ZK_CODE),
    CONSTRAINT FK_AM_ANCODE FOREIGN KEY(AM_ANCODE)
    REFERENCES ANIMAL(AN_CODE)
);

 

-- 동의어 생성

CREATE SYNONYM AM FOR Animal_Manager;

 

-- DUMMY DATA INSERT INTO Animal_Manager

INSERT INTO AM VALUES(1,4);
INSERT INTO AM VALUES(1,5);
INSERT INTO AM VALUES(2,3);
INSERT INTO AM VALUES(2,7);
INSERT INTO AM VALUES(2,9);
INSERT INTO AM VALUES(3,1);
INSERT INTO AM VALUES(3,2);
INSERT INTO AM VALUES(3,8);
INSERT INTO AM VALUES(4,2);
INSERT INTO AM VALUES(4,6);

SELECT * FROM AM;

 

-- 확인

SELECT * FROM AC;
SELECT * FROM FC;
SELECT * FROM AN;
SELECT * FROM FV;
SELECT * FROM AF;
SELECT * FROM ZK;
SELECT * FROM AM;

COMMIT;

 

-- ZOODB 관련 문제
-- Q1. 포유류 동물리스트 및 마리수 검색
-- 포유류, 사자, 마리

SELECT AC.AC_NAME, AN.AN_NAME, AN.AN_QTY 
FROM AC JOIN AN
ON AC.AC_CODE = AN.AN_ACCODE
WHERE AC.AC_CODE='1';

 

-- Q2. 포유류 총 마리수
-- 포유류, XXX마리

SELECT AC.AC_NAME, SUM(AN.AN_QTY)
FROM AC JOIN AN
ON AC.AC_CODE = AN.AN_ACCODE
GROUP BY AC.AC_NAME
HAVING AC.AC_NAME='포유류';

 

-- Q3. 조류중(GROUP BY)에서 최대 개체수를 가진 동물이름 및 마리수 검색

1. 서브쿼리

선생님)

SELECT AN.AN_NAME, AN.AN_QTY 
FROM AN
WHERE AN.AN_ACCODE =2
AND AN.AN_QTY=(
SELECT MAX(AN.AN_QTY)
FROM AN
WHERE AN.AN_ACCODE=2);

나)

SELECT AN_NAME, AN_QTY FROM
(SELECT AN_NAME, AN_QTY
FROM AN
WHERE AN_ACCODE = 2
ORDER BY AN_QTY DESC)
WHERE ROWNUM=1;

 

2. 조인문

SELECT AC.AC_NAME,AN.AN_NAME,AN.AN_QTY
FROM AC JOIN AN
ON AC.AC_CODE=AN.AN_ACCODE
WHERE ac.ac_name='조류'
AND AN.AN_QTY>=(SELECT MAX(AN.AN_QTY)
                FROM AC JOIN AN
                ON AC.AC_CODE=AN.AN_ACCODE
                WHERE AC.AC_NAME='조류');

 

-- Q4. 동물 카테코리별로 최대 개체수를 가진 동물이름 및 마리수 검색

-- 다시해보기

CREATE OR REPLACE VIEW AC_MAX_VIEW
AS
SELECT AN_ACCODE,MAX(AN_QTY) MAX_QTY
FROM AN
GROUP BY AN_ACCODE;

 

-- 방법1

SELECT AC.AC_NAME, AN.AN_NAME, AN.AN_QTY
FROM AN JOIN AC_MAX_VIEW M
ON AN.AN_ACCODE=M.AN_ACCODE AND AN.AN_QTY=M.MAX_QTY
JOIN AC
ON AN.AN_ACCODE=AC.AC_CODE;

 

-- 방법2 : JOIN & 서브쿼리

SELECT ac.ac_name, an.an_NAME, AN.AN_QTY
FROM AN JOIN AC
ON an.an_accode=ac.ac_code
WHERE (an.an_accode, an.an_QTY) IN(SELECT * FROM AC_MAX_VIEW);

 

-- Q5. 먹이중에서 과일들의 단가 및 개수 리스트 검색

-- 나)

SELECT AF.AF_NAME, AF.AF_COST, AF.AF_QTY
FROM FC JOIN AF
ON fc.fc_code=AF.AF_FCCODE
WHERE FC.FC_NAME='과일';

 

 

-- 선생님)

SELECT AF.AF_NAME, AF.AF_COST, AF.AF_QTY
FROM AF
WHERE AF.AF_FCCODE IN(SELECT fc.fc_CODE FROM FC
                        WHERE fc.fc_NAME = '과일');

 

-- Q6. 과일중에서 단가가 가장 싼 과일 이름, 단가 검색

SELECT AF.AF_NAME, AF.AF_COST 
FROM AF
WHERE AF.AF_FCCODE = 1 -- 조건1
AND AF.AF_COST=( -- 조건2
SELECT MIN(AF.AF_COST)
FROM AF
WHERE AF.AF_FCCODE=1);

 

SELECT AF.AF_NAME, AF.AF_COST 
FROM AF
WHERE AF.AF_FCCODE = 1 -- 조건1
AND AF.AF_COST=( -- 조건2
SELECT MIN(AF.AF_COST)
FROM AF
WHERE AF.AF_FCCODE=(SELECT FC_CODE FROM FC WHERE FC_NAME='과일'));

 

-- Q7. '양마나' 벤더가 공급하는 먹이이름 검색

SELECT AF_NAME
FROM AF
WHERE AF_FVCODE=1;
SELECT FV.FV_NAME, AF.AF_NAME
FROM FV JOIN AF
ON FV.FV_CODE = AF.AF_FVCODE
WHERE AF.AF_FVCODE=1;
SELECT AF_NAME
FROM AF
WHERE AF_FVCODE = (SELECT FV_CODE FROM FV
                    WHERE FV_NAME='양마나');

 

-- Q8. 마시써 벤더가 공급하는 먹이 카테고리(육류,어류) 리스트 검색

SELECT FV.FV_NAME,FC.FC_NAME, AF.AF_NAME
FROM AF JOIN FV
ON FV.FV_CODE = AF.AF_FVCODE
JOIN FC
ON FC.FC_CODE = AF.AF_FCCODE
WHERE FV_CODE=2;

 

-- Q9. 이주성 사육사가 담당하는 동물이름 검색

SELECT ZK.ZK_NAME, AN.AN_NAME
FROM AM JOIN ZK
ON AM.AM_ZKCODE = ZK.ZK_CODE
JOIN AN
ON AM.AM_ANCODE = AN.AN_CODE
WHERE ZK.ZK_CODE = 1;
저작자표시

'ICIA 수업일지' 카테고리의 다른 글

2021.07.12 수업일지  (0) 2021.07.17
2021.07.09 수업일지  (0) 2021.07.12
2021.07.07 수업일지  (0) 2021.07.10
2021.07.06 수업일지  (0) 2021.07.10
2021.07.05 수업일지  (0) 2021.07.10

관련글

티스토리툴바