관계(Relationship)

1. 오라클 데이터베이스는 종류로 나눌때 관계형 데이터베이스로 분류한다.

2. 관계는 테이블(데이터저장소)간의 논리적으로 연결될 수 있는 부분에 대한 연결하는 고리.

예를들어, 학생과 강사 간에는 수업이라는 연결고리가 존재한다.

학생 - (수업) - 강사

수업이라는 행위가 관계가 된다.

3. 데이터베이스에서 관계는 데이터간의 공통점을 찾아서 연결시키는 것.

-> 데이터 중복 최소화의 이점이 있다.

-> 관계형 데이터베이스 구축

--원시데이터(회원테이블)

이름, 전화번호, 주소, 직장명

홍길동, 010-111-1111, 서울, KH정보교육원

김길동, 010-222-2222, 서울, KH정보교육원

박길동, 010-333-3333, 경기, KH정보교육원

--> 중복 최소화하기 위해서 테이블 분리 --> 관계설정

회원테이블 -----------

회원아이디(PK), 이름,   전화번호,     주소아이디(FK), 직장명아이디(FK)

1,              홍길동, 010-111-1111, 1,              1

2,              김길동, 010-222-2222, 1,              1

3,              박길동, 010-333-3333, 2,              1

주소테이블 -----------

주소아이디(PK), 주소

1,              서울

2,              경기

직장명테이블 ---------

직장명아이디(PK), 직장명

1,                KH정보교육원

4. HR 스키마의 경우

윈시데이터

first_name, last_name, department_name(중복데이터), job_title(중복데이터)

--> 관계형 데이터베이스 구축 필요.

-- 관계형 데이터베이스 구축 결과.

employee_id, first_name, last_name, department_id(FK), job_id(FK) -> employees 테이블

department_id(PK), department_name -> departments 테이블

job_id(PK), job_title -> jobs 테이블

5. insa 테이블의 경우

--원시데이터

num, name, ssn, ibsadate, city(중복), tel, buseo(중복), jikwi(중복), basicpay, sudang

--> 관계형 데이터베이스 구축 필요

-- 관계형 데이터베이스 구축 결과.

--member테이블

num(PK), name, ssn, ibsadate, city_id(FK), tel, buseo_id(FK), jikwi_id(FK), basicpay, sudang

--city테이블

city_id(PK), city

--buseo 테이블

buseo_id(PK), buseo

--jikwi 테이블

jikwi_id(PK), jikwi

JOIN

1. 두개 이상의 테이블에서 관련된 정보를 연결해서 출력하는 방법.

2. EQUI JOIN: 두개 이상의 테이블에서 관련된 정보(PK, FK)가 일치하는 경우에만 연결하는 경우.

   EQUI JOIN 형식

--오라클 형식

SELECT 컬럼리스트

FROM 테이블1 별칭1, 테이블2 별칭2

WHERE 별칭1.공통컬럼 = 별칭2.공통컬럼; <- EQUI JOIN 시 필수 조건절이며, = 을 사용한 경우 EQUI JOIN 이라고 칭할 수 있음.

--ANSI 형식(여러 SQL 에서 구동할 수 있도록 정한 표준 형식)

SELECT 컬럼리스트

FROM 테이블1 별칭1 JOIN 테이블2 별칭2

ON 별칭1.공통컬럼 = 별칭2.공통컬럼;    <- 위 구문과 동일한 표현

-- departments 테이블과 관련된 테이블 찾기

1. departments 테이블에서 PK 컬럼을 찾는다. -> departments.department_id

2. departments 테이블과 연결된 테이블을 찾는다. -> ERD에서 관계선을 찾는다. employees 테이블.

3. employees 테이블에서 FK 컬럼을 찾는다. -> employees.department_id

4. departments.department_id 컬럼과 employees.department_id 컬럼이 공통 컬럼.

5. JOIN QUERY 작성

SELECT first_name, last_name, department_name

FROM departments dep, employees emp

WHERE dep.department_id = emp.department_id; <- EQUI JOIN 시 필수 조건절이며, = 을 사용한 경우 EQUI JOIN 이라고 칭할 수 있음.

--  employees, departments 테이블에서 first_name이 'Nancy', last_name이 'Greenberg'인

직원의 부서명(department_name)을 출력.

select first_name, last_name, department_name

from departments d, employees e

where d.department_id = e.department_id and (first_name='Nancy' and last_name='Greenberg');

-- employees, jobs 테이블에서 first_name이 'Nancy', last_name이 'Greenberg'인 직원의 직무명(job_title)을 출력.

select first_name, last_name, job_title

from jobs j join employees e

on j.job_id = e.job_id and first_name='Nancy';

