231127 5일차(오라클)

**1127 수업

*트랜잭션 더이상 쪼갤 수 없는 최소의 수행 단위
*-ALL OR NOTHING/ TCL

*ROLLBACK, COMMIT 으로 끝나면 된다. 

-SCOTT계정에서 사용 가능한 데이터 사전 살펴보기
SELECT*FROM DICT;
SELECT*FROM DICTIONARY;
SELECT TABLE_NAME FROM USER_TABLES;
SELECT OWNER, TABLE_NAME FROM ALL_TABLES;

*더 빠른 검색을 위한 인덱스(색인)
--SCOTT계정이 소유한 인덱스 정보 알아보기
SELECT*FROM USER_INDEXES;
--인덱스 생성
--CREATE INDEX 인덱스 이름
--ON 테이블 이름(열 이름1 ASC OR DESC,
--            (열 이름 ASC OR DESC,..._;

--EMP 테이블의 SAL 열에 인덱스를 생성하기
CREATE INDEX IDX_EMP_SAL ON EMP(SAL);

--인덱스 삭제 DROP INDEX 인덱스 이름;
DROP INDEX IDX_EMP_SAL;

*테이블처럼 사용하는 뷰 : 가상테이블, 하나 이상의 테이블을 조회하는 SELECT문을 저장한 객체
-SELECT문의 FROM절에 사용하면 특정 테이블을 조회하는 것과 같은 효과를 얻을 수 있음
-뷰의 사용 목적 1. 편리성, 보완성

 

--뷰를 생성
CREATE VIEW VW_EMP20 AS(SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP
WHERE DEPTNO=20);

SELECT*FROM VW_EMP20;
SELECT*FROM USER_VIEWS;

--부서번호가 30인 사원 정보의 모든 열을 출력하는 VM_EMP30ALL뷰를 작성
SELECT * FROM VM_EMP30ALL;
CREATE VIEW VM_EMP30ALL AS(SELECT * FROM EMP WHERE DEPTNO=30);

DROP VIEW VW_EMP20;

*ROWNUM : 의사 열(pseudo column)이라고 하는 특수 열
SELECT ROWNUM, E.*
FROM EMP E;

SELECT ENAME, JOB, EMPNO, FROM EMP;

SELECT ROWNUM, E.*FROM EMP E;

-ORDER BY절을 통해 정렬해도 유지되는 특성이 있음
SELECT ROWNUM, ENAME,JOB,SAL FROM EMP E ORDER BY SAL;

 

--ROWUMN를 테이블로 인식해서 정렬하려는 범위를 넓힌다.
SELECT ROWNUM,E.*
FROM(SELECT ENAME,JOB,SAL FROM EMP E ORDER BY SAL)E;

SELECT ROWNUM,E.*
FROM(SELECT ENAME,JOB,SAL FROM EMP E ORDER BY SAL)E
WHERE ROWNUM <=5;

--SAL이 높은 순서 5번
SELECT ROWNUM,E.*
FROM(SELECT ENAME,JOB,SAL FROM EMP ORDER BY SAL DESC )E
WHERE ROWNUM <=5;

*시퀀스 : 오라클 데이터베이스에서 특정 규칙에 맞는 연속 숫자를 생성하는 객체

 

--DEPT_SEQUENCE 테이블 생성
DROP TABLE DEPT_TEMP;
CREATE TABLE DEPT_TEMP AS SELECT*FROM DEPT;
SELECT*FROM DEPT_TEMP;
--DEPTNO 값이 안들어갔음 

 

INSERT INTO DEPT_TEMP(DNAME, LOC)
VALUES('SALES','SEOUL');

 

--시퀀스 생성
CREATE SEQUENCE SEQ_DEPT
INCREMENT BY 5
START WITH 50
MAXVALUE 70
CYCLE
CACHE 2;

 

*시퀀스 사용 ★
--[시퀀스 이름.CURRNAL],[시퀀스 이름.NEXTVAL]
>>DEPTNO 자리에 시퀀스를 통해 자동으로 번호를 부여하겠다

 

INSERT INTO DEPT_TEMP(DEPTNO,DNAME, LOC)
VALUES(SEQ_DEPT.NEXTVAL,'SALES','SEOUL');
SELECT*FROM DEPT_TEMP;

 

--시퀀스 삭제
DROP SEQUENCE SEQ_DEPT;
SELECT*FROM USER_SEQUENCES;


*공식 별칭을 지정하는 동의어 시노님 
-CREATE [PUBLIC] SYNONYM 동의어 이름 FOR [사용자.][객체이름];
-시스템에서 권한 부여>SYSTEM 확인

 

--EMP 테이블의 동의어 생성하기
CREATE SYNONYM E FOR EMP;

 

--E테이블 전체 내용 조회하기
SELECT*FROM E;

--EMP와 DEPT를 조인해서 VIEW을 D라고 하고, ENAME과 LOC를 출력해봅시다
DROP VIEW ;
CREATE VIEW VW_D
AS SELECT E.*,D.LOC FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO;

SELECT VW_D.ENAME, VW.D.LOC;


*제약조건★:데이터 무결성은 데이터베이스에 저장되는 데이터의 정확성과 일관성을 보장한다.
--NOT NULL, UNIQUE, PRIMARY KEY, FOREIGE KEY, CHECK

*빈값을 허락하지 않는 NOT NULL

 

--테이블을 생성할 때 NOT NULL 설정하기
CREATE TABLE TABLE_NOTNULL(
LOGIN_ID VARCHAR2(20) NOT NULL,
LOGIN_PWD VARCHAR2(20) NOT NULL,
TEL VARCHAR2(20));
DESC TABLE_NOTNULL;

 

--제약 조건이 NOT NULL인 열에 NULL값 넣어보기
INSERT INTO TABLE_NOTNULL(LOGIN_ID, LOGIN_PWD, TEL)
VALUES('TEST_ID_01',NULL,'010-1234-5678');
-->NULL을 ("SCOTT"."TABLE_NOTNULL"."LOGIN_PWD") 안에 삽입할 수 없습니다

--제약 조건이 없는 TEL 열에 NULL 값 입력하기
INSERT INTO TABLE_NOTNULL(LOGIN_ID, LOGIN_PWD)
VALUES('TEST_ID_01','1234');
SELECT*FROM TABLE_NOTNULL;

--NOT NULL 제약 조건이 지정된 열 데이터를 NULL 값으로 업데이트하기
UPDATE TABLE_NOTNULL
SET LOGIN_PWD=NULL
WHERE LOGIN_ID='TEST_ID_01';
-->NULL로 ("SCOTT"."TABLE_NOTNULL"."LOGIN_PWD")을 갱신할 수 없습니다

--제약 조건 살펴보기
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS;

--TEL 열에 NOT NULL 제약 조건 추가하기
ALTER TABLE TABLE_NOTNULL MODIFY(TEL NOT NULL);
-->사용으로 설정 불가 - 널 값이 발견되었습니다.

--TEL 열 데이터 수정하기
UPDATE TABLE_NOTNULL
SET TEL='010-1234-5678'
WHERE LOGIN_ID='TEST_ID_01';

--NOT NULL 제약 조건 추가하기>>모르겠음
ALTER TABLE TABLE_NOTNULL
MODIFY(TEL NOT NULL);

ALTER TABLE TABLE_NOTNULL2
MODIFY(TEL CONSTRAINT TBLNN_TEL_NN TO TBLNN2_TEL_NN);

--제약 조건 삭제하기
ALTER TABLE TABLE_NOTNULL
DROP CONSTRAINT SYS_C0011056;

*중복되지 않는 값 UNIQUE
--제약 조건 지정하기(테이블 생성할때)
CREATE TABLE TABLE_UNIQUE(
LOGIN_ID VARCHAR2(20) UNIQUE,
LOGIN_PWD VARCHAR2(20)NOT NULL,
TEL VARCHAR2(20));
DESC TABLE_UNIQUE;

 

-- UNIQUE 제약 조건을 지정한 LOGIN_ID 열은 중복 값이 저장되지 않는다. 

--UNIQUE 제약 조건이 지정된 열에 NULL값 입력하기
INSERT INTO TABLE_UNIQUE(LOGIN_ID, LOGIN_PWD, TEL)
VALUES(NULL, 'PWD01','010-2345-6789');

SELECT*FROM TABLE_UNIQUE;
-->UNOQUE제약조건이 지정된 열에는 NULL이 여러 개 존재할 수 있습니다.

--TEL 값에 UNIQUE 제약 조건 설정하기
ALTER TABLE TABLE_UNIQUE MODIFY(TEL UNIQUE);

ROLLBACK;

--PRIMARY KEY
--데이터 중복을 허용하지 않고 NULL도 허용하지 않는다. 
--PRIMARY KEY 테이블의 각 행을 식벽하는 데 활용
--테이블에 하나밖에 지정할 수 없다.

--테이블을 생성할 때 특정 열에 PRIMARY KEY 설정
CREATE TABLE TABLE_PK(
LOGIN_ID VARCHAR2(20) PRIMARY KEY,
LOGIN_PWD VARCHAR2(20) not null,
TEL VARCHAR2(20));

--생성한 PRIMARY KEY 확인하기
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME LIKE 'TABLE_PK%';
-->CONSTRAINT_TYPE에 P가 설정되어 있음, P=PRIMARY KEY 제약 조건을 의미

--TABLE_PK 테이블에 데이터 입력하기
INSERT INTO TABLE_PK(LOGIN_ID,LOGIN_PWD,TEL)
VALUES('TEST_ID_01','PWD01','010-1234-5678');

--TABLE_PK 테이블에 중복되는 데이터 입력하기
INSERT INTO TABLE_PK(LOGIN_ID, LOGIN_PWD, TEL)
VALUES('TEST_ID_01','PW02''010-2345-6789');
-->값의 수가 충분하지 않습니다.

--NULL 값을 암시적으로 입력하기
INSERT INTO TABLE_PK(LOGIN_PWD, TEL)
VALUES('PWD02','010-2345-6789');
->NULL을 ("SCOTT"."TABLE_PK"."LOGIN_ID") 안에 삽입할 수 없습니다

--제약 조건을 지정하는 다른 방식
CREATE TABLE TABLE_NAME(
COL1 VARCHAR2(20) CONSTRAINT CONSTRAINT_NAME PRIMARY KEY,
COL2 VARCHAR2(20) NOT NULL,
COL3 VARCHAR2(20));

CREATE TABLE TABLE_NAME(
COL1 VARCHAR2(20),
COL2 VARCHAR2(20),
COL3 VARCHAR2(20),
PRIMARY KEY(COL1),
CONSTRAINT CONSTRAINT_NAME UNIQUE(COL2));


--관계를 맺는 FOREIGNKEY(외래키) : 참조 , 내가 값을 집어넣는게 아니고 래퍼런스로 참조
--DEPT 테이블이 부모, EMP 테이블이 자식 테이블이 된다. 
--테이블의 DRPTNO 열에 존재하는 값과 NULL만 저장할 수 있게 된다. 
--10,20,30,40 그리고 NULL
SELECT*FROM EMP;
DESC EMP;
--FOREIGN KEY가 참조하는 열에 존재하지 않는 데이터 입력하기
INSERT INTO EMP(EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES(9999,'홍길동','CLERK','7788',TO DATE('2017/04/30', 'YYYY/MM/DD'),1200,NULL,50);
-->DEPTNO=50은 없음

--FOREIGN KEY 지정하기
--EMP_FK 테이블 생성하기
CREATE TABLE EMP_PK(
EMPNO NUMBER(4) CONSTRAINT EMPFK_EMPNO_PK PRIMARY KEY,
ENAME VARCHAR2(10),
DEPTNO NUMBER(2) CONSTRAINT EMPFK_DEPTNO_FK REFERENCES DEPT_FK(DEPTNO));
-->테이블 또는 뷰가 존재하지 않습니다. 

CREATE TABLE DEPT_FK(
DEPTNO NUMBER(2) CONSTRAINT DEPTFK_DEPTNO_PK PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13));

--EMP_FK 테이블에 데이터 삽입
INSERT INTO EMP_PK
VALUES(9999,'TEST_NMAME','TEST_JOB',3000,NULL,10);
-->값의 수가 너무 많습니다
--DEPT_FK에 데이터 삽입하기
INSERT INTO DEPT_FK
VALUES(10,'TEST_DNAME','TEST_LOC');
SELECT*FROM DEPT_FK;

--DEPT_FK 테이블의 10번 부서 데이터 삭제하기
DELETE FROM DEPT_FK
WHERE DEPTNO=10;


*수업중 테스트 코드*
--같은 내용의 행 있음
INSERT INTO EMP(EMPNO, DEPTNO)
VALUES(7369,20);
--SAL에 NULL관련 이슈
INSERT INTO EMP(SAL, DEPTNO)
VALUES(7369,20);
 
INSERT INTO EMP(EMPNO, DEPTNO)
VALUES(7777,20);

SELECT *FROM EMP;
SELECT * FROM DEPT;

--부모키는 DEPTNO가 40번대까지 있음,
INSERT INTO EMP(EMPNO, DEPTNO)
VALUES(7778,50);

ROLLBACK;

--행 삭제
DELETE FROM EMP
WHERE EMPNO=7777;

INSERT INTO EMP(EMPNO, DEPTNO)
VALUES(7778,40);

COMMIT;

SELECT *FROM DEPT;
--DEPT에서 40번 먼저 삭제
DELETE FROM EMP WHERE  DEPTNO=40;
COMMIT;
--조건으로 BOSTON 삭제
DELETE FROM DEPT 
WHERE LOC = 'BOSTON';
--순서대로하지않고, LOC에서 BOSTON 먼저 삭제하려고하면 안된다. 
COMMIT;

SELECT*FROM DEPT;

ROLLBACK;

SELECT*FROM DEPT;
*DEPT 에서 10번 부서를 삭제하라
DELETE FROM EMP WHERE DEPTNO=10;
DELETE FROM DEPT WHERE LOC='NEW YORK';
ROLLBACK;

*데이터 형태와 범위를 정하는 CHECK
*기본값을 정하는 DEFAULT

 

 

 

※데이터 모델링

1.데이터 모델링이란

-데이터베이스 모델링 개념

 : 현 세계에서 사용되는 작업이나 사물들을DBMS의 데이터베이스 개체로 옮기는 과정,

현실에서 쓰이는 것을 테이블로 변경하기 위한 작업

 

※2단계 데이터 모델링

*개념적 데이터 모델링 : 현실 세계의 중요 데이터를 추출하여 개념 세계로 옮기는 작업 예)개체-관계 모델

*논리적 데이터 모델링 : 개념 세계의 데이터를 데이터베이스에 저장하는 구조로 표현하는 작업 예)관계 데이터 모델

 

