조합(복합)키, NULL값 제어, 모델링

---

▶ 조합(복합)키

- 테이블에서 컬럼 2개를 조합해서 기본키(pk)를 지정하는 방법

- 조합된 2개의 컬럼에서 하나의 컬럼은 중복이 가능하지만, 2개의  컬럼은 중복이 불가능하다.

CREATE TABLE TBL_FLOWER(
FLOWER_NAME VARCHAR2(1000),
FLOWER_COLOR VARCHAR2(1000),
FLOWER_PRICE NUMBER,
CONSTRAINT PK_FLOWER PRIMARY key(FLOWER_NAME, FLOWER_COLOR));

 

기본 TABLE로 해보고

INSERT INTO TBL_FLOWER VALUES('장미','빨강',1000);
INSERT INTO TBL_FLOWER VALUES('장미','노랑',2000);
INSERT INTO TBL_FLOWER VALUES('장미','빨강',3000);

이때 SQL Error [900] [42000]: ORA-00900: invalid SQL statement가 발생하는데 이유가 무엇이냐면

장미, 빨강이 1줄이랑 3줄이랑 같기때문에 ERROR가 발생하게 됩니다.

공공기관에서 주민번호와 사람이랑 매칭시킬때 사용하게됩니다.

---

▶NULL값 제어하는 방법

** NULL값을 다른 값으로 치환하는 방법

-NVL(컬럼명, '값') : NULL값 대신 다른 값으로 변경(치환)후 조회

-NVL2(컬럼명, 'NULL이 아닐 때의 값', 'NULL일때의 값')

 

CREATE TABLE TBL_BOOK(
BOOK_NUMBER NUMBER,
BOOK_NAME VARCHAR2(1000),
BOOK_CATEGORY VARCHAR2(1000),
BOOK_NULL VARCHAR2(1000));

INSERT INTO TBL_BOOK VALUES(1,'코난','추리',NULL);
INSERT INTO TBL_BOOK VALUES(2,'중증외상센터','의학', NULL);
INSERT INTO TBL_BOOK VALUES(2,'코난','추리','NULL값 아님!');


SELECT * FROM TBL_BOOK;

 

 

SELECT BOOK_NUMBER, BOOK_NAME, BOOK_CATEGORY, NVL(BOOK_NULL,0)
FROM TBL_BOOK;

 

여기서 0 대신 한글써도되고 다만 번거로운 점은 컬럼을 모두 지정해주어야하는점이 살짝 번거로울뿐이다!

 

---

문제. 값이 없는 부분은 0으로 표기 / 값이 있는 부분은 1로 표기해서 조회하기

SELECT BOOK_NUMBER, BOOK_NAME, BOOK_CATEGORY, NVL2(BOOK_NULL,1,0)
FROM TBL_BOOK;

 

---

▶모델링 (중요)

00. 개요

- 현실 세계의 데이터를 컴퓨터가 이해할 수 있는 형태로 표현하는ㄱ ㅘ정

- 효율적인 데이터베이스 구축을 위해서는 정확하고 효과적인 DB모델

- ex) 건축물을 만들기 전에 설계도를 그리듯이, 데이터베이스를 구축 전 데이터구조를 사전 설계!!

 

01. 요구사항 분석

- 비즈니스 분석 단계!!!

ex) 회원으로부터 수집하는 정보는?

판매물품의 종류는?

해외배송 유/무는? 등...

 

02. 개념적 모델링

- 현실세계의 데이터를 추상적인 개념으로 표현

- 분석된 사업적인 비즈니스를 기준으로 대략적으로 표현

- ex) 연락처, 이름, 주소, 성별 등

- 가나, abc, 고디바 등...

- 해외배송 불가능!!

 

03. 논리적 모델링

- 개념적 모델링을 기준으로 DBMS에 맞게 구성!!!

- 제약조건 기능을 추가하면 됨

ex) 이름은 한글 기준으로 최대 6글자 이내 적용 / 영어는 10글자 이내 적용

 

04. 물리적 모델링

- 논리적 모델링을 기준으로 테이블화 구성 및 생성

- ex) 보통은 바로 만들지 않음(실수가있을 수 있으니) 엑셀이나 notepad에 대략적으로 테이블화 문법을 작성합니다.

이유 : 재검토 하기 위해서

- 위 과정이 끝나고 문제가 없다면 테이블화 생성하면 됩니다.

 

---

 

-- **모델링 예습문제 (핸드폰 vs 케이스) 모델링

