Correction Examen Mi Session

INFO4030

Bases de donnes relationnelles et rparties

Correction examen mi-session

INFO4030 - C ORRECTION EXAMEN MI - SESSION

I.

Q UESTIONS

DE COURS

Expliquer quoi sert la normalisation dune base de donnes. La normalisation sert tablir une redondance minimale entre les attributs (champs) ncessaires aux besoins dune compagnie, tout en gardant des liens troits (cls primaires/trangres) entre ces attributs. Donner le rsultat du produit scalaire des deux relations suivantes : R A B C a b c et b c d c b a d c b S B C D c b a d a b b c d c b d

Le rsultat est une table P contenant les 16 (4 4) combinaisons (6-tuples) des 3-tuples de R avec les 3-tuples de S : P A a a a d d B C B b c c b c d b c b .. . c c b b b c C b a c c b D a b d d d

Donner le rsultat de la jointure naturelle des deux relations prcdentes. La jointure naturelle se fait sur les attributs B et C de mme valeur, autrement dit un sous-ensemble du produit scalaire : J A B C D a b c d d c b a d c b d

ric Hervet

-1/4-

Hiver 2009

INFO4030



II.

N ORMALISATION

On souhaite crer une base de donnes pour grer les rendez-vous de patients avec des mdecins. On a besoin des attributs suivants : NumroMdecin, NomMdecin, SpcialitMdecin, NumroCabinet, AdresseCabinet, NomHpital, AdresseHpital, ServiceHpital, NumroPatient, NomPatient, AdressePatient, DateHeureRendezVous, AdresseRendezVous, ServiceRendezVous. Quelques explications : Les numros pour les mdecins et les patients sont des identiants crs pour les identier de manire unique (puisque deux personnes peuvent avoir le mme nom). Un mdecin peut tre gnraliste ou spcialiste (dentiste, chirurgien, oncologue, ophtalmologiste, etc.), do la ncssit du champ SpcialitMdecin. Un mdecin opre dans un cabinet mais galement, soit de manire rgulire ou ponctuelle, dans un ou plusieurs hpitaux. Dans chaque hpital o il intervient, il peut, selon sa spcialit, intervenir dans diffrents services (par exemple un chirurgien peut oprer en oncologie ou pdiatrie, etc.). Un rendez-vous se fait bien sr entre un patient et un mdecin, et peut avoir lieu soit au cabinet du mdecin, soit dans un des services hospitaliers o le mdecin intervient.

Travail demand : Mettez en vidence un cas de redondance dinformation si on voulait garder une seule relation avec tous les attributs dcrits plus haut. Par exemple, pour un nouveau rendez-vous, donc des nouvelles valeurs de DateHeureRendezVous, AdresseRendezVous et ServiceRendezVous, il faut rpter toute linformation concernant le mdecin (numro, nom, spcialit, adresse), le patient (nom, adresse) et lhpital (nom, adresse) ! Mettre la base de donnes en quatrime forme normale en dcrivant bien chaque tape : Y-a-til des attributs qui ne sont pas en 1NF ? crire et justier en quelques mots les dpendances fonctionnelles. Identier la ou les cls possibles de la relation initiale avec tous les attributs. Mettre la base de donnes en 2NF. Mettre la base de donnes en 3NF. Mettre la base de donnes en BCNF. Si selon vous il y a des dpendances multivalues, crivez-les et justiez-les, puis mettez vos relations en 4NF.

ric Hervet

-2/4-

Hiver 2009

INFO4030



