Fall 2007
updated July 31, 2007, minor corrections Aug 23
updated Nov 19, 2007: extended due date for WHAT TO HAND IN
- Specify conditions and responses for the system that does the job to be done. For this step, try to take the point of view of the functioning human system that will be interacting with the database.
- Condition-response specification will be useful for:
- Seeing need for various user views (external level of design)
- Generating appropriate test cases
- Obtain as many examples as possible of existing forms, reports, etc.
- For each row of the Condition-Response Table, name the Chunk of Data contained in the desired user's view. (Use all lower-case names as explained at the end of Step 2.)
- Make up an example of any user view that you don't already have as an existing form.
- Your example should help the class to see what kind of data will be included in the inputs and outputs. It should also help us to see the one-to-one and one-to-many relationships among the data. (In other words, your examples should help to produce a correct data dictionary)
- The data dictionary should define the composition of each Chunk of Data that must be stored, updated, or retrieved by the system. (Each Chunk of Data shown in the Condition-Response Table). The composition of each Chunk of Data should be defined using the data dictionary notation presented in class. Each Chunk of Data should be broken down into simple data elements. (Each data element will eventually become an attribute in the relational database.) Note that the data dictionary notation shows any repeating groups which occur within each Chunk of Data.
- Any data element whose meaning is not obvious should be defined in a brief phrase that helps us to understand what that data element means in the user's world. (i.e. what it means within the application(s) for which the database is being designed.)
- When naming your data elements and chunks of data, use all lowercase (using underscores_as_word_separators_to_improve_readability) to minimize problems later when you get to the last bullet of step 10.
First normal form is explained in the Sept. 6-11 reading in the schedule in the course syllabus.
Second normal form is explained in the Sept. 6-11 reading in the schedule in the course syllabus.
Boyce_Codd normal form is explained in the Oct. 9 reading in the schedule in the course syllabus.
In each table, show the primary key and any foreign keys.
- Alphabetical list of all attributes with a 1-line explanation of each
- List of all domains; which attribute(s) belong to each domain; legal values for finite & numerical domains
- Final data dictionary for each input & output user view
- (shows data composition of each input form/screen & each output screen/report)
- The data dictionary does not show how the data are actually laid out within the given form or report.
- Gather/generate test data - ready to be input into system
- Cases to include in the test data:
Try to cover the different cases which may arise in the real system (based on user requirements such as the Condition-Response Table)
- Show all relations on one diagram, with an arrow pointing from each foreign key to the primary key it refers to.
- Write the relational calculus expression needed to produce two of the output user views defined in your final data dictionary. (You'll get more credit for choosing two of your more difficult user views than for your easy ones.) Express your relational calculus in the notation and syntax used in handout 4.
- Create all your database tables using an SQL script, so that you can easily re-create the database from scratch as many times as you wish. This will make it much easier to make changes as necessary in your design and implementation after you've tried it out and seen how it works in practice.
- Most (but not necessarily all) of the user views defined in Step 6 should be implemented in some user interface package.
- You don't have to implement your user interface in PHP -- you're welcome to use any user interface software package that you know how to use, as long as it meets these four requirements:
- Your user interface must run in a web browser (Firefox or Internet Explorer).
- You must have access to some web server on which you can use your software package to build your user interface and make it accessible over the web.
- It must be possible to run your user interface in a web browser on the PC we use in class -- the PC in the classroom at UTSI whose screen is displayed in the UTK classroom. (You'll have mouse/keyboard control of that PC during your demo, as in the preceding presentations.)
- Your database must be implemented in PostgreSQL on the database server we use in CS 541, and your user interface software must be able to make connections to your database on the CS 541 PostgreSQL database server.
- To avoid unpleasant surprises, make sure your user interface package meets the four requirements above before investing a lot of time implementing your user interface. You should be able to test and verify this with my help any day after class while we still have the interactive video connection, and can make sure that your user interface approach works from the web browser in the classroom at UTSI.
- I'll need to know your database name, and also the IP address of the web server where your user interface code will be running, in order to set access permissions on our database server accordingly. (I can set these permissions for your chosen database name and IP address even if your database does not actually exist yet.)
- Any user views not implemented in your user interface should still be implemented in SQL scripts.
- (In other words, everything should be implemented in SQL. Most of this SQL will be called by your user interface. The rest can be called using the generic web-based SQL interface on the CS 541 main web page.)
- If you're using PHP, note that PHP variable names are case-sensitive even though SQL is not. To avoid problems and keep your sanity, use all lowercase (using underscores_as_word_separators_to_improve_readability) for database names, relation names, and attribute names. (Attribute values can be mixed case in both SQL and PHP with no problem since SQL, like PHP, is case-senstive inside quoted strings).
- Input all your test data using SQL scripts, so that you can easily re-create and populate the database from scratch as many times as you wish).
- As the implementation progresses, test the contents and behavior of your implementation against how you expect it to behave, and your specifications such as
- Condition-response table
- Domains of data elements
- Composition of user views
The purpose of this final presentation is mainly to demonstrate your
working system in operation. You should not present step 6
or the code used in step 10.
It would be a good idea to briefly present step 8 (referential integrity diagram) to
give the class an overview of what's in your system at the beginning
of your demo. Again, the main purpose of this last presentation is to
demonstrate your working system in operation, and to show how
you intend it to be used by users in practice.
Steps 1, 6, 8, 9, and 10 define what is expected as written
documentation of your project (where step 10 consists of all the code
needed to re-create your database and your user interface from scratch,
in the form of runnable SQL scripts and user interface source code). I don't need hardcopy of
Steps 1, 6, 8, 9, and 10, just put them in your electronic notebook and I'll print
them out from there.
These steps should be revised
as necessary to reflect any new thinking and to keep in synch
with any design changes you make during the implementation process.
The goal is that Steps 1, 6, 8, 9, and 10 when handed in should be consistent
with the final implementation as demonstrated.
Steps 7 and 11 are necessary for a successful demo, but I don't need copies
of them.
If you attempt to upload php files containing any html into your
electronic notebook, it may try to render the html rather than
showing your source code.
The workaround I'd suggest is to try zipping up a directory
containing all your SQL scripts and user interface source code,
then uploading the zipfile to your electronic notebook. If your
php files are on a Linux machine such as www.cs.utk.edu, then
I'd suggest you put them in a directory by themselves (at least
temporarily, for zipping purposes) and then
zip -r foo foo
where foo is the name of the directory they're in. See manpage
excerpt & explanation below.
(excerpt from Unix zip manpage)
To zip up an entire directory, the command:
zip -r foo foo
creates the archive foo.zip, containing all the files
and directories in the directory foo that is contained
within the current directory.
(explanation) in the command line above
- the first foo causes the archive to be named foo.zip
- the second foo causes the directory foo (and its subdirectories, recursively) to be zipped.
To retrieve the files, I'll download your foo.zip from your
electronic notebook and unzip them with
unzip foo.zip
Steps 1-12 above will be evaluated according to the following
criteria. These criteria will be evaluated in the context of the level of
difficulty of what was attempted. The idea here is that doing
reasonably well on something difficult can be more significant than
doing perfectly on something straightforward.
- Design of normalized tables
- Integrity design/enforcement
- Domain integrity
- Entity integrity
- Referential integrity
- Quality from technical perspective
- Quality from user's perspective
- How likely is it that the implementation would be successful in real-world use?
- Clarity and Understandability
- Having real users and an actual real-world system that guides and constrains your design.
- This is not an absolute requirement, but it is an evaluation criterion, in order to fairly take into account that, when you are trying to meet the needs of an actual real-world system, most of the steps in the project become more difficult.
This document was generated using AFT v5.0792