Discussion :

[daemongeek]

Bonjour,

Même après avoir lu ce sujet, j'ai un peu de mal à créer ma requête sql pour créer un héritage...

En en gros, j'ai 3 tables
Produit
Maquillage
accessoires

La table père est produit, si je me trompe pas et les 2 autres héritent??

Comment est ce que je fait ça en sql??
Je dois faire une foreign key ds la table maquillage et une ds la table accessoire, et un seule clé primaire sur produit?

Merci d'avance.
Désolé si la question semble bête...

[punkoff]

bonjour,

Un peu de lecture : http://sqlpro.developpez.com/cours/m...tion/heritage/

[daemongeek]

Merci pour ta réponse.

J'ai bien lu le tuto, mais je n'y arrive pas je ne sais pas trop si je suis sur la bonne voie...

[CinePhil]

Tes trois tables auront la structure suivante :
Produit (prd_id, [colonnes communes à tous les produits])
Maquillage (mql_id_produit, [colonnes spécifiques au maquillage])
Accessoire (acc_id_produit, [colonnes spécifiques aux accessoires])

Donc tu crées d'abord la table Produit puis successivement les deux autres avec une contrainte de clé étrangère référençant l'identifiant du produit.

[daemongeek]

en sql, ça donnerait un truc du genre:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
-- ajout de la clé étrangère
alter table TProduit add FKmaq_acc foreign key(id_maq, id_acc);

-- ajout de la contrainte
alter table TProduit add constraint CTR_FKmaq_acc
foreign key (FKCategorie_Pro) 
references TProduit(id_prod)

c'est logique???

merci d'avance

[CinePhil]

Meuh non ! Il faut une clé étrangère par table ! Regarde la structure que j'ai donnée :

Produit (prd_id, [colonnes communes à tous les produits])
Maquillage (mql_id_produit, [colonnes spécifiques au maquillage])
Accessoire (acc_id_produit, [colonnes spécifiques aux accessoires])

Les clés primaires sont soulignées et les clés étrangères sont en italique ; j'avais omis cette précision mais ça me semblait évident.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE Produit
(
	prd_id INTEGER NOT NULL PRIMARY KEY,
	-- les autres colonnes communes à tous les produits
);

CREATE TABLE Maquillage
(
	mql_id_produit INTEGER NOT NULL PRIMARY KEY,
	-- les autres colonnes spécifiques au maquillage
	CONSTRAINT FK_mql_id_produit FOREIGN KEY (mql_id_produit) REFERNECES Produit(prd_id)
);

CREATE TABLE Accessoire
(
	acc_id_produit INTEGER NOT NULL PRIMARY KEY,
	-- les autres colonnes spécifiques aux accessoires
	CONSTRAINT FK_acc_id_produit FOREIGN KEY (acc_id_produit) REFERNECES Produit(prd_id)
);

[daemongeek]

Merci je viens de comprendre le principe, c'est tout con
je partais mal...
J'essayais de mettre toutes les contraintes dans une requêtes.

Je n'y arrivais vraiment pas, mon prof était moins clair.

encore merci.

et si je comprends bien, la seule différence c'est que les deux foreign key sont "liées" à la même clé primaire.
et bien sûre une FK par table.

[CinePhil]

Oui, c'est ça.

La particularité d'une table qui hérite d'une autre est qu'elle n'a pas d'identifiant propre ; elle hérite de l'identifiant de la table mère.

[daemongeek]

Merci beaucoup

je mettrai cet après midi le mcd ça pourra servir aux autres

[daemongeek]

voici mon sql complet, il n'y a pas d'erreur mais je pense qu'il peut être optimisé...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
CREATE TABLE TADRESSE (
  RueEtNumero VARCHAR(45) NOT NULL  ,
  CodePostal INTEGER ,
  Commune VARCHAR (45) NOT NULL ,
  Pays VARCHAR (45) NOT NULL,
  PRIMARY KEY(RueEtNumero)
);

