Base de données cinématographique⚓︎

D'après 2022, Métropole, J1, Ex. 2

On pourra utiliser les mots clés SQL suivants : SELECT, FROM, WHERE, JOIN, ON, INSERT, INTO, VALUES, UPDATE, SET, AND.

Nous allons étudier une base de données traitant du cinéma dont voici le schéma relationnel qui comporte 3 relations :

la relation individu (id_ind, nom, prenom, naissance)
la relation realisation (id_rea, titre, annee, type)
la relation emploi (id_emp, description, #id_ind, #id_rea)

Les clés primaires sont soulignées et les clés étrangères sont précédées d'un #.
Ainsi emploi.id_ind est une clé étrangère faisant référence à individu.id_ind.

Voici un extrait des tables individu et realisation :

Extrait de individu

`id_ind`	`nom`	`prenom`	`naissance`
`105`	`'Hulka'`	`'Daniel'`	`'01-06-1968'`
`403`	`'Travis'`	`'Daniel'`	`'10-03-1968'`
`688`	`'Crog'`	`'Daniel'`	`'07-07-1968'`
`695`	`'Pollock'`	`'Daniel'`	`'24-08-1968'`

Extrait de realisation

`id_rea`	`titre`	`annee`	`type`
`105`	`'Casino Imperial'`	`2006`	`'action'`
`325`	`'Ciel tombant'`	`2012`	`'action'`
`655`	`'Fantôme'`	`2015`	`'action'`
`950`	`'Mourir pour attendre'`	`2021`	`'action'`

1. On s'intéresse ici à la récupération de données dans une relation.

1.a. Décrire ce que renvoie la requête ci-dessous :

🗂️ Requête SQL

SELECT nom, prenom, naissance
FROM individu
WHERE nom = 'Crog';

Réponse

La requête renvoie les nom, prénom et date de naissance de tous les individus qui portent Crog comme nom de famille. Dans la mesure où l'on ne fournit que des extraits des tables, on ne peut pas fournir le résultat de cette requête de façon certaine.

1.b. Fournir une requête SQL permettant de récupérer le titre et la clé primaire de chaque film dont la date de sortie est strictement supérieure à 2020.

Réponse

🗂️ Requête SQL

SELECT titre, id_rea
FROM realisation
WHERE annee > 2020;

2. Cette question traite de la modification de relations.

2.a. Dire s'il faut utiliser la requête 1 ou la requête 2 proposées ci-dessous pour modifier la date de naissance de Daniel Crog. Justifier votre réponse en expliquant pourquoi la requête refusée ne pourra pas fonctionner.

🗂️ Requête SQL 1

UPDATE individu
SET naissance = '02-03-1968'
WHERE id_ind = 688 AND nom = 'Crog' AND prenom = 'Daniel';

🗂️ Requête SQL 2

INSERT INTO individu
VALUES (688, 'Crog', 'Daniel', '02-03-1968');

Réponse

Compte tenu de l'extrait fourni de la table individu, l'identifiant 688 est déjà utilisé pour un enregistrement et il ne peut pas y avoir de doublon pour les clés primaires, ainsi la requête 2 provoquera une erreur.

La requête 1 est correcte.

Bien que valide cette requête peut être simplifiée en n'utilisant que la clé primaire de la table :

🗂️ Requête SQL 1

UPDATE individu
SET naissance = '02-03-1968'
WHERE id_ind = 688;

2.b. Expliquer si la relation individu peut accepter (ou pas) deux individus portant le même nom, le même prénom et la même date de naissance.

Réponse

Aucun des champs correspondant ne possède la contrainte UNIQUE (hypothèse réaliste). Les deux individus n'auront donc pas le même identifiant ! Ainsi, oui, la relation individu peut accepter deux tels individus.

3. Cette question porte sur la notion de clés étrangères.

3.a. Recopier sur votre copie les demandes ci-dessous, dans leur intégralité, et les compléter correctement pour qu'elles ajoutent dans la relation emploi les rôles de Daniel Crog en tant que James Bond dans le film nommé 'Casino Impérial' puis dans le film 'Ciel tombant'.

🗂️ Requête SQL

INSERT INTO emploi
VALUES (5400, 'Acteur(James Bond)', ... , ... );

INSERT INTO emploi
VALUES (5401, 'Acteur(James Bond)', ... , ...);

Réponse

🗂️ Requête SQL

INSERT INTO emploi
VALUES (5400, 'Acteur(James Bond)', 688, 105);

INSERT INTO emploi
VALUES (5401, 'Acteur(James Bond)', 688, 325);

3.b. On désire rajouter un nouvel emploi de Daniel Crog en tant que James Bond dans le film 'Docteur Yes'.
Expliquer si l'on doit d'abord créer l'enregistrement du film dans la relation realisation ou si l'on doit d'abord créer le rôle dans la relation emploi.

Réponse

Il faut d'abord créer l'enregistrement du film dans la relation realisation, car l'identifiant du film doit être connu afin d'être utilisé comme clé étrangère dans la relation emploi.

4. Cette question traite des jointures.

4.a. Recopier sur votre copie la requête SQL ci-dessous, dans son intégralité, et la compléter de façon à ce qu'elle renvoie le nom de l'acteur, le titre du film et l'année de sortie du film, à partir de tous les enregistrements de la relation emploi pour lesquels la description de l'emploi est 'Acteur(James Bond)'.

🗂️ Requête SQL

SELECT ...
FROM emploi
JOIN individu ON ...
JOIN realisation ON ...
WHERE emploi.description = 'Acteur(James Bond)';

Réponse

🗂️ Requête SQL

SELECT nom, titre, annee
FROM emploi
JOIN individu ON emploi.id_ind = individu.id_ind
JOIN realisation ON emploi.id_rea = realisation.id_rea
WHERE emploi.description = 'Acteur(James Bond)';

4.b. Fournir une requête SQL permettant de trouver toutes les descriptions des emplois de Denis Johnson (Denis est son prénom et Johnson est son nom).
On veillera à n'afficher que la description des emplois et non les films associés à ces emplois.

Réponse

🗂️ Requête SQL

SELECT description
FROM emploi
JOIN individu ON emploi.id_ind = individu.id_ind
WHERE prenom = 'Denis' AND nom = 'Johnson';