Voici une solution possible, selon les choix des dpendances fonctionnelles et/ou multivalues que lon fait : Pour simplier lexercice on peut par exemple considrer quun mdecin na quune spcialit possible, auquel cas on peut considrer que tous les attributs donns dans lnonc sont atomiques, donc en 1NF. Sinon : si un mdecin pouvait avoir plusieurs spcialits, une solution consisterait crer une table avec les champs NumroMdecin et NomSpcialit. Voici une liste des DFs possibles selon lnonc : NumroMdecin NomMdecin, SpcialitMdecin (df1) NumroPatient NomPatient, AdressePatient (df2) NomHpital AdresseHpital (df3) NumroCabinet AdresseCabinet (df4) NumroMdecin, NumroPatient, NumroCabinet, DateHeureRendezVous AdresseRendezVous (df5) NumroMdecin, NumroPatient, ServiceHpital, DateHeureRendezVous AdresseRendezVous, ServiceRendezVous (df6) Remarques : La (df2) suppose que chaque hpital a un nom unique. On peut raisonnablement considrer que cest le cas au moins au niveau provincial, rgional ou national dans le cas du Canada par exemple (mais peut-tre pas au niveau international). On na ni NomHpital ServiceHpital puisquun hpital possde plusieurs services, ni ServiceHpital NomHpital puisquun mme service (par exemple pdiatrie, oncologie, etc.) est prsent dans plusieurs hpitaux. Les (df5) et (df6) traduisent le fait quun rendez-vous se fait soit au cabinet du mdecin, soit dans un service hospitalier. La cl de la relation initiale avec tous les attributs est donc, selon les DFs listes : (NumroMdecin, NumroPatient, NumroCabinet, NomHpital, ServiceHpital, DateHeureRendezVous) Premire forme normale Tel que discut plus haut, on considre que tous les attributs sont en 1NF. Deuxime forme normale Toutes les dpendances fonctionnelles violent la 2NF (dpendances par rapport des parties de cl). On cre alors les sous-relations suivantes (cls primaires soulignes) : Mdecins(NumroMdecin, NomMdecin, SpcialitMdecin) Patients(NumroPatient, NomPatient, AdressePatient) Hpitaux(NomHpital, AdresseHpital) Exercices(NumroMdecin, NumroCabinet, NomHpital, ServiceHpital) RendezVous(NumroMdecin, NumroPatient, NumroCabinetOuServiceHpital, DateHeureRendezVous, AdresseRendezVous)ric Hervet-3/4-

Hiver 2009

INFO4030



Explications : La table Exercices est ncessaire car on a besoin de linformation o exerce un mdecin (son cabinet et les diffrents services o il intervient). Dans la table RendezVous, on a remplac les champs NumroCabinet et ServiceHpital par un seul champ NumroCabinetOuServiceHpital. Sinon il aurait fallu une table RendezVousCabinet pour les rendez-vous au cabinet dun mdecin, et une table RendezVousHpital pour les rendez-vous lhpital, et rpter dans chaque table les champs NumroMdecin, NumroPatient et DateHeureRendezVous . . . ce qui aurait t redondant ! Autrement dit on a rduit les (df5) et (df6) une seule DF : NumroMdecin, NumroPatient, NumroCabinetOuServiceHpital, DateHeureRendezVous AdresseRendezVous Chacune des tables cres est en 2NF : pas de dpendance par rapport des parties de cl. Troisime forme normale Les tables sont galement en 3NF : pas de dpendance entre attributs non cls. Forme normale de Boyce-Codd Les tables sont galement en BCNF : chaque partie gauche dune dpendance est une cl dune des tables. Quatrime forme normale Dans la table Exercices, on peut considrer quil y a deux dpendances multivalues : NumroMdecin NumroCabinet / ServiceHpital NumroMdecin NomHpital / ServiceHpital puisque le ou les services o exerce un mdecin sont indpendants du cabinet o il exerce et vice-versa, quun hpital possde plusieurs services, et enn quun service hospitalier se retrouve dans plusieurs hpitaux. Par consquent on remplace la table Exercices par trois sous-tables : CabinetsExercices(NumroMdecin, NumroCabinet) HpitauxExercices(NumroMdecin, NomHpital) ServicesExercices(NumroMdecin, ServiceHpital) Finalement, voici la dcomposition 4NF de la base de donnes Mdecins / Patients : Mdecins(NumroMdecin, NomMdecin, SpcialitMdecin) Patients(NumroPatient, NomPatient, AdressePatient) Hpitaux(NomHpital, AdresseHpital) CabinetsExercices(NumroMdecin, NumroCabinet) HpitauxExercices(NumroMdecin, NomHpital) ServicesExercices(NumroMdecin, ServiceHpital) RendezVous(NumroMdecin, NumroPatient, NumroCabinetOuServiceHpital, DateHeureRendezVous, AdresseRendezVous)

ric Hervet

-4/4-

Hiver 2009

Documents

Correction Examen Mi Session