DATABASE STEP 20 - RDBMS&Foreign key

★ 관계형 데이터베이스 시스템(RDBMS)이 지양하는 사항

1. 테이블에 기본키가 없는 상태 > 데이터 조작 곤란
2. null이 많은 상태의 테이블 > 데이터 조작 곤란 + 공간 낭비
3. 데이터가 중복되는 상태 > 데이터 조작 곤란 + 공간 낭비
4. 하나의 속성값이 원자값이 아닌 상태

■ 1. 테이블에 기본키가 없는 상태 > 데이터 조작 곤란(3번과 동일)

create table tbltest (
    name varchar2(30) not null,
    age number(3) not null,
    address varchar2(300) not null
);

insert into tblTest (name, age, address) values ('홍길동', 20, '서울시');
insert into tblTest (name, age, address) values ('아무개', 25, '인천시');
insert into tblTest (name, age, address) values ('호호호', 27, '부산시');

insert into tblTest (name, age, address) values ('홍길동', 20, '서울시'); -- 중복된 값이 들어감

 

■ 2. null이 많은 상태의 테이블 > 데이터 조작 곤란 + 공간 낭비

create table tbltest (
    name varchar2(30) not null,
    age number(3) not null,
    address varchar2(300) not null,
    hobby varchar2(300) null
);

insert into tblTest (name, age, address, hobby) values ('홍길동', 20, '서울시', '독서');
insert into tblTest (name, age, address, hobby) values ('아무개', 25, '인천시', 'null');
insert into tblTest (name, age, address, hobby) values ('호호호', 27, '부산시', '맛집,여행,운동,낮잠');
insert into tblTest (name, age, address, hobby) values ('후후후', 29, '부산시', '운동유튜브보기');

select * from tblTest;

-- 독서가 취미인 사람?
select * from tbltest where hobby = '독서';

-- 운동이 취미?
select * from tbltest where hobby = '운동';
select * from tbltest where hobby like '%운동%';

create table tbltest (
    name varchar2(30) not null,
    age number(3) not null,
    address varchar2(300) not null,
    hobby1 varchar2(300) null,
    hobby2 varchar2(300) null,
    hobby3 varchar2(300) null
    ..
    hobby8 varchar2(300) null
);

-- 홍길동, 20, 서울시, 독서, null, null, null, null, null, null, null
-- 아무개, 25, 인천시, null, null, null, null, null, null, null, null
-- 호호호, 27, 부산시, 운동, 맛집, 낮잠, null, null, null, null, null
-- 후후후, 29, 서울시, 취미 8개를 적음.

-- 취미가 운동?
select * from tblTest where hobby1 = '운동' or hobby2 = '운동' .... -- 지속적으로 계속 or 해줘야하는 문제가 있음..

 

■ 한개의 테이블로 이루었을 경우의 문제

-- 직원 정보
-- 직원(번호(pk), 이름, 급여, 거주지, 담당프로젝트)
create table tblStaff (
    seq number primary key,         -- 번호(pk)
    name varchar2(30) not null,      -- 이름
    salary number not null,            -- 급여
    address varchar2(300) not null,  -- 거주지
    project varchar2(300) null         -- 담당 프로젝트
);

insert into tblStaff (seq, name, salary, address, project)
        values (1, '홍길동', 300, '서울시', '홍콩 수출');
        
insert into tblStaff (seq, name, salary, address, project)
        values (2, '아무개', 250, '인천시', 'TV 광고');
        
insert into tblStaff (seq, name, salary, address, project)
        values (3, '하하하', 350, '부산시', '매출 분석');
        
select * from tblStaff;

-- 직원 + 담당프로젝트
-- 1명의 직원 > 여러개의 프로젝트 담당 가능

-- 방법 1 : insert로 하나의 레코드를 더 추가
-- '홍길동'에게 담당 프로젝트 1건 추가 > '고객관리'
insert into tblStaff (seq, name, salary, address, project)
        values (4, '홍길동', 300, '서울시', '고객관리' );
        
-- 방법 2 : update로 project 컬럼에 하나의 데이터를 더 추가
-- '아무개' 에게 담당 프로젝트 1건 추가 > '게시판 관리'
update tblStaff set project = project || ', 게시판 관리' where name = '아무개';

drop table tblStaff;

 

■ 두개의 테이블로 나누었을 경우

• 테이블 생성

create table tblStaff (
    seq number primary key,         -- 번호(pk)
    name varchar2(30) not null,      -- 이름
    salary number not null,            -- 급여
    address varchar2(300) not null  -- 거주지
);

create table tblProject (
    seq number primary key,               -- 번호(pk)
    project varchar2(300) not null,         -- 담당 프로젝트
    staff_seq number not null               -- 직원번호
);

insert into tblStaff (seq, name, salary, address) values (1, '홍길동', 300, '서울시');
insert into tblStaff (seq, name, salary, address) values (2, '아무개', 250, '인천시');
insert into tblStaff (seq, name, salary, address) values (3, '하하하', 350, '부산시');

