Portfolio de Riadh Hajji

1. les inconvénients pour l’utilisation des fichiers :
1.1. Si on utilise le même fichier à un temps donnée et dans le même emplacement on bloque le système d’où la notion de collusion.
1.2. Ainsi on peut trouve redondance des données c'est-à-dire le même information peut se trouve dans plusieurs fichiers, ainsi ses données peuvent être incohérent.
2. Les principaux intervenants dans une base de données sont :
2.1. Les utilisateurs de la base de données qui gèrent et synchronisent et contrôlent les données.
2.2. Les administrateurs de la base de données (ABD) qui gèrent ou contrôlent l’accès à la base de données.
3. Schéma de l’architecture ANSI-SPARC :
3.1. SWSGBD
(Synchronise
et agit sur
les données)
4. La différence entre SGBD et DBA.

4.1. Le DBA (Administrateur de Base de Donnés – Data Base Administrator) :
4.1.1. est la personne qui gère et garantit le fonctionnement du SGBD.
4.1.2. Responsable de l'organisation du système et de la base afin d’obtenir les meilleures performances pour l'entreprise
4.1.3. détermine la structure de stockage, stratégie d'accès
4.1.4. définit les autorisations et les procédures de validation
4.1.5. définit une procédure de sauvegarde
SGBD (Système de Gestion des Bases de Données)

5. Le SGBD est un ensemble de services (applications logicielles) permettant de gérer les bases de données.
Ces fonctionnalités sont les suivantes :
5.1. permettre l'accès aux données de façon simple
5.2. autoriser un accès aux informations à de multiples utilisateurs
5.3. manipuler les données présentes dans la base de données (insertion, suppression, modification)

5) Concevoir une BD hiérarchique pour gérer les emplois de temps d’une institution universitaire :
Enseignant, Etudiant, Salle, Cours.
schéma hiérarchique pour cette base :
cours
enseignant « A » , enseignant « b »
salle « A1 » salle« A10 », salle« A12 », salle« A4 »
Etudiant1, Etudiant5, Etudiant8, Etudiant7 Etudiant15, Etudiant2, Etudiant20, Etudiant8

Exercice n°1: Création des tables supplier S, pièces P, projets et SPJ

table supplier S:
create Table S (s# varchar(10),
nom varchar(20),
adrs varchar(50),
ville varchar(20));

table pieces P:
create Table P (p# varchar(10),
couleur varchar(20),
nom varchar(10),
ville varchar(20));

table projets J:
create Table J (J# varchar(10),
nom varchar(20),
ville varchar(20));

table SPJ:
create Table SPJ (s# varchar(10),
p# varchar(10),
J# varchar(10),
Qty number(5));

