Bases de données

SQL

Définition des données

Bases de données

Instructions

Définitions (LDD)

CREATE, DROP, ALTER

Bases de données

Définition des structures

3 principales étapes:

1. Création de la base de donnée

2. Description des schémas de tables (relations) et de leur contenu

3. Définition des contraintes qui assurent des contrôles sur l’intégrité des données

Bases de données

Exemple

num nom age fonc sup datent salaire comm dept

Employe

Departement

num nom noresp datecre loc

Bases de données

Numériques

Chaînes de caractères

Dates

Types de la norme ANSI

Bases de données

Types numériques

INTEGER Entiers relatifs 4 octets

SMALLINT Entiers relatifs 2 octets

BIGINT Entiers relatifs 8 octets

FLOAT Flottants 4 octets

DOUBLE Flottants 8 octets

Bases de données

Types chaînes de caractères

CHAR(M) Chaînes de longueur fixe

M octets

VARCHAR(M) Chaînes de longueur variable

au plus M octets

Text Chaînes d'octets longueur de la chaîne

Bases de données

Types dates

DATE date(hour, mois, an) 4 octets

TIME heure(h, mm, ss) 4 octets

DATETIME date et heure 8 octets

YEAR année 2 octets

Bases de données

• Instructions sur les relations

CREATE TABLE créer une relation

DROP TABLE supprimer une relation

ALTER TABLE modifier la structure d'une relation

Définition des relations

Bases de données

CREATE TABLE Employe(

num INTEGER,

nom VARCHAR(20),

age INTEGER,

fonc VARCHAR(10),

sup CHAR(4),

datent DATE,

salaire DOUBLE,

comm DOUBLE,

dept INTEGER);

Exemple

Bases de données

CREATE TABLE Departement(

num INTEGER,

nom VARCHAR(14),

noresp INTEGER,

datcre DATE,

loc VARCHAR(15) );

Bases de données

Lors de la création de la relation :

sur valeurs nulles et valeurs par défaut

contraintes de clé

d' intégrité référentielle

sur le domaine de l'attribut

Définition des contraintes

Bases de données

NOT NULL pour préciser que l’attribut correspondant doit toujours avoir une valeur

motDePasse VARCHAR(60) NOT NULL

DEFAULT pour définir la valeur d' un attribut par défaut

adresse VARCHAR (50) DEFAULT ’Inconnue’

Valeurs nulles, Valeurs par défaut

Bases de données

CREATE TABLE Employe(

num CHAR(4) NOT NULL,

nom VARCHAR(20),

age NUMERIC(2),

fonc VARCHAR(10),

sup CHAR (4),

datent DATE,

salaire NUMERIC(6,2),

comm NUMERIC(3,2),

dept CHAR (2) );

Exemple

Bases de données

CREATE TABLE Departement(

num CHAR (2) NOT NULL,

nom VARCHAR(14),

noresp CHAR (4),

datcre DATE,

loc VARCHAR(15) DEFAULT 'NICE' );

Bases de données

Clés primaires

PRIMARY KEY (att)

PRIMARY KEY (att1, ..., attn)

Tous les attributs figurant dans une clé doivent être déclarés NOT NULL

On peut également spécifier que la valeur d’un attribut est unique pour l’ensemble de la colonne. (UNIQUE) (clés secondaires)

UNIQUE (nom, prenom)

Bases de données

CREATE TABLE Employe(

num CHAR(4) NOT NULL,

nom VARCHAR(20),

age NUMERIC(2),

fonc VARCHAR(10),

sup CHAR (4),

datent DATE,

salaire NUMERIC(6,2),

comm NUMERIC(3,2),

dept CHAR (2),

PRIMARY KEY (num) );

Bases de données

CREATE TABLE Departement(

num CHAR (2) NOT NULL,

nom VARCHAR(14) NOT NULL,

noresp CHAR (4),

datcre DATE,

loc VARCHAR(15) NOT NULL DEFAULT 'NICE',

PRIMARY KEY (num),

UNIQUE(nom, loc) );

Bases de données

Clés étrangères

