CASE Dictionary V5.1 - Technical Update

CASE Dictionary V5.1 - Technical Update
(CASE and ORACLE7)

1.0 Abstract
CASE Dictionary V5.1 will be released beta shortly after the general availability of ORACLE7. It is primarily designed to support the developer wishing to effectively support the many new ORACLE7 Co-Operative Server facilities including enhanced data integrity, extended security, database held PL/SQL and full distributed access. Additionally V5.1 will aid the design of distributed systems and add a new set of reporting options. Following the V5.1 release a new Forms Generator release will further extend support for the ORACLE7 developer with enhanced management of application components in a client-server environment. This paper concentrates on describing V5.1 functionality but reference to current V5.0 capability and upcoming Generator features will also be made.

2.0 Introduction
The release of ORACLE7 is a significant milestone in the development of the ORACLE DBMS. Developers will now be able to exploit a significantly extended set of options for the construction of systems. The traditional model of previous releases which ostensibly places data management in the kernel and application logic and validation in the programs has been extended to give developers a more generalised approach allowing them to place systems components in the appropriate place based on business and user needs, hardware configurations and development decisions. ORACLE7 also extends the capability for the physical placement of system components. The distributed data access and RPC facilities offer greater freedom to capitalize on hardware, software and network resources throughout potentially a worldwide network, all protected by the 2PC mechanism. Like any new breakthrough, the Co-operative Server technology of ORACLE7 offers developers great opportunity but it is not the technology in its own right that delivers effective systems. It is the appropriate, timely and flexible implementation of the technology that will result in quality systems that add to the overall effectiveness of the business that counts. The Oracle CASE strategy has always been to provide the tools, techniques and methods that help organizations to use technology to the best effect and to allow organizations to change the use of technology as the business environment changes and user needs grow. With both current and planned releases of the CASE products, the ORACLE7 developer has the tools to manage, control and document the use of the new features. And through the use of the CASE transformers and generators the ORACLE7 facilities can be driven in a highly productive and flexible fashion allowing for the construction of the right systems exploiting the right new technologies at the right time.

3.0 ORACLE7 support in CASE Dictionary V5.0 Whilst many of the ORACLE7 concepts are new to the DBMS they have been incorporated into CASE over several previous releases. CASE Dictionary has allowed the recording of certain key design concepts which prior to ORACLE7 were only implementable in generated Forms applications or via ORACLE V6 using passive syntax. This means that the current production release of CASE Dictionary V5.0 (first production in Jan 92 and now available on approximately 15 platforms) already offers considerable direct support for the ORACLE7 developer. Before the new CASE Dictionary V5.1 features are reviewed, the existing V5.0 capability will be described. One of the key deliverables from any analysis activity undertaken using the Oracle CASE tools is a set of data models describing the information that is required in the business, the rules the information must obey and the relationships between the information. The Entity-Relationship diagrams, typically prepared via CASE Designer and held in CASE Dictionary, have for a number of years been available to record the business need for various types of data integrity.

This simple ERD expresses a considerable amount of the data integrity that must ultimately be implemented in either the application code or the database. Reading it we understand that "EACH EMPLOYEE MUST BE A MEMBER OF ONE AND ONLY ONE DEPARTMENT". The obvious corollary to this is that provision will have to be made in the system for situations where a DEPARTMENT containing EMPLOYEES is deleted or modified. With the addition of attribute information, the data modelling also gives the opportunity to specify detailed rules that relate to data values that will be stored. The default database design transformer in CASE Dictionary V5.0 reads these definitions and produces a set of table, column and key information inside the repository which can be subsequently fine tuned to meet detailed user requirements or other design considerations. At the design level then CASE Dictionary V5.0 already holds the following concepts

o NOT NULL
o DEFAULT
o UNIQUE
o PRIMARY KEY
o FOREIGN KEY
o CHECK condition
o CASCADE delete and CASCADE update

These definitions represent the detailed business rules which are now actively supported in ORACLE7. (NOTE: CASCADE update is NOT supported in ORACLE7). These business rules are implemented using a combination of SQL DDL and the Forms Generator. Today the CASE Dictionary V5.0 SQL DDL generator supports the creation of SQL DDL to directly implement the following rules in ORACLE7 via the CREATE TABLE command (notably the CONSTRAINT subclause).

