Formal specifications

Beginning of class on June 3

Note that we expect your final project to have some sort of rudimentary GUI - but it doesn't have to be fancy. The goal of this checkpoint is for you to tell us exactly what to expect from your final application. Check here for an example of the formal specifications.

You should hand in the following:

• A cover page. We need to make sure we know who you are.
• An updated E/R diagram for your project. If it has changed from your previous submission, say why. If it has not, say so, but please send along another copy of the old one.
• The schema derived by translating from your ER diagram (above). For each table:
  • List the table definition e.g., Table1(attr1: domain1, attr2: domain2...)
  • Specify the primary key, foreign keys, and other constraints that the table has to maintain
  • List all functional dependencies that are applicable to the table (including the ones involving the primary key). For each functional dependency, briefly describe its meaning in English.
  • Normalize the table, if necessary, to be in 3NF or BCNF (you don't need to give a formal proof about 3NF or BCNF.) Give the list of tables, their primary keys, and foreign keys after normalization.
• The SQL DDL to create all the tables in SQL. All primary keys and foreign keys must be declared appropriately.
• Populate each table with at least 5 tuples. Show the instance of each relation after inserting the tuples.
• What platform you will use. Now that you've had time to think it over more, and perhaps experiment some with PHP & JDBC, it's time to decide which platform that you want to use. In particular, you must say if you are using:
  1. The CS Ugrad Oracle installation and JDBC
  2. The CS Ugrad Oracle installation and provided PHP/Apache
  3. Other. For other, you must say what you are doing, and each of you must sign a form (or e-mail me) indicating that you realize that you are responsible for whatever difficulties you will have
• What functionality the final application will have. By this we mean that we expect to know, what will the different users be able to do your system. Essentially, you should tell us:
  • Which classes of users are you going to provide different interfaces to the data (e.g., in a banking domain, I might have one interface for the bank employees and one interface for the customers)
  • For each class of user, what are you going to allow them to do? At a minimum, you need to explain what kinds of queries you expect your users to be able to ask and get answers for. You're welcome to provide exact queries if you'd like, but that's not required.

    Note that we expect you to have both the ability to update at least some part of the database from the application, and the ability to have some queries that depends on the user's input (e.g., in the banking domain, I might want the customer to be able to update the database by transferring money from one account to the other, and I might also want the bank employees to be able to ask for all of the account information for a given customer, where the employee enters the customer number).

    We also expect that some of these queries will be something interesting - something beyond a simple select, project, join. In the banking domain, one example would be to have a report generator that would generate for each customer, the set of statements the month for each account, including a list of transactions and a beginning and ending account balance.

    If you anticipate having any triggers or assertions, this would also be functionality that would be valuable to present.

• What data will be in the final application (It's fine to just reiterate what you said in the schema-checkpoint). Our goal here is to make sure that the application will still work well with the data that you have. For the same reason, including your schema with your report would also be helpful, though not required.
• What the division of labour will be. We want to know how you're planning on splitting up the work. E.g., we'll all figure out what the rough application should look like. Team Member X will be rough out the GUI, Team Member Y will populate the database....
• Write the code to connect to the database of your choice and perform a meaningful selection query. This is the first step to start implementing your project. You would need to submit the code that creates a connection to the database, and performs a selection query that is relavent to your project. To prove that your code works, you need to submit the screenshot of the result of your selection query. It could just be printed from the command line. Note that you don't have to implement the UI at this point, we would just like to make sure that you are able to connect to the database of your choice.
• Anything you'd particularly like feedback on For the most part, we've been providing you with feedback on what we think you need to know about the project. As well, you are always welcome to drop by Hassan or the TAs' office hours or send us e-mail if you want more feedback on something. However, if there is anything in particular that you would like more detailed feedback on, please make sure to include it here.