Exercicie n°2
1/ Select * from J;
2/select * from J
where ville='Londre';
3/select S# from SPJ
Where J#='J1' Order By S#;
4/select p# from SPJ
Where qty Between 300 and 700;
5/select distinct couleur,ville from P;
6/select s#,j#,p# from S,P,J
where s.ville=p.ville and
p.ville=j.ville;
7/select S#,P#,J# from s,p,j
where S.ville != p.ville and s.ville!=p.ville;
8/select p# from S,SPJ
where s.S#=SPJ.S# and s.ville='Londre';
9/select S#,p#,j# from S,P,J,SPJ
where s.s#=spj.s# and
p.p#=sps.p# and
j.j#=spj.j# and
s.ville!=p.ville and
p.ville!=j.ville and
s.ville!=j.ville;
10/select p#
from s, J, spj
WHERE s.s#=spj.s# And j.j#=spj.j# And s.ville=j.ville And s.ville='londre';
11/select s.ville, j.ville from s,j,spj
where s.s#=spj.s# and spj.j=j.j#;
12/select count(*) from s;
13/select count(distinct s#) from spj;
14/select count(s#) from spj
where p#='p2';
15/select sum(qty)from spj
where p#='p2';

le travail est réalisé sur Oracle9i

**** création de la table fournisseur F****
create table F (
f# varchar2(2) constraint pk_f primary key,
fnom varchar(10) not null,
statut numeric(5) not null,
ville varchar2(20) not null);
***********************************************************
**** création de la table piece P *****
create table P (
p# numeric(5) constraint pk_p primary key,
pnom varchar(10) not null,
couleur varchar(10),
poids number(10,3),
ville varchar2(20) not null);
************************************************************
**** création de la table projet J *****
create table J (
j# varchar2(2) constraint pk_j primary key,
jnom varchar2(10) not null,
ville varchar2(20) not null);
**************************************************************
**** création de la table quantite fournie par fournisseur FPJ *****
create table FPJ (
f# varchar2(2),
p# varchar2(2),
j# varchar2(2),
qte number check( qte>0),
constraint pk_fpj primary key (f#,p#,j#),
constraint fk_fpj1 foreign key (f#) references F(f#),
constraint fk_fpj2 foreign key (p#) references P(p#),
constraint fk_fpj3 foreign key (j#) references J(j#));


**************************************************************
*** remplissage de la table fournisseur***

insert into F values('f1','smith',20,'Londre');
insert into F values('f2','jones',10,'Paris');
insert into F values('f3','blake',30,'Paris');
insert into F values('f4','klarck',20,'Londre');
insert into F values('f5','adams',30,'Athenes');

**** contenu de la table fournisseur aprés insertion*****

F# FNOM STA VILLE
-- ---------- --- ---------
f1 smith 20 Londre
f2 jones 10 Paris
f3 blake 30 Paris
f4 klarck 20 Londre
f5 adams 30 Athenes

**************************************************************
*** remplissage de la table piece***

insert into P values('P1','Nut','Rouge',12,'Londre');
insert into P values('P2','Boltes','Vert',17,'Paris');
insert into P values('P3','Screw','Bleu',17,'Rome');
insert into P values('P4','Screw','Rouge',14,'Londre');
insert into P values('P5','Cam','Bleu',12,'Paris');
insert into P values('P6','Cog','Rouge',19,'Londre');

**** contenu de la table piece aprés insertion*****


P# PNOM COULEUR POIDS VILLE
-- ---------- ---------- ---------- ---------
P1 Nut Rouge 12 Londre
P2 Boltes Vert 17 Paris
P3 Screw Bleu 17 Rome
P4 Screw Rouge 14 Londre
P5 Cam Bleu 12 Paris
P6 Cog Rouge 19 Londre

**************************************************************
*** remplissage de la table projet***
insert into J values ('J1', 'Sorter','Paris');
insert into J values ('J2', 'Punsh','Rome');
insert into J values ('J3', 'Reader','Athenes');
insert into J values ('J4', 'Cpnsole','Athenes');
insert into J values ('J5', 'Collator','Londre');
insert into J values ('J6', 'Terminal','Oslo');
insert into J values ('J7', 'Tape','Londre');

**** contenu de la table projet aprés insertion*****
J# JNOM VILLE
-- ---------- ------------
J1 Sorter Paris
J2 Punsh Rome
J3 Reader Athenes
J4 Cpnsole Athenes
J5 Collator Londre
J6 Terminal Oslo
J7 Tape Londre

**************************************************************
*** remplissage de la table FPJ***
INSERT into FPJ values( 'f1','p1','j1',200);
INSERT into FPJ values( 'f1','p1','j4',700);
INSERT into FPJ values( 'f2','p3','j1',400);
INSERT into FPJ values( 'f2','p3','j2',200);
INSERT into FPJ values( 'f2','p3','j3',200);
INSERT into FPJ values( 'f2','p3','j4',500);
INSERT into FPJ values( 'f2','p3','j5',600);
INSERT into FPJ values( 'f2','p3','j6',400);
INSERT into FPJ values( 'f2','p3','j7',800);
INSERT into FPJ values( 'f2','p5','j2',100);
INSERT into FPJ values( 'f3','p3','j1',200);
INSERT into FPJ values( 'f3','p4','j2',500);
INSERT into FPJ values( 'f4','p6','j3',300);
INSERT into FPJ values( 'f4','p6','j7',300);
INSERT into FPJ values( 'f5','p2','j2',200);
INSERT into FPJ values( 'f5','p2','j4',100);
INSERT into FPJ values( 'f5','p5','j5',500);
INSERT into FPJ values( 'f5','p5','j7',100);
INSERT into FPJ values( 'f5','p6','j2',200);
INSERT into FPJ values( 'f5','p1','j4',100);
INSERT into FPJ values( 'f5','p3','j4',200);
INSERT into FPJ values( 'f5','p4','j4',800);
INSERT into FPJ values( 'f5','p5','j4',400);
INSERT into FPJ values( 'f5','p6','j4',500);

*************************************************
**** modificataion le contenu d'une colonne***
update P set P# =lower(P#);
update J set J#= lower(J#);
update F set F#= lower(F#);
***************************************************
**** suppression d'une table ****
drop table FPJ;
***************************************************
**** contenu de la table FPJ ****
F# P# J# QTE
-- -- -- ----------
f1 p1 j1 200
f1 p1 j4 700
f2 p3 j1 400
f2 p3 j2 200
f2 p3 j3 200
f2 p3 j4 500
f2 p3 j5 600
f2 p3 j6 400
f2 p3 j7 800
f2 p5 j2 100
f3 p3 j1 200

F# P# J# QTE
-- -- -- ----------
f3 p4 j2 500
f4 p6 j3 300
f4 p6 j7 300
f5 p2 j2 200
f5 p2 j4 100
f5 p5 j5 500
f5 p5 j7 100
f5 p6 j2 200
f5 p1 j4 100
f5 p3 j4 200
f5 p4 j4 800

F# P# J# QTE
-- -- -- ----------
f5 p5 j4 400
f5 p6 j4 500

********************************************************
**** les requetes de la serie n°3 ***
1)Donnez les numéros des fournisseurs, des pièces et projets qui ont la même localisation.

select f#,p#,j#
from F,P,J
where F.ville=P.ville
and P.ville=J.ville ;

résultat affiché

F# P# J#
-- -- --
f1 p1 j5
f4 p1 j5
f1 p4 j5
f4 p4 j5
f1 p6 j5
f4 p6 j5
f1 p1 j7
f4 p1 j7
f1 p4 j7
f4 p4 j7
f1 p6 j7

F# P# J#
-- -- --
f4 p6 j7
f2 p2 j1
f3 p2 j1
f2 p5 j1
f3 p5 j1

2)Donnez pour toutes les pièces l’identificateur et le poids en gramme (multiplier par 10 la valeur de la table). Le résultat sortira comme l’exemple suivant :
P1 Poids en grammes = 5823

select rpad(p#,20,' Poids en grammes= ') as "identificateurs" ,(10*poids)as "Poids"
from P;
*** rpad c'est une fonction qui permet de compléter une valeur de chaine en lui ajoutant des caracteres à droite.

résultat affiché

identificateurs Poids
----------------------- ------
p1 Poids en grammes= 120
p2 Poids en grammes= 170
p3 Poids en grammes= 170
p4 Poids en grammes= 140
p5 Poids en grammes= 120
p6 Poids en grammes= 190

3)Donnez pour toutes les pièces l’identificateur et le poids ordonnés selon le poids et l'identificateur;

select p#,poids
from P
order by poids,p#;

résultat affiché

P# POIDS
-- ----------
p1 12
p5 12
p4 14
p2 17
p3 17

4)Donnez le nombre des fournisseurs qui fournissent des pièces.

select count(distinct f#) as "Nombre des fournisseurs"
from fpj;

***résultat affiché ***

Nombre des fournisseurs
-----------------------
5
5) Pour la pièce P2, donnez la quantité totale fournie.

select sum(qte) as" qte totale de p2"
from FPJ
where p#='p2';

***résultat affiché***

qte totale de p2
-----------------
300

6)Donnez les identificateurs des pièces fournies par plus d'un fournisseur.

select p#
from FPJ
group by p#
having count(distinct f#)>1;

***résultat affiché***

P#
--
p1
p3
p4
p5
p6

7)Donnez toutes les pièces dont le nom commence par la lettre C.

select *
from P
where lower(PNOM) like'c%';

***résultat affiché***


P# PNOM COULEUR POIDS VILLE
-- ---------- ---------- ---------- ---------
p5 Cam Bleu 12 Paris
p6 Cog Rouge 19 Londre

8)Donnez les noms des fournisseurs qui fournissent au moins une pièce rouge


select distinct F.fnom
from F,FPJ,P
where lower(P.couleur) like 'rouge'
and F.f#=FPJ.f#
and P.p#=FPJ.p#;

select F.F#
from F,FPJ,P
where F.f#=FPJ.f#
and P.p#=FPJ.p#
minus (
select F.F#
from F,FPJ,P
where lower(P.couleur) <> 'rouge'
);

***résultat affiché***
FNOM
--------
adams
blake
klarck
smith

9)Donnez les identificateurs des fournisseurs ayant un statut inférieur au maximum actuel de la table F

select F# from F where statut <
(select max(statut)
from F
);

***résultat affiché***
F#
--
f1
f2
f4

10)Donnez les différentes paires de villes telleque la première ville soit celle d’un fournisseur qui fournit un projet dont la ville correspond au deuxième élément de la paire

