(2주차 10일) DB 모델링, erdcloud, 테이블, 뷰

DB Modeling

> 데이터 모델링

• 데이터를 중심으로 모델링을 진행
• 현실에서 쓰이는 개체를 데이터로 옮기는 작업
• 분류
  
  1. 개념적 모델링
  2. 논리적 모델링
  3. 물리적 모델링

> 개념적 모델링

• 요구분석 단계에서 정의된 핵심 개체와 그들 간의 관계를 바탕으로 ERD 생성
• ERD: 개체-관계 모델로, 테이블 간의 관계를 도형으로 설명해주는 다이어그램 
  1. 엔티티(Entity) -> 실제 관리해야 할 유형, 무형의 구체적인 대상
  2. 속성(Attribute) -> 엔티티가 가지고 있는 특징.
     * 식별자: 엔티티의 데이터들을 서로 구분해 줄 수 있는 속성. 여러 속성을 결합하여 사용할 수도 있음
     * 주 식별자(unique, 중복X, null X), 보조 식별자, 외래 식별자
  3. 관계(Relationship) -> 1:1, 1:M, M:M, 식별/비식별 관계
     * M:M(다대다 관계): :물리적 구현이 어려우므로 중간 테이블을 이용하여 1:M, N:1의 방식으로 작성함
     

     

     수강과목 테이블의 학번, 학과번호 속성이 주 식별자, 외래 식별자 두 역할 모두 수행함(노랑+핑크=민트)

     * 식별 관계: 부모 엔티티의 주 식별자를 자식 엔티티의 주 식별자, 외래 식별자로 사용하는 관계
       비 식별 관계: 부모 엔티티의 주 식별자를 자식 엔티티의 외래 식별자로만 사용하는 관계

> 논리적 모델링

• 개념적 모델링 과정에서 추상화된 데이터를 구체화하여 개체, 속성을 테이블화하고 상세화 하는 과정
• 이상(Anomaly): 정규화를 진행하지 않은 엔티티를 대상으로 삽입, 갱신, 삭제 시 발생할 수 있는 예기치 못한 현상
  
  1. 삽입 이상
  2. 갱신 이상
  3. 삭제 이상
• 정규화
  
  1. 제 1 정규화: 하나의 속성이 하나의 값을 갖도록 함
  2. 제 2 정규화: 주 식별자 전체에 종속적이지 않은 속성 분리(주 식별자가 복합 식별자인 경우)
  3. 제 3 정규화: 주 식별자에 종속적이지 않고 다른 속성에 종속적인 속성 분리

> 물리적 모델링

• 논리적 모델링 과정에서 표현된 데이터를 실제 데이터베이스에 맞도록 구현하는 과정

논리적 DB 설계	물리적 DB 설계
엔티티(Entity)	테이블(Table)
속성(Attribute)	컬럼(Column)
주식별자(Primary Identifier)	기본키(Primary Key)
외래식별자(Foreign Identifier)	외래키(Foreign Key)
	뷰(View)
	인덱스(Index)

 

> erdcloud.com 실습

 

> 테이블(Table)

• 테이블 생성
  
  1. CREATE TABLE
  2. DEFAULT
  3. NULL / NOT NULL

/*
* 테이블 생성 실습
*/
-- 회원에 대한 데이터를 담을 수 있는 tb_member 테이블 생성
DROP TABLE tb_member;

CREATE TABLE tb_member (
	mem_no INT NOT NULL,
	mem_id VARCHAR(20) NOT NULL,
	mem_pass VARCHAR(20) NOT NULL,
	mem_name VARCHAR(15) NOT NULL,
	enroll_date DATE DEFAULT CURDATE()
);

-- 샘플 데이터 추가
INSERT INTO tb_member VALUES (1, 'user1', '1234', '홍길동', '2022-12-09');
INSERT INTO tb_member VALUES (2, 'user2', '1234', '이몽룡', CURDATE());
INSERT INTO tb_member VALUES (3, 'user3', '1234', '성춘향', DEFAULT);
INSERT INTO tb_member VALUES (4, 'user4', '1234', '임꺽정', NULL);