※개체

-사람이나 사물과 같이 현실 세계에서 조직을 운영하는 데 꼭 필요한 구별되는 모든 것

-다른 개체와 구별되는 이름을 가지고 있고, 각 개체만의 고유한 특성이나 상태, 즉 속성을 하나 이상 가지고 있음

예) 서점에 필요한 개체 : 고객, 책

예) 학교에 필요한 개체 : 학과, 과목

-E-R 다이어그램에서 사각형으로 표현하고 사각형 안에 이름을 표기

 

※속성

-개체나 관계가 가지고 있는 고유의 특성

-의미 있는 데이터의 가장 작은 논리적 단위

-파일 구조의 필드(field)와 대응됨

-E-R 다이어그램에서 타원으로 표현하고 타원 안에 이름을 표기

*속성의 분류

-단일 값 속성 : 값을 하나만 가질 수 있는 속성

예) 고객 개체의 이름, 적립금 속성

 

-다중 값 속성 : 값을 여러 개 가질 수 있는 속성

예)고객 개체의 연락처 속성(여러곳)

예)책 개체의 저자 속성(공동저자

-E-R 다이어그램에서 이중 타원으로 표현

 

-단순 속성 :  의미를 더는 분해할 수 없는 속성

예) 고객 개체의 적립금 속성

