CREATE TABLE Role
(
roleId INT IDENTITY
, roleNom VARCHAR(50) NOT NULL
, CONSTRAINT Role_PK PRIMARY KEY(roleId)
) ;
insert into Role values
('admin'), ('client')
;
select * from Role ;
CREATE TABLE Adresse
(
adresseId INT IDENTITY
, adresseLigne1 VARCHAR(50) NOT NULL
, adresseLigne2 VARCHAR(50) NOT NULL
, CONSTRAINT Adresse_PK PRIMARY KEY(adresseId)
) ;
insert into Adresse values
('3 rue qui monte', '75022, Pantruche')
, ('9 allée des roses', '75023, Paname')
;
select * from Adresse ;
CREATE TABLE Utilisateur
(
utilisateurId INT IDENTITY
, utilisateurNom VARCHAR(50)
, utilisateurPrenom VARCHAR(50)
, roleId INT NOT NULL
, CONSTRAINT Utilisateur_PK PRIMARY KEY(utilisateurId)
, CONSTRAINT Utilisateur_Role_FK FOREIGN KEY(roleId)
REFERENCES Role(roleId)
) ;
insert into Utilisateur values
('Kotoni', '', 1), ('Naudin', 'Fernand', 2), ('Volfoni', 'Raoul', 2), ('Volfoni', 'Paul', 2)
;
select u.utilisateurPrenom, u.utilisateurNom, r.roleNom
from Utilisateur as u
join Role as r on u.roleId = r.roleId
;
CREATE TABLE Domicilier
(
utilisateurId INT
, adresseId INT
, CONSTRAINT Domicilier_PK PRIMARY KEY(utilisateurId, adresseId)
, CONSTRAINT Domicilier_Utilisateur_FK FOREIGN KEY(utilisateurId)
REFERENCES Utilisateur(utilisateurId)
, CONSTRAINT Domicilier_Adresse_FK FOREIGN KEY(adresseId)
REFERENCES Adresse(adresseId)
) ;
CREATE TABLE Statut
(
statutId INT IDENTITY
, statutNom VARCHAR(50) NOT NULL
, CONSTRAINT Statut_PK PRIMARY KEY(statutId)
) ;
insert into Statut values
('en cours'), ('livrée')
;
select * from Statut ;
CREATE TABLE Commande
(
commandeId INT IDENTITY
, utilisateurId INT NOT NULL
, commandeDate DATE NOT NULL
, statutId INT NOT NULL
, CONSTRAINT Commande_PK PRIMARY KEY(commandeId)
, CONSTRAINT Commande_Utilisateur_FK FOREIGN KEY(utilisateurId)
REFERENCES Utilisateur(utilisateurId)
, CONSTRAINT Commande_Statut_FK FOREIGN KEY(statutId)
REFERENCES Statut(statutId)
) ;
insert into Commande values
(2, '01/05/2022', 2), (2, '01/06/2023', 1)
, (3, '14/07/2022', 2), (4, '31/05/2023', 1)
;
select commandeId as NumeroCommande
, utilisateurPrenom as clientPrenom
, utilisateurNom as clientNom
, commandeDate
, statutNom as Statut
from Commande as c
join Utilisateur as u on c.utilisateurId = u.utilisateurId
join Statut as s on c.statutId = s.statutId
;
CREATE TABLE Bouquet
(
bouquetId INT IDENTITY
, bouquetNom VARCHAR(50) NOT NULL
, bouquetPrix DECIMAL(5,2) NOT NULL
, CONSTRAINT Bouquet_PK PRIMARY KEY(bouquetId)
) ;
insert into Bouquet values
('bouquet de saison', 30), ('bouquet parfumé', 32), ('bouquet normand', 30)
, ('bouquet parisien', 34), ('bouquet breton', 32), ('bouquet provençal', 33)
;
select bouquetNom, bouquetPrix
from Bouquet
;
CREATE TABLE CommandeLigne
(
commandeId INT
, commandeLigneId SMALLINT
, commandeLigneQte INT NOT NULL
, bouquetId INT NOT NULL
, CONSTRAINT CommandeLigne_PK PRIMARY KEY(commandeId, commandeLigneId)
, CONSTRAINT CommandeLigne_Commande_FK FOREIGN KEY(commandeId)
REFERENCES Commande(commandeId)
, CONSTRAINT CommandeLigne_Bouquet_FK FOREIGN KEY(bouquetId)
REFERENCES Bouquet(bouquetId)
) ;
insert into CommandeLigne values
(1, 1, 2, 1), (1, 2, 1, 3)
, (2, 1, 1, 6)
;
select commandeId as NumeroCommande
, commandeLigneId as numeroLigne
, commandeLigneQte as Quantite
, bouquetNom
from CommandeLigne as l
join Bouquet as b on l.bouquetId = b.bouquetId
;
CREATE TABLE Fleur
(
fleurId INT IDENTITY
, fleurNom VARCHAR(50) NOT NULL
, fleurPrix DECIMAL(5,2) NOT NULL
, CONSTRAINT Fleur_PK PRIMARY KEY(fleurId)
, CONSTRAINT Fleur_AK UNIQUE(fleurNom)
) ;
insert into Fleur values
('lys', 2), ('pivoine', 3), ('rose', 3)
;
select * from Fleur ;
CREATE TABLE Couleur
(
couleurId INT IDENTITY
, couleurNom VARCHAR(50) NOT NULL
, CONSTRAINT Couleur_PK PRIMARY KEY(couleurId)
, CONSTRAINT Couleur_AK UNIQUE(couleurNom)
) ;
CREATE TABLE fleur_couleur
(
fleurId INT
, couleurId INT
, CONSTRAINT fleur_couleur_PK PRIMARY KEY(fleurId, couleurId)
, CONSTRAINT fleur_couleur_Fleur_FK FOREIGN KEY(fleurId)
REFERENCES Fleur(fleurId)
, CONSTRAINT fleur_couleur_Couleur_FK FOREIGN KEY(couleurId)
REFERENCES Couleur(couleurId)
) ;
CREATE TABLE Commentaire
(
commentaireId INT IDENTITY
, utilisateurId INT NOT NULL
, commentaireTitre VARCHAR(50) NOT NULL
, commmentaireContenu VARCHAR(max) NOT NULL
, commentaireDate DATE NOT NULL
, bouquetId INT
, fleurId INT
, CONSTRAINT Commentaire_PK PRIMARY KEY(commentaireId)
, CONSTRAINT Commentaire_Bouquet_FK FOREIGN KEY(bouquetId)
REFERENCES Bouquet(bouquetId)
, CONSTRAINT Commentaire_Fleur_FK FOREIGN KEY(fleurId)
REFERENCES Fleur(fleurId)
, CONSTRAINT Commentaire_Utilisateur_FK FOREIGN KEY(utilisateurId)
REFERENCES Utilisateur(utilisateurId)
, CONSTRAINT Commentaire_soit_bouquet_soit_fleur CHECK
(bouquetId is not NULL AND fleurId is NULL OR bouquetId is NULL AND fleurId is not NULL)
) ;
insert into Commentaire values
(2, 'Quel beau bouquet !', 'C''est ma femme qui va être contente !', '10/05/2022', 1, NULL)
, (2, 'Quel beau bouquet !', 'C''est ma femme qui va être contente !', '10/05/2022', NULL, 2)
-- , (2, 'Quel beau bouquet !', 'C''est ma femme qui va être contente !', '10/05/2022', NULL, NULL)
-- , (2, 'Quel beau bouquet !', 'C''est ma femme qui va être contente !', '10/05/2022', 1, 2)
;
select utilisateurPrenom, utilisateurNom
, commentaireTitre as Titre, commmentaireContenu as Contenu
, commentaireDate as DateCommentaire
, coalesce(b.bouquetNom, '/') as Bouquet
, coalesce(f.fleurNom,'/') as Fleur
from Commentaire as c
join Utilisateur as u on c.utilisateurId = u.utilisateurId
left join Bouquet as b on c.bouquetId = b.bouquetId
left join Fleur as f on c.fleurId = f.fleurId
;
CREATE TABLE Composer
(
bouquetId INT
, fleurId INT
, CONSTRAINT Composer_PK PRIMARY KEY(bouquetId, fleurId)
, CONSTRAINT Composer_Bouquet_FK FOREIGN KEY(bouquetId)
REFERENCES Bouquet(bouquetId)
, CONSTRAINT Composer_Fleur_FK FOREIGN KEY(fleurId)
REFERENCES Fleur(fleurId)
) ;
Demande d'aide MCD - ecommerce
Répondre avec citation
Envoyé par Kotoni
.png)
Partager