Chapitre 4  Algèbre - SQL : Appartements - Écoles

4.1  Schéma

ADI :

adresse d'immeuble, clé; on fait l'hypothèse pour simplifier, que l'adresse identifie de manière unique un immeuble

NBETAGES :

nombre d'étages d'un immeuble

DATEC :

date de construction

PROP :

nom du propriétaire de l'immeuble qui est une personne

ADI :

adresse d'immeuble

NAPR :

numéro d'appartement

OCCUP :

occupant de l'appartement (nom de la personne)

TYPE :

type de l'appartement (Studio, F2, ...)

SUPER :

superficie de l'appartement

ETAGE :

étage où se situe l'appartement

NOM :

nom de personne, clé; on fait l'hypothèse pour simplifier, que ce nom est unique sur l'ensemble des personnes que l'on considère dans la base

AGE :

âge de la personne

PROF :

profession de la personne

ADR :

adresse de la résidence d'une personne, il s'agit d'un immeuble

NAPR :

numéro d'appartement

NOMEC :

nom d'une école, clé

ADEC :

adresse d'une école

NBCLASSES :

nombre de classes

DIR :

nom du directeur

NOMEC :

nom d'une école

NCL :

nom de la classe, e.g., CP1, CE2, CE3, etc...

MAITRE :

nom de l'instituteur

NBEL :

nombre d'élèves dans la classe

NOMP :

nom de la personne responsable de l'enfant, clé e.g., père, mère etc...

PRENOM :

prénom de l'enfant

AN :

année de naissance

NOMEC :

nom d'une école

NCL :

nom de la classe

4.2  Requêtes

Requête 1 :

Donner l'adresse des immeubles ayant plus de 10 étages et construits avant 1970.

pi_ADI (sigma_{NBETAGES > 10 /\ DATEC < 1970} IMMEUBLE)

     SELECT ADI
     FROM IMMEUBLE
     WHERE NBETAGES > 10 AND DATEC < 1970

Requête 2 :

Donner les noms des personnes qui habitent dans un immeuble dont ils sont propriétaires (occupants et habitants).

pi_NOM(PERSONNE _{NOM = PROP /\ ADR = ADI} IMMEUBLE)

     SELECT NOM
       FROM PERSONNE, IMMEUBLE
      WHERE NOM = PROP AND ADR = ADI

Requête 3 :

Donner les noms des personnes qui ne sont pas propriétaires.

pi_NOM(PERSONNE) - pi_PROP(IMMEUBLE)

     SELECT NOM
       FROM PERSONNE
     MINUS
     SELECT PROP
       FROM IMMEUBLE

ou

     SELECT NOM
       FROM PERSONNE 
      WHERE NOM NOT IN (SELECT PROP
                          FROM IMMEUBLE)

Requête 4 :

Donner les adresses des immeubles possédés par des informaticiens dont l'âge est inférieur à 40 ans.

R1	:=	sigmaAGE < 40 /\ PROF = 'INFORMATICIEN' PERSONNE
R2	:=	piADI(R1 NOM = PROP IMMEUBLE)

     SELECT ADI
     FROM PERSONNE, IMMEUBLE
     WHERE NOM = PROP AND AGE < 40 AND PROF = 'INFORMATICIEN'

Requête 5 :

Donner la liste des occupants (nom, âge, profession) des immeubles possédés par DUPONT.

pi_NOM,AGE,PROF(sigma_{PROP = 'DUPONT'}(IMMEUBLE) _{ADI = ADR}(PERSONNE))

     SELECT P.NOM, P.AGE, P.PROF
       FROM PERSONNE P, IMMEUBLE I, APPIM A
      WHERE I.ADI = A.ADI 
            AND I.PROP = 'DUPONT'
            AND P.NOM = A.OCCUP

Requête 6 :

Donner le nom et la profession des propriétaires d'immeubles où il y a des appartements vides.

R1	:=	piADI,NAPR(APPIM OCCUP = NOM PERSONNE)
R2	:=	piADI,NAPR APPIM - R1
R3	:=	piNOM,PROF(PERSONNE NOM = PROP (IMMEUBLE R2))

• R1(ADI,NAPR) : adresses et numéros d'appartement occupés
• R2(ADI,NAPR) : adresses et numéros d'appartement vides
• R3(NOM,PROF) : nom et profession des propriétaires d'immeubles avec des appartements vides

Avec valeur nulle :

    SELECT DISTINCT P.NOM, P.PROF
      FROM APPIM A, IMMEUBLE I, PERSONNE P
     WHERE P.NOM = I.PROP AND I.ADI = A.ADI 
           AND A.OCCUP IS NULL

Sans valeurs nulles :

    SELECT DISTINCT P.NOM, P.PROF
      FROM APPIM A, IMMEUBLE I, PERSONNE P
     WHERE P.NOM = I.PROP 
           AND I.ADI = A.ADI 
           AND NOT EXISTS (SELECT *
                             FROM PERSONNE O
                            WHERE O.ADR = I.ADI
                                  AND O.NAPR = A.NAPR)

Requête 7 :

Donner les noms des maîtres qui habitent dans le même immeuble (à la même adresse) qu'au moins un de leurs élèves (on suppose que les enfants vivent sous le même toit que leur responsable).

