SQL Assignment

Due: October 9, 2009

Create all of these tables in a single database. Ensure that all good design practices mentioned in class and chapter 6 are followed.

Note! I forgot MySQL does not support check constraints. Assignment has been changed to eliminate those.

  1. Create a table for users with a primary key userid, a name (which cannot be null), an address, and an email address (which must be unique).
  2. Create a table for stocks with a primary key stockid of 8 characters, a symbol (which must be unique), and a name (which must not be null).
  3. Create a table for stock purchases with a userid which must be in the users table and not null, a stock id which must be in the stock table and not null, a timestamp which is not null and a number of shares purchased which must be not null, and a price, which also must be not null. The foreign key for users must cascade, while the foreign key for stocks must prohibit the action. Make sure that MySQL supports the foreign key constraints. Insert 3 rows into the stock purchases table. One should violate a foreign key constraint, but the other two should be ok.
  4. Create a table for stocks sold with the exact same fields as the stock purchases table. In the create statement, add one row for all rows in the stock purchases table with the transaction timestamp being one day later, and the price being 10% more than in the stock purchases table.
  5. Create an agent table with the agents social security number as a primary key, a name, a start date, which must not be null, and an end date.
  6. Alter your user table (with the alter table command) to have the additional column of agent, which must be a foreign key for the agent table.
  7. Create a view of all of the transactions performed by an agent for his customers. Only include transactions if the agent was employed during the time of the transaction.
  8. Write a JDBC program to drop the users table and then recreate it (with the agent information) and then reload the data that was originally in the table (including any rows lost from the cascading delete). Your program should work even if additional rows are added to your tables. This question is worth 30 points.