Mastères 1999 - corrigé

Q1) Une requête SQL déclare/spécifie ce que l'on cherche, pas la procédure de recherche
Q2) Redondances de données => anomalies après mises à jour
Q3) Contraintes de domaine, d'unicité, de clé primaire, de clé étrangère, etc..
Q4) Persistence de la séquence des mises à jour depuis le précédent commit/rollback;

visibilité des mises à jour données au autres utilisateurs

Q5) select REGION from PLAGES group by REGION having count(NP) > 20
Q6) select NOM,PRENOM from NAGEURS

where NN not in (select NN from BAINS where DUREE >= 15)

select NOM,PRENOM from NAGEURS N

where not exists (select * from BAINS B where DUREE >= 15

and B.NN=N.NN)

select NOM,PRENOM from NAGEURS

minus

select NOM,PRENOM from NAGEURS N, BAINS B where DUREE >= 15

and B.NN=N.NN

select NOM,PRENOM from NAGEURS N

where 15 > ALL (select * from BAINS B where B.NN=N.NN)

select NOM,PRENOM from NAGEURS N, BAINS B

where B.NN=N.NN

group by NOM, PRENOM

having max(DUREE < 15)

Q7) select distinct NN from BAINS

minus

select NN from BAINS B where NP not in

(select NPP from NAGEURS N where N.NN = B.NN)

Q8) select REGION, NOMP, NP from PLAGES

minus

select REGION, NOMP, NP from PLAGES P where REGION not in

(select REGION from NAGEURS N, BAINS B

where P.NP=B.NP and B.NN=N.NN)

Q9) create view FREQUENTATIONS as

select REGION, NOMP, P.NP, CATEGORIE, sum(DUREE) HEURES99

from PLAGES P, BAINS B where P.NP=B.NP

and B.DATE between '01-01-99' and '31-12-99'

group by REGION, NOMP, P.NP, CATEGORIE

Q9bis) en théorie les mises à jour possibles sur la table PLAGES

le sont sur la vue FREQUENTATIONS

en pratique elles ne le sont pas car la vue est définie avec une jointure

et une agrégation (conditions chacune suffisante de refus par un SGBD comme Oracle)

CHECK (CATEGORIE IN ('CROUS', 'GET', 'Autres'))

CREATE TABLE CLIENTS (
       EMAIL                VARCHAR2(20) NOT NULL,
       NOMP                 VARCHAR2(20),
       CATEGORIE            VARCHAR2(20) NOT NULL,
       PRIMARY KEY (EMAIL),
       FOREIGN KEY (CATEGORIE)
                             REFERENCES CATEGORIES
);

CREATE TABLE PLATEAUX (
       EMAIL                VARCHAR2(20) NOT NULL,
       HDATE                DATE NOT NULL,
       PRIMARY KEY (EMAIL, HDATE),
       FOREIGN KEY (EMAIL)
                             REFERENCES CLIENTS
);

CREATE TABLE ARTICLES (
       ARTICLE              VARCHAR2(20) NOT NULL,
       NATURE               VARCHAR2(20),
       PRIX                 NUMBER NULL,
       PRIMARY KEY (ARTICLE),

CHECK (NATURE IN ('Plat principal', 'Supplément', 'Boisson'))

CREATE TABLE COMPOSANTS (
       EMAIL                VARCHAR2(20) NOT NULL,
       HDATE                DATE NOT NULL,
       ARTICLE              VARCHAR2(20) NOT NULL,
       QUANT                NUMBER NOT NULL,
       PRIMARY KEY (EMAIL, HDATE, ARTICLE),
       FOREIGN KEY (EMAIL, HDATE)
                             REFERENCES PLATEAUX,
       FOREIGN KEY (ARTICLE)
                             REFERENCES ARTICLES
);