/* NOT NULL 설정에 의해 NULL값 데이터 삽입, 수정 불가능
INSERT INTO tb_member(mem_no, mem_id) VALUES (4, 'user4');
INSERT INTO tb_member VALUES (NULL, NULL, NULL, NULL, NULL);

UPDATE tb_member SET mem_id = NULL
WHERE mem_name = '홍길동'; */

SELECT * FROM tb_member;

• 제약 조건: 무결성 충족을 위하여 데이터 조건 제한
  
  1. PRIMARY KEY(기본 키)
     - 식별자 -> UNIQUE, NOT NULL 만족
     - 테이블에 하나만 존재하며, 여러 칼럼을 결합하여 지정 가능
  2. UNIQUE
     - 중복 X, NULL 허용
  3. FOREIGN KEY(외래 키)
     - 두 테이블 간의 관계를 설정하는 키: 하나의 테이블이 다른 테이블에 의존하게 됨
     - 기준 테이블에 이미 데이터가 존재해야 함
  4. CHECK: 열 입력 데이터 점검 기능 -> 제약 조건에 위배되는 값은 입력되지 않음

/*
* PRIMARY KEY, UNIQUE 제약 조건
* 기본키가 지정되어 있지 않은 경우, mem_no에 데이터 중복 입력 가능
* PRIMARY KEY로 기본키를 지정하는 경우 NOT NULL 조건이 없어도 된다.(PRIMARY가 더 상위 조건)
* AUTO_INCREMENT: 시스템에서 자동으로 기본 키를 생성 -> 숫자(INT) 형식 지정 필수
*/

CREATE TABLE tb_member (
	mem_no INT AUTO_INCREMENT PRIMARY KEY,
	mem_id VARCHAR(20) NOT NULL UNIQUE,
	mem_pass VARCHAR(20) NOT NULL,
	mem_name VARCHAR(15) NOT NULL,
	enroll_date DATE DEFAULT CURDATE()
);

INSERT INTO tb_member VALUES (1, 'user1', '1234', '홍길동', '2022-12-09');
INSERT INTO tb_member VALUES (2, 'user2', '1234', '이몽룡', CURDATE());
/* 기본키, UNIQUE 중복, NULL으로 에러 발생
INSERT INTO tb_member VALUES (1, 'user1', '4567', '김철수', DEFAULT);
INSERT INTO tb_member VALUES (NULL, 'user1', '4567', '김철수', DEFAULT);*/
INSERT INTO tb_member VALUES (3, 'user3', '4567', '김철수', DEFAULT);

-- AUTO_INCREMENT 이후 INSERT문
INSERT INTO tb_member(mem_id, mem_pass, mem_name) VALUES ('user1', '1234', '홍길동');
INSERT INTO tb_member(mem_id, mem_pass, mem_name) VALUES ('user2', '5678', '이몽룡');

SELECT * FROM tb_member;

-- 열 정의 후에 제약 조건을 별도로 지정하는 밥법
CREATE TABLE tb_member (
	mem_no INT AUTO_INCREMENT,
	mem_id VARCHAR(20) NOT NULL,
	mem_pass VARCHAR(20) NOT NULL,
	mem_name VARCHAR(15) NOT NULL,
	enroll_date DATE DEFAULT CURDATE(),
	PRIMARY KEY (mem_no),
	/* PRIMARY KEY, UNIQUE 제약 조건의 경우 여러 열을 묶어 하나의 기본키로 생성할 수 있음
	CONSTRAINT PRIMARY KEY (mem_no, mem_id)*/
	CONSTRAINT uq_tb_member_mem_id UNIQUE (mem_id)
);

/*
* FOREIGN KEY 제약 조건
* 두 테이블 사이의 관계를 설정
*/
-- 기준 테이블 생성
CREATE TABLE tb_member_grade (
	grade_code VARCHAR(10) PRIMARY KEY,
	grade_name VARCHAR(10) NOT NULL
);

INSERT INTO tb_member_grade VALUES ('vip', 'vip회원');
INSERT INTO tb_member_grade VALUES ('gold', '골드 회원');
INSERT INTO tb_member_grade VALUES ('silver', '실버 회원');

CREATE TABLE tb_member (
	mem_no INT AUTO_INCREMENT PRIMARY KEY,
	mem_id VARCHAR(20) NOT NULL UNIQUE,
	mem_pass VARCHAR(20) NOT NULL,
	mem_name VARCHAR(15) NOT NULL,
	grade_code VARCHAR(10) REFERENCES tb_member_grade(grade_code),
	enroll_date DATE DEFAULT CURDATE()
);

