Unified Patents Inc. v. Vilox Technologies LLC
`Ex. 1006 / Page 1 of 24
`
`

`

`U.S. Patent
`
`Dec. 23, 1997
`
`Sheet 1 of 12
`
`5,701,453
`
`WOISAHd
`
`NAaxHoS
`
`LHOd34
`
`YOLVHANAD
`
`VWSHOS
`
`NaaY0s&
`
`0%NASYOS
`
`
`
`YOLVYANSSVW3SHOS
`
`
`
`LYOd4uWOISAHd
`
`WOIDOT
`
`VWSAHOS
`
`QNaA
`
`Y3SN
`
`¢Old
`
`Ex. 1006 / Page 2 of 24
`
`GNA
`
`YWasn
`
`t J
`
`d
`
`Ex. 1006 / Page 2 of 24
`
`

`

`U.S. Patent
`
`Dec. 23, 1997
`
`Sheet 2 of 12
`
`5,701,453
`
`LEVELS
`TABLES
`28
`32
`—TTFFa
`129
`
`MASTER
`
`CUSTOMER
`
`
`
`CUSTOMER
`
`
`
`132 \ MASTER
`
`LEVEL 133(TABLELEVEL STORE
`
`29
`30
`
`1ST DETAIL
`LEVEL
`34
`
`2ND DETAIL
`LEVEL
`35
`
`SALESHEADER 136
`
`
`135
`SALES ITEMS|
`LEVEL
`
`
`
`|
`
`SALES_ITEMS
`PARTS
`
`137
`138
`
`DETAIL
`TABLES
`36
`
`
`
`42
`
`LOGICAL
`
`SCHEMA
`
`TABLES
`
`DATABASE
`
`LS
`
`=
`
`ee
`
`USER
`TABLES
`38
`
`CATAL
`
`SYSTEM
`TABLES
`
`FIG._4A
`
`Ex. 1006 / Page 3 of 24
`
`Ex. 1006 / Page 3 of 24
`
`

`

`U.S. Patent
`
`Dec. 23, 1997
`
`Sheet 3 of 12
`
`5,701,453
`
`Parts
`
`139
`
`200
`
`Logical Schema Name
`
`{Invoice
`
`Available Tables
`limx.Cost
`Ifmx.Customer
`Ifmx.employee
`Himx.inventory
`lfmx.mfg
`limx.Sales_header
`
`=
`e
`
`>
`
`Selected Tables
`
`Customer
`Store
`Sales_Header
`Sales_Items
`
`FIG._4B
`
`198197
`
`202
`
`132
`
`133
`136
`
`
`lowner char (32
`
`
`
`
`
`
`svwname
`char(32)
`
`
`
`
`
`
`
`
`
`remarks
`
`
`
`Logical Schemaidentifier
`
`
`Logical Schema owner
`
`
`Short nameof Logical Schema
`
`
`name: unique within
`
`syssuperviews.svwname
`Logical Schema description
`char (72)
`(narrative)
`[Columns
`Type
`iswidsid
`
`
`
`
`FIG._5
`
`
`Type
`
`
`
`
`
`Ex. 1006 / Page 4 of 24
`
`Ex. 1006 / Page 4 of 24
`
`

`

`U.S. Patent
`
`Dec. 23, 1997
`
`Sheet 4 of 12
`
`5,701,453
`
`
`
`
`
`
`
`
`
`
`
`
`tableseq
`
`masteralias
`
`cardinality
`
`levelname
`
`
`
`int
`
`char(32)
`char (32)
`char (32)
`
`char(1)
`
`char(1)
`char(32)
`char(32)
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`(narrative)
`
`
`Type[CoumnsSSCS
`
`
`unique
`
`unique
`
`
`
`
`
`Logical Schemaidentifier
`table instance identifier unique
`within svwid
`reference to ownerin systables
`reference to tabnamein systables
`tabalias of table that is "master"
`of this table.
`
`
`numberof rows per master row.
`1=always 1; O=0 or 1; n=0 or
`
`more; N=1 or more: M=master.
`
`There should only be one master
`per Logical Schema. For master,
`the masteralias is NULL.
`(L) ookup only, (U) pdate
`alias name,defaults to table name
`grouping nameforall columns
`joined at this level.
`
`
`
`
`
`FIG._6
`
`Ex. 1006 / Page 5 of 24
`
`Ex. 1006 / Page 5 of 24
`
`

`

`US. Patent
`
`Dec. 23, 1997
`
`Sheet 5 of 12
`
`5,701,453
`
`
`
`
`
`
`
`
`
`
`|svwidintLogicalSchemaidentifier
`
`
`ovefeeeager
`
`
`svwid, tabalias
`
`joinoperator
`
`
`
`
`join operation between columns:
`
`
`
`(=), (!=), (<>), (>), (>=), (s), (<=),
`
`
`Index Name
`
`
`
`
`[svwioinst[unique_[svwid,tablalias, jcolseq
`FIG._7
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`[svwid,colseqSSS
`
`
`svwaliases
`dup
`
`FIG._8
`
`svwid
`tabalias
`colseq
`coiname
`
`colalias
`
`title
`
`index Name
`
`svwaliases1
`
`svwaliases?
`
`i
`
`nt
`
`char (3
`(
`2
`char(32
`
`char (32)
`char (32)
`small
`
`Type
`
`Logical schemaidentifier
`joins to syssvwtables.tablalias
`orders columns
`
`reference to syscolumns.colname
`Logical Schema alias for the
`table/column
`labelfor the alias (left of field)
`title for the alias (abovefield)
`
`Ex. 1006 / Page 6 of 24
`
`
`int
`char (
`
`32
`
`) ) )
`
`Ex. 1006 / Page 6 of 24
`
`

`

`US. Patent
`
`Dec. 23, 1997
`
`Sheet 6 of 12
`
`5,701,453
`
`
`
`
`
`[svwidint[LogicalSchema_——=«sidentifier
`
`
`
`seqno|smallint[linenumberoftheorderbytext__|
`
`
`
`
`(A) sending or (D) escending
`
`
`
`[indexName[Type[ColumnsSS~*d
`
`
`
`unique
`FIG._9
`
`username
`
`svauth
`
`
` svwid
`
`
`
`
`int
`
`char(32)
`
`char(8)
`
`Logical Schemaidentifier, 0 forall
`Logical Schemas
`user login identifier, public for
`all users
`Authorization type
`
`Logical Schema
`readlimit a
`readsperrowiimit
`
`elapsedtimelimit
`
`
`
`
`Types: (1) Logical Schemarecord
`smallint
`isolationmode
`
`
`stability, (2) committed read,
`
`(3) query stability
`
`
`IndexName
`[Type[columns
`
`
`
`
`sysvwauthindex|unique|svwid,username
`FIG._10
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`Ex. 1006 / Page 7 of 24
`
`Ex. 1006 / Page 7 of 24
`
`

`

`U.S. Patent
`
`Dec. 23, 1997
`
`Sheet 7 of 12
`
`5,701,453
`
`EEL
`
`GEL
`
`ZE1
`
`SJBW9)
`SyIBWIdy=|BWIEUAS
`
`uinujsno=winuysnoyour]
`Wuelo}s=WUEIOISc3ury
`
`BOlOAU|xU}]
`SdIHSNOLLV1acet
`J2UMQ:
`KL“Sls
`
`pimas
`SWUELISAD| ousgjes=ousejes9oul
`
`JewojysngJ@woysngTINN3101S8101STINN
`
`
`
`
`
`TINNSUa}]Sa|egSWS}Sales
`
`TINNojuysayesJapeayysayes
`
`
`swoySeesSHONHO<«——_®Japeeysajes|geur]
`
`
`
`
`
`epee}seesFHOWHO|«+——»Jewojsngo1~|eur
`
`
`
`
`seljeqe,adAjosnAyjeutpseo seijeso}sew
`
`a1ojS|<«———»Jawojsny||eurq
`
`
`aweuge]12uMQbasajqe}
`
`PIMAS
`
`—
`
`bl‘“Sld
`
`
`
`Vel“Old
`
`Ex. 1006 / Page 8 of 24
`
`Ex. 1006 / Page 8 of 24
`
`

`

`U.S. Patent
`
`Dec. 23, 1997
`
`Sheet 8 of 12
`
`5,701,453
`
`
`
`
`
`
`
`
`
`
`
`CustomerTable 132
`Iname
`fname
`address
`phone
`city
`state
`storenm
`cusitnum
`
`Store Table 733
`
`storenm
`storeid
`storaddr
`
`Sales_Header Table 736
`
`storeid
`salesdate
`salesprsn
`salesno
`custnum
`
`
`
`
`
`
`
`
`
`
`
`Sales_Items Table 137
`
`itemnm
`
`FIG._12B
`
`Ex. 1006 / Page 9 of 24
`
`Ex. 1006 / Page 9 of 24
`
`

`

`U.S. Patent
`
`Dec. 23, 1997
`
`Sheet 9 of 12
`
`5,701,453
`
`
`
`
`
`
` svwid tabalias
`
`colname label_titlecolalias
`
`
`flag
`colseq
`
`1
`Customer
`0
`Iname
`Iname
`Iname
`Iname
`0
`1
`Customer
`0
`fname
`fname
`fname
`fname
`0
`1
`Customer
`0
`address
`address
`address
`address
`0
`1
`Customer
`0
`phone
`phone
`phone
`phone
`0
`1
`Customer
`0
`city
`city
`city
`city
`0
`1
`Customer
`0
`state
`state
`state
`state
`0
`1
`Customer
`0
`storenm
`storenm
`storenm storenm
`0
`1
`Customer
`0
`custnum
`custnum
`custnum custnum
`0
`Store
`0
`storenm_
`storenm_
`storenm_ storenm_
`0
`Store
`0
`storeid
`storeid
`storeid
`storeid
`0
`Store
`0
`storaddr
`storaddr
`storaddr
`storaddr
`0
`
`
`
`
`
`
`0
`_storeid_
`storeid_
`storeid_
`storeid_
`0
`Sales_Info
`0
`salesdate salesdate
`salesdate
`salesdate
`0
`Sales_Info
`
`0
`_salesprsn salesprsn
`_salesprsn
`salesprsn
`0
`Sales_Info
`
`
`
`
`
`
`Sales_Info salesno_salesno0 salesno salesno 0
`Sales_Info
`0
`custnum_
` custnum_
` custnum_ custnum_
`0
`
`
`
`
`
`
`
`Sales_Items itemnm—itemnm0 itemnm itemnm 0
`Sales_Items
`0
`salesno_
`salesno_
`salesno_ salesno_
`0
`
`SalesItems
`0
`itemno
`itemno
`itemno
`itemno
`0
`
`Sales_Items
`0
`price
`price
`price
`price
`0
`
`Sales_Items
`0
`qty
`qty
`qty
`qty
`0
`0
`SalesItems dscrptn_—_dscrptn0 dserptn dserptn
`
`
`
`
`FIG._13
`
`Ex. 1006 / Page 10 of 24
`
`Ex. 1006 / Page 10 of 24
`
`

`

`U.S. Patent
`
`Dec. 23, 1997
`
`Sheet 10 of 12
`
`5,701,453
`
`seiesejsew
`§seijeqe)
`JQWO}SNDJewoysny7AWTINNB10]SxyZl---JguwojsnyB10}S1LJawojsny4JOWO}SND=xXuU{|LL xw}|©L---
`
`
`
`
`
`
`
`
`
`
`
`sejegseesojulsejes7NJOWOISNDJepeeyHSal]SajeS=slay]SBeS=SwaySajesvL---ojuysayjes1Nojul
`
`SYJBWAlSUWIPUISAd|seyeqeyadAjasnAyjeuipseo
`
`
`
`
`
`Jojeradoulof§=zjooyejeap=joojiejeap=joouaysewbasjoof
`
`
`
`
`TINN“ousajesousgjesLswaysaes9L~"k)]|=f
`
`
`TINN~ouysnooujsnoLojupsayes
`TINN~WUSIO}SLUUaJ01S|8101S
`
`
`PIMAS
`edAyios§=selejoo=oubas
`WIULLeHZt‘Sl
`
`
`SweUuge]18UMObesajqe}
`
`
`
`eueU|
`
`St“Old
`
`Ex. 1006 / Page 11 of 24
`
`Ex. 1006 / Page 11 of 24
`
`
`
`
`

