CA2222583A1 - Method and apparatus for modifying existing relational database schemas to reflect changes made in a corresponding object model - Google Patents

Abstract

An object model comprises one or more semantic objects that represent items about which data is stored in a relational database in a computer system. Each semantic object has one or more components that define the data stored for each item. The object model is mapped into a current relational database schema. As a user makes changes to the model the computer system generates a proposed relational database schema, and the differences between the current relational database schema and the proposed relational database schema are determined. The relational database is modified to reflect changes made in the corresponding object model based upon the differences between the current and proposed relational database schemas.

Description

WO 97/03406 PCTlUS96/08571 MET:ElOD AND APPARATUS FOR MODIFY~G
~;Xl~ G RELAlION~L DATABASE SC~R~.M~S TO REEI,ECT
CHANGES ~ADE IN A CC)RRESPONDING OI~JECT MODEL
Field ofthe Illv~ ion The present ~lvelllioll relates to co. . .~ r systems in general, and in particular to co-~ er :jy~ s that store and r~l.;c~ .rU....~ using a r~1~ti~nA1 d ,~, hAC~. Bach~o~nd ofthe Invention At some point in time, most co...l.~/ler users have the need to store and ldliGve some sort of i.~r.,"..,~1;fu- Typically, this is Acco-n~ chPA using any one of l~ulllGlo~s, co.. - ~~ally ~ il~le d~l~bAce programs. These plUgl~llS allow a user to define the types of ;--rv----~ on to be stored in the dA~AbAC~r as well as provide forms for users who will enter data into the d<~lal)Ace and print reports for people wishing to IGIl;GVG previously stored ~llllaLion.
One of the most popular types of d~bAses is lG~.lt;d to as a ,. I~ion~l datAb~ce In a r~ ionAl d~tAbACP~ data are stored in rows of a two-~im~n~;on~l table.
Each table has one or more co1~lmnc that define the types of data that are stored.
TM~1ition~ it has been dif~icult for novice or unsophi~l;ç~tP(l users to create the rçl~tion~l d~t~h~ce tables (also lGrel.Gd to as a d~t~base schema) in a way thatac~ ur~tely mirrors the user's idea of how the data should be ~I~I~ed and stored.
A new approach for allowing users to create re~afion~l ~Ihl7~b~ce schPm~c is a comp~lter program Galled SALSAlU being developed by Wall Data Incorporated of Seattle, W~q~hin~on This program allows users to create a model of the data to be stored on the ~t~hace. The model concictc of one or more sem~n~iC objects that esGnl a comrl~e item such as a person, an order, a c~ y, or ~lyllling else that , W O 97/03406 PCT~US96/08S71 a user might think of in terms of being a unique entity that will be stored in the ~lAtAbAse Each Se.~ ;C object has one or more attributes that store ide.l~iryillg ;llrol l llAI ;on about the s~ ;e object, as well as object links that define rP~ onehirs between two or more sPn Antic o~3e~ts. C~n~~ ~e user ~as c~ pl~ed the sPmAntie 5 object model, the SALSA colllpulef program anaLlyzes the model and creates a COl~eSpOndlllg r~l~tionAl d~AtAha~e schema that can store data in the co.np~,lP~. The details ofthe SALSA s~ ;c object. modeling and schema ~ene~almg system are set forth in c~n....( l-ly a~ci ner3 copending U.S. patent applicAti~m serial no. 08/145,997, filed October 29, 1993, which is hereby incorporated by r~rel~,nce.
In most real world ~lAI~hAee Al~licAI;one, the types of data 1~Ui1~d to be stored in the ~AIAl~n~e change over time. Tllro",.A1;~n t~n l~ht to be il,~ l when the rel~tionAl schema was created turns out to be of little value to a user. Other more L il~llll.tlion iS left out of the ~ bace and needs to be added. Cull~nlly, there are no GQ~ ci_lly available cQmr~lter programs that will allow a user to easily 1~ modify a rel~tionAl dAlnbAee schema to remove ;nro,A~ n add ll~A1;0n or change r~ tionchirs bel~,n ;llr~ ;on in the ~AIAbAce Ther~ore, to modify a clAtAbAce sch~mA it is often lleCeSSA~Y to define a completely new schema and use a IAncl~l;on plo~ll that will fill in the new relati~nAl ~lAbACe schema with data that have been previously stored in the ~h~i,kAee Typically, the ~ e schema must be ~lpdAted by users who are experts in the field of ~ lAhRCe ~lnd~ This is not only time-co~ , and polf~ y ~ e, but also ~ius~ llg for users who only wish to store data in the ~1AtAbA~e and are not cQ~ ed with the inner wolkings of a bA~e mAnA~omPnt system.
In light ofthe pr~~mC with current ~lAtAba~e technnlo y, there is a need for a co~ e~ system that will allow a user to modify an f~ tin~ r~1_l;n~ AtAhA~e sçh~mA The system should be intuitive and easy to learn while allowing the user to - nl- - ~ comr~ ted schema mo~ific~tionc that previously l~u~ed the aid of a Inb~e mo~l~.ling expert.
SU~ Y of the Invention The present invention is a computer system for modifying an ~ g dAIAhAce schema to ref~ect ~ l.n~Pes made in a col.c~onding object model. The object model incl~ldçs one or more objects, each of which conlains one or more co.~pol-~-nlq that ~1esçribe the objects. The object model is related to a r~l~tionAI ~IAt~bA~e schema that is stored within the mellloly of the comr~ltçr system.
As the user makes moriificAti~n~ to the ob3ect model, the c~ r system ~e.~el~les a proposed sch~mA The proposed schema is cc,ll~ed to a current schema .

W O 97/03406 PCT/~ Y'71 that defines the e~ g ~ h~e Ch~n~es be~weel~ the current and ploposed schema are de~ected and are combined into a list of c~ c5 that must be made for each table in the d~bfice The order in which the tables are ms)~1ified is selc~,led so that tables r~el~ced ~ o~r tables are mo~ified before the :er~ cillg tables.
T~e Comrl~tpr system further det~rmin~s wL~ el a column of a table within the d~t~h~e in~hldes data that is to be moved to another table in the d~t~ e When a column cc..~ g data is to be deleted, a search is pelrulllled for a similar column that is to be added to another table in the ~t~h~e Once a table to receive the data is fouuld, the ~S~ le~ system u~-lales the ~1~t~ha~e by moving the data to the 10 app,ùp-iale table.
BliefDes~ ion ofthe Dl~hl~s The ~Oil~g aspects and many of the ~ advantages of this invention will become more readily appi~;ated as the same beco.-.Ps better undel jLood by r~t;l~.~ce to the following det~iled descriptio~l, when taken in Co~ ;ol. with the 15 ~qCG~ a~ i~ dl~wil~,~;s, wl~
FIGI~RE 1 is a block d;a~ll of a co...~ er system accol.lilg to the present invention that allows a user to modi~r an P~ tin~ r~l~tion5~ t~hsce SCI~m~;
~IGURES 2-8 are diagl~ ;c ~ f.~l~l;nn~ of how a r~lfllion~1 d~ CG
is modified to reflect a change in a col,c~un&l~ object model;
FIGURES 9A-9F are a series of flow charts sLuwillg the steps y - r.,,l~d by the CQ~ r system of the present h~ Liol1 to co~ ue two rPl~tion~l d~ h~e schf;~ in order to update an ~"dsLi"~ re~tion~ h~ee to reflect cl~ ~ges made in a coll~,sponding object model;
FIGI~ES 10A and 10B are flow charts ~huwing the steps pei r5-, ...ed by the 25 col..~ Pr system ofthe present invention to modify an ~ I;..g rP~tioîn~l d~t~h~e;
FIGURE 11A is a diagram showing how G~ g~S to ~l~t~ba~e tables are ordered by the present invention in a way that ~ e ves data i~ glily, FIGUR~ 1 lB is a flow chart showing the steps pel~lmed by the cûnl~ er system of the present invention to modi~r d~Al~a~ tables in an order of least 30 dependency;
r FIGIl~E 11C is a diagram showing r~l~tion~ t~b~e tables defined in a circular re~tinn~hir;
FIGURE 12 is a .~ esel-laLion of how data is mûved from one diagl~ l;c table tû another to r~lese..l a change made to a col-t:sponding object model;
3~ FIGVRE 12A illuskates a many-to-many value type data migration;
FIGUl~E 12B illu~ les a many-to-ûne link type data migration;

CA 02222583 l997-ll-i7 FIG~RE 13 is a flow.,l~L ~howulg the steps ~ ru..l.e(l by the present invention when data within the d~l~b~ce is moved from one table to al-oLl.~,., FIGUR~S 14A-14C are fiow charts ~I,owu~; the steps p~;lrullllcd by the present invention to compute a migration path; and S FIGURES l5A-lSD are flow charts sl,~,wh~g the steps ~,r.. ed by thepresent invention to modify an eYieting rel~tion~l d~t~ba~e to reflect ~ ,es made in a ccll~spolldil ~, object model.
Detailed Descli~Liol1 of the Prer~lled Embodiment ~ o solve the preblcm~ R~Qot~i~ted with the il~ilily of prior art ~t~b~e 0 l~ fig~ y:~L~Ills to allow users to easily modify a r~lsticn~l d~t~h~ce, the present invention is a cQmr~ltPr system that is pro~,~led to update a r~l~ti~n~ h~se schema to reflect ~ ges made in a COll~;5lJOl~ object model.
Turning now to FIGVRE 1, a block diagram of a co~ system for imp~s~ the present invention is shown. The c~ p~ system generally cQ.. ~ e5 a central proces~ing unit (CPU) 70 having internal me~loly 72 and a p~ -e~ storage means such as a disk drive 74. A ~ or or display 76 is co~
to the CPU 70 to aUow the user to see an object model that l'tp.. ~ data to be stored in a rf~ ionAl ~t~hAce that exists within the internal lU~ lOly 72 and eventually on the disk drive 74. The user creates the object model using a kcyl~oa~d 78 and a 20 pointing device such as a mouse 8Q. After the object model has been created, a co...l~v~e~ program causes the CPU to analyze the model and create a c~ ,~ondillg rPl~tion~ t~Ab~ce schema within the lllClllOly 72. As will be desc ;l ed below, AI~,~,P,C made to the object model are analyzed and the f1A~h~ c schema stored in the .nuly is mf--iified to reflect the ~ ,e9 made.
25As is fully set forth in the '997 patent applicA~;on the SALSA co..... ~
program provides a simple means by which users can create r~l~l;o~ lhls~b~ce tables wilLuul having to unde ~Lal~d such re1~ion~ Ah~ce conf~l~ as tables, fl~ c, eclion tables, foreign keys, surrogate keys, etc.
In the SALSA program, sPm~ntiC objects are used to lepl.,s_.ll items for 30 which data is stored in a lfl~;ol~l d~t~baie. Each sem~ntiG object is defined by the lec it co~ u,s. Simple value alllibules lcplescl,l simple char~cterictics of an item such as a pcl:iOll'S name, age, ocalp~tion, etc. Group &ll~ibules colle.;livcly define a char~ctçrictic of the object l~presenlcd by the sPm~ntiC object. An address is an e~ "le of a group au-ll ule because is inrlll~lP,s a street, house number, city and 35 state that collectively defines where a person lives. Object link ~ttributec define rel~tionchips bt;l~.een sPm~ntic objects in the object model.

CA 02222583 l997-ll-27 W O 97/03406 PCT~US96/08S71 In the '997 patent ~rrli~tion, a sPm~ntiC object was defined as inr~ 1in one or more h~ s These ~ çs may be s~nple values, groups or object link es In colllla~l vvith the '997 patent, the ~ es of a s~ ;c ob3ect are now ç~re -dd to ~s cQ~ one~lLs, ~nd a ~Pm~ntic obiect ~ ~mply ~ ed to as an object.
However, for the purpose of the previously filed application and the present specifir,Atioî~ the teIm object shall be ~yllO~lyll~OuS with the term sPm~ntiC object, and the term component shall be syno.,~/.nous wath the term ~Ulibu~e.
~ lthou~ the semAntic object ms)clPling system described in the '997 patent app!ic~A~tion allows a user to create a d~ e schPm~ no means are provided by 10 which a user can modify an ~ g schema to reflect a change made to the co.ie~ol1dillg object model. In the present invention, the Çl~ 8f~5 that a user may make to the object model can be divided into four caleg.-ies. These cnl~o~i~s are:
n~,~s to an object within the model; ~ PS to a CO~ O~ within an object;
~ An~S to a ~r~ Cl~ of a component; or Ç1~n~ÇS to the 1~ ;On~ S bt;lv~eell 15 objects in the model. For each change that occurs in the object model, there must be a c~ )on~ g change made in a table ofthe rçlqtionAl dhl~ha~e TABLE 1 sets forth the ~ ~5 that a user can m~e to an object within the object model and the co l~o~ cl.~l~gf,5 that are made to a tl .~nba~e schema in order to ~ . ' m~nt the model change.
TABI,E 1 Object Model ~h~n~s n~t~h~ce Schema Change Create new object Create new table Delete object Drop table Change object name Change table name As d~,.;l ed in the '997 patent applic~ti~ n each object in the object model is paired with a co.lc;sl.ol dillg r~l~tion~l dal~hace table that exists within the lll~.llOly of the co...~ system. Each table typically has the same name as its collc~oll-ling object. If the user adds an object to the model, a new rel~tiQn~ t~h~e table m~lst 2~ be added to the ~t~b~ce s~h~m~ Similarly, if an object is deleted from the model then its corresponding C1~9bi95G table is deleted from the sçh~m~ Finally, if the user rh~r~es the name of an object then the name of the colles~ol1dil1g rPI~ti~n~ t~h~ce table is ~h~nge~l aecoldingly.

_ CA 02222583 1997-ll-27 W O 97/03406 PCTrUS96/08571 The second type of change that a user may make to a ~ b--ce is to modify the components within an object. TABLE 2 lists the cl~ es that a user may make to a component and the co.lG~ondi~g ~h~ es that are made within a table of the rf-J~tion~l dal~ba~.

Coll~ollellL C~h~t~g~,,5 ~S~tS~b~e .SrllPm~ Ch~g~.s Add single-valued, ~ lc-value or group Add new col~lmn(s) ~ oll_~.L
Delete single-valued, simple-value or Drop col~mn(s) group co~ >ol enl Add multi-valued, simple-value or group Create new table COll~OllGIlL
Delete multi-valued, simple-value or Drop tab~le group con~o~
Add single-valued obje: link col Iponelll Add new colllmn(s), define as foreign key Delete single-valued ol~ nl~ Drop foreign key col~lmn(s) Add multi-valued, cb,e: link ~lllpoll~nl Create new Lller~ecL.on table if many-to-many 1~ tn.~ ; or add new co'-~mn(s) as foreign key Delete multi~-valued, object-link Drop intersection table or foreign key colllpo~e.ll column(s) Add parent-type colll~olle,ll Add new colllmn(s), define as forei~ key Drop parent-type colllpol~nl Delete foreign keys Add/delete subtype-group component No G~ nges FIGURES 2-8 show diagl;~ y the cl-~n~es that can be made to the colll~ollenls within an object and the coll.,sponding ~h~ es that are made to a ~ ba~e srhf-m~ As will be a~plc~ Led, the object model is modified on the display screen of the co~ system and the ~t~ba~e schema G~ ~S are carried out in 10 internal memory ofthe comrl1tsr system.

W O 97/03406 PCT~US96/08571 -7-~iIGlJRE 2 shows the c~ es made to a rPl~tion~l d~t~b~e table when a user adds a single-valued, simple value or group componPnt to an object. Here an object 100 ~ C,S~IS an employee. The object in~ es three simple value com~o~ "r~m~ kLress," snd "~oc_~Sec_N~ ollelLs that uni~iuely 5identi~y an i~ e of the employee object stored in the ~t~h~e are inrliç~ted by a pair of ~te~ C to the le~ of the colll~olle,lL name.
As set forth in the '977 patent app~ tion~ the employee object 100 is c~lt;stl~Led within the ~l~t~b~e as a relational table 105. The name of the table ".~ es the name of the ob3ect 100. The table has three columns: Name, Address 10and Soc_Sec_No. Tbe social se-;ulily number column within the employee table is s~levled as the pli ll~y key ofthe table to ler~,~nce a unique employee. The ~ b~e ~A.~ a record of the col~mn(s) that define the plill~a~y key of the table.
iti~nz~ny~ most ~ ccs keep a record of whel}l~,r a co}umn is a foreign key to another table in the ~ e 15When a user adds a new, single-valued, simple value c~ll~ol~e.ll to an object the co~ ,onding rol~fion~l table 105 must be upd~tecl Here, the user is adding aCo...l~ol.f-.~ }abeled "BhLllday" r~le3~ , an c.lll)loy~;e's b~ dâ~ to the employee object. The c~ ol-- -.l is single-valued because it is ~ ..~ that an ~ e canhave only one I~LIlda~. To store this il~ln~àlion in the ~lAI~hA~e, a column 107 is 20added to the employee table 105. The column 107 has the ssme name as the colllpo~ l added. The column 107 is defined to hold data of the type defined by the bL~d&~ Compoll~ i.e., a date.
As will be apl,re~,iaLed, if a user removes a single-valued cGlllpollent from the object, then the corresponding column will be removed from the table. If the user 25removes a single-valued col"~onent that uniquely identifies an ;~ ce of the object, such as the co"l~onel,~ labeled "Soc_Sec_No," the ~lilll~y key ofthe table will also be l~"lloved. ~ that case, a column of surrogate keys (not shown) is g~,nelaled and added to the table.
The effect of adding a multi-valued colllpon~,lll to an object is shown in 30FIGURE3. An objectllO, repre~nting a st~ldent incllldes three simple value components~ t~d~nt_id," "Address," and "Phone." The Student object 110 is associated with a collesponding r~T~tion~ t~ba~e table 112 having three coh~mn~
co.lesponding to each of the three components within the object. In the ~Y;~..ple shown in FIGURE 3, a user is adding a multi-valued, simple value cc lll~on ~ Iabeled 35 "MajorON" to the Student object 110. The component is shown on the screen as having a min;m~m cardinal~ty of zero, intt~ tin~ that a student may not have declared WO 97/03406 P~llU~,5,/08571 any major and a ,~Y;~ cardinality of N, intlit,s~t-ng that a student may have more than one major.
Multi-valued components are stored in s~a~alG tables within the rt~lstiQn~l ~l~t~bA~e Th~l-Ç~re~ the ~ ition ~fthe ~lti-v~ co~ onelll "MajorO N." to the Student object 110 will cause a table 114 to be created. The table 114 has the same name as the colllpone~ being added. The table has three columns: a first column holds a surrogate plhll~y key, a second column stores the student's major, and a third column stores a foreign key to the student table 112. In particular, the plinl~y key (i.e., those values stored in the column labeled Student_id) of the student table 112 is used as a foreign key in the table 114 thereby providing a I~Y~ for 3eEI~ 2111g the table 114 to ~ each student Ac~O~ sled with a p~li-,u]ar major and a way of se~cl~i~lg the table 112 to dGIe.ll~e each student's major~s).
If a user ~ s a multi-valued, s ~ value co~ ,onelll from an object, the coll~s~onding table 114 is deleted from the t~Z~AbAce~
The ~'hAI~geS that take place in the rel~tinnAI tiA~AbA~e schema when a user adds a single-valued, object link conl~ollenL to an object within the object model are shown in FIGURE 4. Here, the object model intl~ldes an object 120 I.,pl~s-n~ , a~AI~Ae~.r and an object 122 r~~ g a se~,lGt~y. Adding an object link colll~ollc.ll 118 to the ~An~er object 120 defines a r~1~ti~n~hir be~ a ~nae~
and a sccl~)/. The object link CO12~012.,.ll 118 has a ~.. ;ni.~.. cardinality of zero, inrlitAtir~ that a "-AnAga~ need not have a se~ ~y, and a ~ ca~ y of one, il~t~ that a ~AI-Ae,f~l can have, at most, one secl~l~y. Placing the objectlink collll)oll.,.ll 118 in the M~n5~çr object 120 a.~lu~ ;c~l~y causes a co".,s~olldi~lg object link 124 to be g~,.w ~Led and added to the SecreL~uy object 122.
A table 126 I~lGsellls the M~n~er object 120 in the r~ tionAl ~Th(~.b~e. The table in~tudes three cQlllmn~ to stûre a m~nagler~s narne, salary, and address. A
table 128 is used to l~les_.lL the Secr~,t~y object. The table 128 incl~des at least four columns that store the name, address, and s~lary of a particular sccleLafy.itinn~lly, the table 128 inrl~ldes a col--mn, labeled "Wpm," to store the ~lu~ er of words per minute a se.i,t;L~y can type.
The ~d~litir)n ofthe single-valued, object link colll~ol1ellL 118 to the Managerobject 120 causes a columm 130 to be added to the table 126. The colurnn 130 is labeled "Name_l" to dinerenLiaLe it from the column labeled "Name." The column 130 holds a foreign key to the r~l~tion~l table 128. As in-li..~ted above, the 35 foreign key selecte~l is defined to ~ ce the ~lhn~y key (i.e., Narne) for the cc,~l~D~olldillg table.

