Not only does Instagram allow you to share photos, it also allows you to send text messages to other users, which you can even unsend (i.e., delete). Suppose that Instagram uses a SQLite database to implement text messages:
CREATE TABLE messages ( id INTEGER, sender INTEGER NOT NULL, recipient INTEGER NOT NULL, message TEXT NOT NULL, PRIMARY KEY(id), FOREIGN KEY(sender) REFERENCES users(id), FOREIGN KEY(recipient) REFERENCES users(id) ); CREATE TABLE users ( id INTEGER, username TEXT NOT NULL UNIQUE, name TEXT NOT NULL, PRIMARY KEY(id) );
(2 points.) Even though Instagram usernames are unique, why, in no more than three sentences, might Instagram be using
usernameas a primary key?
(2 points.) In
finsta/2.sql, write a SQL statement via which Instagram could
SELECTall of the text messages that Harvard, whose username is
@harvard, has sent to Yale, whose username is
(2 points.) Suppose that Harvard wants to unsend one of its messages, the
idof which is
finsta/3.sql, write a SQL statement via which Instagram could
DELETEthat one message.
Suppose that Instagram wants to let users unsend messages (so that senders and recipients cannot see them anymore), but, for legal or nosey purposes, Instagram doesn’t actually want to delete unsent messages forever. Instead, Instagram just wants to record that messages have been unsent.
(3 points.) In no more than three sentences, propose how to alter Instagram’s SQLite database in order to support such.
(2 points.) Suppose that Yale wants to unsend one of its messages, the
idof which is
1701. Based on your proposed alterations to Instagram’s SQLite database, via what SQL statement could Instagram record that the message has been unsent without deleting it forever?
Suppose that Instagram instead wants to let users send messages to multiple (i.e., groups of) users at once.
- (3 points.) In no more than three sentences, propose how to alter Instagram’s SQLite database in order to support such. No need to let users unsend those messages.