o NOT NULL
o UNIQUE
o PRIMARY KEY
o FOREIGN KEY
o CHECK condition

NOTE: The changes in ORACLE7 SQL DDL for compliance with ANSI SQL2 means that SQL created from CASE Dictionary V5.0 must be run with SET COMPATIBILITY V6 enabled. The remaining rules are implemented by programs generated using CASE Generator for SQL*Forms V2.0. Thus from analysis definitions recorded using a combination of the diagrams and more detailed textural descriptions Oracle CASE today provides most of the basic facilities to understand the need for and subsequently to generate the SQL DDL to implement business rules directly within the DBMS. And if the business or user needs change these can be reflected down through the repository and into ORACLE7 using the ALTER TABLE commands generated by CASE Dictionary V5.0 (within the constraints of the capabilities of the current SQL DDL generator and the ALTER TABLE syntax restrictions.)

In order to help the ORACLE7 developer exploit the new distributed capability, CASE Dictionary V5.0 has the concept of a TABLE at a NODE and a MODULE at a NODE. These definitions can be manipulated graphically using the CASE Designer Matrix Diagrammer to provide documentation of the distribution of data and programs around a network. No node specific SQL DDL or Forms generation is currently supported. Using the facilities of CASE Dictionary V5.0, CASE Designer and Generators, the ORACLE7 developer has a rich repository to manage business, design and technical specifications and can generate automatically much of the syntax required to create the desired functionality in both the ORACLE7 kernel and the tools. However there are a number of ORACLE7 concepts and facilities which are not supported in V5.0 so the CASE development team are shortly to release CASE Dictionary V5.1 which further contributes to the effective use of ORACLE7 features as well as introducing several new components to speed and aid the general development process.

4.0 CASE Dictionary V5.1
4.1 Datatype Changes/Additions and Foreign DB Support ORACLE7 introduces several new and changed datatypes for complete conformance with the ANSI SQL2 standard. These are

CHAR 		- a "new" datatype
VARCHAR2 	- new name for V6 CHAR  VARCHAR 		-
		currently the same as VARCHAR2 but reserved for
		future possible compliance with SQL2 VARCHAR. 
		NOTE: Use not recommended. 
LONG 		- extended to 2 GigaBytes 
RAW 		- extended to 2000 bytes 
LONG RAW 	- extended to 2 GigaBytes

CASE Dictionary V5.1 will support these column datatypes and will allow the user to set preferences which control how definitions in CASE Dictionary get mapped to generated SQL DDL.

4.2 ROLLBACK SEGMENT Storage Parameters ORACLE7 allows the definition of an OPTIMAL size for a ROLLBACK SEGMENT. This is the size the rollback segment shrinks back to following possibly a long running complex transaction. The new ROLLBACK SEGMENT syntax will be supported and generated in V5.1.

4.3 Declarative Data Integrity
The declarative data integrity features supported in CASE Dictionary V5.0 will be extended to support the management of ALL ORACLE7 concepts and the generation of native ORACLE7 syntax. The complete list of SQL DDL components generated from design and analysis deliverables within the repository will thus be

o NOT NULL
o DEFAULT
o UNIQUE
o PRIMARY KEY
o FOREIGN KEY
o CHECK condition
o ON CASCADE DELETE
o EXCEPTIONS INTO table
o ENABLE | DISABLE

