Automated report of broken relationships between tables

In a method for reporting data loaded to a database that does not adhere to referential constraints of the database that are not enforced, a computer receives a file of Data Definition Language (DDL) statements for creating a database. The computer identifies statements from the DDL statements that comprise text indicating the statements are referential constraints that are not to be enforced. The computer converts indentified statements to create view statements. The computer generates a file comprising the create view statements. Create view statements are used to generate database views during database build. The computer generates a file, comprising the received DDL statements without the statements that comprise the text indicating the one or more DDL statements are referential constraints not to be enforced in the database. The computer, responsive to new data being loaded to the database, creates a report comprising data from the database views.

TECHNICAL FIELD

The present invention relates generally to database management and more specifically to a method, system, and computer program product for reporting data loaded to a database that does not adhere to not enforced referential constraints of the database.

BACKGROUND

A database is an integrated collection of data records, files, and other database objects needed by an application. Database objects are stored in database tables that are accessible to a user. A database management system (“DBMS”) is the software that controls the creation, maintenance, and the use of a database. The DBMS handles user queries for data.

A relational database is a database that is perceived by its users as a collection of tables. Each table arranges items and attributes of the items in rows and columns, respectively. Each table row corresponds to an item, and each table column corresponds to an attribute of the item (referred to as a field or, more correctly, as an attribute type or field type).

Database management systems provide mechanisms that permit users to constrain the value of one database entity based on the value or existence of another database entity. One common constraint type is the referential constraint. Referential constraints require that a value referred to by one database entity (e.g., a row in a first table) is associated with an existing entity in the database (e.g., another row in the same or different table). In the context of the Structured Query Language (“SQL”), referential constraints are implemented through the use of Foreign Keys, wherein a database entity's Foreign Key value must equate to the Primary Key value of another, existing, database entity.

A Primary Key for a table is a simple or composite attribute that uniquely identifies rows of the table. A key must be inherently unique and not merely unique at a particular point in time. It is possible, but not typical, to have a table where the only unique identifier is the composite attribute consisting of all the attributes of the table. It is also possible, but not typical, for a table to have more than one unique identifier. In such a case, the table may be said to have multiple candidate keys, one of which could be chosen and designated as the Primary Key; the remaining candidates then would be said to be “alternate keys”. Collectively, primary and alternate keys may be referred to as “unique” keys. Primary and alternate keys must satisfy two time independent properties. First, at no time may two rows of the table have the same value for the key. And second, if the key is composite, then no component of the key can be eliminated without destroying the uniqueness property.

A Foreign Key can be a composite attribute of a table having values required to match those of the Primary Key of some table, which is typically but not necessarily distinct from the table for which the Foreign Key is defined. A Foreign Key value represents a reference to the row containing the matching Primary Key value, which may be called the referenced row or the target row.

A “referential integrity” rule requires a database system to maintain “referential integrity” between Foreign Keys and Primary Keys. The rule states that a database must not contain any unmatched Foreign Key values. An unmatched Foreign Key value is a non-null value for which there does not exist a matching value of the Primary Key in the relevant target table. Note that referential integrity requires Foreign Keys to match Primary Keys. A database system is not required to maintain integrity with respect to alternate keys.

A data warehouse (DW) is a database used for reporting and analysis. The data stored in the warehouse is uploaded from source operational systems (systems that process the day-to-day transactions of an organization). Normally a DW has a data model that describes its information. The data model may have defined referential constraints between tables which are not implemented in the physical build of the database. The referential constraints are not implemented to allow for the loading of data from multiple source databases and to increase the speed of loading. By not building referential constraints between tables, source data can be loaded which does not adhere to the data model constraints.

SUMMARY

Aspects of an embodiment of the present invention disclose a method, system, and a program product for reporting data loaded to a database that does not adhere to one or more referential constraints of the database that are not enforced. The method includes a computer receiving an original file of a plurality of Data Definition Language (DDL) statements for creating a database. The method further includes the computer identifying one or more DDL statements from the plurality of DDL statements that comprise text indicating the one or more DDL statements are referential constraints that are not to be enforced in the database, and in response, the computer converting the one or more DDL statements to one or more DDL create view statements, respectively. The method further includes the computer generating a first file comprising the one or more DDL create view statements, wherein the one or more DDL create view statements are used to generate one or more database views, respectively, during the build of the database. The method further includes the computer generating a second file, comprising the plurality of DDL statements without the one or more DDL statements from the plurality of DDL statements that comprise the text indicating the one or more DDL statements are referential constraints not to be enforced in the database, used to build the database. The method further includes the computer, responsive to new data being loaded to the database, creating a report comprising data from the one or more database views.

DETAILED DESCRIPTION

Data that is uploaded to a target database from a source operational system may have referential constraints between tables in the source database that cannot be easily maintained in the target database. The referential constraints between tables are not examined or enforced in the target database when data is loaded. Users may have a need to know what data is not conforming to the referential constraints of the target database. The following discloses a method, system, and computer program product for reporting data loaded to a database that does not adhere to the referential constraints of the database that are not enforced.

FIG. 1depicts a diagram of computing system10in accordance with one embodiment of the present invention.FIG. 1provides only an illustration of one embodiment and does not imply any limitations with regard to the environments in which different embodiments may be implemented.

In the depicted embodiment, system10includes client system30, source system40, and target system50interconnected over network20. Network20may be a local area network (LAN), a wide area network (WAN) such as the Internet, or any combination of connections and protocols that will support communications between client system30, source system40, and target system50in accordance with embodiments of the invention. Network20may include wired, wireless, or fiber optic connections. System10may include additional target systems, client systems, or other devices not shown.

Client system30is a desktop computer. In other embodiments, client system30may be a laptop computer, tablet computer, personal digital assistant (PDA), or smart phone. In general, client system30may be any electronic device or computing system capable of sending and receiving data, and communicating with source system40and target system50. Client system30contains UI (User Interface)60.

Source system40and target system50are database servers. In other embodiments source system40and target system50may be management servers, web servers, or any other electronic devices or computing systems capable of receiving and sending data. In other embodiments, source system40and target system50may each represent a server computing system utilizing multiple computers as a server system, such as in a cloud computing environment. Source system40contains DBMS70and source database80. Target system50contains DBMS90, target database100, and reporting function110.

UI60operates on client system30to visualize content, such as menus and icons, and to allow a user to interact with the client system or an application. In one embodiment, UI60comprises an interface to a DBMS. UI60may display data received from DBMS90and send user input to DBMS70or90. In other embodiments, UI60may comprise one or more interfaces such as, an operating system interface and application interfaces.

DBMS70and90operate to control the creation, maintenance, and the use of source database80and target database100, respectively. DBMS70and90can be database management system programs such as IBM® DB2®. In other embodiments, DBMS70and90may be any other program or function that can control the creation, maintenance, and the use of source database80or target database100. In one embodiment, DBMS70resides on source system40and DBMS90resides on target system50. In other embodiments, DBMS70and90may reside on other computing systems or devices provided that the DBMS have access to source database80and target database100, respectively, have access to each other, and can communicate with UI60.

Source database80is a database that may be written and read by DBMS70. For example source database80may be a database such as an IBM® DB2® database or an Oracle® database. In one embodiment, source database80represents a source operational system that contains data to be uploaded to target database100. Source database80is located on source system40. In another embodiment, source database80may be located on another system or another computing device, provided that the source database is accessible to DBMS70.

Target database100is a database that may be written and read by DBMS90. For example target database100may be a database such as an IBM® DB2® database or an Oracle® database. In one embodiment, target database100is located on target system50. In another embodiment, target database100may be located on another system or another computing device, provided that the target database is accessible to DBMS90.

Reporting function110operates to report data loaded to a database that does not adhere to not enforced referential constraints of the database. Reporting function110will create database Views based on received referential constraints, not enforced in a target database, and data loaded to the target database that does not conform to the received referential constraints.

In one embodiment, database Views are stored queries accessible as virtual tables computed or collated from data in the database. A database View does not form part of the physical schema of a database. Database Views are created using SQL Create View statements. An example of an SQL Create View statement is discussed in greater detail with reference toFIG. 6.

In one embodiment, reporting function110receives, from UI60, a file of DDL statements that contains statements creating the structure of the target database and referential constraints specifically labeled so as to identify specific constraints that are not to be enforced in the target database. An example of a file of DDL statements is discussed in greater detail with reference toFIG. 3. Reporting function110generates a file of DDL Create View statements based on the referential constraints specifically labeled and a file of DDL statements that contains the original statements creating the structure of the target database with the referential constraints specifically labeled removed. The two files generated by reporting function110are executed to build target database100, from the file of DDL statements that contains the original statements creating the structure of the target database with the referential constraints specifically labeled removed, and database Views, from the file of DDL Create View statements. Responsive to data being loaded to target database100, reporting function110queries the database Views to create a report showing the data loaded to target database100that does not conform to the referential constraints that the DDL Create View Statements were based on.

In another embodiment, reporting function110reporting function110receives, from UI60, a file of DDL statements that contains statements creating the structure of the target database and referential constraints. In this embodiment, reporting function110generates a file of DDL Create View statements based on all referential constraints concerning foreign keys and a file of DDL statements that contains the original statements creating the structure of the target database with the referential constraints concerning foreign keys removed. The two files generated by reporting function110are executed to build target database100, from the file of DDL statements that contains the original statements creating the structure of the target database with the referential constraints concerning foreign keys removed, and database Views, from the file of DDL Create View statements. Responsive to data being loaded to target database100, reporting function110queries the database Views to create a report showing the data loaded to target database100not conforming to the referential constraints on which the DDL Create View Statements were based.

In one embodiment, reporting function110is a function of DBMS90on target system50. In other embodiments, reporting function110may be a separate program that resides on another system or another computing device, provided that the reporting function is able to access DBMS70and90, and communicate with UI60.

Client system30, source system40, and target system50, each maintain respective internal components800a,800b, and800c, and respective external components900a,900b, and900c. In general, client system30, source system40, and target system50can be any computing system as described in further detail with respect toFIG. 8.

FIG. 2depicts a flowchart of the steps of reporting function110executing within the computing system ofFIG. 1, for reporting data loaded to a database that does not adhere to not enforced referential constraints of the database, in accordance with one embodiment of the present invention.

In one embodiment, initially, a user (e.g., Database Administrator) at client system30will create an original file of DDL statements using a data definition subsystem (used to define the structure of the files in a database) of DBMS90or a separate data modeler program accessed through UI60. An exemplary original file of DDL statements, file300, is discussed in greater detail with reference toFIG. 3. The user also determines which referential constraints will be used to generate DDL Create View statements and adds those referential constraints to the original file of DDL statements as NO_RI (NO Referential Integrity) as shown inFIG. 3. UI60sends the original file of DDL statements to reporting function110, over network20. In another embodiment, UI60sends the original file of DLL statements to DBMS90, over network20.

In step200, reporting function110receives the original file of DDL statements from UI60. In another embodiment, reporting function110receives the original file of DDL statements from DBMS90if the original file of DDL statements was first sent to DBMS90from UI60.

In response to receiving the original file of DDL statements, reporting function110identifies referential constraints that will be used to generate DDL Create View statements (step210). In one embodiment, reporting function110searches the text of the received original file of DDL statements for referential constraints labeled NO_RI, the referential constraints labeled NO_RI will be used to generate DDL Create View statements. In another embodiment, reporting function110searches the text of the received original file of DDL statements for all referential constraints concerning foreign keys, the referential constraints concerning foreign keys will be used to generate DDL Create View statements.

In step220, reporting function110generates a file of DDL Create View statements based on the referential constraints identified in step210. In one embodiment, reporting function110converts the referential constraints identified in step210to DDL Create View statements as shown inFIG. 6. In step230, reporting function110generates a file of DDL statements that contains the original statements creating the structure of the target database with the referential constraints identified in step210removed. Exemplary program code for executing steps210through230of reporting function110is shown inFIGS. 5A-5C, in accordance with one embodiment of the present invention.

In one embodiment, reporting function110sends the file of DDL Create View statements and the file of DDL statements that contains the original statements creating the structure of the target database with the referential constraints identified removed to DBMS90. DBMS90executes the received files to build target database100and the database Views. In another embodiment, reporting function110executes the files to build target database100and the database Views, directly.

In one embodiment, after target database100and the database Views are built, data from source database80is loaded to the target database. In one embodiment, a function of DBMS90will call DBMS70, over network20, to fetch data to be loaded to target database100. After the data is loaded, the function of DBMS90will send an indication that the data has been loaded to reporting function110. In other embodiments, any program that can communicate with DBMS70and90, including reporting function110, can be programmed to retrieve data from source database80and transfer the data to target database100. After the data is loaded, the program which loads the data to target database100will send an indication that data was loaded to the target database to reporting function110.

