Base de-donn-es-bac-si-95ae18

Base de Données

Classe : 4ème SIAnnée scolaire : 2010-2011

Résumé Devoirs de contrôles et de synthèses corrigés

*****Bonne révision*****

Enseignant : Ismail wael 4ème SI

Résumé Base de Données

Définitions :

Une donnée : est une information qui peut effectuer des traitements

Une information : c’est une donnée regroupée avec d’autres liées à un contexte donnée.

Entité : c’est la représentation d’un objet du monde réel ayant une existence propre. (Exp : Etudiant, Livre, classe…)

Propriété (attribut) : représente les caractéristiques les propriétés de l’entité.

Association : c’est un outil permettant de représenter les liens entre les entités.

Base de Données : est un ensemble de données non redondantes, structurées et persistantes (non volatiles) relatives à un ou plusieurs domaines du monde réel.

Intérêts de l’utilisation d’une BD :-Centralisation-Indépendance entre données et programme-Intégrité de données-Partage de données (concurrence d’accès)

Intégrité de données : Ensemble de règles appelées contraintes d’intégrité qui décrivent les propriétés qui doivent être vérifié par les données pour être enregistrées. Elle permet d’assurer la cohérence des données.

Modèles de BD : -Hiérarchique-Réseau-Modèle Relationnel : le principe consiste à représenter aussi bien les objets que les liens à l’aide d’une structure appelée table.

Système de Gestion de Base de Données ( SGBD ) : c’est un logiciel qui permet de créer, de gérer et d’interroger efficacement une BD.(Pour dialoguer avec le SGBD il faut utiliser le langage SQL(Structured Query Language))

Les fonctions de bases d’un SGBD :-Définition des données-Manipulation des données-Intégrité des données-la gestion des accès concurrents-la confidentialité-la sécurité de fonctionnement

Table : une table est un ensemble de données relatives à un même sujet(ou entité) et structurées sous forme de tableau.Colonne(ou champ ou propriété) :Dans une table une colonne est une propriété élémentaire de l’objet décrit par cette table.Ligne (n-uplet ou enregistrement) :Une ligne correspond à un objet représenté par la table.

Clé primaire (identifiant=primary key) : c’est un identificateur unique pour la table qui correspond à une colonne ou un ensemble de colonnes.

Clé étrangère =référence =contrainte d’intégrité référentielle = foreign keyUn lien entre deux tables A et B est représenté par l’ajout dans la table B d’une nouvelle colonne correspondant à la clé primaire de la table A. cette nouvelle colonne est appelée Clé étrangère.

Contraintes d’intégrité : Ensemble de règles appliquées à une colonne ou une table et qui doit être toujours vérifiée.

(les principales contraintes d’intégrité sont : contraintes de domaines, contraintes d’intégrité de table et contrainte d’intégrité référentielle)

Démarche de détermination de la structure d’une BD : - Délimiter le domaine- Déterminer les colonnes

Liste des colonnesNom

ColonneDescription Types de

donnéesTaille Obligatoire Valeur par

défautValeur autorisé

Sujet

….. ………. …… ….. …….. ……… …… ……..-Déterminer les tables

Liste des tables

Nom table Description Sujet

Base De Données


- Affecter les colonnes aux tables- Déterminer les clés primaires- Déterminer les liens entre les tables- Analyser et affiner la structure de la BDMode assisté : permet de créer les éléments de la BD à travers des assistants graphiques

Formulaire : permet aux utilisateurs d’interagir avec l’application à travers des champs de type texte, liste..plusieurs opérations offertes par le formulaire: constatation, insertion, modification et suppression des données

les Etats : ils permettent aux utilisateurs d’obtenir des données afin de les imprimer ou les stocker sous une forme quelconque

Mécanismes de sécurité :-L’authentification -Les droits et les privilèges-Les logs ou traces -Tolérance aux pannes-Sauvegarde et restauration -Mécanisme transactionnels

NB :

Exemples pour les relations :

Relation un à plusieurs

Un élève appartient à une seule classe. Dans une classe on trouve plusieurs élèves

ELEVE ∞ 1 CLASSE

Donc CLASSE table mère et ELEVE table fille. Comme CLASSE est mère alors sa clé primaire migre à la table fille qui est ELEVE et sera une clé étrangère

Description textuelle :

