day02[무결성,모델링,DML]

무결성
데이터에 결함이 없어야 함.  데이터의 정확성, 일관성, 유효성이 유지되는 것.
정확성	: 데이터는 애매하지 않아야 한다.
일관성	: 각 사용자가 일관된 데이터를 볼 수 있도록 해야 한다.
유효성	: 데이터가 실제 존재하는 데이터여야 한다.
1.개체 무결성	개체는 하나의 테이블을 의미함 모든 테이블은 PK로 선택된 컬럼을 가져야 한다.
2.참조 무결성	두 테이블의 데이터가 항상 일관된 값을 가지도록 유지하는 것.
3.도메인 무결성	도메인은 컬럼. 컬럼의 무결성. 컬럼의 타입, NULL값의 허용 등에 대한 사항을 정의하고 올바른 데이터가 입력되었는 지를 확인하는 것.

모델링(기획) : 추상적인 주제를 DB에 맞게 설계하는 것.

어플리케이션 기획 안에 모델링이 포함되어 있음.
테이블을 짜야지 모델링 할 수 있음.
(프론트, 서비스, 모델링 3가지가 기획팀에서 어플리케이션 기획)


1.요구사항 분석
 : 3가지를 관리하고자 한다. >> 문서화

2.개념적 설계

회원, 주문, 상품 >> 테이블 3개가 필요하네

회원	주문	상품
아이디(PK)	주문번호(PK)	상품번호(PK)
비밀번호	주문날짜	상품명
이름	아이디	가격
주소	상품번호	재고량
이메일
생일

3.논리적 설계(논리 모델링)

회원	주문	상품
아이디(PK)	주문번호(PK)	상품번호(PK)
비밀번호	주문날짜	상품명
이름	아이디(FK)	가격
주소	상품번호(FK)	재고량
이메일(UK)
생일

 

4.물리적 설계 //영어이름, 자료형 등등 구체적으로, 누구나 테이블을 만들 수 있게끔

TBL_USER

USER_ID : VARCHAR2(1000) : PRIMARY KEY

USER_PW : VARCHAR2(1000)

USER_NAME : VARCHAR2(1000)

USER_ADDRESS : VARCHAR2(1000)

USER_EMAIL : VARCHAR2(1000) : UNIQUE

USER_BIRTH : DATE

5.구현

==============================================================
EX
1.요구사항 분석
꽃과 화분 두 종류를 판매하고,
꽃을 구매할 때 화분도 같이 구매합니다.
꽃은 이름과 색상, 가격, 유통기한, 재고량이 있고,
화분은 제품번호, 색상, 모양, 재고량, 가격이 있습니다.
화분은 모든 꽃을 담을 수 없고 맞는 꽃들을 담아야 합니다.

KF는 중복됨.

2.개념적 설계

꽃	화분
이름	제품번호
색상	색상
가격	모양
유통기한	재고량
재고량	가격
	꽃 이름
	꽃 색상

3.논리적 설계

꽃	화분
이름PK	제품번호PK
색상PK
가격	색상
유통기한	모양
재고량	재고량
	가격
	꽃 이름FK
	꽃 색상FK

하나의 화분(번호 PK)에 여러 종류의 FK꽃이 들어갈 수 있음.


4.물리적 설계

TBL_FLOWER

FLOWER_NAME : VARCHAR2(1000) : PRIMARY KEY

FLOWER_COLOR : VARCHAR2(1000) : PRIMARY KEY

FLOWER_PRICE : NUMBER

FLOWER_DATE_OF_EXPIRY : DATE

FLOWER_STOCK : NUMBER

TBL_POT

POT_NUMBER : NUMBER : PRIMARY KEY

POT_COLOR : VARCHAR2(1000)

POT_SHAPE : VARCHAR2(1000)

POT_STOCK : NUMBER

POT_PRICE : NUMBER

FLOWER_NAME : VARCHAR2(1000) : FOREIGN KEY

FLOWER_COLOR : VARCHAR2(1000) : FOREIGN KEY

5.구현(day02.sql)

SET ACTIVE
스크립트 만들기 CTRL+]

PK(FLOWER) -> FK(POT)
부모테이블 -> 자식테이블

PRIMARY KEY 명령어는 한번만 쓸 수 있음.
기본 키는 하나만 있어야 함, 기본 키를 할당하는 컬럼의 개수는 여러개 가능 

PK는 컬럼 옆에다 쓰는 게 편하고,
FK는 밑에다 이어서 쓰는게 훨씬 편함.
==============================================================
[실습]

1.요구사항 분석
동물병원에서 반려동물 정보와 보호자의 정보가 필요하다
반려동물의 정보는 핀번호, 종, 이름, 나이, 병명이 필요하고
보호자의 정보는 이름, 나이, 주소, 핸드폰 번호가 필요하다.
한 명의 보호자는 여러 반려동물을 등록할 수 있다.

2.개념적 설계

보호자	반려동물
이름	핀번호
나이	종
주소	이름
핸드폰번호	나이
	병명
	핸드폰 번호

