Well, this won't work:
- your commande table refers to livre on a non-existing
primary or unique key; see inline.
--
Frank
La famille Labrie <psautier_at_videotron.ca> schreef in berichtnieuws
zmnQ5.3139$Am5.171040_at_wagner.videotron.net...
> Can you help me with a script please?
>
> These is the script:
> clear screen
> --Supprimer toutes les tables existantes
> drop table auteur cascade constraints;
> drop table editeur cascade constraints;
> drop table client cascade constraints;
> drop table livre cascade constraints;
> drop table commande cascade constraints;
>
> --Supprimer toutes les séquences existantes
> drop sequence auteur_seq;
> drop sequence editeur_seq;
> drop sequence client_seq;
> drop sequence livre_seq;
>
>
>
> --Création de la table auteur
> create table auteur( numaut number(4) constraint pk_auteur_numaut primary
> key,
> nomaut varchar2(30) not null,
> preaut varchar2(30) not null
> );
>
I would reconsider the fact that preaut is mandatory
> --Création de la table editeur
> create table editeur( numedi number(4) constraint pk_editeur_numedi
primary
> key,
> nomedi varchar2(30) not null,
> adredi varchar2(60) not null
> );
>
Again, reconsider ardredi being mandatory
> --Création de la table client
> create table client( numcli number(4) constraint pk_client_numcli primary
> key,
> nomcli varchar2(30) not null,
> precli varchar2(30) not null
> );
>
Ditto
> --Création de la table livre
> create table livre( numliv number(6),
> titliv varchar2(60) not null,
> numaut number(4) constraint fk_livre_numaut references
> auteur(numaut),
> numedi number(4) constraint fk_livre_numedi references
> editeur(numedi),
> datedi date not null,
> constraint pk_livre_cles primary key(numliv,numaut,numedi)
> );
Some odd things here:
- numaut is not mandatory (there is no NOT NULL clause)
- ditto numedi
- primary key should be numliv
- add unique keys on titliv, numaut (title/author combination is unique),
and titlev, numedi (title/editor combination is unique). But consider
replacing those two by one unique key on titlev, numaut, numedi.
>
> --Création de la table commande
> create table commande( numcom number(4),
> numliv number(6) constraint fk_livre_numliv
> references livre(numliv),
> numcli number(4) constraint fk_client_numcli
> references client(numcli),
> qtecom number(2) not null,
> constraint pk_commande_cles primary
> key(numcom,numliv,numcli)
> );
>
Primary key here is numcom, which should be generated by a sequence,
like client, livre, etc. Consider adding a unique key on numcom, client if
you want to enforce that, but as numcom is unique, this is pointless.
Add an extra table commande_ligne: that holds order, orderline, numliv:
create table
mande(
numcom number(4)
, numcli number(4)
constraint fk_client_numcli references client(numcli)
, constraint pk_commande_cles primary key(numcom)
create table commande_ligne (
numcom number(4) not null
constraint fk_comlin_com references commande(numcom)
, numlin number(4) not null
, numliv number (4) not null
constraint fk_numlin_livre references livre(numliv)
, quantite numer(2) not null
, constraint pk_comlin primary key(numcom, numlin)
);
> The conditions are:
>
> 1- One commande(order) has one or more livre(book)
> 2- one commande(order) has onely one client
> 3- one livre(book) has only one auteur(author)
> 4- one livre (book) has only one editeur(editor)
>
> Thank
>
>
>
>
hth,
Received on Wed Nov 15 2000 - 03:39:03 CST