CA 02222583 l997-ll-27 WO 97/03406 PcT/u~5l~7 _9_ The ~ hion of the column 130 to the $able 126 allows a ~AI~h~c m~n~m~nt system to search the tables in order to dele .~ e which ".~ns~g~.r iS
~soci~ted with a particular sew~,l~y and vice versa. It should be noted that for one-to-one r~l~tiQn~hi~s~ ~e ~reign l~y may ~e added to either table. For c~n~;~t~ y, S the present invention always adds the foreign key to the table CC~ Jo~ g to the sçm~ntiC object in which a user has first placed an object link cGIllpol~ellL, if ~y~ llellic~ or having a required link, or ha-ving an object link in a single-valued group CO~ )OII~.lL.
The d~letinn of the object link COlll~)Ollt;lll 118 from the M~n~r object 120 or the object link component 124 ~om the Se~i-cL~y object 122 causes the foreignkey column 130 to be r~ --oved from the rt~lation~l table 126.
In cc~ l~L to the obJect model modifir~tion shown in FIGI~RE 4, FIGURE S
shows the ~ g.~.l7 made to a r~l~tinn~ t~b~e when a user adds a multi-valued, object link comron~nt to an obiect. Here, an object model in~t~ldes an object 134 that 15 l~.esenLs a book and an object 136 l~ es~ an author. The ~d~lition of a multi-valued, object link coll-pollel-L 138 to the Book object 134 in~lir~t~s that a book may have mllltirle authors and an author may have wntten several books. When the multi-valued object link component 138 is added to the object 134, a co.~ )on-ling multi-valued, object link collll,onc~ 140 is ~ IIy created and placed in the Allthor 20 object 136.
To ~pl~se.ll the Book object, a co--t~olldillg l~laliollal table 144 is created in the d~b~ce~ The table has three colllmnc col.es~)onding to each of the three COnl~)Oll~,.lki within the book object 134. A rPI~tion~l table 146 co.-e,po~ s to the Author object 136. Rec~lse none of the con-~olle..ls within the Author object 136 uniquely. identify a particular author, the table 146 inr~ldes a column of surrogate keys. The surrogate key column is the ~I;lll~y key for the table 146.
The ulr1iti9n of the multi-valued object link co,ll~on~.~l 138 within the object 134 causes an ~llel~eclion table 148 to be created. The inle-~e~.lion table in~ PS two colllmnc holding the plhll~y keys of the table 144 and the table 146.The in~.se.ilion table 148 allows the ~i~t~b~ce m~n~EemPnt system to del~,.n-llle the auth~r(s) for any book in the cl~t~h~ce and to ~el~ e which books a particular author has written.
The d~oletiQn of the multi-valued object link col~onelll 138 from the Book object 134 causes the illlel~c~;~ion table 148 to be deleted from the d~t~h~ce s~hPm7~
FIGIJRE 6 shows how a d~t~h~ce is modified when a user creates a parent/subtype rel~tionship belweell two objects in the object model. For r-7....ple, jf CA 02222~83 1997-11-27 W O 97/03406 PCT~US96tO8571 a user has an object 150 l~r~sç~ an employee and an object 152~ rs~ g a m~n~f~r, the user can inrlif ~te that a ~ "is an" employee by placing a parent type, object linlc C(jl.lpol1GIl~ 154 within the m5tn~er object 152. Parent type object link cGmp~el~ls are in~if ~ted by a "P" s~lLs~ t ~hs lower right hand side of the 5 compollellL name Placing the parent type ob3ect l.nlc comt)onell~ 154 within ~he Mstn~gfr object 152 ~ ol~ tlly causes the creation and insertion of a collGsponding subtype object link component 156 to be p}aced in the Employee object 150 To IG~ ,s_ l~ the Employee object 150 with~n the a relational ~ b~-, a r~ ttion~l table 158 is created having two column~ that store values for both of the COlll1~OI1e.1lS of the Employee object 150 To ~~resel~l the M~ ~r object, a rf,1~tion~l table 160 is created having two cQI~Tmnc that store values for each of the components within the ~n~ger object, as well as a surrogate key column that acts as a pl~&ly key for the table The ~ lition of the parent-type object link 5 COmpOl~n~l54 to the ~n~f r object 152is ~epr~senled in the ~ hA~c by adding a colunnn 162 to the table 160. The column 162 ~o~ a foreign key for the table 158 As inf~if ~ted above, the foreign key sFk~led is pn,fe.~bly the pllll~y key for the CC~1le.,~ d;~ F . ~ e table 158.
The ~ 1etion of a parent type object link co-npo~ l from the ~t~
object 152 causes the column 162 to be l~.lluv~d from the table 160.
In ~d~iition to C~ ~t~ the colll~onel,ls within an object, the user may also make ~ 5 to a conlpollcl.~ p~op~ y As descnbed in the '977 patent appti~ation~
prope.lies define allowable values that can be stored in a column of the dA~Al~A~e TABLE 3 shows the ~ ges that can be made to a propel Ly of COIll~)O~ within an object and the corresponding rl-~ g. ~ that take place in the dA1~bA~e W O 97/03406 PCT~US96/08571 Con~ enL Change C~ sl,ondiil~ Dalal~ase S~hem~ Change Change colu~onellL name C~$e ta~le ~r column nam Change l~lAX;~ cardh~aLLy of Drop column, create new table with c~ ollelll from one to many foreign key and surrogate key Change .. ~ ... cardinality of Drop table and foreign key; add ne~v co. ~ ~~ from many to one colu~mn Change ...~x;..~ .. cardinality of object Creates foreign key in related table if one-link f~ll-~oll~ll from one to many to-many, or creates illlel~e~;Lion table if many-to-many Change IOi~ .. cardinality of Change NULL con~L
CCil~pOl~f lll Change ID status ~I.e., lmifl~lf~nf~e) of Change UNIQUE col~ll~ll COI~
Change value type of simple-value Change data type coll.pon~
Change default value of co---l~on~,.lL Change default value As can be seen from TABLE 3, many of the ç~ es that a user may make to cf~ oll~;lll propel lies involve the simple redefinition of a column or data type stored in a colurnn of a relqtinn-q-l table. However, some conl~onenl G1~n~,~s require the 5 ~d~lition or d~etiQrl of a table and/or colllmn FI~URE 7 shows a d~qt~q-bq~e change when a user redefin-~s a single-valued, simple value c~ one"l as a multi-valued co",~onenl. Here, an object model inr.l~dç,s an object 170 that r~.esell~s a student. The object 170 has four cc"--pol~ lls labeled "Student_id," "Name," "Address," and "MajorO 1"- The 1~ cardinality10 of the Major co",~ollenl is zero, inflir~tir~ that a student may not have declared a major. The ...~x;~ cardinality of the Major co,ll~onellL is one, i~ , that astudent has at most one major. To store data about a student within the f35lt~b~ef'; a re~ on~l table 172 is created within the comr~lt~r memory. The table has at least four columns l~l.,~ each of the co,..~ollellls within the student object.