select distinct f.ville,j.ville from f,j,fpj
where f.f#=fpj.f#
and j.j#=fpj.j#;

***résultat affiché***
VILLE VILLE
-------------------- --------
Athenes Athenes
Athenes Londre
Athenes Rome
Londre Athenes
Londre Londre
Londre Paris
Paris Athenes
Paris Londre
Paris Oslo
Paris Paris
Paris Rome

11)Donnez les identificateurs des fournisseurs qui fournissent au moins toutes les pièces fournies par F2

select distinct f#,p#
from fpj
where p# in(
select p#
from fpj
where f#='f2'
group by p#);

12) Donnez les noms des fournisseurs qui ne fournissent pas P2.

select fnom
from f
where not exists (select *
from fpj
where p#='p2'
and f.f#=fpj.f#);
***résultat affiché***
FNOM
--------
smith
jones
blake
klarck

13)Déterminez les numéros des fournisseurs fournissant des projets avec la pièce P1 en une quantité supérieure à la moyenne avec laquelle le projet a été fourni en P1.

select f#
from fpj
where p#='p1'
and qte>
(select avg(qte)
from fpj
where p#='p1');

***résultat affiché***
F#
--
f1

14)Doublez le statut de tous les fournisseurs de Londre

update f
set statut=2*statut
where ville ='Londre';

