Creating relationships between tables that where not specified in the given UML diagram

by Marine1   Last Updated May 02, 2018 17:06 PM

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?



Answers 2


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).

Ziggy Crueltyfree Zeitgeister
Ziggy Crueltyfree Zeitgeister
April 18, 2016 04:57 AM

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

Ahmad Abuhasna
Ahmad Abuhasna
April 19, 2016 15:09 PM

Related Questions


Questions Concerning the Chase Test

Updated July 10, 2018 05:06 AM


Converting SQL to Relational Algebra / Calculus

Updated June 20, 2017 15:06 PM

From domain calculus to relational algebra

Updated June 29, 2018 12:06 PM