CA 02222S83 l997-ll-27 Wo 97l03406 PCT/US96/08~71 If a user rh~es the m~imllm cardinality of the Major co.l.pollenl from one to N, thereby indiç~tin~ that a student may have more than one major, the table 172 is r.?~ e~ In particular, the column labeled "Major" is removed from the table 172 and a neu~ 176 is sreated to store ml~1tirle ent~e~ student's major. The table176 in~l~ldes three colllmn~ one column stores a student's major such as ~F.n~ h,~' "History," "Math," etc., and the second holds a foreign key to the St~dPnt table 172. The last column holds a surrogate key that is a plilll~y key of the table 176.
A change of a In~imllm cardin~lity of the Major con~por.~ l from N to one is ,)'t '~' in the ~flb~ee as the reverse process ofthat shown in FIGIJRE 7.
FIGI~RE 8 shows the d~b~e change that takes place when a user ~ ges the ,.,~x;-~ " cardinality of an object link ~~ onc.-l from one to N. In the object model shown, an object 180 l~rese.lLs a pru~ssor in a ul~ivt;l~;ly. The proressor object has three colllpol ellls labeled "Name," "Age," and ''D~LIllelll'' that ~ ese.ll 15 data stored about a professor. ~ tion~lly, there is an object link co~ onenl labeled "Student Advisee" that links a pr~>ressor to a StUdpnt object (not shown), thereby l~lese~ , the fact that a pr~)ressor is an advisor of a shldpnt The ...;~
cardinality of the object link colllpol~ is zero, in~lic~tin~ that a pr~,f~,ssor may have no student &d~/iSeeS. The ...~ cardinality of ~he Student Advisee object link 20 ~ e is one, il~l;rr~ that a pro~s;-or has, at most, one student advisee.
To ~ se.ll the ~roressor object within the d~t~h~e, a le~ table 182 is created having three col~lmn~ that collt;s~,olld to the three co~ on~ in the proressor object. ~ldition~lly~ the table 182 in~ les a column that holds a foreign key from a COIIG~I1O~ g student table 186.
When the ~ x;.~.. , ca-d-l~lity of the Student Advisee object link ~ e is ~ed from one to N, to in-1ic~te that a ~r~ressor may have mnltirle advisees, theforeign Icey column 184 ofthe rlqlsfion~l table 182 is lGIllu~,d. A column 188 holding a foreign key from the rel~tion~l table 182 is then added to a re1stiQn~l table 186 that stores data about a sturlent In a case where both objects have multi-valued links to each other, an hllGI~e~i~ion table is created having foreign keys to the two object tables.
The last type of object model change that can be made by a user is to change a table con~LI~ll. Although this is not ~Yrli~.itly shown on the object model, the user may change which column of a table is selected as the pl;n~y key. TABLE 4 sets forth the collG~onding çl~ g~s that must be made when a user ch~n~es the pli keys of a tab}e.

CA 02222583 1997-ll-27 W O 97/03406 PCT~US96/08571 Table Consl,~nl Cc,l,e~ondil-g Table Change Change p~ ~y key Change ~ "~ ~ key and update corresponding foreign keys Change ~ y key ~pe (data/surrogate) Change plilll~ ~ key and update co~ ,ondi,l~, foreign keys and/or drop surrogate key Ch~ es to a plull~ key within a table are made by redefinittg a column within a felaliol~al rlAIAbAce table as well as the co~ ,ondji g foreign key co1~-mnc Of any related tables.
To detr~ e how a dAlAhA~e schema within the co~ llenloly or stored on a Pe~ I stora~e media should be updated, it is neC~SA~ to co.l~ e a proposed rPl~tionAAt ~ Ace schema cc,lre~ond.ng to the object model, as modifiedby a user, with a previously stored d~ ;ol- of the current ~1A~AhAce schPmA The dil~ences bel~n the two schema are d~l ...;ned and the e~;~ B rPl~til>
10 d~l~b~e is mortified accolJ,Il~.
To store a l~l~;S'' I~AI;OI~ of the proposed and current 5~h. -..AC, the presentinvention creates a data structure that is stored within the intern~ l~,e.~lol~ of the CO...~ as well as on the co...~ e~ I. ~11 storage means. The data structureinrt~ldes a dPfinitiQn of each table in the r~ ACe as well as d~ ;l;Qnc of each of the 5 cQ1~lmnc ~nthin the table. The data structure for the proposed schema is cc~ d as the object model is being analyzed to create the rPI~t;QnA1 d~ qbACe SÇhPm~A
In the ~l~rel-ed embodirnent of the invention, the data structure used to store a ~ s~ nl~l;on of the current schema is made up of i~ es of the following C~t classes. However, those sldlled in the art of CC~ lle~ P10glA-~....;.~B will reco~-:~e 20 that other data structures could be used to store the schema dPfinition. ~ itionA1ly, other prog-A-~ g~.APP,S could be used. It should be noted that the class de~ ;ol~ provided below only include the relevant ll~ el v~iabl~s for a class.
The n .e~ nrtion~ of the classes are not ~Yp~ir.it1y shown but are evident from the descript;on of the invention described below and shown in the ~cco~pA~ g 25 flc wcll&~la.
- class Schema {

CA 02222583 1997-11-i7 tableList; // List of all tables CO~ dlllg to each object in object 1/ model };
S The SçhPn~q class inc~des a list of table definitions that co.. ~,~ond to esch of the objects in the object model. Tables created to hold i.~ ,es of multi-valued components and ~IlLG ~ecLion tables are not inc1~de~1 in the list. Each p1~ "~ within the list of tables inc~ les a cc p'.~:e definition for the co~ ondi~g l~t~l:
d~t~ e table.
T~ ces of the following TableDef class are used to store the ~lpfiniti~1l of each table within the d~ bA~ sch~m~
class TableDef {

table_Id; // unique number ~C~i~P~d to the ~1A~ table ~;ol 1mnT i~t 1/ List of column entries in this table (oftype n~t~co~lmn~
// KeyCo1~1mn, GroupColumn, or // ForeignKey) *parentTable; // Pointer to parent table if applicable t5~1~T ;ct; // List of child tables be1o~ g to this table r~1~tinnT ict // List of relations to other tables int1PYT;et 11 List of indices defined for this table uyKeyType; /1 User specifi~ ~tion for TableKeyType p~ ~yKey; // Index defined as p,--,-a,y key lab'~~l&.l.e; // Original name, may be same as object name newTr'~ , // Actual table name used within the ~ ;.h~
tableType; // F.Yp1ir.it table type (i.e. object, multi-valued component t/ or mLerse~,lioll) hDBObject; 11 Handle to co.. ~ onding object in object model ~;
As can be seen, the TableDef class inel~1des ~ r variables that store il~-ll-aLion on the co1~lmn~ within a table, a pointer to a parent table if applicable, any child tables that belong to the table as well as rP1~fionchirs btLweell tables.
35 Fullt.r~...o~e, the pl~lll~y key(s) and key type for the ~able are specifiP~

W O 97/03406 PCT~US96/08571 T~rc~....AI;o~- co~-c~ each column within a r~1-AtiQnAl table are stored as ces of the classes DataColumn, KeyColumn, GroupColumn or ForeignKey. All these classes are related to a common base class, ColumnDef, defined below.
class ColumnDef // Base Glass to store info on each column in ~At~,ba~e {
Col_Id; // Unique number Acsi ne~l to each column in da~l Ace nullAllowed; /1 TRUE-optional, FALSE-required i~tnh1s; // Un~ çness non-unique, unique or ~lhllaly key co1~mnl~ame; /I Column name A~i~ed in clAl~bAce hDBProp; 11 Handle to corresponding object component in the // model colType; // Data Colurnn, Key Column, Group Column or Foreign //Key };
No ;~ IAnC~S ofthis class are actually created. Rather the class is only used tostore the &~ O~ t~r variables of the following derived classes.
class DataColumn: public ColumnDef {
va1ueType; // Data type speAified in the co~ ol~ g object // model dataType; 1/ Data type declared in the ~l t, ba~e logica1r~n~h /I Data length specified in the model when applic&ble dAtAAT.~r~E~h, // Data length declared in the d~bA~e when applicable scale; // Precision length declared ;n the dAtAhAA~e when // applicable };
Tn~ S ofthe DataColumn class store il~c,l}l,~lion on any colurnn that holds data within r - ti--~nA~ ~Ahase table If a column is defined as a llw~ er of a foreign key of a table, il~,lllalion cone~rninE the column is stored as an ;~ .~IAnce of the fo}lowing class.

CA 02222583 l997-ll-27 W O 97/03406 PCT~US96/08571 class KeyColumn: public DataColumn {

*pRefColumn; 11 Pointer to a co,lesl)ollding column in table rt;rt;lcnced // by parent foreign key };
Because most rl~t~b~ee programs do not recogni~e column groups within a rel~tion~l table, i.~l.nalion concerning columns that are logically ol~ d as a group are stored as i~ c~s as of the following class:
class GroupColumn: public ColumnDef cot lmnT iet // List of column malllbc.~ within a group Il (may be of type DataColumn, GroupColumn or Il ForeignKey) };
Finally, ;.. r.. ,.. ~;.,,. collc~ any foreign lcey relations within a table are stored as ~ ces of the following class. Foreign keys are derived from the GroupColumn class bec~se a foreign key of a table may comrri~e more than a single cc ~lmn However lllC.~ of the cc'-lmnT iet for foreign keys are r~ ed to i.... .cl~ces ofthe Keycolu-m--n class.
class ForeignKey: public Groupcotl~mn {

*pRerelellceTable; 1I pointer to the table where the foreign key };
T~ A~ces of the classes described above are created and initi~li7ed as the object model is analyzed to define a proposed d~t~haee sçhP.m~
The ~ e schema stored in the data structure desrrihed above coll~ollds to the object model as modified by the user. The proposed schema is coll~p~c;d to the d~ e schema that desc-il,es the d~AI-~e as it exists in the c~mrl~ter me.llol~. For the purposes of the present ~peçific~tiQn> the previous dçfinition of the clA~Al~ee is ~;r~lled to as the current s~h~m~ The data structure desc~ g the current schema is stored in the co..~ r memory or on the comrlltrr's pelll,allen~ storage means. Af'cer the user comrletes a series of ~h~n~S to the object model, the data structure for the current schema is recalled into memory from the pC~ ent storage and colll~red to3~ the data structure that defines the proposed srhem~

WO 97/03406 PCT~US96/08S71 Turning now to FIGI~RES 9A-9F, the steps pelrull-led by the c~ e~
system of the present invention to co~l~are two ~ ce sc~ are shoum. As in~ ted above, each schema is stored as a data structure that defines the tables in a c~, the coh~mns ~hin each table, and any keys (~il~l~y or foreign) within a 5 table.
Re~ at a step 200, the co~ ulel system recalls the plupos~d schema data s~,uclule and current schema data structure and places these data structures within the internal lllellloly of the comrut~r. At a step 204, the co...~ er begins a loop wLel'e~l each table listed in the tableList for the current schema is co~ d to 10 an ently in the cc,l,~ )ollding t~ble~ ict of the plupGSed sçh~m~ At a step 208, it is de~ ed whether the table in the current schema has an empty list of child tables.
If not, t~he table c~lllpa,;so-~ routine to be descriked is called for each ta~le in the table's list of child tables. This recursive operation takes place until a table is found having an empty ts} ' ~ t At a step 214, the schema C~ PA~ ;~On routine determines whGIller the table in the current schema is found in the proposed sch~m~ If not, the user must have deleted the object or multi-valued conlpolle ll from the object model and the table is deleted from the rel~tit)n~ ha~e at a step 218.
If the table is found in the proposed s~hem~ the cc ...p~. ;cOl- routine begins a loop that analy~s each colu-m--n in the colllmnTi~t defined for a p~lieuldr table at a step 216. At step 220, }t is ~ d wL~,Iller the column is of type GroupCol~lmn If so, the collll,~ison routine at step 216 is called for each column in the cohlmnT i~t that keeps track of the coJI~mns that make up a group. The .e~;ul~ive op~.~lion at step 224 takes place until all coll~mn~ within a group are plOC~ed Turning now to FIGVRE 9B, once a column is found that is not a group, the co...~ Ie~ system dele .nes whether the column is of type n~t~olumn or type KeyColumn (step 228). If a column is defined as either of these two types, ~rvce~ p proceeds to a step 232 wht;f~l it is determined if the column in the current schema is found in the proposed sf~hem~ This is pe.r~,. ,ned by se~clfing the colllmn~ i~t defined 30 in the proposed table for a column having the same Col_Id number. If the column is not found in the pluposed table, the user must have pelf~ ed an object model change that caused the column to be deleted from the table under cons;~l~ration.Before the column is deleted from a table, the computer system must cl~le~ e whether the column includes data to be moved into another table. This process is3~ shown in FIGURE 13 and is described below. After pelr"-2"llg the steps shown in CA 02222~83 1997-11-27 W O 97/03406 PCT~US96/08571 FIGURE 13, the column is ready to be deleted from the current table under conci(leration at step 236.
As will be dPscrihed in further detail below, any ~ ~s to be made to the co--e~olld~g ~ are nc~ made ;~ 7 ~-~t~. R~t~.~r, ~ t~e cl~ Pes t~ be 5 made to the ~l~t~h~ce tables are stored until the current and proposed schema have been fully cclllp~ed. At that time, the ch~n~s to tables in the ~ b~G are made.
If a column is found in both a table in the current schema and a table in the pl(,posed s~ hPm~ the cQrnrllt~Pr system dele.ll.h~es wllelllel the dataType, lO~ lr Pn~h, nulL~lowed, colllmn~ame, d~t~T ~n~h~ and scale pr~c.lies are the 10 same for the column in the current schema as in the l,l~osed schema at step 240. If not, the prop~i.lies ofthe column are updated at a step 244.
At a step 248 ~IGURE 9C), the cQ~ ler system de~ s wll~ ,, the column being analyzed is of type ForeignKey. If so, the CO~ system then dtslf~ c whether the key exists in the table of the proposed schema (step 252). If 15 the key does not exist }n the pl~,posed srhPm~ tlhen the CQ~.~pl~CI system must d~;lc.l.~e wLelL~ the column COI~ c data that are to be moved to another table in the t~t~h~ce using the steps shown in FIGURE 13 as ~ se.~hd by box 254. The key is then marked to be dr~pped from the table in the current ~ b~ce at a step 256.
If the key exists in both the table of the current schema and the propGsed 20 schP~m~, then the co..~ system co~ c.res and ~ e~ the ...~ he~ key col~m that def~ne the foreign key at a step 260 in the same manner as steps 240 and 244 ~le~c~ ed above.
At a step 264, the co.n~ system d~ s ~L~ . all ~ ~' mne in the co1~lmnT i~ for the table under conc;~leration have been analyzed. If not, the C~J~
25 system loops back to 216 (FIGURE 9A) and the next entry in the col-lmnT i~t for the current table is snalyzed.
Once all colllmn~ inr1~lded in a table within the current schema have been analyzed, the coi~.l.vler system deLe,ll~,nes at step 268 (FIGURE 9D) wl-elLel there are any columns that exist in the proposed table that are not found in the current table.
30 If so, the ~ itior ~I col~lmn~ are added to the d~l~ba~e table under con~;dPration at a step 272.
After all the col-lmn~ in a current table have been analyzed, the co...~ le.
system begins a loop at step276 that analyzes each index within the indPYT i~t ~.A;.~ e(l for a particular table. At a step 280, it is d~Le~ ...il-Pd whether an entry in 35 the inrle~rT.ict for the current schema is ~ound and is the same as the corresponding entry for the proposed schPm~ If an index in the current schema is not found in the Wo 97/03406 PCT/US96/08S71 ploposed sr*~mSt it is drup~)ed. If not the same, the entry within the indPYT ict is pAstP,d at step284. At a step288, it is del~ d whether all entries in the inrl~YT ;ct have been analyzed. If not, proceccin~ returns to step 276 and the next entry ~n the }ist of ~dices is &~ ed. After step 288, any ~ g indices in theS proposed table not found in the current table are created.
At a step 292 ~FIGURE 9E), the comrllter system dr~ s wLt;lller the plim~y key of the table in the current schema is the same as the ,~ y key of thetable defined in the proposed s-h~.ms~ If not, the plilll~y key of the table in the current ~lA~hs ce is updated at a step 296. If data for the new pl--~ y key do not 10 qualify, i.e., either not unique or contain null values, an error c~n~itton will be raised.
The K~ system then deL~ mes wl.elller the tableName pr~pel ly of the table in the current schema is the same as the tableName defined in the pluposedschema at step 300. If not, the name of the table in the current t~ bz~ce is ~ ed at a step 304.
At a step 3087 the co.. l~"P~ system determines w~ ;l all entries in the tab~-T ict have been analyzed. If not, the system loops back to step 204 (Figllre 9A) and the next table is analyzed in the method desc ;bed above. Once all the tables have been analyzed, the co~ P' system det~ les at step 312 (FIGU~E 9F) whelher any tables exist in the pluposed schema that are not present in the current s ~hPm~ If so, the ndtlitinn~l tables are added to the current d ~~b~ce at a step 316.
As in~ ted above, rl~An~ç to be made to a rPl~tinn~l table within the current ce are stored in a list of actions that are to be p~lr~ll-,ed. Each action to bepel~lllled as a result of rh~n~es on a colllponelll within an object in the object model is added to a list of colurnn operations, index op~l~Liol1s, or foreign key ope.~Liol~s that are ~ A;-~ed by il~cl~n~es ofthe following class:
class SPTable // Defining table operations CreateO;
DropO;
ChangcN&I-le( oldName );