CREATE TABLE  TCODE (
  Id VARCHAR (45) NOT NULL  ,
  MotDePasse VARCHAR(20) NOT NULL,
  PRIMARY KEY(Id)
);

CREATE TABLE MAQUILLAGE (
  Nom VARCHAR (45) NOT NULL  ,
  Couleur VARCHAR (45),
  Contenance INTEGER ,
  PRIMARY KEY(Nom)
);

CREATE TABLE OUTILS (
  Nom VARCHAR (45) NOT NULL,
  Description varchar (200) ,
  PRIMARY KEY(Nom)
);

CREATE TABLE TSTOCK (
  ID VARCHAR (45) NOT NULL  ,
   TCODE_Id VARCHAR (45) NOT NULL,
  DateAcces TIMESTAMP NOT NULL,
  Quantite INTEGER  DEFAULT 000 NOT NULL,
  PrixUnitaire DECIMAL DEFAULT 00.00 NOT NULL,
  PRIMARY KEY(ID,  TCODE_Id),
  FOREIGN KEY( TCODE_Id)
    REFERENCES  TCODE(Id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
);

CREATE TABLE TFOURNISSEUR (
  Nom VARCHAR (45) NOT NULL  ,
  TADRESSE_RueEtNumero VARCHAR(45) NOT NULL,
  Telephone VARCHAR(20) ,
  Email VARCHAR (45) NOT NULL,
  SiteWeb VARCHAR (45) NOT NULL,
  JoursFermeture VARCHAR (45) NOT NULL,
  HeuresD_ouverture VARCHAR (45) NOT NULL,
  PRIMARY KEY(Nom, TADRESSE_RueEtNumero),
  FOREIGN KEY(TADRESSE_RueEtNumero)
    REFERENCES TADRESSE(RueEtNumero)
      ON DELETE CASCADE
      ON UPDATE CASCADE
);

CREATE TABLE TUTILISATEUR (
  Nom VARCHAR(45) NOT NULL  ,
  TFOURNISSEUR_TADRESSE_RueEtN VARCHAR(45) NOT NULL,
  TFOURNISSEUR_Nom VARCHAR (45) NOT NULL,
   TCODE_Id VARCHAR (45) NOT NULL,
  Prenom VARCHAR (45) NOT NULL,
  DateDeNaissance DATE NOT NULL,
  email VARCHAR(45) NOT NULL,
  PRIMARY KEY(Nom, TFOURNISSEUR_TADRESSE_RueEtN, TFOURNISSEUR_Nom,  TCODE_Id),
  FOREIGN KEY(TFOURNISSEUR_Nom, TFOURNISSEUR_TADRESSE_RueEtN)
    REFERENCES TFOURNISSEUR(Nom, TADRESSE_RueEtNumero)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY( TCODE_Id)
    REFERENCES  TCODE(Id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
);

CREATE TABLE TPRODUIT (
  Nom VARCHAR (45) NOT NULL  ,
  MAQUILLAGE_Nom VARCHAR (45) NOT NULL,
  OUTILS_Nom VARCHAR (45) NOT NULL,
  Marque VARCHAR (45) NOT NULL,
  Description varchar (200) ,
  PRIMARY KEY(Nom, MAQUILLAGE_Nom, OUTILS_Nom),
  FOREIGN KEY(MAQUILLAGE_Nom)
    REFERENCES MAQUILLAGE(Nom)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(OUTILS_Nom)
    REFERENCES OUTILS(Nom)
      ON DELETE CASCADE
      ON UPDATE CASCADE
);

CREATE TABLE TSTOCK_has_TPRODUIT (
  TSTOCK_TCODE_Id VARCHAR (45) NOT NULL,
  TSTOCK_ID VARCHAR (45) NOT NULL,
  TPRODUIT_OUTILS_Nom VARCHAR (45) NOT NULL,
  TPRODUIT_MAQUILLAGE_Nom VARCHAR (45) NOT NULL,
  TPRODUIT_Nom VARCHAR (45) NOT NULL,
  PRIMARY KEY(TSTOCK_TCODE_Id, TSTOCK_ID, TPRODUIT_OUTILS_Nom, TPRODUIT_MAQUILLAGE_Nom, TPRODUIT_Nom),
  FOREIGN KEY(TSTOCK_ID, TSTOCK_TCODE_Id)
    REFERENCES TSTOCK(ID,  TCODE_Id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(TPRODUIT_Nom, TPRODUIT_MAQUILLAGE_Nom, TPRODUIT_OUTILS_Nom)
    REFERENCES TPRODUIT(Nom, MAQUILLAGE_Nom, OUTILS_Nom)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
);

CREATE INDEX TSTOCK_FKIndex1 on TSTOCK (Id);
CREATE INDEX TFOURNISSEUR_FKIndex2 on TFOURNISSEUR (TADRESSE_RueEtNumero);
CREATE INDEX TUTILISATEUR_FKIndex1 on TUTILISATEUR (TFOURNISSEUR_Nom, TFOURNISSEUR_TADRESSE_RueEtN);
CREATE INDEX TUTILISATEUR_FKIndex on TUTILISATEUR ( TCODE_Id);
CREATE INDEX TPRODUIT_FKIndex2 on TPRODUIT (MAQUILLAGE_Nom);
CREATE INDEX TPRODUIT_FKIndex3 on TPRODUIT (OUTILS_Nom);
CREATE INDEX TSTOCK_has_TPRODUIT_FKIndex1 on TSTOCK_has_TPRODUIT (TSTOCK_ID, TSTOCK_TCODE_Id);
CREATE INDEX TSTOCK_has_TPRODUIT_FKIndex2 on TSTOCK_has_TPRODUIT (TPRODUIT_Nom, TPRODUIT_MAQUILLAGE_Nom, TPRODUIT_OUTILS_Nom);

voici le modèle:


vous en pensez quoi?

[punkoff]

Bonjour,


Concernant les clefs primaires, lisez cet article : http://sqlpro.developpez.com/cours/clefs/

Et appliquez le.

Table tadresse :
quel est le code postal de la corse ?
externalisez pays / CP / ville dans des tables appropriées

Table maquillage :
contenance peut-il y avoir des virgules ?
Pourquoi n'y a-t-il pas d'unité (ML, uL, etc) ?

Table TCODE :
Que vient faire TCODE_ID dans la PK ?


Table TFOURNISSEUR :
JoursFermetures c'est quoi ? une liste de jours ?
idem poiur heures d'ouverture

Email / siteWebg varcahr de 45 uniquement ?


Table Tutilisateur :
La clef primaire me semble bizare


etc.


Vous devriez allez dans le forum conception / modélisation et commencer par la base : faire un MCD.

Car là il y a beaucoup de chose à revoir.

[daemongeek]

Merci pour les conseils,

pourriez vous expliquer ceci?

Table tadresse :
quel est le code postal de la corse ?
externalisez pays / CP / ville dans des tables appropriées

Je comprend pas... dsl

Pour la table maquillage en effet c'est une bête faute... j'y ai travaillé tard, trop tard...
pas de virgule c'est évident et l'unité je l'avais mis sur mon plan papier...

Jours de fermeture c'est une liste oui
Par contre pour les heures c'est juste un truc du genre 00h00-00H00

pour mail et web, j'avoue que c'est un peu court et je dois rajouter un trigger pour verifier si il y a bien un "@___.---" il devra checker juste si il y a un @ ou un domaine aussi?
je me dis que si il check le domaine, je dois faire une liste qu'il check, ce sera donc plus lourd non?

je vois ce qui ne va pas avec la table utilisateur, la clé primaire c'est juste le nom.

[CinePhil]

Ce que veut dire punkoff par "externaliser", c'est qu'il faut créer des tables de référence pour au moins les pays et les villes afin d'éviter d'avoir la même ville ou le même pays écrit avec X orthographes différentes ou bien une adresse située à Paris... au Maroc !

Ton image n'est pas très lisible mais j'ai l'impression que les cardinalités sont à revoir.
D'après ce que je vois, il y a une clé étrangère référençant une adresse dans la table des fournisseurs. Ne serait-ce pas plutôt l'inverse, à savoir qu'un fournisseur peut avoir plusieurs adresses et qu'une adresse n'appartient qu'à un seul fournisseur ?

Tu devrais écrire tes règles de gestion des données avant de te lancer tête baissée dans la création des tables.

[daemongeek]

il faut créer des tables de référence pour au moins les pays et les villes

mais si je crée une table pays, il y aura une table avec une seule colonne, mon prof disait qu'il fallait éviter...

pour la table ville je peux faire ceci?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
-- création TVille
create table TVille(
cp_vil smallint not null,
ville_vil varchar(40) not null,
primary key (cp_vil,ville_vil)
);
-- création de l'index
create index idxVille on TVille(ville_vil);

en effet le fournisseur peut avoir plusieur adresse mais une adresse se rapporte à un fournisseur.

[punkoff]

pourriez vous expliquer ceci?

Les codes postaux de la corse commencent par 2A ou 2B.
Vous avez déclaré cette colonne comme étant de type INTEGER, ça ne va pas marcher

[daemongeek]

Les codes postaux de la corse commencent par 2A ou 2B.
Vous avez déclaré cette colonne comme étant de type INTEGER, ça ne va pas marcher

Désolé je suis Belge et ici les codes postaux c'est 1000 à 9999
...
je devrais mettre un type varchar pour que les français puisse l'employer alors?

[el_slapper]

Désolé je suis Belge et ici les codes postaux c'est 1000 à 9999
...
je devrais mettre un type varchar pour que les français puisse l'employer alors?

Le jour ou la norme belge change, tu est chocolat. Ce sont des choses qui arrivent. C'est arrivé en france le jour ou ils ont tronçonné la Corse, par exemple.

La question à se poser, c'est de savoir si il y a un avantage réel à considérer ce code comme un numérique : est-ce qu'on va diviser la valeur, l'additionner, l'incrémenter, ou est-ce que ce n'est qu'un vulgaire code? Dans le cas du code postal, je ne crois pas qu'on s'amuse à les multiplier. Ni même à les incrémenter : on se contente de les lire dans l'ordre. Par conséquent, mettre les codes postaux en numériques, c'est un risque, pour un gain que je n'arrive pas à cerner.

(je dis ça, j'ai mis une fois des codes banques en numérique. Ca marche, on a toujours 30001 pour la banque de France, mais ça n'a quand même aucune valeur ajoutée : j'aurais du les mettre en alphanumérique)

[punkoff]

Vu que vous avez mis une notion de pays ce serai mieux effectivement de pouvoir répondre à des cas qui ne sont pas en belgique

Sauf si, biensur, vos règles de gestions ne prévoient pas un tel cas

[daemongeek]

en effet aucun intérêt de traiter les cp comme un entier,
je ferai donc un varchar,

ma table adresse, va donc devenir 3 tables?

adresse avec:
rue
numéro

ville avec:
commune
code postal

pays avec
nom

fournisseur____(1,n)____adresse
adresse____(1,1)____ville
ville____(1,1)____pays

c'est correct ?

[CinePhil]

Pour commencer, bis repetita :

Tu devrais écrire tes règles de gestion des données avant de te lancer tête baissée dans la création des tables.

mais si je crée une table pays, il y aura une table avec une seule colonne, mon prof disait qu'il fallait éviter...

Et il a plutôt raison, même si ça peut arriver dans certains cas particuliers.

Une table de référence est toujours composée d'au moins deux colonnes :
- un identifiant qui sert de clé primaire, de type entier auto-incrémenté ;
- une colonne signifiante du type approprié à la valeur stockée, de plus munie d'un index de type UNIQUE puisque c'est sur cette colonne que vont porter les éventuelles recherches.

A minima, ta table de référence des pays devrait ressembler à ça :
tr_pays_pay (pay_id, pay_nom)
On peut aussi y ajouter des codes uniques et de préférence reconnus internationalement pour compléter les informations sur les pays. Une fois que tu as cette table, tu peux la réutiliser dans n'importe quelle BDD qui a besoin de traiter des pays.

pour la table ville je peux faire ceci?

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
-- création TVille
create table TVille(
cp_vil smallint not null,
ville_vil varchar(40) not null,
primary key (cp_vil,ville_vil)
);
-- création de l'index
create index idxVille on TVille(ville_vil);

Non !
D'abord parce que, en France, certaines villes ont plusieurs codes postaux et qu'un code postal peut s'appliquer à plusieurs villes. Je ne sais pas si ça exsite aussi en Belgique ou ailleurs mais comme ta BDD semble prendre en compte potentiellement les codes postaux de tous les pays, il vaut mieux prévoir.

Ensuite parce que, comme dit plus haut et comme signalé par punkoff, une clé primaire d'une table issue d'une entité type du MCD sera dans la grande majorité des cas de préférence de type entier et auto-incrémenté. Je te renvoie donc, comme punkoff, vers l'article de SQLPro à ce sujet.

le fournisseur peut avoir plusieurs adresses mais une adresse se rapporte à un fournisseur

Voilà une règle de gestion assez bien formulée qui entraîne, dans le MCD, la création de l'association suivante :
founisseur -0,n----avoir----1,1- adresse

Et ce MCD entraînera la création des tables suivantes :
te_fournisseur_frn (frn_id, frn_nom...)
te_adresse_adr (adr_id, adr_id_fournisseur...)

fsmrel dirait même que l'adresse est une propriété multi-valuée du fournisseur et qu'il conviendrait d'utiliser l'identification relative :
founisseur -0,n----avoir----(1,1)- adresse

Ce qui modifiera la table des adresses précédente de cette façon :
te_adresse_adr (adr_id_fournisseur, adr_sequence, ...)

L'identification relative signifie que l'adresse sera identifiée relativement au fournisseur. C'est à dire que, par exemple, le fournisseur 1 aura les adresses de clé primaire {1, 1}, {1,2} et {1,3} et que pour le fournisseur 2 et pour chaque autre fournisseur, on recommence la numérotation de la séquence à 1 : {2,1} et {2,2}.

Les codes postaux de la corse commencent par 2A ou 2B.

Non, les codes postaux de la Corse sont restés commençant par 20 alors que la Corse a été divisée en 2A et 2B.

La vraie raison de donner le type CHAR ou VARCHAR à une colonne de codes postaux est donnée par el_slapper : un code n'est pas un nombre, même s'il n'est composé que de chiffres. C'est également vrai pour un numéro de sécurité sociale ou autre, une référence de produit ou autre, un numéro de chapitre, d'étagère, de bâtiment...

ma table adresse, va donc devenir 3 tables?

adresse avec:
rue
numéro

ville avec:
commune
code postal

pays avec
nom

fournisseur____(1,n)____adresse
adresse____(1,1)____ville
ville____(1,1)____pays

c'est correct ?

C'est mieux oui, même si la représentation laisse à désirer.
founisseur -0,n----avoir----(1,1)- adresse -1,1----situer----0,n- ville -1,1----situer----0,n- pays
tr_pays_pay (pay_id, pay_nom)
tr_ville_vil (vil_id, vil_id_pays, vil_nom...)
te_adresse_adr (adr_id_fournisseur, adr_sequence, adr_id_ville, adr_code_postal, adr_rue...)
te_adresse_adr (adr_id_fournisseur, adr_sequence, ...)