Creating relationships between tables that where not specified in the given UML diagram
5
votes
2
answers
2578
views
Good evening!
I'm working on designing my very first actual database from the following UML class diagram (in French unfortunately):
I'm at the point of creating the relational sketch of it which I created this way:
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:
The fact that I can't explain it in relational calculus let me think that I've done a mistake.
**But the last ones have the same primary key then. is it a problem?**
I'm at the point of creating the relational sketch of it which I created this way:
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:
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?
**But the last ones have the same primary key then. is it a problem?**
Asked by Revolucion for Monica
(677 rep)
Apr 16, 2016, 03:01 PM
Last activity: May 2, 2018, 05:01 PM
Last activity: May 2, 2018, 05:01 PM