Base de donnée

[Delwins]

bonjour à tous j'ai un problème avec un exercice.l'exercice en question est le suivant:
On désire gérer les emplois du temps des différents personnels (enseignant,enseignant-chercheur et chercheur) de l'UFR, pour savoir à tout moment s'il est possible de les joindre, et où .Pour cela, on considère que, en dehors des périodes où ils peuvent être joints dans leur bureau, les personnels peuvent être en réunion,quel que soit leur statu.une réunion est désignée par une date précise, une tranche horaire et une salle de réunion. On veut connaitre les autres personnes participant à la réunion.

Chaque personne est désignée par son nom, son prénom, le bureau où on peut la joindre.

Les enseignants peuvent, de plus être en cours. Un cours est identifié par la matière enseignée à laquelle est affectée toujours la même salle.Il est désigné par une période de début et de fin (ex: de février à mai), un jour de la semaine, une tranche horaire et une salle de cours. Plusieurs enseignants peuvent enseigner la même matière dans l'année, à des jours et créneaux horaire différents.un enseignant peut enseigner plusieurs fois la même matière dans l'année, à des période différentes.

Les chercheurs peuvent être à certaines périodes de l'année en mission en dehors de l'UFR. Une mission est désignée par une date de début et de fin, un lieu de mission avec le numéro de téléphone correspondant. Les chercheurs appartiennent à un laboratoire dont on peut joindre le secrétariat en cas d'urgence.

Les enseignants-chercheurs sont à la fois enseignants et chercheurs, avec un pourcentage plus ou moins grand d'enseignement (par rapport à la recherche) à effectuer .Ils peuvent donc être soit en réunion, soit en mission, soit en cours.

A) NORMALISATION

Proposez une liste d'informations élémentaire de départ, tout en donnant toutes les hypothèses nécessaires.
Donnez votre modèle à la première , deuxième et troisième forme normale.
Donnez le dictionnaire final.

B) SQL et PL/SQL

Donnez les requêtes SQL de création de base de données et de ses composants.
Donnez les requêtes SQL de création d'une vue permettant d'avoir la liste des enseignants qui sont en mission le mois de janvier de l'année universitaire en cours.
Donnez les requêtes SQL de création d'un déclencheur (trigger) empêchant la programmation d'une réunion entre 22h et 7h.
-----

[JPL]

Lis d'abord http://forums.futura-sciences.com/pr...ces-forum.html. Qu'as-tu essayé de faire ?

[Delwins]

Voici ce qui j’ai fait dans la partie A)
La liste d’infos élémentaire :
*INFORMATION
• Statut
• Nom
• Prénom
• Num Bureau
• Date réunion
• Temps de réunion
• Salle de réunion
• Personnes participant à la réunion
• Cours de
• Salle de cours
• Jours de cours
• Temps de cour
• Période de cours
• Période de mission
• Lieu de mission
• Période de mission
• Num du lieu de mission
• Num de Laboratoire
Les formes normales
1NF :
*INFORMATION
• Num Personnel
• Statut * PERSONNE PARTICIPANT A LA REUNION
• Nom
• Prénom Id Participant
• Num Bureau Nom
• Date réunion Prénom
• Temps de réunion
• Salle de réunion
• Cours de
• Jour de cours
• Salle de cours
• Temps de cours
• Période de cours
• Période de mission
• Lieu de mission
• Période de mission
• Num du lieu de mission
• Num de Laboratoire
2NF :
*INFORMATION * PERSONNE PARTICIPANT A LA REUNION *REUNION
• Num Personnel  Id Participant -Id Réunion
• Statut  Nom -Date de réunion
• Nom Prénom -Temps de réunion
• Prénom -Salle de réunion
• Num bureau

*PEDAGOGIE *MISSION *LABPRATOIRE
-Id Pédagogie -Id Mission -Id Laboratoire
-Cours de -Lieu de mission -Num Laboratoire
-Salle de cours -Période de Mission
-Jour de cours -Num du lieu de mission
-Période de cours
3NF :
Pour la 3 ème forme normale je ne la connais pas trop

[Bluedeep]

Bonjour

Je ne comprend pas trop la forme de ta restitution.
tel que je comprends l'exercice (qui est réaliste, ça mérite d'être souligné !) tu dois fournir (A) un MCD et (B) un MPD respectant la 3NF (donc par extension la 1NF et la 2NF).

[A voir en vidéo sur Futura]

[Delwins]

Donc si je comprend ma ma liste des informations élémentaire, 1NF et 2NF sont acceptables

[Delwins]

bonjour, j'aimerais que quelqu'un me dise quelque chose par rapport à mon exercice

