Technical Specifications for Proposed Master Department System
Draft - Jim Repa - 12/13/2001
(Project tasks updated 12/18/2001)
(Added requirements for stored procs. update_dept_parent and
add_dept_parent - 01/02/2001)
Tables
department -- Contains one row per dept. or node
dept_id number(8) not null
d_code varchar2(15) not null
short_name varchar2(25) not null
long_name varchar2(70) not null
dept_type_id number(4) not null
create_date date
created_by varchar2(8)
modified_date date
modified_by varchar2(8)
Indexes: Unique index on dept_id
Unique index on short_code
Constraints: deptnode_type_id must match a dept_node_type.dept_type_id
more_dept_info -- Additional info for each dept. (expand in future)
dept_id number(8) not null
ao_mit_id varchar2(9)
dept_head_mit_id varchar2(9)
Indexes: Unique index on dept_id
Constraints: dept_id must match a department.dept_id
dept_node_type -- Types (each dept has a type)
dept_type_id number(5) not null
dept_type_desc varchar2(50)
check_object_link varchar2(1) CHECK(check_object_link IN ('Y', 'N'))
department_child -- Defines department parent/child links
parent_id number(12) not null
view_subtype_id number(5) not null
child_id number(12) not null
created_by varchar2(8)
start_date date
end_date date
Indexes: Unique index on parent_id, view_subtype_id, and child_id
Non-unique index on parent_id
Constraints: parent_id must match a department.dept_id
child_id must match a department.dept_id
view_subtype_id must match a view_subtype.view_subtype_id
Additional constraints to be enforced by stored procedures:
- A parent_child link must not create a circular
loop (i.e., a department cannot be its own parent, grand-parent,
great-grand-parent, etc.)
- Every department except the root must have at least one link
to a parent
view_type -- There may be more than one tree "view"
view_type_code varchar2(8) not null
view_type_desc varchar2(50)
root_dept_id number(8)
Indexes: unique index on view_type_code
Constraints: root_dept_id must match a department.dept_id
view_subtype -- A view_type can have 1 or many subtypes
view_subtype_id number(5) not null
view_subtype_desc varchar2(50)
Indexes: unique index on view_subtype_id
view_type_to_subtype -- Maps a view_type to list of parent/child subtypes
view_type_code varchar2(8) not null
view_subtype_id number(5) not null
Index: unique index on view_type_code and view_subtype_id
Constraints: view_type_code must match a view_type.view_type_code
view_subtype_id must match a view_subtype.view_subtype_id
object_type -- External object types, e.g., Prof Ctrs, Funds Ctrs, etc.
object_type_code varchar2(8) not null
obj_type_desc varchar2(50)
obj_type_html_name varchar2(50) -- Name for table columns
min_link_count number(6) -- minimum links to this obj type per dept
-- min_link_count: Applies when check_object_link = 'Y' for dept_type_id
max_link_count number(6) -- maximum links to this obj type per dept
-- max_link_count: Applies when check_object_link = 'Y' for dept_type_id
import_conversion varchar2(1000)
-- import_conversion -- SQL code fragment for converting object code
-- from external table to internal object code fmt
export_conversion varchar2(1000)
-- export_conversion -- SQL code fragment for converting internal
-- object code to format in external table
validation_table varchar2(60)
-- validation_table -- Table or view in which to find object code
validation_field varchar2(50)
-- validation_field -- Field within validation_table to find obj code
validation_mask1 varchar2(50)
validation_mask2 varchar2(50)
validation_mask3 varchar2(50)
validation_mask4 varchar2(50)
-- validation_mask1 -- If validation_mask* is not null, internal ...
-- validation_mask2 -- ... object_code must match one of the ...
-- validation_mask3 -- ... validation masks which are strings ...
-- validation_mask4 -- ... to be used in a SQL "LIKE" clause.
Index: unique index on object_type_code
object_link -- Link between a dept and an external object
dept_id number(8) not null
object_type_code varchar2(8) not null
object_code varchar2(20) not null
Indexes: Non-unique index on dept_id
Non-unique index on object_code
Constraints: dept_id must match a department.dept_id
object_type_code must match an object_type.object_type_code
-- Additional tables for later development --
* In the future, we should design tables to record an audit trail for
changes made to the hierarchy of departments and links to other
objects
Stored procedures
-- First set (high priority) --
FUNCTION can_maintain_dept (ai_for_user, ai_dept_id)
/* Determine whether a user is authorized to maintain department info */
/* More notes: For first pass, always return 'Y'.
Later, we'll interface with the Roles Database */
FUNCTION can_maintain_links (ai_for_user, ai_dept_id)
/* Determine whether a user is authorized to maintain department links
to other objects */
/* More notes: For first pass, always return 'Y'.
Later, we'll interface with the Roles Database */
add_dept (ai_for_user, ai_d_code, ai_short_name, ai_long_name,
ai_dept_type_id, ai_view_subtype_id, ai_parent_id,
ao_dept_id, ao_message);
/* Add a new department or node in the department hierarchy */
/* More notes: If ai_for_user is null, use user instead.
Check can_maintain_dept
Make sure d_code starts with D_ and is <= field length
Make sure short_name is not null and <= field length
Make sure long_name is not null and <= field length
Make sure dept_type_id is found in dept_node_type table
Make sure view_subtype_id is found in view_subtype table
Make sure parent_id is not null and matches an existing dept
For ai_dept_type_id, ai_view_subtype_id, and ai_parent_id,
start with character strings and convert to numbers
If everything was OK, then insert into department table and into
department_child table
Return the new dept_id and a message "Dept nnnnnn successfully added" */
delete_dept (ai_for_user, ai_dept_id, ao_message);
/* Delete a department or node in the department hierarchy */
/* More notes: If ai_for_user is null, use user instead.
Check can_maintain_dept
Make sure this department does not have any child departments
If everything checks out OK, delete from department table
and delete from department_child table where child_id = ai_dept_id */
/* For next 5 stored procedures,
if ai_for_user is null, use user instead
Check can_maintain_dept for ai_for_user
Check for not-null, and length of input paramenters
Accept numeric parameters as character strings and convert to numbers
*/
update_dept_code (ai_for_user, ai_dept_id, ai_short_code, ao_message);
/* Change the d_code of a department or node */
update_dept_name (ai_for_user, ai_dept_id, ai_short_name, ai_long_name,
ao_message);
/* Change the short_name and/or long_name of a department or node */
update_dept_parent (ai_for_user, ai_dept_id, ai_view_subtype_id,
ai_old_parent_id, ai_new_parent_id, ao_message);
/* Move a department or node, i.e., change its parent link.
If this causes the dept_id to have more than one parent in the
table department_child, then each parent/child record must have a
different view_subtype_id. */
add_dept_parent (ai_for_user, ai_dept_id, ai_view_subtype_id, ai_parent_id,
ao_message);
/* Add another parent link for a department or node.
If this causes the dept_id to have more than one parent in the
table department_child, then each parent/child record must have a
different view_subtype_id. */
delete_dept_parent (ai_for_user, ai_dept_id, ai_view_subtype_id,
ai_parent_id, ao_message);
/* Delete one of the parent links for a department or node. (You cannot
delete the link if there is only one link.) */
/* For next 2 stored procedures,
if ai_for_user is null, use user instead
Check can_maintain_links for ai_for_user
Check for not-null, and length of input paramenters
Accept numeric parameters as character strings and convert to numbers
*/
add_link_to_object (ai_for_user, ai_dept_id, ai_object_type, ai_object_code,
ao_message);
/* Add a link between a department and another object type */
delete_link_to_object (ai_for_user, ai_dept_id, ai_object_type,
ai_object_code, ao_message);
/* Delete a link between a department and another object */
-- 2nd set (lower priority) --
Write stored procedures for
* Adding or deleting a view_subtype
* Changing the description for a view_subtype
* Adding or deleting a dept_node_type
* Changing the description for a dept_node_type
* Adding a view_type (with desc, optional root_dept_id, and list of
view_subtype_id's from view_type_to_subtype table)
* Deleting a view_type
* Updating a view_type, e.g., changing description, root_dept_id, or
the list of view_subtype_id's from view_type_to_subtype table
* Adding an object type (with all of its fields)
* Deleting an object type
-- 3rd set (also lower priority) --
* Write triggers for maintaining an audit trail for changes to
department hierarchy and links to other objects
User interface
Build a web-based interface to do the following:
-- Phase 1 (high priority) --
* Allow authorized user to
Add, delete, change name, or move a department. For first phase,
presume that there is only one view_type_code and view_subtype_code,
which is set by default for each parent-child relationship.
* Allow authorized user to
Add or delete links between a department and other objects
* Generate exception reports. If it is time-consuming and
resource-intensive to do exception report on all departments, allow an
option for running it on only one department.
Flag the appropriate department or object link where any of the
following rules are broken:
+ After applying export conversion rules to the object_code, make sure
it is found in the target table
+ If there are one or more non-null validation_masks for the given
object_type_code, make sure the object_code matches at least one of
them
+ For each department and object_type, make sure the number of links
of a given object_type_code is between min_link_count and max_link_count
(This only applies to departments that have no department children)
-- Phase 2 (medium priority) --
* Extend add/delete/move department interface to support multiple view
types and subtypes. We can take a simple approach and demand that the
user making changes can handle the subtleties of this, or we can try to
build a more sophisticated interface that helps the user through these
subtleties.
-- Phase 3 (lower priority) --
* Add an interface for maintaining view_types, view_subtypes, and
and object_types.
Issues to be resolved
-- What, if any, are the rules for links to objects from a node in the
department hierarchy?
-- What additional rules, if any, do we need to apply to make sure that
we do not have multiple department connections to the same object?
Should we have another report that checks for this?
Project tasks
-- (Done) Set up an Oracle username (MDEPT$OWNER)
to own the tables and stored procedures
-- (Done) Set up a different Oracle username to do SELECT statements and
run the stored procedures (MDEPT$USER)
-- (Done) Set up a Unix username and directories to store .SQL scripts for
defining tables and stored procedures
-- (Done 12/18) Create scripts for tables, along with indexes. Grant SELECT
privileges to MDEPT$USER.
-- (Done 12/18) Insert a few test rows into tables, to be used for testing
stored procedures
-- (Done) Write more detailed description of 1st set of stored procedures
(exactly what will you check, what will you insert, delete, or update).
-- Write specification determining who is authorized to do what, along with a
stored function to check it.
-- Write and test first set of stored procedures.
-- Do initial load of departments, department hierarchy, and links from
other objects. (Must decide on initial department number, and use a
sequence for subsequent numbers.)
-- Mock up a static html document for a user interface for
(1) Adding/deleting/moving departments in the hierarchy
(2) Adding/deleting links between a department and other objects
-- Rewrite stored functions can_maintain_dept and can_maintain_links to
use the Roles Database
-- Decide on environment for developing web interface (Perl CGI scripts,
Java Servlets, or PHP). If necessary, set up the development environment
on a test machine.
-- Build web tools for phase 1 of user interface
-- Work with Warehouse team to develop a feed of Department data and links
to the Warehouse.
-- Build web tools for phase 2 of user interface
-- Write specifications, write, and test 2nd set of stored procedures
-- Build web tools for phase 3 of user interface
-- More future work: Design and implement tables for audit trail, triggers
to populate these tables whenever changes are made, and a user
interface tool to look at the audit trail information