4. Modification du schéma relationnel de la base de données


1- Créer un index sur la colonne ville de la table entraîneur.

CREATE INDEX 
	ville_idx 
ON 
	Entraineur( VilleEntr )

2- Créer une vue VNBC contenant le nombre de coureurs
entraînés par chaque entraîneur (Nomentraineur, nbcoureurs)

CREATE VIEW VBNC AS
	SELECT
		NomEntr
		, COUNT(NumCoureur) AS NbCoureurs
	FROM
		Entraineur
	INNER JOIN
		Coureur
	ON
		Coureur.NumEntr = Entraineur.NumEntr
	GROUP BY
		NomEntr


3- Créer une vue VLION contenant tous les coureurs ayant
participé à la course «le lion 2005 » (numcoureur,
nomcoureur, ordre)

CREATE VIEW VLION AS
SELECT
	Participe.NumCoureur
	, Coureur.NomCoureur
	, Participe.Ordre
FROM
	Participe
JOIN
	Coureur
ON
	Coureur.NumCoureur = Participe.NumCoureur	
WHERE
	NumCourse = (	SELECT 
				NumCourse
			FROM
				Course
			WHERE
				Datecourse LIKE '%/05'
			AND
				LibCourse = 'Course du Lion' );


4- Ajouter un coureur dans la table coureur. Effet sur la vue
VNBC

INSERT INTO Coureur
VALUES (12, 4, 'PAPET', 'LYON');

Incrémentation du nombre de coureurs pour le l'entraineur num. 4 (dans ce cas ici)


5- A l'aide d'une requête, créer une table TCOURBEL contenant
tous les coureurs de Belfort.

CREATE TABLE TCOURBEL (NomCoureur VARCHAR2(128));
INSERT INTO TCOURBEL VALUES
('PARISSE');
INSERT INTO TCOURBEL VALUES
('DALO');
INSERT INTO TCOURBEL VALUES
('MARTIN')
;

(C'est un peu de la triche ...)


6- Créer une vue VCOURBEL contenant tous les coureurs de
Belfort.

CREATE VIEW VCOURBEL AS
SELECT
	NomCoureur
FROM
	Coureur
WHERE
	VilleCoureur = 'BELFORT';



7- Ajouter un coureur habitant Belfort dans la table coureur.
Effet sur la table TCOURBEL et sur la vue VCOURBEL ?

Directement mis à jour dans la vue, alors que cela nécessite une mise à jour via un ajout dans la table TCOURBEL.

8- Supprimez un coureur dans la vue VCOURBEL.
Effet sur la table COUREUR et sur la vue VCOURBEL ?

DELETE FROM
	Participe
WHERE
	NumCoureur = (SELECT NumCoureur FROM Coureur WHERE NomCoureur = 'MARTIN');

DELETE FROM
	VCOURBEL
WHERE
	NomCoureur = 'MARTIN';

Ceci provoque évidemment une suppression dans la vue, mais aussi dans la table Coureur.


9- Supprimer un coureur dans la table TCOURBEL.
Effet sur la table COUREUR et sur la vue VCOURBEL ?

Aucun.

10- Ajoutez un coureur dans la vue VCOURBEL. Effet sur la table
COUREUR ?

Impossible, car les contraintes sur le systeme empêchent l'ajout de valeur nulles dans la table coureur.


11- Afficher le texte SQL définissant la vue VCOURBEL

DESCRIBE VCOURBEL;


12- Augmentez la taille de la colonne nomcoureur dans la table
COUREUR.

ALTER TABLE
	Coureur
MODIFY (NomCoureur VARCHAR2(256));



13- Ajoutez les colonnes Date de naissance et Email dans la
table COUREUR. Effet sur la vue VCOURBEL ?
SI nécessaire modifier la vue pour afficher ces deux
colonnes

ALTER TABLE
	Coureur
ADD (DateNaissCoureur DATE);

ALTER TABLE
	Coureur
ADD (MailCoureur VARCHAR2(512));

Aucun effet sur VCOURBEL (qui ne s'intéresse qu'à la ville).

DROP VIEW VCOURBEL;

CREATE VIEW VCOURBEL AS
SELECT
	NomCoureur
	, DateNaissCoureur
	, MailCoureur
FROM
	Coureur
WHERE
	VilleCoureur = 'BELFORT';


14- Modifiez l'email de tous les coureurs en affectant comme
adresse de mél : nom@utbm.fr

UPDATE
	Coureur
SET
	MailCoureur = CONCAT(NomCoureur, '@utbm.fr');

15- Supprimer la colonne Email dans la table Coureur

ALTER TABLE
	Coureur
DROP COLUMN	
	MailCoureur;


16- Supprimez la table TCOURBEL. Quelle est la conséquence de
cette suppression ?

DROP TABLE TCOURBEL;

Aucune (Si ce n'est la disparition de la table TCOURBEL).