`

`U.S. Patent
`
`Dec. 23, 1997
`
`Sheet 11 of 12
`
`5,701,453
`
`Fields & Expressions
`
`[> Customer
`
`Master
`Level
`29
`
`Logical
`Schema
`Levels
`28
`
`
`
`Detail
`Level
`35
`
`address
`
`phone
`
`custnum
`
`storeid
`
`storaddr
`
`> Sales_Info
`
`storeid
`
`salesdate
`
`salesprsn
`salesno
`
`custnum__
`
`> Sales_Items
`itemnm
`
`salesno
`
`price
`qty
`
`description
`
`Customer
`Level
`129
`
`Fields
`44
`
`Sales
`Level
`134
`
`items
`Level
`135
`
`
`
`FIG._18
`
`Ex. 1006 / Page 12 of 24
`
`Ex. 1006 / Page 12 of 24
`
`

`

`U.S. Patent
`
`Dec. 23, 1997
`
`Sheet 12 of 12
`
`5,701,453
`
`Labels——4
`49S
`
`Last Name:
`Phone #:
`Store Name:
`
`Iname
`phone
`storenm
`
`Store number: storeid
`
`Master
`Level
`Data
`Sales date:
`Sales Person:
`Fields
`salesdate
`salesprsn
`46
`:
`&
`
`Detail ‘itemname_|Part number Price Quantit
`
`Level
`itemno
`price
`ty
`Data
`Data
`Fields
`Fields
`
`
`
`
`
`
`Detail Level
`
`Scroling Table
`
`FIG._19
`
`
`
`Master
`tevel
`ee.
`
`LastName: Smith
`First Name:
`John
`Phone #: 898-3341
`Store Name: X Co.
`
`Store number: G8&392
`
`soe
`Sohema
`“54.
`54
`
`Sales Person:
`Sales date:
`ZI
`Johnson
`5\23\93
`__ Detail
`
`Detail Price_Quantity)/}ScrollingItem name Part number
`
`
`.
`Level
`Level
`1
`Widget
`1
`34851
`Records
`3.90
`1
`pe
`Table
`Widget 2
`786123
`58
`00
`13
`|
`52
`1
`61.001
`Widget 3
`1278
`Widget 4
`52348
`9.43
`41
`
`
`
`Peg
`
`FIG._20
`
`Ex. 1006 / Page 13 of 24
`
`Ex. 1006 / Page 13 of 24
`
`

`

