DATABASE MANAGEMENT SYSTEMS (CSE530)
Fall Semester, 2005
 		 FD Project
| Lab Hours: |  Teaching Assistants | Graders email account |
Functional Dependencies Project Description - Individual Database Optimization (a bottom-up approach)

Using a combination of SQL and a host language, you must prepare and load the data into a schema will all foreign key, primary key, unique and index constraints for an application that could be used to provide the following functionality:

  1. A normalized database derived from all fields in the project database
  2. Data entry for new records
  3. Data modification and deletion for a specifically identified product
  4. At least the following queries:*
  • Allow a person to select a dosage form and a single ingredient from lists of each and show the product brandname and dosage form of all products containing that ingredient in the specified dosage form
  • Allow a person to select a product brandname,  the brandname's strength (if any), the dosage form, the route of administration and the package from their respective lists, in any order, and show the ingredients for that product organized in ascending order for all fields but where ingredient quantities are in descending order
  • Allow a person to enter up to three ingredients and show the location of all firms that make the specified combination of ingredients
  • Allow a person to enter a company name and show all product brandnames for that company, along with the brandname and company name for all products in the same dosage form and given by the same route of administration on the market that contain the same combination and strength of ingredients as each product made by the specified company

*You do not have to write the application to implement these queries.  You can create views or stored procedures instead.

Turn in documentation for this product by utilizing Items I-III below.  Your database, source code, conceptual design using an Entity Relationship Model, a diagrammatic illustration of the Steps of Normalization, and consideration and application of Indexes and Keys must be included in the final submission.

Grading will be subjective, but every effort will be made to evaluate you on your database functionality (10%), SQL code(20%), the ER design meeting the business needs in 1-4 above, the resulting relational model - including the use of constraints and access methods (20%), and the 5-10 pages of documentation you provide regarding consideration and discussion of:

  1. Conceptual Database Design using 1) an entity-relationship diagram and 2) a relational model for the highest level of normalization in your resulting tables for the specific business needs described in 1-4 above
  2. The steps of Normalization and their impact on the speed of each query and deletion of a minimum of 5% of records (e.g., 1NF - speed of queries 4.a-4.d; your final normal form (BCNF or 3NF) - speed of queries...), and
  3. The impact of constraints and Indexes on the speed of each query and concurrent update and deletion of a minimum of 5% of records for 1NF and your final NF

*Email a zip file containing your lab material to cse530gr@cec.wustl.edu by noon on the date indicated.  Grades for any submission received after the designated time will be reduced by 10 % for each 24 hours late.

For those who want to apply attribute names.

Due November 18: Answers to the following questions should be submitted to the graders' email account:
  1. Is the database in 1NF?  Why or why not?  If not, describe how you would normalize the data to produce 1NF.
  2. Use the data to identify and list ONE functional dependency that satisfies 1NF, but not 2NF.  Describe how you would normalize the data to avoid this by identifying the resulting relations.
  3. Use the data to identify and list ONE functional dependency that satisfies 2NF, but not 3NF.  Describe how you would normalize the data to avoid this by identifying the resulting relations.

Use the following design for questions two and three:

FD violating xNF but not yNF Explanation as to why Resulting tables once normalized to avoid this unnecessary redundancy
    Table 1:
Attributes:		 Table 2:
Attributes:
       
Due December 16: completed project (minus the data) should be submitted to cdbutler@seas.wustl.edu.