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

`

`US005615367A
`11»
`United States Patent
`5,615,367
`{11] Patent Number:
`
`[45] Date of Patent: Mar. 25, 1997
`Bennettet al.
`
`OU ARTAAOTA
`
`[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:
`
`May25, 1993
`
`Burns, L.M.et al., “A Graphical Entity—Relationship Data-
`base Browser,” System Sciences, 1988 Annual Hawaii Int'l
`Conference, vol. 2., pp. 694~704.
`Siau, K.L. et al., “Visual Database Interface for End User
`Computing,” Computing and Information, 1992 Int’! Con-
`ference, pp. 393-396.
`Kiispert, K., et al., “Design Issues and First Experiences
`with a Visual Database Editor for the Extended NF? 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, a quar-
`terly newsletter of SIGBDP,vol. 1, No. 2, Summer 1969, pp.
`4-10.
`
`[ST] Ute Co ec ccecccccssssesessstsesssssessssesecsssseessseee G06F 17/30
`[52] US. Cheeee ecssecasessessstesseenans 395/613; 395/326
`(58) Field of Search ou... ccccssscecsseeeees 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...eeeceseeeeseeertsceeeee 395/600
`3/1993 Krieger et al...
`seseeesenees 395/157
`
`..
`. 395/600
`4/1994 Terada et al.
`
`
`11/1994 Dipaolo et al.
`395/149
`9/1996 Lietal.
`.....
`395/148
`
`OTHER PUBLICATIONS
`
`Demariaet al, “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. 39-48.
`
`A system includesa relational database management system
`(RDBMS) having a data modeling component. A “data
`model” 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-
`tate tables appears to the user to come from one place).
`Methodsare 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
`
`TYPICALLY, GETPR OF
`MASTER (FIRST) TABLE
`JS REYOF SECOND TABLE
`UNIQUE KEYOFFIRST?
`0g_AVAILABLE WHICH SATISFI
`
`
`COMPOSITE INDEXES
`
`PREFER SIMPLE OVER
`
`(04. EXACTSTR, INTIAL STRa,
`SOUNDEXMATCH, AND THE LIKE)
`
`We
`
`
`
`
`
`
`INTELLIGENTLYDETERMININGLINK
`
`UkACCEPT
`
`

`

`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
`U.S. Patent
`
`Mar. 25, 1997
`Mar. 25, 1997
`
`Sheet 5 of 29
`Sheet 5 of 29
`
`5,615,367
`5,615,367
`
`
`
`VeOld
`
`098
`
`saadoiaid
`
`
`
`ShedeeAhMecheReebeeace
`
`GeereydaH Mopurmy
`
`

`

`U.S. Patent
`U.S. Patent
`
`Mar. 25, 1997
`
`Sheet 6 of 29
`
`5,615,367
`5,615,367
`
`oY
`
`gers
`
`ReneeROOKeSeea¥>EeesoeSeeeeaSRRROENSTeeSsoemSxsooSSOs
`SRECONLOSesiene
`x;PSECOSOOROREOS.USESOOSidFOONSOROOROROOOOSenos
`SSSN
`‘
`
`oe
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`0ee62e8ceLee
`
`G&Old
`
`
`
`
`
`

`

`U.S. Patent
`U.S. Patent
`
`Mar. 25, 1997
`Mar. 25, 1997
`
`Sheet 7 of 29
`Sheet 7 of 29
`
`5,615,367
`5,615,367
`
`Eck Table
`
`380
`
`360
`
`
`
`
`
`FIG, 3C
`FIG. 3C
`
`

`

`U.S. Patent
`U.S. Patent
`
`Mar. 25, 1997
`Mar. 25, 1997
`
`Sheet 8 of 29
`Sheet 8 of 29
`
`5,615,367
`5,615,367
`
`
`
`tie Edn Ferm Hecerd Properties Window
`
`
`
`
`
`FIG. 3D
`FIG. 3D
`
`

`

`U.S. Patent
`U.S. Patent
`
`Mar. 25, 1997
`Mar. 25, 1997
`
`Sheet 9 of 29
`Sheet 9 of 29
`
`5,615,367
`5,615,367
`
`
`
`?"?
`7 300
`
`
`
`
`
`
`
`
`370
`
`
`
`FIG. 3E
`FIG. 3E
`
`

`

`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
`
`Mar. 25, 1997
`
`Sheet 11 of 29
`
`5,615,367
`5,615,367
`
` VEDd
`STHTJOBWUOY
`Tang|dkSEC|8>tZ|SoeOCA|QWeENPiet
`tempadalyopqe119¢_S=—ele8ue
`
`
`
`diaMOpulsopadorypsooayaqelup3aid
`
`
`
`
`
`SMOPULAA10)XOpeleg
`
`
`
` LNJawojsnSWOLSN
`
`=|
`
`dese
`
`VvN
`
`Vv
`
`Vv
`
`Vv
`
`Vv
`
`Vv
`
`aVv
`
`apojeisoqydiz}g
`
`ADIG/BBISTS
`
`ISM416
`
`
`

`

