In this lab exercise, we'll review relational design and normalization and the finish the design from lab #2.

Creating a Database Schema from an ERD

Divide into your teams from last week and dig up the ERD you built. Build a relational database schema that matches the structure specified in your ERD. Remember to specify all the relation schemata with their primary keys

Normalizing Your Schema

Consider your database schema with respect to the normal forms discussed in class and in the text. Address the following questions:

• Do you see any potential data redundancy here? If so, where?
• What are the functional dependencies?
• What are the candidate keys of the relations?
• Are your relation schemata in 1NF? How about 3NF? BCNF? If so, show why, if not, decompose them so that they are or be prepared to give some coherent explanation of why they should stay in their de-normalized form.

Check with me on your analysis and final schema before going on to the next step.

Creating an MS Access Database for Your Schema

Now that you have a reasonable schema, fire up Microsoft Access, build the schema, and add a few sample records for each table. Use the relationships editor (on the toolbar) set up the referential constraints properly.

Ask questions if you have trouble with this. You've used Access to build tables and enter data before (in IS-141). You can refer to the text, chapter 9, particularly the early sections, for more help here.

The only potentially new task is that of using the relationships editor. This editor allows you to define the referential integrity constraints that operate between your tables. Start this editor (by choosing "Tools"-"Relationships") and then add all the tables you have defined to the working area (by clicking mouseRight-"Show Table"). When this is done, you can define your relationships by dragging the primary key field name from one table onto the foreign key field of another.

Checking In

To conclude, each team will present its database schema and database. Explain its normal form and the work you had to do to get it in that form, then show the database and its relationships. Be sure to save your results for potential use in future labs.