15) Supprimer les fournisseurs S1 de la table S

delete from f
where f#='f1';

\\ j'ai pas compris la requete 12

PARTIE I

1) Création de la table compagnie & la table pilote
*** table compagnie ***
create table compagnie (
comp varchar2(5) constraint pk_c primary key,
nrue numeric(5),
rue varchar2(10),
ville varchar2(10) default 'Paris',
nomcomp varchar2(20) not null);

*** table pilote ***
create table pilote (
brevet varchar2(5) constraint pk_b primary key,
nom varchar2(20) not null constraint u_nom unique,
nbhvol numeric(5) check( nbhvol between 0 and 20000),
compa varchar2(20) ,
constraint pf_c foreign key (compa) references compagnie (comp));

insertion des données:
table compagnie:
insert into compagnie values('AF',124,'Port Royal','Paris','Air France');
insert into compagnie values('Sing',7,'Camparols','Singapour','Singapore AL');

COMP NRUE RUE VILLE NOMCOMP
------------ ---------- ---------- --------- ---------
AF 124 Port Royal Paris Air France
Sing 7 Camparols Singapour Singapore AL


table pilote
insert into pilote values('PL-1','Amélie Sulpice',450,'AF');
insert into pilote values('PL-2','Thomace Sulpice',900,'AF');

BREVE NOM NBHVOL COMPA
----- -------------------- ---------- -----
PL-1 Amélie Sulpice 450 AF
PL-2 Thomace Sulpice 900 AF

2)Ecrivez l'instruction d'insertionde l'occrence suivante dans la table pilote:
insert into pilote values('PL-3','Paul Soutou',1000,'Sing');

BREVE NOM NBHVOL COMPA
----- -------------------- ---------- -----
PL-1 Amélie Sulpice 450 AF
PL-2 Thomace Sulpice 900 AF
PL-3 Paul Soutou 1000 Sing

3)update compagnie set nrue = 50 where comp='AF';

****Résultat à afficher**************************
COMP NRUE RUE VILLE NOMCOMP
----- ---------- ---------- ---------- --------------------
AF 50 Port Royal Paris Air France
Sing 7 Camparols Singapour Singapore AL

Création et insertion des données pour les tables Avion AF & AvionSing
*** table Avion AF ***
create table AvionAf(
immat varchar2(10) constraint pk_immat primary key,
typeAvion varchar2(10),
nbhvol numeric(4));

insertion des données:
insert into AvionAF values('F-Wtss','Concorede',6570);
insert into AvionAF values('F-Glfs','A320',3500);
insert into AvionAF values('F-Gtmp','A340',3000);

*** table AvionSing ***
create table AvionSing(
immat varchar2(10) constraint pk_immat_sing primary key,
typeAvion varchar2(10),
prixachat numeric(10));


SQL> select * from avionaf;
IMMAT TYPEAVION NBHVOL
---------- ---------- ----------
F-Wtss Concorede 6570
F-Glfs A320 3500
F-Gtmp A340 3000

insertion des données:
insert into AvionSing values('S-Ansi','A320',104500);
insert into AvionSing values('S-Avez','A320',104500);
insert into AvionSing values('S-Smile','A320',104500);
insert into AvionSing values('F-Gtmp','A340',204500);

SQL> select * from avionsing;
IMMAT TYPEAVION PRIXACHAT
---------- ---------- ----------
S-Ansi A320 104500
S-Avez A320 104500
S-Smile A330 104500
F-Gtmp A340 204500

a)Ecrivez l'instruction qui permet de donner tous les types d'avions que les deux compagnies exploitent.
select distinct typeavion from avionaf
union
select distinct typeavion from avionSing;
*****Résultat à afficher**********
TYPEAVION
----------
A320
A330
A340
Concorede

