Overview

Part 1: Choose a project and design an E/R diagram. Due Tue, 9/18/07.

Part 2: Relational design. Due Tue, 10/02/07.

Part 3: Create database in MySQL. Due Tue, 10/23/07. (*new due date*)

Part 4: Queries, updates, and indexes. Due Thu, 11/08/07. (*even newer due date*)

Part 5: Embedded SQL. Due Tue, 12/4/07. (*newest due date*)

The project is worth 35% of your course grade, broken down like this: Parts 1, 2 and 3 are worth 5% each. Parts 4 and 5 are worth 10% each.

Back to beginning

Logistics

• Every student will complete this project individually.

• Turning in project work:
  • All parts of the project will be turned in electronically using the turnin program in the CS Department intranet (CSNet). Instructions are here. No hand-written or hand-drawn material will be accepted. Every submission must be turned in with this cover sheet, which should be signed and turned in by hand in class.
  • ONLY use the file names given below for each part.
  • For each part of the project, bundle all the files into one archive (.tar or .zip file).
  • Submission file format: (1) for diagrams, TA can only open .pdf, .doc, .JPG or .ppt files; (2) .SQL (SQL script files) and plain text files (i.e. .txt files;) will be preferred for all other submissions. Please don't use other file formats.

• MySQL:
  • We will use the DBMS MySQL for the project. You should start getting familiar with MySQL via their web page. A good place to start is the tutorial.
  • An account for using MySQL on the CS department machines has already been made for you. Your account name is your cs user name. The initial password is your UIN. (Note: if you already had a MySQL account, your password has also been reset, but your data should be untouched.) Instructions for changing your MySQL password are here.
  If you have problems with your MySQL account, please contact the CS Helpdesk.

Back to beginning

Part 1: E/R Diagram

Try to pick an application with a schema that is relatively substantial, but not too enormous. For example, your E/R design should have in the range of six or so entity sets, and a similar number of relationship sets. This is a ballpark figure only - shooting for somewhere between 5 and 10 is fine - you'll sense if your design is too simple or too complex. You should certainly include different kinds of relationships (e.g., many-one, many-many) and different kinds of data (strings, integers, etc.), but your application need not necessarily require advanced features such as weak entity sets, "is-a" relationships, or roles.

1. Write a short description (no more than one page) of the database application you propose to work with throughout the course. Your description should be brief, relatively complete and clear. If there are any unique or particularly difficult aspects of your proposed application, please point them out. This part is worth 1 point and your description will be graded on suitability and conciseness.
2. Specify an E/R diagram for your proposed database. Don't forget to underline key attributes for entity sets and include arrowheads indicating the multiplicity of relationship sets. If there are weak entity sets or "is-a" relationships, make sure to notate them appropriately. This part is worth 4 points. Points will be subtracted for poor or wrong design, wrong shapes or arrows, missing or wrong key attributes, wrong relationships, not complex enough, etc.

MAKE SURE TO SAVE A COPY OF YOUR E/R DIAGRAM - YOU WILL NEED IT FOR SUBSEQUENT PROJECT PARTS.

Back to beginning

Part 2: Relational Schema

1. Please attach a copy of your E/R diagram from Part 1. If you would like to make changes to your original E/R diagram at this point (due to staff feedback or any other reason), you may do so. The new E/R design will be used as a basis for grading part 2.

2. Using the method for translating an E/R diagram to relations, produce a set of relations for your database design. As usual, please be sure to capitalize key attributes in your relations.

3. For each relation in your schema, specify a set of completely nontrivial functional dependencies for the relation. Any functional dependencies that actually hold in the real- world scenario that you're modeling should be specified, or should follow from the specified dependencies. Don't worry if you find that some of your relations have no nontrivial functional dependencies.

4. Is each relation in your schema in Boyce-Codd Normal Form (BCNF) with respect to the functional dependencies you specified? If not, decompose the relation into smaller relations so that each relation is in BCNF. Be sure to capitalize key attributes in your new relations. Don't worry if you don't have any BCNF violations - many PDAs will not have any.

5. Are there any nontrivial multivalued dependencies that hold on any of the relations in your schema? (You needn't consider MVD's that are also functional dependencies.) If so, specify the multivalued dependencies, then decompose the relations into smaller ones so that each one is in Fourth Normal Form (4NF). Be sure to underline key attributes in your new relations. Don't worry if you don't have any 4NF violations - most PDAs will not have any.

6. Now that you've decomposed your relations as far as possible, are there any relations that could be combined without introducing redundancy (i.e., without creating BCNF or 4NF violations)? If so, combine them.

7. Is there anything you still don't like about the schema (e.g., attribute names, relation structure, etc.)? If so, modify the relational schema to something you prefer. You will be working with this schema quite a bit, so it's worth spending some time now to make sure you're happy with it.

Back to beginning

Part 3: Creating your Database in MySQL

1. Familiarize yourself with the MySQL relational DBMS by reading the on-line documentation (referenced above under logistics), logging into MySQL, trying some of the examples in the document, and experimenting with the various commands. You don't need to turn anything in for this part.

2. Create relations for your PDA based on your final relational schema from Part 2. Use the CREATE TABLE command to specify each relation, its attributes and attribute types. If you have an attribute that represents a date and/or time, you may want to look at this page.