예) 책 개체의 이름,  ISBN, 가격 속성

 

-복합 속성: 의미를 분해할 수 있는 속성

예) 고객 개체의 주소 속성 : 도,시,동, 우편번호 등으로 의미를 세분화 할 수 있음

예) 고객 개체의 생년월일 속성 : 연,월 일로 의미를 세분화 할 수 있음

 

-유도 속성 : 기존의 다른 속성의 값에서 유도되어 결정되는 속성(값이 별도로 저장되지 않음)

예) 책 개체의 가격과 할인율 속성으로 계산되는 판매가격 속성

예) 고객 개체의 출생연도 속성으로 계산되는 나이 속성

E-R 다이어그램에서 점선 타원으로 표현

 

*키 속성 

-각 개체 인스턴스를 식별하는 데 사용되는 속성

-모든 개체 인스턴스의 키 속성 값이 다름

-둘 이상의 속성들로 구성되기도 함 > 고객 개체의 고객아이디 속성

>E-R 다이어그램에서 밑줄로 표현

 

※ 관계(relationship)

-개체와 개체가 맺고 있는 의미 있는 연관성

-개체 집합들 사이의 대응 관계, 즉 매핑을 의미> 고객 개체와 책 개체 간의 구매 관계

예)고객은 책을 구매한다.

