2009

EPU GB 4`eme ann´ee Bases de donn´ees relationnelles
Examen du 25 mai 2009 Documents autoris´es.
Exercice 1 : (SQL)
On consid`ere une base de donn´ees dans laquelle sont stock´ees des s´equences nucl´eiques codantes
et des s´equences prot´eiques. Le sch´ema est compos´e 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´esent´e 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 chromo-
somes. Chacun des chromosomes (table chromosome) est identifi´e par un num´ero (idChrom), il
fait r´ef´erence `a l’organisme dont il est extrait, et on lui associe sa longueur (longChrom). La table
seqNC donne la liste des s´equences nucl´eiques codantes qui sont repr´esent´ees par un identifiant
idSNC. Chaque s´equence codante est localis´ee sur un chromosome idChrom, les champs debut,
sens,longADN et seqADN repr´esentent respectivement la position de d´ebut de s´equence sur le
chromosome, le sens de lecture, la longueur et enfin la s´equence elle-mˆeme. Enfin, une prot´eine
(table seqProt) est identifi´ee par un num´ero (idProt), elle fait r´ef´erence `a la s´equence nucl´eique
codante associ´ee (idSNC), et elle est repr´esent´ee par sa longueur (longP), et la s´equence (seqAA).
Ainsi, plusieurs prot´eines peuvent provenir de la mˆeme s´equence codante (´epissage alternatif).
1. Donnez une requˆete permettant de trouver la longueur moyenne des s´equences nucl´eiques
codantes du chromosome dont l’identifiant est Human21b.
2. Donner une requˆete SQL permettant de trouver les s´equences nucl´eiques codantes pour les-
quelles on connaˆıt plusieurs prot´eines associ´ees. Les trier par ordre d´ecroissant de nombre
de s´equences prot´eiques associ´ees.
3. Donnez une requˆete SQL permettant de classer les identifiants des chromosomes de l’or-
ganisme Human par nombre d´ecroissant de s´equences prot´eiques qu’ils contiennent.
4. Donnez une requˆete SQL permettant de trouver les s´equences nucl´eiques codantes pour
lesquelles on ne connaˆıt pas de prot´eines associ´ees.
Exercice 2 : (Alg`ebre relationnelle)
A partir du sch´ema donn´e ci-dessous, exprimez en alg`ebre relationnelle chacune des quatre
requˆetes suivantes.
Patient (num´eroPatient, nom, adresse, ^age)
M´edecin (num´eroPraticien, nom, adresse)
Prescription (num´eroPraticien, numMed, quantit´e, date, num´eroPatient)
M´edicament (numMed, prix)
1. Quels sont les num´eros des patients qui ont eu une ordonance depuis le 1er janvier 2009 ?
2. Quelles sont les adresses des m´edecins qui ont prescrit un m´edicament dont le prix d´epasse
150 euros ?
3. Quels sont les num´eros des patients auxquels le m´edicament de num´ero M1 a ´et´e prescrit
en quantit´e sup´erieure `a celle prescrite au patient de num´ero patient1 ?
4. Donner la liste des m´edecins qui ont prescrit apr`es le 15 mai 2009, l’ensemble des m´edicaments
qui ont ´et´e prescrit au moins une fois depuis le 15 mai 2009. On ne donnera que le num´ero
du praticien.
Indication : il s’agit d’une division.

Exercice 3 : (Am´elioration d’un sch´ema de BD)
Un informaticien d´ebutant a cr´e´e une base de donn´ees pour l’occupation des chambres d’un hopi-
tal avec une seule table : Hopital(NumService, NomService, NumChambre, Etage, SurfaceChambre,
NbPlaces, NumPatient, NomPatient,D´ebuthospitalisation, FinHospitalisation). Intuitivement,
chaque n-uplet de cette table exprime le fait qu’un patient est hospitalis´e `a des dates bien pr´ecises dans
une chambre donn´ee et qu’il est rattach´e `a un service donn´e. Notons d’une part que plusieurs patients
dans la mˆeme chambre peuvent d´ependre de services disctincts, et d’autre part que pendant une hospi-
talisation, un patient ne change pas de chambre. Ce deuxi`eme point signifie qu’`a partir du num´ero de
patient et de la date de d´ebut d’hopitalisation, on est capable de d´eterminer sa chambre ainsi que le
service dont il d´epend.
L’objectif de cet exercice est d’am´eliorer ce sch´ema.
1. Trouvez les d´ependances fonctionnelles.
2. Trouvez tous les identifiants de la table Hopital. Combien y en a-t-il ?
3. Est-elle sous 2`eme Forme Normale ? 3`eme Forme Normale ? Justifiez vos r´eponses.
4. Normalisez la table pour qu’elle soit sous 3`eme Forme Normale. Justifiez les diff´erentes d´ecompositions
que vous effectuez. Pour chacune des tables obtenues, donnez les identifiants.
5. La d´ecomposition pr´eserve-t-elle les d´ependances fonctionnelles ? Autrement dit, est-ce que toutes
les d´ependances fonctionnelles ´el´ementaires non d´eduites trouv´ees en 1 se retrouvent dans l’une
des tables obtenues ?