1re forme normale

Créer des bases de données PostgreSQL

Darryl Reeves

Industry Assistant Professor, New York University

Exemple : gestion des dossiers d'élèves

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

Erreurs de mise à jour
Erreurs d'insertion
Erreurs de suppression

Exemple : données dupliquées après mise à jour

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

Exemple : données dupliquées après mise à jour

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

Example: duplicated data after update

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

Example: insertions with column restrictions

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

Example: insertions with column restrictions

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

Example: data integrity impacted by deleting records

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

Example: data integrity impacted by deleting records

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

Satisfying 1st Normal Form (1NF)

1NF Requirement:
- Table values must be atomic

Example: student table satisfying 1NF

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

Example: student table satisfying 1NF

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

Example: student table satisfying 1NF

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

Example: student table satisfying 1NF

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

Example: student table satisfying 1NF

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

Example: student table satisfying 1NF

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
);

Example: student table satisfying 1NF

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

Let's practice!

Créer des bases de données PostgreSQL