`
`Ex. 1011
`EX. 1011
`
`US Patent No. 5,615,367 (“Bennett”)
`US Patent No. 5,615,367 (“Bennett”)
`
`
`
`
`
`

`

`HlllllllllllllllllllllllllIllllllllllllll|||||l||||||lll|||||||||||||H|||
`US005615367A
`
`United States Patent
`
`[19]
`
`[11]
`
`Patent Number:
`
`5,615,367
`
`Bennett et a1.
`Date of Patent:
`[45]
`Mar. 25, 1997
`
`[54] SYSTEM AND METHODS INCLUDING
`AUTOMATIC LINKING OF TABLES FOR
`IMPROVED RELATIONAL DATABASE
`MODELING WITH INTERFACE
`
`[75]
`
`Inventors: John G. Bennett, San Mateo; Perry A.
`Gee; Charles E. Gayraud, both of
`Santa Cruz, all of Calif.
`
`[73] Assignee: Borland International, Inc., Scotts
`Valley, Calif.
`
`[21] Appl. No.: 67,202
`
`[22]
`
`Filed:
`
`May 25, 1993
`
`Burns, L.M. et al., “A Graphical Entity-Relationship Data-
`base Browser," System Sciences, 1988 Annual Hawaii Int’l
`Conference, v01. 2., pp. 694-704.
`Siau, K.L. et al., “Visual Database Interface for End User
`Computing,” Computing and Information, 1992 Int’l Con-
`ference, pp. 393-396.
`Kilspert, K., et al., “Design Issues and First Experiences
`with a Visual Database Editor for the Extended NF2 Data
`Model,” System Sciences, 1990 Annual Hawaii Int’l. Conf.,
`pp. 308-317.
`“Borland Paradox for Windows—Guide to Object PAL,”
`1992 by Borland International Inc., pp. 1—521.
`Bachman, C., Data Structure Diagrams, Data Base, 21 quar-
`terly newsletter of SIGBDP, v01. 1, No. 2, Summer 1969, pp.
`4—10.
`
`Int. Cl.6 .................................................... .. G06F 17/30
`[51]
`[52] US. Cl.
`............................................. 395/613; 395/326
`[58] Field of Search ................................... .. 395/600, 155,
`395/156, 157, 160, 161, 148
`
`Primary Examiner—Paul V. Kulik
`Attorney, Agent, or Firm-John A. Smart
`
`[57]
`
`ABSTRACT
`
`[56]
`
`References Cited
`U.S. PATENT DOCUMENTS
`
`4,791,561
`5,195,178
`5,301,313
`5,367,619
`5,553,218
`
`12/1988 Huber .................................... .. 395/600
`3/1993 Krieger et a1.
`.
`395/157
`
`..
`4/1994 Terada et al.
`. 395/600
`.. 395/149
`11/1994 Dipaolo et a1.
`
`.. 395/148
`9/1996 Li et a1.
`OTHER PUBLICATIONS
`
`Demaria et a], “Working with dBase Mac,” 1988, pp. 16—17,
`115—118, 408—409.
`Microsof Access User’s Guide, MicroSoft Corporation,
`1992.
`Hartzband, David, J. et al., “Enhancing Knowledge Repre-
`sentation in Engineering Database,” Computer, Sep. 1985,
`pp. 3948.
`
`A system includes a relational database management system
`(RDBMS) having a data modeling component. A “data
`mode ” is a graphical representation of the relationship
`between tables one may use in a design document. “Design
`documents” allow a user to customize how his or her data
`
`are presented, including presenting information in formats
`which are not tabular and including formats which link
`together different tables (so that information stored in sepa-
`rate tables appears to the user to come from one place).
`Methods are described for automatically linking tables to be
`placed in a data model by comparing unique keys (e.g.,
`primary key or other unique identifier) of one table with
`indexes (or indexable fields) of another table. Based upon
`the comparison, the system automatically suggests an appro-
`priate link (if any) for the tables.
`
`35 Claims, 29 Drawing Sheets
`
`
`MOUSEDUTTUNDONN
`55 m mg]- m ‘3 Es ONfiKSTTA-BLE.’
`MO
`1.
`DRAGIDSECONDWLE
`ANDMOUSEDUTTDNUF
`
`TYP‘ICALLX GETPK 0F
`MASTEK (FIRED TABLE
`15 KEY0F SECOND TAISLE
`UNIQUE KEYOFF1517
`AVAIMDLEW SATISFIE
`
`
`303
`
`
`
`(4,. morsm mmAL 5m
`sauuparmm. AND ms uxs)
`
`
`
`’1I
`
`
`
`
`
`MEJJGEVTLYDETERMININGLINK
`
`Ul:
`
`ACCEPT
`
`

`

`U.S. Patent
`
`Mar. 25, 1997
`
`Sheet 1 of 29
`
`5,615,367
`
`100.
`
`104
`
`105
`
`106
`
`107
`
`108
`
`??????RD
`
`PONTING
`DEVICE
`
`DISPLAY
`DEVICE
`
`MASS
`STORAGE
`
`PRINTING
`DEVICE
`
`
`
`
`
`102
`
`103
`
`MAIN
`MEMORY
`
`|O
`CONTROLLER
`
`110
`
`FIG. 1A
`
`10:
`
`CENTRAL
`PROCESSOR
`
`111
`
`CACHE
`MEMORY
`
`109
`
`

`

`U.S. Patent
`
`Mar. 25, 1997
`
`Sheet 2 of 29
`
`5,615,367
`
`
`
`EKOVEEEILNI BEST)
`
`
`
`
`
`

`

`U.S. Patent
`
`Mar. 25, 1997
`
`Sheet 3 of 29
`
`5,615,367
`
`SOTE||-||
`
`(SNWmTOO)
`
`9/11
`
`
`
`XEC?N?
`
`
`
`# GB00BB;anTVA ABX
`
`

`

`U.S. Patent
`
`Mar. 25, 1997
`
`Sheet 4 of 29
`
`5,615,367
`
`9:22
`
`
`
`N
`
`28z
`
`
`
`(105180 LXHL)
`
`|82
`
`
`
`(IOBTHO OIHdVH9)
`
`

`

`U.S. Patent
`US. Patent
`
`Mar. 25, 1997
`Mar. 25, 1997
`
`Sheet 5 of 29
`Sheet 5 of 29
`
`5,615,367
`5,615,367
`
`
`
`m3ct5>>E.x::....:;
`
`,W.W2mm32.5%Eugem2m
`
`Sm.GE
`
`098
`omm
`
`

`

`U.S. Patent
`US. Patent
`
`Mar. 25, 1997
`
`Sheet 6 of 29
`
`5,615,367
`5,615,367
`
`mmmmmmmmRm
`
`mmGE
`
`oo
`
`.(flowmwwomwmwmomwwmwowfiwwomowwnwwowwmwmvwm
`wfiowgwwawwmwwfimwwfiwao
`oo..oo.....o3o.«X,K.w9&3?....o$§§§&vx$oo99%J..%%gfivxyfiomvwwvv%%%%s...%%%%%w.3on%%%.
`
`
`
`
`
`
`
`
`
`
`
`
`dwww.‘,.~wmmw..w§w&wwmw.,wnwwmwwy;.wwwwwow..wwwm..%w.&.Humwflwmawvw‘‘RWAwfifiw.www
`
`
`
`
`
`‘VV.wmfiwMMWoWRQMAVMWwfiWWWmWV‘mhvwowwwvfiwmowomomowofiwwmoawmem.o0%cofiv.cm39%ammo”?okofiwowo9&3.0%9%..u..my9.$0.00Eva0&6..cc?».
`
`1.1.5:.:6
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`

`

`U.S. Patent
`US. Patent
`
`Mar. 25, 1997
`Mar. 25, 1997
`
`Sheet 7 of 29
`Sheet 7 of 29
`
`5,615,367
`5,615,367
`
`3&8
`
`380
`
`
`
`
`
`FIG, 3C
`FIG.
`:36
`
`

`

`U.S. Patent
`US. Patent
`
`Mar. 25, 1997
`Mar. 25, 1997
`
`Sheet 8 of 29
`Sheet 8 of 29
`
`5,615,367
`5,615,367
`
`
`
`
`3% gm £39m 32mm E;
`
`
`' E3
`
`
`
`
`FIG. 3D
`FIGS, 3g
`
`
`
`
`
`

`

`U.S. Patent
`US. Patent
`
`Mar. 25, 1997
`Mar. 25, 1997
`
`Sheet 9 of 29
`Sheet 9 of 29
`
`5,615,367
`5,615,367
`
`?"?
`r“ 3% >
`
`
`
`
`
`
`
`
`
`3’29
`
`FIG. 3E
`FIG; 35
`
`

`

`U.S. Patent
`
`5,615,367
`
`
`
`
`
`
`
`
`
`80'SEE|0||8||0?
`
`
`
`
`
`
`
`LLLLLLLL LLLLSLLLLLKKLLLLLLSLL LLLLLLL LLLSL LLLSLLLL LLLSLLL LLSLL LLLLLL0KLL LL
`
`
`
`
`
`
`
`
`
`

`

`U.S. Patent
`U.S. Patent
`
`92f011ae
`
`5,615,367
`5,615,367
`
`M£8<w4,38h.z.bmg5.atmeaz22M83:32“oz:anI\ownrmhmmsmM1betwiowmouhwfifin
`ha32:8Er.mS<
`,wis}:
`82.25
`—05323202F5,529m8.5m;
`
`nNEED-HBBHEEM,..._..,MEMEE&V
`NEWNxomOn092:38.EN...
`
`Gm.GE
`
`<
`
`<
`
`<3.8Emigmm<35355m
`
`Doings-Ghana"93:.
`
`2:252,535.8am;
`
`
`
`"Eu2583m:miohms
`
`
`
`
`
`aux-sets..5.
`
`
`
`
`
`so:32...?Siege28%23=32m
`
`

`

`U.S. Patent
`
`Mar. 25, 1997
`
`Sheet 12 of 29
`
`5,615,367
`
`
`
`£10."SHEICTH
`
`

`

`U.S. Patent
`
`Mar. 25, 1997
`
`Sheet 13 of 29
`
`5,615,367
`
`
`
`
`
`saopu?AA 104 xopeuved
`
`
`
`??????? (IEE (DIREIDDEDI Ë?£5 E 5 EX.:
`
`
`
`
`
`

`

`U.S. Patent
`US. Patent
`
`Mar. 25, 1997
`Mar. 25, 1997
`
`Sheet 14 of 29
`Sheet 14 of 29
`
`5,615,367
`5,615,367
`
`
`
`«32.5.3.2“Sufi-n—
`
`
`
`5.52531223.
`
`02Evan)
`
`}} ( | )) !! !! ) (IN I A
`
`:2.,..:_:,_2_>
`
`
`
`.2.a:a.5:35.39no.5.
`
`E8....
`
`R.fit
`
`In
`
`an...F28.:
`
`so:‘239m,32...?833m.9on.32m
`
`
`
`
`
`
`
`
`
`
`
`

`

`U.S. Patent
`US. Patent
`
`Mar 25, 1997
`Mar. 25, 1997
`
`Sheet 15 of 29
`Sheet 15 of 29
`
`5,615,367
`5,615,367
`
`
`
`
`
`m:x_,.=_n
`
`
`
`
`
`Eur32...;£5.23.“Egan
`
`«33:;.2Xosfiul an.0F‘s-00:
`
`525
`
`3.5m2m28E23:585.
`
`$26«:3qumm
`
`auxaim
`
`am
`
`am
`
`am
`
`am
`
`m
`
`m—9m_.
`
`m—
`
`gz4<<<<<<<<
`?? *?<<<<<< <<
`
`25222
`
`252525)wE?_,FLTV.
`
`EEgnanN
`
`Shemaaumw
`
`3:22.“.9
`
`usuHgnu2m.
`
`

`

`U.S. Patent
`
`Mar 25, 1997
`
`Sheet 16 of 29
`
`5,615,367
`
`343
`
`360a
`
`
`
`-----
`¡ew
`
`es žiadave Help
`#artit.
`
`
`
`
`
`
`
`: ;
`
`3
`
`; :
`:
`: ;
`
`

`

`U.S. Patent
`US. Patent
`
`Mar 25, 1997
`Mar. 25, 1997
`
`Sheet 17 of 29
`Sheet 17 of 29
`
`5,615,367
`5,615,367
`
`
`
`
`
`452
`4552
`
`— 409
`W: 42139
`WW3i
`
`405
`
`435
`Imam DB
`
`
`
`,
`
`_
`
`.
`
`.
`
`§ 3
`
`

`

`U.S. Patent
`
`Mar. 25, 1997
`
`Sheet 18 of 29
`
`5,615,367
`
`
`
`
`
`
`
`
`
`
`
`- 414
`
`# risis.
`grider?tange ...
`
`?Strict franslation i
`
`tit
`
`.
`
`ZipfPosta? Code ?a1#
`Country A20.
`Phone pathi
`First contact ?py
`
`FIG. 4E
`
`

`

`U.S. Patent
`US. Patent
`
`Mar. 25, 1997
`Mar. 25, 1997
`
`Sheet 19 of 29
`Sheet 19 of 29
`
`5,615,367
`5,615,367
`
`
`
`
`
`FIG. 4F
`
`

`

`U.S. Patent
`US. Patent
`
`Mar 25, 1997
`Mar. 25, 1997
`
`Sheet 20 of 29
`Sheet 20 of 29
`
`5,615,367
`5,615,367
`
`
`
`
`
`
`
`"£32m: t GRQERSfiQ
`
`¢.mm.mmm.
`
` Form : SUMMARYfSL
`iluamm'er
` K3933 £39922 Sim m3
`
`
`
`
`
`
`
`r:
`it?
`
`E?
`
`
`5; ,21;
`
`253 mm:
`
`
`
`
`g L
`
`?g g
`
`:Q
`
`?
`
`

`

`U.S. Patent
`
`Mar. 25, 1997
`
`Sheet 21 of 29
`
`5,615,367
`
`
`
`402a
`
`
`
`

`

`U.S. Patent
`US. Patent
`
`Mar. 25, 1997
`Mar. 25, 1997
`
`Sheet 22 of 29
`Sheet 22 of 29
`
`5,615,367
`5,615,367
`
`
`
`
`
`;
`:
`:
`§
`§
`
`
`
`.9. mm
`
`E’miinc Link
`
`

`

`U.S. Patent
`
`Mar 25, 1997
`
`Sheet 23 of 29
`
`5,615,367
`
`576
`
`575
`
`
`
`stat? Ageje i
`
`
`
`?? ??*
`
`
`
`
`??? ???
`583
`58:
`
`%
`
`-
`
`

`

`U.S. Patent
`U.S. Patent
`
`Mar. 25, 1997
`Mar. 25, 1997
`
`Sheet 24 of 29
`Sheet 24 of 29
`
`5,615,367
`5,615,367
`
`
`
`
`
`
`
`

`

`U.S. Patent
`
`Mar. 25, 1997
`
`Sheet 25 of 29
`
`5,615,367
`
`
`
`400e
`
`

`

`U.S. Patent
`
`Mar. 25, 1997
`
`Sheet 26 of 29
`
`5,615,367
`
`?
`
`
`
`3.
`;
`i
`:
`;
`
`linefielit. Itu
`
`SINGLE VALUE LINK 625
`THIS RELATIONSHIP WAS CREATED BETWEEN
`ITEM NO (A SECONDARY INDEX IN LINETTEM)
`AND TEH No {THE PRIMARY ?NDEX OF STOCK,
`
`FIG 6E
`
`

`

`U.S. Patent
`
`Mar 25, 1997
`
`Sheet 27 of 29
`
`5,615,367
`
`(1) FOREIGN KEY MATCH
`(a) ONE-TO-MANY RELATION:
`
`/? 701
`
`CUST —-> ORDERS
`
`(b) MANY-TO-ONE RELATION:
`
`
`
`703
`/?
`
`?? 74
`
`(2) NO FOREIGN KEY MATCH
`(a) TYPE MATCH AGAINST PRIMARY KEY
`(b) AUGMENT TYPE MATCH WITH NAME MATCH.
`(IF POSSIBLE}
`
`CUST -???? ?? ???? ORDERs
`
`
`
`
`
`707
`/?
`
`CUST NO. * `--
`
`CUST NO.
`
`NAME: CUST NO.
`TYPE: A40
`
`NAME: CUST NO.
`????: ?40
`
`FIG. 7B
`
`

`

`U.S. Patent
`
`Mar 25, 1997
`
`Sheet 28 of 29
`
`5,615,367
`
`º-MOUGEBUTTONDOWN
`SECOND
`SELEC
`ON FIR5T TABLE,
`T FIRST AND
`TABLES DRAG TO 5ECOND TABLE
`
`?
`
`803
`
`i5 KEY OF 5ECOND TABLE
`AVAILABLE WHICH 6AT16FIE5
`UNIQUE KEY OF FIRST?
`
`804
`
`Eis YES
`R???????
`
`
`
`
`
`
`
`
`
`
`
`
`
`USE EXISTING (REF
`INTEGRITY) ?
`PREFER 5IMPLE OVER
`?????3?????????5
`
`D???-????
`COMPATIBLE
`???? ABLE)
`
`(e.g. EXACTSTR, INITIAL STRs,
`50UNDEXMATCH, AND THE LIKE)
`
`
`
`NO
`
`
`
`
`
`
`
`
`
`
`
`? ;
`
`FIG. 8A
`
`(IF ? U DAL OG;
`
`812
`ADD ACCEPTED LINK
`TO DATA MODEL
`
`REPEAT UNTIL
`USER DONE
`
`

`

`U.S. Patent
`
`Mar. 25, 1997
`
`Sheet 29 of 29
`
`5,615,367
`
`|
`
`??
`
`
`
`833
`
`ONE-TO-ONE L?NK
`
`ONE-TO-MANY LINK*
`
`* ?R ??????-??? ?F
`UNIQUE KEY ON SECOND
`
`837
`
`FIG. 8B
`
`

`

`5,615,367
`
`1
`SYSTEM AND METHODS INCLUDING
`AUTOMATIC LINKING OF TABLES FOR
`IlVIPROVED RELATIONAL DATABASE
`MODELING WITH INTERFACE
`
`COPYRIGHT NOTICE
`
`A portion of the disclosure of this patent document
`contains material which is subject to copyright protection.
`The copyright owner has no objection to the facsimile
`reproduction by anyone of the patent document or the patent
`disclosure as it appears in the Patent and Trademark Office
`patent file or records, but otherwise reserves all copyright
`rights whatsoever.
`
`BACKGROUND OF THE INVENTION
`
`'The present invention relates generally to information
`processing environments and, more particularly, to model—
`ing information in a data processing system, such as a
`Database Management System (DBMS).
`Computers are a powerful tool for the acquisition and
`processing of information. Computerized databases can be
`regarded as a kind of electronic filing cabinet or repository
`for collecting computerized data files; they are particularly
`adept at processing vast amounts of information quickly. As
`such, these systems serve to maintain information in data—
`base files or tables and make that information available on
`demand. Of these systems, ones which are of particular
`interest to the present invention are Relational Database
`Management Systems (RDBMSs).
`The concept of relational databases is perhaps best intro-
`duced by reviewing the problems surrounding traditional or
`non—relational systems. In a traditional database system, the
`task of retrieving information of interest (i.e., answering a
`“database query”) is left to the user; that is, the user must
`give detailed instructions to the system on exactly how the
`desired result is to be obtained.
`
`Consider the example of a simple query: “Who are the
`teachers of student John Smith?” In a traditional system,
`several explicit instructions are required before the query
`can be answered. One instruction, for instance, is typically
`to instruct the system to allocate sections in memory for data
`to be read from a storage disk. Another command may tell
`the system which disk files to open and read into the
`allocated memory for processing. Still other commands may
`specify particular search strategies, such as use of specific
`indexes, for speeding up the result of the query. And still
`even further commands may be needed for specifying
`explicit links between two or more files so that their data
`may be combined. Thus, instead of just telling the system
`“what” is desired (i.e., the desired data result as expressed in
`a query expression), one must specify internal procedures
`(i.e., the “how”) for obtaining the data. Even for a simple
`query, such as that above, the task is complex, tedious, and
`error-prone.
`
`From the user’s perspective, such details—ones directed
`to the physical implementation——-are completely irrelevant;
`the user is interested only in the result. Thus, the lack of
`separation of logical operations from the physical represen—
`tation of the data (i.e., how it is internally stored and
`accessed by the system) in traditional systems burdens users
`with unnecessary complexity. Moreover, as traditional data-
`base products employ proprietary data access procedures,
`knowledge of one product is not necessarily helpful in use
`of another. And where database systems differ, their practi—
`tioners cannot effectively communicate with one another.
`
`10
`
`15
`
`20
`
`25
`
`30
`
`35
`
`4O
`
`45
`
`50
`
`55
`
`60
`
`65
`
`2
`In 1970, Dr. E. F. Codd invented the “relational model”,
`a prescription for how a DBMS should operate. The rela—
`tional model provides a foundation for representing and
`manipulating data, that is, a way of looking at data. The
`model includes three basic components: structure, integrity,
`and manipulation. Each will be described in turn.
`The first of these, structure,
`is how data should be
`presented to users. A database management system is
`defined as “relational” when it is able to support a relational
`View of data. This means that data which a user can access
`
`and the operators which the user can use to operate upon that
`data are themselves relational. Data are organized as rela-
`tions in a mathematical sense, with operators existing to
`accept relations as input and produce relations as output.
`Relations are perhaps best interpreted by users as tables,
`composed of rows (tuples) and columns (attributes).
`Ideally, data in a relational system is perceived by users
`as tables and nothing but tables. This precludes the user from
`seeing explicit connections or links between tables, or
`having to traverse between tables on the basis of such links.
`It also precludes user-visible indexes on fields and, in fact,
`precludes users from seeing anything that smacks of the
`physical storage implementation. Thus, tables are a logical
`abstraction of what is physically stored.
`The integrity aspect, on the other hand, dictates that every
`relation (i.e., table) should have a unique, primary key to
`identify table entries or rows. The integrity of the data for the
`user is of course crucial. If accuracy and consistency of the
`data cannot be achieved, then the data may not be relied
`upon for decision-making purposes.
`Data manipulation, the last component, may be thought of
`as cut-and-paste operators for tables. Data manipulation is of
`course the purpose for which databases exist in the first
`place. The superiority of manipulating tables relationally
`(i.e., as a whole, or sets of rows) is substantial. Users can
`combine data in various tables logically by matching values
`in common columns, without having to specify any internal
`details or the order in which tables are accessed;
`this
`provides users with a conceptual view of the database that is
`removed from the hardware level. Non—relational DBMSs,
`in contrast, require complex programming skills that form
`an inherently unreliable means to interact with databases.
`The general construction and operation of a database
`management system is known in the art. See e.g., Date, C.,
`An Introduction to Database Systems, Volume I and II,
`Addison Wesley, 1990; the disclosures of which are hereby
`incorporated by reference.
`Today, relational systems are everywhere—commonly
`seen operating in corporate, government, academic settings,
`and other shared environments. A typical installation will
`employ one of the popular UNIX-based RDBMS running on
`a minicomputer. By submitting queries to the DBMS from
`a remote terminal (e.g., using a SQL “query editor”), users
`are often able to handle many of their own data processing
`needs directly. Thus, relational technology is not only just
`another way to build a database system, but it also offers a
`set of underlying principles that provide very direct practical
`benefits to the user.
`
`The strong theoretical underpinnings of relational systems
`which account for their superior design have also created
`some unexpected problems. With the ever-increasing trend
`towards “down-sizing,” more and more data processing
`tasks are being moved off mainframes and minicomputers
`and onto desktop PCs, often operating in a Local Area
`Network (LAN). Although relational systems are easier for
`end users to use (e.g., for querying), they are by no means
`
`

`

