Transcript Base de données

TD 5
[Miage CFA L3]
15 mai 2014
Base de donn´
ees
On consid`ere le sch´ema de base de donn´ees suivant :
P roduit(Constructeur, M odele, T ype)
ORDI(M odele, V itesse, RAM, HD, CD, Ecran, P rix)
Imprimante(M odele, Couleur, T ype, P rix)
et son instance :
P roduits
Constructeur M odele
A
1001
A
1002
A
1003
B
1004
B
1006
B
3002
B
3004
C
1005
C
1007
D
1008
D
1009
D
1010
D
2001
D
2002
D
2003
D
3001
D
3003
E
2004
E
2008
F
2005
G
2006
G
2007
H
3005
I
3006
T ype
pc
pc
pc
pc
pc
printer
printer
pc
pc
pc
pc
pc
laptop
laptop
laptop
printer
printer
laptop
laptop
laptop
laptop
laptop
printer
printer
ORDIs
M odele V itesse RAM HD
1001
3
256
120
1002
2
256
120
1003
3.5
512
180
1004
3.5
1024
180
1005
3.5
256
160
1006
4.5
1024
200
1007
4.5
1024
300
1008
4
1024
160
1009
4.5
1024
180
1010
3.4
256
100
2001
2
256
60
2002
2.1
192
40
2003
2.1
1024
50
2004
3
256
60
2005
3.5
256
50
2006
2
128
45
2007
2.5
256
80
2008
2
256
60
M odele
3001
3002
3003
3004
3005
3006
Imprimantes
Couleur T ype
true
ink − jet
true
ink − jet
f alse
laser
true
laser
f alse
ink − jet
true
dry
1
CD
48x
48x
48x
56x
56x
56x
56x
56x
56x
56x
72x
48x
56x
48x
48x
56x
56x
56x
P rix
99
69
209
391
59
129
Ecran
17
17
16
16
14
17
19
14
14
17
14
15.4
15
15.5
15.4
17
17
17
P rix
795
699
999
1099
1099
1199
1249
1699
1299
700
1099
1298
1499
1399
1299
1099
1999
1199
Exercice 1 : alg`
ebre relationnelle
Exprimez en alg`ebre relationnelle les requˆetes suivantes :
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
Quels sont les portables (laptop en anglais) r´ef´erenc´es dans la relation Produits ?
Quelle est la liste des constructeurs ?
Quels sont les mod`eles des ordinateurs r´ef´erenc´es ?
Quels sont les ordinateurs ayant 256 Mo de RAM ?
Quels sont les ordinateurs dont le prix est inf´erieur `a 1100 Euros ?
Quels sont les portables avec un disque d’au moins 50 Go dont le prix ne d´epasse pas 1400 Euros ?
Quelles sont les imprimantes laser couleur ?
Quels sont les portables du constructeur D ayant au moins 256 Mo de RAM ?
Quels sont les ordinateurs du constructeur D ayant au moins 256 Mo de RAM ?
Quels sont les portables dont le prix est strictement inf´erieur `a 1200 Euros ?
Quels sont les constructeurs fournissant des imprimantes et des pc ?
Quels sont les constructeurs couvrant tous les types de produits ?
Quels sont les constructeurs ne fournissant que des PC ?
Quels sont les constructeurs ne fournissant qu’un type de materiel ?
Quels sont les constructeurs disposant de pc avec au moins 1024 Mo de RAM, un cd x56 et d’un portable
avec un ´ecran sup`erieur `
a 15 pouces et pouvant fournir une imprimante ´a moins de 300 Euros ?
Quelles sont les tailles de disques durs qui sont utilis´ees par au moins 2 ordinateurs ?
Quels sont les constructeurs qui vendent plus d’un ordinateur ayant une fr´equence d’au moins 3 Ghz ?
Quels sont les constructeurs vendant l’ordinateur le plus rapide ?
Quels sont les ordinateurs qui ont la mˆeme capacit´e RAM que le mod`ele 1001 ?
Quels sont les constructeurs qui ne vendent aucun portable ?
Quels sont les constructeurs qui proposent au moins deux mod`eles de portables ?
Quels sont les constructeurs vendant exactement trois types de PC ?
Sachant que pour faire de la PAO il faut au moins 1024 Mo de RAM, une vitesse de 3 Ghz ou 3.5 , 160 Go
minimum de hd ainsi qu’une imprimante couleur, quelle est le constructeur fournissant cette configuration
et `a quel prix ?
Exercice 2 : Cr´
eation et manipulation d’une base de donn´
ees
Nous allons cr´eer la base de donn´ees au-dessus. Nous rappelons que cette base est constitu´ee des relations
suivantes :
T ables
P roduits(Constructeur : caractere, M odele : entier, T ype : chaine)
ORDI(M odele : entier, V itesse : entier, RAM : entier, HD : reel, CD : chaine, Ecran : reel, P rix : reel)
Imprimantes(M odele : entier, Couleur : booleen, T ype : chaine, P rix : reel)
Contraintes
Les clef s donnees ci − dessus
P as de valeur nulle
ORDI[M odele] ⊆ P roduits[modele]
Imprimantes[M odele] ⊆ P roduits[modele]
dom(P roduits.T ype) = {laptop, pc, printer}
dom(ORDI.CD) = {6x, 8x}
dom(Imprimantes.T ype) = {dry, ink − jet, laser}
2
Connexion `
a votre base de donn´
ees Oracle
Pour manipuler votre base de donn´ees, vous devez utiliser l’interpr´eteur de commande d’PostgreSQL : psql. Vous trouverez
des informations sur cette commande, `
a l’adresse suivante :
http://www.postgresql.org/docs/9.3/static/app-psql.html
Manipulation des tables via le SGBD
Nous allons voir comment cr´eer une table, la modifier et la supprimer.
Les liens suivants montrent des tutoriels sur la syntaxe SQL :
http://www.w3schools.com/sql/default.asp
http://www.postgresql.org/docs/9.3/static/tutorial.html
http://www.postgresqltutorial.com/
http://www.tutorialspoint.com/postgresql/
Cr´
eation d’une table
La cr´eation d’une table s’effectue grˆ
ace `
a la commande create table. Celle-ci s’utilise de la fa¸con suivante :
create table nom table (attribut1 type1 , . . . , attributn typen );
Vous utiliserez les types de donn´ees integer, real et varchar(longueur max).
http://www.postgresql.org/docs/9.3/static/datatype.html
Cr´eez `
a l’aide de cette commande les tables de la base (sans clef ni contrainte). Tapez select ∗ f rom tab; pour obtenir
un descriptif du contenu courant de la base. Vous pouvez taper \d nom_table; pour obtenir le descritif d’une table.
Ins´erer les trois premiers tuples (voir ´enonc´e de TD) de la table produits `a l’aide de la commande :
insert into nom table values (val attribut1 , . . . , val attributn );
Nous rappelons que pour voir le contenu d’une table vous pouvez utiliser la requˆete : select ∗ from nom table;.
Changer le nom du constructeur du premier tuple `a l’aide de la commande :
update nom table set attribut = nouvelle valeur where condition;
La commande update instancie la valeur de attribut `a nouvelle valeur pour tous les tuples v´erifiant la condition.
Modification d’une table – Ajout de contraintes
` l’aide de la commande alter table, modifiez les tables que vous venez de cr´eer afin d’y indiquer les clefs primaires et
A
´etrang`eres, les droits `
a la valeur nulle, ainsi que les contraintes de domaine d’attribut.
Notez qu’une clef ne doit pas pouvoir prendre la valeur null.
Suppression d’une table
La suppression d’une table s’effectue grˆ
ace `
a la commande drop table nom table.
Supprimez toutes vos tables en commen¸cant par produit. Que se passe-t-il ? Pourquoi ?
Supprimer les n-uplets du constructeur D de la table produits en utilisant la commande :
delete from nom table where condition
Que constatez-vous ? Pourquoi ?
Supprimez tous les tuples que vous avez ins´er´es dans les tables (sans supprimer les tables).
R´ecup´erez le fichier remplissage.sql sur le compte de votre charg´e de TD. Ce fichier contient les commandes de remplissage des tables de la base de donn´ees. Lisez et chargez ce fichier.
3
Exercice 3
Consid´erons l’instance suivante de bases de donn´ees :
R
A
1
2
6
4
B
2
2
7
7
S
A
B
T
A
2
4
4
B
3
7
4
U
A
1
2
C
2
2
Donnez le r´esultat de chacune des requˆetes suivantes :
1. [R] ∪ [S]
21. σA≤1∧B≤1 [R]
2. [T ] ∪ [R]
22. σA≤1∨B≤1 [R]
3. [R] ∪ [T ]
23. σA6=B [U ]
4. [R] ∪ [U ]
24. [R] 1 [U ]
5. [R] ∪ [R]
25. [R] 1 [S]
6. [U ] − [T ]
26. [U ] 1 [U ]
7. [R] − [S]
27. [R] 1 [T ]
8. [S] − [R]
28. ρA→C,B→D [R]
9. [S] − [T ]
29. ρB→C [R]
10. [R] − [T ]
30. [ρB→C [R]] ∪ [U ]
11. [T ] − [R]
31. [R] 1 [ρA→B,C→A [U ]]
12. [R] − [R]
32. [R] 1 [ρA→D [U ]]
13. ΠA,B [R]
14. ΠA [R]
19. σA<1 [R]
33. Voici des instances de relations R1 et R2 :
R2 A B D
R1 A B C
1 2 3
1 2 4
2 2 4
6 7 4
6 7 5
1 7 3
4 7 6
Calculer [R1] 1 [R2].
20. σA=B [R]
34. [[T ] ∪ [R]] − [[[T ] − [R]] ∪ [[R] − [T ]]].
15. ΠC [R]
16. ΠA [S]
17. ΠC [U ]
18. σA≥2 [R]
4