[핸드폰을 구매하면 케이스를 서비스로 준다.]

 

- 핸드폰과 핸드폰 케이스를 판매한다.

- 핸드폰을 구매하면 핸드폰 케이스를 서비스로 제공함

- 핸드폰은 제품번호, 색상, 사이즈 , 가격, 제조일, 할인률이 필요하다

- 케이스는 제품번호, 색상, 가격이 필요하다.

- 핸드폰은 특정 케이스만 같이 구입할 수 있다.

 

 

개념(추상)적 설계

엑셀 테이블

 

샘플로 5개정도 데이터를 넣어보는것이 팁이다!

그러면 문제가 있는지 없는지 좀 더 뚜렷하게 보인다.

 

 

논리적 설계 (제약조건)

기본키, 후보키 등같은건 어떻게할건지 중복값이 나올지 안나올지 등에 대한것을 논리적으로 결정하고 회의를 합니다.

fk는 받는쪽을 기준으로 선택하는것이 중요하다!

 

물리적 설계(테이블화 구성)

 

이상태에서 그대로 만들기만하면 됩니다.

 

최종 확인

 

이런식으로 노트패드에 한번더 확인해봅니다.

TBL_PHONE
PHONE_NUMBER : VARCHAR2(1000) : PRIMARY KEY
==================================
PHONE_COLOR : VARCHAR2(1000)
PHONE_SIZE : VARCHAR2(1000)
PHONE_PRICE : NUMBER
PHONE_PRODUCTION_DATE : DATE
PHONE_SALE : NUMBER

TBL_CASE
CASE_NUMBER : NUMBER : PRIMARY KEY
==================================
CASE_COLOR : VARCHAR2(1000)
CASE_PRICE : NUMBER
PHONE_NUMBER : NUMBER

 

 

- 핸드폰 테이블
CREATE TABLE TBL_PHONE(
PHONE_NUMBER NUMBER CONSTRAINT PK_PHONE PRIMARY KEY,
PHONE_COLOR VARCHAR2(1000),
PHONE_SIZE VARCHAR2(1000),
PHONE_PRICE NUMBER,
PHONE_PRODUCTION_DATE DATE,
PHONE_SALE NUMBER);
- 케이스 테이블
CREATE TABLE TBL_CASE(
CASE_NUMBER NUMBER CONSTRAINT PK_CASE PRIMARY KEY,
CASE_COLOR VARCHAR2(1000),
CASE_PRICE NUMBER,
PHONE_NUMBER NUMBER,
CONSTRAINT FK_CASE FOREIGN KEY(PHONE_NUMBER)
REFERENCES TBL_PHONE(PHONE_NUMBER));

 

 

---

 

▶모델링실습 1

* 도서관 책 대여 서비스

- **요구사항 분석**
    - 도서관에서 책을 대여하는 모델링을 실습.
    - 테이블명은 TBL_MEMBER2, TBL_BOOK.
    - 회원의 정보는 회원번호, 이름, 나이, 핸드폰번호, 주소
    - 책의 정보는 도서번호, 책이름, 장르 항목이 존재
    - 장르는 인문학, 추리, IT, 로맨스, 만화 종류만을 선택 가능
    - 한 명의 회원은 여러권의 책을 대여 가능(부모-자식 테이블 연결)

 

엑셀화

 

- 회원정보 테이블

