Au format erdiag, modélisation simpliste supposant que tous les livreurs sont là tout le temps (en réalité chacun suit un planning, qui serait à prendre en compte). On n'envisage pas non plus d'optimisation géographique (regroupement de commandes proches par un même livreur, etc).

[Clients]
+id
nom
adresse
tel
infos

[Livreurs]
+id
nom
salaire

[Bases]
+nom
prix

[Pizzas]
+id

[Ingrédients]
+nom
prix

[Articles]
+nom
prix

[Commandes]
+id
Timestamp

{Contient_suppléments}
Pizzas *
Ingrédients *
--
Quantité

{Contient_ingrédients}
Bases +
Ingrédients *

{Contient_base}
Pizzas 1
Bases *

{Contient_articles}
Commandes *
Articles *

{Contient_pizzas}
Commandes *
Pizzas *

{Emporter}
Clients *
Commandes ?

{Livrer}
Clients *
Commandes ?
Livreurs +

Commentaires :

• Attention une commande n'est pas une association ternaire Client-Pizza-Article car cela signifierait qu'un client commande autant de pizzas que d'articles.
• Il faut distinguer commandes emportées et livrées, mais une commande est forcément consommée : la cardinalité 0,1 indiquée depuis Livrer et Emporter vers Commandes est en réalité un OU exclusif (schématisation vue hors programme).
• Le reste est assez simple : on crée en général une association n-n pour indiquer qu'un objet est composé d'autres (pizzas / ingrédients, commande / pizzas etc).

Notes :

• Client.infos : interphone, étage...
• (nom,adresse) unique ; mais pas (nom,tel) : un client peut commander depuis plusieurs endroits.
• Base.nom contrainte : prédéfinie ("Orientale","Végétarienne"...etc)
• Prix unitaires
• On suppose sur le schéma que toute commande à emporter est effectuée par un client enregistré (pourra être nuancé en SQL)

---

MLD : Compte-tenu des cardinalités la (référence à) la base s'ajoute à l'entité Pizzas, et deux champs sont ajoutés à Commandes : Client et Livreur (ce dernier pouvant être nul si à emporter). Si de plus le champ Client est nul alors il s'agit d'une commande effectuée sur place par un client anonyme (il ne peut alors pas y avoir de livreur renseigné). Les autres associations sont n-n et se transforment donc en tables, dont les clés primaires sont composées des clés des entités en association.

CREATE TABLE Clients (
	id INTEGER,
	nom TEXT NOT NULL,
	adresse TEXT NOT NULL,
	tel TEXT NOT NULL,
	infos TEXT,
	PRIMARY KEY (id),
	UNIQUE (nom, adresse)
);
CREATE TABLE Livreurs (
	id INTEGER,
	nom TEXT NOT NULL,
	salaire REAL NOT NULL,
	PRIMARY KEY (id)
);
CREATE TABLE Bases (
	nom INTEGER,
	prix REAL NOT NULL,
	PRIMARY KEY (nom)
);
CREATE TABLE Pizzas (
	id INTEGER,
	Base TEXT NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (Base) REFERENCES Bases(nom)
);
CREATE TABLE Ingredients (
	nom TEXT,
	prix REAL NOT NULL,
	PRIMARY KEY (nom)
);
CREATE TABLE Bases_Ingredients (
	base TEXT,
	ingredient TEXT,
	PRIMARY KEY (base,ingredient)
);
CREATE TABLE Articles (
	nom TEXT,
	prix REAL NOT NULL,
	PRIMARY KEY (nom)
);
CREATE TABLE Commandes (
	id INTEGER,
	timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	Client INTEGER,
	Livreur INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY (Client) REFERENCES Clients(id),
	FOREIGN KEY (Livreur) REFERENCES Livreurs(id)
);
CREATE TABLE Pizzas_Supplements (
	Pizza INTEGER,
	Supplement TEXT,
	Quantite INTEGER,
	PRIMARY KEY (Pizza,Supplement),
	FOREIGN KEY (Pizza) REFERENCES Pizzas(id),
	FOREIGN KEY (Supplement) REFERENCES Supplements(nom),
	CHECK (Quantite >= 1)
);
CREATE TABLE Commandes_Articles (
	Commande INTEGER,
	Article INTEGER,
	PRIMARY KEY (Commande,Article),
	FOREIGN KEY (Commande) REFERENCES Commandes(id),
	FOREIGN KEY (Article) REFERENCES Articles(nom)
);
CREATE TABLE Commandes_Pizzas (
	Commande INTEGER,
	Pizza INTEGER,
	PRIMARY KEY (Commande,Pizza),
	FOREIGN KEY (Commande) REFERENCES Commandes(id),
	FOREIGN KEY (Pizza) REFERENCES Pizzas(id)
);

CREATE TABLE Users (
	id INTEGER,
	name VARCHAR NOT NULL,
	PRIMARY KEY (id)
);
CREATE TABLE Groups (
	id INTEGER,
	name VARCHAR NOT NULL,
	description TEXT,
	PRIMARY KEY (id)
);
CREATE TABLE Messages (
	id INTEGER,
	created DATETIME NOT NULL,
	content TEXT NOT NULL,
	sender INTEGER,
	receiver INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY (sender) REFERENCES Users(id),
	FOREIGN KEY (receiver) REFERENCES Users(id)
);

Note : friend_with est bidirectionnel (symétrique)