-Wo 97/03406 PCT/US96/08571 // Defining column operations that add entries to co}umnOpel~l;QI-T i~t AddColumn( pColumn );
DropColumn( currentColumnName );
Ch~ng~Columr~ame~ pNewCols oldColumnName );
ChangeColumnCo~ (p~ol~lmn );
ChangeColumnDataType( pColumn );

// Defining index operations that add enl:ries to indexOper~tisnT i~t ~MTntlP,Y( pIndex );
DropTn-iPy( cull -~ITl-~lPY~ame );
Ch~ pTn~lpycollsL~ l( pIndex );

// Defining key opel~LIiolls that add entries to foreignkeyOper~tionT ict Ch~ T'~ .~yKey( );
AddForeignKey( pForeignKey );
Dropl~oreignKey( currentForeignKeyName );

// Create/Alter table fi-nrtion~ /l FYP,c~ltion Type CreateItO; // CREATE
DropItO; // DROP
AlterO0; // ALTER
AlterlO; // REPLACE_NEWNAME
Alter20; l/ REPLACE_SAVEDATA
lTn~P~O; //
DropIndexO; //
AddForeignKeyO; //
DropForeignKeyO; //

// Base SPTable data lllt;...bel~
oldName; // Last table name defined in d~b~e columnOperationList; // List of alter operation on colllmn~
indexOper~liol List; // List of alter operation on indices foreignkeyOperationList; // List of alter operation on foreign keys F.YP~ltionType; // Current mode of table altering operation CA 02222583 l997-ll-27 W O 97/03406 PCT~US96/08571 ; -21-*pTableDef; // Pointer to a new table APfinition object *pDMTable; // Pointer to temp table dc~ ;o~
~;
As can be seen from the class definitic n, the class SPTable in.~ d~f,s a series of colllmn, index, and key dPfinine methorts E~ch method o~ L~,s to add the ~plu~liale data to the columnOper~AtionTict~ indexOper~tionT ict, and foreignkeyOperAti~ nT ;et respectively. In A.~A.~liticn, the class inrl~Aes several create/alter table methods which allow the cornp~1tr~r system to pc.rwlll dAtAb~ee moflificA,tiol~c, such as create a table or drop a table, as well as to p~-ru-...
10 moAifif AtiOnc that redefine colllmnC within a table. The particular operations ~ ed to ;...~ any of these metho~Ac require a series of SQL s~ .ls that may vary,depel~d~g upon the dAtsbac-e II~A~ system that the cn~ I-f~ system is usingto operate or control the underlying rr~t~tionAl dAl~ ce For f~Y~mp'o, the COI~
may utilize Microsoft Access~', or Borland Pa~adox~), or any number of other 15 eo"....on ~fl~ionAI fiAt~l)Ace plUgl~llS to operate the re~tinnAI f3~hAc~ In the present i,.~.,..L;o4 the user is asked to identify which type of AA~ fiee prf~r~ .";1~, is being used and the CO~ vf selects the a~plop-iale routine(s) to p~. r", ... a desired task acco~ y.
Not all AAI~b~e sy~h.l~ support SQL cf~ An-f1s that manipulate tables in the same way. For ~ ~",~ , some d~ cfv f~.. ~As do not allow the name of a table to be ~ ged once the table has been created. The.~r~re, the present in~Lion mustadjust the particular SQL CQ~ RI~f~C selected to pe rullll an opel~Liol~ based upon the capabilities ofthe ~ b~ce ~ nA~ system that controls the underlying r~ ion~ld~ hACC. The manner in which an operation must be pe~r~ d for any particular 25 dAl~l)ace are listed in a map data structure that is loaded into .-lell-o-y once the comp~lter system has been told which ~ ce a user is using. The following table is an; 1 'e of a map that relates a change to be made to a rfl~l;onAl Ahl~h~Ce table with one of several metht-cls of pe rO-..~g an operation.

