도메인, Key, 참조무결성, SQL

서현서현·2022년 2월 8일

참조무결성

DB, SQL

목록 보기

2/27

🚀 도메인

: 각 속성들이 가질 수 있는 값의 집합

(ex) 성별의 도메인은 남과 여, 학년의 도메인은 1학년,2학년,3학년

🚀 KEY

: 그값이 나머지 모든 행위의 값과 구별되는 속성을 가진 값

(속성) not null, 중복(duplicate) X

기본키 설정을 위해 후보키(Candidate key)를 정한다.
(기본키성질에 위배되지 않으며, 동명이인과 같은 경우가 없는 경우)
선택되어진것이 기본키(Primary Key), 그외의 것들이 대체키(Alternate Key)

Candidate key = Primary Key + Alternate Key

복합키(Composite key) : 하나의 칼럼이 후보키의 역할을 하지 못하고 두개 이상의 칼럼이 합쳐져야 후보키의 역할을 하는 경우

(ex1) 한 학생이 두개이상의 동아리에 가입할 경우 학번과 동아리이름을 합쳐서 기본키가 되어진다.

(ex2) 한 고객이 여러가지 상품을 살 수 있음 = 1:N 관계

이것이 연속되면 = N:M 관계 (이것은 개념모델링에서만 허용되며 논리,물리 모델링에선 불허한다.) 따라서 다 대 다 관계를 없애기 위해서 테이블을 하나 더 만든다.

상품 - 고객을 상품 - 주문 - 고객 테이블로 만드는것이다

위 그림에서 주문테이블의 키는 복합키일까?

부모테이블의 기본키가 자식테이블의 기본키가 되는것을 Foreign Key, 즉 외래키 라고 한다.

🚨 외래키 헷갈림주의!
그렇다면 외래키는 다 기본키인가?
: 아님! 직선으로 연결될때만그렇다.

고객번호+상품코드 = 복합키

직선은 관계를 의미하며, 관계가 존재하므로 Join연산 가능하다.

타 테이블의 기본키를 가져왔으므로 외래키이면서
맨위에 정의되니 기본키를 의미하고
하나가 아니고 두개가 결합된 것 이니까 복합키이다

그런데, 중복이 발생한다.
예를들어 고객번호 : a001 / 상품코드 : p101 / 갯수 : 10개 를 테이블에 넣었을때, 테이블은 중복을 허용하지 않으므로 모든 상품을 딱 한번씩 밖에 사지 못하게 된다.

Error! 이를 정규화를 통해 해결해야 한다.

<알아둬야 할것>

직선으로 연결 되어진것(Identifying) = 식별자관계
점선은 비식별관계 = 뭐가 사라져도 살아있어야..
기본키의 항이 많으면 많을수록 WHERE절이 길어짐..
기본키가 변하면 나머지값도 변해야한다. 변하지 않으면 종속이 안된것이다
부서코드 바뀌면 부서이름 바뀜.
사원이름 바뀌어도 부서이름 바뀌지 않는다.
제1정규화 중복이 반복될수있는거
제2정규화 복합키에 완전종속 안되고...
제3정규화 1번항목이 지들끼리 주종맺음...
이런게 정규화인데 나중에 자세히 글써볼것!

🚀 예시1

사원은 부서를 반드시 가져야된다. 부서가 없는사원은 존재할수없다.

외래키는 참조 무결성 유지해야한다.

EMP TABLE의 50번 아이디를 갖고 DEPT TABLE에서 50번인 부서 찾는데 없다? >> 그렇다면 EMP TABLE에 50을 바로 삽입하지 않는다. DEPT TABLE은 부서코드를 꼭 갖고있어야 한다. 만약 그 부서를 없앨 예정이여도 삭제하면 안되는것임!

왜 삭제가 안될까?

상품번호가 100번일때 100번상품을 많이 팔았다. 근데 a의 외상값이 있을때 그 제품코드를 실수로 날려버려 a한테 외상값을 받지 못했다. 그래서 지우지 못하게 만들었다. (참조되어지는 테이블을 말하는것임 EX.dept 테이블) 만약 삭제하고 싶다면 자식 다 삭제하고 더이상 해당되는 부모가 없을때 할수있다.
이게바로 외래키관계 때문에 발생한 참조무결성!

