Envoyé par
Nerva
je suis perdu car je ne sais pas comment dissocier clients et employés lors de la création de fiches...
Voyons comment procéder au moins avec MySQL seul...
Tout d’abord, vous ne pouvez pas créer de clé alternative pour la table PERSONNE au moyen d’une combinaison des attributs naturels PER_NOM, PER_PRENOM, PER_DATE_NAIS, PER_DATE_ENTREE, car rien n’interdit que deux personnes puissent avoir même nom, même prénom, être nées le même jour et avoir été embauchées le même jour. Autrement dit effectuer des SELECT dans les tables dépendant de PERSONNE n’est pas déterministe, c’est quand même embêtant...
Le plus sage est de mettre en oeuvre un code (attribut PER_CODE ci-dessous) qui soit clé alternative.
Allons-y :
CREATE TABLE TITRE
(
TIT_ID INT AUTO_INCREMENT,
TIT_LIB VARCHAR(4) NOT NULL,
CONSTRAINT T_TITRES_PK PRIMARY KEY(TIT_ID),
CONSTRAINT T_TITRES_AK UNIQUE(TIT_LIB)
);
INSERT INTO TITRE (TIT_LIB)
VALUES
('Mme'), ('M.'), ('Mlle'), ('Dr'), ('Me')
;
SELECT TIT_LIB FROM TITRE ;
=>
TIT_LIB
Mme
M.
Mlle
Dr
Me
CREATE TABLE PERSONNE
(
PER_ID INT AUTO_INCREMENT,
PER_CODE CHAR(8) NOT NULL,
TIT_ID INT NOT NULL,
PER_NOM VARCHAR(48) NOT NULL,
PER_PRENOM VARCHAR(48) NOT NULL,
PER_DATE_NAIS DATE NOT NULL,
PER_DATE_ENTREE DATE NOT NULL,
CONSTRAINT PERSONNE_PK PRIMARY KEY (PER_ID),
CONSTRAINT PERSONNE_AK UNIQUE (PER_CODE),
CONSTRAINT PERSONNE_TITRE_FK FOREIGN KEY(TIT_ID) REFERENCES TITRE (TIT_ID)
);
INSERT INTO PERSONNE (PER_CODE, PER_NOM, PER_PRENOM, PER_DATE_ENTREE, PER_DATE_NAIS, TIT_ID)
VALUES
('NAU00001', 'Naudin', 'Fernand', '1962-10-21', '1919-07-14',(SELECT TIT_ID FROM TITRE WHERE TIT_LIB = 'M.'))
, ('VOL00001', 'Volfoni', 'Raoul', '1962-12-14', '1916-01-11',(SELECT TIT_ID FROM TITRE WHERE TIT_LIB = 'M.'))
, ('VOL00002', 'Volfoni', 'Paul', '1962-12-14', '1919-10-03',(SELECT TIT_ID FROM TITRE WHERE TIT_LIB = 'M.'))
, ('FOL00001', 'Folace', 'Francis', '1962-10-22', '1921-07-20', (SELECT TIT_ID FROM TITRE WHERE TIT_LIB = 'Me'))
, ('JEA00001', 'Jean', 'Jean', '1962-10-22', '1903-07-19',(SELECT TIT_ID FROM TITRE WHERE TIT_LIB = 'M.'))
, ('PAT00001', 'Patricia', 'Patricia', '1962-10-22', '1942-08-18',(SELECT TIT_ID FROM TITRE WHERE TIT_LIB = 'Mlle'))
, ('ANT00001', 'Antoine', 'Delafoy', '1962-10-22', '1929-02-08',(SELECT TIT_ID FROM TITRE WHERE TIT_LIB = 'M.'))
, ('PAS00001', 'Pascal', 'Pascal', '1963-01-07', '1930-04-17',(SELECT TIT_ID FROM TITRE WHERE TIT_LIB = 'M.'))
, ('BAS00001', 'Bastien', 'Mac', '1963-01-07', '1913-06-20',(SELECT TIT_ID FROM TITRE WHERE TIT_LIB = 'M.'))
, ('THE00001', 'Théo', 'Théo', '1963-01-07', '1929-07-28',(SELECT TIT_ID FROM TITRE WHERE TIT_LIB = 'M.'))
, ('MAD00001', 'Mado', 'Mado', '1963-01-07', '1919-01-27',(SELECT TIT_ID FROM TITRE WHERE TIT_LIB = 'Mme'))
, ('TOM00001', 'Tomate', 'Tomate', '1962-12-21', '1914-07-22',(SELECT TIT_ID FROM TITRE WHERE TIT_LIB = 'M.'))
;
SELECT PER_CODE, TIT_LIB, PER_NOM, PER_PRENOM, PER_DATE_ENTREE, PER_DATE_NAIS
FROM PERSONNE as x JOIN TITRE as y ON x.TIT_ID = y.TIT_ID
;
=>
PER_CODE TIT_LIB PER_NOM, PER_PRENOM PER_DATE_ENTREE PER_DATE_NAIS
NAU00001 M. Naudin Fernand 1962-10-21 1919-07-14
VOL00001 M. Volfoni Raoul 1962-12-14 1916-01-11
VOL00002 M. Volfoni Paul 1962-12-14 1919-10-03
FOL00001 Me Folace Francis 1962-10-22 1921-07-20
JEA00001 M. Jean Jean 1962-10-22 1903-07-19
PAT00001 Mlle Patricia Patricia 1962-10-22 1942-08-18
ANT00001 M. Antoine Delafoy 1962-10-22 1929-02-08
PAS00001 M. Pascal Pascal 1963-01-07 1930-04-17
BAS00001 M. Bastien Mac 1963-01-07 1913-06-20
THE00001 M. Théo Théo 1963-01-07 1929-07-28
MAD00001 Mme Mado Mado 1963-01-07 1919-01-27
TOM00001 M. Tomate Tomate 1962-12-21 1914-07-22
CREATE TABLE ADRESSE
(
ADR_ID INT AUTO_INCREMENT,
ADR_L1 VARCHAR(48) NOT NULL,
ADR_L2 VARCHAR(48) NOT NULL,
ADR_CP CHAR(5) NOT NULL,
ADR_LOC VARCHAR(48) NOT NULL,
PER_ID INT NOT NULL,
CONSTRAINT ADRESSE_PK PRIMARY KEY(ADR_ID),
CONSTRAINT ADRESSE_PERSONNE_FK FOREIGN KEY(PER_ID)
REFERENCES PERSONNE (PER_ID)
);
INSERT INTO ADRESSE (PER_ID, ADR_CP, ADR_LOC, ADR_L1, ADR_L2)
VALUES
((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'NAU00001')
, '75001', 'Paris', 'Ets Fernand Naudin', '3, rue en pente')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'VOL00001')
, '75008', 'Paris', 'Volfoni frères', 'Quai des péniches')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'VOL00002')
, '75008', 'Paris', 'Volfoni frères', 'Quai des péniches')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'FOL00001')
, '75008', 'Paris', 'Aux bons soins des Ets Fernand Naudin', '3, rue en pente')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'JEA00001')
, '75008', 'Paris', 'Aux bons soins des Ets Fernand Naudin', '3, rue en pente')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'PAS00001')
, '75008', 'Paris', 'Chez Volfoni frères', 'Quai des péniches')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'BAS00001')
, '75008', 'Paris', 'Chez Volfoni frères', 'Quai des péniches')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'MAD00001')
, '75008', 'Paris', 'Chez Volfoni frères', 'Quai des péniches')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'PAT00001')
, '75008', 'Paris', 'Chez M. Fernand Naudin', '3, rue en pente')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'ANT00001')
, '75008', 'Paris', 'Chez M. Fernand Naudin', '3, rue en pente')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'TOM00001')
, '78999', 'Trifoully', 'Chez Tomate', '5, rue des clandés')
;
SELECT PER_CODE, ADR_CP, ADR_LOC, ADR_L1, ADR_L2
FROM ADRESSE as x JOIN PERSONNE as y ON x.PER_ID = y.PER_ID
;
=>
PER_CODE ADR_CP ADR_LOC ADR_L1 ADR_L2
NAU00001 75001 Paris Ets Fernand Naudin 3, rue en pente
VOL00001 75008 Paris Volfoni frères Quai des péniches
VOL00002 75008 Paris Volfoni frères Quai des péniches
FOL00001 75008 Paris Aux bons soins des Ets Fernand Naudin 3, rue en pente
JEA00001 75008 Paris Aux bons soins des Ets Fernand Naudin 3, rue en pente
PAS00001 75008 Paris Chez Volfoni frères Quai des péniches
BAS00001 75008 Paris Chez Volfoni frères Quai des péniches
MAD00001 75008 Paris Chez Volfoni frères Quai des péniches
PAT00001 75008 Paris Chez M. Fernand Naudin 3, rue en pente
ANT00001 75008 Paris Chez M. Fernand Naudin 3, rue en pente
TOM00001 78999 Trifoully Chez Tomate, 5, rue des clandés
CREATE TABLE TELEPHONE
(
TEL_ID INT AUTO_INCREMENT,
TEL_NUM CHAR(10) NOT NULL,
PER_ID INT NOT NULL,
CONSTRAINT TELEPHONE_PK PRIMARY KEY(TEL_ID),
CONSTRAINT TELEPHONE_PERSONNE_FK FOREIGN KEY (PER_ID)
REFERENCES PERSONNE (PER_ID)
);
INSERT INTO TELEPHONE (PER_ID, TEL_NUM)
VALUES
((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'NAU00001'), '0600000001')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'NAU00001'), '0600000002')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'VOL00001'), '0600000003')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'VOL00002'), '0600000004')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'FOL00001'), '0600000005')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'JEA00001'), '0600000006')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'JEA00001'), '0600000007')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'PAS00001'), '0600000008')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'BAS00001'), '0600000009')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'PAT00001'), '0600000010')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'ANT00001'), '0600000011')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'TOM00001'), '0600000012')
;
SELECT PER_CODE, TEL_NUM
FROM TELEPHONE as x JOIN PERSONNE as y ON x.PER_ID = y.PER_ID
;
=>
PER_CODE TEL_NUM
NAU00001 0600000001
NAU00001 0600000002
VOL00001 0600000003
VOL00002 0600000004
FOL00001 0600000005
JEA00001 0600000006
JEA00001 0600000007
PAS00001 0600000008
BAS00001 0600000009
PAT00001 0600000010
ANT00001 0600000011
TOM00001 0600000012
CREATE TABLE EMPLOYE
(
PER_ID INT NOT NULL,
EMP_NUM_SS CHAR(15) NOT NULL,
CONSTRAINT EMPLOYE_PK PRIMARY KEY (PER_ID),
CONSTRAINT EMPLOYES_AK UNIQUE (EMP_NUM_SS),
CONSTRAINT EMPLOYE_PERSONNE_FK FOREIGN KEY (PER_ID)
REFERENCES PERSONNE (PER_ID)
ON DELETE CASCADE
);
INSERT INTO EMPLOYE (PER_ID, EMP_NUM_SS)
VALUES
((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'NAU00001'), '119078200000783')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'FOL00001'), '121077500514586')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'JEA00001'), '103077937012355')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'PAT00001'), '242085700200444')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'ANT00001'), '129026700014801')
;
SELECT PER_CODE, PER_NOM, PER_PRENOM, EMP_NUM_SS
FROM PERSONNE as x JOIN EMPLOYE as y ON x.PER_ID = y.PER_ID
;
=>
PER_CODE PER_NOM PER_PRENOM EMP_NUM_SS
JEA00001 Jean Jean 103077937012355
NAU00001 Naudin Fernand 119078200000783
FOL00001 Folace Francis 121077500514586
ANT00001 Antoine Delafoy 129026700014801
PAT00001 Patricia Patricia 242085700200444
CREATE TABLE CLIENT
(
PER_ID INT NOT NULL,
CLI_NUM VARCHAR(16) NOT NULL,
CONSTRAINT CLIENT_PK PRIMARY KEY (PER_ID),
CONSTRAINT CLIENT_AK UNIQUE (CLI_NUM),
CONSTRAINT CLIENT_PERSONNE_FK FOREIGN KEY (PER_ID)
REFERENCES PERSONNE (PER_ID)
ON DELETE CASCADE
);
INSERT INTO CLIENT (PER_ID, CLI_NUM)
VALUES
((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'VOL00001'), '0007')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'VOL00002'), '0008')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'PAS00001'), '0014')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'BAS00001'), '0015')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'MAD00001'), '0020')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'THE00001'), '0035')
, ((SELECT PER_ID FROM PERSONNE WHERE PER_CODE = 'TOM00001'), '0048')
;
SELECT PER_CODE, PER_NOM, PER_PRENOM, CLI_NUM
FROM PERSONNE as x JOIN CLIENT as y ON x.PER_ID = y.PER_ID
;
=>
PER_CODE PER_NOM PER_PRENOM CLI_NUM
VOL00001 Volfoni Raoul 0007
VOL00002 Volfoni Paul 0008
PAS00001 Pascal Pascal 0014
BAS00001 Bastien Mac 0015
MAD00001 Mado Mado 0020
THE00001 Théo Théo 0035
TOM00001 Tomate Tomate 0048
Essayez ça, déjà...
L’attribut PER_CODE devrait vous aider, vous nous direz.
Partager