Due: Wednesday, May 11, 2PM for Part I and 4PM for Part II

Part I. Database Schema Design (10pt)

Given classes User, Question, Answer, and Vote, please design a database schema for the data described by these classes. The table and column names should follow these naming conventions:

• Names should use full words instead of partial words. For example, use password instead of pwd.
• Table names should be in plural form, e.g. users instead of user, and column names should be in singular form, e.g. email instead of emails.
• Names should use lower-case letters. When a name consists of multiple words, use underscore to concatenate them. Do not use mixed cases in names. For example, use first_name instead of FirstName.

For this exercise you need to submit an SQL script lab2-create.sql that contains CREATE TABLE statements that create the tables in MySQL.

Part II. SQL (25pt)

Run the SQL script qa-create.sql (the script will be available after 2PM) in your MySQL database.

Use INSERT statements to populate the tables created by qa-create.sql with some test data so that each table contains at least two records. For this exercise you need to submit an SQL script lab2-insert.sql that contains the INSERT statements.

Complete the following queries:

1. List the id, title, and timestamp of the questions posted yesterday.
2. List the id, title, and timestamp of the questions with the tag "java".
3. List the id, title, author's first and last name, and timestamp of all the questions ordered by timestamp in descending order (i.e. the most recent first).
4. List the tags of the question with id=1.
5. List the content, author's first and last name, and timestamp of the answers to the question with id=1.
6. List the content, author's first and last name, timestamp, and sum of the votes of the answers to the question with id=1. The results should be ordered by the sum of the votes in descending order.
7. Find the average rating of the answers given by the user whose username is cysun. The rating of an answer is the sum of the votes for the answer.
8. List the first and last names of all the users and the average ratings of their answers. The results should be ordered by the average rating in descending order.
9. Find the question that has received the most number of answers today (i.e. the most popular question of the day).
10. List the questions that have yet to receive any answer.

For this exercise you need to submit an SQL script lab2-query.sql which contains your queries.

[Post-Lab Activities]  

Discuss the pros and cons of adding a column rating to the answers table. This column would store the sum of the votes for each answer.