속성(Attribute) -> 엔티티가 가지고 있는 특징. * 식별자: 엔티티의 데이터들을 서로 구분해 줄 수 있는 속성. 여러 속성을 결합하여 사용할 수도 있음 * 주 식별자(unique, 중복X, null X), 보조 식별자, 외래 식별자
관계(Relationship) -> 1:1, 1:M, M:M, 식별/비식별 관계 * M:M(다대다 관계): :물리적 구현이 어려우므로 중간 테이블을 이용하여 1:M, N:1의 방식으로 작성함 수강과목 테이블의 학번, 학과번호 속성이 주 식별자, 외래 식별자 두 역할 모두 수행함(노랑+핑크=민트)
* 식별 관계: 부모 엔티티의 주 식별자를 자식 엔티티의 주 식별자, 외래 식별자로 사용하는 관계 비 식별 관계: 부모 엔티티의 주 식별자를 자식 엔티티의 외래 식별자로만 사용하는 관계
> 논리적 모델링
개념적 모델링 과정에서 추상화된 데이터를 구체화하여 개체, 속성을 테이블화하고 상세화 하는 과정
이상(Anomaly): 정규화를 진행하지 않은 엔티티를 대상으로 삽입, 갱신, 삭제 시 발생할 수 있는 예기치 못한 현상
삽입 이상
갱신 이상
삭제 이상
정규화
제 1 정규화: 하나의 속성이 하나의 값을 갖도록 함
제 2 정규화: 주 식별자 전체에 종속적이지 않은 속성 분리(주 식별자가 복합 식별자인 경우)
제 3 정규화: 주 식별자에 종속적이지 않고 다른 속성에 종속적인 속성 분리
> 물리적 모델링
논리적 모델링 과정에서 표현된 데이터를 실제 데이터베이스에 맞도록 구현하는 과정
논리적 DB 설계
물리적 DB 설계
엔티티(Entity)
테이블(Table)
속성(Attribute)
컬럼(Column)
주식별자(Primary Identifier)
기본키(Primary Key)
외래식별자(Foreign Identifier)
외래키(Foreign Key)
뷰(View)
인덱스(Index)
> erdcloud.com 실습
> 테이블(Table)
테이블 생성
CREATE TABLE
DEFAULT
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;
제약 조건: 무결성 충족을 위하여 데이터 조건 제한
PRIMARY KEY(기본 키) - 식별자 -> UNIQUE, NOT NULL 만족 - 테이블에 하나만 존재하며, 여러 칼럼을 결합하여 지정 가능
UNIQUE - 중복 X, NULL 허용
FOREIGN KEY(외래 키) - 두 테이블 간의 관계를 설정하는 키: 하나의 테이블이 다른 테이블에 의존하게 됨 - 기준 테이블에 이미 데이터가 존재해야 함
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');
