EXAMEN BD 14.1.2005
-----------------------------------------------

1a)

SELECT hostes.num, SUM(mida)
FROM hostes, pagines
WHERE pagines.hoste=hostes.nom
GROUP BY pagines.hoste

1b)

SELECT institucio, nom
FROM llocs
WHERE NOT EXISTS (
  SELECT * FROM hostes, pagines
  WHERE
    pagines.institucio = llocs.institucio AND
    pagines.nom = llocs.nom AND
    hostes.nom = pagines.hoste AND
    hostes.num = 1234
)

2a)

Projectes(
  num_proj PRIMARY KEY,
  nom_proj,
  pressupost,
  descripció
)

Empleats(
  codi_empl PRIMARY KEY,
  nom_empl,
  sou,
  num_dpt,
  num_proj REFERENCES Projectes(num_proj)
)

2b)

CREATE VIEW vProjectes(num_proj, nom_proj, pressupost, descripció) AS
  SELECT * FROM Projectes;
CREATE VIEW vEmpleats(codi_empl, sou, num_dpt, num_proj) AS
  SELECT * FROM Empleats;

Això ens dóna independència lògica.

2c)

Projectes(num_proj, nom_proj, pressupost, descripció)
Empleats(codi_empl, nom_empl, sou, num_dpt)
Assignacions(num_proj, codi_empl)

CREATE VIEW vProjectes(...) AS ...; -- sense canvis */
CREATE VIEW vEmpleats(codi_empl, sou, num_dpt, num_proj) AS
  SELECT Empleats.*, num_proj
  FROM Empleats, Assignacions
  WHERE Empleats.codi_empl=Assignacions.codi_empl

3a)

Algunes interferències que es produeixen són:
 - Actualització perduda (T1, T2).
 - Lectura no repetible (T2, T3).
 - Lectura no repetible (T3, T1).

No hi ha cap horari serials equivalent, ja que el graf de precedències
té cicles.

3b)

T1                T2                T3
---------         ---------         ---------
LOCK(A, X)
R(A)
                                    LOCK(A, S)
                  LOCK(A, X)        |        |
W(A)              |        |        |        |
LOCK(F, X)        |        |        |        |
R(F)              |        |        |        |
W(F)              |        |        |        |
LOCK(D, S)        |        |        |        |
R(D)              |        |        |        |
COMMIT + UNLOCK   |        |        |________|
                  |        |        R(A)
                  |________|        UNLOCK(A)
                  R(A)
                  LOCK(B, S)
                  LOCK(C, S)
                  W(A)
                                    LOCK(B, S)
                                    R(B)
                                    UNLOCK(B)
                                    LOCK(A, S)
                  COMMIT + UNLOCK   |________|
                                    R(A)
                                    UNLOCK(A)
                                    LOCK(E, X)
                                    R(E)
                                    W(E)
                                    COMMIT + UNLOCK

El graf d'espera just abans del commit de T1: T2->T1, T3->T1 .
I el graf d'espera just abans del commit de T2: T3->T2.

4)

Per comprovar que el preu sigui el correcte i actualitzar la informació:

CREATE FUNCTION processa_entrada() RETURNS trigger AS $$
DECLARE
  tipus_sessio Sessio.tipus_sessio%type;
  preu_esperat Entrada.preu_entrada%type;
  places_dispnibles Sessio.places_disponibles%type;
BEGIN
  places_disponibles := (
    SELECT places_disponibles
    FROM Sessio
    WHERE datahora_sessio=NEW.datahora_sessio
  );
  IF places_disponibles < 1 THEN
    RAISE EXCEPTION 'No queden places';
  END IF;
  tipus_sessio := (
    SELECT tipus_sessio FROM Sessio WHERE datahora_sessio=NEW.datahora_sessio);
  preu_esperat := (
    SELECT preu_base FROM Localitat WHERE fila=NEW.fila AND seient=NEW.seient);
  IF tipus_sessio = 'E' THEN
    IF preu_esperat > 50:
      preu_esperat := 0
    END IF;
  END IF;
  IF tipus_sessio = 'S' THEN
    preu_esperat := preu_esperat / 2;
  END IF
  IF preu_esperat != NEW.preu_entrada THEN
    RAISE EXCEPTION 'Preu incorrecte';
  END IF;
  UPDATE Sessio SET
    places_disponibles=places_disponibles - 1,
    recaptacio=recaptacio+preu_esperat
    WHERE datahora_sessio=NEW.datahora_sessio;
  RETURN NEW;
END;
$$ LANGUAGE PLpgSQL;

CREATE TRIGGER comprova_preu BEFORE INSERT ON Entrada
  FOR EACH ROW EXECUTE PROCEDURE processa_entrada();

Per comprovar que no hi hagi entrades repetides:

No cal fer res, la clau primària d'Entrada ja se n'encarrega.

5a)

39 < X < 41 < Y < 45

5b, 5c)

No aprenem a resoldre'ls al grau.