[cherbe]

bonjour, j'aimerais que quelqu'un me dise quelque chose par rapport à mon exercice

Bonjour
On ne fera pas l’exercice à ta place, c'est un principe de base du forum.
Si l'on t'a demandé de faire ce travail, c'est que tu as reçu une formation/un cours.
Commence la réalisation sur la base de ce que tu as retenu du cours et le forum t'aidera probablement.
Personnellement, je regarderai la partie SQL.

[Delwins]

Ok merci pour ta réponse,sur la partie A:
les infos élémentaires sont: statut prof,nom du prof,num buro,date réunion,temps de réunion,salle de réunion,personnes participants à la réunion,cours,jrs de cours,salle de cours,temps de cours,période du cours,période de la mission,num du lieu de la mission,num du labo.
Pour les formes normales je trouve:
-1FN
info prof(num prof,statut prof,Nom prof,Prenom prof,num buro,num labo,date reunion,salle reunion,temps reunion,nom pers participant,prenom pers participant,cours de,jrs de cours,temps de cours,periode de cours,periode de mission,lieu de mission,num dulieu de mission)
-2FN
info prof(num prof,num reunion,num cours,num mission,statut prof,nom prof,prenom prof,num buro,num labo);
Reunion(num reunion,date reunion,salle reunion,temps reunion,nom pers participants,prenom pers participants);
cours(num cours,cours de,jrs de cours,temps de cours,periode de cours);
mission(num mission,lieu mission,periode mission,num tellieu de mission).
-3FN
info prof(num prof,num reunion,num cours,num mission,statut prof,nom prof,prenom prof,num buro,num labo);
Reunion(num reunion,num participants,date reunion,salle reunion,temps reunion);
participants(nom,prenom);
cours(num cours,cours de,jrs de cours,temps de cours,periode de cours);
mission(num mission,lieu mission,periode mission,num tellieu de mission).
-le dictionnaire

Nom donnée                Format                Longueur                       Type                     Règle de calcul                         Règle de gestion                             Documents  

numprof                      N                          100                               E                                                                                                                            texte
numreunion                   N                          100                               E                                                                                                                            texte
numcours                     N                          100                               E                                                                                                                            texte
nummission                   N                          100                               E                                                                                                                           texte
statut prof                  A                           15                               E                                                                                                                            texte

j'ai fait juste une partie du dictionnaire avec N=numerique,A=alphabétique et E=élémentaire.
Pour la partie B:
les requêtes

*info prof
CREATE TABLE info_prof(
num_prof number(100) NOT NULL,
num_reunion number(100) NOT NULL,
num_cours number(100) NOT NULL,
num_mission number(100) NOT NULL,
nom_prof varchar(50) NOT NULL,
prenom_prof varchar(50) NOT NULL,
num_buro number(100) NOT NULL,
num_labo number(100) NULL),
PRIMARY KEY(num_prof);

Pour la table info prof.

[cherbe]

les requêtes
*info prof
CREATE TABLE info_prof(
num_prof number(100) NOT NULL,
num_reunion number(100) NOT NULL,
num_cours number(100) NOT NULL,
num_mission number(100) NOT NULL,
nom_prof varchar(50) NOT NULL,
prenom_prof varchar(50) NOT NULL,
num_buro number(100) NOT NULL,
num_labo number(100) NULL),
PRIMARY KEY(num_prof);

Pour la table info prof.

Remarque 1 : je ne vois pas trop l'intérêt des variables NUM, ex num_prof. Son rôle sera redondant avec nom_prof. Il y a bien sûr risque d'homonymie mais le problème se pose ailleurs et a sans doute été résolu autrement ; il faut utiliser cette résolution.
Mais c'est juste un avis perso, pas une règle !

Remarque 2 : les variables qui stockent un nombre de salles, de prof... Etc, sont des entiers non signés. Ces variables sont donc de type INT ou INTEGER et pas NUMBER.
Ensuite, le nombre entre parenthèses est un nombre de caractères. Si il y a moins de 100 profs, tu as besoin de 2 caractères (99 maxi) donc mets 2 ; si moins de 1000, mets 3 (999 maxi) Etc

remarque 3 : pour les variables prénoms et noms de type varchar(), 50 caractères me semble excessif. Ça réserve inutilement de la mémoire. par expérience, 25/30 caractères suffisent (sauf pour les noms malgaches qui sont souvent très long).

Remarque 4 : je complète toujours CREATE TABLE ma_table ainsi :
CREATE TABLE ma_table IF NO EXISTS
c'est plus prudent en cas de ré exécution du code.

