LYCEE SALEM BEN HMIDA AKOUDA 4 ème SCIENCES DE L’INFORMATIQUE BASES DE DONNEES Série de révision pour le deuxième trimestre Exercice 1 : 1) Donner la définition d’une base de données. 2) Depuis l’apparition des bases données, quatre modèles se sont succédés pour permettre la structuration des données : - Le modèle hiérarchique. - le modèle réseau. - le modèle relationnel. - le modèle objet. Expliquer brièvement chaque modèle. 3) Donner la définition d’une clé primaire d’une table. 4) Donner la définition d’un système de gestion de bases de données. Citer deux exemples. 5) Quels sont les types de relations entre les tables. 6) La différence entre l’authentification et la confidentialité d’une base de données. 7) Expliquer les notions suivantes au point de vue sécurisation des données : - Intégrité - Disponibilité - Traçabilité 8) Expliquer d’une façon générale la notion de cryptographie. 9) Quel est le rôle d’un administrateur d’une base de données? 10) Un nom d’utilisateur et un mot de passe sont suffisants pour accéder à la base de données ? 11) Expliquer la différence entre la disponibilité d’un système et sa tolérance aux pannes. 12) Toute application informatique est constituée de deux composantes principales : - une partie visible aux utilisateurs (interface) - une partie cachée (code) Expliquer le rôle de chaque partie. 13) Une application développée autour d’une base de données peut être exploitée selon trois modes : - Mode mono poste - Mode client / serveur - Mode Internet Expliquer les trois modes. 14) Quelle est la différence entre un site web dynamique et un site web statique? Enseignant : Khédhiri Adel 1 [email protected] LYCEE SALEM BEN HMIDA AKOUDA 4 ème SCIENCES DE L’INFORMATIQUE BASES DE DONNEES Exercice 2 : Soit le schéma relationnel de base de données suivant : HOTEL (NumHôtel, NomHôtel, CP, Ville) CHAMBRE (NumHôtel#, NumChambre, Type, Prix) RESERVATION (NumHôtel#, NumClient#, DateDébut, DateFin, NumChambre#) CLIENT (NumClient, NomClient, CP, Ville, Pays) Ecrire les commandes SQL permettant de : 1) 2) 3) 4) 5) 6) 7) 8) 9) 10) Créer les tables de la base de données. Afficher la liste des clients et les prix de leurs chambres réservées. Calculer le nombre de clients habitant aux Etats-Unis. Donner la liste des chambres libres à l'hôtel "Ours blanc". Calculer le prix moyen des chambres de l’hôtel "Ours blanc". Donner le prix et le type des chambres proposées à l'hôtel "Royal". Augmenter le prix des chambres de 10 %. Calculer le montant à payer par les clients séjournant à l'hôtel "Royal". Donner le nombre de clients de chaque hôtel classés par pays de résidence. Calculer le prix moyen par pays de résidence des factures payées par les clients. Exercice 3 : Soit le modèle relationnel suivant relatif à la gestion des notes annuelles d'une promotion d'étudiants : ETUDIANT(N°Etudiant, Nom, Prénom) MATIERE(CodeMat, LibelléMat, CoeffMat) EVALUER(N°Etudiant#, CodeMat#, Date, Note) Ecrire les commandes SQL permettant de répondre aux questions suivantes : 1) 2) 3) 4) 5) 6) 7) 8) 9) 10) 11) 12) 13) 14) Quel est le nombre total d'étudiants ? Quelles sont, parmi l'ensemble des notes, la note la plus haute et la note la plus basse ? Quelles sont les moyennes de chaque étudiant dans chacune des matières ? Quelles sont les moyennes par matière ? Quelle est la moyenne générale de chaque étudiant ? Quelle est la moyenne générale de la promotion ? Quels sont les étudiants qui ont une moyenne générale supérieure ou égale à la moyenne générale de la promotion ? Ajouter la table Université tel que Université(Codeuni,Nomuni,adresseuni) et un étudiant suit son cours dans une seule université. Ajouter le champ Responsable de type chaîne de 50 caractères au maximum à la table université. Modifier le type du champ responsable en chaîne de 20 caractères au maximum. Insérer les enregistrements suivants <200,’Faculté des sciences’, ‘Monastir’,’Ali Ben Saleh’) et <201,’Faculté des lettres’, ‘Sousse’,’Mhomed Ben Saleh’) Dispenser l’étudiant n°1523 de l’examen du «Réseaux » Vider la table Université. Supprimer la table Université. Enseignant : Khédhiri Adel 2 [email protected] LYCEE SALEM BEN HMIDA AKOUDA 4 ème SCIENCES DE L’INFORMATIQUE BASES DE DONNEES Exercice 4 : Soit le schéma relationnel de base de données suivant : ARTICLES (NOART, LIBELLE, STOCK, PRIXINVENT) FOURNISSEURS (NOFOUR, NOMFOUR, ADRFOUR, VILLEFOUR) ACHETER (NOFOUR#, NOART#, PRIXACHAT, DELAI) Ecrire les commandes SQL permettant de répondre aux questions suivantes : 1) 2) 3) 4) 5) 6) 7) 8) 9) 10) 11) 12) 13) 14) 15) 16) 17) 18) 19) 20) Numéros et libellés des articles dont le stock est inférieur à 10 ? Liste des articles dont le prix d'inventaire est compris entre 100 et 300 ? Liste des fournisseurs dont on ne connaît pas l'adresse ? Liste des fournisseurs dont le nom commence par "STE" ? Noms et adresses des fournisseurs qui proposent des articles pour lesquels le délai d'approvisionnement est supérieur à 20 jours ? Nombre d'articles référencés ? Valeur du stock ? Numéros et libellés des articles triés dans l'ordre décroissant des stocks ? Liste pour chaque article (numéro et libellé) du prix d'achat maximum, minimum et moyen ? Délai moyen pour chaque fournisseur proposant au moins 2 articles ? Prix minimum de chaque article avec le fournisseur correspondant ? Fournisseurs auprès desquels on ne s'approvisionne plus et qu'il faudrait donc peut-être supprimer de la table Fournisseurs. Quels sont les fournisseurs qui fournissent les 2 articles 100 et 106, et à quel prix ? Listes des articles pouvant être achetés chez plusieurs fournisseurs, avec le prix d'achat et le délai correspondants ? Quels sont le ou les fournisseurs qui fournissent le plus de produits ? Créer l’utilisateur « Ali » avec le mot de passe « aliopi78 » Ali peut il accéder à la base de données ? Accorder à Ali les droits UPDATE et INSERT sur la table ARTICLES. Ali peut t-il accorder des droits à d’autres utilisateurs ? Comment retirer tous les droits à tous les utilisateurs ? Enseignant : Khédhiri Adel 3 [email protected] LYCEE SALEM BEN HMIDA AKOUDA 4 ème SCIENCES DE L’INFORMATIQUE BASES DE DONNEES Exercice 5 : Soit les schéma relationnel suivant : Fournisseur(F, FNOM, STATUT,VILLE) Produit(P, PNOM, COULEUR, POIDS, VILLE) Produitfourni(F#, P#, QTE) Travail demandé : Répondre aux questions suivantes en donnant les commandes SQL correspondantes. 1- Créer la table Fournisseur représentant les fournisseurs. Cette table contient une clé primaire F constituée de 6 caractères, un nom de fournisseur FNOM (20 caractères), une valeur de STATUT (entier) et un nom de VILLE (15 caractères). 2- Créer la table Produit représentant les produits. Cette table contient une clé primaire P constituée de 6 caractères, un nom de produit PNOM (20 caractères), une couleur (6 caractères), un POIDS (entier) et une VILLE (15 caractères). POIDS doit être >0. 3 - Créer la table ProduitFourni représentant les produits fournis. Cette table contient deux clés étrangères P et F qui sont forcément non nulles. Le couple P et F forme la clé primaire de cette table. De plus, on stocke dans cette table un attribut QTE de type entier et qui représente la quantité de produits identifiés par P qui ont été fournis par le fournisseur identifié par F. QTE doit être >=0. 4 - Vous vous rendez compte qu’il est nécessaire de stocker une information supplémentaire sur la date de livraison du produit fourni. Ajouter dans la table ProduitFourni un attribut FPDATE constitué de 6 caractères. 5 - Finalement le choix d’attribuer le type char à l’attribut FPDATE n’était pas judicieux.Modifier l’attribut FPDATE de la table ProduitFourni pour lui associer le type date. 6 - Créer la table Employé représentant les employés de la société. Cette table contient une clé primaire E constituée de 6 caractères, un nom ENOM (20 caractères), un prénom EPRENOM (20 caractères) et un grade EGRADE (15 caractères). 7 Insérer l’employé PIGNON François dans la table Employé. Cet employé est identifié par « 00ERT1 ». 8 - Modifier le grade de l’employé PIGNON François en le déclarant comme directeur. 9 - Finalement, la table Employé ne vous intéresse pas. Supprimer la table Employé. 10 - Changer la couleur du produit P2 en jaune, augmenter son poids de 5 et mettre sa ville “Inconnue” (NULL). 11 - Doubler le statut de tous les fournisseurs à Londres. 12 - Mettre la quantité égale à zéro pour tous les fournisseurs à Londres. 13 - Changer le numéro du fournisseur F2 en F9. 14 - Ajouter le produit P7 (nom ‘Boulon’, couleur ‘Grise’, poids 2, ville ‘Genève’) à la table Produit. 15 - Nous souhaitons supprimer tous les droits pour le public sur la table ProduitFourni. 16 - Attribuer le droit de sélection seulement sur cette même table à l'utilisateur visiteur. Enseignant : Khédhiri Adel 4 [email protected] LYCEE SALEM BEN HMIDA AKOUDA 4 ème SCIENCES DE L’INFORMATIQUE BASES DE DONNEES Exercice 6 : Soit le modèle relationnel suivant relatif à la gestion simplifiée des étapes duTour de France 97, dont une des étapes de type "contre la montre individuel" se déroula à Saint-Etienne : EQUIPE(CodeEquipe, NomEquipe, DirecteurSportif) COUREUR(NuméroCoureur, NomCoureur, CodeEquipe#, CodePays#) PAYS(CodePays, NomPays) TYPE_ETAPE(CodeType, LibelléType) ETAPE(NuméroEtape, DateEtape, VilleDép, VilleArr, NbKm, CodeType#) PARTICIPER(NuméroCoureur#, NuméroEtape#, TempsRéalisé) ATTRIBUER_BONIFICATION(NuméroEtape#, km, Rang, NbSecondes, NuméroCoureur#) Travail demandé : Répondre aux questions suivantes en donnant correspondantes. les commandes SQL 1 - Quelle est la composition de l'équipe Festina (Numéro, nom et pays des coureurs) ? 2 - Quel est le nombre de kilomètres total du Tour de France 97 ? 3 - Quel est le nombre de kilomètres total des étapes de type "Haute Montagne"? 4 - Quels sont les noms des coureurs qui n'ont pas obtenu de bonifications ? 5 - Quels sont les noms des coureurs qui ont participé à toutes les étapes ? 6 - Quel est le classement général des coureurs (nom, code équipe, code pays et temps des coureurs) à l'issue des 13 premières étapes sachant que les bonifications ont été intégrées dans les temps réalisés à chaque étape ? 7 - Quel est le classement par équipe à l'issue des 13 premières étapes (nom et temps des équipes) ? Exercice 7 : Examiner les extraits des tables suivants provenant d’une entreprise de location de voitures : Table Client Num_client 1 2 3 4 5 6 7 Nom_client Souguir Mrad Aoun Gaddes Kortas Essaghi Rgez Enseignant : Khédhiri Adel Prénom_clie nt Hamza Béchir Amine Abelwahheb Amine Abdelkarim Firas Adresse_client 20 rue de la liberté 45 rue Elbortoukal 36 place Barcelone 61 rue 7 Novembre 5 rue de la république 8 rue Enasr 3 rue Farhat Hached 5 CP 4000 2080 7000 5160 4022 4020 4070 Ville_client Entreprise SOUSSE Non ARIANA Non BIZERTE Oui EL JEM Oui AKOUDA Non KONDAR Oui MSAKEN Non [email protected] 4 ème SCIENCES DE L’INFORMATIQUE LYCEE SALEM BEN HMIDA AKOUDA BASES DE DONNEES Table Véhicule Immatriculation 1234 AA 62 1436 TO 62 3597 HA 62 6541 CG 62 9876 ZZ 62 Marque Renault Citroën Peugeot Citroën Peugeot Désignation Mégane Xantia 406 Xsara 106 Date_Achat 18/12/1997 08/12/1997 02/12/1997 26/12/1997 22/12/1997 Prix_HT 9 890 10 100 8 670 7 940 6 529 Code_Tarif E F D C B Num_client 3 1 7 4 2 3 6 3 5 1 Km_départ 145 259 33 214 658 330 357 756 701 1058 Km_retour 658 357 330 1058 756 701 421 798 825 1469 Table Location Date_Location 12/01/2002 12/01/2002 14/01/2002 16/01/2002 19/01/2002 21/01/2002 23/01/2002 27/01/2002 29/01/2002 31/01/2002 Immatriculation 1234 AA 62 9876 ZZ 62 6541 CG 62 1436 TO 62 1234 AA 62 6541 CG 62 9876 ZZ 62 1234 AA 62 6541 CG 62 1436 TO 62 Table Tarif Code_Tarif A B C D E F Tarif 2,10 3,05 3,95 4,10 4,40 5,00 Ecrire les commandes SQL permettant de : a) Créer la table location. b) Ajouter ce nouveau véhicule dans la base : Immatriculation Marque Désignation Date_Achat Prix_HT Code_Tarif 7570 RI 62 Renault Clio 06/03/2008 9 890 E c) Afficher les véhicules loués par le client "Souguir ". d) Afficher le tarif du véhicule loué par le client "Mrad". e) Afficher le kilométrage parcouru par le client "Kortas". f) Afficher le nombre de clients "Entreprise". g) Afficher le kilométrage parcouru par le véhicule "1234 AA 62". h) Afficher les véhicules (Immatriculation, Désignation, Date_Achat) de la marque "Peugeot". Enseignant : Khédhiri Adel 6 [email protected] LYCEE SALEM BEN HMIDA AKOUDA 4 ème SCIENCES DE L’INFORMATIQUE BASES DE DONNEES Exercice 8 : Soit la représentaion graphique d'une base de données d'une agence de location des voitures : Ecrire les commandes SQL permettant de répondre aux questions suivantes : 1. Créer les différentes tables. 2. Où est le véhicule numéro 02aa69 ? 3. Listes des véhicules disponibles à l'agence Lyon nord ? 4. Listes des véhicules de classe normale disponibles à l'agence Lyon nord? 5. Listes des véhicules déjà loués par Mr Martin. 6. Liste des préférences de Mr Dupont par ordre décroissant de note. 7. Prix à payer pour la location par le bon de numéro 1. 8. Nombre de fois ou le véhicule 01cc69 a été loué. 9. Nombre de km parcourus par le véhicule 01cc69. 10. Liste des véhicules les plus loués, avec km parcourus, classée par nombre de locations. 11. Nombre de kilomètres parcourus par le client nommé Martin. 12. Liste des clients fidèles ayant loués plus d'une fois. 13. Chiffre d'affaires des agences (total des sommes perçues et nombre de locations), classement décroissant. 14. Liste des véhicules ayant parcourus plus de 1000km depuis leur dernière révision. 15. Nombre de véhicules disponibles par classe à l'agence de Lyon Nord. 16. Pour détecter certaines erreurs de saisies, on désire connaître la liste des véhicules faisant l'objet de plusieurs locations simultanées. 17. Liste des véhicules qui n'ont jamais été loués. Enseignant : Khédhiri Adel 7 [email protected]