INF3560: Database Design - Homework Assignments

• HW8 - More Stored Procedures and Triggers - Due. Mon. Dec. 26, 2005

  Click here to see the HW assignment.

• HW7 - Stored Procedures and User Defined Functions - Due. Mon. Dec. 26, 2005

  Click here to see the HW assignment.

• HW6 - Triggers - Due. Wed. Dec. 21, 2005

  Click here to see the HW assignment.

• HW5 - Dynamic SQL - Due. Monday Dec. 12, 2005

  Write a script that uses dynamic SQL to create a backup copy of a table (any table you like). The script should automatically create a new table. The new table should have the same structure as the original table. The name of the new table should be the same name as the original table concatonated with the date that the script was run. For example, if the original table's name is ORDERS, and the script was run on Dec 7, 2005, the name of the new table should be ORDERS_20051207 (or something similar).

  Hints: The following TSQL command will display the current date in yyyymmdd format:

      select convert(varchar(30),getdate(),112)

  The EXEC command differs when used for dynamic SQL then when used to call a stored procedure. When used for dynamic sql, the string must be in parentheses, e.g. EXEC (@mystring). When use for calling a stored procedure the name of the stored procedure is not in parentheses, e.g. EXEC myStoredProcedure.

• HW4 - Advanced SQL

  Answer quesitions 1-16,20-25 at the end of chapter 9 in the Mannino book

• HW3 - Basic SQL
  Due: W Nov 3rd, 2005 - before class

  Answer ALL of the following questions at the end of chapter 3 pages 111 - 114 in the Mannino book (i.e. Part 1: q. 1-38 AND Part 2: q. 1-10)

  Submit your HW to the HW3 folder (i.e. "drop-box") on Angel. You should upload a file to angel that contains a single .sql script file with all of the sql statements. Use SQL comments (i.e. -- and /* ... */) to identify the question numbers.

  The scripts to create the sample database associated with the questions are on Angel

• HW2 - functional dependencies, 2nd/3rd/Boyce-Code normal forms
  Due: M Sept. 12th, 2005 - before class

  1. Modify your tables from HW1 to convert them into 3NF (or BCNF). Include the same information that you did from HW1 (see the description for HW1 below).
  2. For each of the tables list all of the functional dependencies (FD) for that table. (e.g. field1 -> field2, field3) Only list the FDs for the versions of the tables that are in 3NF (or BCNF).

• Click here for the solution to HW1

  HW1 - creating database tables in first normal form
  Due: W Sept. 7th, 2005 - before class

  Design a set of database tables that will be used to track information for a movie production studio. The database should track the following information:

  • The names of the movies
  • the year which a movie was produced
  • the rating for the movie (e.g. G, PG, PG-13, R, etc.)
  • the producer's first and last names
  • the first and last names of the actors in the movie
  • which actors were starring actors in the movie and which were supporting actors
  • the amount of money each actor was paid for making the movie
  • the names and addresses of the theatres where each movie was shown (there can be many theatres, possibly thousands, where each movie was shown)
  • The number of tickets sold for each movie at each theatre
  • The price per ticket at each theatre - for the purpose of this assignment you should assume that a theatre charges the same amount of money for every ticket that it sells.

  There are potentially many different table structures that can be used to store this information in a relational database. Create as many tables and as many fields in those tables as you need. Feel free to create extra fields to hold primary key values if you wish. For each table make sure that you list ALL of the following information:

  • the name of the table
  • the names of each of the fields
  • indicate which of the fields in a table are part of the primary key for that table
  • indicate which of the fields in a table are part of a foreign key and the name of the other table that the foreign key is related to.

  YOU MUST MAKE SURE THAT YOUR TABLES ARE ALL IN FIRST NORMAL FORM

  Create a Word document with your work and submit it to the HW1 folder on Angel. For each database table fill out a form such as the following:

  Table Name: __________________________

  Field Name	Primary Key Field(Y/N)	Foreign Key Field (Y/N)	Related Table(s) (only enter this for foreign key fields)