Module BDD - L3 GTR Fév.
2023
TP1- Requêtes de création Tables /Index
Oracle : Système de Gestion de Bases de Données (SGBD) relationnel (SGBDR) édité par Oracle
Corporation (http://www.oracle.com/).
SQL - Structured Query Language : langage de définition, de manipulation et de contrôle de
bases de données relationnelles (standard ANSI depuis 1986) :
• "LDD (Langage de Définition de Données) : création, modification et suppression des
définitions des tables et index
• "LMD (Langage de Manipulation de Données) : ajout, suppression, modification et
interrogation des données
• "LCD (Langage de Contrôle de Données) : gestion des protections d’accès
Chaque instruction est terminée par (;). Pour insérer des Commentaires : /* … */.
Définition des données : SQL propose aux utilisateurs un ensemble d’instructions lui permettant de
créer des tables, des indexes, des vues et d’associer à une définition d’une contrainte.
a. Création d’une table :
CREATE TABLE Nom-Table (Attribut1 TYPE, Attribut2 TYPE, …, contrainte_integrité1,
contrainte_integrité2, …);
Type des données :
• NUMBER(n) : Entier à n chiffres
• NUMBER(n, m) : Réel à n chiffres au total (virgule comprise), m après la virgule
• VARCHAR(n) : Chaîne de n caractères (entre ‘ ’) ; Char(n)
• DATE : Date au format ‘JJ-MM-AAAA’
b. Définitions des contraintes d’intégrité :
• Clé primaire : CONSTRAINT nom_contrainte PRIMARY KEY (attribut_clé [, attribut_clé2, …])
• Clé étrangère : CONSTRAINT nom_contrainte FOREIGN KEY (attribut_clé_ét) REFERENCES
table(attribut)
• Contrainte de domaine : CONSTRAINT nom_contrainte CHECK (condition)
c. Création d’index (accélération des accès)
CREATE [UNIQUE] INDEX nom_index ON nom_table (attribut [ASC|DESC], …);
CREATE INDEX ID1 ON Employé (NomE ASC);
Tel que : UNIQUE → pas de double
ASC/DESC → ordre croissant ou décroissant
1
L.Berkani
d. Destructions :
• Pour supprimer une table : DROP TABLE nom_table;
• Pour supprimer un index : DROP INDEX nom_index;
Exemples de requêtes :
Contrainte d’intégrité : clé primaire / clé étrangère
SQL> Create table Departement(
2 NumDep number(4),
3 NomDep varchar (20),
4 Localisation varchar (15),
5 Constraint DepPK Primary Key (NumDep);
Contrainte clé étrangère:
SQL> Create table Employé(
2 NumEmp number(4),
3 NomEmp varchar (20) NOT NULL,
4 NumDep number(4),
5 Fonction varchar (10),
5 DateDéb Date,
6 Constraint EmpPK Primary Key (NumEmp),
7 Constraint DepFK Foreign Key (NumDep) References Department (NumDep);
Description table créée :
SQL> Desc Employé;
Contrainte NOT NULL: pour exprimer que l’attribut est obligatoire.
Contrainte Check : limiter les valeurs d’un attribut (seules certaines valeurs seront autorisées)
Contrainte Default : insérer une valeur par défaut.
SQL> Create table Film(
2 Code number(4),
3 Titre varchar (40) Not null,
4 Durée Integer CHECK (Durée >0 and Durée < 3),
5 Nationalité DEFAULT ‘Algérienne’,
6 NomRéalisateur varchar (30) CHECK (NomRéalisateur Like '%r'
7 Constraint CodPK Primary Key (Code);
2
L.Berkani
TP 2 - Instruction de mises à jour
• Ajout d’attributs : ALTER TABLE nom_table ADD (attribut TYPE, …);
• Modifications d’attributs : ALTER TABLE nom_table MODIFY (attribut TYPE, …);
• Suppression de contraintes : ALTER TABLE nom_table DROP CONSTRAINT
nom_contrainte;
…Suppression d’attributs….
Exemples de requêtes :
customers (customerID, customerName, adr)
CREATE TABLE customers
(customerID NUMBER (10),
customerName VARCHAR(50) NOT NULL,
adr VARCHAR(50),
CONSTRAINT cust_PK PRIMAY KEY (customerID));
ALTER TABLE customers ADD (city VARCHAR(20), state VARCHAR(30));
ALTER TABLE customers MODIFY customerName VARCHAR (60);
ALTER TABLE customers MODIFY (city VARCHAR (30), state VARCHAR(40));
ALTER TABLE customers DROP COLUMN state;
ALTER TABLE customers RENAME COLUMN customerName TO cname;
ALTER TABLE customers RENAME TO contacts;
3
L.Berkani
Manipulation « 1 » – TP1 et TP 2 (Langage de définition de données)
Soit le modèle relationnel suivant relatif à la gestion des hôpitaux.
HOPITAL (CODE-HOPITAL, NOM_HOP, WILAYA, NB_SERVICE, DATE-CREATION)
SERVICE (CODE-SERVICE, NOM-SERVICE, BATIMENT, CODE-HOPITAL*)
CHAMBRE (CODE-HOPITAL, CODE-SERVICE, NUM-CHAMBRE, NUM-INF*, NB_LITS)
EMPLOYE (NUM- EMP, NOM-EMP, PRENOM-EMP, ADRESSE-EMP, TEL-EMP, CODE-
SERVICE*, CODE-HOPITAL*)
MEDECIN (NUM- MED, SPECIALITE, SALAIRE, NUM-EMP*)
INFIRMIER (NUM-INF, ROTATION, SALAIRE, NUM-EMP*)
PATIENT (NUM-PATIENT, NOM-PATIENT, PRENOM-PATIENT, ADRESSE-PATIENT,
TEL-PATIENT, MUTUELLE, CODE-SERVICE*)
HOSPITALISATION (NUM-PATIENT, DATE-HOSP, CODE-HOPITAL*, CODE_SERVICE*,
NUM_CHAMBRE*, LIT)
SOIGNE (NUM-PATIENT, NUM-MED)
Remarque : Les clés primaires sont soulignées et les clés étrangères sont marquées par *.
1- Créer les relations de base avec toutes les contraintes d’intégrité.
2- Ajouter l’attribut « Date de naissance » à la relation EMPLOYE.
3- Supprimer l’attribut « date-création » de la table HOPITAL.
4- Le salaire d’un médecin ne peut être inférieur à 46.000 DA
5- Le salaire d’un infirmier ne peut être inférieur à 30.000 DA.
4
L.Berkani
Corrigé TP1:
1. Création de quelques tables :
Create table Hopital (
CODE-HOPITAL varchar (5),
Nom_Hop varchar (20) NOT NULL,
Wilaya varchar (20),
NB_service number(5),
date-création date,
constraint hop-pk primary key (CODE-HOPITAL));
Create table SERVICE (
CODE-SERVICE varchar (5),
NOM-SERVICE varchar(20),
BATIMENT varchar(5),
CODE-HOPITAL varchar(5),
constraint serv-pk primary key (CODE-SERVICE),
constraint hop-fk foreign key (CODE-HOPITAL) references Hopital (CODE-HOPITAL));
Create table CHAMBRE (
CODE_HOPITAL varchar(5),
CODE_SERVICE varchar(5),
NUM_CHAMBRE number(5),
NUM_INF number(5),
NB_LITS number(5),
constraint CC1PK primary key (CODE_HOPITAL, CODE_SERVICE,NUM_CHAMBRE),
constraint NIFK foreign key (NUM_INF) references INFIRMIER (NUM_INF));
Create table HOSPITALISATION (
NUM_PATIENT number(5),
DATE_HOSP date,
CODE_SERVICE varchar(5),
CODE_HOPITAL varchar(5),
NUM_CHAMBRE number(5),
LIT varchar(5),
constraint CC2PK primary key (NUM_PATIENT,DATE_HOSP),
constraint CCFK foreign key (CODE_HOPITAL,CODE_SERVICE,NUM_CHAMBRE)
references CHAMBRE (CODE_HOPITAL,CODE_SERVICE,NUM_CHAMBRE));
5
L.Berkani
2. Ajouter l’attribut « Date de naissance » à la relation EMPLOYE.
Alter table EMPLOYE ADD DATE_n date;
3. Supprimer l’attribut « date-création » de la table HOPITAL.
Alter table Hopital Drop column date_creation ;
4. Ajouter les contraintes suivantes :
Le salaire d’un médecin ne peut être inférieur à 46.000 DA.
Alter table Medecin add constraint check_salaire check (salaire > 460000 );
5. Le salaire d’un infirmier ne peut être inférieur à 35.000 DA.
Alter table INFIRMIER ADD constraint check_SAL check (SALAIRE> 35000);
6
L.Berkani
