Part A:

    CREATE TABLE Students (
    student_id INT NOT NULL AUTO_INCREMENT,
    email VARCHAR(100),
    password VARCHAR(50),
    birthdate DATE,
    avatar VARCHAR(256),
    PRIMARY KEY (student_id) 
    );

    CREATE TABLE Questions (
    question_id INT NOT NULL AUTO_INCREMENT,
    student_id INT,
    created_dt DATE,
    upvotes INT,
    question TEXT,
    answered BOOL,
    PRIMARY KEY (question_id),
    FOREIGN KEY (student_id) REFERENCES Students (student_id)
    );

    CREATE TABLE Upvotes (
    upvote_id INT NOT NULL AUTO_INCREMENT,
    student_id INT,
    question_id INT,
    upvote_dt DATE,
    PRIMARY KEY (upvote_id),
    FOREIGN KEY (student_id) REFERENCES Students (student_id),
    FOREIGN KEY (question_id) REFERENCES Questions (question_id)
    );

Part B:
    1. Sign-up Page:

        INSERT INTO Students (email, password, birthdate, avatar)
        VALUES ("test1@test.com", "abc123", '1999-01-01', "sample1.jpg");

        INSERT INTO Students (email, password, birthdate, avatar)
        VALUES ("ustudent@uregina.ca", "zxy987", '2000-05-23', "sample2.jpg");

        INSERT INTO Students (email, password, birthdate, avatar)
        VALUES ("testuser@gmail.com", "foobar1234", '1997-11-14', "sampleimg.jpg");

    2. Post Question Page:

        INSERT INTO Questions (student_id, created_dt, upvotes, question, answered)
        VALUES (2, CURDATE(), 3, "Nunc lacinia nibh a diam mattis, laoreet fringilla diam congue. Nulla dignissim maximus ligula sed fringilla. Donec quis fermentum eros.", 0);

        INSERT INTO Questions (student_id, created_dt, upvotes, question, answered)
        VALUES (3, '2021-05-24', 2, "Suspendisse ac leo sit amet mi luctus venenatis. Interdum et malesuada fames ac ante ipsum primis in faucibus.", 1);

        INSERT INTO Questions (student_id, created_dt, upvotes, question, answered)
        VALUES (1, '2021-05-29', 1, "Nunc consectetur, mi porta pellentesque tincidunt, risus quam dignissim elit, vitae cursus libero nunc eu augue.", 0);

        INSERT INTO Questions (student_id, created_dt, upvotes, question, answered)
        VALUES (2, '2021-05-27', 2, "Mi porta pellentesque tincidunt, risus quam dignissim elit.", 0);

        INSERT INTO Questions (student_id, created_dt, upvotes, question, answered)
        VALUES (2, '2021-05-26', 0, "Risus quam dignissim elit, vitae cursus libero nunc eu augue.", 1);

        INSERT INTO Questions (student_id, created_dt, upvotes, question, answered)
        VALUES (3, '2021-05-25', 0, "Nunc lacinia nibh a diam mattis, laoreet fringilla diam congue. Nulla dignissim maximus ligula sed fringilla.", 0);

        INSERT INTO Questions (student_id, created_dt, upvotes, question, answered)
        VALUES (1, '2021-05-28', 1, "Nulla dignissim maximus ligula sed fringilla. Donec quis fermentum eros.", 0);

        INSERT INTO Questions (student_id, created_dt, upvotes, question, answered)
        VALUES (1, '2021-05-23', 0, "Mi porta pellentesque tincidunt, vitae cursus libero nunc eu augue.", 1);

        INSERT INTO Questions (student_id, created_dt, upvotes, question, answered)
        VALUES (2, '2021-05-29', 1, "Vitae cursus libero nunc eu augue, mi porta pellentesque tincidunt.", 0);

        INSERT INTO Questions (student_id, created_dt, upvotes, question, answered)
        VALUES (3, '2021-05-28', 2, "Interdum et malesuada fames ac ante ipsum primis in faucibus.", 1);

        INSERT INTO Questions (student_id, created_dt, upvotes, question, answered)
        VALUES (1, '2021-05-27', 0, "Suspendisse ac leo sit amet mi luctus venenatis.", 0);

        INSERT INTO Questions (student_id, created_dt, upvotes, question, answered)
        VALUES (2, '2021-05-27', 0, "Nulla dignissim maximus ligula sed fringilla, Vitae cursus libero nunc eu augue, mi porta pellentesque tincidunt.", 0);

    3. Question List Page / User Detail Page:

        INSERT INTO Upvotes (student_id, question_id, upvote_dt)
        VALUES (1, 2, '2021-06-01');

        INSERT INTO Upvotes (student_id, question_id, upvote_dt)
        VALUES (3, 1, '2021-05-27');

        INSERT INTO Upvotes (student_id, question_id, upvote_dt)
        VALUES (2, 3, '2021-05-24');

        INSERT INTO Upvotes (student_id, question_id, upvote_dt)
        VALUES (1, 1, '2021-06-02');

        INSERT INTO Upvotes (student_id, question_id, upvote_dt)
        VALUES (2, 1, '2021-06-04');

        INSERT INTO Upvotes (student_id, question_id, upvote_dt)
        VALUES (2, 2, '2021-05-19');

        INSERT INTO Upvotes (student_id, question_id, upvote_dt)
        VALUES (3, 2, '2021-06-05');

        INSERT INTO Upvotes (student_id, question_id, upvote_dt)
        VALUES (1, 4, '2021-05-23');

        INSERT INTO Upvotes (student_id, question_id, upvote_dt)
        VALUES (3, 4, '2021-05-24');

        INSERT INTO Upvotes (student_id, question_id, upvote_dt)
        VALUES (3, 7, '2021-05-29');

        INSERT INTO Upvotes (student_id, question_id, upvote_dt)
        VALUES (1, 9, '2021-06-02');

        INSERT INTO Upvotes (student_id, question_id, upvote_dt)
        VALUES (1, 10, '2021-06-04');

        INSERT INTO Upvotes (student_id, question_id, upvote_dt)
        VALUES (2, 10, '2021-05-28');

        DELETE FROM Upvotes WHERE student_id = 1 AND question_id = 2;

        UPDATE Questions SET answered = 1 WHERE question_id = 1;

Part C:
    1. Login Form:

        SELECT student_id, avatar FROM Students WHERE email = "test1@test.com" AND password = "abc123";

        SELECT student_id, avatar FROM Students WHERE email = "false@email.com" AND password = "noMatch";

    2. Question List Page:

        SELECT Questions.question, Questions.created_dt, Students.avatar, Questions.upvotes 
        FROM Questions
        INNER JOIN Students ON Questions.student_id = Students.student_id 
        ORDER BY upvotes DESC, created_dt DESC
        LIMIT 10;

        SELECT Students.student_id, Students.avatar
        FROM Students
        INNER JOIN Upvotes ON Students.student_id = Upvotes.student_id
        WHERE Upvotes.question_id = 4
        ORDER BY student_id;

    3. User Detail Page:

        SELECT Questions.question, Questions.created_dt, Students.avatar, Questions.upvotes 
        FROM Questions
        INNER JOIN Students ON Questions.student_id = Students.student_id 
        WHERE Students.student_id = 2
        ORDER BY upvotes DESC, created_dt DESC
        LIMIT 10;