第1正規形

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 データベースの作成