CA 02222583 1997-ll-i7 WO 97/03406 -22- PCTtllS96/08571 TABLE S
Type of Operations Execution Type Create new table CREATE
Drop current table DROP
Change table name REPLACE_NEVVNAME
~dd new column ALTER
Add new required column REPLACE_SAVEDATA
Drop current colum~ ALTER
Change column name ALTER
Change column consl.~L REPLACE_SAVEDATA
Change data type ALTER
Add new index ALTER
Drop current index ALTER
Change index con~ L ALTEiR
Add new foreign key REPLACE SAVEDATA
Drop current foreign key REPLACE_SAVEDATA
Change index foreign key REPLACE_SAVEDATA
Migrate data REPLACE_SAVEDATA
TABLE S maps a desired ~ ;c~ table change to one of the five di~r~nl create/alter table msthods defined in the SPTable class. The mpthod~ are labeled:
CREATE, DROP, ALTER, REPLACE NEWNAME, and REPLACE_SAVEDATA.
5 Each method is defined for a particular ~l~t~h~e For ~ le, the REPLACE NEWNAME routine to be used with a ~l~t~b~e created with Microsoft Access~ may be defined ~lifI~rel~Lly than for a ~l~t~b~e created with Borland Parado~.
To change a table within a ~taba~e, the comr~ter system selects one of the 10 table morlifir/ation methods and adds a desired operation to a list of operations to be e~l for a table. For example, to add a column to an ~ P~ ,b~ce table, the W O 97103406 PcT/u~ &~r~7l -~ -23-co..~p.llRr system calls the method SPTable::AddColumn (pColumn). This method adds an entry into the columnOperationT iet telling the co...~ e~ system to add a column IG~elel~ced by the pointer &1~ itiQn~lly the method SPTable::AddColumn (pColumn~ will use TA~L~ 5 to set the FYef,lltiQnType 5 ~ er variable equal to ALTER. The PYeCuti( nType will remain ALTER until another operation updales the EYP~,~ltionType variable with another, higher ranking type. For e~cample, the method SpTable::ch~n~e(~olumncollsL~ (pColumn) sets the FYeel~tion Type to REPLACE_SAVEDAT~. In the present embodiment of the invention, the highest ranlcing PYeCution type is REPLACE_SAVEDATA followed by 10 REPLACE_NEWNAME, ALTER, DROP and CREATE in that order.
The majority of co~ e-c;a] rPl~tionAI ~t~h~e ~I~An~*~ systems provide standa~di~ed SQL ..ieel~A~ia~e for creating and dlu~pillg rRI~tio~ a~a~e tables.ThGIero.e, the CreateItO and DropItO mPtho~ls do not vaty a great deal belwt;en di~e~ ha~e types. E~owever, the mPtho~ that correspond to the ALTER, 15 R~PLACE NEVVNAME and REPLACE_SAVEDATA Ic~uli~les will likely vary lw~ll di~GIGnL f~fll~ ce m~n~ment systems.
The ALTER method is employed for ~ b~ses that support fairly sophi~tir~te~ 1b~ee c~ es The ~ ba~e may support SQL ~A~e~ to add col--mns to ~ tables, rename columns andior tables in the /l~t~b~ce and redefine 20 keys (~ ~-~y and foreign) of a table wiLlloul having to redefine the entire table.
The REPLACE_NEWNAME method is used for ~l~t~h~ee . "~
s that allows a table to be renamed without having to redefine the entire table.FIGI~RE 10A outlines the steps p~lf~,ll"ed by the colllyulcr system when using the REPLACE_NEWNAME method of ~Yecl1tion to completely redefine a 25 table within a rçl~tion~l d~t~b~ce. ~3e~ g at a step 320, a new ~ lAb~Ce table is created with a new name. At a step 324, the data from the old table are copied into the new table. At a step 328, the old table is dropped from the ~ bA~
If the d~AI-~ce m~n~g~m~.nt system does not support using a simple SQL
s~ to compt~:~ a certain operation, then a more elabo~le approach must be 3û used. FIG13RE 10B outlines the steps pc;lrulllled by the l~EPLACE_SAVEDATA
method of eYec~lti~n Be~nnin~ at a step 332, a temporary table having the same ~l~finition as the proposed table is created in the d~l~hace without any key con~l~lLs.
At step 336, the data are moved ~om the ~,Yi~tin~ table to the tempo-~y table. The original table is dropped from the f1~t~k~e at step 340. At a step 344, a new table is 35 created in the ~t~b~e and the data are then copied from the lelllpol~u~r table to the W O 97/~3406 PCT~US96108571 -24-new table at a step 348. Finally, the te~ or~y table is deleted from the ~1A~ Re at step 352.
As can be seen, the map shown in TABLE 5 and desçrihed above dictates which ~Y~ltinn r~uti}~e -- to be used to nu~dify a ~le, depe~ & upon the SQL
r~l.~res that are supported by a particular dA~A~ .A~Ag~ program that controls the underlying r~l~tio~l d~ ee.
In order to plest;lve data illl~;;~iLy within the ~l~t~b~e.s, the order in whichtables are modified is ~nlpo~ . In the presenlly prere,led embodiment of the invention, tables having plilll~y keys that are referenced foreign keys in other tables 10 must be mt-difie~ before the tables that contain the foreign keys. As in~iç~ted above, the o~e-~l;olls ~~ ed to modify each table in the ~ bAce are stored as ;~ n~e6 of the SPTable class. These ;~ A-~ces are placed in a list in the correct order to pl~s_.ve data illle~ily.
FIGURE 11A shows a r~ ;oll of a ChangeTah1~r ;st 320 that c~ c 15 a number of entries COIl~pOl ding to ~ ncf,s of the SPTable class. Each of the entries coll-,~pollds to a Table 382, 384, 386 and 388. The Table 382 COI~ Q a plilll~y key but does not l~;~e.lce any other tables. Table 384 inr1~ldes a foreign key to table 388, and table 388 c~ a foreign key to table 386. In order not to violate data illle~;~y consl,~nls, a table having a prlln~y key rt;~f nced by a foreign key in 20 another table must be modii;ed before the table that CQ~ C the foreign key. This ordering scheme is referred to as least d-lJe-~ 1 order.
In the present illV~ iOll, the i-~ res of the SPTable class are in3_.1ed into a list in a least dep~n~l~nt order. Tables not having relations to any other table can be illselled ~Iy~Lcl'~ in the list, while tables co..~ -g foreign keys must be m~ fie~l 25 after the tables that they ler~:lel~ce. For the example shown in FIGllRE llA,table 386 must be modified before table 388, and table 388 must be mo~ifiP,d before table 384. Table 382 can be m~ fiP,d at any time.
FIGIJRE llB shows the steps taken by the present invention to insert the ees of the SPTable class into a list called ChangeTableList in the least dep~n~nt 30 order. The computer begins at a step 400 that analyzes each ~ nce of the SPTable class~o be insel1ed into the ChangeTableList. At a step 402, the co...l~."~ system begins a loop that analyzes each entry already in the ChangeTableList. At a step 404, the cQmr~lt~r system determines whether the entry already in the list co.~ an active foreign key to the entry to be added. If not, the co~ I.sr system det~rmines 3~ whether all entries already in the list have been analyzed at step 406. If the answer to step 406 is no, the co...p-lle~ system loops back to step 402 and the next entry in the .

CA 02222583 l997-ll-27 W O 97/03406 P~ 'S/08571 ChangeTflb'~T iet is analyzed. If the answer to step 406 is yes, then the new entry is added to the end of the list at step 408.
If the answer to step 404 is yes, ~.e~ g that an entry akeady in the list CG..~ .c an active foreign key to the entry to be added, then the new entry is placed S before the entry CO~.lnii-;.-g the foreign key at a step 410.
At a step 412, the co~ ler then begins a loop that analyzes each entry that exists after the newly-added entry. At a step 414, the comr~ltPr system det~ s whether the newly n~ldeA entry ~..l~ e a foreign key to an entry that is positiQIl~d after the newly added entry in the list. If so, the co..,~ er system then moves the 10 entry rcrel~ced by the new entry to a position before the new entry cQ~-IAi~ the foreign key at a step 416. If the answer to step 414 is no, the co~-p.lle~ system dt;~ es whtiLl~l all le~-A;~ entries in the ChangeTableList have been analyzed at a step 418. If the answer is no, the co~ "er system loops back to step 412. If the end of the list has been reached, the eo~ system then del~,.lni.les WLClllel all 15 i..~ .c~e of the SPTable class have been added to the list at a step 420. If not, the co...l~ system loops back to step 400 and adds the next new entry- to the list.
Once all ;..~ s of the SPTable dass have been added to the ChangeTableList, the ChangeT~l~3et is in~lic~ted as being ~ e at step422.
Mer p~,.rO.~ g the steps shown in FIGURE llB, the tables ofthe ~ ,k~ee are set 20 to be mor~ified in an order that will preserve data illk;~ily.
The only ~ -w~;o~- to the orde~ , scheme desc.il,ed above is when a set of tables have a circular dependency on each other. FIGURE 1 }C illu~ s this type of relation among three tables, Tl, T2 and T3. When a particular rlAt~Aba~e system s~lppolls adding and dl~ppi-lg foreign key definition~ of a ~ ;ol-Al table through 25 simple SQL syntax to drop or add a foreign key without redPfinin~ the table, any circular relationships can be hQnAled by first d.~ hig ~ffiocted foreign key d~finitions before modifying other table definition~ and then le~.~o~ g all needed r~olAtion~ After d,oppillg the foreign key relations, the L,~.ces of SPTable class ~~l~,se~ , theop~ lions to be ~ d on a table can be ins_.led in the ChangeT~b'-T3~t at any30 point. When this option is not available the tables may be modified in any order and any relations that define a circular rPt~tir~ hir are not declared. Affiected tables are defined to have collc~ondhlg key colllmnc for storing data to be entered, but those cohlmn~ are not declared as foreign keys.
Many of the tables being modified in the d~t~ e will contain previously 35 stored data. Theler~l~, there are schema mol1ific~tion~ in which data must be moved W O 97/03406 PCT~US96/08S71 from one table to another in order to wcurately refiect a change that a user has made to the corresponding object model. This is referred to as data migration.
Typically, ;.~ cçs of data migration are in~ ted when a data column is to be dropped from a table. Thele~vle, before ~te~236 shown ~n FIGURE9B, the 5 ~ "~e~ system de~ -;--es ~L~II.er a column to be deleted from a table GOIllaillS
data. When a ForeignKey column is to be dropped from the d~lAb~ce, the co...p."~determines whether the column co~ C data before the column is dropped at step 256.
In order to better understand the way in which the comp~1tPr system moves data from one rçlstion~l table to another, FIGVRE 12 shows a r~p~est;~ t)n of anobject model and the COIl~Spol~di~ l9l;o~ tables that require data migration. The object model in~ Ps three objects 500,510 and 520 that l~es~ an offlce, an employee and a m~na~r r~i,yeclively~ The object 500iS ~ccvri~ed with a rPIstion~l table 530. The object 510 is ~ccofi~ted with a rPI~tion~l table 540 and the object 520 is ~cco~i~tPd with a ~ nAl table 550.
In the e. h }Ic shown, a user has moved a simple value com~3ol~ labeled "Phone" from the object 500 and placed it in the object 510. Similarly, the simple value object labeled "Name" has been moved from the object 520 into the object 510.
Each of the CO.~pOI~ iS~ccOC;9ted with data stored in the col-~ondil1g tables 530 and 550.
To lep.-~s~ the object model change, the data must be moved from the t~vo source tables 530 snd 550 into the dectin~tion table 540. This is ar,co.~.pl;cl.ed by Clea~illg a l~ o.~y table (not shown) that lecei~e~ any e~sting data that may bestored in the destin~tion table (i.e., the data stored in the column labeled "Soc_Sec No" from the table 540), as well as the data stored in the cohlmne labeled "Phone" and "Name" from the source tables 530 and 550, ,espe~l;v~ly. After the data has been moved to the ~ Vl~y table, the cQl~~mnc labeled "Phone" and "NameH of the tables 530 snd 550 can be deleted from the d~ b~ce The original table 540 isdropped, a new table 540' is created and the data from the le.n~vl~y table are copied into the new table 540'. Finally, the tel-lpor~y table is d~leted FIGURE 13 shows some of the steps pel~llllad by the co.~ ul~l system of the present invention to move d~ta from one table to another in an existing d~t~b~se Pe~ g at a step 450, the comyuler system det~nnin~ whether the co}umn to be deleted from a table incl~dçs data. As will be appreci~ted by those slcilled in the art, 3~ this is yelro----ed using an SQI, ~ I that returns the number of rows in a crl 7mn If the column Co~ data, the comr~ter system then se~cll~ s any parent , W O 97/03406 PCT~US96/08571 table of the table co~ .;ng the column to be deleted for a column having the same Col_Id number at step 4~2.
At a step 456, the co...p~ r system determines wl~elht;r a column having the same CQ1--Id n}~b~ as ~e column to be deleted was found. If ~e answer to 5 step 456 is no and the Col_Id was not found, then the co...~ el- system begins a search of object taUes that are related by a foreign key to the table in which the column is to be Jlùp~ed (step 458). At a step 460, it is ~ele~ .ed wL.,lllcr a colurnn having the same Col_Id as the column to be deleted was found in the related tables.
Once a column to be deleted is located in another table in the ~At~ha~e, the 10 c~ p.l~e~ system generates an ;..~IA~ e of one of the following classes DMData or DMForeignData at step 462.
dass DMData SourceTable; ll Where data is coming from DestTable; l/ Table to receive data SourceCol; // Column to provide the data DestCol; // Column to receive the data MigrationType; // One-to-One, One-to-Many, etc.
DataType; // ~alue or Link l!~nl;''"l'A~I'~ // List of Foreign key relations bel~,~,n source and // destin~tion tables ~;
The DMData class is used to store illrulll~Lion about a column cc~ ;n;.~g data that is to be moved from one table to another. The migration path specifies a 25 series of tables relating a destin~tic!n table and a source table. The migration path is a list of data structures for each table and a foreign key relation to a next table in the list. The steps pelrull-led by the cc,..~p~le~ system to compute the migration path are set forth in greater detail below.
When the column to be moved is of type ForeignKey, an ;..~ ce of the 30 DMForeignData class is created.

class DMForeignData: public DMData {
*pDestForeignKey; //pointer to foreign key in proposed table CA 02222s83 l997-ll-27 W O 97/03406 PCTnUS96/08571 *pSourceForeignKey; //pointer to foreign key in current table };
As shown in FIGURE 12 a table may receive data from ' . ~e source tables.
ThelerolG, the following class stores the ~ ces of DMData and 5 DMForeignKeyData that dçscrihe each source table that will provide the data to be migrated into a ~1~stin~tion table.
class DMTable {

D1~n5~t~qT i- t // List of all i~ es of DMData or //DMForeignData *TableDef; // Pointer to ~lel~ ;on of d~stin~tion table to receive // data TempTableName; // Te.ll~ol~uy table to hold data during 3;
Only one ;~ n~ce. of DMTable is created for any destin~tio~l table. Tl~ rc"~
at step 464 ~FIGURE 13), the colll~ulel system del~ es if an ~ ce of this class has been created for the de~stin~tiQn table and, if not, creates an ;..~1 nnce for the table.
At a step 466, the cc,...~ adds the ;~ ~t~..ce of DMData or DMForeignData to the Dl~l~at~Tict for each column of data that is to be moved into the dF~;n~l;o--20 table. If the ~ n table is to include data that is found in the original table, then nl~e of DMData is created for each original column of data that will be copiedinto the new table.
At a step 468, the collll)u~er finds or creates an ;..~ re of the SPTable class defined above for the destin~tion table. As previously indic~tetl, the SPTable class 25 defines what operations are to be p ;Iru~ ed on a table. The in~ ce of the SPTable class ;..~ les a pointer to the i ce of DMlable class created for the de ~
table. The c-~.":on type for any table ~-,veivi--g data is always set to REPLACE_SAVEDATA in order to use the ta~ole mortific~tion steps o~ ;l-ed in FIGT~RE 10B and described above.
The following illustrates the basic format of an SQL ~t~tPmPnt that creates a ul~uy table and inserts v~ith the data from three di~elelll tables for the P,Y~mple shown in FIGURE 12.
CREATE TABLE TempTbl (Soc_Sec_No, Int, Name Text(50), Phone Te,Y~t(25));
INSERT INTO TempTbl (Soc_Sec_No, Name, Phone) SELECT ~mployee.Soc_Sec_No, M~na~er.Name, Offlce.Phone ~OM E~ loyee, M~ulagel, Office WH~ M~n~g~r.Soc_Sec_No is foreign key to Employee.Soc_Sec_~o S AND Of fice.Title is foreign Key to l~n~g~r.Title With the present sl~ld~ i SQL, it is not possible to insert multiple sets of data from di~e elll tab1es seq~1~nti~lly. As the above ~ lu~ Les~ all three source cohlmm and their coll~;sl,ollding tables have to be ~re~ifiPd at the same time. The WHERE clause specifies how data rows from di~ tables are related accoldin~ to 10 specific ~ n path of each data set.
TABLE 6 shown below c~ ifies din'ere-lL types of migration paths for ~lope~ly defining table relations for data insertion. This table is for 11~ ~Lill~3, data as values (i.e., not as foreign keys). There are five di~er~ll migration path types of value type data miglalions Vsluo Type Data Migrations One_To_One Data moved without a cardinality change as a result of:
-- moving a co---~one -l into an object related through a single one-to-one ob)ect link ret~tion~hir One_To Many Data moved with cardinality L,cr~ase as a result of:
-- co.~ ollel.l cardinality rh~nged from one to N
-- moving a single-valued con~>ollellL into a multi-valued ~oup CO~ OIl~
Many_To_One Data moved with cardinality reduction as results of:
-- co.l.ponel.L caldi..alily f~h~nged from N to one -- moving a co---~)ollw-L out of a multi-valued group to a CO~ g group or object Many_To_Many Data merged with another set of data as results of:
çh~n~.~g multi-valued co.--~,ollent to single-valued and moving into a multi-valued group Copy Simple data copy from an original to a temp/new table W O 97/03406 PCT~US96/08571 An; ,1~ of a one-to-one value type data migration is shown in Figure 12, where the Name culllpon~,.ll is moved from the MAn~r object 520 to the Employee object 510. These two objects are related by a single-valued object link cr.. l .l .o~
An ~ of a one-t~nany value type data migration is shown in S FIGURE 7 where the cardinality of the Major component is el-A~-g~'-d from one to N.
An ~ le of a many-to-one value type data migr~tion would be the inverse of the cardinality change shown in FIGUR~ 7.
An c~u"~le of a many-to-many value type data migration is shown in FIGURE 12~ Here an object 550 I-,pr~se-lls a st~d~nt The object cc,..l~;l-s two single-valued, simple value CO1111)OnGI1IS labeled "Student_ID" and "Name". In ~d~lition the object550 in~ t~S a multi-valued, simple value C~ n-~1 labeled "Major" and amulti-valuedgroup ~.--l-on~ Iabeled "Ye~rr~t~ "
A table 5521G~)1'VS~ the object 550 in the d~ Aee This table has t~vo child tables 554 and 556. The table 554 co~ ine col~lmn~ that hold a sulroga~e key, a 15 foreign key to the parent table 552, and co1llmne that store data for a year and a gpa.
The table 556 has coll~mn~ that hold a surrogate key, a foreign key to the parent table 552 and a column to store the student's major.
A many-to-many value type data migration occurs if a user ~ ge5 the e~ y of the eo~ o~ labeled "Major" from N to one, and moves it in the 20 multi-valued group YearData. To lGpl'~ G.l~ this change in the dA~ cp~ the column that stores the student's major is moved from the table 556 into its sibling table 554.
A copy type data migration occurs when data is moved from an original version of a ~ A~ n table to a mot1ified version of the ~lestin~ti~-n table. For"le moving data in the Soc_Sec_No column in the table 540 to the table 540' 25 shown irl FIGURE 12 is an eY~mple of a copy type data migration.
Another set of migration types are used to classify moving data that lepl~sv..l a foreign key from one table to another. When a r~1~ti-~n~hir beLw~ll two objects is ~h~ngecl some cl~A"ges may need to be made for the corresponding foreign keys.
Table 7 c~ ifies various types of link type data m-i~liolls.