ELEVE (num_inscription, nom, prenom, date_naissance, adresse, id_classe#) CLASSE(id_classe, nom_classe)

Description graphique :

Relation plusieurs à plusieurs

Un athlète peut participer à plusieurs courses. Dans une course on trouve plusieurs athlètes.

Base De Données


Dans ce cas la relation participer se transforme en un table identifié par deux clés primaires qui sont les clés primaires de la table athlète et la table Courses( les deux clés seront primaires et étrangères au même temps dans la nouvelle table)


athlete (anum,anom,aage)

participation (cnum#,anum #)

course (cnum,cnom)


Création des tables en mode commande (SQL)

Exemple :

On va créer les tables décrites par la description textuelle ci-dessous, sachant que le titre et la date fin d’abonnement sont obligatoires et que le nombre maximal d’emprunt doit être ≥0.Film(id_film, titre)Client(id_client, nom, date_fin_abonnement, nbmaxemprunt)Cassette(id_cassette, id_film #) Emprunt (id_client #, id_cassette #, date_emprunt)

CREATE TABLE Film(id_fim INT(3) PRIMARY KEY,titre VARCHAR(10) NOT NULL,);

CREATE TABLE Client(id_client INT(3) PRIMARY KEY ,nom VARCHAR(10) NOT NULL ,date_fin_abonnement date NOT NULL , nbmaxemprunt CHECK(nbmaxemprunt >=0)) ;

CREATE TABLE Cassette ( id_cassette INT(3) ON DELETE CASCADE,id_client INT(3) ON DELETE CASCADE,date_emprunt date,CONSTRAINT pk PRIMARY KEY(id_cassette, id_client),CONSTRAINT fk_cas FOREIGN KEY(id_cassette) REFERENCES Cassette ,CONSTRAINT fk_cl FOREIGN KEY(id_client) REFERENCES Client ) ;

Base De Données


Modifier la structure d’une table en mode commande (SQL)

Rôle Commande Exemple

Ajout d’une colonne à la table

ALTER TABLE nom_table ADD COLUMN définition_colonne ;

ALTER TABLE client ADD COLUMN email VARCHAR(80);

Ajout d’une contrainte à la

table

ALTER TABLE nom_table ADD CONSTRAINT définition_contrainte ;

ALTER TABLE article ADD CONSTRAINT PRIMARY KEY (code_article);

Modification des propriétés d’une

colonne

ALTER TABLE nom_table Modify définition_colonne ;

On veut élargir la taille de la colonne « email » :ALTER TABLE client MODIFY email VARCHAR(100);

Suppression d’une colonne de

la table

ALTER TABLE nom_table DROP COLUMN nom_colonne ;

ALTER TABLE client DROP COLUMN email;

Suppression d’une contrainte

de la table

ALTER TABLE nom_table DROP CONSTRAINT nom_contrainte ;

Supprimer la clé primaire de la table article :

ALTER TABLE article DROP CONSTRAINTE PRIMARY KEY;

Désactiver une contrainte

ALTER TABLE nom_table DISABLE nom_contraint ;

ALTER TABLE article DISABLE CONSTRAINT PRIMARY KEY;

Réactiver une contrainte

ALTER TABLE nom_table ENABLE nom_contrainte ;

ALTER TABLE article ENABLE CONSTRAINT PRIMARY KEY;

Suppression d’une table en mode commande (SQL)

DROP TABLE nom_table;

Manipulation des données en mode commande

Mise à jour de données

Insertion des lignes Suppression des lignes Modification des lignes

INSERT INTO nom_table(liste_nomcolonne) VALUES(liste_valeurs) ;

Exp :INSERT INTO Athlete

DELETE FROM nom_tableWHERE condition ;

Exp :DELETE FROM Athlete

UPDATE nom_table SET nom_colonne1=expression1, nom_colonne2=expression2,… WHERE condition ;

Exp :

Base De Données


(num,nom,age) VALUES (123, ‘GUAMOUDI’,45) ;

WHERE age>50 ; UPDATE Athlete SET age=32WHERE num=100 ;

Recherche des données (requêtes)

Recherche de colonne à partir d’une table (projection)

SELECT liste_nom_colonne From nom_table ;

SELECT * from nom_table ;

SELECT DISTINCT(nom_colonne) From nom_table ;

RQ : Par défaut les colonnes de la table résultat portent les mêmes noms que ceux de la table de départ ; il est possible de donner aux colonnes de la table résultat des noms différents de ceux de la table d’origine. On doit dans ce cas donner un entête de colonne juste après son nom donné par le paramètre liste_nom_colonne. Cette entête est appelé Alias.

Recherche de ligne à partir d’une table : sélection

SELECT */liste_nom_colonne FROM nom_table WHERE condition ;

Dans la condition on peut utiliser :

Les opérateurs de comparaison (=,>, <,>=, <=, et <>), l’opérateur BETWEEN, l’opérateur IN, l’opérateur IS NULL, l’opérateur IS NOT NULL, l’opérateur LIKE et les opérateurs logiques AND, OR et NOT.

Recherche de données à partir du plusieurs tables : jointure

On parle de jointure si la recherche est relative à deux tables ayant au moins une colonne en commun.la condition de jointure doit porter sur ces colonnes communes. .

Recherche de données avec tri

SELECT liste_nom_colonne FROM nom table ORDER BY nom_colonne ASC/DESC ;

ASC : Dans l’ordre ascendant.DESC : Dans l’ordre descendant. Par défaut le tri est ascendant.

Utilisation des fonctions de calculs

Base De Données


dans les opération de recherche (fonctions Agrégat)

COUNT

Compter le nombre de lignes du résultat obtenu par la commande SELECT

SUM

Faire la somme des valeurs d’une colonne dont le type est numérique

MIN Déterminer la valeur minimale d’une colonne

MAX Déterminer la valeur maximale d’une colonne

AVG

Déterminer la moyenne des valeurs numériques d’une colonne

Exercice:

Soit la description textuelle suivante :

Client(numcli, nom, prenom, adresse,code_post, ville, tel)

Produit(numprod, designation, prix_unit, qte_stock)

Commande(numcom, numcli#, idvendeur#, date_com, qte, numprod#)

Vendeur(idvendeur, nomvendeur, qualite ,salaire)

1-Donner la liste des prix TTC des produit, sachant que prix TTc= prix unitaire +0.1 *prix unitaire.

SELECT prix_unit+prix_unit*0.1 ‘Prix TTC’ FROM Produit ;

2-Donner la liste des noms et prénoms des clients

SELECT nom, prenom FROM Client ;

3-Donner la liste des clients habitant à Tunis

SELECT * FROM Client WHERE ville=’Tunis’ ;

Base De Données


4- Donner les numéros, noms et prénoms de tous les clients. Au moment de l’affichage les entêtes des colonnes doivent être respectivement ‘ Numéro du client’, ‘Nom du client’ et ‘Prénom du client’

SELECT numcli ‘Numéro du client’, nom ‘Nom du client’, prenom ‘Prénom du client’

FROM client ;

5- Donner la liste des commandes qui ont des quantités au moins égale à trois

SELECT * FROM Commande WHERE qte>=3 ;

6-Donner les désignations des produits qui ont un prix comprix entre 1500 et 3500

SELECT designation FROM Produit WHERE prix_unit BETWEEN 1500 AND 3500 ;

7-Donner les numéros des commandes qui ont des quantités indéterminées

SELECT numcom FROM Commande WHERE qte IS NULL ;

8- Donner La liste des noms et prénoms des clients qui habitent une ville dont Le nom se termine par AA.

SELECT nom, prenom FROM Client WHERE ville LIKE ’%AA’ ;

9-Donner la liste des clients dont les villes commencent par Menzel.

SELECT * FROM Client WHERE ville LIKE ‘Menzel%’ ;

10- Donner les prénoms des clients dont le nom est Ghdir, Hbibi, Aloui, Malki ou Hilali.

SELECT prenom FROM Client WHERE nom IN (‘Ghdir’,’ Hbibi’,’ Aloui’,’ Malki’,’ Hilali’) ;

11- Donner les numéros et les prénoms des clients dont la 3ème lettre de leurs prénoms est h.

SELECT numcli, prenom FROM Client WHERE prenom LIKE ‘__h%’;

12- Donner La moyenne des prix des produits

SELECT AVG(prix_unit) FROM Produit ;

13- Donner le nombre total des commandes

SELECT COUNT(numcom) FROM Commande ;

14- Donner le nombre des clients ayant passé une commande

SELECT COUNT(DISTINCT(numcli)) FROM Commande ;

15- Donner la liste des numéros des commandes avec les noms des clients. (Requête de jointure)

SELECT numcom, nomcli FROM Commande, Client WHERE Commande.numcli=Client.numcli ;

Ou bien(en utilisant les alias)

Base De Données


SELECT numcom, nomcli FROM Commande C, Client CL WHERE C.numcli=CL.numcli ;

16- Donner les numéros, les noms et prénoms des clients qui habitent à Mhamdia et dont leur prénom commence par A.

SELECT numcli,nom, prenom WHERE ville=’Mhamdia’ AND prenom LIKE ‘A%’ ;

17- Donner la liste des clients ordonnées par ordre alphabétique des noms ;

SELECT * FROM Client ORDER BY nom ;

18- Supprimer les produits qui ont une quantité de stock égale à zéro

DELETE FROM Produit WHERE qte_stock=0 ;

19- Augmenter Le salaire du vendeur identifié par 125 en lui ajoutant 1500.

UPDATE Vendeur SET salaire=salaire+1500 WHERE idvendeur=125 ;

20- Afficher la liste des vendeurs dont le nom commence par S et dont la qualité est déterminée, ordonnée dans l’ordre décroissant des salaires.

SELECT * FROM Vendeur WHERE nomvendeur LIKE ‘S%’ AND qualite IS NOT NULL ORDER BY salaire DESC ;

Bonne révision et très Bon travail pour le concours…

Base De Données


Devoir de contrôle N°1

Matière : Base de DonnéesClasse : 4ème SIDocuments : Non autorisés

Date : 14/11/2007 Durée : 2h Nb. Pages : 2 Barème : 20

Exercice N°1(3 pts)

Définir les termes suivants:

- Base de Données

- Association

- SGBD

- Modèle Réseau

- LMD

- Table


1- Citer deux inconvénients de l’organisation en papiers et de l’organisation en fichiers.

2- Donner un schéma clair représentant l’architecture ANSI/SPARC.

3- Répondre par vrai ou faux et justifier votre réponse.

a- Une base de données garde les informations d’une façon volatile.

b- Une clé primaire est l’identificateur unique pour une table.

c- Si une entité A se réfère à une entité B ceci signifie que A est une entité mère et B est une entité fille.

d- Une colonne est une occurrence du sujet représenté par la table.

e- Le SGBD assure la redondance des données.

f- Le SGBD peut restaurer la base de données suite à une panne.

Base De Données


g- Une contrainte de domaine vérifie l’existence d’une clé primaire dans chaque table.

h- L’architecture ANSI/SPARC définit trois niveaux d’abstraction pour un SGBD.

Exercice N°3 (2 pts)

Un Système de Gestion de base de donnés offre un ensemble de fonctions pour gérer une

base de données. Citer quatre fonctions et les expliquer brièvement.


On s’intéresse à la gestion des propriétaires d’automobile.

Un propriétaire a un numéro (num_prop) et un nom (nom_prop).

Une automobile est identifiée par son numéro d’immatriculation (num_mat), elle possède

un nom (nom_auto) et une couleur.

Chaque automobile appartient à une marque.

Une marque est présentée par num_marque et nom_marque.

Un propriétaire peut posséder plusieurs automobiles.

1- Dégager les entités.

2- Pour chaque entité donner ses attributs et préciser la clé primaire.

3- Préciser les liens entre les entités et indiquer à chaque fois l’entité Mère et l’entité

fille.

4- Représenter la structure de la Base de Données sous forme textuelle.

5- Représenter la structure de la Base de Données sous forme graphique.

Bon travail

Base De Données


Correction du Devoir de contrôle N° 1

Exercice N°1 :

- Base de Données : une BD est un ensemble de données structurées, enregistrées sur

un support physique de façon permanente et accessibles de façon sélective par

plusieurs utilisateurs.

- Une association : c’est un lien entre entités.

- SGBD : Système de Gestion de Bases de données est un logiciel permettant de gérer,

créer et manipuler une BD.

- Modèle Réseau : Une BD réseau se présente comme un graphe. Tous les types des

liens sont possibles (n-m).

- LMD : Langage de Manipulation de données utilisé par le SGBD pour manipuler les

bases de données.

- Table : Ensemble de données relatives à un même sujet, elle est formée d’un ensemble

de lignes (valeurs) et un ensemble de colonnes (attributs).

Exercice N°2 :

1-

L’organisation en papier possède plusieurs inconvénients, parmi les quels on cite :

difficulté d’accès et contrainte de volume…

L’organisation en fichier présente un ensemble de limites, parmi les quels on cite :

manque de sécurité et lourdeur d’accès…

2-

L’architecture ANSI/SPARC définit trois niveaux d’abstraction pour un SGBD :

niveau externe, niveau conceptuel et niveau interne. Le schéma ci dessous représente cette

architecture :

Base De Données


3-

a- Faux. Une BD garde les données d’une façon permanente (permanente ≠ volatile).b- Vrai.c- Faux. Si une entité A se réfère à une entité B ceci signifie que B est une entité mère et

A entité fille.d- Faux. Une ligne est l’occurrence du sujet représenté par la table.e- Faux. Le SGBD assure la cohérence des données.f- Vrai.g- Faux. Une contrainte du domaine permet d’assurer si une colonne est obligatoire ou

non et les valeurs autorisées qui peuvent être prise h- Vrai.

Exercice N°3 :

Un SGBD offre un ensemble de fonctions pour gérer une base de données. Parmi ces

fonctions on cite les suivantes :

- Définition des données : le SGBD offre la possibilité de définir et décrire les données.

Pour la définition des données le langage LDD est utilisé.

Base De Données

Schéma externe Schéma externe

Schéma externe

Schéma

Conceptuel

Schéma interne

Monde

Réel

Monde

Réel

BDBD

SGBD

Niveau

Externe

Niveau

Conceptuel

Niveau

Interne


- Manipulation des données : le SGBD doit offrir à l’utilisateur la possibilité de

manipuler des données (insertion, suppression, consultation..).LMD est le langage

utilisé pour manipuler des données.

- Sécurité de fonctionnement : à la suite d’une panne le SGBD offre la possibilité de

restaurer la BD.

Exercice N°4 :

1- Les entités sont : Automobile, Propriétaire et Marque.

2-

Entités Attributs Clés primaires

Automobile num_mat, couleur,

nom_auto

num_mat

Propriétaire num_prop, nom_prop num_prop

Marque num_marque, nom_marque num_marque

3-

- Propriétaire Posséder Automobile

Dans cette relation propriétaire peut posséder plusieurs automobiles et une

automobile à un seul propriétaire donc : Propriétaire est une entité mère et

Automobile une entité fille.

- Automobile Appartenir Marque

Dans cette relation Automobile appartient à une seule marque et une marque peut

contenir plusieurs automobiles donc : Marque est l’entité mère et Automobile une

entité fille.

4- Automobile (num_mat, nom_auto, couleur , num_prop #, num_marquer #)

Propriétaire (num_prop, nom_prop)

Marque (num_marque, nom_marque)

5-

Base De Données

Lien

Lien

Propriétaire

num_prop

nom_prop

Automobile

num_matnom_autocouleurnum_prop

num_marque

Marque

num_marque

nom_marque

11

∞

∞


Devoir de Synthèse N°1


Date : 06/12/2007 Durée : 2h Nb. Pages : 3 Barème : 20

NB : La propreté et la clarté des réponses entrent dans l’évaluation de la copie !

Lisez bien les questions !

Exercice N°1 : (2pts)

Définir les termes suivants :

- Une donnée.

- Une base de données.

- n-uplet.

- contrainte d’intégrité.


1- Citer deux intérêts de l’utilisation d’une base de données.

2- Donner les trois types d’utilisateurs d’une base de données.

3- Arranger les phrases suivantes pour obtenir la démarche ordonnée de la structuration d’une base de données.

A B C D E F G

Déterminer les clés

primaires

Analyser la structure de

la BD

Déterminer les tables

Délimiter le/ les

domaines

Affecter les colonnes aux tables

Déterminer les liens entre

les tables

Déterminer les

colonnes

Base De Données

Enseignante : Mlle DOUGUI Sameh 4ème SI

4- Répondre par vrai ou faux aux phrases suivantes (si faux alors les rectifier)

a- Une BD est un ensemble de données redondantes, structurées et persistantes relatives à un ou plusieurs domines du monde réel.

b- Pour communiquer avec un SGBD on utilise le langage SQL.

c- Le niveau interne de l’architecture ANSI/SPARC définit les vues de l’utilisateur.

d- Une clé primaire est un identificateur de la table qui correspond toujours à une seule colonne.

e- Le processus de verrouillage des données est utilisé par le SGBD pour gérer les accès concurrents.


Transformer la présentation graphique ci dessous en présentation textuelle.

Exercice N° 4: (11 pts)

Enoncé :

Nous voulons concevoir une BD pour gérer un groupe de recherche.

Le groupe est constitué de chercheurs dont on connaît pour chacun, le nom (nom-ch),

le prénom (prenom-ch), le diplôme (dip-ch), l’adresse (ad-ch) et le téléphone (tel-ch). Chaque

chercheur possède un diplôme. Un diplôme est présenté par son nom (nom-dip) et par un code

(code-dip).

Les chercheurs rédigent des articles caractérisés chacun par un code (code-art), un titre

(titre-article) et une date de rédaction. Un article peut être rédigé par un seul chercheur.

Base De Données


Un article appartient à plusieurs domaines de recherches, et dans un domaine de

recherche on peut trouver un et un seul article. Chaque domaine de recherche possède un

code de domaine (code-DR) et un nom (nom-DR).

Le groupe de recherche anime également des séminaires. Pour chaque séminaire on

détient le titre (titre-sem), le lieu (lieu-sem), la date (date-sem) et on l’identifie par un numéro

(num-sem). Un séminaire peut avoir un seul responsable qui est un chercheur, et un chercheur

peut être un responsable sur plusieurs séminaires.

Différents participants participent au séminaire. Chaque participant possède les

informations suivantes : numéro (num-participant), nom, prénom et l’organisme où il

travaille. Un organisme est désigné par un nom (nom-organisme) et un code qui l’identifie.

Pour chaque participation on enregistre le numéro de participant et le numéro de séminaire.

Questions :

1- Elaborer la liste des colonnes.

2- Donner la liste des tables.

3- Donner la liste des liens entre les tables.

4- Préciser les clés primaires des tables.

5- Donner une description textuelle de la base de données.

6- Donner une description graphique de la base de données.

Bon travail

Base De Données


Correction du Devoir de Synthèse N°1

Exercice N° 4

1-

Liste des colonnesNom colonne

Description TypeDe Données

taille obligatoire

Valeur par défaut

Valeur autorisé

Sujet

……. …… ……. ….. …… ….. …… …….

2-

Liste des tablesNom Table Description Sujet

Chercheur L’ensemble des chercheurs Chercheurs

Diplôme Ensemble des diplômes Diplômes

Article Ensemble des articles Articles

Domaine Domaines de recherches Domaines

Séminaire Ensemble des séminaires Séminaires

Participant Ensemble des participant Participants

Organisation Ensemble des organismes Organisations

Participation Les participations des participants dans les séminaires

Participations

3-

Mère FilleDiplôme ChercheurChercheur ArticleArticle DomaineChercheur SéminaireOrganisme ParticipantParticipant ParticipationSéminaire Participation

4-

Table Clé primaireChercheur Code_chDiplôme Code_dip

Base De Données


Article Code_artDomaine Code_DRSéminaire Num_semParticipant Num_participantOrganisme Code_organismeParticipation Num_sem, num_paticipant

5- Chercheur (code_ch, nom_ch, prenom_ch, ad_ch, tel_ch, code_dip#)

Diplôme (code_dip, nom_dip)

Article (code_art, date_redaction_art, code_ch #, titre_art)

Domaine ( code_DR, nom_DR, code_art#)

Séminaire (num_sem, titre_sem, date_sem, lieu_sem, code_ch#)

Participant ( num_participant, nom_participant, prenom_participant, code_organisme#)

Organisme (code_organisme, om_organisme)

Participation ( num_sem #, num_participant #)

6-

Base De Données


Base De Données


Devoir de contrôle N°2


Date : 22/02/2008 Durée : 1h Nb. Pages : 2 Barème : 3+4+13



Exercice N°1 :


- Clé primaire.- Contrainte d’intégrité.- Mode assisté.

Exercice N°2 :

Pour chacune de ces propositions, donner la (ou les) bonne(s) réponse(s).

1- Une clé primaire :

a- Doit être définie par une seule colonne.

b- Peut être une clé étrangère dans une autre table.

c- Ne peut être que de type numérique.

2- Une base de données

d- Peut contenir une seule table.

e- Doit contenir au minimum deux tables.

f- Ne peut jamais être supprimé.

3- Une base de données garde les informations d’une façon

g- Volatile.

h- Permanente.

i- Temporaire.

4- Dans une base de données relationnelle, les données sont

Base De Données


j- Représentées par des objets.

k- Reliées par des liens de hiérarchie.

l- Regroupées sous forme de tables.

Exercice N°3 :

Soit la description suivante :

Client (Numcli, Nomcli, Prenomcli, adresse, mail)

Produit (Numprod, désignation, prix_unit, qte_stock)

Vendeur (Idvendeur, Nomvendeur)

Commande (Numcom, Numcli#, Idvendeur#, date_com, qte, Numprod#)

1- On suppose que Numcli, Numprod, Idvendeur et Numcom sont de type numérique.

Le nom, le prénom et l’adresse des clients sont des informations obligatoires, le mail peut ne pas être indiqué.

La valeur par défaut de la quantité en stock des produits est égale à 0.

La quantité commandée (qte) doit être toujours ≥0.

Ecrire les commandes SQL permettant de créer les tables : Client, Produit, Vendeur et Commande.

2- Rajouter à la table Client un nouveau champ nommé « tel » qui désigne le numéro de téléphone du client et qui contient au maximum 10 chiffres.

3- Rajouter à la table Produit une contrainte consistant à vérifier que qte_stock≥0.

4- Ecrire la commande SQL permettant de supprimer la propriété mail de la table Client.

5- Ecrire la commande SQL permettant de désactiver la clé primaire de la table Commande.

6- Ecrire la commande SQL permettant de réactiver la clé primaire de la table Commande.

7- On veut élargir la taille de la colonne Nomvendeur de la table Vendeur. Ecrire la commande SQL correspondante.

8- On veut supprimer la table Commande de la BD, écrire la commande correspondante.

Base De Données


Bon Travail

Devoir de Synthèse N°2


Date : 06/03/2008 Durée : 2h Nb. Pages : 3 Barème : 3+2+7+8



Exercice N°1 :

Pour chacune de ces propositions, donner le ou les bonne(s) réponse(s) :

1-

a- Une Base de données peut contenir une seule table.

b- Une clé primaire est l’identificateur unique des n-uplets d’une table.

c- Les données d’un seul champ peuvent avoir des propriétés différentes

d- La clé étrangère a le même rôle que la clé primaire.

e- On peut stocker des images dans une BD.

2- Dans une BD on peut dire que :

f- Les tables servent à stocker des données.

g- Les tabes servent à stocker des données avec des règles de validation.

h- Les tables doivent comporter au moins deux clés étrangères.

3- La création d’une clé étrangère dans une table a pour objectif de :

i- Différencier sans ambiguïté les lignes.

j- Accélérer la recherche des données.

Base De Données


k- Aider à l’élaboration de liens entre les tables.

Exercice N°2 :


Base de Données Clé étrangère Mode assisté SGBD

Exercice N°3 :

Après la construction de ses hôtels dans l’une des zones touristiques, le directeur désire

préparer une Base de Données pour faciliter la gestion de ses hôtels.

Dans le cahier de charge, le directeur a donnée les informations suivantes :

Un hôtel possède un nom (nom_hotel) et il est identifié par numéro qui est unique

(num_hotel).

Un hôtel appartient a un seul type (exemple : 5 étoiles…), chaque type est caractérisé

par un code qui est unique (code_type) et un nom (nom_type).

L’hôtel est composé de plusieurs chambres, chaque chambre a un numéro qui

l’identifie (num_chambre).

Les chambres d’un hôtel appartiennent chacune à une seule catégorie (luxe, confort,

suites, tourisme…). Chaque catégorie a un code unique (code_cat), un Libellé

(lib_cat), un tarif (tarif_cat) et un nombre de lits (nb_lits).

1- Elaborer la liste des colonnes en remplissant le tableau suivant :


colonne DescriptionTypeDe

Donnéestaille obligatoire

Valeur par

défaut

Valeur autorisé Sujet

……. …… ……. ….. …… ….. …… …….

2- Elaborer la liste des tables en remplissant le tableau suivant:


…………….. …………………. …………………

3- Donner la liste des liens entre les tables en remplissant le tableau suivant :

Base De Données


Table Mère Table fille Clé primaire Clé étrangère…………….. …………………. ………………… ………………

4- Donner une description textuelle et graphique de la base de données.

Exercice N°4 :

Soit la description suivante :

Pilote (PlNum, PlNom, PlPrenom, ville, salaire)

Avion (AvNum, AvNom, capacité, localisation)

Vol (VolNum, PlNum#, AvNum#, VilleDep, VilleArr, heureDep, heureArr)

1- Ecrire les commandes SQL permettant de créer les tables : Pilote, Avion et Vol. En tenant compte de ses informations :

Colonne Type Taille contraintePlNum Entier 3 Clé primaire

PlNom Chaine de caractères 25 Non NULL

PlPrenom Chaine de caractères 25 Non NULL

ville Chaine de caractères 30

salaire décimale (8,2) >0

AvNum Entier 5 Clé primaire

AvNom Chaine de caractères Non NULL

capacité Entier 5 >=100

localisation Chaine de caractères 30

VolNum Entier 10 Clé primaire

VilleDep Chaine de caractères 30 Non NULL

VilleArr Chaine de caractères 30 Non NULL

heureDep Décimale (2,2) Non NULL

heureArr Décimale (2,2) Non NULL

2- Rajouter à la table Pilote un nouveau champ nommé « adresse » qui contient au maximum 30 caractères.

3- Rajouter à la table Vol une contrainte consistant à vérifier que l’heure de départ est inférieur à l’heure d’arrivé.

4- Ecrire la commande SQL permettant de supprimer la propriété ville de la table Pilote.

5- On veut réduire la taille de la colonne localisation de la table Avion. Ecrire la commande SQL correspondante.

6- On veut supprimer la table Pilote de la BD, écrire la commande correspondante.

Base De Données


7- Est ce que l’action de suppression demandé dans la question précédente est autorisé ou non ? Expliquez.

Bon Travail


Exercice n°3 :

1-


ColonneDescription Types de

donnéesTaille Obli

gatoire

Valeur par défaut

Valeur autorisé

Sujet

nom_hotel Nom d’hôtel

Chaîne de caractères

40 Non hôtels

num_hotel Numéro d’hôtel

numérique 5 Oui hôtels

code_type Code de type

numérique 5 Oui Types

nom_type Nom de type


20 Non Types

num_chambre

Numéro de chambre

numérique 3 Oui Chambres

code_cat Code de la catégorie

numérique 5 Oui Catégories

lib_cat Libellé de la catégorie


20 Oui Catégories

tarif_cat Tarif de la catégorie

numérique (8,2) Non >0 Catégories

nb_lits Nombre de lits

numérique 3 Non 1 >0 Catégories

2-

Listes des tables

Nom table Description Sujet

Hôtel Ensemble d’hôtels Hôtels

Chambre Ensemble de chambres Chambres

Type Types d’hôtels Types

Catégorie Catégories des chambres Catégories

3-

Base De Données


Un hôtel appartient à un seul type .Un Type contient plusieurs hôtels.

Hôtel : Fille et Type : Mère

L’hôtel est composé de plusieurs chambres. Une chambre appartient à un seul

hôtel.

Hôtel : Mère et Chambre : Fille

Chambre appartient à une seule catégorie. Une catégorie contient plusieurs

chambres.

Catégorie : Mère et Chambre : fille

Table Mère Table fille Clé primaire Clé étrangère

Type Hôtel code_type code_type

Hôtel Chambre num_hotel num_hotel

Catégorie Chambre code_cat code_cat

4-


Hôtel(num_hotel, nom_hotel, code_type#)

Type(code_type, nom_type)

Chambre(num_chambre, num_hotel#, code_cat# )

Catégorie(code_cat, lib_cat, tarif_cat, nb_lits)


Base De Données


Exercice n°4 :

1-

CREATE TABLE Pilote (

PLNum INT(3) PRIMARY KEY,

PLNom VARCHAR(25) NOT NULL,

PLPrenom VARCHAR(25) NOT NULL,

Ville VARCHAR(30),

salaire DECIMAL(8,2) CHECK (salaire >0)) ;

CREATE TABLE Avion (

AvNum INT(5) PRIMARY KEY,

AvNom VARCHAR(30) NOT NULL,

Capacite INT(5) CHECK (Capacite>=100),

localisation VARCHAR(30));

CREATE TABLE Vol (

VolNum INT(10) PRIMARY KEY,

PLNum INT(3),

AvNum INT(5),

VilleDep VARCHAR(30) NOT NULL,

VilleArr VARCHAR(30) NOT NULL,

heureDep DECIMAL(2,2) NOT NULL,

heureArr DECIMAL(2,2) NOT NULL,

CONSTRAINT clesec_vol_pilote FOREIGN KEY(PLNum) REFERENCES Pilote(PLNum),

CONSTRAINT clesec_vol_avion FOREIGN KEY(PLNum) REFERENCES Avion (AvNum)) ;

2-

ALTER TABLE Pilote ADD COLUMN adresse VARCHAR(30) ;

5-

ALTER TABLE Avion MODIFY localisation(20) ;

3-

ALTER TABLE Vol ADD CONSTRAINT CHECK (heureDep< heureArr) ;

6-

DROP TABLE Pilote;

4- 7- l’action de suppression de la table Pilote n’est pas autorisée car la table Pilote est en relation avec table

Base De Données


ALTER TABLE Pilote DROP COLUMN Ville ; Vol.

Section : Sciences de l’InformatiquesREPUBLIQUE TUNISIENNE

MINISTERE DE L’EDUCATION ET DE LA FORMATION

***

Devoir de synthèse N° 3

Base de Données

DATE : 13 Mai 2008

DUREE : 2 h – Coefficient : 1.5





- Base de Données

- Clé primaire

- Contraintes d’intégrités

- SGBD


Pour chacune de ces propositions, donner la (ou les) bonne(s) réponse(s).

1- Une clé primaire :

a- Doit être définie par une seule colonne.

m- Peut être une clé étrangère dans une autre table.

n- Ne peut être que de type numérique.

5- La création d’une clé étrangère dans une table a pour objectif de :

o- Différencier sans ambiguïté les lignes.

p- Accélérer la recherche des données.

q- Aider à l’élaboration de liens entre les tables.

Base De Données


6- Une base de données garde les informations d’une façon

r- Volatile.

s- Permanente.

t- Temporaire.

7- Dans une base de données relationnelle, les données sont

u- Représentées par des objets.

v- Reliées par des liens de hiérarchie.

w- Regroupées sous forme de tables.


Soit la description textuelle suivante :

Pilote (PLNUM, PLNOM, PLPRENOM, VILLE, SALAIRE)

Avion (AVNUM, AVNOM, CAPACITE, LOACLISATION)

Vol (VOLNUM, PLNUM#, AVNUM#, VILLE_DEP, VILLE_ARR, HEURE_DEP, HEUR_ARR)

1- Ecrire les commandes SQL permettant de:

a- Donner la liste de tous les vols

b- Donner nom, prénom et ville de tous les pilotes par ordre alphabétique des noms

c- Donner le nombre total des vols

d- Donner la capacité maximale et minimale des avions localisés à Paris

e- Donner les noms et prénoms des pilotes qui ont un salaire entre 1500 et 3000

f- Donner les numéros de vols, villes départs et villes d’arrivées des vols dont le

deuxième caractère de la ville de départ est ‘a ‘

g- Donner la moyenne des capacités des avions localisés dans une ville commençant

par la lettre ‘H’

h- Donner pour chaque vol son numéro, la ville de départ, la ville d’arrivé, la durée

(durée de vol=heure arrivé-heure départ) et le nom et prénom de son pilote

Base De Données


i- Insérer l’enregistrement suivant à la table Pilote :

120 CIRIN Christophe Paris 5000

j- Supprimer les avions ayant une capacité inférieure à 100


Enoncé :

Pour assurer la gestion des ses filiales, la société a mis en œuvre une Base de Donnée.

A chaque filiale est associé un ensemble de salariés. Un salarié appartient qu’à une

seule filiale.

Chaque filiale a un nom (nom_filia), une adresse (adresse_filia) et un code (code_filia)

qui l’identifie.

Chaque salarié possède un nom (nom_sal), prénom (prenom_sal), une adresse

(adresse_sal) et une matricule (matricule_sal) qui l’identifie.

Un salarié à une seule fonction. Une fonction possède un libellé (lib_fonct) et un code

(code_fonct). Dans une fonction on trouve plusieurs salariés.

Les salariés répartissent en catégories : un salarié appartient à une seule catégorie, dans

une catégorie on trouve plusieurs salariés.

Une catégorie possède un libellé (lib_cat) et un code ( code_cat) qui l’identifie.

Une filiale est affilé au prés de plusieurs organismes, à un organisme plusieurs filiales

lui sont affilés.

Un organisme possède un nom (nom_org) et un code (code_org).

Questions :

1- Elaborer la liste des colonnes en remplissant le tableau suivant :


colonne DescriptionTypeDe


Valeur par

défaut


……. …… ……. ….. …… ….. …… …….2- Elaborer la liste des tables en remplissant le tableau suivant:


…………….. …………………. …………………3- Donner la liste des liens entre les tables en remplissant le tableau suivant :

Table Mère Table fille Clé primaire Clé étrangère

Base De Données


…………….. …………………. ………………… ………………

4- Donner une description textuelle et graphique de la base de données.

Bon travail


Exercice N°1 :

Base de Données : Est un ensemble de données non redondantes, structurées et persistantes (non volatiles) relatives à un ou plusieurs domaines du monde réel.

Clé primaire : C’est un identificateur unique pour la table qui correspond à une colonne ou un ensemble de colonnes.

Contraintes d’intégrités : Ensemble de règles appliquées à une colonne ou une table et qui doivent être toujours vérifié pour maintenir la cohérence de la BD.

SGBD : Système de Gestion de BD c’est un logiciel qui permet de créer, de gérer et d’interroger efficacement une BD.

Exercice N°2 :

1 : b 2 : e+f 3 : h 4 : l

Exercice N°3 :

a- SELECT * FROM Vol ;

b- SELECT PLNOM, PLPRENOM, VILLE FROM Pilote ORDER BY PLNOM;

c- SELECT COUNT(VOLNUM) FROM Vol ;

d- SELECT MAX(CAPACITE), MIN(CAPACITE) FROM Avion WHERE LOCALISATION=’Paris’ ;

e- SELECT PLNOM, PLPRENOM FROM Pilote WHERE SALAIRE BETWEEN 1500 AND 3000;

f- SELECT VOLNUM, VILLE_DEP, VILLE_ARR FROM Vol WHERE VILLE_DEP LIKE ‘_a% ‘ ;

g- SELECT AVG(CAPACITE) FROM Avion WHERE LOCALISATION LIKE ‘H%’ ;

h- SELECT VOLNUM, VILLE_DEP, VILLE_ARR, HEUR_ARR- HEUR_DEP ‘Duree’, PLNOM, PLPRENOM FROM Vol, Pilote WHERE Pilote.PLNUM= Vol.PLNUM ;

Base De Données


i- INSERT INTO Pilote values (120,’CIRIN’, ‘Christophe’,’Paris’,5000) ;

j- DELETE FROM Avion WHERE CAPACITE<100;

Exercice N°4 :

1.

Liste des colonnesNom colonne

Description

TypeDe


Valeur par

défaut


nom_filia Nom filiale Chaîne de caractères

30 O FILIALE

adresse_filia Adresse filiale


30 O FILIALE

code_filia Code filiale Numérique 5 O FILIALE

nom_sal Nom salarié


20 O SALARIE

prenom_sal Prénom du salarié


25 O SALARIE

adresse_sal Adresse du salarié


30 O SALARIE

matricule_sal Matricule du salarié

Numérique 5 O SALARIE

lib_fonct Libellé de la fonction


20 O FONCTION

code_fonct Code de la fonction

Numérique 5 O FONCTION

lib_cat Libellé de la catégorie


20 O CATEGORIE

code_cat Code de la catégorie

Numérique 5 O CATEGORIE

nom_org Nom de l’organisme


15 O ORGANISME

code_org Code de l’organisme

Numérique 5 O ORGANISME

2.


FILIALE Ensemble de filiales FilialesSALARIE Ensemble de salariés SalariésFONCTION Ensemble de fonctions FonctionsCATEGORIE Ensemble de catégories Catégories

Base De Données


ORGANISME Ensemble d’organismes OrganismesFilia_Orga Regroupe les appartenances des

filiales aux organismes Filia_orga

3.

Table Mère Table fille Clé primaire Clé étrangère FILIALE SALARIE code_filia code_filia

FONCTION SALARIE code_fonct code_fonctCATEGORIE SALARIE code_cat code_cat

FILIALE Filia_orga code_filia code_filiaORGANISME Filia_Orga code_Org code_org

4.

FILIALE (code_filia, nom_filia, adresse_filia )

FONCTION (code_fonct, lib_fonct)

CATEGORIE (code_cat, lib_cat)

SALARIE (matricule_sal, nom_sal, prenom_sal, adresse_sal, code_filia#, code_fonct#,

code_cat # )

ORGANISME (code_org, nom_org)

Filia_Orga (code_filia#, code_org# )

Base De Données