26일차 - ERD/UPDATE/DELETE/외래키 삭제 조건/JOIN

데이터 모델링  

 

관계(Relationship)

두 개체에 관련이 있을 경우 1:1, 1:n, n:m 등의 관계를 맺을 수 있습니다.

 

1:1 관계

→ 개체들이 하나의 관계만을 갖는 것을 의미합니다.

→ ex) 학생이 수학 시험을 무조건 응시한다고 가정했을 때, 한 명의 학생은 반드시 하나의 수학 점수를 갖습니다.

→ 학생 : 수학 점수 → 1:1 관계

 

1:n 관계

→ 하나의 개체가 다른 개체의 여러 객체를 가질 수 있는 것을 의미합니다.

→ ex) 학생이 국어,영어,수학 시험을 응시한다고 가정했을 때, 한 학생은 3개 과목의 성적을 갖습니다.

→ 학생 : 성적 → 1:n 관계

 

 

n:m 관계

→ A개체가 B개체에 1:n 관계를 갖고, B개체도 A개체에 1:n 관계를 갖습니다.

→ ex) 한 학원에는 여러 학생이 있고, 한 학생은 여러 학원을 다닐 수 있습니다.

→ 학생 : 학원 → n:m 관계

 

ERD에서 관계는 다음과 같이 나타냅니다.(까마귀 발)

 

ERD 작성

1. draw.io를 접속하여 Device - Create New Diagram을 클릭합니다.

 

2. 파일명을 설정, Entity Relationship Diagram을 선택하고 Create를 클릭한다.

 

3. ERD를 작성합니다.

 

ERD 예제

시나리오 : 땡땡 중학교의 성적 처리를 합니다.
논리 모델에 필요한 개체는 "학생"과 "교과목"입니다.
학생 개체의 속성(필드)은 학번,이름,주소,나이,성별 
교과목은 과목코드, 과목명, 학점, 담당교사

문제 : 개체 사이의 관계("성적")를 포함하여 논리 모델 완성합니다.
- 관계의 속성(필드)는 여러분이 완성하세요.
- PK, FK 여러분이 완성하세요.
- 까치발 표기 여러분이 완성하세요.

→ 학생 1명은 최소 1개 과목 이상 시험 성적이 있어야 하므로 1:n관계입니다. → 1:(1~n)

→ 교과목과 성적은 1:n 관계이고, 시험 안 본 과목이 있을 경우 1:0일 수 있습니다. → 1:(0~n)

→ 1:n 관계에서 1 개체의 기본 키(PK)가 n 개체에서 외래 키(FK)로 존재합니다.

 

UPDATE

 

형식: UPDATE 테이블_이름 SET 수정할 열_이름1=수정할 값1, ... WHERE 수정할 행에 대한 조건식 ;

→ 테이블에서 조건식에 부합하는 행의 특정 컬럼 값을 수정합니다.

→ WHERE 조건절을 사용하지 않을 경우, 모든 행의 데이터가 수정될 수 있습니다.

→ WHERE 조건절을 사용하는 것이 안전합니다.

 

예시

→ tbl_custom# 테이블에서 custom_id가 'twice'인 행의 age 열의 값을 39로 변경합니다.

 

→ 조건식에 기본키 값 일치 조건을 사용하면 1개의 행만 수정됩니다. 

 

→ IN 연산자로 여러 번의 OR 연산자를 대체할 수 있습니다.

 

DELETE

 

형식: DELETE FROM 테이블_이름 WHERE 삭제할 행에 대한 조건식 ;

→ 테이블에서 조건식에 부합하는 행을 삭제합니다.

→ WHERE 조건절을 사용하지 않을 경우, 모든 행의 데이터가 삭제될 수 있습니다.

→ WHERE 조건절을 사용하는 것이 안전합니다.

 

예시

→ 샘플 데이터 추가: tbl_product# 테이블에 새로운 행을 추가합니다.

 

→ 1) 실행: pcode가 'GAL0112'인 행을 삭제합니다.

→ 2) 오류: 'IPAD011'값의 pcode는 tbl_buy#에서 참조하고 있으므로 삭제할 수 없습니다.

→ 다른 테이블에서 참조 중인 값은 삭제할 수 없습니다.

 

→ 1) 실행: pcode가 'IPAD011'인 행의 pname을 '아이패드프로'로 변경합니다.

→ 2) 오류: 'IPAD011'값의 pcode는 tbl_buy#에서 참조하고 있으므로 pcode 값을 변경할 수 없습니다.

 

→ 다른 테이블에서 참조 중인 값은 변경/삭제할 수 없습니다.

 

외래키 삭제/ 제약조건 생성

 

ALTER TABLE 형식 : ADD, DROP, MODIFY(제약 조건은 불가능)

→ 제약 조건은 변경이 불가능합니다.

→ 속성을 바꾸고 싶다면 삭제 후 다시 생성해야 합니다.

→ 오라클에서는 ON UPDATE를 지원하지 않습니다. (탭에는 존재)

→ 외래 키를 삭제 후 ON DELETE 속성을 변경하여 다시 생성합니다.

→ ON DELETE CASCADE: 다른 테이블에서 참조중인 값을 삭제하면, 참조 테이블에서 그 값을 갖는 행도 삭제됩니다.

→ NO ACTION(기본값): 다른 테이블에서 참조중인 값은 삭제할 수 없습니다.

 

SELECT - JOIN

 

→ 둘 이상의 테이블(주로 참조 관계의 테이블)을 연결하여 데이터를 조회하는 명령입니다.

→ 둘 이상의 테이블은 공통된 컬럼을 갖고, 이 컬럼을 이용하여 JOIN합니다.

→ 동등(INNER) 조인과 외부(OUTER) 조인이 있습니다.

 

동등 조인 형식:

→ 1) SELECT 열_이름 FROM 테이블 1 a, 테이블 2 b, ... WHERE a.공통컬럼1=b.공통컬럼1 ;

→ 2) SELECT 열_이름 FROM 테이블 1 a JOIN 테이블 2 b ON a.공통컬럼1=b.공통컬럼1 ;

→ 동등 조인은 공통 컬럼의 값 중 둘 다 있는 값만 표시합니다.

 

예시

→ SELECT 뒤의 열_이름에 별칭(tp.)으로 소속 테이블을 명시할 수 있습니다.

 

→ 공통 컬럼(pcode)에는 소속 테이블을 반드시 명시해야 한다. 안하면 오류가 발생합니다.

 

외부 조인 형식(LEFT 기준):

→ 1) SELECT 열_이름 FROM 테이블 1 a, 테이블 2 b, ... WHERE a.공통컬럼1=b.공통컬럼1(+) ;

→ 2) SELECT 열_이름 FROM 테이블 1 a LEFT(RIGHT) OUTER JOIN 테이블 2 b ON a.공통컬럼1=b.공통컬럼1 ;

→ LETT OUTER 조인은 왼쪽 컬럼에 있는 값을 기준으로 JOIN합니다.