Off-site file management

1. CS8630 : AIA: Vault Off-site file management Brian Paige (Where’s my file?) CS 8630, Summer 2004

2. Initial Problem: CS8630 : AIA: Vault • Limited on-site storage for files, resulting in • Staff routinely culling the file room and shipping inactive files to off-site facility • Listing of off-site files kept in two manually written binders. At time of introduction to problem in 2004, off-site facility & binders had files back to 1945. • When requesting file, user looks up name of student in book, then contacts off-site facility for delivery of file.

3. Problems with existing system: CS8630 : AIA : Vault • User had no way of knowing if file had already been requested, and if so when and by whom. • Management had no information on how often files were requested, to assist in controlling costs associated with retrieval and search. • No history of request information was kept, to assist in logging known issues with files. Can assist to reduce cost if users only need to look at system rather than request file.

4. Requirements gathering and system definition: CS8630 : AIA : Vault • Spoke with Registrar and users to determine what exact problems existed with current system • Determined which could be handled by a DBMS and which needed procedural refinement • Created drawings of sample interfaces, with descriptions of how these would work. Received feedback on interface design, transaction processing, further system requirements. • Defined final version of interface, along with business rules. Reverse engineered system design from these.

5. System design: CS8630 : AIA : Vault • Given known volume of files currently in off-site location, and anticipated growth in the system, DBMS was specified to have Oracle back end for tables and Access front end for interface.

6. System Design: Tables: TContainers: contId (PK) container number, location, memo field for description of contents Used to indicate basic information on each container at off-site facility TContHist: id (PK), subject, date, time, username, memo field for notes on request, containerID (foreign key from TContainers.contid), location field, boolean field “requested”, boolean field “inserted” Used to hold history of requests, along with notes on each, concerning containers TContacts: id (PK), Lname, Fname, Company, VoicePhone, FaxPhone, Email, webURL Holds listing of contacts at off-site facility TUsers: userid (pk), username, LastReal, FirstReal Holds usernames for those with permission to use system, along with their real last and first names CS8630 : AIA : Vault

7. System Design: Tables (continued) TStatus: id (PK), status, text Holds the listing of statuses approved for the system, along with explanatory text which is used when printing reports TReport: id, boxno, ssn, lname, fname, maiden, mid1, status, date, text, user Temporary storage location used as source file for generating reports and updating history tables. TStuinfo: id (PK), ssn, SID, boxno, location Holds the SSN, Student ID, box number, and location of file TStuinfo2: id(PK), Lname, Fname, mid1, maidenName, TStuinfoMain (foreign key from TStuinfo.id) Holds the name (last, first, middle, maiden) of student, and the id number from TStuinfo with which it is associated. TStuHistory: id (PK), subject, date, time, notes, user, fileID, location, boolean field “requested” Used to hold history of requests for student files. fileID is FK from TStuinfo. CS8630 : AIA : Vault

8. Rationale behind table design: CS8630 : AIA : Vault Separate Histories for containers and Files: • Does require more administration space for two entities that are essentially duplicates in structure. • Contents of the two entities are related to two separate structures. • Files are requested more often than containers, with the effect that the file history will grow faster than the container history Status table: • Approved statuses held in one location to maintain control over which statuses are introduced to and used in the system. Student information broken over two relations: • All files have student names • Given age of files, not all files have SSN or student ID numbers.

9. Triggers/Stored Procedures: CS8630 : AIA : Vault • Given that triggers are automatically executed on update/insert/delete, I decided against using them in this project. They can be very useful, but I decided on stored procedure. • Part of the decision guiding this was the comfort level of the users in working with any computer. • It was reasoned that it was better to run a stored procedure when, for example, forms are opened and closed thus signaling that user is finished with manipulating records, rather than running triggers immediately after manipulation of records. • User might insert new record in history file, enter a location, then change the location and/or wind up deleting the new record. • Each of these are handled by elements of the stored procedure, but implementing them as triggers would result in the DBMS doing more work than is necessary. • Stored procedures handle update, insert and delete functions for each of the tables in the DBMS. In cases such as containers, inserting a record into for container history also inserts that record into the history of each file in that container. Changing the location of a file affects only that file, but changing the location of a container affects the location of all files in that container.

10. Views/Queries: CS8630 : AIA : Vault • Access permits forms and reports to use SQL in the background for the definition of their data source. Given this, there is no need for views or queries to be defined independent of the forms and reports. • Might have some use as an ad hoc feature, but if this is needed can define these when required.

11. Stored Procedures in this project handle: CS8630 : AIA : Vault • Record Navigation • Includes moving from record to record in a set (as with container screens) and the locating of records from the main file location screen. • Opening / closing of forms (most close the form from which user navigated to assist in keeping screen clean and reducing the number of windows displayed in task bar at bottom of screen). • Actions to be taken after insert/update/delete • While these can be handled by triggers, the procedures can be specified to work after a form closes or opens, which reduces the work the DBMS is required to do. • Error checking • After entering the new location for the file and any short subject, system must insert the record into the history table prior to user entering extended notes in the note field. Doing otherwise would result in some information being entered in one record while the rest is recorded in another.

12. Security: CS8630 : AIA : Vault • There are several potential loopholes in the Access front end, which must be handled by use of code, macros, and user-level security. • Created a workgroup independent of system default group • Permits complete control over settings of each user’s capabilities • In joining this workgroup, forces users to log into the database prior to being able to use it

13. CS8630 : AIA : Vault Security (cont’): • Created user and administration accounts • User accounts have ability to read/insert/update history, file and container records. User accounts can delete history records, but cannot delete file or container records. • User accounts are individually established on each machine. User accounts do not require passwords, but because only that user’s account exists on their machine, they can’t log in as anyone else. • Administration accounts have complete access to all objects and user accounts. Administration accounts are password protected. Given small office size and relative lack of technical sophistication in users, administration accounts are not subject to expiration of passwords. • All users (basic and admin) must also log in using a second login (one login, described above, for interface, one separate for the Oracle DBMS. *Double login is anticipated to be handled by stored procedure in future developments.

14. Security (cont’d): CS8630 : AIA : Vault • Created Autokeys macro to disable certain functions of Access front-end • Disabled Control-G function used to pull up the code window for the stored procedures • Disabled the F-1 function key. This key has a different meaning with another widely used application, and is known to cause difficulties when users switch context to Microsoft products. • Password protected stored procedure code • Even if user is able to get past the Control-G to bring up the code, they would need the password to view the stored procedures for the project. • On Startup bar, (Tools>Startup) disabled special access keys, full menus, shortcut menus, display database window and display status bar options.

15. CS8630 : AIA : Vault Security (cont’d): • However, user can still see the database window by holding down the Shift key at startup, enabling them to see the otherwise hidden database window and all objects in the system. • Handled by setting the properties of each object in the project to “Hidden.” • Not a very strong precaution. Savvy user can hold down Shift key at startup, go to Tools>Options, and check the Show Hidden Objects field. One might think this requires a good level of familiarity, but pathway can be found by simply typing “Show hidden objects” into help screen. • User might be able to see the objects, and individually click on them, but unless they have Read Design or Administer permissions, they cannot alter the structure of the object. • User’s ability to interact with the system in this way can be compensated for with appropriate stored procedures.

16. Concurrency: CS8630 : AIA : Vault • In Access, under Tools>Options, set default open mode to Shared. • Under same menu, set record locking to Edited Record. • These are not very strong precautions, for reasons noted under Security. Reasonably savvy user can adjust these. • By establishing only the interface on each user’s machine, their adjustment of the application, if any, will affect only records dealt with by them.