HW8 - More Stored Procedures and Triggers - Due. Mon. Dec. 26, 2005
HW7 - Stored Procedures and User Defined Functions - Due. Mon. Dec. 26, 2005
HW6 - Triggers - Due. Wed. Dec. 21, 2005
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
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:
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:
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) |
|---|---|---|---|