TP 4 QualiBD

Modélisation Non First Normal Form

Partie 1. Création de relations par « désimbrication »

Question 1. Donnez la relation obtenue suite aux opérations de désimbrication suivantes

a) UNNEST(MATCH ; joueur1) = MATCH_1 :

b) UNNEST(MATCH_1 ; joueur2) = MATCH_2 :

c) UNNEST(MATCH_2 ; equipe1) = MATCH_3 :

d) UNNEST(MATCH_3 ; equipe2) = MATCH_4 :

Question 2. Donnez les commandes SQL de création de la table MATCH_4 obtenue.

CREATE TABLE Match_4 (
    numeroMatch INT NOT NULL,
    stade VARCHAR(50) NOT NULL,
    numeroEquipe1 CHAR(3) NOT NULL,
    nomEquipe1 VARCHAR(50) NOT NULL,
    scoreEquipe1 INT NOT NULL,
    prenomJoueur1 VARCHAR(50) NOT NULL,
    nomJoueur1 VARCHAR(50) NOT NULL,
    maillotJoueur1 INT NOT NULL,
    numeroEquipe2 CHAR(3) NOT NULL,
    nomEquipe2 VARCHAR(50) NOT NULL,
    scoreEquipe2 INT NOT NULL,
    prenomJoueur2 VARCHAR(50) NOT NULL,
    nomJoueur2 VARCHAR(50) NOT NULL,
    maillotJoueur2 INT NOT NULL,
    PRIMARY KEY (prenomJoueur1, nomJoueur1, prenomJoueur2, nomJoueur2, numeroMatch)
);

Question 3. Insérez dans la table MATCH_4 les enregistrements issus du match nm=30 présenté dans la table initiale.

INSERT INTO Match_4 VALUES (30, 'Stade de France', 'FRA', 'France', 41, 'Thomas', 'Ramos', 15, 'WAL', 'Pays de Galles', 28, 'Dan','Biggar', 10);

INSERT INTO Match_4 VALUES (30, 'Stade de France', 'FRA', 'France', 41, 'Romain', 'Ntamack', 10, 'WAL', 'Pays de Galles', 28, 'Dan','Biggar', 10);

INSERT INTO Match_4 VALUES (30, 'Stade de France', 'FRA', 'France', 41, 'Antoine', 'Dupont', 9, 'WAL', 'Pays de Galles', 28, 'Dan','Biggar', 10);

INSERT INTO Match_4 VALUES (30, 'Stade de France', 'FRA', 'France', 41, 'Thomas', 'Ramos', 15, 'WAL', 'Pays de Galles', 28, 'Georges','North', 13);

INSERT INTO Match_4 VALUES (30, 'Stade de France', 'FRA', 'France', 41, 'Romain', 'Ntamack', 10, 'WAL', 'Pays de Galles', 28, 'Georges','North', 13);

INSERT INTO Match_4 VALUES (30, 'Stade de France', 'FRA', 'France', 41, 'Antoine', 'Dupont', 9, 'WAL', 'Pays de Galles', 28, 'Georges','North', 13);

Question 4. Proposez une forme 3NF de la relation MATCH_4 en considérant les dépendances fonctionnelles suivantes

la couverture minimale pour ces dépendances est :

{nm} → {stade, ne1, ne2, scr1, scr2} {ne} → {nome} {prej, nomj} → {ne} {nm, prej, nomj} → {maillot} {nm, prej, nomj} → {ne}

Question 5. Donnez les commandes SQL de création des nouvelles tables obtenues.

Création

CREATE TABLE Equipe (
  ne INT PRIMARY KEY,
  nome VARCHAR(255),
  scr INT
);

CREATE TABLE Joueur (
  prej VARCHAR(255),
  nomj VARCHAR(255),
  ne INT REFERENCES Equipe(ne),
  maillot INT,
  PRIMARY KEY (prej, nomj)
);

CREATE TABLE Match (
  nm INT PRIMARY KEY,
  stade VARCHAR(255),
  ne1 INT REFERENCES Equipe(ne),
  ne2 INT REFERENCES Equipe(ne),
  scr1 INT,
  scr2 INT
);

Question 6.Insérez dans les tables obtenues les enregistrements issus du match nm=30 présenté dans la table initiale

Insertion

-- Insérer dans la table Equipe
INSERT INTO Equipe (ne, nome, scr)
VALUES (1, 'France', 41),
       (2, 'Pays de Galles', 28);

-- Insérer dans la table Joueur
INSERT INTO Joueur (prej, nomj, ne, maillot)
VALUES ('Thomas', 'Ramos', 1, 15),
       ('Romain', 'Ntamack', 1, 10),
       ('Antoine', 'Dupont', 1, 9),
       ('Georges', 'North', 2, 13),
       ('Dan', 'Biggar', 2, 10);

-- Insérer dans la table Match
INSERT INTO Match (nm, stade, ne1, ne2, scr1, scr2)
VALUES (30, 'Stade de France', 1, 2, 30, 2);