R1	:=	piADR,NOMEC,NCL,MAITRE(PERSONNE MAITRE = NOM CLASSE)
R2	:=	piADR,NOMEC,NCL(PERSONNE NOMP = NOM ENFANT)
R3	:=	piMAITRE (R1 R2)

• R1(...) : adresses des maîtres, écoles, classes et maîtres
• R2(...) : adresses des élèves, écoles, classes
• R3(MAITRE) : résultat

ou:

R1	:=	piADR,MAITRE(PERSONNE NOMP = NOM(ENFANT CLASSE))
R2	:=	piMAITRE(sigmaNOM = MAITRE(R1 PERSONNE))

• R1(ADR,MAITRE) : adresses des enfants et leurs maîtres
• R2(MAITRE) : résultat

     SELECT DISTINCT M.NOM
       FROM CLASSE C, PERSONNE M,
            ENFANT E, PERSONNE R
      WHERE C.MAITRE = M.NOM 
            AND E.NOMEC = C.NOMEC 
            AND E.NCL = C.NCL
            AND E.NOMP = R.NOM
            AND M.ADR = R.ADR

ou (imbriqué) :

     SELECT C.MAITRE
       FROM CLASSE C, PERSONNE P
      WHERE C.MAITRE = P.NOM AND P.ADR IN (SELECT P.ADR
                                             FROM PERSONNE P, ENFANT E
                                            WHERE P.NOM = E.NOMP AND
                                                  E.NOMEC = C.NOMEC AND
                                                  E.NCL = C.NCL)

Requête 8 :

Donner l'adresse de l'immeuble, la date de construction, le type d'appartement et l'étage où habitent chacun des maîtres des enfants de DUPONT.

R1	:=	piMAITRE(sigmaNOMP = `DUPONT' ENFANT CLASSE)
R2	:=	R1 MAITRE = NOM PERSONNE
R3	:=	piADI,DATEC,TYPE,ETAGE (R2 IMMEUBLE)

• R1(MAITRE) : les maîtres des enfants de DUPONT
• R2(...) : les noms, adresses, ... des maîtres
• R3(ADI,DATEC,TYPE,ETAGE) : résultat

     SELECT A.ADI, I.DATEC, A.TYPE, A.ETAGE
     FROM CLASSE C, ENFANT E, PERSONNE P, IMMEUBLE I, APPIM A
     WHERE I.ADI = P.ADR 
           AND A.NAPR = P.NAPR 
           AND A.ADI = I.ADI 
           AND P.NOM = C.MAITRE 
           AND C.NOMEC = E.NOMEC 
           AND C.NCL = E.NCL
           AND E.NOMP = `DUPONT'

Requête 9 :

Donner le nom et l'âge des maîtres qui habitent dans un immeuble dont le propriétaire est responsable d'un de leurs élèves.

R1	:=	piMAITRE,AGE,ADR,NOMP(ENFANT (CLASSE MAITRE = NOM PERSONNE))
R2	:=	piMAITRE,AGE (R1 NOMP = PROP /\ ADR=ADI IMMEUBLE)

• R1(MAITRE,AGE,ADR,NOMP) : pour chaque enfant : le nom, l'âge et l'adresse des maîtres et le nom du responsable
• R2(MAITRE,AGE) : résultat

     SELECT M.NOM, M.AGE
     FROM IMMEUBLE I, ENFANT E, CLASSE C, PERSONNE M
     WHERE I.ADI = M.ADR
           AND I.PROP = E.NOMP
           AND C.NCL = E.NCL 
           AND C.NOMEC = E.NOMEC
           AND M.NOM = C.MAITRE

Requête 10 :

Donner le nom et l'âge des personnes qui sont propriétaires mais qui ne sont ni maître ni directeur d'école.

R1	:=	rhoMAITRE/PROP(piMAITRE CLASSE)
R2	:=	rhoDIR/PROP(piDIR ECOLES)
R3	:=	piPROP IMMEUBLE - (R1 union R2)
R4	:=	piAGE,NOM(PERSONNE PROP = NOM R3)

  
      SELECT NOM, AGE
      FROM PERSONNE
      WHERE NOM IN (SELECT PROP
                      FROM IMMEUBLE
                    MINUS
                    (SELECT DIR
                       FROM ECOLE
                     UNION
                     SELECT MAITRE
                       FROM CLASSE))

4.3  Mise à jour

Requête 11 :

Ajouter un enfant de nom np, de prénom e, né en a et l'inscrire à la classe c de l'école ec.

      INSERT INTO ENFANT VALUE (np,e,a,ec,c);
      UPDATE CLASSE
      SET    NBEL = NBEL + 1
      WHERE  NOMEC = ec AND NCL = c;

4.4  Contraintes

• IMMEUBLE[PROP] inclus dans ou égal à PERSONNE[NOM]
• ECLOLE[DIR] inclus dans ou égal à PERSONNE[NOM]
• CLASSE[MAITRE] inclus dans ou égal à PERSONNE[NOM]
• ENFANT[NOMP] inclus dans ou égal à PERSONNE[NOM]