Remarque 5 : il vaut mieux indiquer le type de moteur (facultatif) et préciser le jeu de caractères des variables varchar. Insère ceci en dernière ligne de la création de table :
ENGINE=MyISAM DEFAULT CHARSET=utf8;
(tu peux mettre tout autre jeu de caractères que UTF8. Si tu es sous Windows, le type "latin1_general_ci" va bien)

[Bluedeep]

Bonjour

Remarque 1 : je ne vois pas trop l'intérêt des variables NUM, ex num_prof. Son rôle sera redondant avec nom_prof. Il y a bien sûr risque d'homonymie mais le problème se pose ailleurs et a sans doute été résolu autrement ; il faut utiliser cette résolution.

Dans un MCD tout à fait d'accord, si le numéro ne constitue pas une information fonctionelle (ce qui est le cas, cf. énoncé); dans un MPD utiliser une chaine comme PK est plus que contestable en terme de perf. (notamment sur les jointures). Par ailleurs, ne pas confondre le critère métier d'unicité (ici : nom, prénom, bureau) avec le besoin technique que constitue la PK. Dans certains cas, on peut éventuellement superposer lun et l'autre (N° matricule, N° SS, etc ....) en revanche quand le critère d'unicité est constitué de plusieurs info (ce qui est le cas ici), il est toujours préférable de le différencier de la PK, qui devient une clef purement technique, typiquement de type INT/BIGINT IDENTITY ou de type GUID (ce dernier à utiliser uniquement si il existe un besoin de fusion de base, la dégradation de perfs entrainée par l'usage de ce type ne pouvant pas être négligée).

Or, ici il doit sortir au final un MPD; donc avoir un ID de personne hors le critère d'unicité (sur lequel on se contentera de définir un index unique) est une bonne chose; d'autant qu'ici utiliser le critère d'unicité comme PK est une impasse compléte : il devient de facto impossible de changer de nom ou de bureau.

Remarque 2 : les variables qui stockent un nombre de salles, de prof... Etc, sont des entiers non signés. Ces variables sont donc de type INT ou INTEGER et

Oui (mais, accessoirement, INT et INTEGER sont des entiers signés )

remarque 3 : pour les variables prénoms et noms de type varchar(), 50 caractères me semble excessif. Ça réserve inutilement de la mémoire. par expérience, 25/30 caractères suffisent (sauf pour les noms malgaches qui sont souvent très long).

Totalement faux.
Un varchar ne réserve que 6 ou 8 octets en sus de son stockage. (c'est justement pour cela qu'il existe les types CHAR et les types VARCHAR). Le stockage maxi n'a aucun impact sur le stockage effectif.
Bref :

declare @X varchar(10);
set @X = 'Z';

et

declare @X varchar(4096).
set @X = 'Z';

utilise exactement autant de mémoire l'un que l'autrre.

En revanche, utiliser VARCHAR pour des noms me semble un choix fumeux (impossible de rentrer certains noms nordiques ou est-européen par exemples); utiliser plutot NVARCHAR.

Remarque 4 : je complète toujours CREATE TABLE ma_table ainsi :
CREATE TABLE ma_table IF NO EXISTS
c'est plus prudent en cas de ré exécution du code.

Ce conseil me semble un peu discutable : risque de laisser passer un script de création en mise à jour sans alterte. (accessoirement la syntaxe IF NO EXISTS n'est absolument pas standard - lire agnostique au SGBD - les seuls test standards d'existence sont ceux qui font appel aux vues du schéma INFORMATION_SCHEMA).

Remarque 5 : il vaut mieux indiquer le type de moteur (facultatif)

Là, je suis en désaccord total avec cette remarque.
Si on laisse de coté le (très regrettable) MySql, aucun DBMS actuel n'a de moteur suffisament mal foutu pour avoir à en changer en fonction du stockage.
Ce n'était pas le cas il y a 25 ou 30 ans, mais actuellement, préciser le type de moteur n'a aucun sens, puisqu'il ne peut pas être précisé de facto (sauf le cas du DBMS libre particuliérement mal fichu sus-mentionné).

[Delwins]

Bonjour, avec vos remarque je crois que ceci vaut mieux:
*pour ma table mission

CREATE TABLE mission(
Id_mission integer(50) NULL,
lieu_mission varchar(15),
periode_mission varchar(30),
lieu_mission varchar(15),
num_tel_lieu_mision int(10),
PRIMARY KEY(Id_mission),
CREATE TABLE IF NO EXISTE);

*pour la table cours

CREATE TABLE cours(
Id_cours integer(99) NOT NULL,
cours_de: varchar(99) NOT NULL,
jrs_cours varchar(10) NOT NULL ,
temps_cours varchar(10) NOTNULL,
periode_cours varchar(20) NOT NULL,
PRIMARY KEY(Id_cours),
CREATE TABLE IF NO EXISTE);
*pour la table reunion

CREATE TABLE reunion(
Id_reunion integer(99) NULL,
date_reunion varchar(15) NULL,
salle_reunion_n: integer(99) NOT NULL,
temps_reunion varchar(10),
PRIMARY KEY(Id_reunion),
CREATE TABLE IF NO EXISTE);

*pour la table participant

CREATE TABLE participant(
Id_participant integer(20) NULL,
nom_participant nvarchar(15)NOT NULL,
prenom_participant nvarchar(25) NOT NULL,
PRIMARY KEY(Id_participant),
CREATE TABLE IF NO EXISTE);

*pour la table prof

CREATE TABLE prof(
Id_prof integer(999) NOT NULL,
Id_mission integer(50)NULL,
Id_cours integer(99)NOT NULL,
Id_reunion integer(99)NULL,
Id_participant integer(50)NULL,
statut_prof varchar(15) NOT NULL,
nom_prof nvarchar(15) NOT NULL,
prenom_prof nvarchar(25) NOT NULL,
n:_buro integer(999) NOT NULL,
n:_labo integer(99) NULL,
PRIMARY KEY(Id_prof),
FOREIGN KEY(Id_mission)references mission(Id_mission),
FOREIGN KEY(Id_cours)references cours(Id_cours),
FOREIGN KEY(Id_reunion)references mission(Id_mission),
FOREIGN KEY(Id_participant)references participant(Id_participant),
CREATE TABLE IF NO EXISTE);

Cela est les requêtes SQL de la création de ma base de donnée.

[Delwins]

Pour la création d'une vue pour voir la liste de prof disponible dans le mois de janvier

[Delwins]

Pour la création d'une vue pour voir la liste de prof disponible dans le mois de janvier

SELECT nom_prof,prenom_prof FROM prof WHERE periode_mission IS janvier;

[cherbe]

Bonjour
Dans la table MISSION, tu as deux fois la variable lieu_mission ;
tu devrais simplifier les noms de variables. Ex dans la table mission, "lieu" suffit à la place de "lieu_mission". Tu penseras à moi en écrivant les requêtes qui seront moins fastidieuses à taper !
Tu n'as pas tenu compte de ma remarque sur le dimensionnement des variables INT. Tu continues à les dimensionner à 99 chiffres ce qui n'a aucun sens ici. Ex pour stocker le nombre 9 999 999, 7 chiffres suffisent. Tu prévois autant de réunions ou de cours ?
Tu termines la création des tables par CREATE TABLE IF NO EXISTE. Pour moi, c'est une erreur, en tous cas je ne connais pas cette syntaxe.
D'autre part, c'est CREATE TABLE IF NO EXISTS en MySQL. Je doute que ce soit différents dans les autres SGBD faisant appel au langage SQL.
Supprime cette ligne inutile.

[cherbe]

Pour la création d'une vue pour voir la liste de prof disponible dans le mois de janvier
SELECT nom_prof,prenom_prof FROM prof WHERE periode_mission IS janvier;

attention à ta façon de t'exprimer. Ces instructions sont une requête, et non une vue.
Moi j'aurais écris:

SELECT nom_prof,prenom_prof FROM prof WHERE periode_mission = 'janvier';

ou plus prudemment :

SELECT nom_prof,prenom_prof FROM prof WHERE periode_mission like UCASE('%janvier%');

like avec % % permet de s'affranchir des espaces qui pourraient s'inviter dans la chaine ;
UCASE permet de s'affranchir des problèmes de majuscules/minuscules.
Je sais bien que like est insensible à la casse mais la configuration du serveur peut générer ce problème.

[Delwins]

pour la dimension c'est compris

je devais ecrire par exemple Id_prof integer(2) NOT NULL pour 99 prof

je ne sais pas comment utiliser le trigger malgré que j'ai la syntaxe:

CREATE TRIGGER nom { BEFORE | AFTER } { evenement [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE nomfonc ( arguments )

[Delwins]

pour la dimension c'est compris

je devais ecrire par exemple Id_prof integer(2) NOT NULL pour 99 prof

je ne sais pas comment utiliser le trigger malgré que j'ai la syntaxe:

CREATE TRIGGER nom { BEFORE | AFTER } { evenement [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE nomfonc ( arguments )

[cherbe]

pour la dimension c'est compris
je devais ecrire par exemple Id_prof integer(2) NOT NULL pour 99 prof

OUI MAIS attention à la longévité de la base. Des profs vont partir et d'autres vont arriver. Rien ne dit que les partants seront sortis de la table. Il faut donc prévoir large.
Si tu as moins de 100 profs, INT(2) suffira au début mais pour un avenir lointain, il vaut mieux mettre INT(3).

je ne sais pas comment utiliser le trigger malgré que j'ai la syntaxe

TRIGGER est un déclencheur qui va exécuter quelque chose avant (BEFORE) ou après (AFTER) qu'un événement se produise. Quel événement veux-tu surveiller ? Quelle est l'action à déclencher ?
Tu as un exemple ici

[Delwins]

je ne sais pas comment m'y prendre avec le trigger malgré que je connais la syntaxe:



CREATE TRIGGER nom { BEFORE | AFTER } { evenement [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE nomfonc ( arguments )

[cherbe]

je ne sais pas comment m'y prendre avec le trigger malgré que je connais la syntaxe

Tu lis les réponses ?

[Bluedeep]

CREATE TRIGGER nom { BEFORE | AFTER } { evenement [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE nomfonc ( arguments )

La syntaxe particulière (choix entre trrigger de niveau "ligne" et de niveau "commande") permet de dier que tu travaille avec une base Oracle, si je ne me trompe pas.

[Delwins]

Bonjour je n'avais su que nous étions sur la deuxième page c'est pour quoi j'ai répété la même question,
Pour empêcher la programmation d'une réunion entre 22heure et 7heure je propose :

CREATE TRIGGER pas_de_réunion BEFORE ON mission
UPDATE EACH ROW
WHEN(debut_réunion<7 heure & debut_reunion>22 heure )
DECLARE
pas_de_reunion_a_cette_heure CHAR;
BEGIN
pas_de_reunion_a_cette_heure :=new.pas_de_reunion_a_cette_h eure - ld.pas_de_reunion_a_cette_heur e;
DBMS_OUTPUT.LINE('erreur:' || pas_de_reunion_a_cette_heure);
END;
/

[Delwins]

effectivement je travail avec oracle

[cherbe]

Bonjour je n'avais su que nous étions sur la deuxième page c'est pour quoi j'ai répété la même question,
Pour empêcher la programmation d'une réunion entre 22heure et 7heure je propose :

CREATE TRIGGER pas_de_réunion BEFORE ON mission
UPDATE EACH ROW
WHEN(debut_réunion<7 heure & debut_reunion>22 heure )
DECLARE
pas_de_reunion_a_cette_heure CHAR;
BEGIN
pas_de_reunion_a_cette_heure :=new.pas_de_reunion_a_cette_h eure - ld.pas_de_reunion_a_cette_heur e;
DBMS_OUTPUT.LINE('erreur:' || pas_de_reunion_a_cette_heure);
END;
/

Je n'ai jamais utilisé TRIGGER donc je ne suis pas qualifié pour vérifier ton code.
Cependant, le test WHEN n'est pas logique. debut_reunion ne peut être à la fois inférieur à 7 et supérieur à 22. Il faut remplacer AND par OR
D'autre part, tu auras besoin d'autres TRIGGER, notamment pour éviter les doublons. Le nom de celui-ci (pas_de_réunion) devrait être plus précis exemple : test_nocturne"

[Bluedeep]

Bonjour je n'avais su que nous étions sur la deuxième page c'est pour quoi j'ai répété la même question,
Pour empêcher la programmation d'une réunion entre 22heure et 7heure je propose :

CREATE TRIGGER pas_de_réunion BEFORE ON mission
UPDATE EACH ROW
WHEN(debut_réunion<7 heure & debut_reunion>22 heure )
DECLARE
pas_de_reunion_a_cette_heure CHAR;
BEGIN
pas_de_reunion_a_cette_heure :=new.pas_de_reunion_a_cette_h eure - ld.pas_de_reunion_a_cette_heur e;
DBMS_OUTPUT.LINE('erreur:' || pas_de_reunion_a_cette_heure);
END;
/

- Une opération "-" sur des CHAR, c'est étrange.
- Pour du boolean pourquoi utiliser du char plut que du BIT ?
- le plus simple pour annuler la modif est soit :

- de "rollbacker" la transaction
- de remettre l'ancienne valeur en place
- de lever une exception si vous voulez gérer un message d'erreur depuis le programme client (en "trappant" l'exception sur l'appel de requête de modification).

[Delwins]

OK après la connaissance de tout ces codes de ma base de donnée comment doit faire pour créer mon programme? je dois le mettre dans les code dans le terminalSQL?