3. Turn in a script log showing a MySQL session in which your relations are created successfully, as described next. For each relation in your PDA, create an execution script file and a few (approximately 5-10) records of "realistic" data. Then execute the script file from the mysql command line using the \T option. Turn in a listing showing the contents of the files you created, the successful loading of the data into MySQL, and the execution of "SELECT *" commands to show the contents of each relation.

4. Write a program in any programming language you like that creates large files of records for each of your PDA relations. If you have available real data for your PDA, then your program will need to transform the data into files of records conforming to your PDA schema and to MySQL's load format. The rest of you will need to write a program to fabricate data: your program will generate either random or nonrandom (e.g., sequential) records conforming to your schema. Note that it is both fine and expected for your data values, strings especially, to be meaningless gibberish. The point of generating large amounts of data is so that you can experiment with a database of realistic size, rather than the small "toy" databases often used in classes. The data you generate and load should be on the order of:
   • At least two relations with thousands of tuples
   • At least one relation with hundreds of tuples
   If your application naturally includes relations that are expected to be relatively small (e.g., schools within a university), then it is fine to use some small relations, but please ensure that you have relations of the sizes prescribed above as well. When writing a program to fabricate data, there are two important points to keep in mind:
   • Make sure not to generate duplicate values for key attributes.
   • Your PDA almost certainly includes relations that are expected to join with each other. For example, you may have a Student relation with attribute courseNum that's expected to join with attribute number in relation Course. When generating data, be sure to generate values that actually do join - otherwise all of your interesting queries will have empty results! There are a couple of ways to properly generate joining values. One way is to generate records for multiple relations (e.g., Course and Student) at the same time. Another way is to generate the records for one relation first, and then use the joining values for the other relation. For example, you could generate records for relation Course first, then use the Course.number values when creating values for Student.courseNum.

Back to beginning

Part 4: Queries, Updates, and Indexes

(a) Queries and Updates

Please note:

• For this assignment you will be invoking your SQL commands interactively through mysql. Of course you should certainly build a script file, rather than typing in the queries each time you run them.

• Please write "interesting" queries, in particular,
  • three general queries with at least two conditions (For example, SELECT t1.c1, t2.c2 FROM t1, t2 WHERE t1.c1 > 10 and t2.name = `Texas`),
  • two "join" queries (related with three tables),
  • one "union" query,
  • one "group by" query,
  • one "order by" query,
  • one "DISTINCT" query, and
  • one "Aggregate" query).
  You should try to use most or all of the SQL constructs discussed in class and in the textbook. You will not receive full credit if your queries and modifications are all extremely simple.

• We suggest that you experiment with your SQL commands on your small hand-created database before running them on the large database for which you generated data. Initial debugging is much easier when you're operating on small amounts of data. Once you're confident that your commands are working, try them on your large database. We do expect that the commands you turn in work properly on both your small and large databases.

• If you discover that most or all of your "interesting" queries return an empty answer on your large database, check whether you followed the instructions in Part 3 for generating data values that join properly. You may need to modify your data generator.

• Turn in a copy of all of your SQL commands, along with a script illustrating their execution on both your small and large databases. Your script should be sufficient to convince us that your commands run successfully, but you can and should truncate query results after a few lines. Please do not turn in query results that are hundreds of lines long.

In Part 4 (a), you may have discovered that some queries run very slowly over your large database. As discussed in class, an important technique for improving the performance of queries is to create indexes. An index on an attribute A of relation R allows the database to quickly find all tuples in R with a given value for attribute A (which is useful when evaluating selection or join conditions involving attribute A). An index can be created on any attribute of any relation, or on several attributes combined. See this page for more information on how to create an index in MySQL.

1. Create at least three useful indexes for your PDA. Run your queries from part 4(a) on your large database with the indexes and without the indexes. Turn in a script showing your commands to create indexes, and showing the relative times of query execution with and without indexes. Here too, please truncate any large query results.

2. You can use an execution script file that creates your indexes to ease your testing. Use the \T command (Set outfile [to_outfile]. Append everything into given outfile) to call your queries script file both before and after you create the indexes. Surround the calls to execute the queries files with lines that execute time functions in MySQL; so you can measure elapsed time. You can then easily calculate elapsed time manually and add it to your script log as a comment. You can note your added comments by enclosing them with /*...*/. For an added challenge you can try to get your execution script file to calculate the elapsed time and display it for you.

• MySQL automatically creates indexes for attributes declared as primary keys.

• Actual timings will be affected by external factors such as system load. However, for some of your queries, with appropriate indexes you should see a consistent dramatic difference between the execution times with indexes and the times without. If others of your queries do not show performance improvement even when relevant indexes are created, please include a short note suggesting why this might be the case. The plans described by the EXPLAIN should be helpful in understanding why things take as they do.

Back to beginning

Part 5: Using Java and Embedded SQL

• A list of at least five alternative options is offered to the user. (An additional alternative should be quit.)
• The user selects an alternative.
• The system prompts the user for appropriate input values.
• The system accesses the database to perform the appropriate queries and/or modifications.
• Data or an appropriate acknowledgment is returned to the user.

Your application code should interact with the database using the JDBC call-level interface for Java programs. You can refer to following links.

• Basic JDBC concepts
• JDBC Reference
• Writing JDBC Applications with MySQL

Back to beginning