-E-R 다이어그램에서 마름모로 표현

 

※ 관계의 종류 : 매핑 카디널리티 기준

-매핑 카디널러티 : 관계를 맺는 두 개체 집합에서, 각 개체 인스턴스가 연관성을 맺고 있는 상대 개체 집합의 인스턴스 개수

-개체 인스턴스 : 개체를 구성하고 있는 속성이 실제 값을 가짐으로써 실체화된 개체

<김현준, 서울시 구로구 , 02-111-1111,1000>

*일대일(1:1) 관계

: 개체 A의 각 개체 인스턴스가 개체 B의 개체 인스터스 하나와 관계를 맺을수 있고, 개체 B의 각 개체 인스턴스도 개체 A의 인스턴스 하나와 관계를 맺을 수 있음

*일대다(1:N) 관계

:개체 A의 각 객체 인스턴스가 개체 B의 개체 인스턴스 여러 개와 관계를 맺을 수 있지만, 개체 B의 각 개체 인스턴스는 개체 A의 개체 인스턴스 하나와  관계를 맺을 수 있음

*다대다(N:M) 관계

:개체 A의 각 개체 인스턴스가 개체 B의 개체 인스턴스 여러개와 관계를 맺을 수 있고, 개체B의 각 개체 인스턴스도 개체 A의 개체 인스턴스 여러 개와 관계를 맺을 수 있음

 

