Exam 2018

Note

Examen de base de donnée de 2018

Warning

Les sujet change légérement d'une années à l'autre.

Sujet

Sujet 2018 PDF

Correction

Exercice 1.1 - Conception de base de données

Q 1.1)

Note

Contraintes de multiplicité« Un projet peut avoir au plus un budget» (cardinalité : 0..1) : NumProj −Ι−> budget

« Un projet emploie au moins un employé » (cardinalité : 1..) : NumProj −−>> NSS

« Un département peut concerner des projets » (cardinalité : 0..) : NumDept −Ι−>> NumProj

Dépendance fonctionnel :

Nom_ingrédient -> quantité, unité de mesure, saison, coût, valeur_énergique

numéro étape -> action, destination, durée

nom_ustensile

numéro_recette -> nom, catégorie, temps_de_préparation, temps_de_cuisson

Contraintes de valeur :

temps_de_cuisson >= 0

Containtes de multiplicité :

Recette 1..* -->> Ingrédient

Recette 1..* -->> Étape

Étape 0..1 -I-> Ingrédient

Étape 0..1 -I-> Usentsile

Action 0..1 -I-> Usentsile

Q 1.2)

Exercice 1.2 - Algèbre relationnelle et SQL

Q 2.1)

Note

Algebre relationnel :

σ = Selection

π = projection

⋈ = Jointure

∪ = Union

a) Y a-t-il des films dont l’année de sortie est antérieure à l’année de naissance du réalisateur ?

π titre (σ annéeDeSortie > annéeDeNaissance(Film ⋈ Personne)) réalisateur = idPersonne

SELECT *
FROM Film AS f, Personne AS p
WHERE f.réalisateur = p.idPersonne
AND p.annéeDeNaissance > f.annéeDeSortie

b) Y a-t-il des réalisateurs qui n’ont jamais fait jouer Chuck Norris dans aucun de leurs films ?

π réalisateur - π réalisateur (σ prenom == "Chuck" ^ nom == "Norris" (Film ⋈ Joue ⋈ Personne)) idFilm = film acteur = idPersonne

SELECT réalisateur
FROM Film
WHERE idFilm NOT IN(
  SELECT film
  FROM Joue
  WHERE acteur in (
    SELECT idPersonne
    FROM Personne
    Where prenom == "Chuck" and nom == "Norris"
  )
)

c) En SQL uniquement. Donnez l’ensemble des années de sortie des films de la base de données, avec, pour chaque année, le nombre de films sortis. Ordonnez le tout par ordre décroissant de nombre de films.

SELECT annéeDeSortie, COUNT(*) AS Nombre_de_films_sortis
FROM Film
GROUP BY annéeDeSortie
ORDER BY Nombre_de_films_sortis DESC;

d) Donnez la liste des couples d’acteurs (nom, prénom) ayant joué ensemble sur le même film (pensez à éliminer les couples constitués de deux acteurs identiques).

SELECT DISTINCT a1.nomPersonne, a1.prénomPersonne, a2.nomPersonne, a2.prénomPersonne
FROM Joue j1
JOIN Joue j2 ON j1.film = j2.film AND j1.acteur < j2.acteur
JOIN Personne a1 ON j1.acteur = a1.idPersonne
JOIN Personne a2 ON j2.acteur = a2.idPersonne;

Exercice 2.1 - Arbres B+

Pas d'exercice sur les arbre B+ en cours

Se référer aux exemple de cours

Exercice 2.2 - Evaluation de requêtes

SELECT E.Nom, E.Prénom
FROM Employés E, TravailleSur T, Projets P, Départements D
WHERE E.NSS = T.NSS AND T.PNo = P.PNo AND P.DeptNo = D.DeptNo
AND P.Ville = ‘Grenoble’ AND D.DNom = ‘Recherche’ ;