FOREIGN KEY (att) REFERENCES ...

FOREIGN KEY (att1, ..., attn) REFERENCES ...

FOREIGN KEY(numprod) REFERENCES Produit(num)

indique que numprod référence la clé primaire de la table

Produit

En cas d'insertion, suppression, majle SGBD vérifie la violation de la contrainteAction par défaut : rejet de l'opération

Intégrité référentielle

Bases de données

CREATE TABLE Employe( num CHAR(4) NOT NULL,nom VARCHAR(20),age NUMERIC(2),fonc VARCHAR(10),sup CHAR(4),datent DATE,salaire NUMERIC(6,2),comm NUMERIC(3,2), dept CHAR(2), PRIMARY KEY (num),FOREIGN KEY(dept) REFERENCES Department(num),FOREIGN KEY(sup) REFERENCES Employe(num));

Bases de données

CREATE TABLE Departement(

num CHAR (2) NOT NULL,

nom VARCHAR(14) NOT NULL,

noresp CHAR (4),

datcre DATE,

loc VARCHAR(15) NOT NULL DEFAULT 'NICE' ,

PRIMARY KEY (num),

UNIQUE(nom, loc),

FOREIGN KEY(noresp) REFERENCES Employe(num));

Bases de données

en cas de violation, si l'action par défaut de rejet de l'opération ne convient pason peut spécifier la répercussion de la mise à jour

ON UPDATE ON DELETE

de manière à ce que la contrainte soit respectéeSET NULL clé étrangère mise à NULL CASCADE application de la même opération SET DEFAULT valeur par défaut pour la clé

étrangère

FOREIGN KEY(numprod) REFERENCES ProduitON DELETE CASCADEON UPDATE CASCADE

Clauses d'action référentielle

Bases de données

CREATE TABLE Employe( num CHAR(4) NOT NULL,nom VARCHAR(20),age NUMERIC(2),fonc VARCHAR(10),sup CHAR(4),datent DATE,salaire NUMERIC(6,2),comm NUMERIC(3,2), dept CHAR(2), PRIMARY KEY (num),FOREIGN KEY(dept) REFERENCES Department(num)ON DELETE SET NULL ON UPDATE CASCADE,FOREIGN KEY(sup) REFERENCES Employe(num)ON DELETE SET NULL ON UPDATE CASCADE);

Bases de données

CREATE TABLE Departement(

num CHAR (2) NOT NULL,

nom VARCHAR(14) NOT NULL,

noresp CHAR (4),

datcre DATE,

loc VARCHAR(15) NOT NULL DEFAULT 'NICE' ,

PRIMARY KEY (num),

UNIQUE(nom, loc),

FOREIGN KEY(noresp) REFERENCES Employe

ON UPDATE CASCADE );

Bases de données

CHECK sur un attribut ou une ligne

CHECK (annee BETWEEN 1900 AND 2050)CHECK (nomCinema IN (SELECT nom FROM Cinema))CHECK (capacite<300 OR climatise = ’O’)

En cas d'insertion ou d’une modification,

le SGBD vérifie que la nouvelle valeur vérifie la contrainte

spécifiée par la clause CHECK

Contrainte nommée

CONSTRAINT annee_cst CHECK (annee BETWEEN 1900 AND 2050)

Contraintes sur domaines

Bases de données

Suppression d'une relation

DROP TABLE nom_de_relation ;

Modification d'une table

ALTER TABLE nom_de_relation ACTION description

Modification du schéma

Bases de données

ALTER TABLE Departement MODIFY nom VARCHAR(20)

ALTER TABLE Employe ADD adresse VARCHAR(20);

ALTER TABLE Employe ALTER adresse SET DEFAULT ’NICE’;

ALTER TABLE Employe DROP adresse CASCADE;

ALTER TABLE Departement ADD CONSTRAINT NLocUniqueUNIQUE (nom, loc)

ALTER TABLE Employe ADD CONSTRAINT E_cst PRIMARY KEY (num)

ALTER TABLE Employe ADD CONSTRAINT E_cstFOREIGN KEY (sup) REFERENCES Employe