Due: Monday, March 5

Please upload your SQL scripts to CSNS. Note that file uploading will be disabled automatically after 6PM of the due date, and no late submissions will be accepted for this assignment.

1. CREATE (10pt)

Given classes WikiPage, Revision, and User, 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 lab3-create.sql which contains CREATE TABLE statements to create the tables in a MySQL database.

2. INSERT (6pt)

Run your lab3-create.sql in your MySQL database to create the tables. Use INSERT statements to populate the tables with some test data so that each table contains at least two records. For this exercise you need to submit an SQL script lab3-insert.sql that contains the INSERT statements.

3. SELECT (18pt)

Complete the following queries. Note that when we say "wiki page <path>" (e.g. wiki page index), we mean the wiki page whose path is <path>.

1. List the content, author name, and timestamp of the latest revision of the wiki page index.
2. List the id, author name, and timestamp of all the revisions of the wiki page index. The results should be sorted by timestamp in ascending order.
3. List the id, path, author name (of the latest revision), and timestamp (of the latest revision) of all the wiki pages. The results should be sorted by timestamp in descending order.
4. Find whether the wiki page mypage1 exists. The result should be either Yes or No. HINT: use the CASE operator or the IF function.
5. Find the number of revisions created by the user John Doe.
6. Find the number of pages edited by the user John Doe.

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