5

Good evening!

I'm working on designing my very first actual database from the following UML class diagram (in French unfortunately):

UML description of the database

I'm at the point of creating the relational sketch of it which I created this way:

RelationalScheme

Yet, it creates some issues when trying to ask to the database which are the clients that never ordered product number one?

Indeed, I don't know how to do it in SQL as far as it seems that there is no relations between Client, Commande and Produit. It gived the following sql code:

CREATE TABLE Client (
IDClient INT NOT NULL,
AdresseClient VARCHAR(255)NOT NULL ,
NomContact VARCHAR(255)NOT NULL,
NumeroSIRET VARCHAR(14) NOT NULL,
CONSTRAINT cclient PRIMARY KEY (IDClient)
);

CREATE TABLE Produit (
IDProduit INT NOT NULL  ,
PrixVente INT NOT NULL ,
QuantiteEnStock INT NOT NULL ,
CONSTRAiNT cproduit PRIMARY KEY (IDProduit)
) ;

CREATE TABLE Commande (
IDNumeroCde INT NOT NULL  ,
Date DATE NOT NULL ,
CONSTRAINT ccommande PRIMARY KEY (IDNumeroCde)
);

In relational algebra and calculus it gives:

Relational algebra and calculus

The fact that I can't explain it in relational calculus let me think that I've done a mistake.

Edit with the new tables

I read all the links you provided and therfore add some comments and changes on my tables, can you tell me if I'm right on my assumptions?

The new relational scheme

But the last ones have the same primary key then. is it a problem?

1
+50

UML class diagrams do not include relations in a class' attributes list, so when creating database tables representing those classes, the database designer must identify and specify them, much like the software developer must create the pointers and lists to support the related objects.

In your diagram, Commande relates to Client but does not explicitly specify a IDClient attribute. When creating the Commande table, you must include this attribute along with a foreign key to Client.

You can find further information about this in Database Design - UML/SQL:

The UML representation of the Order class contains only its own descriptive attributes. The UML association tells which customer placed an order. In the database, we will need a different way to identify the customer; that will be part of the relation scheme (below).

  • Thank you for your answer! I may be right but this was unexpected for me and my colleagues and I therefore started a bounty. – ThePassenger Apr 18 '16 at 17:45
  • @Marine1 I've extended the answer somewhat and included sources for you to verify. – Ziggy Crueltyfree Zeitgeister Apr 19 '16 at 2:21
  • 1
    Okay, I'm okay with the answer. I leave the bounty open for a few more days if an even better answer poped out and I will award then, thanks! Could you provide an example to make it straight and clear? – ThePassenger Apr 21 '16 at 11:48
  • I've just updated a new table using the reference you gived, can you tell me if my assumptions are right? – ThePassenger May 3 '16 at 21:11
1

In addition to Ziggy answer, if you want to make this diagram work, you need to edit it, for example you should create a third table called cleint_order and this table has primary key from Client table and idproduct from product table since one client may have one or more product and one product can be ordered from many customers.

useful link: https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de

  • That's what we did at the beginning, but I don't remember why did we deleted it. We have to this because it is a one to several relation, isn't it? Your link is useful. – ThePassenger Apr 20 '16 at 10:18
  • I've just updated a new table using the link you gived, can you tell me if my assumptions are good? – ThePassenger May 3 '16 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.