b)Ecrivez l'instruction qui permet de donner tous les types d'avions exploités par la compagnies 'AF' mais pas par 'Sing'

select typeavion from avionaf
where typeavion not in(
select distinct typeavion from avionSing);
*ou Plus long(le tous moins l'intersection)
select distinct typeavion from avionaf
minus(
(select distinct typeavion from avionSing)
intersect
(select distinct typeavion from avionaf));
*****Résultat à afficher**********
TYPEAVION
----------
Concorede
c)Ecrivez l'instruction qui permet de donner les couples(avions pilotes) en considérant les avions et les pilotes de la compagnie 'AF'.

select p.nom ,a.typeavion as "couple avion pilote"
from pilote p, avionaf a
group by p.nom, a.typeavion;

*****Résultat à afficher**********

NOM couple avi
-------------------- ----------
Amélie Sulpice A320
Amélie Sulpice A340
Amélie Sulpice Concorede
Paul Soutou A320
Paul Soutou A340
Paul Soutou Concorede
Thomace Sulpice A320
Thomace Sulpice A340
Thomace Sulpice Concorede

5) A la table pilote nous supposons l'ajout de la colonne chepil (chf pilote) pour indiquer qu'un pilote est responsable d'un groupe de pilotes.
a - ecrivez l'instruction qui permet l'ajout de cette colonne

alter table pilote add chefpil varchar2(5);
update pilote set COMPA='AF' where brevet ='PL-3';

b- ecrivez l'instruction qui permet de donner le brevet et le nom des pilotes sous la responsabilité de 'Alquié'

**** ajout des données dans la table pilote pour trouver des résulatats réels****
insert into pilote values ('PL-5', 'Ali', 400,'AF','PL-2');
insert into pilote values ('PL-6', 'Salah', 400,'Sing','PL-2');
insert into pilote values ('PL-7', 'Thomas Harisson', 200,'Sing','PL-4');

SQL> select * from pilote;

BREVE NOM NBHVOL COMPA CHEFP
----- -------------------- ---------- ----- -----
PL-1 Amélie Sulpice 450 AF PL-4
PL-2 Thomace Sulpice 900 Sing PL-2
PL-3 Paul Soutou 1000 AF PL-4
PL-4 Alquié 400 AF PL-2
PL-6 Salah 400 Sing PL-2
PL-7 Thomas Harisson 200 Sing PL-4
PL-5 Ali 400 AF PL-4

*** reqete simple ***
select brevet, nom
from pilote
where chefpil = (select brevet
from pilote
where nom= 'Alquié');

select brevet, nom
from pilote
where chefpil = (select brevet
from pilote
where nom= 'Alquié');

*** requete auto-jointure***
Select p2.brevet, p2.nom
from pilote p1,pilote p2
where p1.brevet=p2.chefpil AND p1.nom='Alquié';

*****Résultat à afficher*****
BREVE NOM
----- ------------------
PL-1 Amélie Sulpice
PL-2 Thomace Sulpice
PL-3 Paul Soutou
PL-7 Thomas Harisson

6- Ecrivez l'instruction qui permet de donner la somme des heures de vol des pilotes placés sous la responsabité des chefs pilotes de la compagnie de nom 'Air France'.


**** requete autojointure****
select sum(nbhvol)
from pilote
where chefpil in (select chefpil
from pilote
where compa= 'AF');


***** requete imbriqué****
select sum(nbhvol)
from pilote
where chefpil in
(select brevet
from pilote
where compa='AF' and brevet in ( select chefpil
from pilote ));

***** modificatio du tableau*****
insert into pilote values ('PL-8','xyz',860,'AF','PL-5');

BREVE NOM NBHVOL COMPA CHEFP
----- -------------------- ---------- ----- -----
PL-1 Amélie Sulpice 450 AF PL-4
PL-2 Thomace Sulpice 900 Sing PL-4
PL-3 Paul Soutou 1000 AF PL-4
PL-4 Alquié 400 AF PL-4
PL-6 Salah 400 Sing PL-2
PL-7 Thomas Harisson 200 Sing PL-4
PL-5 Ali 400 AF PL-4
PL-8 xyz 860 AF PL-5

**** résultat affiché*****
SUM(NBHVOL)
-----------
4210
7- Ecrivez l'instruction qui peremt de donner les noms des compagnies qui n'ont des pilotes.

***** ajout d'une enregistrement dans la table compagnie
insert into compagnie values ( 'Tn',10,'Tunis','Tunis','TunisAir');

select comp
from compagnie
minus (select compa from pilote);

*** résultat affiché****
COMP
-----
Tn