CREATE DATABASE IF NOT EXISTS nerva ;
USE nerva ;
DROP TABLE IF EXISTS CLIENT ;
DROP TABLE IF EXISTS EMPLOYE ;
DROP TABLE IF EXISTS PERSONNE_PHYSIQUE ;
DROP TABLE IF EXISTS FOURNISSEUR ;
DROP TABLE IF EXISTS TRANSPORTEUR ;
DROP TABLE IF EXISTS SOCIETE ;
DROP TABLE IF EXISTS ADRESSE ;
DROP TABLE IF EXISTS TELEPHONE ;
DROP TABLE IF EXISTS PERSONNE ;
DROP TABLE IF EXISTS SITE ;
DROP TABLE IF EXISTS TITRE ;
CREATE TABLE TITRE
(
titId INT,
titNom VARCHAR(48) NOT NULL,
CONSTRAINT TITRE_PK PRIMARY KEY(titId),
CONSTRAINT TITRE_AK UNIQUE(titNom)
);
INSERT INTO TITRE (titId, titNom)
VALUES
(1, 'titre a')
, (2, 'titre b')
, (3, 'titre c')
;
SELECT * FROM TITRE ;
CREATE TABLE SITE
(
siteId INT,
siteNom VARCHAR(48) NOT NULL,
CONSTRAINT SITE_PK PRIMARY KEY(siteId)
);
INSERT INTO SITE (siteId, siteNom)
VALUES
(1, 'site a')
, (2, 'site b')
, (3, 'site c')
;
SELECT * FROM SITE ;
CREATE TABLE PERSONNE
(
personneId INT,
CONSTRAINT PERSONNE_PK PRIMARY KEY(personneId)
);
INSERT INTO PERSONNE (personneId)
VALUES
(1)
, (2)
, (3)
, (4)
, (5)
, (6)
, (7)
, (8)
, (9)
, (11)
, (12)
, (13)
, (14)
, (15)
, (16)
, (17)
, (18)
, (19)
;
SELECT * FROM PERSONNE ;
CREATE TABLE ADRESSE
(
adresseId INT,
adresseLigne1 VARCHAR(48) NOT NULL,
adresseLigne2 VARCHAR(48) NOT NULL,
codePostal VARCHAR(5) NOT NULL,
localite VARCHAR(48) NOT NULL,
personneId INT NOT NULL,
CONSTRAINT ADRESSE_PK PRIMARY KEY(adresseId),
CONSTRAINT ADRESSE_PERSONNE_FK FOREIGN KEY(personneId)
REFERENCES PERSONNE(personneId)
ON DELETE CASCADE
);
INSERT INTO ADRESSE (personneId, adresseId, codePostal, localite, adresseLigne1, adresseLigne2)
VALUES
(1, 1, '75001', 'Paris', 'Ets Fernand Naudin', '3 rue en pente')
, (2, 2, '75008', 'Paris', 'Volfoni frères', 'Quai des péniches')
;
SELECT * FROM ADRESSE ;
CREATE TABLE TELEPHONE
(
telephoneId INT,
telephoneNumero VARCHAR(24) NOT NULL,
personneId INT NOT NULL,
CONSTRAINT TELEPHONE_PK PRIMARY KEY(telephoneId),
CONSTRAINT TELEPHONE_PERSONNE_FK FOREIGN KEY(personneId)
REFERENCES PERSONNE(personneId)
ON DELETE CASCADE
);
CREATE TABLE SOCIETE
(
personneId INT,
siret DECIMAL(14,0) NOT NULL,
raisonSociale VARCHAR(48) NOT NULL,
contact VARCHAR(48) NOT NULL,
CONSTRAINT SOCIETE_PK PRIMARY KEY(personneId),
CONSTRAINT SOCIETE_AK UNIQUE(siret),
CONSTRAINT SOCIETE_PERSONNE_FK FOREIGN KEY(personneId)
REFERENCES PERSONNE(personneId)
ON DELETE CASCADE
);
INSERT INTO SOCIETE (personneId, siret, raisonSociale, contact)
VALUES
(1, '12345678901234', 'Ets Naudin', 'Mle Noelle Joyeux')
, (2, '23456789012345', 'Commerces divers', 'Mme Mado')
, (3, '45678901234567', 'Les expéditions rapides', 'M. Marc')
, (4, '34567890123456', 'A la bonne roulette', 'M. Luc')
;
SELECT * FROM SOCIETE ;
CREATE TABLE FOURNISSEUR
(
personneId INT,
tauxRemise DECIMAL(4,2) NOT NULL,
CONSTRAINT FOURNISSEUR_PK PRIMARY KEY(personneId),
CONSTRAINT FOURNISSEUR_SOCIETE_FK
FOREIGN KEY(personneId) REFERENCES SOCIETE(personneId)
ON DELETE CASCADE
);
INSERT INTO FOURNISSEUR (personneId, tauxRemise)
VALUES
(1, 10)
, (2, 0)
, (4, 5)
;
SELECT * FROM FOURNISSEUR ;
CREATE TABLE TRANSPORTEUR
(
personneId INT,
CONSTRAINT TRANSPORTEUR_PK PRIMARY KEY(personneId),
CONSTRAINT TRANSPORTEUR_SOCIETE_FK FOREIGN KEY(personneId)
REFERENCES SOCIETE(personneId)
ON DELETE CASCADE
);
INSERT INTO TRANSPORTEUR (personneId)
VALUES
(3)
;
SELECT * FROM TRANSPORTEUR ;
CREATE TABLE PERSONNE_PHYSIQUE
(
personneId INT,
nom VARCHAR(48) NOT NULL,
prenom VARCHAR(48) NOT NULL,
dateEntree DATE NOT NULL,
dateNaissance DATE NOT NULL,
titId INT NOT NULL,
societeId INT NOT NULL,
CONSTRAINT PERSONNE_PHYSIQUE_PK PRIMARY KEY(personneId),
CONSTRAINT PERSONNE_PHYSIQUE_PERSONNE_FK FOREIGN KEY(personneId)
REFERENCES PERSONNE (personneId)
ON DELETE CASCADE,
CONSTRAINT PERSONNE_PHYSIQUE_SOCIETE_FK FOREIGN KEY(societeId)
REFERENCES SOCIETE (personneId),
CONSTRAINT PERSONNE_PHYSIQUE_TITRE_FK FOREIGN KEY(titId)
REFERENCES TITRE (titId)
);
INSERT INTO PERSONNE_PHYSIQUE (personneId, societeId, nom, prenom, dateEntree, dateNaissance, titId)
VALUES
(11, 1, 'Naudin', 'Fernand', '1962-10-21', '1919-07-14', 1)
, (12, 2, 'Volfoni', 'Raoul', '1962-12-14', '1916-01-11', 2)
, (13, 2, 'Volfoni', 'Paul', '1962-12-14', '1919-10-03', 2)
, (14, 1, 'Folace', 'Francis', '1962-10-22', '1921-07-20', 3)
, (15, 1, 'Jean', 'Jean', '1962-10-22', '1903-07-19', 3)
, (16, 2, 'Pascal', 'Pascal', '1963-01-07', '1930-04-17', 3)
, (17, 2, 'Bastien', 'Mac', '1963-01-07', '1913-06-20', 3)
, (18, 3, 'Théo', 'Théo', '1963-01-07', '1929-07-28', 3)
, (19, 4, 'Tomate', 'Tomate', '1962-12-21', '1914-07-22', 3)
;
SELECT * FROM PERSONNE_PHYSIQUE ;
CREATE TABLE EMPLOYE
(
personneId INT,
numSS CHAR(15) NOT NULL,
siteId INT NOT NULL,
CONSTRAINT EMPLOYE_PK PRIMARY KEY(personneId),
CONSTRAINT EMPLOYE_AK UNIQUE(numSS),
CONSTRAINT EMPLOYE_PERSONNE_PHYSIQUE_FK FOREIGN KEY(personneId)
REFERENCES PERSONNE_PHYSIQUE(personneId)
ON DELETE CASCADE,
CONSTRAINT EMPLOYE_SITE_FK FOREIGN KEY(siteId)
REFERENCES SITE(siteId)
);
INSERT INTO EMPLOYE (personneId, numSS, siteId)
VALUES
(11, '123456789012345', 1)
, (12, '234567890123451', 2)
, (13, '345678901234512', 2)
, (14, '456789012345123', 1)
, (15, '567890123451234', 1)
, (16, '678901234512345', 2)
, (17, '789012345123456', 2)
;
SELECT * FROM EMPLOYE ;
CREATE TABLE CLIENT
(
personneId INT,
CONSTRAINT CLIENT_PK PRIMARY KEY(personneId),
CONSTRAINT CLIENT_PERSONNE_PHYSIQUE_FK FOREIGN KEY(personneId)
REFERENCES PERSONNE_PHYSIQUE(personneId)
ON DELETE CASCADE
);
INSERT INTO CLIENT (personneId)
VALUES
(18)
, (19)
;
SELECT * FROM CLIENT ;
ROLLBACK ;
Partager