`5,701,453
`
`1
`LOGICAL SCHEMA TO ALLOW ACCESS TO
`A RELATIONAL DATABASE WITHOUT
`USING KNOWLEDGEOF THE DATABASE
`STRUCTURE
`
`10
`
`15
`
`20
`
`25
`
`30
`
`35
`
`2
`Despite the improvement over command line interfaces,
`GUIs suffer a drawbackin that end usersarestill required to
`have knowledge ofthe physical structure of the database. In
`other words, the end user must know the names of, and
`relations between database tables and columns before being
`able to access data in the database.
`the
`i.e..
`The way in which a database is structured,
`relationships between tables and columns, is known as the
`physical schema of the database. The physical schema
`should be designed so that all user applications, which
`retrieve and manipulate data, may do so in a convenient
`manner. The convenience provided by such a database
`design, however, diminishes over time as the database
`matures, since as tables and columnsare added, deleted and
`modified, the original physical schema may no longer pro-
`vide efficient access to information. The data maystill be
`obtained, but the user may have to retrieve the data by
`joining,
`ie.. jumping from table to table. This joining
`process requires additional steps due to the fact that the
`database schema wasnotinitially designed to manipulate the
`new relations among the tables. Thus, schema navigation
`becomes more difficult as the system matures and more
`applications are added.
`The physical database schema cannot be restructured
`from year to year in order to solve this problem. This is
`especially true ofa real-time production databases support-
`ing day-to-day operations, as companies are generally reluc-
`tant to halt a production database and replace it with a new
`application that may contain bugs. As a result, the database
`schema becomes more and more complicated over time, and
`eventually only a few system programmersin the company
`may have the necessary knowledge to perform database
`operations. End users must therefore send database queries
`to these programmers,and as they become busier, a backlog
`of requests may be accumulated.
`GUIs thatdisplay a list of table and column namesfor the
`end user do not solve this problem. Most end users are not
`sophisticated database users and need to view data in
`familiar formats. For example, an end user may prefer to
`look at information in a format such as an invoice or
`purchaser order. The numberof tables in a relational data-
`base and the relationships between those tables may seem
`arbitrary and confusing to end users. The challenge is then
`to provide an end user access to the data he wants without
`having to understandthe physical database schema.
`Accordingly, an object of the present invention is to
`provide improved accessto relational databases.
`A further object of the invention is to provide improved
`access for an end user withoutthat user understanding the
`physical database schema.
`Other objects, features and advantages of the present
`invention will become apparent from the following descrip-
`tion when taken in conjunction with the accompanying
`drawings.
`
`BACKGROUND OF THE INVENTION
`This invention relates to computerized databases, and
`more particularly to a system and method ofretrieving data
`in a relational database using a graphical user interface.
`A database system is a computerized system whose over-
`all purpose is to maintain information and to make that
`information available on demand. Many of the database
`products developed today are based on what is called the
`relational approach. In a conventional relational system the
`data is perceived by theuser as tables. A table is comprised.
`of a row of column headings, and zero or more rows of data
`values. The rowsof the table may be referred to as records.
`while each column represents a field of those records.
`All access to the database is handled by a database
`management system (DBMS). Using the DBMS, users of
`application programs perform operations on the database
`such as storing, editing. updating, inserting, deleting, and
`retrieving data by issuing requests or commands called
`queries. The database language known as SQL(structured
`query language)is the standard languagefor interacting with
`database systems, and is utilized by most commercial data-
`base systems.
`From a highlevel point of view, database systems have a
`twopart structure consisting of the DBMSandtheinterface
`to the DBMS.Interfacesare various applications that enable
`users to control the DBMS.
`Database products are used by a wide variety of entities;
`large companies, for instance, store large amounts ofinfor-
`mation in corporate databases. In general, there are two
`types of employees that may use the corporate database.
`A person known as a database administrator (DBA) or
`system programmer is required to create, maintain and
`implementthe database. Forlarge entities, the DBA function
`may be performed by a team of individuals. However, for
`simplicity the DBA is considered to be a single individual in
`the following description.
`Besides the DBA,the secondtype ofuserto interact with
`the database system is the end user. End users can access the
`database via a separate application program, or through an
`interface provided as part of the database system software.
`For more backgroundon relational databases and the SQL
`language. see Date C.J., An introduction to Database Sys-
`tems; The Systems Programming Series, vol. 1, 5th Ed..
`Addison-Wesley Publishing Company, 1990, which is
`herein incorporated by reference.
`Several problems havebeen associated with conventional
`relational database management systems. In some prior
`approaches, end users mainly access data in the database
`through the use of a database query language, such as SQL.
`The problem with these commanddriven interfacesis that
`they require a certain amount of data processing expertise on
`the part of the end user.
`Other types of relational databases, or applications,
`include a graphical user interface (GUD that allow users to
`interact with items that appear on the computer screen,
`rather than requiring users to memorize the names of com-
`mands. Using a GUL, endusers interact with the database by
`choosing an item from a menu,filling in information on a
`form,or interacting with a dialog box. A dialog box is a type
`of window, invoked by a command,that accepts responses
`needed to carry out a particular command.
`
`45
`
`50
`
`35
`
`SUMMARYOF THE INVENTION
`Thepresent invention is a method for providing access to
`a relational database having data stored as physical database
`records in physical database tables. Pairs of tables which
`will comprise a logical schema are selected from the rela-
`tional database and the logical relationships between the
`pairs of tables are defined. The tables in each pair oftables
`are joined by selecting a field common to both tables. Once
`a join operator is selected for each join field, the logical
`relationship between the pairs of tables is stored in a
`relational database thereby creating a logical schema.
`
`Ex. 1006 / Page 14 of 24
`
`65
`
`Ex. 1006 / Page 14 of 24
`
`

`

`5,701,453
`
`3
`invention provides access to a relational
`The present
`database where an end user queries the database without
`knowledge or understanding of the physical database
`schema. A logical schema is created and displayed to an end
`user as a set of fields grouped by levels that denote logical
`relationships between the information.
`An end user selects from a display those fields for which
`he wishes to see information. The database tables and
`columnscorrespondingto fields selected by the end user are
`then queried. The relationships between the data defined in
`the logical schema controls the order in which data is
`returned and presented to the end user.
`Becauseactual table and column namesare not used in the
`logical schema, the present invention masks the complexity
`of the physical database schema from the end user.
`
`BRIEF DESCRIPTION OF THE DRAWINGS
`
`The accompanying drawings, which are incorporated in
`and form a part of this specification, schematically an
`illustrate embodimentofthe invention and, together with the
`general description given above and the detailed description
`given below, serve to explain the principles of the invention:
`FIG. 1 is a block diagram ofa prior art database approach.
`FIG. 2 is a block diagram illustrating the present inven-
`tion.
`FIG.3 provides a graphical representation ofthe structure
`of logical schema in accordance with the principles of the
`present invention.
`FIG. 4A is a block diagram representing how a logical
`schema is stored.
`FIG. 4B depicts the “Available Tables” list and the
`“Selected Tables” list for a sample customer invoice data-
`base.
`FIGS. 5-10 depict logical schema catalog tables.
`FIG. 11 depicts a row of information inserted into the
`SYSSUPERVIEWStable for the “invoice” logical schema.
`FIG. 12A depicts the SYSSVWTABLES table after rows
`of data have been inserted.
`FIG. 12B depicts the tables and columns in a sample
`database.
`FIG. 13 shows the SYSSVWALIASES tableafter being
`updated with table information.
`FIG. 14 depicts the relationship definition for an
`“invoice” logical schema.
`FIG. 15 shows the SYSVWTABLES table after being
`updated with relationship information.
`FIG. 16 depicts the SYSSVWIJOINStable after being
`inserted with join information from the a sample schema.
`FIG. 17 shows the SYSSVWORDER tableafter a sort
`order has been defined for an “invoice” logical schema.
`FIG. 18 depicts a sample dialog box for a logical schema.
`FIG. 19 is a form produced from a sample Customer
`logical schema.
`FIG. 20 depicts a sample form after records have been
`retrieved from the database.
`
`10
`
`15
`
`20
`
`25
`
`30
`
`35
`
`45
`
`50
`
`35
`
`DETAILED DESCRIPTION OF THE
`PREFERRED EMBODIMENTS
`Reference will now be made in detail to a preferred
`embodiment of the invention. While the invention will be
`described in conjunction with a preferred embodiment,it
`will be understood that this description is not intended to
`limit the invention to that embodiment. On the contrary, the
`
`65
`
`4
`invention is intended to cover alternatives, modifications
`and equivalents, which may be included within the spirit and
`scope of the invention.
`Overview
`Aconventional database is shown in FIG. 1. This database
`requires an end user to know the physical database schema,
`ie.,
`table and column names and relationships. before
`retrieving and viewing data contained in the database. An
`end user, denoted generally by reference numeral 8, is a
`person who queries the database to obtain specific informa-
`tion. In order to perform the query. he mustfirst know a
`physical schema 14 of the database. A database management
`system (DBMS) 16 executesthe user's query to the database
`(not shown) and sendsthe results to a report generator 18.
`Report generator 18 is an application program capable of
`building forms and reports. A form provides end user 8 with
`a window to the database, while a report is a document that
`allows enduser 8 to format and print data retrieved from the
`database. Forms are usually sent to a computer screen 20
`while reports are usually sent to a printer 22.
`Each end user 8, of course, may desire to see different
`views of the data. The different ways an end user 8 may view
`the data is depicted in FIG. 1 as a set of boxes 10 between
`end user 8 and physical schema 14, each box 10 representing
`a different view.
`Different end user views 10 can be illustrated by exam-
`ining a hypothetical sound recording company. Assume the
`database administrator for the company produces a monthly
`report that displays sales of the company’s compact discs
`grouped byartist. Assume further that a sales manager in the
`company needs to see the sales of only two artists over a
`six-month period. Although the sales manager’s report que-
`ries the same tables, the DBA must generate a separate
`report to retrieve the specific information required by the
`sales manager. The two reports produced by the DBA
`represent separate end user views of the data stored in the
`company’s database. The person who generates the reports
`must know the physical schema ofthe database to issue the
`query.
`The operations and features of the present invention are
`illustrated by FIG. 2. As shown, end user 8 queries the
`database within the DBMs16 through a logical schema 12.
`The logical schema 12 eliminates the requirement that end
`user 8 know the physical schema 14 before accessing data.
`Logical schema 12is a set of instructions that defines the
`relationships between selected tables and column data in a
`relational database. In other words, logical schema 12 pro-
`vides a set of rules to control how data is returned from the
`database.
`Thepresent invention is a method for providing access to
`a relational database which has data stored as physical
`database records in physical database tables. Pairs of tables
`comprising a logical schema are selected from the relational
`database and the logical relationships between the pairs of
`tables are defined. Each pair of tables is joined by selecting
`a field common to both tables. Once a join operator is
`selected for each join field, the logical relationship between
`the pairs of tables is stored in a relational database, creating
`a logical schema.
`For example, a logical schema might model a purchase
`order or a sales invoice by specifying the invoice relation-
`ships between customers, orders, and sales items in the
`database.
`Using the present invention, end user 8 can use a graphi-
`cal user interface (GUT) to logical schema 12 to design
`custom forms and reports without knowledge of the database
`structure or actual table and column names. The end user 8
`
`©
`
`Ex. 1006 / Page 15 of 24
`
`Ex. 1006 / Page 15 of 24
`
`

`

`5,701,453
`
`5
`begins by specifying the database and logical schema 12 on
`which a documentis to be based. End user 8 then selects the
`fields or columns he wants from a dialog box (not shown)
`that displays all the columns that are available in logical
`schema 12.
`The dialog box groups columns according to a logical
`schema level, as explained in more detail below. In this
`approach, end user 8 does not need to know which table
`holds a specific item of data. He only needs to know which
`logical schemalevel contains the desired information. For
`example, to place customer information on a form, end user
`8 simply selects displayed fields grouped under a heading
`named customer_info.
`Instead of dealing with rows in individual tables, an end
`user deals with a logical schema record. A logical schema
`record consists of one master-level row and a number of
`dependent detail rows.Forinstance, a logical schema record
`could includeall the sales for one customer andall the items
`for each of those sales. This may encompass many rowsin
`several tables, but is presented to end user 8 as a single and
`simple logical schema record.
`Before end user 8 can makeuseof logical schema 12, the
`schema must first be created. A person with knowledge of
`the physical schema,including a person who later becomes
`end user 8, can create a logical schema 12. Since in most
`large companies the task of creating logical schema 12
`would be the responsibility of the DBA, the person who
`creates the logical schema 12 will, for the sake of conve-
`nience and not by the wayof limitation, may hereinafter be
`referred to as the DBA.
`When designing logical schema 12. the first task is to
`choosethe tables an end user 8 needs to access. It must then
`be determined how the data in one table relates to data in
`another table by establishingtherelationships between those
`tables. When the end user 8 sends queries to the database, the
`relationships established in logical schema 12 control the
`master-detail order of the data returned in response to those
`queries. This allows logical schema 12 to portray data
`having different levels of detail.
`Before explaining what is meant by a master-detail order,
`it is necessary to first describe the relationships between
`database tables. In a database designed to follow the rela-
`tional model, each row of data in a table has a number of
`potential relationships to the other rows of data. In other
`words, in addition to the individual entities recorded in the
`database. there are also relationships linking those entities
`together.
`For example, a given database might hold information as
`to which customers ordered what sale items from which
`stores. A relationship exists between customers andstores:
`each store has customersthat placed orders, and conversely,
`a customer may have placed orders in more than onestore.
`Similarly, a sales item may be stocked in several stores, and
`conversely, a store may stock several sales items. These
`relationships may betraversed in either direction.
`Relationships between entities, or fields, of a database are
`a part of the databasejust as the basic data entities are part
`the database. A relationship can be regarded as an entity of
`its own. For example.
`the relationship “sales item X is
`stocked in store Y” is an entity for which one may wish to
`record data, ie. the corresponding quantity.
`A logical schema is a named set of these types of
`relationships and provides a set of rules to control how data
`is returned from the database. The types of relationships
`between tables that can be defined in a logical schema are:
`One-to-One, One-to-Zero-or-One, One-to-Zero-or-More,
`and One-to-One-or-More.
`
`40
`
`45
`
`50
`
`35
`
`60
`
`65
`
`10
`
`15
`
`20
`
`25
`
`30
`
`35
`
`6
`A One-to-One relationship means that for every row in
`onetable, there is only one row of related data in a second
`table. For example. a part may have only have one manu-
`facturer.
`A One-to-Zero-or-Onerelationship means that for every
`row of data in onetable. there is no more than one row of
`related data in a secondtable or there may not be any related
`rowsin the second table. For example, some but not all of
`the items in a catalog may have a correspondingpicture.
`A One-to-Zero-or-Morerelationship meansthat for every
`row of data in one table. there may or may not be multiple
`rowsof related data in a second table. For example, some
`customers may have placed many orders, but there may be
`customers who have never placed orders.
`A One-to-One-or-Morerelationship meansthat for every
`row ofdata in one tabie. there is one or more rowsofrelated
`data in a second table. For example, every order must have
`at least one item onit, and an order can have manyitems.
`The relationship between tables can vary depending on
`the purpose of the logical schema. For example.if a logical
`schema is created that looks at all customers and their
`orders, then the table holding the customer information has
`a One-to-Zero-or-Morerelationship to the table holding the
`order information. However, if a logical schema looks atall
`the orders a companyfills and the customersthat placed the
`orders, the order table has a One-to-Onerelationship to the
`customer table.
`After data has been modeled in the database by defining
`logical schema 12, end user 8 can use that model to create
`any form, report, or query that looks at the data in the same
`logical way. In the sound recording company example given
`above, a logical schema could be created that displays the
`monthly sales of compact discs grouped by artist. Although
`the sales manager may needa specialized view ofthe data,
`the logical schema would enable the sales manager to
`personally choose information he needs without the help of
`a DBA,for instance, and his specialized knowledgeof the
`physical database schema.
`The number of logical schemas to be created for a
`database is determined by the requirements of the end user.
`For example, only one logical schema for each department
`in a company maysuffice, but if end users within the same
`departmentrequire different types of information, then addi-
`tional logical schemascan be created to satisfy those needs.
`Logical Schema Structure
`As shownin FIG.3, a sample logical schema may include
`five tables denoted collectively by reference numerical 32: a
`customer table 132, a store table 133, a sales_header table
`136, a sale_items table 137, and a parts table 138,
`In relational databases, tables are joined by one or more
`columns that contain the same values. For example, cus-
`tomer table 132 and sales_header table 136 can be joined by
`a column named cust_no. The value of this column in both
`tables must match to relate the rows from the two tables.
`The table joins are specified in a logical schema of the
`present invention whenthe relationships amongthe tables is
`defined. Since the joins may be previously defined, all end
`users 8 need to understand when building forms or reports
`is the logical levels 28 into which the data is organized. For
`example, they must know that for every customer there may
`be 0 or moresales, and each sale consists of 1 or more items.
`Thefirst level in a logical schema is known as the master
`level 29. Master level 29 contains a logical schema master
`table 30. All other tables 32 in the logical schema join
`directly or indirectly to the master table 30. In the example,
`the logical schema organizes all the data according to its
`relationships to rows in customer table 132. Therefore,
`customer table 132 is the logical schema master table 30.
`
`Ex. 1006 / Page 16 of 24
`
`Ex. 1006 / Page 16 of 24
`
`

`

`5,701,453
`
`7
`A new logical schema level is created each time a One-
`to-Zero-or-More or One-to-One-or-More relationship
`between tables is established. Any new logical schema level
`under master level 29 is called a detail level. The levels are
`ordered according to how the tables in the level are joined
`to master table 30.
`In the example of FIG. 3, two detail levels exists. a first
`detail level 34 and a second detail level 35. Each row in the
`customer table. 132 may have zero or more corresponding
`rowsin sales_header table 136. If a customer has not made
`any purchases then that customer would not have any rows
`in the sales_header table 132. Therefore, this One-to-Zero-
`or-Morerelationship between tables 132 and 136 spawns a
`first detail level 34. Likewise. each row in sales_header
`table 136 may have one or more corresponding rows in
`sales_items table 137 (a One-to-One-or-Morerelationship),
`so it spawns a second detail level 35.
`Tables joined on a One-to-One or One-to-Zero-or-One
`relationship appear on the same logical schema level. In
`FIG. 3. one row in customer table 132 relates to one row in
`store table 133.
`i.c.. a One-to-One relationship exists
`between tables 132 and 133. Therefore, store table 133
`belongs in the same level as customer table 132. Likewise,
`parts table 138 has a One-to-Onerelationship to sales items
`table 137 so it belongs in the same level as sales items table
`137.
`In a preferred embodiment. logical schema levels are
`automatically named by default. Each level may be given the
`nameofthe first detail table 36 assigned to that level. The
`exception to this is master level 29 of the logical schema
`which assumes the name of master table 30. In a preferred
`embodiment, logical schema levels 28 may be renamed to
`make them more meaningful to the end users. In FIG. 3,
`customer table 132 is the first table assigned to the logical
`schema master level 29, so the name of the master level of
`the logical schema is “customer level” 129. The sales header
`table 136 is the first, and only, table assigned to thefirst
`detail level 34. so the nameofthefirst detail level 34 of the
`logical schema is “sales header level” 134. The Sales__items
`table 137is the first table assigned to the seconddetail level
`35, so the name of the second detail level 35 of the logical
`schema is “sales items level” 135.
`Logical Schema Creation
`To create a logical schema, the structure and contents of
`the database must be known. In a preferred embodimentof
`this invention, each of the following tasks must be per-
`formed when building a logical schema:
`1) openthe database for which the logical schema is to be
`built.
`2) name the logical schema,
`3) select the tables to be included in the logical schema,
`4) specify the relationships between the rows of data in
`the various tables that comprise the logical schema, and
`5) specify the joins between the columnsofthe selected
`tables.
`The following tasks are optional when creating a logical
`schema and may be performed in any order:
`1) select columns to be included in the logical schema
`from the set of chosen tables,
`2) arrange how the columns are to appear to end users
`when building forms and reports,
`3) create aliases for tables or columns,
`4) establish a default sort order for the data retrieved into
`the forms or reports based on the logical schema, and
`5) determine which users will be able to use the logical
`schema.
`
`8
`In the preferred embodiment, each stage in the creation
`process is accomplished by entering information in, or
`makingselections from, a series of dialog boxes displayed
`by a GUI.
`Logical Schema Storage
`Once a logical schema