🚀 예시2

사원테이블이 부서테이블을 참조하고있다.

Q1. 부서테이블의 첫번째 튜플이 삭제된다면?
Q2. 부서테이블의 100번 번호가 110으로 변경된다면?
Q3. 사원테이블에 부서번호 500인 사원이 삽입된다면?

A1. 참조무결성을 위배하게된다.

부서 테이블의 첫 튜플을 삭제하려할때
- 제한(RESTRICT) : 삭제하려는 부서의 부서번호를 사원테이블에서 가지고 있는 튜플이 있으므로 삭제연산 거절
- 연쇄(CASCADE) : delete cascade <>update cascade 사원테이블 삭제된 후 부서테이블 삭제
- 널값으로 대체(nullify) : 삭제연산 수행 뒤 삭제부서번호의 사원테이블 튜플에서 부서번호를 null로 대체 (거의 안씀)

A2. 사원테이블도 같이 바꿔줘야하는데 힘들다. 강제로 삭제해서 바꾸는건데 cascade옵션이나 관계를 끊는 등의 방법이 있다. (추천 X)
A3. 두 테이블의 불일치 발생

(CF)
외래키를 통해 두 테이블간의 데이터 무결성을 유지하는 것을 ‘참조 무결성 제약조건’이라고 함

오라클은 정해진 데이터 크기보다 큰자료가 들어오면 잘려서 저장이 안된다. 정해진 기억공간보다 큰 자료는 무조건 ERROR

🚀 SQL

결과중심적 언어 (절차중심 X)
비절차적 언어

마이바티스를 이용할때 설정부터 연동시켜주는것, xml(마크업언어를 만드는 언어)형식으로 정의되어지며 inr이라는 설정파일안에 다 저장해놓고 접속되어서 실행된다. 요즘 대부분의 프로젝트는 myBatis나 기타 프로그램 이용해서 Sql문과 개발언어를 연동하는 방법을 사용.

즉, 내장sql 사용법중 프레임워크를 이용하는게 가장 보편적

DDL(DATA DEFINITION LANGUAGE)

1. CREATE TABLE

테이블 생성 명령
(사용형식)

CREATE TABLE 테이블명(
컬럼명 데이터타입[(크기)] [NOT NULL] [DEFAULT 값] [,]
                    :
컬럼명 데이터타입[(크기)] [NOT NULL] [DEFAULT 값] [,]
[CONSTRAINT 기본키설정명 PRIMARY KEY(컬럼명[,컬럼명,...])[,]]
[CONSTRAINT 외래키설정명 FOREIGN KEY(컬럼명[,컬럼명,...])
 REFERENCES 참조테이블명(컬럼명)[,]
                    :
[CONSTRAINT 외래키설정명 FOREIGN KEY(컬럼명[,컬럼명,...])
 REFERENCES 참조테이블명(컬럼명)[,];  

. 테이블명과 컬럼명은 사용자정의어를 이용하여 구성
    =>첫 글자는 영문자이고 두번째 글자부터 숫자도 사용가능
      특수문자는 '_'와'$'만 사용가능
      예약어와같은 단어 사용할 수 없음
. '데이터타입' : CHAR,VARCHAR2,DATE,NUMBER,CLOB,BLOB,BFILE 등 사용
. '기본키설정명' : 기본키를 설정한것에 대한 인덱스로 중복된 단어를 사용 할 수 없다
. '외래키설정명' : 외래키를설정한 것에 대한 인덱스로 중복된 단어를 사용할수 없다
. '참조테이블명' : 부모테이블명

Default는 Nullable하다.

🚀실습

상품테이블 = 상품번호 상품명 단가
주문테이블 = 주문번호 고객번호 상품번호 일자 수량
고객테이블 = 고객번호 고객명 주소 전화번호 마일리지

CREATE TABLE CUSTOMERS(
    CUST_ID CHAR(6), --PK
    CUST_NAME VARCHAR(50) NOT NULL,
    CUST_ADDR VARCHAR(100),
    CUST_TEL VARCHAR2(20),
    CUST_MILEAGE NUMBER(5) DEFAULT 0,
    CONSTRAINT pk_customers PRIMARY KEY(CUST_ID));