Bases de données relationnelles Hiver 2021

Geoffrey Glangine, M.Sc.

8TRD151

Création et administration des bases de données

Rappel Nomenclature SQL

En SQL, les noms des tables et des colonnes sont soumises à des règles très strictes On ne doit uniquement mettre des noms composés de Lettres (pas d'accents), de chiffres, et de signes #,$ et _.

On ne peut pas commencer par un chiffre.

Le langage n'est pas sensible à la casse !

Mais par convention, on a tendance à écrire le nom des commandes SQL en MAJUSCULES

Partie définition du schéma de base de données

Commençons par une créer table

	
		 CREATE TABLE CLIENT(
		 NCLI 		CHAR(10),
		 NOM 		CHAR(32),
		 ADRESSE CHAR(60),
		 LOCALITE CHAR(30),
		 COMPTE DECIMAL(9,2)
		 );

Commençons par une créer table

	
		 CREATE IF NOT EXISTS TABLE CLIENT(
		 NCLI 		CHAR(10),
		 NOM 		CHAR(32),
		 ADRESSE CHAR(60),
		 LOCALITE CHAR(30),
		 COMPTE DECIMAL(9,2)
		 );

Syntaxe sql

Commençons par une créer table

	
		 CREATE OR  REPLACE TABLE CLIENT(
		 NCLI 		CHAR(10),
		 NOM 		CHAR(32),
		 ADRESSE CHAR(60),
		 LOCALITE CHAR(30),
		 COMPTE DECIMAL(9,2)
		 );

syntaxe SQLserver

Commençons par une créer table

	
		 DROP TABLE IF EXISTS CLIENT;
		 CREATE TABLE CLIENT(
		 NCLI 		CHAR(10),
		 NOM 		CHAR(32),
		 ADRESSE CHAR(60),
		 LOCALITE CHAR(30),
		 COMPTE DECIMAL(9,2)
		 );

syntaxe mySQL

Les colonnes et leurs types

La norme sql définit ces types :

booléen : BOOLEAN
caractère : CHAR, VARCHAR
nombre exact : NUMERIC, DECIMAL, INTEGER, SMALLINT, BIGINT
nombre approx : FLOAT, REAL, DOUBLE PRECISION
date/heure : DATE, TIME, TIMESTAMP
intervalle : INTERVAL
"gros" objets : CHARACTER LARGE OBJECT, BINARY LARGE OBJECT

Par défaut, toutes les colonnes sont facultatives, mais il est possible de les rendre obligatoires en ajoutant la clause NOT NULL


CREATE TABLE CLIENT(
NCLI 		INT not null,
NOM 		VARCHAR(30),
ADRESSE 	CHAR(60),
LOCALITE 	VARCHAR(30),
COMPTE 		DECIMAL(9,2)
);

Les identifiants / clés primaires

Pour ajouter une clé primaire, il suffit de compléter la clause de création de Table par PRIMARY KEY (id)


CREATE TABLE CLIENT(
NCLI 		INT not null,
NOM 		VARCHAR(30),
ADRESSE 	CHAR(60),
LOCALITE 	VARCHAR(30),
COMPTE 		DECIMAL(9,2),
PRIMARY KEY (NCLI)
);

En fait, les instruction, primary key, foreign key et not null definissent les contraintes d'intégrité !

il existe 5 types de contraintes d'intégrité :

Données requises
Contraintes de domaine
Intégrité d<'entité
Intégrité référentielle
Contraintes générales

Données requises :

Pour une colonne non facultative on exige que l'attribut ait toujours une valeur différente de null :

nom VARCHAR(30) NOT NULL

Contraintes de domaine :

Spécifie les valeurs permises (i.e. le domaine) pour un attribut

propertyType CHAR NOT NULL
CHECK (VALUE IN ('B', 'C'))

Contraintes de domaine :

On peut créer un domaine :

CREATE DOMAIN NomDomaine AS type
				DEFAULT valeur
				CHECK condition

Contraintes de domaine :

exemple

CREATE DOMAIN domainAge AS INTEGER
				DEFAULT 0
				CHECK (VALUE BETWEEN 0 AND 99)

CREATE TABLE CLIENT(
NCLI 		INT not null,
NOM 		VARCHAR(30),
AGE domainAge

Contraintes d'intégrité référentielle :

L'instruction PRIMARY KEY s'assure que la clé primaire contienne des valeurs uniques et non nulles.

PRIMARY KEY (staffNo)
PRIMARY KEY (clientNo,propertyNo)

Une seule PRIMARY KEY possible par table, mais on peut imposer que d'autres colonnes aient des valeurs uniques avec le mot-clé UNIQUE:

UNIQUE (telNo)

Contraintes d'intégrité référentielle (clé étrangère)

Pour ajouter une clé étrangère, il suffit de compléter la clause de création de Table par FOREIGN KEY (colonne) REFERENCES (NOM_TABLE_PROVENANCE)


CREATE TABLE CLIENT(
NCLI 		INT not null,
NOM 		VARCHAR(30),
ADRESSE 	CHAR(60),
LOCALITE 	VARCHAR(30),
COMPTE 		DECIMAL(9,2),
id_MARQUE_VOITURE INTEGER,
PRIMARY KEY (NCLI),
FOREIGN KEY (id_MARQUE_VOITURE) REFERENCES (MARQUE_VOITURE)
);

ATENTION ! Les clés étrangères ont quelques petites particularités...

Comportement lors de la mise à jours des données

En effet, en créant des clés étrangères, vous allez créer des liens entre vos tables, évidement en faisant cela, si vous modifiez ou supprimez un élément d'une table, il peut y avoir des conséquences sur les autres tables ! Mais SQL permet de sélectionner entre 3 modes de mise à jours dans ce genre de situation.

Blocage
Propagation
Découplage

Blocage

Dans ce cas, la suppression est bloquée. Si des éléments dépendent de l'élément que l'on cherche à supprimer, alors la suppression est bloquée. La clause est ON DELETE NO ACTION


CREATE TABLE CLIENT(
NCLI 		INT not null,
NOM 		VARCHAR(30),
id_MARQUE_VOITURE INTEGER,
...
PRIMARY KEY (NCLI),
FOREIGN KEY (id_MARQUE_VOITURE) REFERENCES (MARQUE_VOITURE)
				ON DELETE NO ACTION;
);

Dans ce cas, si l'utilisateur supprime la marque de voiture, la suppression sera bloquée.

Propagation

Dans ce cas, la suppression est propagée. Si des éléments dépendent de l'élément que l'on cherche à supprimer ces éléments sont supprimés. La clause est ON DELETE CASCADE


CREATE TABLE CLIENT(
NCLI 		INT not null,
NOM 		VARCHAR(30),
id_MARQUE_VOITURE INTEGER,
...
PRIMARY KEY (NCLI),
FOREIGN KEY (id_MARQUE_VOITURE) REFERENCES (MARQUE_VOITURE)
				ON DELETE CASCADE;
);

Dans ce cas, si l'utilisateur supprime la marque de voiture, le client sera supprimé.

Découplage

Dans ce cas, la suppression se déroule simplement. Si des éléments dépendent de l'élément que l'on cherche à supprimer la valeur de la clé étrangère sera mise à NULL. La clause est ON DELETE SET NULL


CREATE TABLE CLIENT(
NCLI 		INT not null,
NOM 		VARCHAR(30),
id_MARQUE_VOITURE INTEGER,
...
PRIMARY KEY (NCLI),
FOREIGN KEY (id_MARQUE_VOITURE) REFERENCES (MARQUE_VOITURE)
				ON DELETE CASCADE;
);

Dans ce cas, si l'utilisateur supprime la marque de voiture, le client sera supprimé.

La modification des valeurs de clé primaire sont régies de la même manière, si l'on souhaite modifier les clés primaires des tables qui ont été liées à d'autres. Il est donc possible d'utiliser la clause ON UPDATE comme la clause ON DELETE


CREATE TABLE CLIENT(
NCLI 		INT not null,
NOM 		VARCHAR(30),
id_MARQUE_VOITURE INTEGER,
	...
PRIMARY KEY (NCLI),
FOREIGN KEY (id_MARQUE_VOITURE) REFERENCES (MARQUE_VOITURE)
				ON DELETE NO ACTION
				ON UPDATE CASCADE
);

Si la clé ne possède qu'une seule colonne, alors il est possible de réduire un peu le script SQL en ajoutant seulement PRIMARY KEY ou REFERENCES


CREATE TABLE CLIENT(
NCLI 		INT not null PRIMARY KEY,
NOM 		VARCHAR(30),
id_MARQUE_VOITURE INTEGER REFERENCES MARQUE_VOITURE,
	...
);

Contraintes générales

Toute condition supplémentaire spécifiée par l'utilisateur. On utilise le mot CHECK pour la déclarer.

CREATE DOMAIN OwnerNumber AS VARCHAR(5)
CHECK (VALUE IN (
SELECT ownerNo FROM PrivateOwner));
CREATE TABLE MaTable (
abc AS SMALLINT NOT NULL,
CHECK (abc BETWEEN 1 AND 10));

Exemple de la Base de donnée du cinéma :


CREATE TABLE support(
id_support INT not null AUTO_INCREMENT PRIMARY KEY,
libelle_support VARCHAR
);
CREATE TABLE genre(
id_genre INT not null AUTO_INCREMENT PRIMARY KEY,
libelle_genre VARCHAR
);

Exemple de la Base de donnée du cinéma :


CREATE TABLE annee(
id_annee INT not null AUTO_INCREMENT PRIMARY KEY,
libelle_annee INT
);
CREATE TABLE acteur(
id_acteur INT not null AUTO_INCREMENT PRIMARY KEY,
nom_acteur VARCHAR,
prenom_acteur VARCHAR,
photo VARBINARY(max),
nationalite VARCHAR,
);

Exemple de la Base de donnée du cinéma :


CREATE TABLE Film(
id_film Int not null AUTO_INCREMENT PRIMARY KEY,
titre VARCHAR,
duree Int,
synopsis TEXT,
id_annee INT REFERENCES annee(id_annee)
);

Exemple de la Base de donnée du cinéma :


CREATE TABLE Films_Genres(
id_film INT REFERENCES film(id_film),
id_genre INT REFERENCES Genre(id_genre),
primary key(id_film, id_genre)
)
CREATE TABLE Films_Support(
id_film INT REFERENCES film(id_film),
id_support INT REFERENCES Support(id_support),
PRIMARY KEY(id_film, id_support)
)

CREATE TABLE Films_Acteur(
id_film INT REFERENCES film(id_film),
id_acteur INT REFERENCES acteur(id_acteur),
PRIMARY KEY(id_film, id_acteur)
)

Le code est disponible ICI en intégralité (:

Suppression d'une table

Pour supprimer une table il faut utiliser l'instruction drop table


		 DROP TABLE nomTable;

Si au moment de la suppression la table contient des lignes, alors celles-ci sont préalablement supprimées.

Ajout de contraintes

Un fois la table créée, il est possible de la modifier. Pour ce faire, il faut utiliser la commande Alter Table


		 ALTER TABLE Client ADD PRIMARY KEY (id_client)

Si la table contient déjà des lignes qui violent la contrainte que l'on souhaite ajouter, alors il sera impossible de l'ajouter.

Ajout / suppression d'une contrainte d'obligation de colonne


		 ALTER TABLE Client ALTER nom_client not null;
		 ALTER TABLE Client ALTER nom_client null;

Si la table contient déjà des lignes qui violent la contrainte que l'on souhaite ajouter, alors il sera impossible de l'ajouter.

Ajout d'une contrainte de clé étrangère


		 ALTER TABLE Client add FOREIGN KEY (id_type) REFERENCES Type;
		 ALTER TABLE Client ALTER nom_client null;

Si la table contient déjà des lignes qui violent la contrainte que l'on souhaite ajouter, alors il sera impossible de l'ajouter.