Overview:

In this programming project, you and your teammates will build a substantial database of your choice with a real-world application. It has five parts:

Part 1: Choose a project and design an E/R diagram. (Due Tuesday, 09/30/2008, 12:45pm)

Part 2: Design the relational schema. (Due Tuesday, 10/07/2008, 12:45pm)

Part 3: Create an actual database using MySQL. (Due Tuesday, 10/21/2008, 12:45pm)

Part 4: Queries and updates to the database. (Due Tuesday, 11/04/2008, 12:45pm)

Part 5: Embedded SQL. (Due Tuesday, 11/25/2008, 12:45pm)

The project counts as 25% of your grade. Each part is 5% of your grade.

Teams:

Team 1: Nathan Benjamin, nbenjamin@tamu.edu; Christopher (Corey) Laird, lairdcc@tamu.edu; Jessica Gonzales, unknownletter@tamu.edu; Irene Soto, isoto0041@tamu.edu (team communicator)

Team 2: Peiyi Lee, peiyilee@tamu.edu; Michael Simon, msimon101010@gmail.com; Curtis Engel, stickman17@tamu.edu; Lei Gu, gulei@tamu.edu; Richard Conrad, trombonium@tamu.edu (team communicator)

Team 3: Clint Burney, clintburney@tamu.edu; Joshua Gartner, joshg410@tamu.edu; Mark Metcalf, m-diddy@tamu.edu; Saeed Noori, snoori@tamu.edu; Jo Anne Rodriguez,  joanne0902@tamu.edu (team communicator)

Team 4: Charles Smith, csmith3332@tamu.edu; Prince Woodrow, abovedarim0305@tamu.edu; Gary Rabe, krabe3ag@tamu.edu; Casey Gonzales, bottomlesspit2@tamu.edu (team communicator)

Submission:

Turn in all parts of the project online using the CSNet. A signed hard copy of the project cover page should also be submitted in class.

Every team should choose one student as the team communicator, who will be responsible for turning in the project. For each project part, only one copy needs to be turned in electronically. However, as a team project, it is important for every student to participate in all aspects of the project. So with each part of the project, the submission should be accompanied with a statement describing the participation of each team member. The statement is part of the project cover page. Your signature in the cover page serves as a certification that the statement correctly describes what you did. Part of your grade will be based on how evenly everyone participated.

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.

Every student needs to apply for a MySQL database account in the CS department. Here is the link showing you how to apply for the account.

Part 1: E/R Diagram. (Due  Tuesday, 09/30/2008, 12:45pm)

Pick a database application that you really like working on! Then design an E/R diagram for it. The E/R diagram should have about five entity sets and about five relationship sets. Include different kind of relationships (e.g., many-one, many-many) and different kinds of data (strings, integers, etc.). You can, but are not required to, use features such as weak entity sets, "is-a" relationships, or roles.

1. Write a short (one paragraph or so) description of the database application. The description should be brief and relatively informal. If there is any unique or particularly difficult aspects of your proposed application, please point it out. Your description will be graded on its suitability and conciseness.

2. Specify an E/R diagram for your proposed database. Underline the key attributes for entity sets and include arrowheads indicating the multiplicity of the relationship sets. If there are weak entity sets of "is-a" relationships, be sure to notate them appropriately.

You should turn in: 1. A written description of the database application. Turn it in online as a separate file. 2. The E/R diagram. Turn it in online as a separate file. 3. The project cover page. Turn it in online as a separate file, and also turn in a signed hard copy in class.

Part 2: Relational Schema. (Due Tuesday, 10/07/2008, 12:45pm)

In this part, you will produce a relational schema for the E/R diagram that you submitted last time.

Using the method for translating an E/R diagram to relations, produce a set of relation schemas for your database design. Be sure to underline key attributes in the relations schemas.

For each relation, specify all the nontrivial functional dependencies.

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. Again, underline the key attributes, and specify all the nontrivial functional dependencies for the new relations.

Are there any nontrivial multivalued dependencies (MVDs) in your relations? If so, specify the MVDs, then decompose the relations into smaller ones so that each relation is in the Fourth Normal Form (4NF). Again, underline the key attributes.

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

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 should turn in: 1. Schemas for all relations in your database, with keys underlined. Turn it in online as a separate file. 2. For each relation, list all the nontrivial functional dependencies. Turn it in online as a separate file. 3. The project cover page. Turn it in online as a separate file, and also turn in a signed hard copy in class.

Part 3: Creating the Database in MySQL. (Due Tuesday, 10/21/2008, 12:45pm)

In this part of the project, you will create a relational schema for your database in the MySQL database system, and you will populate the tables in your database with initial data sets.

