1 / 12

UC Berkeley System Map Project

The UC Berkeley System Map Project aims to create a centralized hub for IT system information across the Berkeley campus. Key objectives include managing an inventory of IT systems, facilitating data flow visualization, and ensuring user-friendly submission processes. Developed using Oracle 9i and ColdFusion, the project addresses challenges like sequence number generation and maintains an approval status lifecycle for all system records. The project enhances campus IT leadership's ability to oversee system data while ensuring precise and streamlined data management.

terah
Télécharger la présentation

UC Berkeley System Map Project

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related