`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
`U.S. Patent
`
`Mar. 25, 1997
`Mar. 25, 1997
`
`Sheet 14 of 29
`Sheet 14 of 29
`
`5,615,367
`5,615,367
`
` 65Jo|poo
`
`
`
`=|SyGaquyquasajoy
`
`sayswore
`
`NONpus,
`
`Oy
`
`"Lbab
`
`'ZBLL"LL"h
`
`Zc@Zz
`
`UaSUUGNIA
`
`}} ( | )) !! !! ) (IN I A
`
`pT
`
`
`
`SMOPUIAA10}XOpeled
`
`
`
`
`
`
`
`dja}MopuyzAsapisdosypiooayyaiqelypaid
`
`

`

`U.S. Patent
`U.S. Patent
`
`Mar 25, 1997
`Mar. 25, 1997
`
`Sheet 15 of 29
`Sheet 15 of 29
`
`5,615,367
`5,615,367
`
`4049015
`
`
`
`12S
`
`PYplayYyINOS491
`
`1881SPIENOS
`
`?? *?<<<<<< <<
`Szaqdd¢ededqae
`
`agelyey4
`fateMereCer@inirretea0Letabs)keiraoathsMead2Otosee
`
`
`
`
`y98NSsweppy9gBy4eyosn]“AATOO'r29Z.
`
`SMOPULA,10)XOpRled
`djajj-mopuAAsapadosg«=pus99f]
`
`
`
`
`
`EZ19|plovey
`
`ta
`
`
`

`

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

`

`U.S. Patent
`U.S. Patent
`
`Mar. 25, 1997
`Mar 25, 1997
`
`Sheet 17 of 29
`Sheet 17 of 29
`
`5,615,367
`5,615,367
`
`
`
`
`
`402!
`452
`
`— 409
`_ 409
`
`seems|
`
`405
`
`405
`oF
`
`
`
`De
`ease
`g
`§ 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
`U.S. 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
`U.S. Patent
`
`Mar. 25, 1997
`Mar 25, 1997
`
`Sheet 20 of 29
`Sheet 20 of 29
`
`5,615,367
`5,615,367
`
`
`
`
`Table : ORDERS. DB
`
`
`| a
`
`sgprewnvecnmnmnanne
`
`
`
`Form : SUMMAR'W.FSL
`
`NE <aun! Cive Chonan
`
`
`
`
`
`
`2
`
`=== 8
`
`z
`3
`oe
`
`
`
`Credit
`
`
`Check
`
`Cash
`
`Vise
`
`

`

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

`

`U.S. Patent
`U.S. Patent
`
`Mar. 25, 1997
`Mar. 25, 1997
`
`Sheet 22 of 29
`Sheet 22 of 29
`
`5,615,367
`5,615,367
`
`
`
`ein
`
`
`
`;
`:
`:
`§
`§
`
`it
`
`

`

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

