제1정규형 (1NF)

PostgreSQL 데이터베이스 만들기

Darryl Reeves

Industry Assistant Professor, New York University

예시: 학생 기록 유지 관리

CREATE TABLE student (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    courses VARCHAR(50) NOT NULL,
    home_room SMALLINT NOT NULL
);

업데이트 오류
삽입 오류
삭제 오류

예시: 업데이트 후 중복 데이터

id	name	courses	home_room
122	Susan Roth	Algebra I, Physics, Spanish II	101
413	Robert Cruz	History, Geometry, Biology	204
613	Thomas Wright	English III, Chemistry, Algebra II	102

예시: 업데이트 후 중복 데이터

id	name	courses	home_room
122	Susan Roth	Algebra I, Chemistry, Spanish II	101
413	Robert Cruz	History, Geometry, Biology	204
613	Thomas Wright	English III, Chemistry, Algebra II	102

예시: 업데이트 후 중복 데이터

id	name	courses	home_room
122	Susan Roth	Algebra I, Chemistry, Spanish II, Chemistry	101
413	Robert Cruz	History, Geometry, Biology	204
613	Thomas Wright	English III, Chemistry, Algebra II	102

예시: 열 제약 조건이 있는 삽입

CREATE TABLE student (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    courses VARCHAR(50) NOT NULL,
    home_room SMALLINT NOT NULL
);

id	name	courses	home_room
122	Susan Roth	Algebra I, Physics, Spanish II	101
413	Robert Cruz	History, Geometry, Biology	204
613	Thomas Wright	English III, Chemistry, Algebra II	102

예시: 열 제약 조건이 있는 삽입

CREATE TABLE student (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    courses VARCHAR(50) NOT NULL,
    home_room SMALLINT NOT NULL
);

id	name	courses	home_room
122	Susan Roth	Algebra I, Physics, Spanish II	101
413	Robert Cruz	History, Geometry, Biology, French Literature	204
613	Thomas Wright	English III, Chemistry, Algebra II	102

예시: 삭제로 인한 데이터 무결성 영향

id	name	courses	home_room
122	Susan Roth	Algebra I, Physics, Spanish II	101
413	Robert Cruz	History, Geometry, Biology	204
613	Thomas Wright	English III, Chemistry, Algebra II	102

예시: 삭제로 인한 데이터 무결성 영향

id	name	courses	home_room
122	Susan Roth	Algebra I, Physics, Spanish II	101
413	Robert Cruz	History, Geometry, Biology	204
613	Thomas Wright	???	102

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

1NF 요구사항:
- 테이블 값은 원자적이어야 함

예시: 1NF를 충족하는 student 테이블

CREATE TABLE student (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    courses VARCHAR(50) NOT NULL,
    home_room SMALLINT NOT NULL
);

예시: 1NF를 충족하는 student 테이블

CREATE TABLE student (
    id INTEGER,
    name VARCHAR(50) NOT NULL,
    courses VARCHAR(50) NOT NULL,
    home_room SMALLINT NOT NULL
);

예시: 1NF를 충족하는 student 테이블

CREATE TABLE student (
    id INTEGER,
    name VARCHAR(50) NOT NULL,
    course VARCHAR(50) NOT NULL,
    home_room SMALLINT NOT NULL
);

예시: 1NF를 충족하는 student 테이블

id	name	course	home_room
122	Susan Roth	Algebra I	101
122	Susan Roth	Physics	101
122	Susan Roth	Spanish II	101
413	Robert Cruz	History	204
413	Robert Cruz	Geometry	204
413	Robert Cruz	Biology	204

예시: 1NF를 충족하는 student 테이블

CREATE TABLE student (
    id INTEGER,
    name VARCHAR(50) NOT NULL,
    course VARCHAR(50) NOT NULL,
    home_room SMALLINT NOT NULL
);

예시: 1NF를 충족하는 student 테이블

CREATE TABLE student (
    student_id INTEGER,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    course VARCHAR(50) NOT NULL,
    home_room SMALLINT NOT NULL
);

예시: 1NF를 충족하는 student 테이블

id	first_name	last_name	course	home_room
122	Susan	Roth	Algebra I	101
122	Susan	Roth	Physics	101
122	Susan	Roth	Spanish II	101
413	Robert	Cruz	History	204
413	Robert	Cruz	Geometry	204
413	Robert	Cruz	Biology	204

연습해 봅시다!

PostgreSQL 데이터베이스 만들기