Examen_Normale_BDDA_Septembre_2020.pdf
Télécharger PDFExamen de BDD avancée - 09 Septembre 2020
Licence : Génie logiciel
Examen : Bases de données avancées
Module I618
Durée 1h45min
Responsable : Aziz BAATAOUI (09 Septembre 2020)
- Respecter la durée du test ;
- Rédiger le test sur des feuilles vides et blanches de format A4 ;
- Écrire ton nom, ton prénom et ton CIN en haut de chaque feuille du test ;
- Une fois la rédaction du test est terminée : scanner les feuilles de rédaction test par ordre, préparer un fichier pdf et me l’envoyer à partir du compte académique Gmail au compte a.baataoui@umi.ac.ma
RQ1 :
Pour ceux n'ayant pas un Scanner, ils sont invités à utiliser l'application Andoid PDF Scanner qui permet de numériser les documents sous formes d’images et de les organiser dans un fichier PDF.
RQ2 :
La durée de préparation du fichier pdf contenant les feuilles de réponses (estimée à 15 min) est incluse dans la durée totale du test.
RQ3 :
La précision et la concision de vos réponses, la clarté d'écriture et la propreté de vos copies seront prises en compte.
RQ4 :
Toute réponse envoyée après 10h45min sera rejetée.
Exercice 1 : Modèle E/A, Algèbre relationnel et langage SQL (14 pts)
La base de données d’un hôpital a le schéma relationnel suivant :
- Service (numService, nom, bâtiment, #numMed)
- Salle (numSalle, #numServ, nbLits, #numInf)
- Infirmier (numInf, nom, adresse, téléphone, #numService)
- Patient (numPat, nom, prénom, adresse, téléphone, mutuelle)
- Medecin (numMed, nom, adresse, téléphone, spécialité)
- Hospitalisation (#numPat, dateEntrée, #numService, #numSalle, dateSortie)
- Acte (#numMed, #numPat, dateActe, description)
Remarque : Les clés primaires sont soulignées et les clés étrangères sont précédées par un « # ».
Dans la table Service, « numMed » désigne le médecin qui dirige le service. Le numéro d’une salle est local à un service (dans l’hôpital, il y a plusieurs salles n◦12). Une salle est surveillée par un infirmier désigné par « numInf ». « nbLits » est le nombre total de lits d’une salle. Un patient est hospitalisé pendant une certaine période [« dateEntrée » ; « dateSortie »]. Un médecin peut traiter un patient hospitalisé en effectuant un acte. La spécialité d’un médecin est, par exemple, cardiologue, ophtalmologue, etc.
1. Donner le modèle Entité-Association correspond le schéma relationnel d’un hôpital.
On suppose que Hospitalisation et Acte sont deux associations. Afin d’éviter l’ambiguïté sur la cardinalité minimale et maximal d’une association plusieurs – plusieurs, on suppose que la valeur minimale est (*) et la valeur maximale est (n).
2. Écrire le script SQL qui permet de créer la relation Hospitalisation en tenant compte les contraintes d’intégrées référentielles.
3. Exprimer les requêtes en algèbre relationnel et en SQL les requêtes suivantes :
- a. Quels sont les noms et spécialités des médecins ?
- b. Quels sont les numéros des médecins ayant fait un acte entre le « 24/12/2018 » et le « 01/01/2019 » ?
- c. Quels sont les noms et prénoms des patients hospitalisés à la date du « 04/04/2010 » ?
- d. Quels sont les noms des cardiologues qui sont directeurs des services ?
- e. Quels sont les numéros des patients qui n’ont jamais été traités par un ophtalmologue ? Pour la requête SQL utiliser la sous-requête NOT IN.
- f. Quels sont les numéros des médecins qui ont traité au moins un patient qui a été hospitalisé dans tous les services de l’hôpital ? Pour la requête SQL utiliser la sous requête NOT EXISTS.
- g. Quels sont les noms et prénoms des patients qui sont toujours restés plus de deux semaines à chaque hospitalisation ? Pour la requête SQL utiliser la sous-requête IN.
Exercice 2 : Dépendances fonctionnelles et normalisation (6 pts)
On considère la relation suivante décrivant des voitures :
R (NumImmat, Puissance, Marque, Pays, Agence, ChiffreAffaire)
1. Exprimer à l’aide des dépendances fonctionnelles, les contraintes suivantes que devient vérifier les instances de la relation R :
- On peut déduire la puissance et la marque d’une voiture à partir de son numéro d’immatriculation.
- Une marque est spécifique d’un pays.
- A partir d’une agence et une marque donnée, on peut déduire le chiffre d’affaires.
- Une agence peut représenter plusieurs marques.
2. Indiquer, à partir de la famille de dépendances fonctionnelles, issue de la question 1, quelle est la clé de la relation R ?
3. Indiquer, à partir de la famille de dépendances fonctionnelles, issue de la question 1, en quel forme normale la relation R ? On supposera que les attributs sont tous simples et monovalués.
4. Donner si besoin une décomposition de R en relations en troisième forme normale.
FAQ
1. Comment créer une relation Hospitalisation en SQL ?
Pour créer une relation Hospitalisation en SQL, il faut définir les colonnes et les contraintes d'intégrité référentielle.
2. Quelles sont les dépendances fonctionnelles dans la relation R ?
Les dépendances fonctionnelles dans la relation R incluent la puissance et la marque d’une voiture à partir de son numéro d’immatriculation, une marque spécifique d’un pays, et le chiffre d’affaires à partir d’une agence et une marque donnée.
3. Comment normaliser une relation en troisième forme normale ?
Pour normaliser une relation en troisième forme normale, il faut éliminer les dépendances transitives et les dépendances partielles.