Finsta

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

  1. (2 points.) Even though Instagram usernames are unique, why, in no more than three sentences, might Instagram be using id instead of username as a primary key?

  2. (2 points.) In finsta/2.sql, write a SQL statement via which Instagram could SELECT all of the text messages that Harvard, whose username is @harvard, has sent to Yale, whose username is @yale.

  3. (2 points.) Suppose that Harvard wants to unsend one of its messages, the id of which is 1636. In finsta/3.sql, write a SQL statement via which Instagram could DELETE that 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.

  1. (3 points.) In no more than three sentences, propose how to alter Instagram’s SQLite database in order to support such.

  2. (2 points.) Suppose that Yale wants to unsend one of its messages, the id of 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.

  1. (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.