For all DBMS-based software systems, a good database design is the key to perfonnance. Nevertheless the necessity to support this important task by adequate methods and comfortable tools has been neglected up to now. As a consequence at least one generation of computer progranuners fought the battle for necessary perf0J111anCe which was already lost in the beginning, lost by design. Facing this situation, GEMAP first developed a database design methodology called Inf01111ation Analysis. As database applications become more and more conunon and as they show increasing complexity today, GEMAP started the development of INFODlC, the GEMAP INFOnnation DICtionary, a tool for automatic database design based on Infonnation Analysis. This paper will present to you an overview of this development. The fust section outlines the basic concepts of our methodology, Information Analysis. The second section shows how the interactive components oflNFODlC support the infonnation analyst in entering and maintaining the infonllation structure. The third section covers the topic of consistency checks during all phases of Infonnation Analysis. The fourth section gives you an impression of the resulting table 1U1d index structures for a relational database. The final one contains an outlook on our plans, coming enhancements and futllre work.
Infom1ation Analysis Objecltypes Semantically Irreducible Associationtypes Dependencetypes The Phases ofInfonnation Analysis Deriving Relations and Access Paths INFODIC as an Infonnation Analysis Tool Entering the Relevant InfOimation Entering and Maintaining Objecltypes Entering and Maintaining Associationtypes and Dependencetypes Consistency Checks in INFODIC Consistency for the Infonnation Stl1lcture Consistency Checks in Preparing the Database Design INFODIC as a Design Tool for Relational Databases 1.1 1.2 1.3 1.4 1.5 2. 2.1 2.2 2.3 3 3.1 3.2 4 4.1 4.2 4.3 5 5.1 5.2 (
Generation of Data Stl1lctures Outlook
INFODIC, the GEMAP-LNFOmlation-DICtionary, is a design tool for conceptual database model ling.
Based on a semantic datamodel, the infonnation analysis [7], INFODIC describes the information stlUcture of an application and automatically derives the relational database schema. The principle ofINFODIC is the object role modelling method, which is based on the semantics of the natural language ([6], [5], [8]). 1
The aim of infOimation analysis is to set up the conceptual schema, that means the description of the infonnation structure of an application, which is not subject to change as the application evolves. The stl1lctural concepts, used for this pl1lpose, are objecltypes, associationtypes and de pendencetypes.
1.1 An objecllype is a class of things (real or abstract) which is interesting in the context of the appli cation area. All the different infonnations in a database are infonnations about objects of some type.
For the following, a bill of material problem should serve as an example: Infonnation conceming paliS contained in components (with quantity QTY) and their deposits, as well as infonnation about pal1 suppliers, their address and phonemllnber.
I Using the graphical notation of figure 1 for the results of infonnation analysis, objecllypes are re presented by rectangles. So e.g. "Pal·t" and "COmpallY" are objecllypes. 1.2
Semantically Irreducible Associationtypes An associationtype is a class of inf0l1l1ations about objects of given types. It can be considered as a pallem of a naturallallguage sentence.
To avoid instabilities in the infonnation structure, the associationtypes have to be semantically irre ducible. This means that it must not be possible to decompose all associationtype into one or more others without loss of infonnation.
In our graphical notation the circular constructs represent asSOCtaliontypes. The associationtype "part_suppl" e.g. between the object types "part" and "compmlY" represents that pro1s are supplied by companies. The objecllype "company" plays the role "supplies" in this associationtype, the objecllype "pro"!" that of "supplied_by".
A role is cOlUlected to exactly one objecllype, which means that only one objecllype can playa special role in an associationtype.
Along this connection line you read the lower and upper bounds of the corresponding membership interval ("*" means unlimited). It has to be read from the objecllype (rectangle) to the role. The lower bound tells us, in how mrolY occurrences of the associationtype any occurrence of the cor responding objecttype at least has to be found playing this role. The upper bound tells us the maXlffiUlll, So in the exmnple, a "part" is supplied by exactly one "company", but one "compmlY" may supply any number of "pro1s" (including 0). The lower bound of 0 means that we are interested also in compmlies which do not supply any parts for the moment.
It is important to note, that an associationtype itself may be used as an objecuype in ,mother asso ciationtype, as the example of figure I shows. I-Iere the associationtype "BaM_entry" plays the role "has QTY" in the associationtype "BOM_QTY". 1.3
Dependencetypes Dependencetypes ro'e infonnationtypes of higher order (correlations between infonnations). They look fOlmally like associationtypes.
Computer Aided Databrlse Design. 3 .
They are especially of interest as integrity assertions for databases and as IUles for expel1 systems. INFODIC is able to record dependencetypes, but they are not yet taken into consideration in a data base design. Therefore we don't want to cover these topics here and the exan1ple doesn't contain any dependencetypes. 1.4
The Phases of Information Analysis It proved very useful to distinguish the following phases in modelling reality using infonnation analysis.
First, we ask for all the objecttypes we see immediately in our area of interest, we collect all the infolmation (associationtypes) relevant for our objecttypes, and we document hierarchical relation ship (subtypes) between our objecttypes.
The second step is iterated for every objecttype in the model until no more new objecttypes appear. Here we ask for the naming conventions for the objecttypes. It has to be stated here that a nanletype is an objecttype which is used to reference another objecttype (uniquely or not), that a naming convention is a special case of an associationtype, which associates an objecttype with its name type. In addition, we ask for the attributes (interesting infonnation) of our ohjecttypes and keep record of the objecttype hierarchies. We also fix the physical representation of the objecttypes. The dotted arrows in figure 2 are the graphical notation for the physical representation of object types. 1.5
Deriving Relations and Access Paths In order to derive table structures, we build the connectivity components of those connections in our graphical representation which are labeled with a membership interval of I: I only, if we want to end up with SNF relations, or with a membership interval of 0: I in addition, which leads - with respect to perfonnance - to an opti.n1al design for an RDBMS (Relational DataBase Management System) handling null values correctly and efficiently (e.g. ORACLE).
Every such component results in one table, which contains one colunm for any objecttype inside the component and one column for every role, which leads out of the component. All nonlexical objecttypes have to be replaced here by an unique name (naming conventions).
Every objecttype inside of a cOlU1ectivity component, as mentioned above, is a candidate key. For every candidate key a unique access path should be implemented in order to SUpp0l1 database inte grity. Also performance is enl1anced by such an access path.
(
Additionally we see foreign keys. this means all the roles a candidate key plays in other com ponents. For these foreign keys additional access paths are to be provided. In database systems access paths are usually suppor1ed by indexes.
The result of the infOlmation analysis for the bill of material problem is shown in figure 4: T h e . relation schema. the unique indexes (underlined colunUl names) mrd the foreign key indexes (join colunUls indicated by the an·ows).
Beyond question. information analysis is m1 exact method. but the result is a very expensive one. because there are lots of details to document. Of course. large systems cm1 be maintained with editors. but a special tool. checking the consistency and taking care of the context. would offer great support.
INFODIC is such a tool. It allows the user to view an infonnation stmcture. resp. a meaningful par1 of it, to extend arId modify it while a maximum of consistency is guarar1teed.
INFODIC is fully database supported (for the time being: ORACLE). The architecture of INFODIC consists of two components. an interactive one to record at1dmaintain infonnation in order to keep it consistent. and a component to check the information at1alysis and derive the relational database design, using a precompiler as interface to the INFODIC database.
The interactive part offers masks which a user Cat1 fIll with the relevant infonnation. All masks are built in such a way that the user is guided from introducing fundmnental input up to the completion of the infomation analysis. All the time it is possible to delete or extend. and the input is checked for consistency automatically. All masks are formed with SQL*FORMS V 2.0 ORACLE V 5.1. 2.1
Entering the Relevant Information A basic mask serves for entering ar1(1 showing the basic infonnation relevarll for the application area. First, all elements can be filled in without being fully specified. Elements are objecttypes. associationtypes and dependencetypes. If the user already knows the refined stmcture of his element. he carl write it also in the basic mask. e.g. the declaration if ar1 objecttype is a lexical or a nonlexical one.
A particular mask serves for long descriptions and comments. 2.2
Entering and Maintaining ObjecttJpes The mask in Figure 6 describes the refined stmcture of objecttypes only. Here the user must specify things like the physical representation of a lexical objecttype by a basetype (BOOL. STRING, INT.... ) arId its length.
Declar'ations in cOlUlection with special objecttypes. like set or enumeration types. are to be fixed in [01 additional mask.
All dates concerning names and types are mandatory, other dales are typedependent and they will be checked against consistency laler.
The possibility to delete separate objecttypes, which are not involved in any associationtype, is also patt of the maintenance. INFODIC checks such wishes ,md denies deletion, whenever an objecttype belongs to any associationtype. For these purposes, there exists a mask, too. For the bill of material problem, figure 7 shows the input possibilities of associationtypes resp. dependencetypes.
Beneath the title "roles", the associations and dependences will be defIned. For this purpose, the associationtypes are specifIed with the involved objecttypes, their roles and membership intelvals. lNFODIC guat·atltees that associations, for which the objecttypes palticipated in it have already been inserted, Catl only be renanled but not deleted. FmthelTIlore it is guaratltecd that role natnes and membership intervals, with lower bounds not greater than the upper ones, have to be inserted. As an example for natning conventions, the identification for the nonlexical objecttype "pmt" is given in fIgure 8. INFODIC prepm'es the name for such a naming convention in figure 8: "NAM-CON-2". The user has to frll in the objecttype being identified (parI), the identifying objecttype (part_no) and the concerning membership intervals. 3
Consistency Checks in INFODIC INFODIC grants the consistency of the information by a lot of checks during and after entering the inf0l111ation.
As indicated in Section 2, the rust checks are done during infonnation analysis. Fmther checks can not be done before the inserting is completed. Concluding checks help to prepare the derivation of the relational schema. The two latter ones are mainly implemented with ORACLE-precompiler calls embedded in C-programs. 3.1
Consistency for the Information Structure To guarmltee the consistency of the whole infonnation structure, infollnation analysis can't be closed without checking that the infollnation is complete. correct and withoul contradiction. Among others the following rules are checked:
Representation rules for lexical objecttypes basetypes (BOOL, DATE, STRING, INT) must not be represented any other lexical objecttype must be represented exception: a subtype of an objecttype fitting one of the two forstanding items does not need it's own representation
( Rules for nonlexical objecttypes Rules for hierarchical relations between elements any nonlexical objecttype must have an unique naming convention element A is not allowed to be subtype of itself if element B is subtype of element A, A cannot be subtype of B if element C is subtype of element Band B is subtype of A, C is also subtype of A 3.2
Consistency Checks in Preparing the Database Design Especially the membership intervals allow INFODlC to check some semantic rules, which are fundamental to derive a database free of redundance.
For instance the following checks are implemented reducible associationtypes are recognized and notified as an error the existence of undefined occurrences of an objecttype (only zero lower bounds in all concerning membership intervals) are notified as a waming nonlexical objecttypes not involved in any association are not considered in the database design. 4
INFODIC as a Design Tool for Relational Databases So far, INFODlC has been presented as a very general tool for the fom1al modelling of all kinds of infonnation stmcture. Neither the basic method nor the tool with its logic concerning the recording and the consistency rules does really restrict the user in any case. Therefore it is not astonishing that this method is very suitable for fitting fonnally and generally into the rules of the relational database model ([2], [3], [4]). 4.1
Generating Tables A relational database consists of tables (relations). These are sets of identically structured data records with elementary data fields.
In nem'ly all cases, INFODIC is able to derive an optullaltable structure out of the existulg informa tion stmcture. For DBMS (DataBase Management System I. based on SQL, the tables are buill with the standard SQL-statement CREATE TABLE, e.g.:
CREATE TABLE company (mm1e is_phone_of is_adch'ess_of
CHAR (40) CHAR (20) CHAR (120)
The necessalY datatype adaption, dependant on the target system is INFODIC's job. For other than SQL based database systems, it's not a principal problem to generate table descriptions with allOt her syntax like QUEL, for instal1ce, instead of SQL (for INORES). If required, the implementation will only need a few days.
The reason for the remark, concerning a restricted optimality, lies in the present release of INFODIC, which doesn't take into account the influence of dependences onto the table structures. But as we know from experience, only one percent of the tables created by INFODIC show all insignificrult loss of optimality. Working with such a database, however, is without restrictions always possible.
E.g.: Very seldom and only in very complex database design problems, a data field may exist twice. In any case, the result with INFODIC will be better than the solution of the most expel1s, ruld by far better th1m rulY ad hoc solution, done by feeling without a method.
INFODIC's possibilities are widely spread. On the one hand, the tool supports bill-of-material-like stmctures, as we have seen; not all database design tools are able to solve such problems (e.g. [I], [9]). On the other hand INFODIC even generates an optimal database stmcture, when other methods like the nOlmalization theory must give up. This happens mostly in case of intended redundallcy, e.g. striking the balance is principally always possible, when the relevant data can be accessed, and it is notnecessalY to store it, but storing the balance can lead to a better perf0111lallce. INFODIC does allow to store redundrult information.
Sununarizing, INFODIC applies an optin1al table schema for a relational database nearly completely and automatically. If this is not possible, it supports such a table schema at least very efficiently. 4.2
Generating Indexes INFODIC however not only SUppOl1S the development of an optimal relation schema, it also SUpp0l1S the development of optin1al access paths on a relational database. It is impol1ant here to mention, that the sepal'ation of data stmctures (tables) and access paths (indexes), usually seen in the relational data model, must be done strictly, because it is impor1ant for developing secure application software; but access paths me also absolutely necessary for tuning aspects, in order to allow all efficient ruld therefore also economic use of a database.
INFODIC proposes a set of indexes derived from the existing infolmation structure. For DBMS based on SQL, indexes are built with the standal"d SQL-Statement CREATE INDEX, e.g.: CREATE UNIQUE INDEX compru1y-naJlle ON comprul), (nrune)
CREATE INDEX par1_supplied_by ON par1 (supplied_by) The necessary adaptions to the target system me done by INFODIC. Also, in this case it's no principal problem to generate index definitions with another syntax. When required, the imple mentation will only need a few days.
The proposed index set contains unique indexes as well as all indexes, necessary for reasonable joining different tables. Especially all indexes which can gumantee the consistency of the database belong to this set. These indexes support efficiency, too. Extreme requests, concerning perfonnarlce and/or the arnount of data may require special tuning actions.
( (
Perfonnance aspects could cause the need to changing the sequence of the data fields in a concatenated index. E.g. the index COMPANY_NAME supports all the accesses onto the companies' name. If lists, s011ed via phone numbers, are a main application, it would be better to have an index COMP_PHONE with the field sequence phone and name.
CREATE UNIQUE INDEX comp_phone ON company (phone, name) Moreover, dependent on the target system, it might be useful to generate not all indexes proposed by INFODIC. So relational database systems may have problems with index fields not occupied. The proposed index list however is of essential help for the database administrator and it is very easy to select those indexes, supported by the target system.
Besides, it is not possible to extract all desired accesses out of the information structure. E.g. attri butes serving as database entries often depend on application details. These indexes must be added. This should be no problem, because the user knows the conesponding data fields and he can induce INFODIC to create the index.
It should be mentioned, that the INFODIC index proposal is to be supplemented by different decla rations depending on the target system, e.g. the index block size, the index organization as B-tree resp. hashing and so on. But those declarations are beyond the intended aims of INFODIC at the time.
SUl1Unarizing it can be stated, that the set of indexes proposed by INFODIC is a first but good approach. It contains all indexes to guarantee structural consistency, also those eventually unknown to the user. Therefore INFODIC simplifies the tuning of a database inunensely.
I 4.3
Support of Other Data Models Though the title of section 4 is restricted to relational databases, it should be mentioned that INFODIC doesn't favour relational database systems. Table structures generated by INFODIC can be used as recordtypes for every network or hierarchical database. Especially because INFODIC is able to propose hierarchical structured records, typical for classic database systems as well. These recordtype definitions have to be supplemented by the unique identifiers. This is no problem because the corresponding information is available (as already mentioned, INFODIC can create unique indexes).
Principally, the relations between recordtypes are also completely available in INFODIC. Therefore the transfollnation to set definitions in a network schema can be done without difficulties. In case of a hiermchical database system, those relations directly supported by the database software must be added. But this step requests a detailed knowledge of all appl ications. because the performance is thereby influenced in a high degree. 5
INFODIC as a Programming Tool INFODIC - and moreover the method of infollnation analysis it is based on - does not only support database design. The application facilities are multivarious ones. But the actual version of INFODIC does not yet cover all principal possibilities.
5.1
Generation of Data Sh'uctures Yet INFODIC generates record structures as INCLUDES conesponding to the database records, which the progranuner can copy in COBOL· PASCAL· and PL/I programs. Thus the standard problem of these languages - losing the hierarchical record st11lcture when facing relational data bases· is avoided. 5.2
Outlook In the future, there is a new kind of software development growing up. It should be possible to generate applications nearly automatically, based on the knowledge of the input parameter, the necessary infonl1ation and the general infOlmation stlUcture. Then the programmer only will have to show the system on the map of the infol111ation structure the statting point, the intennediate stations and the tat'get infonnation.
The actual status of INFODIC is a software system able to generate databases, and therefore it sup ports an area of software development, whose economic meaning is often underestimated. But in future INFODIC will hopefully mature to a very general software development tool.
( [1] R. Barker
SQL*Design Dictionary (SDD) Proc. 5th European Oracle Users Group Conference,
Munich, April 1987 [2] E. F. Codd
A Relational Model for Large Shared Data Banks CACM, Vol. 13, No.6,
Junil970 [3] E. F. Codd
FUl1her Nonnalization of the Database Relational Model in R. Rustin (EeL) Data Base Systems Courant Computer Science Symp., VoL6 Prentice Hall, Englewood Cliffs,
N.J., 1972 [4] R. Fagin
MlIltivalued Dependences and a New Normal Fonn for Relational Databases ACM Transactions on Database Systems Vol. 2, N03,
September 1977 [5] E. Falkenberg
Concepts for Modelling Infonnation in G. M. Nijssen (Ed.) Modelling in Database Management Systems,
N0l1h-Holland, 1976 [8] G. M. Nijssen
A Gross Architecture for the Next Generation Data Base Management Systems in G. M. Nijssen (Ed.) Modelling the Data Base Management Systems,
North-Holland, 1976 [9] Quint
TINA Reference Manual Quint Database Systems COlporatioll, July 1985
I ( ( (
I 1
;n _~'*.
EDe}poot..l_
PhO:] 0'11< 111
o.~ P 0. r -t -
1'1 supplll'!> 0.*
Ol!~ I 1 ~--S-i;-r-In-g---[ - - Phone
11* 1 I I I I : I I I .1
I ~dclr·,,1
( !:Jill of Mnlol'llIl
I I ,k QTY
Jon.
string
Phone I I I I I I I I I I I I
I ~
BOM (PART, COMPONENT, QTY)
PART (NAME, PART) _ type OBJ-LEX selection 1 ADORESS OBJ-LEX lexical object BOM ENTnl\vy,---------------- ASS-OAT independent as~s~o~c-. BOM-QTy BooL COMP'''AN=y--------------_ ASS-DAT independent assoc.
OBJ-LEX lexical Object OBJ-NLX nonlexical object COMP ADDI\ ASS-DAT independent assoc-. COMP-PHON"'"E-------------- ASS-DAT independent assoc. DATE:,,-,- _ OBJ-LEX lexical object DEPOT, _ OBJ-LEX lexical object _ INT,,,- _ ODJ-LEX lexical object, _ NAME ODJ-LEX lexical object NAM '''C'''O''N-'l-------------- ASS-NAM naming convent'~i-::o-::n- NAM=CON==2=========================== ASS-NAM naming convention Char Mode: Replace Page 1
I
I N F 0 0 I C
GEMAP Information Dictionary (1.0) ( (
associations/ dependences selection 2 association/dependence BOM_ENTRY
_ role PART object name PART;-------------------- type OBJ-NLX minLmal occurence 0 Char Mode: Replace Page 1
NAM CON 1
MIN-MAX- i":;d-=e-=n""t..,i..,f"i"e'"'r=----------- MIN MAX _ 1 1 NAME 1_ 1_ naming convention non1exical object PART.
_
NAM CON 2 }UN-MAX-i·"'d;:e-:n"'t"'ir:f"'3.~'e"'"r----------- MIN M1IX 1- 1
PART-NO 1- 1automatic naming (yin) _ nlx. object identifier type __________________ type