[day_06] DB / 11. ERD (Entity Relationship Diagram) - Foreign Key 외래키

 ERD (Entity Relationship Diagram)

  : 개체 관계 모델링, 관계형 데이터 베이스 설계를 위한 다이어그램

 

 

논리 데이터 모델링

- 논리적인 데이터 관리 및 관계를 정의한 모델

- 전체 업무 범위와 업무 구성요소를 확인할 수 있다.

​

물리 데이터 모델링

- 논리 데이터 모델을 DBMS의 특성 및 성능을 고려하여 구체화시킨 모델을 말한다.

---

정규화 (Normalization)

: 데이터베이스 설계 시 중복을 최소화하도록 데이터를 구조화하는 작업
  
사례 )

사원정보를 저장하는 테이블 설계

사원번호	사원명	직종	월급	부서명	부서지역	부서대표번호
1	아이유	개발	1000	연구개발부	판교	0311231234
2	김수민	개발	2000	공공사업부	광화문	024321321

위와 같은 형태로 테이블을 설계하면 중복된 부서 정보가 반복적으로 저장
부서 정보 수정 시 다수의 정보가 변경되어야 한다

 위에 있는 사원 테이블을 분할하는 작업 --> 정규화 작업

 

부서 테이블                 사원 테이블

                      |---------0|<-

 

부서는 사원을 0명에서 다수 사원을 보유할 수 있다

사원은 반드시 하나의 부서에 속해야 한다

 

 

사원 테이블

사원번호

사원명

월급

부서번호

시력

키

몸무게

질환

위 테이블도 분할이 필요 (사원의 신체정보는 특정한 경우에 필요 시에만 조회)

→ 사원 테이블은 신체 정보 테이블로 분할하는 것이 바람직하다. 

(* 사원번호가 외래키 Foreign Key로 설정 )

---

Foreign Key (외래 키)

: 참조 무결성을 위한 제약조건으로, 다른 테이블 정보를 참조하기 위해 사용하는 key

 

ex) 사원 테이블과 신체 정보 테이블이 있고,

신체정보 테이블에서 무결성을 보장하는 데이터를 저장하기 위해서는

사원 테이블의 pk인 사원번호를 참조해야 한다. (제약조건 Foreign key를 설정)

 

---

ERD로 실제로 설계했을 때 모습

식별 관계 identified Relationship

: 부모 테이블의 기본키 혹은 복합키가 자식 테이블의 기본키 혹은 복합키의 구성원으로  전이되는 관계

신체정보 table - 사원 table ⇒ 실선 

 

비식별 관계 non-identified Relationship

: 부모 테이블의 기본키 혹은 복합키가 자식 테이블의 일반 속성(Attribute) 그룹의 구성원으로 전이되는 비 식별관계가 있다.

사원 table - 부서 table ⇒ 점선 

 

---

SQL 

1. DEPARTMENT TABLE 생성 - 부모 테이블 (참조 대상 테이블)

CREATE TABLE DEPARTMENT(
	DEPTNO	NUMBER		PRIMARY KEY,
	DNAME	VARCHAR2(100)	NOT NULL,
	LOC		VARCHAR2(100)	NOT NULL,
	TEL		VARCHAR2(100)	NOT NULL
);

 

2. K_EMPLOYEE TABLE 생성 - 자식 테이블 (참조하는 테이블)

CREATE TABLE K_EMPLOYEE(
	EMPNO	NUMBER			PRIMARY KEY,
	ENAME	VARCHAR2(100)		NOT NULL,
	SAL		NUMBER			NOT NULL,
	DEPTNO	NUMBER			NOT NULL, 
	CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPARTMENT(DEPTNO)
);

 

** CONSTRAINT [제약조건명] FOREIGN KEY ( [컬럼명] ) REFERENCES [참조대상 테이블명] ( [컬럼명] )

: 자식테이블은 FOREIGN KEY가 존재한다.

 

** 테이블 생성은 반드시 부모 테이블을 먼저 만들고 이후에 자식 테이블을 생성해야 한다.

 

3. ERROR : 참조 무결성 제약 조건을 위배 //parent key Not Found ERROR

→ FOREIGN KEY 제약 조건에 의해 부서 테이블에 존재하지 않는 부서번호를 입력하려하여 에러가 발생

INSERT INTO K_EMPLOYEE (EMPNO, ENAME, SAL, DEPTNO) VALUES(1, '앨리스', 500, 10);

 

4. INSERT

INSERT INTO DEPARTMENT (DEPTNO, DNAME, LOC, TEL) VALUES(10, '전략기획', '판교', '031');
INSERT INTO DEPARTMENT (DEPTNO, DNAME, LOC, TEL) VALUES(20, '공공사업', '종로', '02');
INSERT INTO DEPARTMENT (DEPTNO, DNAME, LOC, TEL) VALUES(30, '해양수산', '부산', '051');

 

---