- Develop and test at least six SQL queries involving aggregation. For example, if your application is a dating site you can compute:
- The person with the most dates.
- The most and the least viewed profiles.
- 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.
- 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 and any complicated
attribute-based constraint include a one or two sentence explanation.
Note that you can use triggers to implement standard attribute checks.
Here are some examples constraints if your application is a dating site:
- 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.
- 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.
- 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.