제2정규형 (2NF)

PostgreSQL 데이터베이스 만들기

Darryl Reeves

Industry Assistant Professor, New York University

예시: 학교 교재

CREATE TABLE textbook (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    publisher_name VARCHAR(100) NOT NULL,
    publisher_site VARCHAR(50),
    quantity SMALLINT NOT NULL DEFAULT 0
);

예시: 학교 교재

id	title	publisher_name	publisher_site	quantity
23	Introductory Algebra: 1st Edition	ABC Publishing	www.abc.com	32
74	Calculus Foundations	ABC Publishing	www.abc.com	27
112	Statistical Concepts	Martin House	www.mh.com	22

예시: URL 업데이트로 인한 불일치

id	title	publisher_name	publisher_site	quantity
23	Introductory Algebra: 1st Edition	ABC Publishing	www.abc.com	32
74	Calculus Foundations	ABC Publishing	www.abc.com	27
112	Statistical Concepts	Martin House	www.mh.com	22

예시: URL 업데이트로 인한 불일치

id	title	publisher_name	publisher_site	quantity
23	Introductory Algebra: 1st Edition	ABC Publishing	www.newabc.com	32
74	Calculus Foundations	ABC Publishing	www.abc.com	27
112	Statistical Concepts	Martin House	www.mh.com	22

예시: 교재 없이 출판사 추가

id	title	publisher_name	publisher_site	quantity
23	Introductory Algebra: 1st Edition	ABC Publishing	www.abc.com	32
74	Calculus Foundations	ABC Publishing	www.abc.com	27
112	Statistical Concepts	Martin House	www.mh.com	22

예시: 교재 없이 출판사 추가

id	title	publisher_name	publisher_site	quantity
23	Introductory Algebra: 1st Edition	ABC Publishing	www.abc.com	32
74	Calculus Foundations	ABC Publishing	www.abc.com	27
112	Statistical Concepts	Martin House	www.mh.com	22
??	??	New Horizons	www.nhorizon.com	??

예시: 교재 삭제

id	title	publisher_name	publisher_site	quantity
23	Introductory Algebra: 1st Edition	ABC Publishing	www.abc.com	32
74	Calculus Foundations	ABC Publishing	www.abc.com	27
112	Statistical Concepts	Martin House	www.mh.com	22

예시: 교재 삭제

id	title	publisher_name	publisher_site	quantity
23	Introductory Algebra: 1st Edition	ABC Publishing	www.abc.com	32
74	Calculus Foundations	ABC Publishing	www.abc.com	27

출판사는 별도 테이블 필요
삽입/삭제 시 데이터 이상 발생

제2정규형(2NF) 충족하기

1NF 충족
모든 비키 열은 테이블 PRIMARY KEY에 종속

예시: 2NF의 교재와 출판사

CREATE TABLE textbook (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    publisher_name VARCHAR(100) NOT NULL,
    publisher_site VARCHAR(50),
    quantity SMALLINT NOT NULL DEFAULT 0
);

예시: 2NF의 교재와 출판사

CREATE TABLE textbook (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    quantity SMALLINT NOT NULL DEFAULT 0,
);

CREATE TABLE publisher (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    site VARCHAR(50)
);

예시: 2NF의 교재와 출판사

CREATE TABLE textbook (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    quantity SMALLINT NOT NULL DEFAULT 0,
    publisher_id INTEGER REFERENCES publisher(id)
);

CREATE TABLE publisher (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    site VARCHAR(50)
);

연습해 봅시다!

PostgreSQL 데이터베이스 만들기