※E-R 다이어그램

-사각형 : 개체를 표현

-마름모 : 관계를 표현

-타원 : 속성을 표현

-링크(연결선): 각 요소를 연결

-레이블 : 일대일, 일대다, 다대다 관계를 표기

 

 

※논리적 데이터 모델링

-E-R 다이어그램으로 표현된 개념적 구조를 데이터베이스에 저장할 형태로 표현한 논리적 구조

 데이터베이스의 논리적 구조 = 데이터베이스 스키마

-사용자가 생각하는 데이터베이스의 모습 또는 구조

-논리적 데이터모델링의 종류

 

※관계 데이터 모델

-일반적으로 많이 사용되는 논리적 데이터 모델

-데이터베이스의 논리적 구조가 2차원 테이블 형태임

 

※계층 데이터 모델

-데이터베이스의 논리적 구조가 트리 TREE 형태임

-루트 역할을 하는 개체가 존재하고 사이클이 존재하지 않음

-개체 간에 상하 관계가 성립(부모개체/자식 개체)

-두 개체 사이에 하나의 관계만 정의할 수 있음

-다대다 관계를 직접 표현할 수 없음

-개년ㅁ적 구조를 모델링하기 어려워 구조가 복잡해질 수 있음

-데이터의 삽입/삭제/수정/검색이 쉽지 않음

 