문제) employees, jobs 테이블에서 job_title이 'Finance Manager'인 직원 정보 출력.

select *

from jobs j, employees emp

where j.job_id = emp.job_id and job_title = 'Finance Manager';

문제) departments, employees 테이블에서 부서명(department_name), 부서장의 이름(first_name, last_name) 출력

select first_name, last_name, department_name

from departments dep, employees emp

where dep.manager_id = emp.manager_id;

문제) employees, jobs 테이블. 직위명(job_title)별 최소급여(min_salary)를 받는 직원 정보 출력.

select first_name, last_name, job_title, min_salary, salary

from jobs j , employees emp

where j.job_id = emp.job_id and (j.min_salary = emp.salary);

문제) jobs, employees 테이블. 직위명(job_title)별 직원의 수 출력

select job_title, count(*)

from jobs j, employees emp

where j.job_id = emp.job_id

group by job_title

order by count(*) desc;

문제) jobs, employees 테이블. 직위명(job_title)별 직원의 수 출력. 단 10명 이상인 경우만 출력

select job_title, count(*)

from jobs j, employees emp

where j.job_id = emp.job_id

group by job_title

having count(*) >= 10

order by count(*) desc;

세 개 이상의 테이블 JOIN

1. 형식

--오라클 형식

SELECT 컬럼리스트

FROM 테이블1 별칭1, 테이블2 별칭2, 테이블3, 별칭3

WHERE 별칭1.공통컬럼 = 별칭2.공통컬럼 AND 별칭2.공통컬럼 = 별칭3.공통컬럼;

-- jobs, employees, departments 테이블 join

select first_name, last_name, job_title, department_name

from jobs j, employees e, departments d

where j.job_id = e.job_id and e.department_id = d.department_id;

문제) employees, jobs, department 테이블에서 first_name이 'Nancy', last_name이 'Greenberg'인 직원의 직무명(job_title), 

부서명(department_name)을 출력.

select first_name, last_name, job_title, department_name

from jobs j, employees e, departments d

where j.job_id = e.job_id and e.department_id = d.department_id and (first_name = 'Nancy' and last_name = 'Greenberg')

SELF JOIN

1. 자기 자신(테이블)을 join의 대상으로 지정하는 것.

2. 형식

SELECT 컬럼리스트

FROM 테이블1 별칭1, 테이블1 별칭2  --> 테이블은 같고 별칭은 다르게 지정.

WHERE 별칭1.공통컬럼 = 별칭2.공통컬럼;

-- employees 테이블에서 first_name이 'Steven' last_name이 'King' 직원과 같은 부서 아이디(department_id)를 가진 직원 정보 출력

select e2.first_name, e2.last_name

from employees e1, employees e2

where e1.department_id = e2.department_id and e1.first_name = 'Steven' and e1.last_name = 'King';

-- employees 테이블에서 first_name이 'Alexander' last_name이 'Hunold' 직원과 같은 직위아이디(job_id)를 가진 직원 정보 출력

select e2.first_name, e2.last_name

from employees e1, employees e2

where e1.job_id = e2.job_id and e1.first_name = 'Alexander' and e1.last_name = 'Hunold';

문제) employees 테이블에서 first_name이 'Alexander' last_name이 'Hunold' 직원과 같은 급여(salary)를 가진 직원 정보 출력

select e2.first_name, e2.last_name

from employees e1, employees e2

where e1.salary = e2.salary and e1.first_name = 'Alexander' and e1.last_name = 'Hunold';

문제) employees 테이블에서 first_name이 'Steven' last_name이 'King' 직원의 직속 부하 직원 정보 출력

OUTER JOIN

1. 두개의 테이블을 JOIN한 상태에서 데이터 출력시, 

   양쪽에 동시에 존재하는 경우만 출력되는 경우는 EQUI JOIN 이지만,

   한쪽에만 존재하는 데이터를 출력하는 경우는 OUTER JOIN이라고 함.

예를 들어, 특정 과목에 대한 학생의 성적을 입력하는 과정에서 성적이 모두

입력되지 안은 상태라면, 학생 성적 출력시 EQUI JOIN을 사용하면 성적 처리가

된 학생들의 정보와 성적만 출력된다. OUTER JOIN을 사용하면 성적 처리가 된

학생들의 정보와 성적 외에 성적처리가 되지 않은 학생들의 정보가 같이 출력된다.

2. 형식

SELECT 컬럼리스트                                  -> 테이블1 정보 전체 출력 left outer join

FROM 테이블1 별칭1, 테이블2 별칭2

WHERE 별칭1.공통컬럼 = 별칭2.공통컬럼(+); 

SELECT 컬럼리스트                                  -> 테이블2 정보 전체 출력 right outer join