1. Familiarize yourself with the MySQL relational DBMS by reading the on-line documentation (referenced above in this webpage), 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 database 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 database, 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 list 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 relations. If you have available real data for your relations, then your program will need to transform the data into files of records conforming to your relational 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 database 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.

   Turn in your program code for generating or transforming data, a small sample of the records generated for each relation (5 or so records per relation), and a script log showing the successful loading of your data into MySQL.

You should turn in:

1. a script file containing SQL commands for creating each table in you database application (createTable.sql)

2. a script log file showing a MySQL session in which your tables are created successfully (createLog.txt)

3. a few (approximately 5-10) records of "realistic" data per relation (realData.txt or realData.doc or realData.pdf)

4. the commented source code for your program to generate or transform data.

5. written explanation of how your program works. This is not line-by-line commenting, but a high level global idea of how you generate your synthetic data. (explanation.txt or explanation.doc or explanation.pdf)

6. a small sample of the "synthetic" records generated for each relation by your program (5 or so records per relation) (syntheticData.txt or syntheticData.doc or syntheticData.pdf)

7. a script log file showing the successful loading of your "synthetic" data into MySQL per each relation (syntheticLog.txt or syntheticLog.doc or syntheticLog.pdf)

8. a script file containing SQL commands for inserting synthetic data (approximately 5-10 records as a sample) generated by your program (insertSyntheticData.sql)

9. a script file containing SQL commands (SELECT * FROM each table_name) for selecting the synthetic data per each table (selectSyntheticData.sql)

10. a script log file showing a MySQL session in which the SELECT commands are run successfully (selectSyntheticDataLog.txt)

11. Project cover page. (Turn in both an electronic copy and a hard copy.)

Part 4: Queries and Updates. (Due Tuesday, 11/04/2008, 12:45pm)

In this part of the project, you will issue SQL queries and updates against your database. Since you will be modifying your data as part of this assignment, we strongly suggest that you adopt a routine for getting repeated "fresh" starts with MySQL. You need to save and keep all SQL commands such as creating/dropping database, creating tables, inserting real data and/or synthetic data, querying tuples, updating tuples, etc., in script files. If needed, you just can run these script files again in order to re-setup your database.

Develop and test:

• At least ten SQL data retrieval (select) commands ( three general queries, 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).
• At least six SQL data modification ( two update, two deletion, two insertion) commands

Please note:

• For this assignment you will be invoking your SQL commands interactively through mysql. Certainly you should 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.

You should turn in:

1. a copy of all of your SQL commands (query.sql and update.sql)

2. a script log file showing a MySQL session in which your SQL commands are run successfully on both your small and large databases (queryLog.txt and updateLog.txt)

3. Project cover page. (Turn in both an electronic copy and a hard copy.)

Part 5: Using Java and Embedded SQL. (Due: Tuesday, 11/25/2008, 12:45pm)

In this part of the project, you will interact with your database from an external program. Your task is to build a moderately user-friendly interactive application program front end to your database using the Java programming language. Your program should consist of a continuous loop in which:

• 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.

You should include both queries and modifications among your options. As in Part 4, please include some "interesting" queries or modifications, i.e., operations that require some of the more complex SQL constructs such as subqueries, aggregates, set operators, etc. As a general example, if your database is a campus applicant database, then your interface might include in its menu a number of useful queries on the database, with both input and output in a format more convenient and pleasing than raw interactive SQL. Some queries perform statistical analysis requiring multiple levels of grouping, other queries are simpler.

• Insert a new student record.
• Insert a new application record.
• Update a student's address, GPA, or SAT.
• Update an application's decision field based on user input.
• Update application decision fields automatically, admitting students based on a combination of numeric values and quotas for each campus and/or major.
• Quit.

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

We are not expecting anything particularly fancy in terms of the interface itself. For example, in Java a menu printed via print function is fine. Also, handling of SQL errors can be quite simple. You can write a routine that just prints the error message from MySQL, or model your error handler after one of our sample programs.

You should turn in:

1. written explanation of how your program works. This is not line-by-line commenting, but a high level global idea of what is going on in your program. (explanation.txt or explanation.doc or explanation.pdf)

2. commented source code for your programs

3. Your embedded SQL based application must provide the following functions: connecting to a database, querying, inserting, deleting and updating. The more complex the queries that your application can handle, the more credit you will get. Turn in a file called captured-screen.doc (or .txt or .pdf) that contains screen captures showing examples of running each of your functions. Here is a way to capture a screen shot: Display the needed screen as full size on the monitor. Push "Print Screen" button on keyboard. Click right mouse button and then click paste button in Wordpad or Microsoft word (not Notepad) (Control-V button).

4. Project cover page. (Turn in both an electronic copy and a hard copy.)

Acknowledgement to: Arthur Keller of UC Santa Cruz and Jennifer Welch, TAMU, for their orignal versions of the project design.