※네트워크 데이터 모델

-데이터베이스의 논리적 구조가 네트워크, 즉 그래프 형태임

-개체 간에는 일대다 관계만 허용됨(오너/멤버)

-두 개체 사이에 여러 관계를 정의할 수 있어 이름으로 구별함

-다대다 관계를 직접 표현할 수 없음

-구조가 복잡하고 데이터의 삽입/삭제/수정/검색이 쉽지 않음

 

 

 

※관계형 데이터 모델링

-개념적 구조를 논리적 구조로 표현하는 논리적 데이터 모델

-하나의 개체에 대한 데이터를 하나의 릴레이션에 저장

 

*관계 데이터 모델의 기본 용어

-릴레이션 : 하나의 개체에 관한 데이터를 2차원 테이블의 구조로 저장한 것,

파일 관리 시스템 관점에서 파일에 대응

-속성:릴레이션의 열, 애트리뷰트, 파일 관리 시스템 관점에서 필드에 대응

-투플:릴레이션의 행, 파일 관리 시스템 관점에서 레코드에 대응

-도메인: 하나의 속성이 가질 수 있는 모든 값의 집합,

속성 값을 입력 및 수정할 때 적합성 판단의 기준이 됨,

일반적으로 속성의 특성을 고려한 데이터 타입으로 정의

-널(NULL) : 속성 값을 아직 모르거나, 해당되는 값이 없음을 표현

-차수: 하나의 릴레이션에서 속성의 전체 개수

-카디널리티 : 하나의 릴레이션에서 투플의 전체 개수

 

※데이터베이스의 구성

*데이터베이스 스키마

-데이터베이스의 전체 구조

-데이터베이스를 구성하는 릴레이션 스키마의 모음

 

*데이터베이스 인스턴스

-데이터베이스를 구성하는 릴레이션 인스턴스의 모음

 

※ 릴레이션의 특성

-투플의 유일성 : 하나의 릴레이션에는 동일한  투플이 존재할 수 없다.

-투플의 무순서 : 하나의 릴레이션에서 투플 사이의 순서는 무의미하다

-속성의 무순서 : 하나의 릴레이션에서 속성 사이의 순서는 무의미하다.

-속숭의 원자성 : 속성 값으로 원자 값만 사용할 수 있다.

 

※키(key) : 릴레이션에서 투플들을 유일하게 구별하는 속성 또는 속성들의 집합

*키의 특성

-유일성 : 하나의 릴레이션에서 모든 투플은 서로 다른 키 값을 가져야 함

-최소성 : 꼭 필요한 최소한의 속성들로만 키를 구성함

 

*키의 종류

-슈퍼키 : 유일성을 만족하는 속성 또는 속성들의 집합

예)고객 릴레이션의 슈퍼키: 고객아이디(고객아이디, 고객이름), (고객이름, 주소) 등

-후보키 : 유일성과 최소성을 만족하는 속성 또는 속성들의 집합

예)고객 릴레이션의 후보키: 고객아이디,(고객이름, 주소)등

-기본키 : 후보키 중에서 기본적으로 사용하기 위해 선택한 키

예)고객 릴레이션의 기본키: 고객아이디

-대체키 : 기본키로 선택되지 못한 후보키

예)고객릴레이션의 대체키: (고객이름, 주소)

-외래키 : 다른 릴레이션의 기본키를 참조하는 속성 또는 속성들의 집합,

릴레이션들 간의 관계를 표현