INSERT INTO tb_member(mem_id, mem_pass, mem_name, grade_code)
VALUES ('user1', '1234', '홍길동', 'vip');

/* 외래키 제약조건에 위배되는 경우: 참조 테이블에 존재하지 않는 데이터
INSERT INTO tb_member(mem_id, mem_pass, mem_name, grade_code)
VALUES ('user2', '5678', '이몽룡', 'bronze'); */
INSERT INTO tb_member(mem_id, mem_pass, mem_name, grade_code)
VALUES ('user2', '5678', '이몽룡', NULL);

SELECT * FROM tb_member_grade;
SELECT * FROM tb_member;

-- 두 테이블을 조인하여 데이터 조회
SELECT mem_no, mem_id, b.grade_name FROM tb_member a
LEFT JOIN tb_member_grade b ON a.grade_code = b.grade_code;

/* (외래키) DELETE, UPDATE 옵션
* 외부 테이블에서 참조하고 있는 경우, 해당 행을 수정 혹은 삭제할 수 없음(RESTRICT)
* 외부 테이블에서 참조하는 경우, 해당 데이터 수정 혹은 삭제 시 NULL로 세팅됨(SET NULL)
* 외부 테이블에서 참조하는 경우, 해당 데이터 수정 혹은 삭제 시 참조행 전체가 삭제됨(CASCADE)
*/
-- tb_member_grade 테이블에서 grade_code가 vip인 데이터 삭제
DELETE FROM tb_member_grade
WHERE grade_code = 'gold';

-- tb_member_grade 테이블에서 grade_code가 vip인 데이터 수정
UPDATE tb_member_grade
SET grade_code = 'vvip'
WHERE grade_code = 'vip';

-- UPDATE/DELETE 옵션 지정
DROP TABLE tb_member;
CREATE TABLE tb_member (
	mem_no INT AUTO_INCREMENT PRIMARY KEY,
	mem_id VARCHAR(20) NOT NULL UNIQUE,
	mem_pass VARCHAR(20) NOT NULL,
	mem_name VARCHAR(15) NOT NULL,
	grade_code VARCHAR(10), /* REFERENCES tb_member_grade(grade_code),
	ON DELETE CASCADE ON UPDATE CASCADE,*/
	enroll_date DATE DEFAULT CURDATE(),
	-- 열 이름 지정 및 아래에서 제약조건 지정하는 방법
	CONSTRAINT fk_tb_member_grade_code FOREIGN KEY (grade_code)
		REFERENCES tb_member_grade(grade_code)
		ON DELETE SET NULL ON UPDATE SET NULL
);

/*
* CHECK 제약 조건
* 제약 조건에 위배되는 값은 열에 입력되지 않음
*/
DROP TABLE tb_member;
CREATE TABLE tb_member (
	mem_no INT AUTO_INCREMENT PRIMARY KEY,
	mem_id VARCHAR(20) NOT NULL UNIQUE,
	mem_pass VARCHAR(20) NOT NULL,
	mem_name VARCHAR(15) NOT NULL,
	gender CHAR(2) CHECK(gender IN ('남자', '여자')),
	age TINYINT,
	grade_code VARCHAR(10) REFERENCES tb_member_grade,
	enroll_date DATE DEFAULT CURDATE(),
	
	-- 아래에서 제약조건 지정하는 방법
	CONSTRAINT ck_tb_member_age CHECK(age BETWEEN 0 AND 100)
);

/* 성별, 나이에 유효한 값이 아니므로 데이터가 삽입되지 않음
INSERT INTO tb_member(mem_id, mem_pass, mem_name, gender, age, grade_code)
VALUES ('user1', '1234', '홍길동', '강자', 36, 'vip');

INSERT INTO tb_member(mem_id, mem_pass, mem_name, gender, age, grade_code)
VALUES ('user2', '1234', '성춘향', '여자', -36, 'gold');
*/
INSERT INTO tb_member(mem_id, mem_pass, mem_name, gender, age, grade_code)
VALUES ('user3', '1234', '이몽룡', '남자', 100, 'vip');