CREATE TABLE Posts (
	id INTEGER,
	content TEXT NOT NULL,
	created DATETIME NOT NULL,
	author INTEGER NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (author) REFERENCES Users(id)
);
CREATE TABLE likes (
	user INTEGER,
	post INTEGER,
	PRIMARY KEY (user,post),
	FOREIGN KEY (user) REFERENCES Users(id),
	FOREIGN KEY (post) REFERENCES Posts(id)
);
CREATE TABLE friend_with (
	user1 INTEGER,
	user2 INTEGER,
	PRIMARY KEY (user1,user2),
	FOREIGN KEY (user1) REFERENCES Users(id),
	FOREIGN KEY (user2) REFERENCES Users(id)
);
CREATE TABLE belong_to (
	groupe INTEGER,
	user INTEGER,
	PRIMARY KEY (groupe,user),
	FOREIGN KEY (groupe) REFERENCES Groups(id),
	FOREIGN KEY (user) REFERENCES Users(id)
);

Note : aucune garantie que les solutions proposées ici soient les meilleures (il y a sûrement plus court, plus élégant...) ; mais elles fonctionnent et se lisent assez bien.

1. with id_Stan as (select id from users where name = 'Stan'),
   id_Kyle as (select id from users where name = 'Kyle')
   select content, sender
   from messages cross join id_Stan cross join id_Kyle
   where created BETWEEN '2018-01-10 00:00:00' AND '2018-01-10 23:59:59'
     and ((sender = id_Kyle.id and receiver = id_Stan.id)
       or (sender = id_Stan.id and receiver = id_Kyle.id));

2. with id_Kenny as
     (select id as uid from users where name = 'Kenny'),
   Kenny_friends as
     (select user2 as uid
     from friend_with cross join id_Kenny where user1 = id_Kenny.uid),
   Kenny_and_friends as
     (select uid from Kenny_friends union select uid from id_Kenny)
   select distinct name from users where id in
     (select user2 as uid --amis des amis de Kenny
     from users join friend_with on id = user1
     where user1 in Kenny_friends
       except
     select uid from Kenny_and_friends);

   Note : on peut aussi utiliser "WHERE / NOT IN" au lieu de "EXCEPT"

3. with id_Randy as (select id from users where name = 'Randy')
   select created, content, count(*) as nb_likes
   from posts p cross join id_Randy
   join likes L on p.id = L.post
   where p.author = id_Randy.id and L.user <> id_Randy.id
   group by p.id
   order by nb_likes DESC

4. with id_Wendy as (select id from users where name = 'Wendy'),
   friends as
     (select user2
     from friend_with cross join id_Wendy
     where user1 = id_Wendy.id),
   tailles as --tailles des groupes indépendamment de Wendy
     (select groupe,count(*) as taille
     from belong_to
     group by groupe)
   select name
   from groups g
   join belong_to b on g.id = b.groupe
   join tailles t on g.id = t.groupe
   where user in friends
   group by g.id
   order by count(*) desc, t.taille desc;

5. Dans la solution proposée, "LEFT OUTER JOIN" est nécessaire pour faire apparaître tous les utilisateurs (ils n'ont pas forcément tous écrit un post, envoyé un message et reçu un like). C'est possible en SQLite, et c'est d'ailleurs le seul type de jointure externe supporté.

   with u_posts as
     (select u.id, count(p.author) as P
     from users u
     left outer join posts p on u.id = p.author
     group by u.id),
   u_messages as
     (select u.id, count(m.sender) as M
     from users u
     left outer join messages m on m.sender = u.id
     group by u.id),
   u_likes as
     (select u.id, count(ll.post) as L
     from users u
     left outer join posts p on u.id = p.author
     left outer join likes ll on p.id = ll.post
     group by u.id)
   select name, P, M, L from u_posts p
   natural join u_messages natural join u_likes natural join users
   order by L desc, P+M desc

   Peut être nettement abrégé comme suit (trouvé dans une copie) :

   SELECT u.name,
     (SELECT COUNT(*) FROM Posts WHERE author=u.id) AS P,
     (SELECT COUNT(*) FROM likes ll
       join Posts p on ll.post = p.id
       WHERE p.author=u.id) AS L,
     (SELECT COUNT(*) FROM Messages WHERE sender=u.id) AS M
   FROM Users u
   ORDER BY L DESC, P+M DESC

   ...mais au prix de la performance : trois sous requêtes par utilisateur.

1. On peut obtenir le même effet avec le mot-clé CASCADE, comme vu en commentaire d'un exercice de TP (et pouvant être retrouvé par une recherche Google entre autres)

   CREATE TRIGGER suppr_like AFTER DELETE on Posts
   BEGIN
     DELETE FROM likes WHERE post = old.id;
   END

2. Note : serait plus agréable à lire avec une clause "WITH", mais ce n'est pas supporté en SQLite comme mentionné en bas de page ici. C'est pourtant indiqué comme étant valide dans la doc du CREATE TRIGGER (cliquer sur "insert-stmt" puis sur "with-clause") ...

   CREATE TRIGGER notify_friend AFTER INSERT ON belong_to
   BEGIN
     insert into messages (created,content,sender,receiver)
     values (
       current_timestamp,
       (select
         (select name from users where id = NEW.user)
         || ' joined ' ||
         (select name from groups where id = NEW.groupe)),
       NEW.user,
       (select user2 as id --un ami au hasard
         from friend_with
         where user1 = NEW.user
         order by random() limit 1)
     );
   END