For the next Thursday after break 3/20:

Overview of where we need to be (and please read down further to the assignment):

Our syllabus has the following due:

· Tech spec for required queries and pgm and UML diagrams and demonstrate experimentation with queries

· All required queries if all fields entered

I am also concerned with ensuring you have basic SQL commands down by now:

· Select

· Insert

· Update

Database Design concepts we will address in our next meeting (so don't worry about them now):

· Total and partial participation

· Disjoint and overlapping constraints

· Weak entity sets

· Normal forms

· Triggers

· Authority

Note: I changed the final project to resolve the detail payment amounts. I added a "level" field to the user interface, so there will be two different report designs based on which level. See here: finalProj.htm

----------------ASSIGNMENT SUMMARY------------------------------------
By 3/20, please have the following ready, and feel free to email questions especially since we missed a meeting, and feel free to start on it after break.

Summary of assignment: Write the technical specification for both the SQL queries you will need and the entire program that will prompt the user for the reports and print them. In summary, I want Use diagrams, class diagrams, detail written specifications on the report and sql builder classes (but you don't yet need detail specs on the screen). The SQL builder specification should be so detailed a programmer (your employee) would not need to know anything about Lytec to code the program and build the SQL commands.

Where my specification is not clear, please ask questions. Finding inconsistencies and weaknesses in my functional specification is actually part of your job.

-------------ASSIGNMENT DETAIL----------------------------------------------

Lots of detail on the assignment:

All diagrams should be done in pencil. Don't go overboard on details. At this stage, I want diagrams that can change and help us think, not pretty pictures that are too hard to change.

I think a good way to get a handle on this is to start by writing the SQL that you would use for 5 different possible entries the user might make into the GUI. So if the user just entered a from patient, sort by chart, sub total by loc and level procedure, what SQL statement(s) would you use? Then choose 4 more. Ensure you include one by procedure and another by bill.

Just create two use diagrams. The first use case is printing a bill level report and the second is to print a procedure (detail) level report. Include that the user will ask for the report, the program will build an sql string, query the database, accumulate totals and print the report. Create an exception (dotted line) usage for what happens when there is nothing found to report upon, and when the Lytec database cannot connect. (Add any other exception you can think of, but I cannot think of any.) These should be simple. If you find they get complex, stop.

We did not discuss details on how the screen and report processing should be designed. As a first cut, I would want at least the following classes:

· a screen class to handle the report screen interactions only

· a main control class to handle presenting the screen and asking for a report process when the user presses submit (We have only one screen now, but this may handle many screens in the future)

· a billing report processor to figure out which report needs to be called. (We have two now, but we want to design for more in the future.)

· An SQL handler that will connect to the database and do some formatting of SQL commands with the ability to create both views and select statements. (We only connect to MS SQL now, but we would want to connect to other databases in the future.)

· a bill level report handler

· a procedure level report handler

· *There will be functions common to both the bill level and procedure level report handlers. Decide which class should own them, or should a new class own them.

· *Which class do you think should own the report totals?

· Which class will hold the Lytec user and password? (Now we can just type that data in a text table, but eventually we want that to be able to be entered by the user.)

· Which class do you want responsible for determining that a blank start and end patient means all patients, and that only a blank end patient means to the end of the patient list?

· Who will be responsible for knowing how to get the procedure payment amounts, the bill primary paid, bill secondary paid, etc?

· Should each expected subquery be an action in the sql builder class?

Create a class diagram of the classes you think you will need to create. Include main data elements and actions.

Big written specification: Write a specification of the SQL handler program in detail so that a programmer would be able to code using specification while knowing absolutely nothing about Lytec billing. You are not writing it for yourself, but for your employee, who will be coding this and doesn't want to know anything about the data.

· It should have an action to be able to receive a connection request, which will make it open a connection to the Lytec database.

· It should be able to receive a request for a bill level or procedure level query with all the different sorts and selections on the screen and build an SQL statement. In this specification, you will work out how tightly the screen and report classes will be. Will the SQL handler just receive the class of the fields entered on the screen, or will the report processor do some massaging of that data before it hands the request to the SQL handler? Your class diagrams will give a clue, and this specification will make it more clear.

· If there are subquery or view creation functions, spec them here. Do not assume anything but the Lytec you have exists (so no views have been created in preparation for using this program).

· It should include all tables and column names

· Include some sample SQL statements that will be used. Create at least two samples for bill level and two samples for procedure level queries.

Small written specification: Write a small specification on the bill level and procedure level reports. Right now, I am more interested in the SQL builder, but you might find that as you flesh out these specifications, you find issues the SQL builder will need to address.