Phase III

Due: February 14

You will translate your E/R diagram (and any additional constraints) into a relational schema in SQL Server. Use the account and database for your team. Keep the following requirements in mind:

  1. Pick reasonable domains for your attributes -- pay attention to sizes of numeric and character string fields, e.g.
  2. Know the FDs and NFs for each relation. Each relation must be in at least 3NF.
  3. You must use at least one CHECK constraint somewhere in your database.
  4. You must use at least one view somewhere in your database.
  5. Create any secondary indices you think might help the performance of common queries in your DB. You must create at least one secondary index somewhere in your DB and it should be a B Tree index (this is the default in SQL Server). Note that all your primary keys will automatically have primary B Tree indices created on them (and by default these will be clustered, but you can override that).
  6. Put enough data into each of your tables to convince yourselves that they "work": the foreign key-primary key relationships are properly set up, etc. You will add more data for Phase IV, if necessary.
For this phase of the project you will submit the following, in hardcopy, by the beginning of class on the due date:
  1. Your E/R diagram from Phase II (upgraded, if necessary)
  2. A printout of all your DDL statements (create table, alter table, create view, create index, etc.) used to achieve the above requirements.
  3. Just above each "create table" statement, an indication of the NF for that table (3NF, BCNF, etc.). If there are any FDs other than those with the primary key on the LHS, they should be listed as well.
  4. Just above each "create index" and "create view" statement, an explanation of the purpose of the index or view.