`5,615,367
`
`3
`easier to set up or maintain. With the movement of data
`processing chores to desktop PCs, ordinary PC users are
`nevertheless often faced with the responsibility of designing
`and implementing a database system, one having the reli-
`ability and integrity associated with a relational system.
`Consider the following issues attendant to setting up a
`relational database management system (RDBMS). Tables
`in a relational system are not just any tables but are, instead,
`special “disciplined” tables. Relational systems require, for
`instance, that tables not store duplicates (so that each row
`may be uniquely identified by one or more column values).
`Thus, relations or “R-tables” are subject to particular con—
`straints (e.g., “first normal form”). As another example, to
`preserve simplicity and take advantage of relational opera-
`tions, database tables
`should not contain “repeating
`groups”—that is, multi-valued columns. Such multi-valued
`columns remove table resemblance to relations and thus
`prevent tables from taking advantage of the latter’s math-
`ematical properties. Instead, relational tables should contain
`only single-value cells or “atomic” data values. Thus, while
`relational
`tables are simple and flexible in theory,
`they
`nevertheless entail
`rigorous constraints which must be
`obeyed to implement them in practice.
`While trained database administrators have the expertise
`to tackle such issues, ordinary PC users for the most part
`have received no formal data processing education. They
`cannot be expected to be familiar with such seemingly
`esoteric concepts as “joins” “one«to-many relation” “foreign
`keys”, or any of the other myriad of issues which must be
`considered when applying the relational approach to data-
`base management.
`Prior approaches to this problem for PC systems have
`been restricted to reciting a curt overview of the relational
`model, typically with two or three pages of a user’s manual
`attempting to provide a “quick and dirty” description of first
`and second normal forms (and occasionally third normal
`form). Little or no guidance is given to the user in under—
`standing how one goes about modeling his or her data in a
`relational system. Instead, the user is left to fend for himself
`or herself.
`
`What is needed is system and methods whereby ordinary
`end users, particularly those with no data processing expe-
`rience or training, may apply the relational approach to a
`database management problem in a simple, intuitive fashion.
`In particular, such a system should provide tools for auto-
`mating the task of data modeling in a relational database
`system. The present invention fulfills this and other needs.
`SUMMARY OF THE INVENTION
`
`A system of the present invention includes a relational
`database management system (RDBMS), where information
`is maintained in one or more database tables for easy,
`efiicient storage and retrieval. In addition to database tables,
`the system provides “design documents” which allow a user
`to customize how his or her data are presented, including
`formats which are not tabular. Design documents can also
`link together different tables, so that information stored in
`separate tables appears to the user to come from one place.
`The system of the present invention provides a data
`modeling component or module for simplifying the creation
`of design documents. A “data model” is a graphical repre-
`sentation of the relationship between tables one may use in
`a design document. The model provides the user with a
`simple, intuitive way of telling the system which tables are
`desired to be displayed in a design document and how such
`tables work together.
`
`10
`
`15
`
`20
`
`25
`
`30
`
`35
`
`45
`
`50
`
`55
`
`60
`
`65
`
`4
`
`The data modeling module includes tools to assist the user
`in achieving a desired model. In general operation, the user
`specifies two tables to link (e.g., with a pointing device). In
`response, the system of the present invention intelligently
`determines a link (relationship) which is desired between the
`tables. First, the system automatically attempts to create a
`link using a unique key (i.e., primary key, candidate key, or
`other unique index) of the master table. If a foreign key
`relationship exists between the two tables (e.g., defined
`previously through referential integrity), then the system
`automatically selects the foreign key relationship to display
`to the user as a suggested link.
`In the event that no foreign key relationship exists, the
`system determines if one may be implied. Specifically, the
`system searches for an index (or indexable field) of the detail
`table which is data—type compatible with that of the primary
`(unique) key field of the master. If none exists, then no link
`will be suggested. If one is found, however, the system will
`then proceed to find the best name match available (from the
`one or more data-type compatible indexes).
`Upon selecting an index, the system may suggest a link as
`follows. If the index of the detail table is a unique index, then
`a one-to-one link is inferred. If, on the other hand, the index
`of the detail table is non-unique, then a one-to-many link is
`implied. In the instance that no such index has been selected
`(or can be built from one or more indexable fields), then the
`system does not suggest a link to the user. At this point,
`however, the user may manually select a desired field(s) to
`link on, whereupon the system automatically attempts to
`locate a corresponding field which satisfies the selected
`field(s).
`
`After determining the best possible link (if any), the user
`is provided with a screen dialog whereby the link may be
`accepted or rejected. Tools are also provided which allow the
`user to manually construct a link as desired. In this manner,
`the data modeling module of the present invention allows a
`user to efiiciently and quickly create a desired data model,
`thereby simplifying the task of designing documents for
`presenting one’s information in a desired format.
`BRIEF DESCRIPTION OF THE DRAWINGS
`
`FIG. 1A is a block diagram of a computer system in which
`the present invention may be embodied.
`FIG. 1B is a block diagram of a software system of the
`present invention, which includes operating system, appli-
`cation software, relational database management system,
`and user interface components.
`FIG. 1C is a diagram illustrating the conceptual relation
`between a database table and its index.
`
`FIG. 2 is a bitmap screenshot illustrating the basic archi-
`tecture and functionality of a graphical user interface in
`which the present invention may be embodied.
`FIG. 3A is a bitmap screenshot illustrating a preferred
`Desktop or application interface for the system of the
`present invention.
`'
`FIG. 3B is an enlarged View of a toolbar from the interface
`of FIG. 3A.
`
`FIGS. 3C—E are bitmap screenshots illustrating use of the
`Desktop’s client area for displaying and manipulating major
`objects of the system, including table objects, form objects,
`report objects, and the like.
`
`FIG. 3F is a block diagram of a sample database system
`for tracking sales orders.
`FIGS. 3G—K are bitmap screenshots illustrating the struc-
`turing of information tables for the system of FIG. 3F.
`
`

`

`5
`
`6
`
`5,615,367
`
`FIGS. 4A—F are bitmap screenshots introducing a Data
`Model component of the present invention.
`FIGS. SA—F are bitmap screenshots illustrating the Data
`Model component being employed for modeling a multi—
`table relationship, between a master table and a detail table.
`FIGS. 6A—E are bitmap screenshots illustrating the Data
`Model component being employed for modeling a complex
`data model, one having multiple relationships between many
`tables.
`
`FIGS. 7A—B are block diagrams illustrating the automatic
`determination of a link between tables.
`
`FIGS. 8A-B are flowcharts illustrating a method of the
`present invention for automatically linking tables.
`GLOSSARY
`
`alias: The name a user assigns to a directory path.
`alternate keys: Candidate keys (see below) which are not
`selected as the primary key for a table.
`alphanumeric field: A field containing letters, numbers, or a
`combination of both.
`ASCII: American Standard Code for Information Inter-
`change; a sequence of 128 standard characters.
`binary field: A field used to store data the system cannot
`interpret (without additional instructions). A common use
`of a binary field is to store sound.
`bind: To associate a form or report with one or more tables.
`The document then takes its data from the table(s) to
`which it is bound.
`blank field: A field that does not contain a value.
`candidate keys: Keys comprising all sets of column combi-
`nations with unique values for a table. One of these is
`selected as the primary key; the rest remain alternate keys.
`cascade: To use referential integrity to update child tables
`when a value changes in the parent table.
`command: a word on a menu or button that one chooses to
`perform an action.
`composite key: A key comprised of two or more fields of a
`table which, together, provide a unique value for each
`record of the table.
`data: The information stored in a table.
`data integrity: The assurance that the values in a table are
`protected from corruption.
`data type: The kind of data a field can contain. Data types
`include alphanumeric, number, currency, date, short num-
`ber, memo, formatted memo, binary, graphic, and OLE.
`database: An organized collection of information.
`Database Management System (DBMS): System that con-
`trols the organization, storage, and retrieval of informa-
`tion in a database.
`
`10
`
`15
`
`20
`
`25
`
`30
`
`35
`
`40
`
`45
`
`default: What the system automatically does or looks like in
`the absence of an overriding command.
`default action: The choice that the system determines to be
`the most logical or safest and the one that will be carried
`out unless otherwise specified. Default actions are per-
`formed by double-clicking on an object or its icon.
`define: to attach a design object to data from a table. For
`example, one would define a field object in a form as a
`field in a table.
`
`design document: A form or report that one creates or
`modifies in a design window.
`design object: An object one can place in forms and reports.
`One creates design objects using toolbar tools in a design
`window.
`\
`design window: The window where one creates or modifies
`the design of a document. If one is viewing data in a Form
`or Report window, he or she can select the Design button
`to open the corresponding design window for that docu—
`ment.
`
`50
`
`55
`
`60
`
`65
`
`Desktop: The main window in system. The Desktop is the
`highest level of interaction with all system objects.
`detail table: In multi—table relationships, the table whose
`records are subordinate to those of the master table.
`
`dialog box: A box that requests or provides information.
`Many dialog boxes present options to choose among
`before one can perform an action. Other dialog boxes
`display warnings or error messages.
`domain: A set of permissible values (i.e., pool of values) for
`one or more (shared) columns that have the same mean-
`mg.
`drop-down list box: A single—line text box that opens to
`display more choices when one clicks a downward point—
`ing arrow.
`field: A column of information in a table. A collection of
`related fields makes up one record.
`field type: The type of data a field can contain. Field types
`include alphanumeric, number, currency, date, short num—
`ber, memo, formatted memo, binary, graphic, and OLE.
`field value: The data contained in one field of a record. If no
`data is present, the field is considered blank.
`file: A collection of information stored under one name on a
`disk. For example, the system tables are stored in files.
`form: An alternate presentation or view of a table’s data. A
`multi-table form can display data from several tables at
`once.
`
`group: (1) In a report or query, a set of records that either
`have the same value in one or more fields; fall within a
`range of values; or are displayed in a fixed number of
`records; and (2) to collectively identify various objects as
`a single entity.
`index: A file that determines an order in which the system
`can access the records in a table. A system table’s key
`establishes its primary index.
`inspect: To View or change an object’s properties. To inspect
`an object, one would either right-click it or select it with
`the keyboard and press F6. The object’s menu appears.
`One selects from the menu the property he or she wants
`to change.
`key: A field or group of fields in a system table used to order
`records or ensure referential integrity. Establishing a key
`has three efiects: (l) The table is prevented from contain-
`ing duplicate records; (2) The records are maintained in
`sorted order based on the key fields; and (3) A primary
`index is created for the table.
`
`link: To establish a relationship between tables by linking
`corresponding fields.
`logical value: A value (True or False) assigned to an expres-
`sion when it is evaluated.
`
`lookup table: A table that assures that a value entered in one
`table matches an existing value in another table.
`Main menu: The menu bar across the top of the system
`Desktop.
`master table: In a multi-table relationship, the primary table
`of a user’s data model. If one has only one table in his or
`her data model, that table is the master table.
`multi-record: Refers to an object
`that displays several
`records at once in a form or report.
`normalized data structure: An arrangement of data in tables
`in which each record includes the fewest number of fields
`necessary to establish unique categories. Rather than
`using a few redundant fields to provide all possible
`information within a single table, normalized tables dis-
`tribute information over many tables using fewer fields.
`Normalized tables provide more flexibility in terms of
`analysis.
`object: A table, form, report, query, script, or library. All
`entities that can be manipulated in the system are objects.
`
`

`

`7
`
`8
`
`5,615,367
`
`OLE: OLE stands for Microsoft Windows’ Object Linking
`and Embedding. One can use OLE to insert files from
`OLE servers into system tables or OLE objects.
`primary index: An index on the key fields of a system’s
`table. A primary index (1) Detemrines the location of
`records; (2) Lets one use the table as the detail in a link;
`(3) Keeps records in sorted order; and (4) Speeds up
`operations.
`prompt: Instructions displayed on the screen. Prompts ask
`for information or guide a user through an operation.
`properties: The attributes of an object. One must right-click
`an object to view or change its properties.
`query: A question one asks the system about information in
`his or her tables. The query can be a simple question about
`the information in a single table or a complex question
`about information in several tables.
`record: A horizontal row in a system table that contains a
`group of related fields of data.
`record number: A unique number that identifies each record
`in a system table.
`referential integrity: A way of ensuring that the ties between
`like data in separate tables is maintained.
`report: Information from tables printed on paper or pre-
`viewed onscreen.
`
`secondary index: An index used for linking, querying, and
`changing the view order of tables.
`set: A specific group of records (e.g., about which a user
`intends to ask questions).
`structure: The arrangement of fields in a table.
`table: A structure made up of rows (records) and columns
`(fields) that contains information.
`toolbar: The set of buttons and tools for frequently per-
`formed tasks. The toolbar is displayed under the menu bar
`and changes according to the window one is using.
`unique index: An index capable of uniquely identifying each
`record for which a value is given in a table.
`validity check: A constraint on the values one can enter in a
`field.
`
`DETAILED DESCRIPTION OF A PREFERRED
`EMBODIMENT
`
`The following description will focus on the presently
`preferred