3.논리적 설계

보호자	반려동물
핸드폰번호PK	핀번호PK
이름	종
나이	이름
주소	나이
	병명
	핸드폰 번호FK

보호자 한 명이 여러 동물을 키울 수 있음.
하나의 동물 PK에 핸드폰 번호FK 여러개 올 수 있음.
KF는 자식

4.물리적 설계

TBL_OWNER

OWNER_PHONENUMBER : VARCHAR2(1000) : PRIMARY KEY

OWNER_NAME : VARCHAR2(1000)

OWNER_AGE : NUMBER

OWNER_ADDRESS : VARCHAR2(1000)

TBL_PET

PET_PIN_NUMBER : NUMBER : PRIMARY KEY

PET_SPECIES : VARCHAR2(1000)

PET_NAME : VARCHAR2(1000)

PET_AGE : NUMBER

PET_TROUBLE : VARCHAR2(1000)

OWNER_PHONE_NUMBER : VARCHAR2(1000) : FOREIGN KEY (이름은 달라도 되지만 자료형,용량은 같아야 함.)

FK 참조하는 컬럼 이름만 정확하게 써주면 됨.
테이블의 컬럼명과 참조컬럼; 가독성을 위해서 보통 통일하나, 따로 써야 하는 경우에는 따로 써도 됨.

5.구현

 

--------------------------------------------------------------------------------------------

▶DML(Data Manipulation Language) 데이터 조작어 >> CRUD
1.SELECT : 조회
  SELECT [컬럼명,...]
  FROM [테이블명]
  WHERE [조건식];

2.INSERT : 추가
1) 컬럼을 생략할 수 있다.(DEFAULT 제약조건이 있다면, 알아서 그 값이 들어감)
  INSERT INTO [테이블명] ([컬럼명,...])
  VALUES([값,...]);
2) 모든 값을 전부 작성해야 되면, 컬럼명은 직접 작성하지 않는다.
  INSERT INTO [테이블명]
  VALUES([값,...]);

3.UPDATE : 수정
  UPDATE [테이블명]
  SET [컬럼명 = 값, ...]
  WHERE [조건식]; //WHERE절은 생략 가능 > 그럼 전체 내용이 수정됨.

4.DELETE : 삭제
  DELETE FROM [테이블명]
  WHERE [조건식]; //WHERE절은 생략 가능 > 그럼 전체 내용이 삭제됨.


----------------------------------------------------------
조건식 : 참 또는 거짓 둘 중 하나
조건식에서 사용되는 연산자(WHERE절에서만 사용 가능)

>, <	초과 미만
>=, <=	이상 이하
=	같다
<>, !=, ^=	같지 않다
AND	둘 다 참이면 참
OR	둘 중 하나라도 참이면 참

* : 전체라는 의미, 전체 컬럼
문자열 값은 '' : 작은 따옴표

SET절 = : 대입연산자
WHERE절 = : 같다

TO_DATE 포맷 형식 : 'YYYY-MM-DD HH24:MI:SS'
2019/12/04 이렇게 써도 화면에서는 2019-12-04 이렇게 나옴
20191204 구분점 없이 써도 들어감.

시작점, 끝점, 증가량 정할 수 있는 것 : SEQUENCE
CREATE SEQUENCE SEQ_[테이블명];
디폴트로 1부터 1씩 증가
NEXTVAL : 현재 시퀀스부터 다음 시퀀스 까지 계속 증가해줌.
시퀀스는 중복없음에 포커스 맞추기.

자식보다 부모의 값이 무조건 먼저 있어야 한다.
부모(PK)에 없으면 자식(FK)에 추가할  오류가 남.
자식에서 쓰고 있으면(참조 중) 부모에서 수정,변경,삭제되지 않음.(오류: child record found)

시퀀스는 순서를 의미함.
순서가 필요할 때 시퀀스를 생성함.

SYSDATE + 3 : 현재 시스템 시간 + 3

부모가 있어야 자식이 있다 : 추가
수정, 삭제는 자식에서 없애주고 부모를 수정, 삭제 해야 함. 참조가 되어 있어서.

FK 제약조건(CONSTRAINT)을 줄 때 부모가 삭제되면 자식에서도 삭제되게 걸어주기.
>> ON DELETE CASCADE
보안때문에 회사에서는 잘 사용하지 않는다고 함.

부모 테이블의 값이 있어야 자식 테이블에서 참조할 수 있다.
자식에서 참조 중인 부모의 데이터가 있다면, 부모에서 수정 및 삭제가 불가하다.
만약 자식 테이블 생성 시 FK 제약조건에 ON DELETE CASCADE 옵션을 작성해주면
부모 데이터 삭제 시, 참조중인 자식 데이터들이 자동으로 같이 삭제된다.

자식 테이블의 FK수정
 **부모에 없는 값으로 수정할 수 없다.
    부모 테이블에 수정할 새로운 정보를 추가한 뒤 해당 PK로 수정 가능!