FROM 테이블1 별칭1, 테이블2 별칭2

WHERE 별칭1.공통컬럼(+) = 별칭2.공통컬럼; 

-- employees 테이블 전체 직원의 수

select count(*)

from employees; -- 107명

-- employees, departments 테이블 EQUI JOIN

select count(*)

from employees e, departments d

where e.department_id = d.department_id; -- 106명

select *

from employees

where department_id is null; -- 178, Kimberely

--LEFT OUTER JOIN(employees 테이블의 정보를 전체 출력)

select first_name, last_name, department_name

from employees e, departments d

where e.department_id = d.department_id(+);

--RIGHT OUTER JOIN(departments 테이블의 정보를 전체 출력)

select first_name, last_name, department_name

from employees e, departments d

where e.department_id(+) = d.department_id;

-- jobs, employees 테이블. left outer join

select job_name, first_name, last_name

from jobs j, employees e

where j.job_id = e.job_id(+);

문제) employees 테이블에서 부하 직원이 없는 직원 출력

데이터베이스 객체

1. 데이터베이스에 저장되는 항목들. 테이블, 뷰, 함수, 인덱스, 프로시져, 커서, 트리거, ........

2. 테이블 객체. 데이터 저장소 역할. 컬럼이라는 단위로 데이터를 구분해서 저장한다.

자료형, 제약조건 지정 필요.

데이터베이스 -> 테이블 스페이스 -> 테이블 -> 컬럼(자료형, 제약조건)

kor, eng, mat 컬럼이 있다면,

숫자를 저장해야 하므로, 자료형은 NUMBER 지정.

입력가능한 숫자 범위를 추가로 지정하려면 제약 조건(CHECK 제약)을 추가 지정.

자바에서는

class Record {    // -> 오라클에서는 이부분이 테이블 객체

private int kor, eng, mat; // -> 오라클에서는 이부분이 컬럼 및 자료형

public void serKor(){

if (kor >= 0 && kor <= 100) { // -> 오라클에서는 이부분이 제약조건

this.kor = kor;

}

}

}

오라클 에서는

CREATE TABLE Record ( 

kor NUMBER CONSTRAINT CHECK (kor >= 0 and kor <= 100)

,eng NUMBER CONSTRAINT CHECK (eng >= 0 and eng <= 100)

,mat NUMBER CONSTRAINT CHECK (mat >= 0 and mat <= 100)

);

3. 객제 관련 명령 (생성, 수정, 삭제)

생성 - CREATE 객체의종류 신규객체명 [객체의내용];

수정 - ALTER 객체의종류 기존객체명 [수정할내용];

삭제 - DROP 객체의종류 기존객체명;

4. 객체 확인(Data Dictionary)

DBA_XXXX : 관리자 전용 정보확인용

USER_XXX : 사용자 전용 정보확인용. 자기 자신이 소유한 객체의 정보만 출력.

ALL_XXXX : 사용자 전용 정보확인용. 범용 정보만 출력.

-- 관리자용 계정정보 확인

SELECT * FROM dba_users;

-- 사용자용 계정정보 확인

select * from all_users;

-- 사용자 전용 소유한 테이블 객체 확인

select * from user_tables;

-- 관리자 전용 테이블 객체 확인

select * from dba_tables;

5. 객체를 생성, 수정 삭제하려면 객체 관련 권한이 필요함.

GRANT 권한의종류 TO 계정명;

--> 권한의종류는 권한명 또는 ROLE 명으로 표기

GRANT CONNECT, RESOURCE TO scott;  -- ROLE

GRANT CREATE VIEW TO scott; -- 권한

select *

from dba_sys_privs

where grantee='RESOURCE';

6. 나의 권한 상태 확인.

select *

from user_sys_privs;

테이블 작성

1. 테이블은 관계형 데이터베이스에서 데이터 저장을 위해 이용되는 객체이며, 행(row)과 열(column)으로 구성된다.

예를 들어, 학생 정보 저장용 테이블이 있다면

column column    column

번호   이름     전화번호

  1   홍길동  010-111-1111  -> row

  2   김길동  010-222-2222  -> row

번호, 이름, 전화번호 항목 저장용 학생 정보 테이블을 생성한다면,

CREATE TABLE Students (

stid NUMBER

,name VARCHAR2(10)

,tel VARCHAR2(20)

);

INSERT INTO Students (stid, name, tel) VALUES (1, '홍길동', '010-111-1111');

INSERT INTO Students VALUES (2, '김길동', '010-222-2222');

2. 테이블 작성 순서

- 항목(컬럼) 결정 : 이름, 전화번호, .....

- 자료형: NUMBER, VARCHAR2,....

- 제약조건: PK, FK, CHECK, .....

- 관계: