US0082501 19B2

(12) United States Patent (10) Patent No.: US 8,250,119 B2

Zazrivec et al. (45) Date of Patent: Aug. 21, 2012

(54) DATABASE SCHEMA UPGRADE OTHER PUBLICATIONS

VALIDATION USING DENORMALIZATION
“Denormalization', accessed at: [Link]
Denormalization on Feb. 11, 2010, last updated Feb. 10, 2010, 2
(75) Inventors: Milan Zazrivec, Brno (CZ); Jan pageS.
Pazdziora, Brno (CZ) “Materialized View', accessed at: [Link]
terialized view on Feb. 11, 2010, last updated Nov. 13, 2009, 1 page.
(73) Assignee: Red Hat, Inc., Raleigh, NC (US) "Database Normalization', accessed at: [Link]
wiki/Database normalization on Feb. 11, 2010, last updated Feb. 2,
2010, 10 pages.
(*) Notice: Subject to any disclaimer, the term of this “SQL Create View Statement', accessed at: [Link]
patent is extended or adjusted under 35 [Link] on Feb. 18, 2010, 2 pages.
U.S.C. 154(b) by 170 days. Oracle Database Concepts, 11g Release 2 (11.2) Part No. E10713-05.
Tables and Table Clusters, accessed at: [Link]
(21) Appl. No.: 12/713,919 docs/cd/E1 1882 01/server. 112/e10713/[Link] on May 27,
2010, 32 pages.
Red Hat, Inc. Non-Final Office Action for U.S. Appl. No. 12/789,209
(22) Filed: Feb. 26, 2010 (P732), mailed Jan. 24, 2012.
(65) Prior Publication Data * cited by examiner
US 2011 FO213759 A1 Sep. 1, 2011 Primary Examiner — Amy Ng
(74) Attorney, Agent, or Firm — Lowenstein Sandler PC
(51) Int. Cl. (57) ABSTRACT
G06F 7700 (2006.01)
(52) U.S. Cl. ....................................................... 707/806 Some embodiments of a system and a method to validate
(58) Field of Classification Search .................. 707/802, database schema upgrade using denormalization have been
707/803, 806, 809 presented. For instance, a database deployment engine may
See application file for complete search history. upgrade a database from a first version to a second version.
The database upgraded stores a set of entities. By applying
(56) References Cited denormalization to both the upgraded database and a refer
ence database (which contains a newly installed second Ver
U.S. PATENT DOCUMENTS sion of the database), a database validation engine may
5,717.924. A 2, 1998 Kawai ................................... 1f1
reduce false positives during validation of the database
2004, OO19586 A1* 1 2004 Harter ...... schema upgrade Scripts usable by customers to upgrade their
2006/0020619 A1 1/2006 Netz et al. ..................... 707/102 database schema to the latest version.
2008.OO77632 A1 3/2008 Tysowski et al.
2011/0093469 A1 4/2011 BFar et al. ................... 707/741 15 Claims, 5 Drawing Sheets

-
UPGRADED
DATABASE 12O

SERVER
110 DATABASE SCHEMA
UPGRADE ENGINE
112

DB, VA. NETWORK

ENGINE
114
INTERFACE
116
D
FRESHLY
INSTALLED
DATABASE 122
U.S. Patent Aug. 21, 2012 Sheet 1 of 5 US 8,250,119 B2

ZIT

0| ‘TEWCAT EN15DNE 57?I

U.S. Patent Aug. 21, 2012 Sheet 4 of 5 US 8,250,119 B2

UPGRADE A DATABASE FROMA

FIRST VERSION TO ASECOND
VERSION
310

APPLY DENORMALIZATION TO THE

UPGRADED DATABASE AND A
REFERENCE DATABASE
312

VALIDATE DATABASE SCHEMAS IN

THE UPGRADED DATABASE
AGAINST THE DATABASE SCHEMAS
N THE REFERENCE DATABASE
314 FIG. 3A

QUERY THE UPCRADED DATABASE

CREATE TEMPORARY VIEWS OF TO JOIN MULTIPLE TABLESTO
THE UPGRADED DATABASE PRESENT ENTITIES WITHOUT
320 THER SURROGATE KEYS
330

CREATE SMLARTEMPORARY
SO F E QUERY THE REFERENCE
VIEW EFERENC DATABASE TO JOIN THE SAME
321 TABLES TO PRESENT
CORRESPONDINGENTITIES AND
THER RELATIONS WITHOUT THEIR
COMPARE RELATIONS OF ENTITIES SURROGATE KEYS
IN THE TEMPORARY VEW WITH 331
RELATIONS OF CORRESPONDING -
ENTITIES IN THE REFERENCE
DATABASE
COMPARE RELATIONS OF ENTITIES
322 N BOTH RESULTS
332
FIG. 3B
FIG. 3C
U.S. Patent Aug. 21, 2012 Sheet 5 of 5 US 8,250,119 B2

402
PROCESSING -1 400
DEVICE 410

DATABASE WAL, VIDEO DISPLAY

ENGINE 426

404 432 412

ALPHA-NUMERC
DATABASE WAL INPUT DEVICE
ENGINE 422

406 414

CURSOR
STATIC MEMORY CONTROL
DEVICE

408 416

NETWORK SIGNAL
INTERFACE GENERATION
DEVICE DEVICE

418
420
DATA STORAGE DEVICE
COMPUTER-READABLE
STORAGEMEDIUM 430

422
DATABASE
428 WAL ENGINE

DATABASE WAL
ENGINE
FIG. 4
 US 8,250,119 B2
1. 2
DATABASE SCHEMA UPGRADE using denormalization. In some embodiments, database
VALIDATION USING DENORMALIZATION schema upgrade validation is done during development of
database schema upgrade Scripts. Specifically, a database
TECHNICAL FIELD schema upgrade engine may upgrade the schema of a data
base from a first version to a second version. The database
Embodiments of the present invention relate to databases, schema stores a set of entities and a set of relations of the
and more specifically to database schema upgrade validation. entities. Using denormalization of the database, a database
BACKGROUND validation engine may reduce false positives during valida
tion of the entities and the relations after the upgrade. For
In a typical enterprise installation, application Software as 10 instance, the database validation engine may denormalize the
well as the database schema used by the application is main database by creating temporary views of the database before
tained for many years without fresh reinstallations. Upgrades comparing the relations of the entities. By denormalizing the
are applied to the Software and to the Supporting database database, the database validation engine can ignore Surrogate
schema to keep the application up-to-date with the latest keys associated with the entities and the relations. Because
version produced by the software vendor. Unlike with the 15 the surrogate keys may very likely differ between the
Software where new versions of the programs simply replace upgraded database schema and a freshly installed database
the old ones upon upgrade, upgrading the database schema is schema of the second version (which may be considered as a
much more delicate. The database schema upgrade Scripts reference database schema), ignoring the Surrogate keys dur
need to ensure that the upgraded database schema matches ing validation may help to reduce false positives in validation.
exactly the expectations of the relevant version of software More details of some embodiments of how to validate data
application, not only in the database object definitions, but base schema upgrade using denormalization are described
also for content of database tables, especially for entities that below.
are essential for the correct operation of the application soft In the following description, numerous details are set forth.
Wa.
It will be apparent, however, to one skilled in the art, that the
When developing the database schema upgrade Scripts, it is 25 present invention may be practiced without these specific
essential to validate that when applied to the old database details. In some instances, well-known structures and devices
schema, the result matches the database schema is created by are shown in block diagram form, rather than in detail, in
fresh installation. Conventionally, an iterative approach is order to avoid obscuring the present invention.
used when comparing content of a newly installed database Some portions of the detailed descriptions below are pre
schema (also referred to as a reference) with the content of an 30 sented in terms of algorithms and symbolic representations of
old database schema, upgraded with the database schema operations on data bits within a computer memory. These
upgrade Scripts to the latest version. If the content is the same, algorithmic descriptions and representations are the means
then the database schema upgrade scripts are validated. On
the other hand, if the content is not the same, then the database used by those skilled in the data processing arts to most
schema upgrade Scripts have to be checked and additional effectively convey the substance of their work to others
instructions, such as INSERT, UPDATE, etc., may have to be 35 skilled in the art. An algorithm is here, and generally, con
added. As database schema is highly normalized, with exten ceived to be a self-consistent sequence of operations leading
sive use of static lookup tables and dynamic look up tables to a desired result. The operations are those requiring physical
(which may be referred to as registry tables), the same entity manipulations of physical quantities. Usually, though not
may be represented with records with different synthetic pri necessarily, these quantities take the form of electrical or
mary identifiers in each schema, depending on the order of 40 magnetic signals capable of being stored, transferred, com
actions that have been made in the system and upgrade history bined, compared, and otherwise manipulated. It has proven
of the application and its database schema. This yields a large convenient at times, principally for reasons of common
number of false positives in the output of conventional data usage, to refer to these signals as bits, values, elements, sym
base schema validation tools. Currently, these false positives bols, characters, terms, numbers, or the like.
have to be processed manually. 45 It should be borne in mind, however, that all of these and
similar terms are to be associated with the appropriate physi
BRIEF DESCRIPTION OF THE DRAWINGS cal quantities and are merely convenient labels applied to
these quantities. Unless specifically stated otherwise as
The present invention is illustrated by way of example, and apparent from the following discussion, it is appreciated that
not by way of limitation, in the figures of the accompanying 50 throughout the description, discussions utilizing terms such
drawings and in which: as "upgrading or “validating or "disregarding or "compar
FIG. 1 illustrates one embodiment of a computing system ing or “creating or “executing or the like, refer to the
usable in some embodiments of the invention. action and processes of a computer system, or similar elec
FIGS. 2A-2D illustrate some embodiments of database tronic computing device, that manipulates and transforms
tables and database views. 55 data represented as physical (electronic) quantities within the
FIG. 3A illustrates a flow diagram of one embodiment of a computer system's registers and memories into other data
method to validate database schema upgrade. similarly represented as physical quantities within the com
FIGS. 3B-3C illustrate flow diagrams of various embodi puter system memories or registers or other Such information
ments of a method to apply normalization in database schema storage, transmission, or display devices.
upgrade validation. 60 The present invention also relates to apparatus for perform
FIG. 4 illustrates a block diagram of an exemplary com ing the operations herein. This apparatus may be specially
puting System. constructed for the required purposes, or it may comprise a
general-purpose computer selectively activated or reconfig
DETAILED DESCRIPTION ured by a computer program stored in the computer. Such a
65 computer program may be stored in a machine-readable stor
Described herein are some embodiments of a method, an age medium, Such as, but is not limited to, any type of disk
apparatus, and a system to validate database schema upgrade including floppy disks, optical disks, CD-ROMs, and mag
 US 8,250,119 B2
3 4
netic-optical disks, read-only memories (ROMs), random of the reference database 122. If there is a difference between
access memories (RAMs), EPROMs, EEPROMs, magnetic the relations in the two database schemas, the database vali
or optical cards, or any type of media suitable for storing dation engine 114 may output the difference (e.g., displaying
electronic instructions, and each coupled to a computer sys the difference on a terminal coupled to the server 110, writing
tem bus. the difference into a log file, etc.).
The algorithms and displays presented hereinare not inher For highly normalized database schemas, entities in a data
ently related to any particular computer or other apparatus. base table may be associated with Surrogate keys, which are
Various general-purpose systems may be used with programs internal identifiers pointing to other tables in the database. For
in accordance with the teachings herein, or it may prove instance, the internal identifiers may be integers assigned to
convenient to construct more specialized apparatus to per 10 the entities as the entities are entered into the table during
form the required operations. The required structure for a operation of the database. FIG. 2A illustrates some examples
variety of these systems will appear from the description of such database tables storing a list of countries and a list of
below. In addition, the present invention is not described with parcel companies in the reference database 122. The table 210
reference to any particular programming language. It will be can be used to store countries used by a shipping application.
appreciated that a variety of programming languages may be 15 The other table 220 can be used to store parcel companies
used to implement the teachings of the invention as described used by the shipping application. In the current example,
herein. tables 210 and 220 have been pre-populated with countries
FIG. 1 illustrates one embodiment of a computing system and parcel companies, respectively, in the reference database
usable in some embodiments of the invention. The computing 122. For example, 1 is assigned to United States, 2 is assigned
system 100 includes a server 110 and two databases 120 and to Czech Republic, 3 is assigned to France, and so forth. The
122. In some embodiments, the server 110 and the databases surrogate keys may be used in other tables within the refer
120 and 122 are coupled to a network 130 such that the server ence database 122 to refer to the countries to avoid storing
110 can access both databases 120 and 122. The server 110 multiple strings representing the same country. FIG. 2B illus
may be implemented on one or more computing machines, trates an example of another table using the Surrogate keys of
Such as a desktop personal computer, a laptop computer, a 25 tables 210 and 220. The table 230 in FIG. 2B Shows which
personal digital assistant, etc. Details of one example of a parcel company can be used to ship to which country. Column
computing machine are illustrated in FIG. 4. The network 130 231 in table 230 stores surrogate keys of countries and col
may include various types of network, such as, for example, a umn 232 in table 230 stores surrogate keys of parcel compa
public network (e.g., the Internet), a private network (e.g., a nies. Thus, the content in table 230 is a normalized represen
local area network), a wireless network, a wire-lined network, 30 tation of the information in the relationship view 240 in FIG.
etc. In the current example, database 120 stores a first version 2B, which stores strings representing countries and parcel
of a database schema to be upgraded to a second version, companies instead. Note that the relation table 230 contains
whereas database 122 stores a freshly installed database only the Surrogate key pairs, such as (1,1), (1,3), etc., and the
schema of the second version. The freshly installed database Surrogate keys could be completely different in the upgraded
schema may also be referred to as a reference database 35 database schema. One embodiment of the corresponding
schema because it can be used to validate the upgraded data table of countries and table of parcel companies in the
base schema as explained in details below. upgraded database schema are shown in FIG. 2C. Table 215
In some embodiments, the server 110 includes a database and table 225 store the corresponding countries and parcel
schema upgrade engine 112, a database validation engine companies, respectively, in the upgraded database 120. Note
114, and a network interface 116, coupled to each other. The 40 that the surrogate keys of the countries in table 215 are dif
database schema upgrade engine 112 is operable to deploy a ferent from those in table 210. Likewise, the surrogate keys of
schema upgrade to an existing database, or to install a fresh the parcel companies in table 225 are different from those in
copy of a database schema. The database validation engine table 220. The upgraded database 120 further includes a rela
114 is operable to validate a database schema upgrade. The tionship table as shown in FIG.2D. The relationship table 235
network interface 116 is usable to access the network 130. In 45 in FIG. 2D contains a column 236 storing surrogate keys of
some alternate embodiments, the server 110 may further countries and another column 237 storing Surrogate keys of
include one or more internal storage devices (e.g., hard disks) parcel companies in the upgraded database 120. The entries in
to store at least part of the databases 120 and 122. To further table 235, such as (34.5), (34.8), etc., are different from those
explain the operations of the server 110, some examples are in table 230 in FIG. 2B, but they nevertheless represent the
discussed in details below. 50 same information. In the current example, what really matters
In some embodiments, each of the databases 120 and 122 is that for US, there are DHL, USPS, and UPS listed, not
includes a relational database having some tables and data which Surrogate keys are used. In some embodiments, the
base objects. The database objects may include entities hav relation table can include additional columns to represent
ing various attributes and relations between the entities. The more complicated relationships. In the current example, on
entities, their attributes and their relations may be arranged in 55 the upgraded database 120, US could have an internal surro
some predefined data structure referred to as a schema. For gate key 34, and DHL could have an internal surrogate key 5
example, a schema may define tables and fields used in a as shown in FIG. 2C.
database to store some of the entities, their attributes, and Thus, the entries of surrogate keys would be different
their relations. In some database tables, entities with exactly between the upgraded database schema and the reference
defined attributes have to be present to ensure proper func 60 database schema. But in order to correctly validate the
tioning of applications that use the database. The same may upgraded database schema, the database validation engine
hold true for the relations. Therefore, the database validation 114 has to ensure that, for example, the entry of (US, USPS)
engine 114 has to validate the database schemas of the is seen by the shipping application when it does the lookup or
upgraded database 120. In some embodiments, the database join, but not (CZ, USPS).
validation engine 114 compares the relations of the entities in 65 In order to ensure the shipping application to work cor
the database schema of the upgraded database 120 with the rectly with the upgraded database 120, the database valida
relations of the corresponding entities in the database schema tion engine 114 has to verify that not only the values in both
 US 8,250,119 B2
5 6
tables 210 and 215, as well as the values in both tables 220 and the reference database (processing block314). By denormal
225, are the same, but that the semantic of relationship table izing the database, Surrogate keys of entities can be ignored in
230 matches the one from the upgraded schema (i.e., table validation, thus, reducing the chance of having false positives
235). Note that the order and the surrogate keys of the coun in validation. Further, processing logic reports results of the
tries may be different in databases 120 and 122, but such validation to a user, Such as a system administrator (e.g., in a
differences may not cause applications that use the databases user interface, via a message, etc.).
120 and 122 to function incorrectly. This is because these FIG. 3B illustrates a flow diagram of one embodiment of a
Surrogate keys are the internal representation, while what method to apply normalization in database schema upgrade
matters is the semantic of those values. Therefore, to properly validation. The method may be performed by processing
validate the database schema upgrade, the Surrogate keys 10 logic that may comprise hardware (e.g., circuitry, dedicated
should be ignored in order to reduce false positives in the logic, programmable logic, microcode, etc.), Software (such
validation. as instructions run on a processing device), firmware, or a
To do so, the database validation engine 114 may apply combination thereof. For instance, the server 110 illustrated
denormalization to the databases 120 and 122. In one embodi in FIG. 1 may perform at least part of the method in some
ment, the database validation engine 114 creates temporary 15 embodiments.
views of the databases 120 and 122, which contain the rela Initially, processing logic creates temporary views of the
tions of the entities without their surrogate keys. One embodi upgraded database (processing block 320). For example, pro
ment of a temporary denormalized view of the table 230 is cessing logic may use CREATE VIEW command in Sequen
illustrated in FIG. 2B. Note that the temporary denormalized tial Query Language (SQL) to create a temporary view of the
view 240 contains the (country, parcel company) relationship upgraded database to present only the relations of the entities
described by the names of those entities, without their surro of interest, without the surrogate keys of these entities. Pro
gate keys. Then the database validation engine 114 compares cessing logic further creates similar temporary views of the
the relations of entities (such as which parcel company can be reference database (processing block 321). Then processing
used to ship to which country) in these temporary denormal logic compares the relations in the temporary views of the
ized views of the databases 120 and 122 to determine if there 25 upgraded database with the corresponding relations in the
is any difference in these relations between the two databases temporary views of the reference database (processing block
120 and 122. 322).
Alternatively, the database validation engine 114 may FIG. 3C illustrates a flow diagram of an alternate embodi
query each of the databases 120 and 122 to obtain results that ment of a method to apply normalization in database schema
present the relations of the entities to be compared without 30 upgrade validation. The method may be performed by pro
their surrogate keys. Then the database validation engine 114 cessing logic that may comprise hardware (e.g., circuitry,
may compare the results in order to validate the relations in dedicated logic, programmable logic, microcode, etc.), soft
the upgraded database 120. ware (such as instructions run on a processing device), firm
The approach in the above paragraph allows incorporation ware, or a combination thereof. For instance, the server 110
of the temporary database views into validation in a plugin 35 illustrated in FIG.1 may performat least part of the method in
fashion. When the database validation engine 114 finishes Some embodiments.
comparing the entities and above all, their relations, the tem Initially, processing logic queries the upgraded database to
porary database views may be dropped. By hiding the Surro join multiple tables to obtain a result containing the relations
gate keys, and hence, ignoring the Surrogate keys when com of the entities of interest only, without their surrogate keys
paring the relations of the entities, false positives can be 40 (processing block 330). For example, the entities of interest
reduced in validating upgraded database schemas. As a result, may include countries to which products are shipped and
the need for manual intervention in validation can be parcel companies available. Then processing logic may Sub
decreased, and the scope of upgraded schema validation can mit multiple direct queries (e.g., SELECT in SQL) to obtain
be extended. Furthermore, the above approach using direct a result containing countries and parcel companies, but not
queries may be usable in situations where creating new data 45 the Surrogate keys associated with the countries and parcel
base objects might not be possible, especially in read-only companies. Next, processing logic queries the reference data
environments. base to join the same tables to obtain a result containing the
FIG. 3A illustrates a flow diagram of one embodiment of a corresponding relations of the corresponding entities without
method to validate database schema upgrade. The method their surrogate keys (processing block 331). Then processing
may be performed by processing logic that may comprise 50 logic compares the relations in both results to check for dif
hardware (e.g., circuitry, dedicated logic, programmable ferences (processing block 332). If there is no difference, then
logic, microcode, etc.), software (such as instructions run on the database schema upgrade scripts are validated. Other
a processing device), firmware, or a combination thereof. For wise, changes need to be made in the database schema
instance, the server 110 illustrated in FIG.1 may perform at upgrade scripts and validation is repeated on the changed
least part of the method in some embodiments. 55 Scripts.
Initially, processing logic upgrades a database from a first FIG. 4 illustrates a diagrammatic representation of a
version to a second version (processing block 310). In some machine in the exemplary form of a computing system 400
embodiments, one or more applications have been using the within which a set of instructions, for causing the machine to
first version of the database. Furthermore, the applications perform any one or more of the methodologies discussed
may have entered some entities into the database since then. 60 herein, may be executed. In alternate embodiments, the
In some embodiments, processing logic applies denormal machine may be connected (e.g., networked) to other
ization to the upgraded database and a reference database machines in a LAN, an intranet, an extranet, and/or the Inter
(processing block 312). The reference database contains a net. The machine may operate in the capacity of a server or a
freshly installed database of the second version. Some client machine in client-server network environment, or as a
embodiments of denormalization are discussed in more 65 peer machine in a peer-to-peer (or distributed) network envi
details below. Then processing logic validates database sche ronment. The machine may be a personal computer (PC), a
mas in the upgraded database against the database schemas of tablet PC, a set-top box (STB), a Personal Digital Assistant
 US 8,250,119 B2
7 8
(PDA), a cellular telephone, a web appliance, a server, a The database validation engine 428, components and other
network router, a Switch or bridge, or any machine capable of features described herein (for example, in relation to FIG. 1)
executing a set of instructions (sequential or otherwise) that can be implemented as discrete hardware components or
specify actions to be taken by that machine. Further, while integrated into the functionalities of hardware components,
only a single machine is illustrated, the term “machine' shall such as ASICS, FPGAs, DSPs, or similar devices. In addition,
also be taken to include any collection of machines that indi the database validation engine 428 can be implemented as
vidually or jointly execute a set (or multiple sets) of instruc firmware or functional circuitries within hardware devices.
tions to perform any one or more of the methodologies dis Further, the database validation engine 428 can be imple
cussed herein. mented in any combination of hardware devices and Software
10
The exemplary computing system 400 includes a process components.
ing device 402, a main memory 404 (e.g., read-only memory Thus, some embodiments of a system and a method to
(ROM), flash memory, dynamic random access memory validate database schema upgrade using denormalization
(DRAM) such as synchronous DRAM (SDRAM), a static have been described. It is to be understood that the above
memory 406 (e.g., flash memory, static random access 15 description is intended to be illustrative, and not restrictive.
memory (SRAM), etc.), and a data storage device 418, which Many other embodiments will be apparent to those of skill in
communicate with each other via a bus 432. the art upon reading and understanding the above description.
Processing device 402 represents one or more general The scope of the invention should, therefore, be determined
purpose processing devices such as a microprocessing with reference to the appended claims, along with the full
device, a central processing unit, or the like. More particu Scope of equivalents to which Such claims are entitled.
larly, the processing device may be complex instruction set
computing (CISC) microprocessing device, reduced instruc What is claimed is:
tion set computing (RISC) microprocessing device, very long 1. A computer-implemented method comprising:
instruction word (VLIW) microprocessing device, or pro upgrading, by a processing device executing a database
cessing device implementing other instruction sets, or pro 25
Schema upgrade script, a database schema of a database
cessing devices implementing a combination of instruction from a first version to a second version, the database
sets. Processing device 402 may also be one or more special storing a plurality of entities;
purpose processing devices such as an application specific creating, by the processing device, temporary denormal
integrated circuit (ASIC), a field programmable gate array ized views of each of the second version of the database
(FPGA), a digital signal processing device (DSP), network 30
and a reference database, the temporary denormalized
processing device, or the like. The processing device 402 is
configured to execute the database validation engine 426 for views each comprising relations of a plurality of entities
performing the operations and steps discussed herein. in the corresponding database without Surrogate keys of
The computing system 400 may further include a network the plurality of entities, wherein the reference database
interface device 408. The computing system 400 also may 35 comprises a newly installed database of the second Ver
include a video display unit 410 (e.g., a liquid crystal display sion; and
(LCD) or a cathode ray tube (CRT)), an alphanumeric input validating, by the processing device, the plurality of enti
device 412 (e.g., a keyboard), a cursor control device 414 ties and the relations of the plurality of entities, the
(e.g., a mouse), and a signal generation device 416 (e.g., a validating comprising:
speaker). 40 comparing the plurality of entities in the temporary denor
The data storage device 418 may include a computer-ac malized views for differences; and
cessible storage medium 430 (also known as a computer comparing the relations of the plurality of entities in the
readable storage medium) on which is stored one or more sets temporary denormalized views to reduce false positives
of instructions (e.g., database validation engine 422) during validation of the database schema upgrade Script.
embodying any one or more of the methodologies or func 45 2. The method of claim 1, wherein comparing the relations
tions described herein. The database validation engine 422 of the plurality of entities in the temporary denormalized
may also reside, completely or at least partially, within the views further comprises:
main memory 404 and/or within the processing device 402 executing, by the processing device, a set of one or more
during execution thereof by the computing system 400, the queries against the upgraded database to produce a result
main memory 404 and the processing device 402 also consti 50 comprising the plurality of entities and relations of the
tuting computer-accessible storage media. The database vali plurality of entities, without Surrogate keys of the plu
dation engine 422 may further be transmitted or received over rality of entities; and
a network 420 via the network interface device 408. comparing, by the processing device, the relations of the
While the computer-readable storage medium 430 is plurality of entities in the result with relations of a cor
shown in an exemplary embodiment to be a single medium, 55 responding plurality of entities in the reference data
the term “computer-readable storage medium’ should be base.
taken to include a single medium or multiple media (e.g., a 3. The method of claim 1, wherein a first plurality of
centralized or distributed database, and/or associated caches surrogate keys of the plurality of entities in the database after
and servers) that store the one or more sets of instructions. the upgrading are out of sync with a second plurality of
The term “computer-readable storage medium’ shall also be 60 Surrogate keys of a corresponding plurality of entities in the
taken to include any medium that is capable of storing, encod reference database.
ing or carrying a set of instructions for execution by the 4. The method of claim 1, wherein at least one of the
machine and that cause the machine to perform any one or plurality of entities is created after installation of the first
more of the methodologies of the present invention. The term version of the database, and before the upgrading of the
“computer-readable storage medium’ shall accordingly be 65 database to the second version.
taken to include, but not be limited to, Solid-state memories, 5. The method of claim 1, wherein the database comprises
optical and magnetic media, etc. a relational database.
 US 8,250,119 B2
9 10
6. An apparatus comprising: 11. A non-transitory computer-readable storage medium
a memory; embodying instructions that, when executed by a processing
a processing device communicably coupled to the device, will cause the processing device to perform a method
memory; comprising:
a database schema upgrade engine executable from the executing a database schema upgrade script to upgrade a
database schema of a database from a first version to a
memory by the processing device, the database schema second version, the database storing a plurality of enti
upgrade engine configured to upgrade a database ties;
Schema of a database from a first version to a second creating, by the processing device, temporary denormal
version, the database storing a plurality of entities; and 10
ized views of each of the second version of the database
a database validation engine executable from the memory and a reference database, the temporary denormalized
by the processing device, the database validation views each comprising relations of a plurality of entities
upgrade engine configured to: in the corresponding database without Surrogate keys of
create temporary denormalized views of each of the the plurality of entities, wherein the reference database
second version of the database and a reference data 15
comprises a newly installed database of the second Ver
base, the temporary denormalized views each com sion; and
prising relations of a plurality of entities in the corre validating, by the processing device, the plurality of enti
sponding database while disregarding Surrogate keys ties and the relations of the plurality of entities, the
of the plurality of entities, wherein the reference data validating comprising:
base comprises a newly installed database of the sec comparing the plurality of entities in the temporary
ond version; and denormalized views for differences; and
validate the plurality of entities and the relations of the comparing the relations of the plurality of entities in the
plurality of by: temporary denormalized views to reduce false posi
comparing the plurality of entities in the temporary tives during validation of the database schema
upgrade script.
denormalized views for differences; and 25
12. The non-transitory computer-readable storage medium
comparing the relations of the plurality of entities in of claim 11, wherein the method further comprises:
the temporary denormalized views to reduce false executing a set of one or more queries against the upgraded
positives during validation of the database schema database to produce a result comprising the plurality of
upgrade Script.
7. The apparatus of claim 6, wherein the database valida 30 entities and relations of the plurality of entities, without
tion engine is further configured to: Surrogate keys of the plurality of entities; and
execute a set of one or more queries against the upgraded comparing the relations of the plurality of entities in the
database to produce a result comprising the plurality of result with relations of a corresponding plurality of enti
ties in the reference database.
entities and relations of the plurality of entities, but 13. The non-transitory computer-readable storage medium
without Surrogate keys of the plurality of entities; and 35
of claim 11, wherein a first plurality of the surrogate keys of
compare the relations of the plurality of entities in the result the plurality of entities in the database after the upgrading are
with relations of a corresponding plurality of entities in out of sync with a second plurality of Surrogate keys of a
the reference database.
8. The apparatus of claim 6, wherein the surrogate keys of corresponding plurality of entities in the reference database.
the plurality of entities in the database after the upgrading are 40
14. The non-transitory computer-readable storage medium
out of sync with a second plurality of Surrogate keys of a of claim 11, wherein at least one of the plurality of entities is
corresponding plurality of entities in the reference database. created after installation of the first version of the database,
9. The apparatus of claim 6, wherein at least one of the and before the upgrading of the database to the second Ver
Sion.
plurality of entities is created after installation of the first 15. The non-transitory computer-readable storage medium
version of the database, and before the upgrading of the 45
of claim 11, wherein the database comprises a relational
database to the second version. database.
10. The apparatus of claim 6, wherein the database com
prises a relational database.