`

`U.S. Patent
`U.S. Patent
`
`Mar. 25, 1997
`Mar. 25, 1997
`
`5,615,367
`
`Sheet 24 of 29
`Sheet 24 of 29
`
`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
`IMPROVED 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 anyoneof the patent documentor 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 conceptofrelational 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. Oneinstruction, for instance, is typically
`to instruct the system to allocate sections in memory for data
`to be read from a storage disk. Another command maytell
`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. Andstill
`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,astraditional data-
`base products employ proprietary data access procedures,
`knowledge of one product is not necessarily helpful in use
`of another. And where database systemsdiffer, their practi-
`tioners cannot effectively communicate with one another.
`
`10
`
`15
`
`20
`
`25
`
`30
`
`35
`
`40
`
`45
`
`50
`
`35
`
`60
`
`65
`
`2
`In 1970, Dr. E. F. Codd invented the “relational model’,
`a prescription for how a DBMSshould 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 asrela-
`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 onthe basis of suchlinks.
`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 manipulationis 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 ofthe databasethat is
`removed from the hardware level. Non-relational DBMSs,
`in contrast, require complex programmingskills that form
`an inherently unreliable meansto interact with databases.
`The general construction and operation of a database
`management system is knownin the art. See e.g., Date, C.,
`An Introduction to Database Systems, Volume J and U,
`Addison Wesley, 1990; the disclosures of which are hereby
`incorporated by reference.
`Today, relational systems are everywhere—commonly
`seen operating in corporate, government, academicsettings,
`and other shared environments. A typical installation will
`employ oneof the popular UNIX-based RDBMSrunning 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 manyof 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 underpinningsofrelational 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 havingthe 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 ofrelational 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-valuecells 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-manyrelation” “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 oftherelational
`model, typically with two or three pages of a user’s manual
`attempting to provide a “quick and dirty” descriptionoffirst
`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.
`
`Whatis 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 managementproblem 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.
`SUMMARYOF 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,
`efficient 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 oneplace.
`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 mayuse 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
`
`20
`
`25
`
`30
`
`35
`
`45
`
`50
`
`35
`
`60
`
`65
`
`4
`The data modeling moduleincludestoolsto assist the user
`in achieving a desired model. In general operation, the user
`specifies two tablesto link (e.g., with a pointing device). In
`response, the system of the present invention intelligently
`determinesa link (relationship) whichis 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 indexablefield) ofthe 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 proceedto find the best name match available (from the
`one or more data-type compatible indexes).
`Uponselecting an index, the system may suggest a link as
`follows.If the index ofthe detail table is a unique index, then
`a one-to-onelink 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 indexablefields), then the
`system does not suggest a link to the user. At this point,
`however, the user may manually select a desiredfield(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 orrejected. Tools are also provided whichallow the
`user to manually construct a link as desired. In this manner,
`the data modeling module of the present invention allows a
`user to efficiently 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.1Ais 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 screenshotillustrating 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.3Bis an enlarged view of a toolbar from the interface
`of FIG. 3A.
`
`FIGS. 3C-E are bitmap screenshotsillustrating 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 screenshotsillustrating the struc-
`turing of information tables for the system of FIG. 3F.
`
`