W O 97/03406 PCT~USs6/08571 T A R~.~,7 Link Type D ata M i~.~t -~ -One_To_OneForeign key moved to l~relellced object table - Many_To_One Foreign key moved from a CG~ oll~,.lL table (i.e. Iink in a multi-valued group) to the ~rel~ced object table UPDATE Foreign key coll~mn(s) clPfinitiQn c~ ged due to çh~r~es in the ccsll~ ulldil~g ,~ y key column~s), i.e., no cl-~ng~s in the relation data REPLACE New foreign key defined due to change of a parent table An P~Y~mrle ofthe one-to-one link type data 11~ ~liOl~iS shown in FIGURE 8 where the foreign key column is moved from the P~ofessor table 182 to the Shldent tablc 186.
An e7~ample of a many-to-one lin~c type data migration is shown in FIGI~RE 12B. Here an object 57û r~r~s~ s a stl~dPnt The object inc1udes three c<~lllponci~lls~ namely, two ~ll~.le-valued ,,~ po~ labeled ".~tlldent_ID" and lû "Name" a~s well as a group cG,-*onellt labeled "Ye~rn~t~ " The group cGIlll~ol~e~ll inr~ es a simple value co~ o~ labeled "Year" and an obiect link com~o~ L
labeled "Tutor." The object link co~ o~ Iinks the .Stu~lent object 550 to an object 552 that l~les~ a tutor of the stlld~nt A many-to-one link type data migration occurs when a user moves the Tutor object link colll~on~,.lL out of the multi-valued group YearData.
Before the mo~lific~tion~ the object model shown in ~IGURE 12B can be ne3~led in the ~ bace by four tables. A table 574 has two col-~mn~ that hold a student's id~llllic~Lion number and the student's name. A table 576 is a child table of the table 574. The table 576 has three coll~mn~ that hold a surrogate key, a foreign 2û key to the table 574 and a year. A table 578 is a child table of the table 576. The table 578 has three colllmn~ that hold a surrogate key, a foreign key to the table 576 and a foreign key to a table 580 that holds data that describes a tutor. By moving the Tutor object link ~,GIllpOl ent out of the multi-valued group Year Data, the co~system deletes the table 578 and adds a column to the table 580 that will hold aforeign key to link the table 580 with the table 574.

CA 02222583 l997-ll-27 W O 97/03406 PCT~US96/08S71 Update and replace link type data migrations occur when a change to a table's plill~ key causes collcspollding c~ ,es in those tables that use the p~ y key values as foreign keys.
When a c~lumn to be dropped ~om ~ table is found in another table in the 5 proposed s.~h~m~ a migration data type (value or link) is idf~ntifif d Based on this ;~rv~ nl;on~ the migration path required to compose the SQL ~ f-~.~ that will move the data is cG~p."ed for each DMData and DMForeignData class ;'.!J*~ce in the DMlable class i~ e created for the dçstin~tion table.
FIGI)RES 14A and 14B show the steps pe,ïulllled by the col"pulel system to 10 comp~lte the migration paths for va1ue type data migrations and linlc type data ;olls. Referring to FIGI~RE 14A, the co...~ e~ begins a loop at step 7ûO that analyzes each ;..~n~ of the DMData or DMForeignData having a DataType equal to "value" by first ~'1-9 ~ g the migration path type (i.e. one-to-one, one-to-many, many-15 to-one, many-to-many or copy). The source table is then added as the first entry into the l,~lion path being ~...l.~"ed at step 702.
At a step 704, the comrlltçr d~f--~ -çq if the migration type is one-to-one. If so, the CO...~ f r then finds the related object table in the current schema having a table_Id that ..~ 95 the table_Id of the des~ Alion table in the prûpGsed schem~ at 20 step 7û6. For the one-to-one e~r~mple shown in PIGURE 12, the CQ~ locatesthe r ~ e table 540. Next, the comrut-fr system .~gisl~ the table located in step 706 and its foreign key relation to the source table (step 708). In the f ~ -p1-shown in FIGURE 12, the cc...~ ç~ will register the Soc_Sec_No column in the MAn~g~r table 550 as a foreign key to the E~ loyee table 540 to complete the 25 migration path.
Ethe answer to step 704 was no, the cQmputçr system del~ s at step 710 .,.Lell~. the migr~on type is one-to-many. If so, the cc,...p.~ d~ -s at step 712 if the ~Ie .stin~tir~n table is new (i.e. not found in the current schema). If so, there is no filrther migration path to be ~l~fined If the destin~tion table is not new, 30 the co~p~le~ system se~ches the current schema for a child table having a table_Id that ~ lel~es the table_Id of the destin~ti( n table at step 714. The co,,l~,uLcr then adds the child table and its foreign key relationship to the source table at step 716 to the migration path. In the one-to-many ~ -n~ lG shown in FIGURE 7, the migrationpath lists the Student table 172 as the source, the Major table 176 as the d~ ;on 3~ and the fact that the Student Id column of the Major table is a foreign lcey tû the ~ShldPnt table 172.
-CA 02222583 1997-ll-27 W O 97/03406 PcT/u~t~J~ys7 If the migration type was not one-to-one or one-to-many, the comrvt~r d~,t~ les wllell-cf the type is many-to-one at step 720. If so, the parent table to the source table is added to the migration path as well as the foreign ~ey relation b~3lween the source table and the parent table at step 722. Next, the co...l,uLer de~ ss S v~ t;Lhel the table_Id of the parent table ~ es the table_Id of the dçstinAtir~n table (step 724). If so the migration path is compete. If not, the cr mr1~ter ~ s if the patent table lepl~e.lLs an object in the object model at step 726. If not, the conl~ er return to step 722 and adds the parent table of the parent table to themigration path as well as the foreign key illro,ll,alion. As wi~ be a~piec;aLed by those skilled in the art, step 722 adds each table b~ ee.~ the source table and the table g the object in the object model to the migration path. If the answer to step 726 is yes, the co.~ r proceeds as in the one-to-one rnigration type ~ i1led above at steps 706 and 708 (as r ,ple~e,lled by box 728).
If the answer to step 720 was no, the GO~ lct~ 5 if the migr~tion type is many-to-many. If so, the cQmr~1t~or adds the parent table of the source table and the foreign key relation to the migration path at step 742. At step 744, thecQ~Illlu~er d~ e~ wLcll~r the de~ o~ table is new (i.e. not defined in the current schema). If so, the l~aliOII path is ~ If the d~ ;on table is not new, the com~ul., se~ es the current schema for a child table having a table_Id that I)~ eS the df~ table_Id at step 746. F~nally, the COI.11~"~ adds the de l;l.~l;o~ table and its foreign key relation to the parent table at step 748.If the answer to step 740 was no, the ~llpl1~e~ knows the migration is a copy type, and there is no migration path to be comrleted der to allow the user to make C~ eS in the e~ object ~P~ rls~ s at the same time. An e~ r~ can be seen in FIGI~RE 12 by d~o1etir~ the Offlce object 500and the ~ Agel object 520 aflcer moving the components labeled "phone" and "name". Rec~l1se all the ;~rO....~1;0ll nece~ y to migrate the data is still ~v~i1zbl~ in the current srhçm~ the user could malce all these C~ e~c at once.
In order to support this extension, the rnigration path search would need to be 30 modified as illu~ led in FIGURE 14C. The main di~ ce is the migration path type is not available until the path is defined. The initial search in the proposed schema has to be p.,- ru~ ed without any supplem~nt~1 ih~l.l-~l;QIl, i.e., all proposed tables have to be s~.~ched for a colurnn ha~ing an ID that ~ cl~e,s the source colurnn ID. Once a table is found, a coll~sl,onding table with a .)~ hing table ID needs to be 3~ searched in the current sch~m~ A~er the source and the ~lestin~tion tables in the current schema are ~cc~fi111y k~çnfifieri a migration path needs to be defined to W O 97~03406 PCT~US96/08571 -34-co~ ccl the two tables. If the destin~tir~n table is new, its parent table should be se~-,hed. It is possible that the two are not related at all in the current schema resllhing in no data migration. Starting from the source table, a search should be pGIrul-..ed through it~ child ~ables ~ne-t~many), sibling tables (many-to-many),S parent tables (many-to-one) or related object tables ~one-to-one or many-to-one).
The migration path type can then be found based upon the source car~ alily and the path d~c~iLon as in~ ed above.
FIGURE 14C shows the steps p~lru-l.,ed by the present invention to extend the search for a column that has been moved in a sematic object model. Re~...~;~.p 10 with step 814, a search is pelru,--.cd in the proposed schema for a column having an ID that ...~lc-1~es the ID of the column to be moved. As step 816, the co...l..,t~ ~
system determines wL~;Lllel the table was found. If not, no data is ~ ed. If theanswer to step 816 is yes, the c~ ul~r system dt;L~",~lcs whether the table is newly created in the proposed srh~m~ If so, proces~in~ proceeds to step 18 wl-c.~l the1~ coln~ e~ system del~ s wl.~,lL~,. the parent table ofthe table found in step 816 is new. If so, no data ~ liol~ path is p~ lù....~1 If the parent table in the proposed schema is not new, then the co~.,l.~.(er system def nes the parent table as the table to be located as step 822. The co...l~ ,r system then s~rcLcs the current schema for a table with a .~ 8 ID at step 824.
20 At step 826, the co,~ ule~ system d~ whether the table is found. If not, no data migration is p~.rul l"ed. If the answer to step 826 is yes, n~eAui.~33 that the table was found in the current srh~m~ then the co~pul~-~ system adds the source table to the migration path at step 840. At step 842, the co.~p~,ler system seal.,l,es the source table's list of child tables (if any) for the de~ l;Q~ table. At step 844, the CQ~ ,r 25 system d~ s wl,~L~,l the de,~ n table is found in any of the chi1d tables of the source table. If not, then the Cc!...l~ul~ system dete,ll"n~,s if the source table has a parent table and if so, then sed~l~s any sibling tables. If the source table has no parent, proc~inp pr~ceeds to step 838.
At step 828, the parent table and its foreign key relation to the source talbe are 30 added to the n~aLi~ path. At step 830, the comrl~ter sea~ es the sibling tables through the parent tables' table list At step 832, the co~.~p~ r system d~ es whether the ~ n table is found. If not, the CO'.'1'LI~' system begins a loop that se~cl,~,s fûr the dr~ l;on table through the parent tables of the source table. An index, current table, is initi~ti7ed as the source table and is reset to be each parent 35 table's parent. At step 834, the c.,...l-ul ~- system det~rmin~s whether the parent table ofthe current table has an ID that ~ ct.es the destin~tion table. If nût, the co..~l ~,le~

=
CA 02222S83 l997-ll-27 W O 97/03406 PCT~US96/08571 ;
system d~ ;.... es at step 836 whether the parent table is ~ccoG;~tsd with a sematic object in the ob.~ect model. If not, the parent table and foreign key relation to the current table are added to the migration path at step 837. Proce~ing then returns to step 834.
S If the answer to step 836 is yes, the co.~p"l~ system s~,l.es any related object tables. At step 848, the co~l~p.lle~ system de(~ s ~I.ell,er the de~ n table is found in any of the related object tables. If not, the migration path is cr ~ e If the answèr to step 848 is yes, then the c~ le~ system adds the ~le~ ;ol- table and its foreign key relation to the current table to the migration path.
If the d~ctin~t;on table was found in the current table's list of child tables, in a sibling table, or in any of the parent tables, then the cs...l~ - - system adds the de~ ;on table and its foreign key relation to the current table to the migration path in step 846.
Rcrcllin~g now to FIGI~RE 14B, the steps performed by the cr~mrutPr system to co.lll~,(e the migration path for link type mi~liolls are shown. R.~,~",~ p at a 15 step 770, the comruter begins a loop that analyzes each DMForeignData ~ nce having a DataType nlel~er variable equal to "link." The CQ".l~u~ first del~ es wll~ er the ll~aLion is a one-to-one type ~step 77V. If so, the comr~t~-r adds the source table to the rnigration path at step 774. Next the COI~ t'J sea~ es the current schema for a table having a table Id that is the same as the de~ I;on 20 table Id at step 776. The de~tin~tion table is then added to the ~u~licQ path along with the foreign key relation between the source and d~ table at step 778.
If the migration type is not one-to-one, the computçr then de~ es whether the migration is a many-to-one type at step 782. If so, the c~ el sear~hes the current schema for the table having the same table_Id as the des~ ;ol- table and adds 2~ the de~ AIion table to the migration path at step 784. At step 786, the cGm~uLcl adds the source table and the foreign key relation bclwcel~ the source table and the dr.s1;,-~l;0n tab1e to the migration path. At step 788, the parent table of the source table is added to the migration path along with its foreign key h~~ liol~ The c~ er then ~lcl~ es whether the parent table rcl)rcse~ an object in the ob3ect 30 model at step 790. If so, the migration path is co,--l,l~le If not the col.,l"~lPr returns to step 788 and adds the parent table of the parent table to the migration path along with the foreign key ;~r~J"~ I;on If the migration type was not one-to-one or many-to-one, the comrl1ter dc t~ s if the migration is a replace type at step 792. If so, the computer adds the 35 source table to the migration path at step 794. Next, the COllll~u~cl adds the parent table of the source table as well as the foreign key h~"-,alion to the migration path W O 97/03406 PCTnUS96/08571 and se~ches the current schema for a child table having the same table_Id as thedesfin~tio~ table_Id at step 798. At step 804, the co~ ç~- d~ oe wL~ler the child table was found. If so, the migration path is cc~ ~e If not, the c~
system determi~es if th~ par~t t~le found in step 798 has its own parent table S (step 806). If so, the co..~ proceeds as in the one-to-one link type migration set forth in steps 774, 776 and 778 (as ~ esel~led by box 808). If the table does not ,se,ll an object, the colnput~r returns to step 798.
Finally, if the migration type is not one-to-one, many-to-one or replace, the c~ ."~ knows the migration must be of type update. If so, the ll~ioll path is "~--pl.tl~'J by adding the source table at step 810 and the (~e~ ;on table and its foreign key relation to the source at step 812.
Once the llhgl~iOIl path is c~ p-,led the cQmr~tçr ~pm~les the SQL
s~ that when c~ led by the underlying d~li.b~c ~An~.l..~i~l system will modify the 1~ lAh~c~ to reflect the r.h~ .5 made in the object model.
After hav,ng created and ini~ i7ed ~ ces of the SPTable, DMData and DMForeignData classes, the c~-mputçr system mo~lifi~s the tables of the ~ g ~,1DI;On~ h~ee schema to reflect the ~ ,es made by a user to the co ,~.,Jo~J~
ob~ect model. FIGURES l5A-ISD show the steps p~r~ .ed by the co~p-,lçl system to modify the ~ t~h~e Srhf'tnA Rcfel.lll~, to FIGIJRE l5A, the ~ ,r 20 system begins a loop that analyzes each ;.~ n- e of the DMTable class at step 900.
At a step 902, the co~ l system creates a lelll,uolaly table for each i~ e~ At step904, the co...pl~lcr system moves the data from the source table(s) into theL~ ol~y table. The loop ends at step 906 where the collll,ulel system de~
all the DM~able il..~ ces have been plOC~ cd. If not, proc~cei~ returns to step 900 25 and the next ten-p~l~uy table is created.
Once the te llpol~y tables have been created, the co~ t;l system then begins a series of loops that analyze the ;Il~ ces of the SPTable class. As in~ te'l above, the SPTable class informs the COIIIlJ~ I system of all the ch~nglQc that must be p~..ru....cd on the existing tables in the d~t~ba~e as well as creates new tab}es.
30 Bep;.~ 5 at step 908 the comrl~ter system analyzes each ~ n~e of the SPTable class~n the order the ;..~ nces were placed in the ChangeTableList des~,l;l,ed above and shown in F~GURE llA. Next the comruter system determines whether the ~Yecution type is set to "drop" (step 910). If so the comrl~t~r system calls the drop table fim~tion for the class at step 912 and the table will be l~ oved ~om the 35 ~l~t~b~ee At a step 914, the co.~ P~- system ~el~ es wLeLh~ all i,.~ c of the SPTable class have been analyzed. If not, proce~ returns to step 908.