An example of the more complex SQL DDL that can be generated is

 CREATE TABLE loans (

	account	NUMBER (6,0) NOT NULL, 
	loan_number	NUMBER (6,0) NOT NULL UNIQUE,
	loan_type	VARCHAR2 (8) 
		CONSTRAINT loan_check 	
		CHECK (loan_type in (`PERS','HOME','AUTO')) 
		USING EXCEPTIONS INTO MONTY.EXCEPTIONS, 
	amount 	NUMBER (8,0) NOT NULL, 
	loan_date	DATE DEFAULT SYSDATE, 
		CONSTRAINT loan_pk 
		PRIMARY KEY (account, loan_number), 
		CONSTRAINT borrower FOREIGN KEY (account) 
		REFERENCES customers (account) 
		ON DELETE CASCADE DISABLE);

NOTE: As today the mechanism for actually creating constraints from CASE Dictionary is a set of CREATE TABLE commands for the basic column information followed by a set of ALTER TABLE commands containing the CONSTRAINT clauses. This is done to prevent dependency clashes where a CONSTRAINT references a table that has not yet been created by the script. Therefore generated SQL DDL syntax will not be created exactly as above, it is shown this way for clarity.

Each declarative data integrity constraint held with CASE Dictionary V5.1 will be tagged to indicate whether the business rule should be implemented client-side (in the program), server-side (in the DBMS) or in both. If server side or both is selected then appropriate SQL DDL will be created. Using Forms Generator 4 (next forms generator release) will result in the client-side and both constraints being created in the generated forms programs. CASE Generator for SQL*Forms V2.0 (current forms generator release) cannot differentiate between client and server side constraint so all constraints will go into the generated programs as well as the server side constraint going into the SQL DDL. Forms Generator 4 is expected to follow shortly after the release of CASE Dictionary V5.1.

The main advantages of using CASE Dictionary to manage the definitions that are combined to make the SQL DDL are

o Map to analysis deliverables.
The developer can be sure that detailed design information accurately reflects the real needs of the users and the business because they are mapped back through the repository structure directly to the analysis deliverables.

o Full documentation.
Using a comprehensive set of reports the developer can get detailed information concerning what has been defined, how system components fit together and the consequences of changing a particular component.

o Easily changed.
Using the screens of CASE Dictionary the developer can easily change a definition and then generate the appropriate CREATE TABLE or ALTER TABLE commands to implement the change.

o Automatically created. Many of the components that go into the above SQL DDL are delivered directly and automatically from higher level definitions held as diagrams and diagram details. This improves productivity and decreases the chances of error.

o Management of complexity.
In a large system it is easy to omit a SQL DDL definition component yet unless the database definitions are complete and coherent the desired level of data integrity will not be achieved. By defining the higher level logical requirement for data integrity in CASE and then using the generation facilities the developer can be assured that well designed data integrity rules will be faithfully represented in the resulting database implementation.

o Flexible management of client-server applications. Recording the logical constraints in CASE Dictionary and then specifying where they are to reside makes both the creation and possible subsequent modification of the client-server split much easier. Major reorganization of the client-server approach to be followed is easily achieved using CASE technology.

4.4 Stored Procedures/Functions/Packages/DB Triggers With releases of the database engine prior to ORACLE7, all application logic resided in the application programs. With ORACLE7 PL/SQL routines can be placed in a number of structures inside the database itself. These routines are then invoked directly under program control, effectively giving shared code modules in the database, or invoked indirectly by triggering when particular data values are accessed. The structures capable of holding PL/SQL routines are as follows

STORED PROCEDURES - named PL/SQL routines held within the database and accessed either locally or remotely by Oracle Tools and other programs. Multiple parameters can be passed both to and from a stored procedure.

FUNCTIONS - similar to STORED PROCEDURES but only a single value is returned.

PACKAGES - a means of encapsulating STORED PROCEDURES, FUNCTIONS and VARIABLES into a named structure with a well defined external interface. PACKAGES can contain both PRIVATE and PUBLIC routines, PRIVATE routines are only visible from other code within the PACKAGE.

DATABASE TRIGGERS - PL/SQL routines held again in the database but in this case tightly connected with a TABLE and optionally containing a WHERE clause such that the TRIGGER only applies to certain row values. DATABASE TRIGGERS fire automatically when the TABLE or TABLE subset is accessed either for each row accessed or for each verb executed as well as either BEFORE operations or AFTER operations. Operations causing triggering can be either INSERT, UPDATE or DELETE. CASE Dictionary V5.1 will use an extended MODULE type definition to record STORED PROCEDURES, FUNCTIONS and PACKAGES. This will include the PL/SQL body of the routines as well as the naming and parameter calling mechanisms. The MODULE definitions are directly connected to the FUNCTION definitions recorded during analysis so a clear requirements traceability route for documentation and change control purposes is established. MODULES may be made up of or call other MODULES, this gives a convenient infrastructure for the definition of PACKAGES. As the DATABASE TRIGGER is tied to a table the triggering conditions are recorded against the table associated with a module holding the PL/SQL code.

CASE Dictionary V5.1 will thus provide the infrastructure to both manage and generate (via SQL DDL) these new ORACLE7 PL/SQL based structures. The set of structures managed within CASE Dictionary (e.g. TABLES to MODULES, TABLES to ENTITIES, TABLES to NODES) will greatly assist in the management of the potentially large number of independent pieces that are typically combined to make an ORACLE7 system.

4.5 Database Security/Roles
A new and more comprehensive set of techniques exist within ORACLE7 to define groups of extended PRIVILIGES enabling various kinds of access to and operations upon database objects and modules. These groups of PRIVILIGES are named sets called ROLES. A USER may then be given the authority to act within one or more ROLES. V5.1 will support the creation and modification of the new security model as well as the subsequent generation of appropriate SQL DDL to maintain a comprehensive security regime at a user site.

This diagram shows the CASE Dictionary V5.1 structures supported to manage the new security model.

4.6 Distributed Support
CASE Dictionary V5.0 introduced the concept of a TABLE and/or a MODULE at a NODE and a NODE being at a LOCATION. This structure will be extended and will be used as the basis for NODE specific SQL DDL generation. For example all the TABLES for a particular DATABASE at a NODE could be generated into an SQL file and then shipped to that node for execution. The TABLE to DATABASE at NODE and MODULE to DATABASE at NODE (including RPC support of STORED PROCEDURES and FUNCTIONS) will be used to create both SYNONYMS and DBLINKS for easy access to appropriate tables and PL/SQL routines.

Additionally V5.1 will allow the definition of SNAPHOTS and the subsequent SQL DDL generation for the provision of controlled data replication around a network. These features will make the design, creation and management of complex distributed systems easier as much of the syntax will be automatically created from repository definitions. A comprehensive set of screens and reports will be available to give the developer or DBA an in-depth understanding of the requirement for and implementation of the distributed database.

4.7 Other New Features
4.7.1 Table to Entity Retrofit
Many users have requested that a utility be provided to take tables definitions within the repository plus their corresponding FK links and produce an equivalent Entity-Relationship definition. This can be a useful way of taking reverse engineered table definitions from the Online Dictionary through the design stage of CASE through to analysis. The resulting Entity-Relationship definitions can then be placed on an ERD using Copy In, available as a feature of CASE Designer. Care must be taken when using this utility as during a previous design activity many tables will no longer accurately be represented as a single entity but this can still offer useful capability in understanding an older table design. This utility will be available within CASE Dictionary V5.1

4.7.2 New Document Element
In order to provide more comprehensive support of the systems documentation activities a new CASE Dictionary object, a DOCUMENT, will be added with V5.1. This will allow unstructured sections of text to be referenced externally and then associated with any primary element within the application system. A high degree of compliance with DOD2167A is expected with this feature.

4.7.3 New Reports
Again at user request a number of new reports have been added with V5.1. These include

o Entity-Relationship Details By Named Diagram o Menu Module Network
o Application System Version Comparison (showing differences between versions) o Full Module Definition Report (showing all details of a module and its   associated objects including tables, columns, saved preferences etc.) o Module Documentation (showing all module, block and field display details and   help text)
o Application System Metric Report (showing such things as number of elementary   functions, number of entity usages etc.) o Detailed Table Constraint (showing all table constraints)

5.0 Summary
The new features of CASE Dictionary V5.1 are targeted primarily at the ORACLE7 developer. The combination of the extended management and generation of ORACLE7 concepts plus the new user requested functionality should make Oracle CASE the ideal companion to the ORACLE7 developer who needs to rapidly exploit the new co-operative server technology in the DBMS in a well managed way making sure that systems constructed are fully in line with real business needs.

John Bestwick
CASE Development, Oracle Corporation jbestwic_at_uk.oracle.com Received on Thu Apr 01 1993 - 15:57:25 CEST