UC Berkeley System Map Project

1. UC Berkeley System Map Project Database Presentation Kristine Gual IS 257 May 6, 2004

2. System Map application’s goals UC Berkeley System Map Application goals: • To build a central clearinghouse for IT system information on the Berkeley campus • To build a foundation for an inventory of the lifecycle of central data elements Application users: • IT system team members • Campus IT leadership

3. System Map application’s tasks UC Berkeley System Map • Adding to an inventory of IT system and the data flows between them by submitting systems • Managing the inventory of systems • Approving systems submitted to the • Presenting the systems and data flows in a dynamic visualization • Searching and browsing the inventory of approved systems

4. User needs – submitting a system UC Berkeley System Map • Collection of a manageable amount of data • Name, description, location, managers • Users and functional services • The nature of the system data – System of Record data, restricted data, criticality level • Links to online resources about the system • General information about imports and exports to other systems • Unambiguous responses to the form questions • We collected data elements using a range of choices instead of open fields whenever possible. • We used lookup tables to build our form fields and structure our data.

5. Application’s development cycle UC Berkeley System Map • Oracle 9i database • Used freeware version of Quest’s Toad software as a development environment • Cold Fusion front end • SIMS project team: Amy Todenhagen, Kristine Gual, Carolyn Cracraft, Katherine Ahern, Dave Schlossberg

6. Database tables UC Berkeley System Map • Central table • SYSTEMS Related tables DATAFLOWS DOCUMENTS SYSTEM_FUNC_AREAS SYSTEM_DATA_USERS USERS Lookup tables APPROVAL_STATUS CRITICALITY DATAFLOW_FREQUENCY EXCHANGE_METHODS FUNCTIONAL_AREAS DATA_USERS SECURITY_ROLES

7. Interesting issues UC Berkeley System Map • Creating sequence numbers in Oracle • Setting and maintaining Approval Status

8. Sequence numbers in Oracle UC Berkeley System Map • Unlike many databases, Oracle does not automatically generate sequence numbers for primary key fields! • Solution: created a sequence for each table, with a trigger that writes the auto-increment number CREATE SEQUENCE mytable_s1; CREATE OR REPLACE TRIGGER MYTABLE_T1 BEFORE INSERT ON MYTABLE FOR EACH ROW BEGIN  select MYTABLE_S1.nextval into :new.mytable_ID    from dual;END;

9. Approval status of a system record UC Berkeley System Map • As a system moves through the approval process, its status changes. • Approval_Status_ID field tracks an entity’s status: used for Systems, Documents, Dataflows • A demonstration illustrates this process.

10. Approval status of a system record UC Berkeley System Map • This works for adding a new system to the System Map… but what happens when a user edits a system already in the System Map?? • Overwriting the existing system record isn’t an acceptable solution • The administrator couldn’t compare the original and edited version • The original version of the record should still be live until an edited version is approved. • Creating a new version of the record wouldn’t work unless the original and edited versions could be associated somehow.

11. Solution to approval status problem UC Berkeley System Map • Added fields to the SYSTEMS, DOCUMENTS, DATAFLOWS tables: • ARCHIVE_SET_ID • ARCHIVED_DT • Each new system is assigned a unique ARCHIVE_SET_ID number. • An edit to the system creates a new system record, with the same ARCHIVE_SET_ID number as its parent. • An edited system is reviewed in the context of its parent record. • When an edited system is approved: • Parent’s APPROVAL_STATUS_ID changes from ‘Live’ to ‘Archived.’ • Edited system record changes to ‘Live.’

12. Revised approval status lifecycle UC Berkeley System Map