CS 235: Assignment 4

DatingBase: Part 4

1. Develop and test at least ten SQL queries involving aggregation. For example, you can compute the following queries, if they make sense for your database:
   • The person with the most number of dates.
   • The most and the least viewed profiles.
   • The most messaged user.
   • The average number of views per session.
   • The names of all users who have sent messages to more than 20 other users.

   Turn in a copy of all of your SQL commands, along with a script illustrating their execution on your large database. Your script should be sufficient to convince us that your commands run successfully, but you can and should truncate query results after a few lines. Please do not turn in query results that are hundreds of lines long.

2. Modify your database schema declarations to include all different types of constraints that we covered in class:
   • primary keys (which you should have declared already)
   • foreign keys
   • attribute-based checks
   • triggers

   For each trigger, tuple-based constraint, and any complicated attribute-based constraint include a one or two sentence explanation.

   Here are some example constraints that depending on your schema may or may not be relevant to your implementation of DatingBase:

   • Every user must be at least 18 years old.
   • Every message must come from a registered user.
   • No user is allowed to send a message to herself or himself.

   As usual, turn in the file with your modified database schema declarations and a script showing a MySQL session in which your relations are created successfully.

3. Repopulate your database using your real data. Are there any constraint violations? If so, explain why they happened, e.g., your real data may be incomplete or inconsistent, or you may have over-constrained your database.

   Turn in a script that shows the loading of your data into MySQL.

4. Repopulate your database using your fabricated data. Are there any constraint violations? If so, modify your data-generating program and generate new fabricated data that conforms to all of your constraints.

   Turn in a script that shows the loading of your "original" fabricated data into MySQL and a script that shows the loading of your "new" fabricated data.

Problem Set

The name of the homework is CS 235 Homework 4. All questions in this problem set are multiple choice. However, to answer them correctly you will need to work out their long (general) answers. A correct answer is worth 3 points. You lose a point for each incorrect answer. You can take the assignment as many times as you like; only your last score will count. Note that you will probably get slightly different questions each time you take it.