`

`5,615,367
`
`FIGS. 7A-—B are block diagramsillustrating the automatic
`determination of a link betweentables.
`
`FIGS. 8A~B are flowcharts illustrating a method of the
`present invention for automatically linking tables.
`GLOSSARY
`
`6
`5
`Desktop: The main window in system. The Desktop is the
`FIGS. 4A-F are bitmap screenshots introducing a Data
`highestlevel of interaction with all system objects.
`Model component of the present invention.
`detail table: In multi-table relationships, the table whose
`FIGS. SA~F are bitmap screenshots illustrating the Data
`records are subordinate to those of the master table.
`Model component being employed for modeling a mullti-
`dialog box: A box that requests or provides information.
`table relationship, between a mastertable andadetail table.
`Many dialog boxes present options to choose among
`FIGS. 6A-Eare bitmap screenshotsillustrating the Data
`before one can perform an action. Other dialog boxes
`Model component being employed for modeling a complex
`display warnings or error messages.
`data model, one having multiple relationships between many
`domain: A set of permissible values(i.e., pool of values) for
`tables.
`one or more (shared) columns that have the same mean-
`ing.
`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 containedin onefield 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.
`
`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.
`alphanumericfield: A field containing letters, numbers, or a
`combination of both.
`ASCIL 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 moretables.
`group: (1) In a report or query, a set of records that either
`The document then takes its data from the table(s) to
`have the same value in one or morefields; fall within a
`which it is bound.
`range of values; or are displayed in a fixed number of
`blank field: A field that does not contain a value.
`records; and (2) to collectively identify various objects as
`candidate keys: Keys comprising ali sets of column combi-
`a single entity.
`nations with unique values for a table. One of these is
`index: A file that determines an order in which the system
`selected as the primary key; the rest remain alternate keys.
`can access the records in a table. A system table’s key
`cascade: To use referential integrity to update child tables
`establishes its primary index.
`when a value changes in the parenttable.
`command: a word on a menuor button that one chooses to
`inspect: To view or change an object’s properties. To inspect
`an object, one would either right-click it or select it with
`perform an action.
`the keyboard and press Fé. The object’s menu appears.
`composite key: A key comprised of two or more fields of a
`One selects from the menu the property he or she wants
`table which, together, provide a unique value for each
`record of the table.
`to change.
`data: The information stored inatable.
`key:A field or groupoffields in a system table used to order
`40
`records or ensure referential integrity. Establishing a key
`data integrity: The assurance that the values in a table are
`hasthree effects: (1) The table is prevented from contain-
`protected from corruption.
`ing duplicate records; (2) The records are maintained in
`data type: The kind of data a field can contain. Data types
`sorted order based on the key fields; and (3) A primary
`include alphanumeric, number, currency, date, short num-
`index is created for the table.
`ber, memo, formatted memo, binary, graphic, and OLE.
`link: To establish a relationship between tables by linking
`database: An organized collection of information.
`correspondingfields.
`Database Management System (DBMS): System that con-
`logical value: A value (True or False) assigned to an expres-
`trols the organization, storage, and retrieval of informa-
`sion whenit is evaluated.
`tion in a database.
`lookuptable: A table that assures that a value entered in one
`default: What the system automatically does or lookslike in
`table matches an existing value in anothertable.
`the absence of an overriding command.
`Main menu: The menu bar across the top of the system
`default action: The choice that the system determines to be
`the most logical or safest and the one that will be carried
`Desktop.
`mastertable: In a multi-table relationship, the primary table
`out unless otherwise specified. Default actions are per-
`of a user’s data model. If one has only one table in his or
`formed by double-clicking on an object or its icon.
`her data model, that table is the master table.
`define: to attach a design object to data from a table. For
`multi-record: Refers to an object
`that displays several
`example, one would define a field object in a form as a
`field in a table.
`records at once in a form orreport.
`normalized data structure: An arrangementof data in tables
`design document: A form or report that one creates or
`in which each record includes the fewest numberoffields
`modifies in a design window.
`necessary to establish unique categories. Rather than
`design object: An object one can place in forms andreports.
`using a few redundant fields to provide all possible
`Onecreates design objects using toolbar tools in a design
`window.
`‘
`information within a single table, normalized tables dis-
`tribute information over many tables using fewer fields.
`design window: The window where onecreates or modifies
`Normalized tables provide more flexibility in terms of
`the design of a document. If one is viewing data in a Form
`analysis.
`or Report window,he or she can select the Design button
`object: A table, form, report, query, script, or library. All
`to open the corresponding design window for that docu-
`ment.
`entities that can be manipulated in the system are objects.
`
`10
`
`15
`
`20
`
`25
`
`30
`
`35
`
`45
`
`50
`
`55
`
`60
`
`65
`
`

`

`5,615,367
`
`8
`on-chip cache or external cache (as shown). Additional
`input/output devices, such as a printing device 108, may be
`included in the system 100 as desired. As shown,the various
`components of the system 100 communicate through a
`system bus 110 or similar architecture. In a preferred
`embodiment, the system 100 includes an IBM PC-compat-
`ible personal computer, available from a variety of vendors
`(including IBM of Armonk, N.Y.).
`
`System Software
`
`A. Overview
`
`Illustrated in FIG. 1B, a computer software system 150 is
`provided for directing the operation of the computer system
`100. Software system 150, which is stored in system
`memory 102 and on disk memory 107, includes a kernel or
`operating system (OS) 140 and a windowsshell 145. One or
`more application programs,suchas application software 125
`Or one or more windowsapplication software 151, 153, 155,
`may be “loaded” (i.e., transferred from storage 107 into
`memory 102) for execution by the system 100. As shown,
`windows application software includes a Relational Data-
`base Management System (RDBMS) 155 of the present
`invention.
`
`10
`
`15
`
`20
`
`25
`
`40
`
`45
`
`50
`
`7
`OLE: OLEstands for Microsoft Windows’ Object Linking
`and Embedding. One can use OLEto insert files from
`OLEservers into system tables or OLE objects.
`primary index: An index on the key fields of a system’s
`table. A primary index (1) Determines 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 changeits 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 ofrelated fields of data.
`record number: A unique numberthat identifies each record
`in a system table.
`referential integrity: A way of ensuringthatthe 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
`System 150 includes a user interface (UI) 160, preferably
`changing the view orderof tables.
`a Graphical User Interface (GUD,for receiving user com-
`set: A s