insert into tblProject (seq, project, staff_seq) values (1, '홍콩 수출', 1);            -- 홍길동
insert into tblProject (seq, project, staff_seq) values (2, 'TV 광고', 2);               -- 아무개
insert into tblProject (seq, project, staff_seq) values (3, '매출 분석', 3);            -- 하하하
insert into tblProject (seq, project, staff_seq) values (4, '노조 협상', 1);            -- 홍길동
insert into tblProject (seq, project, staff_seq) values (5, '대리점 분양', 2);         -- 아무개

-- 원자 값 보장 & 중복 발생 X > 정규화
select * from tblStaff;
select * from tblProject;

• 상황에 따른 문제점

-- 상황 A. 신입 사원 입사 > 신규 프로젝트 담당
-- A.1 신입 사원 추가(O)
insert into tblStaff (seq, name, salary, address) values (4, '호호호', 250, '일산시');

-- A.2 신규 프로젝트 추가 + 담당 지정
insert into tblProject (seq, project, staff_seq) values (6, '자재 매입', 4);

-- A.3 신규 프로젝트 추가 > 문제 발생 > 존재 하지 않는 직원 번호를 사용!
insert into tblProject (seq, project, staff_seq) values (7, '고객 유치', 5);

-- 사장 > '고객 유치' 담당자를 불러오라고 명령
-- 프로젝트는 진행 중인데, 5번 직원이라는 직원이 없음... 심각한 문제
select * from tblStaff 
    where seq = (select staff_seq from tblProject where project = '고객 유치');

select * from tblStaff;
select * from tblProject;

-- 상황 B. '홍길동' 퇴사
-- B.1 '홍길동' 정보 삭제 > 문제 발생
delete from tblstaff where seq = 1;

-- 사장 > '홍콩 수출' > 담당자 불러오라고 명령
-- 홍길동 퇴사로 인해, 해당 담당 직원이 없음..
select * from tblStaff 
    where seq = (select staff_seq from tblProject where project = '홍콩 수출');

-- B.2 정상 시나리오 : '아무개' 퇴사 > 인수 인계(위임) > '하하하' 
update tblProject set staff_seq = 3 where staff_seq = 2;

update tblProject set staff_seq = (select seq from tblStaff where name = '하하하') where staff_seq = (select seq from tblstaff where name ='아무개');

-- B.3 '아무개' 퇴사
delete from tblStaff where seq = 2;

-- 사장 > ' TV광고'  > 담당자 불러오라고 명령
select * from tblStaff 
    where seq = (select staff_seq from tblProject where project = 'TV 광고');

 

■ 상위 문제를 해결하기 위한 해결안

• 외래키를 이용하여 해결

-- 부모 테이블(기본 테이블)
create table tblStaff (
    seq number primary key,         -- 번호(pk)
    name varchar2(30) not null,      -- 이름
    salary number not null,            -- 급여
    address varchar2(300) not null  -- 거주지
);

-- 자식 테이블(참조 테이블)
create table tblProject (
    seq number primary key,               -- 번호(pk)
    project varchar2(300) not null,         -- 담당 프로젝트
    staff_seq number not null references tblStaff(seq)   -- 직원번호(참조키(외래키), FK) : tblstaff의 키를 참조한다. 
);

insert into tblStaff (seq, name, salary, address) values (1, '홍길동', 300, '서울시');
insert into tblStaff (seq, name, salary, address) values (2, '아무개', 250, '인천시');
insert into tblStaff (seq, name, salary, address) values (3, '하하하', 350, '부산시');

insert into tblProject (seq, project, staff_seq) values (1, '홍콩 수출', 1);            -- 홍길동
insert into tblProject (seq, project, staff_seq) values (2, 'TV 광고', 2);               -- 아무개
insert into tblProject (seq, project, staff_seq) values (3, '매출 분석', 3);            -- 하하하
insert into tblProject (seq, project, staff_seq) values (4, '노조 협상', 1);            -- 홍길동
insert into tblProject (seq, project, staff_seq) values (5, '대리점 분양', 2);         -- 아무개

select * from tblStaff;
select * from tblProject;

• 상황에 따른 문제점

-- 상황 A. 신입 사원 입사 > 신규 프로젝트 담당
-- A.1 신입 사원 추가(O)
insert into tblStaff (seq, name, salary, address) values (4, '호호호', 250, '일산시');

-- A.2 신규 프로젝트 추가 + 담당 지정
insert into tblProject (seq, project, staff_seq) values (6, '자재 매입', 4);

-- A.3 신규 프로젝트 추가 > 문제 발생 > 존재 하지 않는 직원 번호를 사용!
-- ORA-02291: integrity constraint (HR.SYS_C007148) violated - parent key not found
insert into tblProject (seq, project, staff_seq) values (7, '고객 유치', 5);

-- 상황 B. '홍길동' 퇴사
-- B.1 '홍길동' 정보 삭제 > 문제 발생
-- ORA-02292: integrity constraint (HR.SYS_C007148) violated - child record found
delete from tblstaff where seq = 1;

-- B.2 정상 시나리오 : '아무개' 퇴사 > 인수 인계(위임) > '하하하' 
update tblProject set staff_seq = 3 where staff_seq = 2;

update tblProject set staff_seq = (select seq from tblStaff where name = '하하하') where staff_seq = (select seq from tblstaff where name ='아무개');