Create a relational database consisting of at least three related tables, using a variety of datatypes. Query the database and modify the data it contains in various ways (specifications to be discussed in detail as the semester progresses).
 
Plan for at least two of the following elements (each item listed counts as one even if you have more than one of that item):

• One or more required columns
• Appropriate indexing
• One or more identity columns
• User-defined datatype
• One or more default values or constraints
• Calculations of various kinds

Build in at least one mechanism for ensuring the integrity of your database (constraint, check, trigger)..

Create the tables you have designed, including specifications from the list above as appropriate. If possible, grant select rights to instructor (and to rest of class, if you wish).

Enter at least 5 rows of data in each table (fewer if appropriate and you have more than 3 tables).

Write the following data manipulation statements to select or change data in your database:

• Show all the data in each table, sorted explicitly on at least one of the columns.
• Select a grouped subset of data from at least one table.
• Select a subset of data from more than one table (using a single SQL statement).
• Produce a report using aggregate functions and/or mathematical operations.

• For at least one of the items above, provide column headers that are not in the table.

• Show at least one mechanism you have built in to preserve the integrity of your database, and show how it works.
• Use at least three different Sybase stored procedures to show information about tables, columns, indexes, etc.
• Delete one or more rows meeting particular criteria from at least one table. (Do this last!)

 Due dates:

•  Preliminary database project proposal                                                  Sept. 23

•  Detailed database project design                                                          Sept. 30

•  Table structure with representative data                                                Oct. 28

•  Database project completed:

• Optional in-class presentation                                                      Dec. 9
• Hand or mail in SQL statements (*)                                             Dec. 9

(*) Material to hand or mail in:
 

• If your database design is different from the original proposal, 1-2 page revised description of the project (purpose, structure, relationships, how the database might be used)
• SQL statements used to create the tables and other database objects (input files only)
• Results of running the various queries and procedures (output files)