Responsive to the data being loaded to target database100, reporting function110queries the database Views to create a report showing the data loaded to target database100not conforming to the referential constraints on which the DDL Create View Statements were based (step240). In one embodiment, reporting function110queries the database Views in target database110using an SQL query. Using the results of the query, reporting function110creates a report showing the data in each database View. The report may be stored on target system50or on any other system as long as the report is accessible to a user (e.g., database administrator). The report may be in the form of a file containing a table showing the data in each database View or any other form of information that can show the data loaded to target database100not conforming to the referential constraints on which the DDL Create View Statements were based.

FIG. 3is an exemplary original file of DDL statements received by reporting function110, in step200, in accordance with one embodiment of the present invention. Original file of DDL statements300is created by the user (e.g., Database Administrator) at client system30using a data definition subsystem of DBMS90or a separate data modeler program accessed through UI60. Original file of DDL statements300contains database structure statements310and NO_RI constraint320. Database structure statements310are DDL statements that create, manage, and alter the structure of the target database. NO_RI constraint320is a referential constraint that a user determined will be used to generate a DDL Create View statement.

Database structure statements310are representative of statements contained in the file of DDL statements generated by reporting function110in step230. Database structure statements310may create database tables400ofFIG. 4when executed to build target database100. Create DDL statements are used to create objects such as tables in a database. Alter DDL statements may modify an existing structure (e.g., adding a column) or add a constraint. Database statement330is a DDL statement that creates employee table430and adds a constraint making Employee_ID a primary key. Database statement340is a DDL statement that creates department table440(seeFIG. 4) and adds a constraint making Dept_ID a primary key.

NO_RI constraint320is representative of a DDL statement used to generate a DDL Create View statement. NO_RI constraint320is labeled as NO_RI so as to allow reporting function110to recognize that this referential constraint will be used to generate a DDL Create View statement. Reporting function110generates a file of a DDL Create View statement by converting NO_RI constraint320into DDL Create View statement600ofFIG. 6.

FIGS. 5A-5Cdepict exemplary program code for executing steps210through230of reporting function110in accordance with one embodiment of the present invention. Code500is an example of one implementation of steps210through230of reporting function110. Code500as depicted is written as an AIX Korn Shell Script. In other embodiments, other programming languages may be used. The other programming languages may be, for example, object-oriented and/or procedural programming languages.

FIG. 6depicts an exemplary DDL Create View statement contained in a file of DDL Create View statements generated by reporting function110in accordance with one embodiment of the present invention. Reporting function110generates a file containing DDL Create View statement600by converting NO_RI constraint320in to a DDL Create View statement.

If NO_RI constraint320was implemented in the build of target database100a constraint added to employee table430would designate Dept_ID as a foreign key value referencing department table440which has Dept_ID designated as a primary key. Dept_ID values in employee table430would be required to match a Dept_ID value in department table440. DDL Create View statement600creates a database View showing data from employee table430that has a Dept_ID value that does not match a Dept_ID value in department table440.

FIGS. 7A-7Cdepict exemplary tables of target database100that contain data loaded from source database80, and an exemplary database View containing data that does not adhere to a not enforced referential constraint of target database100.

FIGS. 7A and 7Bdepict employee table730and department table740, respectively. DDL Create View statement600creates a database View showing data from employee table730that has a Dept_ID value that does not match a Dept_ID value in department table740. Each employee entry in employee table730lists a Dept_ID. That Dept_ID in employee table730must match a Dept_ID listed in Dept_ID column760of department table740. Dept_ID750(Z222) does not match a Dept_ID listed in Dept_ID column760of department table740(D110, D120, or X200).

The database View created by DDL Create View statement600will contain the information in the row of the employee whose Dept_ID value does not match a Dept_ID listed in Dept_ID column760of department table740. In this example, the information of employee Allisa Carey will be contained in the database View as shown inFIG. 7C.FIG. 7Cdepicts information that will be contained in the database View created by DDL Create View statement600.

FIG. 8is a block diagram of internal and external components of client system30, source system40, and target system50in accordance with one embodiment of the present invention.

Client system30, source system40, and target system50include respective sets of internal components800a,b,cand external components900a,b,c. Each of the sets of internal components800a,b,cincludes one or more processors820, one or more computer-readable RAMs822and one or more computer-readable ROMs824on one or more buses826, and one or more operating systems828and one or more computer-readable tangible storage devices830. The one or more operating systems828and UI60are stored on at least one of one or more of computer-readable tangible storage devices830of internal components800afor execution by at least one of one or more of processors820of internal components800avia at least one of one or more of RAMs822of internal components800a(which typically include cache memory). The one or more operating systems828, DBMS70, and source database80are stored on at least one of one or more of computer-readable tangible storage devices830of internal components800bfor execution by at least one of one or more of processors820of internal components800bvia at least one of one or more of RAMs822of internal components800b(which typically include cache memory). The one or more operating systems828, DBMS90, target database100, and reporting function110are stored on at least one of one or more of computer-readable tangible storage devices830of internal components800cfor execution by at least one of one or more of processors820of internal components800cvia at least one of one or more of RAMs822of internal components800c(which typically include cache memory). In the embodiment illustrated inFIG. 8, each of the computer-readable tangible storage devices830is a magnetic disk storage device of an internal hard drive. Alternatively, each of the computer-readable tangible storage devices830is a semiconductor storage device such as ROM824, EPROM, flash memory or any other computer-readable tangible storage device that can store a computer program and digital information.

Each set of internal components800a,b,calso includes a R/W drive or interface832to read from and write to one or more portable computer-readable tangible storage devices936such as a CD-ROM, DVD, memory stick, magnetic tape, magnetic disk, optical disk or semiconductor storage device. UI60can be stored on at least one of one or more of portable computer-readable tangible storage devices936of external components900a, read via R/W drive or interface832of internal components800aand loaded into at least one of computer-readable tangible storage devices830of internal components800a. DBMS70and source database80can be stored on at least one of one or more of portable computer-readable tangible storage devices936of external components900b, read via R/W drive or interface832of internal components800band loaded into at least one of computer-readable tangible storage devices830of internal components800b. DBMS90, target database100, and reporting function110can be stored on at least one of one or more of portable computer-readable tangible storage devices936of external components900c, read via R/W drive or interface832of internal components800cand loaded into at least one of computer-readable tangible storage devices830of internal components800c.

Each set of internal components800a,b,calso includes a network adapter or interface836such as a TCP/IP adapter card. UI60can be downloaded to client system30from an external computer via a network (such as network20) and network adapter or interface836of internal components800a. From the network adapter or interface836of internal components800a, UI60is loaded into at least one of computer-readable tangible storage devices830of internal components800a. DBMS70and source database80can be downloaded to source system40from an external computer via a network (such as network20) and network adapter or interface836of internal components800b. From the network adapter or interface836of internal components800b, DBMS70and source database80are loaded into at least one of computer-readable tangible storage devices830of internal components800b. DBMS90, target database100, and reporting function110can be downloaded to target system50from an external computer via a network (such as network20) and network adapter or interface836of internal components800c. From the network adapter or interface836of internal components800c, DBMS90, target database100, and reporting function110are loaded into computer-readable tangible storage devices830of internal components800c. The network may comprise copper wires, optical fibers, wireless transmission, routers, firewalls, switches, gateway computers and/or edge servers.

Each of the sets of external components900a,b,ccan include a computer display monitor920, a keyboard930, and a computer mouse934. External components900a,b,ccan also include touch screens, virtual keyboards, touch pads, pointing devices, and other human interface devices. Each of the sets of internal components800a,b,calso includes device drivers840to interface to computer display monitor920, keyboard930and computer mouse934. Device drivers840, R/W drive or interface832and network adapter or interface836comprise hardware and software (stored in storage device830and/or ROM824).

UI60, DBMS70, DBMS90, and reporting function110can be written in various programming languages (such as Java, C++) including low-level, high-level, object-oriented or non object-oriented languages. Alternatively, the functions of UI60, DBMS70, DBMS90, and reporting function110can be implemented in whole or in part by computer circuits and other hardware (not shown).

Based on the foregoing, a computer system, a method and a program product have been disclosed for reporting data loaded to a database that does not adhere to the referential constraints of the database that are not enforced. The description above has been presented for illustration purposes only. It is not intended to be an exhaustive description of the possible embodiments. One of ordinary skill in the art will understand that other combinations and embodiments are possible. Therefore, the present invention has been disclosed by way of example and not limitation.