none
Manipulation du cas Ecole dans un BD répartie (SQL) RRS feed

  • Question

  • j'ai ce TP 

    Soit le schéma relationnel global de la base de données Ecole:
    
    ELEVES(NUM_ELEVE, NOM, PRENOM, DATE_NAISSANCE, POIDS, ANNEE)
    COURS(NUM_COURS, NOM, NBHEURES, ANNEE)
    PROFESSEURS(NUM_PROF, NOM, SPECIALITE, DATE_ENTREE, DER_PROM, SALAIRE_BASE, SALAIRE_ACTUEL)
    ACTIVITES (NIVEAU,NOM , EQUIPE)
    
    RESULTATS(NUM_ELEVE, NUM_COURS, NOTE )
    CHARGE(NUM_PROF, NUM_COURS)
    ACTIVITES_PRATIQUEES(NUM_ELEVE, NIVEAU, NOM)
    
    
    
    Partie 1  Préparation de l’environnement de travail :
    1.	Nettoyez votre compte en supprimant toutes les tables qui existent.
    2.	Proposez une conception de BDR en utilisant au moins un type de fragmentation.
    Exemple de conception : 
    •	les élèves de première année, leurs résultats, les activités qu’ils pratiquent, la table cours et la table activites dans le site S1 
    •	les élèves de deuxième année, leurs résultats, les activités qu’ils pratiquent, la table professeurs et la table charge sur le site S2.
    
    3.	Vous pouvez créer d’abord la « BD » ECOLE (en Lançant le script SQL ecole.sql) dans l’un des deux sites puis appliquer la fragmentation et enfin supprimer les tables d’origines.
    Remarque : vérifiez que toutes tables ont été créées et que les données ont bien été insérées.
    
    Partie 2 – Interrogation de la base de données
    
    1-	Obtenir le nom des élèves de l’équipe AMC INDUS.
    2-	 Professeurs qui prennent en charge TOUS les cours de deuxième année ; on demande le Numéro et le nom.
    
    Partie 3: transactions concurrentes
    1-Afficher le contenu de la table cours par les deux utilisateurs user1 et user2.
    2-useroran insère le tuple (6, IHM, 45, 3). Vérifiez si le tuple a bien été inséré. Vérifiez si user1 peut voir ce qu’user2 peut insérer dans une de ses tables (la table cours).
    3-Maintenant c’est user1 qui va insérer dans le schéma user2 (la table cours) le tuple (7, BDR, 40,2). Est-ce que user1 a besoin de droit d’accès pour insérer ce tuple ? justifiez votre réponse.
    4-Evaluez l’effet de la commande COMMIT et ROLLBACK.
    5-Commentez les situations de blocage et inter-blocage.
    

    et ecole.sql

    drop table ACTIVITES_PRATIQUEES ;
    drop table CHARGE ;
    drop table RESULTATS ;
    drop table ACTIVITES ;
    drop table PROFESSEURS ;
    drop table COURS ;
    drop table ELEVES ;
    
    ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY' ;
    
    --   Table : ELEVES                                            
    create table ELEVES
    (
        NUM_ELEVE       Number (4)   NOT NULL,
        NOM             VARCHAR2(25) NOT NULL,
        PRENOM          VARCHAR2(25) NOT NULL,
        DATE_NAISSANCE  DATE,
        POIDS           Number,
        ANNEE           Number,
        SEXE	    CHAR(1),
        constraint PK_ELEVES primary key (NUM_ELEVE)
    );
    
    --   Table : COURS                                             
    create table COURS
    (
        NUM_COURS       Number(2)   NOT NULL,
        NOM             VARCHAR(20) NOT NULL,
        NBHEURES        Number(2),
        ANNEE           Number(1),
        constraint PK_COURS primary key (NUM_COURS)
    );
    --   Table : PROFESSEURS                                       
    create table PROFESSEURS
    (
        NUM_PROF        Number(4)    NOT NULL,
        NOM             VARCHAR2(25) NOT NULL,
        SPECIALITE      VARCHAR2(20),
        DATE_ENTREE     DATE,
        DER_PROM        DATE,
        SALAIRE_BASE    Number,
        SALAIRE_ACTUEL  Number,
        constraint PK_PROFESSEURS primary key (NUM_PROF)
    );
    
    --   Table : ACTIVITES                                         
    create table ACTIVITES
    (
        NIVEAU          Number(1)    NOT NULL,
        NOM             VARCHAR2(20) NOT NULL,
        EQUIPE          VARCHAR2(32),
        constraint PK_ACTIVITES primary key (NIVEAU,NOM)
    );
    --   Table : RESULTATS                                         
    create table RESULTATS
    (
        NUM_ELEVE       Number(4) NOT NULL,
        NUM_COURS       Number(4) NOT NULL,
        POINTS         Number,
        constraint PK_RESULTATS primary key (NUM_ELEVE, NUM_COURS)
    );
    
    --   Table : CHARGE                                            
    create table CHARGE
    (
        NUM_PROF       Number(4) NOT NULL,
        NUM_COURS      Number(4) NOT NULL,
        constraint PK_CHARGE primary key (NUM_COURS, NUM_PROF)
    );
    
    --   Table : ACTIVITES_PRATIQUEES                              
    create table ACTIVITES_PRATIQUEES
    (
        NUM_ELEVE       Number(4) NOT NULL,
        NIVEAU          Number(1) NOT NULL,
        NOM             VARCHAR2(20) NOT NULL,
        constraint PK_ACTIVITES_PRATIQUEES primary key 
        (NUM_ELEVE, NIVEAU, NOM) 
    );
    --   Les cles etrangeres                                 
    
    alter table RESULTATS
        add constraint FK_RESULTAT_ELEVES foreign key  (NUM_ELEVE)
           references ELEVES (NUM_ELEVE);
    
    alter table RESULTATS
        add constraint FK_RESULTAT_COURS foreign key  (NUM_COURS)
           references COURS (NUM_COURS);
    
    alter table CHARGE
        add constraint FK_CHARGE_COURS foreign key  (NUM_COURS)
           references COURS (NUM_COURS);
    
    alter table CHARGE
        add constraint FK_CHARGE_PROFESSEUR foreign key  (NUM_PROF)
           references PROFESSEURS (NUM_PROF);
    
    alter table ACTIVITES_PRATIQUEES
        add constraint FK_ACTIVITE_ELEVES foreign key  (NUM_ELEVE)
           references ELEVES (NUM_ELEVE);
    
    alter table ACTIVITES_PRATIQUEES
        add constraint FK_ACTIVITEPR_ACTIVITE foreign key  (NOM, NIVEAU)
           references ACTIVITES (NOM, NIVEAU);
    
    
    REM **************************************************************************
    REM Creation des donnees
    REM **************************************************************************
    
    SELECT 'Creation des donnees' FROM DUAL;
    SELECT 'Tapez sur RETURN 'FROM DUAL;
    PAUSE
    
    
    Insert into eleves (Num_eleve, nom, prenom, date_naissance, Poids, annee)
    Values (1, 'Brisefer', 'Benoit', '10-12-1994', 35,1) ;
    
    Insert into eleves (Num_eleve, nom, prenom, date_naissance, Poids, annee)
    Values (2, 'Genial', 'Olivier', '10-04-1994', 42, 1) ;
    
    Insert into eleves (num_eleve, nom, prenom, date_naissance, Poids, annee)
    Values (3, 'Jourdan', 'Gil', '28-06-1990', 72, 2) ;
    
    Insert into eleves (num_eleve, nom, prenom, date_naissance, Poids, annee)
    Values (4, 'Spring', 'Jerry', '16-02-1990', 78, 2) ;
    
    Insert into eleves (num_eleve, nom, prenom, date_naissance, Poids, annee)
    Values (5, 'Tsuno', 'Yoko', '29-10-1993', 45, 1) ;
    
    Insert into eleves (num_eleve, nom, prenom, date_naissance, Poids, annee)
    Values (6, 'Lebut', 'Marc', '29-04-1990', 75,2) ;
    
    Insert into eleves (num_eleve, nom, prenom, date_naissance, Poids, annee)
    Values (7, 'Lagaffe', 'Gaston', '08-04-1991', 61,1) ;
    
    Insert into eleves (num_eleve, nom, prenom, date_naissance, Poids, annee)
    Values (8, 'Dubois', 'Robin', '20-04-1992', 60, 2) ;
    
    Insert into eleves (num_eleve, nom, prenom, date_naissance, Poids, annee)
    Values (9, 'Walthery', 'Natacha', '07-09-1993', 59,1) ;
    
    Insert into eleves (num_eleve, nom, prenom, date_naissance, Poids, annee)
    Values (10, 'Danny', 'Buck', '15-02-1989', 82, 2) ;
    
    update eleves set sexe ='M' where num_eleve in (1, 3, 7);
    update eleves set sexe ='m' where num_eleve in (2,4,5);
    update eleves set sexe ='F' where num_eleve in (6,8);
    update eleves set sexe ='f' where num_eleve in (9,10);
    
    Insert into cours (Num_cours, Nom, Nbheures, annee)
    Values (1, 'Reseau', 15, 1);
    
    Insert into cours (Num_cours, Nom, Nbheures, annee)
    Values (2, 'Sgbd', 30, 1) ;
    
    Insert into cours (Num_cours, Nom, Nbheures, annee)
    Values (3, 'Programmation', 15,1) ;
    
    Insert into cours (Num_cours, Nom, Nbheures, annee)
    Values (4, 'Systeme', 30,2 ) ;
    
    Insert into cours (Num_cours, Nom, Nbheures, annee)
    Values (5, 'Analyse', 60,2) ;
    
    
    Insert into PROFESSEURS (Num_prof, nom , specialite, Date_entree, Der_prom, Salaire_base, Salaire_actuel)
    Values(1, 'Bottle', 'poesie', '01-10-1970', '01-10-1988', 2000, 2600) ;
    
    Insert into PROFESSEURS (Num_prof, nom , specialite, Date_entree, Der_prom, Salaire_base, Salaire_actuel)
    Values(2, 'Bolenov', 'reseau', '15-11-1968', '01-10-1998', 1900, 2468) ;
    
    Insert into PROFESSEURS (Num_prof, nom , specialite, Date_entree, Der_prom, Salaire_base, Salaire_actuel)
    Values(3, 'Tonilaclasse', 'poo', '01-10-1979', '01-01-1989', 1900, 2360) ;
    
    Insert into PROFESSEURS (Num_prof, nom , specialite, Date_entree, Der_prom, Salaire_base, Salaire_actuel)
    Values(4, 'Pastecnov', 'sql', '01-10-1975', '', 2500, 2500) ;
    
    Insert into PROFESSEURS (Num_prof, nom , specialite, Date_entree, Der_prom, Salaire_base, Salaire_actuel)
    Values(5, 'Selector', 'sql', '15-10-1982', '01-10-1988', 1900, 1900) ;
    
    Insert into PROFESSEURS (Num_prof, nom , specialite, Date_entree, Der_prom, Salaire_base, Salaire_actuel)
    Values(6, 'Vilplusplus', 'poo', '25-04-1990', '05-06-1994', 1900, 2200) ;
    
    Insert into PROFESSEURS (Num_prof, nom , specialite, Date_entree, Der_prom, Salaire_base, Salaire_actuel)
    Values(7, 'Francesca', '', '01-10-1975', '11-01-1998', 2000, 3200) ;
    
    Insert into PROFESSEURS (Num_prof, nom , specialite, Date_entree, Der_prom, Salaire_base, Salaire_actuel)
    Values(8, 'Pucette', 'sql', '06-12-1988', '29-02-1996', 2000, 2500) ;
    
    Insert into CHARGE (Num_prof, Num_cours)
    Values(1,1) ;
    
    Insert into CHARGE (Num_prof, Num_cours)
    Values(1,4) ;
    
    Insert into CHARGE (Num_prof, Num_cours)
    Values(2,1) ;
    
    Insert into CHARGE (Num_prof, Num_cours)
    Values(3,2) ;
    
    Insert into CHARGE (Num_prof, Num_cours)
    Values(3,4) ;
    
    Insert into CHARGE (Num_prof, Num_cours)
    Values(3,5) ;
    
    Insert into CHARGE (Num_prof, Num_cours)
    Values(4,2) ;
    
    Insert into CHARGE (Num_prof, Num_cours)
    Values(7,4) ;
    
    Insert into CHARGE (Num_prof, Num_cours)
    Values(8,1) ;
    
    Insert into CHARGE (Num_prof, Num_cours)
    Values(8,2) ;
    
    Insert into CHARGE (Num_prof, Num_cours)
    Values(8,3) ;
    
    Insert into CHARGE (Num_prof, Num_cours)
    Values(8,4) ;
    
    Insert into CHARGE (Num_prof, Num_cours)
    Values(8,5) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(1,1, 15 ) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(1,2 ,10.5 ) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(1, 4, 8) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(1, 5, 20) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(2, 1, 13.5) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(2, 2, 12) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(2, 4, 11) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(2, 5, 1.5) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(3, 1, 14) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(3, 2, 15) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(3, 4, 16) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(3, 5, 20) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(4, 1, 16.5) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(4, 2, 14) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(4, 4, 11) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(4, 5, 8) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(5, 1, 5) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(5, 2, 6.5) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(5, 4, 13) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(5, 5, 13) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(6, 1, 15) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(6, 2, 3.5) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(6, 4, 16) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(6, 5, 5) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(7, 1, 2.5) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(7, 2, 18) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(7, 4, 11) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(7, 5, 10) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(8, 1, 16) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(8, 2, 0) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(8, 4, 6) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(8, 5, 11.5) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(9, 1, 20) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(9, 2, 20) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(9, 4, 14) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(9, 5, 9.5) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(10, 1, 3) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(10, 2, 12.5) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(10, 4, 0) ;
    
    Insert into RESULTATS (Num_eleve, Num_cours, points)
    Values(10, 5, 16) ;
    
    Insert into ACTIVITES (Niveau, Nom, equipe)
    Values(1,'Mini foot','Amc Indus') ;
    
    Insert into ACTIVITES (Niveau, Nom, equipe) 
    values (1,'Surf','Les planchistes ...') ;
    
    
    Insert into ACTIVITES (Niveau, Nom, equipe)
    Values(2,'Tennis','Ace Club') ;
    
    Insert into ACTIVITES (Niveau, Nom, equipe)
    Values(3,'Tennis','Ace Club') ;
    
    Insert into ACTIVITES (Niveau, Nom, equipe)
    Values(1,'Volley ball', 'Avs80') ;
    
    Insert into ACTIVITES (Niveau, Nom, equipe)
    Values(2,'Mini foot', 'Les as du ballon') ;
    
    Insert into ACTIVITES (Niveau, Nom, equipe)
    Values(2,'Volley ball', 'smash') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (1, 1, 'Mini foot') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (1, 1, 'Surf') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (1, 1, 'Volley ball') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (1, 2, 'Tennis') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (2, 1, 'Mini foot') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (2, 2, 'Tennis') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (3, 2, 'Mini foot') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (3, 2, 'Tennis') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (3, 2, 'Volley ball') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (4, 1, 'Surf') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (4, 2, 'Mini foot') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (5, 1, 'Mini foot') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (5, 1, 'Surf') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (5, 1, 'Volley ball') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (8, 1, 'Mini foot') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (8, 1, 'Volley ball') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (8, 2, 'Volley ball') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (9, 1, 'Mini foot') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (9, 2, 'Volley ball') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (10, 1, 'Mini foot') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (10, 2, 'Tennis') ;
    
    Insert into ACTIVITES_PRATIQUEES (Num_eleve, Niveau, Nom)
    Values (10, 2, 'Volley ball') ;
    
    REM ********************************************************************************
    
    REM ********************************************************************************
    
    COMMIT;

    j'ai finie la partie 1 comme vous voir

                      --------S1--------
    create table ELEVES_S1 as select * from ELEVES where (ANNEE=1);
    
    create table RESULTATS_S1 as select RESULTATS.NUM_ELEVE, RESULTATS.NUM_COURS, POINTS from RESULTATS, ELEVES where (ELEVES.NUM_ELEVE=RESULTATS.NUM_ELEVE and ANNEE=1);
    
    create table ACTIVITES_PRATIQUEES_S1 as select ACTIVITES_PRATIQUEES.NUM_ELEVE, ACTIVITES_PRATIQUEES.NIVEAU, ACTIVITES_PRATIQUEES.NOM from ACTIVITES_PRATIQUEES, ELEVES where (ELEVES.NUM_ELEVE=ACTIVITES_PRATIQUEES.NUM_ELEVE and ANNEE=1);
    
    create table COURS_S1 as select * from COURS where (ANNEE=1);
    
    create table ACTIVITES_S1 as select * from ACTIVITES;
    
    
                      --------S2--------
    create table ELEVES_S2 as select * from ELEVES where (ANNEE=2);
    
    create table RESULTATS_S2 as select RESULTATS.NUM_ELEVE, RESULTATS.NUM_COURS, POINTS from RESULTATS, ELEVES where (ELEVES.NUM_ELEVE=RESULTATS.NUM_ELEVE and ANNEE=2);
    
    create table ACTIVITES_PRATIQUEES_S2 as select ACTIVITES_PRATIQUEES.NUM_ELEVE, ACTIVITES_PRATIQUEES.NIVEAU, ACTIVITES_PRATIQUEES.NOM from ACTIVITES_PRATIQUEES, ELEVES where (ELEVES.NUM_ELEVE=ACTIVITES_PRATIQUEES.NUM_ELEVE and ANNEE=2);
    
    create table PROFESSEURS_S2 as select * from PROFESSEURS;
    
    create table CHARGE_S2 as select * from CHARGE;

    et j'ai besoin de resoudre la partie 2 et 3 svp.


    vendredi 30 décembre 2016 15:34