Create a relational database consisting of at least three related tables and 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).
 

 Detailed specifications:
Design a database that contains at least 3 related tables (without counting tables you may need to add for many-to-many relationships). Information stored in the database should be of various datatypes, if possible, but don't "force" the design to fit multiple datatypes. 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 Sybase stored procedures to show information about your tables, columns, indexes, etc.

• Delete one or more rows meeting particular criteria from at least one table. (Do this last.)

 Revised due dates:

•  Table structure with representative data                                                Oct. 28

•  Database project completed:

• 5-10 minute in-class presentation                                                 Dec. 16

• Hand or mail in SQL statements(*)                                               Dec. 16

(*)Material to hand or mail in:
 

• 1-2 page (revised) description of the database project (structure, how it might be used)
• SQL statements used to create the tables and other database objects
• the results of running the queries and procedures listed above (in other words, your output files)