CA 02222S83 l997-ll-27 W 0 97103406 PCT~US96/08571 Re.~ n;~g at a step 916 ~FIGURE 15B) the co...p~el begins a new loop that analy~s each ;~ ce of the SPTable class. At a step 918, the CQ~ v~ l system reads the indexOper~tionT ict of the class. The co~ then dct~ fillcs wllt;ll.~,r an enJ~y ~- ~he ~st re~ es an ~ndex to be dropped at step 920. If so, the co.~ r 5 system calls the drop index r.-,.~ at step 922. Once the entire indexOperatio1-T ;ct has been analyzed (see step 924), the co~ ule~ system reads the ruleig~lu;yOper~tionT ict at step 928. At step 930, the co...~ P~ system del~ s v~/lleLller the entry the list l'~ CS that a foreign key of a table be d-~,ppe~. If so, the co".~",lPr system caUs the drop foreign key ..~ l)er fimcti-~n at step932. At 10 step 934, the co.~ dt;le~ es whell.er all entries in the rOI~ ~B ~-ayOpcr~.~io~Ti~t have been processed. If not, prc!ccss;r~ returns to step 928. A~er all the entries in the indexOper~tionTict and foreignkeyOper~tionT ict have been procç~sed the ~ r system dat~ r~ l~Ler all the SPTable class ;~ r,S have been processed at step 926. Proc~in~ returns to step 916 until aU SPTable ;~s(A-~ces have 15 been al~&Iy~,~l.
Turning now the F~GI~RE 15C, the comr~ter system then begins another loop to process each il~elA~-ce of the SPTable class. At step 934, the CQ~p~ system dete~ 5 whether the FY~ ;onType of the ~ lfll~;eis equal to Ncreate". If so, the comr~ltpr system calls the create table fim~tion of the class at step 936. At step 938, 20 it is ~e~ r~l if all the ~ ce of the SPTable class have been a~ cd. If not, procçssing reh~rns to step 932.
Following step 938 the co--~ e~ system begins another loop to anslyze each c of the SPTable class (see box940). At step942 the co...l.~e( then dt;~ es whether the T~Y~ ;onType of the ;~ e is "alter". If so the co...l~ el system calls the alter table fimctio~ at step 944. Step 946 del~,.. ;.. ~s when each e of SPTable class has been analyzed.
Tun~ing now to FIGURE 15D, the co~ er system begins a loop at step 950 to again analyze each SPTable i~ e At step 952 the CO-IIPU~I system reads the foreignkeyOper~tionT ict The comrut~r system then begins a loop at step 954 that30 dele~ s if an entry in the foreignkeyOper~tionT iet requires a foreign key to be added to the table (step 9~6). If so, the CO1IIPULGI system calls the add foreign key "~ her function at step 958. At step 960, the comr ~t~r system d~;LG ll~les if all the entries in the fc,lGignhGyOperation list h~ve been analyzed. At step 962, the COI~ UlGI
system dGI .~ .es whether all i,.~l~nces of the SPTable class have been pl.~cesse~. If 35 not, procçceing returns to step 950.

W O 97/03406 PCT~US96/08571 Aflcer the SPTable classes have been a~ ~d as ~iPs ;I,ed above, the co~ system begins a loop at step 964 that agair1~ processes each ;.. ~ of the DMTable dass. At step 966 the c~ system then inserts the data from the t~lnpol~y table created earlier into the destinAtion table. Step 968 d~ .es if each 5 entry in the DMTable class has been processed and proce~ing stops at step 970. Finally all l~,llpOl.uy tables are d.~pped at step 970.
As dP~ ed above at step 904 in FIGllRE 15A the co---~ le~ Opc~a~es to move data from the source tables into the temporary ltable defined by the DMDTable class il~slA~lr~s The following desc~ es in greater detail how this is acco~ d by 10 the present invention.
Before ~,-e.~ the steps of FIGVRES 15A-lSD, each DMData or DMForeignData i~ ~ in the ~t~Ti~t for each DMTable i~ ~ is analyzed to create a SQL insert co.~ that col~cisl ~ of the following four clauses:
lNSERT INTO l~ OI~y table name (destin~ti~n column names) SFT FCT source column names FROM de~ ion table, source table names W~RE join ~ ns Once a lt;lnpGI~y table has been created, a single SQL INSERT cQ-----~ d is P ~ ed to popu}ate the tell-polaly table with data for the new destin~tio~ table.
The INSERT and SFT FCT clauses of the SQL s~ f -~ are simply obta~.ed from each DMData or DMForeignData ~ ce. The Fl~OM and WHERE clauses are defined di ;r~ y de~e~ g on a migration type and a specific migration path defined for each DMData or DMForeignData i~ ce The variations for the din~t;re.~l value ~pes of data migrations type are ~ ~d below.
Vnlue Tvne ~linrations ONE_TO_ONE
All source tables are simply joined with join conditions bclw~ell the source tables and the destin~tion table. The following eYAmple shows how three colllmnslabeled "PK", "Cl" and "C2" are moved from tables labeled "dstTable", "srcTablel"
and "scrTable2" into a temporary table labeled "tempTable". In this example, thecolumn labeled "~1" from the table srcTablel and "FK2" from the table srcTable2 are foreign keys to the PK column in the table dstTable.
INSERT INTO tempTable(l?K, Cl, C2) SELECT dstTable.PK, srcTablel.Cl, srcTable2.C2 CA 02222583 l997-ll-27 W O 97/03406 PCT~US96/08S71 FROM srcTablel, srcTable2, dstTable WHERE srcTablel.F~l=dstTable.PK
AND srcTable2.FK2=dstTable.PK;
ONE_TO_MANY
S In the one-to-many value type data rnigration case, the SQL stAAtPmt~nts uiled to move a column of data ~om the source table to a de~ ;GIl table are more complex. If the comr~1ter has ~e~ -Y1 that the dP~t;nAtion table does not exist in the current SAhPmA, then it is ~eCe~ Y to create a new table having a surrogate key. To do this, two teml)Gl~uy tables are created. The first tt;~ Ul~y table (keyTable in the ~ IPIe below) has one column that holds seq~1PntiA1 values for su~ ~Le keys. To create the first te~ ul~y table, the co...~ t -~c~es an SQL
Ç-.~f~nl similar to the st~tem~nt CREATE TABLE keyTable( PK int );.
Seq11PntiAA~ surrogate key values are then rPpeAAteA1y added to the first ~ ul~y tab1e 15 using the SQL :~ln~
INSERT INTO keyTable( PK ) VALUES (?); where "?" is an integer number.
For each iLe.~liGll, the CQ...p~ system ~ ell~ es a unique integer va1ue (cle ~oted here by the q~1estion mark) and inserts the integer into the first tc, n~ol~y table as a 20 surrogate key. The CO...~ system ~ ~cc.1~es the above INSERT ~ 1 until the ulllbe of entries equals the llullll~er of records in the source table to be ll~Lcd.
Because the current standard SQL syntax does not support se1ecl;~ a row by a row ~lullll~er~ the second lelll~ol~y table (pkTable) and a view _re ~ltili7e~ The second ~elll~G-~uy table is created and copies of the p~ l~y key values ai,socia~ed 25 with non-null entries in the column (Cl in the ~,YAmr1e) to be n~Led from thesource table srcTablel are copied into the second tt;lllpOI~uy table using the following SQL :jlnl~..~,,,,ll CREATE TABLE pkTable( PK ty-pe ); where "tvpe" is the data type of the plilll~y key INSERT INTO pkTable( PK ) SELECT srcTablel.PK
F ROM srcTablel WHERE srcTable.Cl IS NOT NULL;

CA 02222~83 l997-ll-27 WO 97l03406 PCT/US96/0857 A view is then created on the second tell~c ~r table to select a single ~lUllal~key value by finding a .. ;~.i.. -- value of the p""~y key column using a SQL~l~ld~'~l fi-nf.tion MinO. However, those skilled in the art of ~ a~e prog will ~wQ~ e that the standard function Max0 could also be used.
CREATE V W minPKView( PK ) ON SELECT Min~ PK ) PROM pkTable;
Once the view has been complete~l the CQ...~ system creates a tl ird tc~ u table (tempTable) that will be used as the pmrl~te for the mo~ified de~ ;o~- table.
After the third tGn~o,~y table has been declared, the co~ e~ system begins a loop that inserts an entry from the column Cl in the source table that cc~ c the data to be ~ led, the p il~ key of the source table that is used as a foreign key and a ~ u~ale key value that form the plilll~y key of the third te~ )ol~y table. The Iollowing is an ~ rle of the SQL ~ / that com~l ~s these steps. The H?" in 1~ the W~RE clause is ,.~'aced by an integer number as the ?~ e-l1 is eYeç~lte~ The value is then hlc.e-~e~led for the next insert.
INSERT INTO tempTable(Cl, FK, PI~) SEIECT srcTablel.Cl, srcTablel.PK, keyTable.PK
FROM srcTablel, keyTable, minPK~Iew W~RE keyTable.key-?
AND srcTablel.PK=minPK~iew.PK;
After inse.~ each row, a co,-G~ondil.~ row in the "pkTable" is deleted.
This process is ,epç~ed until the pkTable becomes empty. All le ~lp~ tables and views are then dloppe1 from the dfl~h~e 2~ If the destin~tion table already exists in the current d~h~c~ s~hPm~ the SQL
~u"~d move a column Cl from a source table "srcTablel" to the t~ r table "tempTable" simrTifieR to:
lNSERT INTO tempTable( F~, PK, Cl ) SELECT dstTable.FK, dstTable.PK, srcTablel.Cl F~OM dstTable, srcTablel WHERE s~cTablel.PK=dstTable.FK
Af'cer the table "tempTable" has been set up, the entire table is then copied into the d~tin~tion table and the tempora~y table can be d,opped.
MANY_TO_ONE

W O 97/03406 PCT~US96/08S71 Many-to-one value migrations occur as result of a cardinality change in a co ~Il)o~ from multi-valued to single-valued. To reflect this change in the d~ e7 it will often be ~ece~ y to delete data. The question becQ.~s which data value will be copied ~om the source table to the ~ fin~tion table and which data values will be S thrown away. As a default, the present embo~1imsnt of the .ll~elllion always selects the record having a .~ .., p~ ~y key as the record to be saved.
To ~cc~ t. the data migration, a ~lAIAb~e view of the source table's ~ y key is created. ~Because the migration path may include ~n~ c tables, it is n~ y to speci~r all foreign key relations between the source table and the 10 de~ A~ table. The.crcre, the view is constructed to enclose the entire ll~alio path using an SQL ~ having the following form:
CREATE VIEW pkView( FK, n~inPK ) ON SELECT Tl.~;~, ~Gn( srcTable.PK ) Fl~OM Tl,T2, .. , Tn, srcTable WH~RE Tl.PK = T2.FK