CREATE TABLE TBL_MEMBER2(
MEMBER_NUMBER CONSTRAINT PK_MEMBER PRIMARY KEY,
========================================
MEMBER_NAME VARCHAR2(100) : NOT NULL,
MEMBER_AGE NUMBER,
MEMBER_PHONE VARCHAR2(100) : NOT NULL, UNIQUE
MEMBER_ADD VARCHAR2(1000) : NOT NULL




-대여 책 테이블

CREATE TABLE TBL_BOOK
BOOK_ID NUMBER CONSTRAINT PK_BOOK PRIMARY KEY,
================================================
BOOK_NAME VARCHAR2(1000): NOT NULL
BOOK_CATEGORY VARCHAR2(1000): CHECK(인문학 추리 IT 로맨스 만화)
MEMBER2_NUMBER NUMBER : FOREIGN KEY

내가 만든 테이블의 노트화 버전이다.

점선은 테이블끼리 관계도 설정한것! 가독성 좋게보이려고한것뿐임

---

강사님과 비교

엑셀화-T

노트화-T

 

CREATE TABLE TBL_MEMBER2 (
MEMBER2_NUMBER NUMBER,
MEMBER2_NAME VARCHAR2(1000) NOT NULL,
MEMBER2_AGE NUMBER,
MEMBER2_PHONE_NUMBER VARCHAR2(1000) NOT NULL,
MEMBER2_ADDRESS VARCHAR2(1000) NOT NULL,
CONSTRAINT PK_MEMBER2 PRIMARY KEY(MEMBER2_NUMBER),
CONSTRAINT UK_MEMBER2 UNIQUE(MEMBER2_PHONE_NUMBER));


CREATE TABLE TBL_BOOK (
BOOK_NUMBER NUMBER,
BOOK_NAME VARCHAR2(1000) NOT NULL,
BOOK_CATEGORY VARCHAR2(1000),
MEMBER2_NUMBER NUMBER,
CONSTRAINT PK_BOOK PRIMARY KEY (BOOK_NUMBER),
CONSTRAINT CHECK_BOOK_CATEGORY
CHECK (BOOK_CATEGORY IN('인문학', '추리', 'IT', '로맨스', '만화')),
CONSTRAINT FK_BOOK FOREIGN KEY(MEMBER2_NUMBER)
REFERENCES TBL_MEMBER2(MEMBER2_NUMBER));

 

여기까지가 기본 구현이라고 하셨다.

---

 

▶모델링실습 2

*피자배달서비스

-피자 배달 서비스를 위한  DB모델링과정

-한기업에서 피자배달 서비스를 기획하고있다.

-서비스 배달 절차는 아래와 같다.

A. 고객이 메뉴를 주문한다.

B. 배달원이 주문정보를 확인해서 고객에게 배달한다.

 

===========================================

최소 필요DB

- 고객정보 (이름, 주소, 연락처)

- 주문정보 (피자종류, 수량, 가격, 배달주소)

- 메뉴정보 (피자 종류, 가격, 토핑)

- 배달원정보 (이름, 연락처)

===========================================

최소라는점이고 이 부분 이상으로 필요한 부분이나 필요없는부분은 가감하면 될것같다.

아직 나는 100% 이해가 잘 된다! 라고생각하지않기때문에 

일단 강사님의 프로세스를  따라가보았다.

엑셀 세팅

FK는 고객정보랑 주문정보의 회원정보, 피자번호를 연결하면됩니다.

 

CREATE TABLE CUSTOMER (
CUST_ID NUMBER PRIMARY KEY, -- 고객 ID (기본 키)
CUST_NAME VARCHAR2(50) NOT NULL, -- 고객 이름
CUST_ADDR VARCHAR2(200) NOT NULL, -- 고객 주소
CUST_PHONE VARCHAR2(20) NOT NULL -- 고객 연락처
);



CREATE TABLE PIZZA (
PIZZA_ID NUMBER PRIMARY KEY, -- 피자 ID (기본 키)
PIZZA_NAME VARCHAR2(50) NOT NULL, -- 피자 이름
PIZZA_PRICE NUMBER NOT NULL, -- 피자 가격
PIZZA_TOPPING VARCHAR2(200) -- 피자 토핑
);



CREATE TABLE DELIVERY (
DELIVERY_ID NUMBER PRIMARY KEY, -- 배달원 ID (기본 키)
DELIVERY_NAME VARCHAR2(50) NOT NULL, -- 배달원 이름
DELIVERY_PHONE VARCHAR2(20) NOT NULL -- 배달원 연락처
);


CREATE TABLE ORDERS (
ORDER_ID NUMBER PRIMARY KEY, -- 주문 ID (기본 키)
CUST_ID NUMBER NOT NULL, -- 고객 ID (외래 키)
PIZZA_ID NUMBER NOT NULL, -- 피자 ID (외래 키)
ORDER_QTY NUMBER NOT NULL, -- 주문 수량
ORDER_PRICE NUMBER NOT NULL, -- 주문 가격
DELIVERY_ADDR VARCHAR2(200) NOT NULL, -- 배달 주소
CONSTRAINT FK_ORDERS_CUST FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER(CUST_ID),
CONSTRAINT FK_ORDERS_PIZZA FOREIGN KEY (PIZZA_ID) REFERENCES PIZZA(PIZZA_ID)
);

---

1.TXT

0.10MB

4.TXT

0.00MB

2.txt

0.00MB

3.txt

0.05MB

 

 

 

 

순차적으로 넣고 SKIP ALL 누르면 순차적으로 실행된다고 합니다.

그러면 TABLE이 모두 생성됩니다 총 4개!  (쓰임)