TP3 Atelier système d’information

AU 2015-2016

Langage SQL

Partie théorique

I. 1. Présentation générale

SQL (Structured Query Language) est un langage qui permet à l’utilisateur de spécifier ses interrogations en terme de résultats sans avoir à décrire les procédures à suivre pour obtenir ces résultats.

SQL fournit un ensemble de commandes pour une variété de tâches, dont:

     

l’interrogation de la base de données l’insertion, la mise à jour et la suppression des données dans la base de données la création et la modification du schéma de la BD la définition de vues le contrôle de l’accès aux données la création d'index pour accélérer les interrogations

Une commande SQL est aussi appelée instruction SQL ou requête SQL.

2. Structure du langage

En tant que langage d’accès aux données, SQL se compose de trois sous ensembles d’instructions :

 SQL est un

langage de définition de données

(LDD), c'est-à-dire qu'il tables dans une base de données relationnelle, ainsi

permet de créer des que d'en modifier ou en supprimer.

 SQL est un langage de manipulation de données (LMD), cela signifie qu'il permet de sélectionner, insérer, modifier ou supprimer des données dans une table d'une base de données relationnelle.

 SQL est un langage de protections d’accès (LCD). Il est possible avec SQL de définir au niveau des utilisateurs d'une base de données.

des permissions On parle de DCL (Data Control Language).

3. Le langage de définition de données

3.1. Type de données

Les types de données autorisés par SQL :

 Char(n) : chaine de caractères sur n positions (peut atteindre 255).  Varchar(n) ou Varchar2(n) : chaine de caractères sur n positions (peut atteindre

2000).

 Long : chaine de caractère pouvant mémoriser de longs textes allant jusqu’à 2Go.

Mme Imène Jemmali

TP3 Atelier système d’information

AU 2015-2016

 Number(n,m) : n est le nombre total de positions , m est le nombre de positions après

la virgule.

 Small Int : entier sur 15 bits.  Integer : entier sur 31 bits signés.  Float : réel.  Date : Données de type date.

3.2.Les contraintes d’intégrité définis par SQL

Une contrainte d’intégrité est une règle qui permet de contrôler la validité et la cohérence des valeurs entrées dans les différentes tables de la base. Elle peut être définie en deux moments, lors de la création d’une table ou bien par une commande de modification d’une table existante. Une contrainte d’intégrité est définie soit sur un attribut, soit sur une table.

a. Sur un attribut :

Ces contraintes suivent la définition des attributs correspondant.

 Not Null : Spécifie que pour tout tuple, l’attribut doit être renseigné.  Unique : Spécifie que toutes les valeurs de l’attribut seront distinctes.  Primary Key : Spécifie que l’attribut est une clé primaire pour la table, cette

contrainte peut être remplacée par Not Null et Unique.

 References table (attribut) : C’est la contrainte d’intégrité référentielle, elle se définit par rapport à une clé étrangère, et vérifie que chaque valeur de l’attribut existe au préalable dans la table dont l’attribut est référencé.

 Check : C’est une contrainte associée à une condition qui doit être vérifiée par toutes

les valeurs de l’attribut.

b. Sur une table :

Ces contraintes portent sur un ensemble d’attributs d’une même table, une virgule sépare la définition des contraintes des définitions des attributs.

 Unique (attribut1, attribut2, …) : L’unicité porte sur la combinaison des valeurs de

tous les attributs cités entre les parenthèses.

 Primary Key (attribut1, attribut2, …) : la clé primaire de la table est une clé

composé.

 Frreign Key (attribut1, attribut2, …) References table (attributi, attributj, …) [On Delete Cascade]: Désigne une clé étrangère compose de plusieurs attributs. L’option On Delete Cascade indique que la suppression d’une ligne de la table de référence va entrainer la suppression automatique des lignes référencées.

3.3. Création de table

Au moment de la création de la table, le no d’une table doit être unique dans la base de données et le nom d’un attribut doit être unique dans une table.

Mme Imène Jemmali

TP3 Atelier système d’information

AU 2015-2016

Syntaxe : Create nom_colonne2 type_colonne2 contrainte_colonne2, ...) ; [Définition de contrainte sur la table]

(nom_colonne1

nom_table

table

type_colonne1

contrainte_colonne1,

Exemple :

Soit les quatre tables Compétition, Résultat, Membre et Catégorie et les lignes de commande suivantes :

Base de données "Club de voile"

 Create table competition (ncomptb number(4), inticompt varchar2(30) not null, datecompt date, lieucompt varchar2((30), constraint pk_compt primary key (ncomptb) );

 Create table Membre (nmembre number(6), … Ncateg number(4), constraint pk_memb primary key( nmembre), constraint fk_membforeign key ncateg references Catégorie(ncateg));

3.4. Affichage de la structure d’une table

describe nom_table ;

3.5.Modification de la structure d’une table

a. Suppression d’une table

drop table nom_table

b. Ajout d’un attribut

Alter table nom_table

Mme Imène Jemmali

TP3 Atelier système d’information

AU 2015-2016

Add attribut type ;

Exemple :

Alter table compétition

Add duréecompt number(15) ;

c. Suppression d’un attribut

Alter table nom_table

Drop column attribut ;

Exemple :

Alter table compétition

Drop column duréecompt ;

d. Agrandissement d’un attribut existant

Alter table nom_table

Modify attribut nouveau_type ;

Exemple :

Alter table competition

Modify initcompt varchar2(40) ;

e. Ajout d’une contrainte

Alter table nom_table

Add constraint nom_contrainte définition_contrainte ;

Exemple :

Alter table Membre

Add constraint datenaissance check(age>= 18) ;

f. Suppression d’une contrainte

Alter table nom_table

Drop constraint nom_contrainte ;

Exemple :

Mme Imène Jemmali

AU 2015-2016

TP3 Atelier système d’information

Alter table Membre

Drop constraint datenaissance ;

g. Désactivation d’une contrainte

Alter table nom_table

Disable constraint nom_contrainte ;

Exemple :

Alter table Membre

Disable constraint datenaissance ;

h. Activation d’une contrainte

Alter table nom_table

Enable constraint nom_contrainte ;

Exemple :

Alter table Membre

Enable constraint datenaissance ;

II.

Partie pratique

Soit le schéma relationnel suivant :

Fournisseur (NF , NomF , Ville , Statut)

Pièces ( NP, NomP, Ville, Prix)

Projets ( NJ, NomJ, Ville, Debut)

Réalisation (#NF, #NP, #NJ, Qte)

Les types des différents attributs est comme suit :

 NF, NP, NJ et Qte number(3)  NomF, NomP et NomJ varchar2(20)  Ville varchar2(15),  Statut number(2)  Prix number(6,3)  Debut Date.

Mme Imène Jemmali

TP3 Atelier système d’information

AU 2015-2016

1. Créer la table Fournisseur et attribuer les propriétés NOT NULL et UNIQUE à sa clé

NF.

2. Créer la table Pièces et définir la contrainte pkNP de la clé primaire équivalente à

l’attribution des propriétés NOT NULL et UNIQUE.

3. Créer la table Projets sans définir de contrainte ni de propriétés sur la clé, ceci sera

défini ultérieurement par la commande ALTER TABLE.

4. Créer la table Réalisation en spécifiant la contrainte de clé primaire pkFPJ(#NF,

#NP,#NJ) et l’une des contraintes de clé étrangère fkNF.

5. Ajouter l’attribut Fin à la table Projets. 6. Ajouter des attributs Couleur et Poids à la table Pièces. 7. Eliminer l’attribut Prix de la table Pièces. 8. Modifier la taille de l’attribut Qte à NUMBER(5). 9. Ajouter la contraint pkPJ de PRIMARY KEY sur l’attribut NJ de la table Projets. 10. Retraire la contrainte pkFPJ. 11. Ajouter la contrainte pkFPJ. 12. Ajouter les contraintes de clés étrangères fkNP et fkNJ sur la table Réalisation. 13. Ajouter la contrainte ckQté CHECK sur l’attribut Qte (Qte>0) de la table Réalisation.

Mme Imène Jemmali

TP3 Atelier Système d’Information

TP3 Atelier Système d’Information