AND Tn.PK= srcTable.F~
GROUP BY Tl.F~;
where Tl.. Tn are the tables in the Ill.~lioll path ~Y~ di~ the de~ table.
20After the view has been d~fin~d, the comrlte~ system t,~eculf~s al~
~SERT SQL cu~ n(l to add the sekcled records to the desl ;on table. Using this view, the INSERT co~n..~Antl will be the following:
INSERT INTO tempTable(PK, Cl, C2) SELECT dstTable.PK, srcTablel.Cl, dstTable.c2 ~OM srcTablel, dstTable, pkView WHERE srcTablel.PK = pkView.minPK
AND dstTable.PK = pkView.F~;
After ~Ye~tin~ the above SQL co...,.~ , the "pkView" needs to be ~llo~ed to complete the insert operation.
30 MANY TO_MANY
A many-to-many type data migration is in effect a 1I1G1~1n~ of two sets of data.ThGIGrorG, it is ~-~cçs~y to ~ oci~le records from both the source and de~ .,.l;on table in some way. As a default opel~lion, the present invention uses a simple C~IG~;a11 product per parent ;..~ e as follows:

_ CA 02222583 l997-ll-27 W O 97/03406 PCT~US96/08571 INSERT INTO tempTable(FK, PK, Cl, C2, PK2 ) SFT FCT srcTable.FK, srcTable.PK, srcTable.Cl, dstTable.C2, dstTable.PK
FROM srcT3blç, dstTable S WHERE srcTable.FK=dstTable.FK
The merged table has new records created because of the Cartesian product resulting in dllp~ SIte ~ ~y key values. These d~lp~icS~te values need to be rt~ f,d new unique values.
In order to nccrtmpti~h this task a pltl~ y key from each source table is also 10 copied into a temporary table. The cc,...~ ;on of two (or more) p~ ~y key mn~ IGlllains unique for selectively il~pdz~ting the s,~l.og~e key values of the;oll table. The w~ p~ r first creates the lGIllpo~y table using the s CREATE TABLE pkTable( FK int, PK int, PK2 type );
where "type" is the data type of the second plinl~y key. Next the p1i~ y keys are lS s_.1ed into the 1~1l~-~y table using the sl~
INSERT lNTO pkTable~FK, PK, PK2) SELECT F~, PK, PK2 ~OM tempTable;
Two ~or mo~e d~e.lctLg on the ~lu~ el of t~bles s~so~;qted) views are then created ce4~ y The first view selects minimllm values of the first ~ y key for each foreign key vatue, i.e., per parent record.
CREATE VIEW minPKView( FK, mi~K ) ON sF~r~F~cT FE~, Min( PK ) FROM pkTable GROllP BY FK;
The second view then selects a ~--;~ -- value of the second plilll~y key for each minimllm vatue ofthe first p~ ~y key.
CREATE VIEW minPKView2( minPK, minPK2 ) ON SELECT minPK, Min( PKTable.PK2 ) FROM minPKView, PKTable GROUP BY minPK;
The update is then pe.î~, ---ed by lcpca1edly ~ -e~ g the following two SQL
co~ ..ds with an integer number "?" inc~ ed a~er each ~ecu~ion until the , W O 97/~3406 PCT~US96/08S71 p~ ~y key- table beco---es empty-~ This key value is ~ ed to the IIIA~ IIII of the ~y key value plus one.
~ UPDATE tempTable SET PK = ?
WHERE l~SELECT COUNT(*) ~OM minPKView2 WHERE minPK~lew2.minPK = tempTable.PK
AND minPKView2.minPK2=
tempTable.PK2);
DF~ FTE FROM pkTable WHERE l<(SELECT COUNT(*) F ROM minPKView2 WHERE minPKView2.minPK = pkTable.PK
AND minPKView2.minPK2 = pkTable.PK2);
Link T~Pe Mi~rations When a foreign key is moved to another object table, the SQL insert ~
needs to be defined di~t;le--Lly in order to copy the ~ y key values from the source table using the source foreign key- data as a const~ . This again ~ r1e on 20 the migration path of each DMForçignnAtA ;~slAl~ee~ The following in an P ~ nof how each ofthe link type migrations are hAnrllsd by the present Illvt:nlion.
ONE TO ONE
In the case of a one-to-one ~ Loll, the source table pl.l~ key values are illse.led as new foreign key values. The following SQL ~ shows how the 25 foreign key values found in a column labeled "F~" in a table "srcTable" are moved into a column "PK" in a desfin~tion table "dstTable".
INSERT INTO tempTable( PK, FE~ ) SELECT dstTable.PK, srcTable.PK
FROM dstTable, srcTable WE~RE dstTable PK=srcTable.P~
MANY_TO_ONE
As i~ icA~ed above, many-to-one migrations oflcen require that data in the AAIAbS~e be deleted Thelerol-e, a view is created to reduce mllltirle records into one per other object ~ ce using an SQL s1~ having the following form.

CA 02222583 l997-ll-27 W O 97/~3406 PCT~US96/08571 CREATE VIEW minPKView( dstFK, minPK ) ON SELECT dstFK, Min( srcObjTable.PK 3 FROM srcObiTable, .. , Tn, srcTable WHERE srcObjTable.PK= Tl.FK
S AND ...
AND TN.PK = srcTable.FK
GROUP BY dstFK;
Using the above view, the main INSERT co.. A~ beco~e,s as follows.
INSERT lNTO tempTable( PK, FK ) SELECT dstTable.PK, srcTable.PK
FROM dstTable, srcTable, minPKView WH~RE srcTable.PK = minPKView.minPK
AND dstTable.PK = minPK~Iew.dstFK;
UPDATE
This migration type is utilized when an eYi~ir~g foreign key needs new v~ues be~ se of some chA~ges in the p~ ~y key found in the l~rt; ~,ced table. New key values are simply copied using the e~sL;ng key con;,l~ls using an SQL ~
having the form as set forth below where the FK is the name of the foreign key column and PK is the column holding the plimdly key values in the l~ferenced table.
INSERT ~TO tempTable( FK ) SELECT refTable.PK
FROM srcTable, refTable VVHERE srcTable.FK= refrable.PK;
REPLACE
This migration type is utilized when a foreign key is replaced by a new Cl~r~i.;l;~ll bec~l~ce of a change of a parent table, i.e., a co~ onel~l table is moved to another parent table. New key values are simply copied using the ~ key co~ ls by ~ ~e~ P an SQL s~ -or~ having the form as set forth below where Tl ... TN are the tables found in the migration path computed above.
INSERT lNTO tempTable( F~ ) SELECT refTable.PK
FROM srcTable, Tl, .. , Tn, refTable WHERE srcTable.FK ~ Tl.PK
AND ...

wo 97/03406 PCT/US96/08571 AND Tn.F~ = rei~able.PK
Mer the current d~ h~e has been modified to reflect the ~ A-~&eS made to the cGllcspol-dil?g object model, the proposed schema is stored as the current schema to be used as a co...~ on to filrther object model e~ ges S As can be seen from the above desr~;lJl;ol~, the present iu~llion op~;lales to modify an ~sl~lg d~l~b~ce schema based upon ~ $ made to a co.re~londing object model. The present i..~/elLo-~ allows users to easily update a ~ e willwul having to l.nd~ld such ~ ;o~ l;o~ e conce~t~ such as tables, keys, foreign keys, ~u--~;ale keys, etc. It is believed that the present ;~ ion makes 10 re1s~inn~l ~lfil~ es easier to use by persons having little or no lel~l;O~ n~k~ce While the ~l~r~ -~ emho~limPnt of the invention has been ilL~ ed and desc.;l,ed, it will be al~p.~,;aled that various ~ nges can be made therein without de~&~ Lill~ ~om the spirit and scope of the invention.
-

Claims (15)

The embodiments of the invention in which an exclusive property or privilege is claimed are defined as follows:

1. A method of modifying an existing relational database to reflect changes made in a corresponding object model, comprising the steps of:
storing an object model in the memory of a computer system which object model includes at least one object that represents a category of items about which data is stored in the relational database, the object including at least one component that defines the data stored in the relational database for the item;
displaying a visual representation of the object model;
storing a current relational database schema in the memory of the computer system, the current relational database schema defining one or more relational tables included in the existing relational database and one or more columns included in the one or more relational tables;
detecting modifications to the object model made by a user and automatically producing a proposed relational database schema corresponding to the modified object model; automatically comparing the current relational database schema and the proposed relational database schema; and automatically modify relational database based on the comparison of the current relational database schema and the proposed relational database schema without additional input from the user other than the modifications to the object model.

2. The method of Claim 1, wherein the proposed relational database schema and the current relational database schema contain lists of relational database tables, wherein the step of comparing the proposed relational database schema and the proposed relational database schema further comprises the steps of:
automatically determining if each entry in the list of relational database tables in the current relational database schema is found in the list of relational database tables in the proposed schema; and automatically deleting from the relational database each table that is in the list of relational database tables in the current relational database schema but is not in the list of relational database tables in the proposed relational database schema.

3. The method of Claim 2, further comprising the steps of:

automatically determining if there are any entries in the list of relational database tables in the proposed relational database schema that are not found in the list of relational database tables in the current relational database schema; and automatically adding a relational database table to the relational database for each entry that is in the list of relational database tables in the proposed relational database schema but is not in the list of relational database tables in the current relational database schema.

4. The method of Claim 3, wherein the current relational database schema and the proposed relational database schema include a list of each column included in each table, wherein the step of comparing the proposed relational database schema and the proposed relational database schema further comprises the step of:
automatically determining if each entry in the list of columns included in a table within the current schema is found in the list of columns included in the table in the proposed schema; and automatically dropping from the relational database those columns that are included in the table in the current database schema but not in the table in theproposed database schema.

5. The method of Claim 4, wherein if the computer system determines that a column is to be dropped from the relational database, the method further comprises the steps of:
automatically determining whether the column to be dropped from the relational database contains data and if so, to search the list of tables in the proposed relational database schema to determine if the column to be dropped has been moved to another table in the proposed relational database schema; and automatically moving the data from a source table in the relational database to a destination table in the relational database if the column to be dropped is found in another table in the proposed relational database schema.

6. The method of Claim 5, wherein the step of moving the data from the source table to the destination table comprises the steps of:
automatically creating a temporary table in the relational database;
automatically moving the data into the temporary table;

automatically determining whether the destination table includes any data that is to be saved and if so, moving the data to be saved into the temporary table;
automatically dropping the destination table from the relational database;
automatically creating a new destination table that includes columns to hold the data that was to be saved and the data from the source table;
automatically moving the database from the temporary table into the new destination table; and automatically dropping the temporary table from the relational database.

7. The method of Claim 1, wherein the relational database includes one or more tables that have foreign keys to other tables in the relational database, the method further comprising the steps of:
automatically creating a list in the memory of the computer that includes each table is to be modified, wherein the entries in the list are arranged so that table containing foreign keys to other tables in the database are modified after the tables to which the foreign keys refer.

8. The method of Claim 5, wherein the step of moving the data from the source table to the destination table further comprises the steps of:
automatically determining a migration path between the source table and the destination table that is indicative of a foreign key relationship between the source table and the destination table.

9. A computer system for modifying a current relational database schema to reflect changes made to a corresponding object model that comprises at least one object that is representative of information stored in the relational database, each object including at least one component that is representative of data stored in the relational database comprising:
a processing unit;
a memory coupled to the processing unit;
a storage means for storing a current relational database schema;
a display that is driven by the central processing unit to produce a representation of the object model that corresponds to the current relational database schema, data entry means for allowing a user to modify the object model;
a set of programmed instructions disposed within the memory that causes the processing unit to analyze the modified object model and to automatically create a proposed relational database schema, the set of instructions further causing the central processing unit to automatically compare the proposed relational database schemawith the current relational database schema and to automatically modify the current relational database schema to correspond to the modified object model without additional user input other than the modifications to the object model.

10. The computer system of Claim 9, wherein the current relational database schema comprises a plurality of tables, each table having at least one column in which data is stored, wherein the set of instructions cause the computer system to produce a list within the memory that indicates an order in which the tables that comprise the current relational database schema are to be modified to correspond to the modified object model, wherein the list is arranged such that tables having foreign keys to other tables in the current relational database schema are modified after the tables to which the foreign keys refer.

11. The computer system of Claim 9, wherein the proposed relational database schema and the current relational database schema contain lists of relational database tables, wherein the set of instructions cause to computer system to compare the proposed relational database schema and the proposed relational database schema by:
searching the list of relational database tables in the proposed and current database schemas to determine if any relational database tables that are in the current relational database schema are not in the proposed relational database schema; and generating an SQL statement that will drop from the current relational database schema any relational database table that is in the current relational database schema but is not in the proposed relational database schema.

12. The computer system of Claim 11, wherein the set of instructions further cause to computer system to compare the proposed relational database schema and the proposed relational database schema by:
searching the list of relational database tables in the proposed and current database schemas to determine there are any relational database tables in the proposed relational database schema that are not found in the list of relationaldatabase tables in the current relational database schema; and generating an SQL statement that adds a relational database table to the current relational database schema for each relational database tables in the proposed relational database schema but not in the current relational database schema.

13. The computer system of Claim 11, wherein current relational database schema and the proposed relational database schema include a list of each columnincluded in each relational database table, wherein the set of instructions further cause the computer system to compare the proposed relational database schema and the proposed relational database schema by:
searching the list of columns for each table in the current relational database schema and the proposed relational database schema to determining if there are any columns included in a relational database table in the current database schema that are not found in relational database table in the proposed relational database schema; and generating an SQL statement that drops from the current relational database schema those columns that are included in the table in the current relational database schema but not in the table in the proposed relational database schema.

14. The method of Claim 4, wherein the set of instructions cause the computer system to operate by:
determining whether the column to be dropped from the database contains data and if so, searching the some of the relational database tables in the proposed relational database schema to determine if the column has been moved to another table in the proposed relational database schema; and generating an SQL statement that when executed by the computer system moves the data from a source table in the relational database to a destination table in the relational database if the column to be dropped is found in another table defined in the proposed relational database schema.

15. The computer system of Claim 14, wherein the set of instructions cause the computer system to generating a series of SQL statements that will:
create a temporary table in the relational database;
move the database into the temporary table;
determine whether the destination table includes any data that is to be saved and if so, moving the data to be saved into the temporary table;
drop the destination table from the relational database;

create a new destination table that includes columns to hold the data that was to be saved and the data from the source table;
move the database from the temporary table into the new destination table; and drop the temporary table from the relational database.