2009

EPU GB 4ème année
Examen du 25 mai 2009
Bases de données relationnelles
Documents autorisés.
Exercice 1 : (SQL)
On considère une base de données dans laquelle sont stockées des séquences nucléiques codantes
et des séquences protéiques. Le schéma est composé des 4 relations suivantes :
organisme(idOrg, nomOrganisme, nbChrom)
chromosome(idChrom, idOrg, longChrom)
seqNC(idSNC, idChrom, debut, sens, longADN, seqADN)
seqProt(idProt, idSNC, longP, seqAA)
Chaque organisme (table organisme) est représenté par un identifiant, un nom et par le nombre
de chromosomes qu’il contient. Par exemple le n-uplet (123,human,46) de la relation organisme
indique que l’organisme d’identifiant 123 est celui de l’homme (human) et contient 46 chromosomes. Chacun des chromosomes (table chromosome) est identifié par un numéro (idChrom), il
fait référence à l’organisme dont il est extrait, et on lui associe sa longueur (longChrom). La table
seqNC donne la liste des séquences nucléiques codantes qui sont représentées par un identifiant
idSNC. Chaque séquence codante est localisée sur un chromosome idChrom, les champs debut,
sens, longADN et seqADN représentent respectivement la position de début de séquence sur le
chromosome, le sens de lecture, la longueur et enfin la séquence elle-même. Enfin, une protéine
(table seqProt) est identifiée par un numéro (idProt), elle fait référence à la séquence nucléique
codante associée (idSNC), et elle est représentée par sa longueur (longP), et la séquence (seqAA).
Ainsi, plusieurs protéines peuvent provenir de la même séquence codante (épissage alternatif).
1. Donnez une requête permettant de trouver la longueur moyenne des séquences nucléiques
codantes du chromosome dont l’identifiant est Human21b.
2. Donner une requête SQL permettant de trouver les séquences nucléiques codantes pour lesquelles on connaı̂t plusieurs protéines associées. Les trier par ordre décroissant de nombre
de séquences protéiques associées.
3. Donnez une requête SQL permettant de classer les identifiants des chromosomes de l’organisme Human par nombre décroissant de séquences protéiques qu’ils contiennent.
4. Donnez une requête SQL permettant de trouver les séquences nucléiques codantes pour
lesquelles on ne connaı̂t pas de protéines associées.
Exercice 2 : (Algèbre relationnelle)
A partir du schéma donné ci-dessous, exprimez en algèbre relationnelle chacune des quatre
requêtes suivantes.
Patient (numéroPatient, nom, adresse, ^
age)
Médecin (numéroPraticien, nom, adresse)
Prescription (numéroPraticien, numMed, quantité, date, numéroPatient)
Médicament (numMed, prix)
1. Quels sont les numéros des patients qui ont eu une ordonance depuis le 1er janvier 2009 ?
2. Quelles sont les adresses des médecins qui ont prescrit un médicament dont le prix dépasse
150 euros ?
3. Quels sont les numéros des patients auxquels le médicament de numéro M1 a été prescrit
en quantité supérieure à celle prescrite au patient de numéro patient1 ?
4. Donner la liste des médecins qui ont prescrit après le 15 mai 2009, l’ensemble des médicaments
qui ont été prescrit au moins une fois depuis le 15 mai 2009. On ne donnera que le numéro
du praticien.
Indication : il s’agit d’une division.
Exercice 3 : (Amélioration d’un schéma de BD)
Un informaticien débutant a créé une base de données pour l’occupation des chambres d’un hopital avec une seule table : Hopital(NumService, NomService, NumChambre, Etage, SurfaceChambre,
NbPlaces, NumPatient, NomPatient,Débuthospitalisation, FinHospitalisation). Intuitivement,
chaque n-uplet de cette table exprime le fait qu’un patient est hospitalisé à des dates bien précises dans
une chambre donnée et qu’il est rattaché à un service donné. Notons d’une part que plusieurs patients
dans la même chambre peuvent dépendre de services disctincts, et d’autre part que pendant une hospitalisation, un patient ne change pas de chambre. Ce deuxième point signifie qu’à partir du numéro de
patient et de la date de début d’hopitalisation, on est capable de déterminer sa chambre ainsi que le
service dont il dépend.
L’objectif de cet exercice est d’améliorer ce schéma.
1. Trouvez les dépendances fonctionnelles.
2. Trouvez tous les identifiants de la table Hopital. Combien y en a-t-il ?
3. Est-elle sous 2ème Forme Normale ? 3ème Forme Normale ? Justifiez vos réponses.
4. Normalisez la table pour qu’elle soit sous 3ème Forme Normale. Justifiez les différentes décompositions
que vous effectuez. Pour chacune des tables obtenues, donnez les identifiants.
5. La décomposition préserve-t-elle les dépendances fonctionnelles ? Autrement dit, est-ce que toutes
les dépendances fonctionnelles élémentaires non déduites trouvées en 1 se retrouvent dans l’une
des tables obtenues ?