JP6244521B2 - Database processing program, database processing method, and database processing apparatus - Google Patents

Description

  The present invention relates to a database processing program, a database processing method, and a database processing apparatus that realize visualization of a schema in a database and simplification of design and construction.

  As a type of database (hereinafter referred to as DB), relational DB has become the mainstream because of its ease of handling. Moreover, in a DB that has been enlarged by adding data over many years, it is often very difficult to define and change a new schema. Even if it is an attribute agreed by the designers of multiple DBs, different attribute names are often defined, and when it is necessary to integrate multiple DBs constructed by different designers, a tremendous amount of labor is required. It becomes.

  It goes without saying that the relationship between attributes (data, values) is not appropriate if only the relational schema is described. In the hierarchical DB, the parent-child (branch and leaf) relationship between attributes can be clarified, but it is redundant to describe a complicated relationship such as a subordinate (branch and leaf) relationship between children. In the network type DB, even if it is the data between the above-mentioned children, a complicated relationship such as the existence of a dependency relationship can be clarified, but it is difficult to change when a new schema is added or changed.

  Various ideas have been conventionally used for DB visualization (Patent Document 1, etc.), but ER (entity-relationship) diagrams are still frequently used. In the ER diagram, the importance of each attribute and the relationship between the attributes are not easy to understand, and it is difficult for a non-technical person to grasp intuitively.

JP 2014-029597 A

  Even if an engineer who has database expertise is involved in designing and constructing a DB, it is very difficult to optimize a schema in a DB including a large amount of data. For users who do not have specialized knowledge, the design and construction of DBs are far from difficult.

The present invention has been made in view of such circumstances, and enables a user who does not have expertise in the DB itself and the technology for handling the DB to intuitively grasp the DB schema. It is an object to provide a database processing program, a database processing method, and a database processing apparatus.

  The database processing program according to the present invention is a database processing program in which a computer that communicates with a database executes processing on the database. The computer includes an analysis step that analyzes the relation of the database, and a plurality of relations included in the analyzed relation. A step of associating an object with each attribute, and a drawing step of arranging and drawing the associated object so as to follow an outer shape of a predetermined shape are executed.

  The database processing program according to the present invention receives a selection and placement operation for the drawn object, and accepts the placement of the object according to the operation in the computer, and performs relations of the database according to the accepted placement. The step of changing is further executed.

  In the database processing program according to the present invention, when the drawing step is analyzed that the database is divided into a plurality of relations by the analysis, predetermined shapes corresponding to the number of divisions of the relations are adjacent to each other, and the relations are adjacent to adjacent parts. An object corresponding to an attribute common to the plurality of relations is arranged, and an object having an attribute belonging to each of the plurality of relations is arranged on an outline other than the adjacent portion and is drawn.

  In the database processing program according to the present invention, the analysis step includes a step of specifying a hierarchical relationship between the plurality of relations, and the drawing step draws the upper relation so as to be arranged in the center of the screen. It is characterized by.

  In the database processing program according to the present invention, the size at which the predetermined shape in which an object corresponding to an attribute belonging to each of the plurality of relations is arranged is drawn is the database of attribute values of attributes belonging to the relation It is characterized by showing the magnitude of the variation in the inside.

  In the database processing program according to the present invention, the analysis step includes a step of decomposing the database for each attribute, a step for calculating the size of variation in the attribute value of the attribute for each attribute, and the calculated size A step of assigning ranks to the attributes based on the steps, a step of extracting candidate keys based on the assigned ranks, a step of identifying functional dependencies between decomposed attributes based on the candidate keys, and attributes based on relational dependencies The method includes a step of creating a decision tree in between, a step of dividing into a plurality of relations based on the created decision tree, and a step of creating a hierarchical network based on the decision tree.

  In the database processing program according to the present invention, the analyzing step further includes a step of generating a complex network in which attributes that are not directly in a parent-child relationship are linked in the hierarchical network.

  The database processing method according to the present invention is a database processing method by a computer that executes processing on a database. The computer analyzes a relation of the database and associates objects with a plurality of attributes included in the analyzed relation. The screen information is created by arranging and drawing the associated objects so as to follow the outer shape of a predetermined shape.

  In the database processing method according to the present invention, the computer decomposes the database for each attribute, calculates for each attribute, the step of calculating the degree of variation in the attribute value of the attribute in the database, A step of assigning ranks to the attributes based on the steps, a step of extracting candidate keys based on the given ranks, a step of identifying functional dependencies between decomposed attributes based on the candidate keys, and between attributes based on relational dependencies Analyzing the database by executing a step of creating a decision tree, a step of dividing into a plurality of relations based on the created decision tree, and a step of creating a hierarchical network based on the decision tree To do.

  The database processing apparatus according to the present invention includes means for communicating with each of the database and the external apparatus, and the database processing apparatus for transmitting the processing result in the database to the external apparatus is analyzed with the analyzing means for analyzing the relation of the database. Means for associating objects with a plurality of attributes included in the relation, and creating screen information in which the associated objects are arranged and drawn along a contour of a predetermined shape. To do.

  In the database processing apparatus according to the present invention, the analyzing means is a means for decomposing the database for each attribute, a means for calculating the degree of variation in the attribute value of the attribute for each attribute, and the calculated magnitude A means for assigning a rank to the attribute based on the above, a means for extracting a candidate key based on the assigned rank, a means for specifying a functional dependency between the decomposed attributes based on the candidate key, and an attribute based on the relation dependency It is characterized by having means for creating a decision tree in between, means for dividing into a plurality of relations based on the created decision tree, and means for creating a hierarchical network based on the decision tree.

  In the present invention, the schema is drawn so that objects corresponding to a plurality of attributes in the DB are arranged along the outline of a predetermined figure for each relation (table) to which the attribute belongs.

  In the present invention, an operation for changing the arrangement of each drawn object on the screen can be received, and the actual database relation is changed according to the arrangement information of the object after the operation.

  In the present invention, when the database is divided into a plurality of relations, an object corresponding to an attribute overlapping in the plurality of relations is arranged at the contact point, and arranged so as to follow a predetermined figure for each of the plurality of relations. And the schema is drawn.

  In the present invention, when the relation is divided into a plurality of relations, the hierarchical relationship is specified, and the object corresponding to the higher relation is arranged at the center and drawn.

  In the present invention, when divided into a plurality of relations, the difference in size of the outline in which the object corresponding to the attribute is arranged for each relation is indicated based on the difference in the attribute value of the attribute in each relation. . A relation includes a large number of candidate keys, and the possibility of being connected to another relation on the outside is higher, so that the relation is drawn in a larger shape.

  In the present invention, a relational schema for data that is the basis of a DB is automatically optimized and constructed from a hierarchical network created based on a decision tree based on functional dependencies between attributes. As a result, a schema complete with the third normalization is constructed.

  In the present invention, a more complex network is generated from the hierarchical network, and a schema is constructed based on the complex network.

  According to the present invention, the relation schema for the relation data that is the source of the DB is analyzed, and an object corresponding to each attribute is arranged along the outline of a predetermined figure for each table to which the attribute belongs. Visualized. Thereby, even the user who does not have knowledge about access to the DB can intuitively grasp the analyzed DB schema.

  Furthermore, by accepting an operation for changing the arrangement of objects, the user can also change the schema in the database by an intuitive operation of moving the object on the screen. Thereby, even a user who does not have expertise in DB and the technology for handling the DB can easily design and construct the DB.

  Further, according to the present invention, the analysis of the relation schema is automatically automatically generated appropriately and optimized and constructed by normalization and further denormalization.

1 is a block diagram illustrating a configuration of an information processing system in a first embodiment. 3 is a block diagram illustrating an internal configuration of a DB processing device, a terminal device, and a DBIF providing device according to Embodiment 1. FIG. 6 is an explanatory diagram illustrating an example of an interface displayed on a browser screen of the terminal device according to the first embodiment. FIG. It is a flowchart which shows an example of the process sequence performed with DB processing apparatus. It is explanatory drawing which shows the example of the content of the data (table) transmitted from a terminal device. It is explanatory drawing which shows the example of the content of a table definition reception screen. It is explanatory drawing which shows the example of the content of a decision tree. It is explanatory drawing which shows the example of the content of a hierarchical network structure. It is explanatory drawing which shows the outline | summary of complex network production | generation. It is explanatory drawing which shows the example of the content of a complex network structure. It is explanatory drawing which shows the example of a schema drawing. It is explanatory drawing which shows the example of a drawing after the change of a schema. It is explanatory drawing which shows DB constructed | assembled 3 is a block diagram illustrating a configuration of an information processing system in Embodiment 2. FIG. 12 is a flowchart illustrating an example of a processing procedure executed by the DB processing apparatus according to the second embodiment.

  Hereinafter, the present invention will be specifically described with reference to the drawings illustrating embodiments thereof. In addition, the embodiment shown below is an illustration, and of course, the present invention is not limited to the following configuration.

(Embodiment 1)
FIG. 1 is a block diagram illustrating a configuration of an information processing system according to the first embodiment. The information processing system in the first embodiment includes a DB processing device 1, a plurality of terminal devices 2, a DB interface providing device (hereinafter referred to as a DBIF providing device) 3, and a DB group 4. The DB processing device 1 is connected to the DB group 4 and the DBIF providing device 3 directly or via a network N including the Internet. The DBIF providing device 3 is connected to the network N and can communicate with a plurality of terminal devices 2 connected to the network N as well.

  The DB group 4 stores various data converted into a DB. The DB group 4 may be composed of a plurality of DBs connected via a network, and includes a management DB in which information indicating the relationship between the DBs and between the tables in the DBs is recorded.

  The DB processing apparatus 1 is an apparatus that automatically analyzes and models the data definition in the DB group 4 and optimizes the data schema.

  The DBIF providing device 3 is a server device that provides the terminal device 2 with an interface to the DB group 4 on a Web basis. The DBIF providing device 3 receives an operation instruction for the DB group 4 from the terminal device 2 and transmits it to the DB processing device 1, and transmits a screen showing the processing result in the DB processing device 1 to the terminal device 2.

  The terminal device 2 is a personal computer (PC) used by a user. As long as the terminal device 2 can execute a Web browser program, various information terminals such as a desktop PC, a notebook PC (laptop PC), a tablet terminal, or a smartphone may be used.

  FIG. 2 is a block diagram showing an internal configuration of the DB processing device 1, the terminal device 2, and the DBIF providing device 3 according to the first embodiment. The DB processing apparatus 1 includes a control unit 10, a storage unit 11, a temporary storage unit 12, and a communication unit 13.

  The control unit 10 uses a CPU (Central Processing Unit). The control unit 10 reads out and executes the DB modeling program 1P stored in the storage unit 11, thereby exhibiting a modeling processing function for the DB group 4. The temporary storage unit 12 uses a RAM such as a DRAM (Dynamic Random Access Memory). The temporary storage unit 12 temporarily stores information generated by the processing of the control unit 10.

  The storage unit 11 uses a nonvolatile storage medium such as a hard disk. The storage unit 11 stores the above-described DB modeling program 1P.

  The communication unit 13 is connected to be able to communicate with the DBIF providing device 3 and the DB group 4.

  The terminal device 2 includes a control unit 20, a storage unit 21, a temporary storage unit 22, a display unit 23, an operation unit 24, and a communication unit 25.

  The control unit 20 uses a CPU. The control unit 20 causes the terminal device 2 to exhibit various functions by reading and executing various programs stored in the storage unit 21. The temporary storage unit 22 uses a RAM such as a DRAM. The temporary storage unit 22 temporarily stores information generated by the processing of the control unit 20.

  The storage unit 21 uses a nonvolatile storage medium such as a hard disk or a flash memory. The storage unit 21 stores a browser program 211. The browser program 211 receives the HTML-based Web page transmitted from the DBIF providing apparatus 3 and displays on the control unit 20 a Web browser function that causes the display unit 23 to display characters, images, or moving images included in the Web page. Let

  As the display unit 23, for example, a liquid crystal display is used. Of course, other types of displays may be used. The control unit 20 causes the display unit 23 to display various screens including characters or images. The operation unit 24 uses an input interface such as a keyboard and a pointing device. The control unit 20 controls each component based on operation information input through the input interface. The display unit 23 and the operation unit 24 may be integrally configured as a touch panel built-in display.

  The communication unit 25 is connected to a network N including the Internet, and realizes transmission and reception of information with other devices via the network N. The control unit 20 transmits / receives an operation screen transmitted from the DBIF providing apparatus 3 and a Web page including a screen showing a processing result through the communication unit 25.

  The DBIF providing apparatus 3 includes a control unit 30, a storage unit 31, a temporary storage unit 32, and a communication unit 33.

  The control unit 30 reads out and executes the Web server program and the DBIF providing program stored in the storage unit 31 using the CPU, thereby exhibiting the Web server function and the access interface function for the DB group 4. Specifically, the control unit 30 provides a Web page for exhibiting an access interface function to the DB group 4 to the plurality of terminal devices 2 via the network N. The temporary storage unit 32 uses a RAM such as a DRAM and temporarily stores information generated by the processing of the control unit 30.

  The storage unit 31 uses a non-volatile storage medium such as a hard disk. The storage unit 31 stores the DBIF providing program described above. The storage unit 31 stores resources (sources describing still pages in various languages such as HTML, still images, etc.) that constitute an access interface provided to the terminal device 2.

  The communication unit 33 is connected to the network N, and realizes information transmission / reception with the terminal device 2 via the network N. The communication unit 33 is connected to be able to communicate with the DB processing apparatus 1.

  In the information processing system configured as described above, a Web page for performing the access interface function provided from the DBIF providing device 3 is displayed by the browser function of the terminal device 2. Depending on the operation contents on the browser screen displayed on the terminal device 2, the DBIF providing device 3 receives access to the DB group 4, and the DB processing device 1 executes processing according to the access, and shows the processing result. The screen is transmitted from the DBIF providing device 3 to the terminal device 2.

  First, the interface information transmitted from the DBIF providing apparatus 1 will be specifically described with reference to a screen example on the browser displayed by the interface information. FIG. 3 is an explanatory diagram illustrating an example of the interface 231 displayed on the browser screen of the terminal device 2 according to the first embodiment. The top page when the user logs in to the Web service provided by the business entity that manages the DBIF providing device 3 using the terminal device 2 is the DB and account to which the logged in account (user) has access authority. A list of local data is displayed for selection.

  As shown in FIG. 3, the interface 231 includes buttons corresponding to new operations for creating new local data, copying, deleting, and uploading to the DB group 4. In the example of FIG. 3, sales data (table) for September 2015 is displayed so as to be selectable as local data regarding data related to business management. When the user performs an operation of uploading sales data for September 2015, the local data from the terminal device 2 via the DBIF providing device 1 is instructed to add to the user DB in the DB group 4 (SQL To the DB processing device 1. Processing executed in the DB processing apparatus 1 at this time will be described with reference to a flowchart and an explanatory diagram.

  FIG. 4 is a flowchart illustrating an example of a processing procedure executed by the DB processing apparatus 1. The control unit 10 of the DB processing apparatus 1 executes the following process based on the DB processing program 1P stored in the storage unit 11.

  The control unit 10 of the DB processing apparatus 1 inputs a relation (table) to be newly added to the DB group 4 by the communication unit 13 (step S101). The control unit 10 decomposes each attribute (row) of the input table (step S102).

  The control unit 10 calculates the variation of the attribute value (numerical value or character string or the like) as entropy for each decomposed attribute (step S103). The variation corresponds to the small number of times of duplication within the attribute (row).

  The control unit 10 assigns a rank in order of the entropy size of each decomposed attribute (step S104), and extracts the attribute having the largest entropy (most variation) (step S105).

  The control unit 10 further extracts, as candidate keys, the attributes extracted in step S105 that do not violate the restrictions as keys (step S106). Note that the constraint is a field length (value) or the like that is not NULL.

  The control unit 10 calculates a functional dependency between the decomposed attributes (step S107). Specifically, in step S107, the control unit 10 determines the relationship between the candidate key extracted in step S106 and other attributes (non-key attributes) uniquely determined by the candidate key (s). Specify for each set of attributes. In addition, at this time, the control unit 10 preferentially specifies the functional dependency between an attribute with a large variation (highly likely to be a candidate key) and an attribute with a small variation (highly likely a non-key attribute). To do.

  The control unit 10 creates a decision tree between the attributes in the table input in step S101 based on the function dependency obtained in step S107 (step S108). Thereby, the hierarchy between the primary key and the attribute is determined.

  The control unit 10 divides the table into a plurality of tables based on the created decision tree (step S109). Specifically, in step S109, the control unit 10 extracts each relationship as a bipartite graph (n-part graph) based on the decision tree created in step 108, obtains a maximum matching for each extracted bipartite graph, Perform topological sorting using maximum matching to divide into multiple tables (Dulmage-Mendelsohn decomposition). In step S109, the input table is subjected to the third normalization as a relational DB.

  The control unit 10 creates a hierarchical network structure from the table divided in step S109 (step S110). Further, the control unit 10 generates a complex network from the hierarchical network (step S111).

  In step S111, the control unit 10 calculates an average inter-vertex distance when each attribute is a vertex in the hierarchical network. Then, links are generated between the vertices (attributes) so that the average distance between vertices in the entire hierarchical network (all relations) becomes small. However, at this time, in the hierarchical network, a link is made between adjacent hierarchies (not skipping one hierarchy), and a vertex (hub) with many outgoing links, that is, a constraint that attributes that are foreign keys are not connected. Create a link below. That is, the control unit 10 generates a link from a certain vertex to another attribute (non-hub attribute) whose distance from the vertex is equal to or greater than the calculated average inter-vertex distance and subordinate to the hub. . Specifically, the control unit 10 extracts a list between attributes that are not connected and satisfy the constraint condition, and calculates the average distance between the vertices and the sum of the outgoing links when connected for each list. . The control unit 10 identifies and connects attribute pairs whose average vertex distance is the smallest in the list and whose total number of outgoing links does not increase. In step S111, the normalization completed in step S109 is partly broken, and a DB having high small world characteristics and low scale-free characteristics can be automatically created. Since the small-world property is high and the scale-free property is low, it is possible to reduce the join operation between the tables and avoid the deterioration of the processing performance, and it is an excellent DB that does not cause any abnormality when updating the DB.

  The control unit 10 draws the schema of the input table based on the complex network generated in step S111 (step S112). The control unit 10 transmits the information obtained by drawing to the terminal device 2 via the DBIF providing device 3 (step S113).

  In step S112, the control unit 10 arranges the object corresponding to the attribute of the primary key (the most upstream) in the above-described hierarchical network structure in the center of the drawing range, and other attributes in the same hierarchy as the object corresponding to the attribute. The object corresponding to is arranged in a circle and drawn. At this time, the radius of the circle is set based on the sum of entropy (variation) of attributes included in the table. The control unit 10 similarly arranges the objects corresponding to the attributes belonging to the table in a circular shape for each decomposed table in the same manner for the objects corresponding to the attributes of the lower hierarchy toward the outside of the drawing range. The average vertex distance calculated in step S111 may be displayed. The object is a card type, and the control unit 10 displays a screen showing the above-described schema so that the object can be selected and moved based on Javascript (registered trademark) on the display unit 23 of the terminal device 2. Create information.

  Further, the arrangement of the objects is not limited to a circular shape, and it is sufficient that the total entropy can be expressed by the size of an ellipse as well as an ellipse. The larger the arranged shape (for example, a circle), the more candidate keys are included in the table, indicating that there is a possibility that a circle corresponding to another table may be connected to the outside.

  Further, the control unit 10 receives an operation on the screen indicating the schema drawn on the display unit 23 of the terminal device 2, and receives information indicating the received operation content via the DBIF providing device 3 (step S114). The control unit 10 constructs a DB in the DB group 4 based on the table input in step S101 based on the functional dependency, complex network structure, etc. obtained by the processing in steps S107 to S110 according to the accepted operation. (Step S115), and the process ends.

  The processing procedure shown in the flowchart of FIG. 4 will be described with a specific example. FIG. 5 is an explanatory diagram showing a content example of data (table) transmitted from the terminal device 2. The example shown in FIG. 5 is the sales data shown in FIG. 3. The sales data (record) order date, customer number, customer name, prefecture, address, customer type, product number, category name, product name, Actual values such as the names of employees in charge of sales are tabulated. The sales data includes the unit price, number, and amount omitted in FIG. Furthermore, the sales data is added to the business data in the DB to which the user has access authority. The business data further includes a relation (table) for the employee including an employee name, height, weight, date of birth, e-mail, year of employment, salary information, and department information as attributes.

  When the upload operation of the table shown in FIG. 5 is performed by the terminal device 2, the control unit 10 of the DB processing device 1 inputs the table (S101), and adds each attribute (row) together with the business data of the addition destination. (S102). At this time, the control unit 10 transmits screen information of a screen for receiving a table definition of a table to be newly input from the user to the terminal device 2 via the DBIF providing device 3. FIG. 6 is an explanatory diagram showing an example of the contents of the table definition acceptance screen. As shown in FIG. 6, the control unit 10 identifies the first character string of each row of the uploaded table as an attribute name, decomposes the attribute value type (numeric value / character string), length, and empty (NULL). ) Can be selected.

  In addition to the information received on the table definition reception screen shown in FIG. 6, other reference information for specifying the relationship of attributes of the uploaded table may be received. For example, when a plurality of tables are uploaded, the DB processing device 1 may accept selection of presence / absence of a relationship between the tables at the terminal device 2 via the DB providing device 3. Also, as shown in FIG. 6, the designation of the primary key may be received from among the plurality of decomposed attributes, or an instruction to intentionally add an ID attribute corresponding to the attribute may be accepted. .

  The control unit 10 of the DB processing apparatus 1 extracts candidate key attributes from a plurality of attributes (S106). At this time, the control unit 10 automatically adds an ID attribute for an attribute that requires an ID attribute (when necessary to make a record unique) for an attribute with high entropy. For example, if the attributes of the input sales data are order date, customer number, name, prefecture, address, customer type, product number, category name, product name, unit price, quantity, amount, employee name, multiple products A record ID number is first normalized and a sales ID attribute is added. The sales ID attribute has the largest variation. Similarly, a customer ID, a product ID, and an employee ID are added automatically or by an instruction from the user. The control unit 10 calculates the functional dependency after adding the ID attribute (S107), and creates a decision tree (S108). In calculating the functional dependency, the control unit 10 determines whether or not an attribute value in another attribute is uniquely determined by the attribute value in the one attribute value for one attribute having high entropy. Identified as function dependent. That is, when the attribute values in the one attribute are the same, the relationship in which the attribute values in the other attributes are the same attribute value is specified.

  FIG. 7 is an explanatory diagram showing an example of the contents of a decision tree. Based on the functional dependency, the tree structure as shown in FIG. 7 is specified with the sales attribute being the attribute having the largest variation as the most upstream.

  Next, the control unit 10 divides the table from the decision tree shown in FIG. 7 in step S109 to create a hierarchical network structure. FIG. 8 is an explanatory diagram showing an example of the contents of a hierarchical network structure. As shown in FIG. 8, on the basis of the decision tree, attributes of the same hierarchy including the most upstream sales ID, including the number, customer ID, order date, product ID, and employee ID are divided as one table. Similarly, the attributes of the same hierarchy including the customer ID belonging to the above-mentioned table, including the address, prefecture ID, customer type ID, (customer) name, and customer number are divided as one table. . Further, the table including the sales ID includes the product ID belonging to the table, and the attributes of the same hierarchy including the product name, the product number, the product category ID, and the unit price are divided as one table. In addition, the employee IDs belonging to the table including the sales IDs are included, and the attributes in the same hierarchy including the employee name, height, weight, e-mail, year of employment, birthday, blood type, affiliation ID, and salary ID Exists as an existing table. The same table is grouped as shown by the broken line. Thus, the related tables are processed (updated) together.

  Further, the control unit 10 generates a complex network from the hierarchical network (S111). FIG. 9 is an explanatory diagram showing an outline of complex network generation. When a hierarchical network structure as shown in FIG. 9 exists, first, an average vertex distance is calculated. In the example of FIG. 9, the average distance between vertices is 1 between the vertices B, C, and D from the primary key A to each of the vertices B, C, and D in the same table. , E, F, and G are all 2 through the keys, and the distance between the vertices A to K is 3. The average distance between vertices is obtained by calculating the average between these vertices and calculating the average. The control unit 10 extracts unconnected combinations among the combinations between the vertices, and extracts combinations that are not included in the same hierarchy and are not separated by two or more hierarchies. For example, the combination of vertex B and vertex C is included in the same hierarchy (table), and the combination of vertex E and vertex I is not extracted because the hierarchy is separated by two. Then, a combination that connects the vertices (other than the vertices C and G) that are larger than the average vertex distance and that are not hubs is extracted. In the example of FIG. 9, for example, a combination of one of the vertices B, C, and E with the vertex F or the vertex H is extracted. Among the extracted combinations, vertices of combinations that satisfy a predetermined criterion are connected by a dependency relationship (link). The predetermined standard is the distance between vertices, higher hierarchy or lower hierarchy, or the same attribute value type (character string, numerical value) at the vertex (attribute). For example, if the extracted combination includes vertices included in a higher hierarchy in a pair and the attribute value of the attribute corresponding to the vertex is a numerical value, it is likely to be used for a high-frequency operation. For example, a dependency relationship (link) is established. As a result, a new link as shown in FIG. 9 is connected.

  FIG. 10 is an explanatory diagram showing an example of the contents of a complex network structure. The content example of the complex network structure shown in FIG. 10 is generated from the hierarchical network of FIG. As shown in FIG. 10, “unit price” is preferentially connected as a non-hub vertex (attribute) that is a non-hub of an adjacent hierarchy from among unconnected combinations. As a result, the unit price and the number are included in the same table, and it is expected that the processing load due to the join operation of the tables is reduced.

  Then, a relation schema is drawn based on the complicated network generated as shown in FIG. 10 (S112). FIG. 11 is an explanatory diagram illustrating a schema drawing example. When local data is uploaded through the interface 231 shown in FIG. 3, as shown in FIG. 11, a screen on which the schema of the integration destination DB of the uploaded data is drawn can be viewed by the function of “modeling deck”. The drawing information of these schemas is created by the control unit 10 of the DB processing apparatus 1 and transmitted to the terminal apparatus 2 via the DBIF providing apparatus 3. As shown in FIG. 11, in the schema drawing example according to the present embodiment, for each divided table in the complex network structure shown in FIG. 10, the attributes included in the table are circles as corresponding cards (objects) 232. Arranged in shape. The size of the circle 233 corresponding to each table corresponds to the total sum of entropy (variation) of the attributes to which the table belongs.

  In the schema drawing shown in FIG. 11, first, a table is displayed as a circle 233, and attributes are arranged on the circumference of the circle 233 by the card 232. In the example shown in FIG. 11, as can be seen from the hierarchical network structure of FIG. 8 (10), the primary key is “sales ID”, but the attributes (customer ID, order date, product ID) directly subordinate to the primary key. , Number, instructor ID) are drawn as centrally as possible on the “modeling deck” as a first dimension relation (table). Furthermore, the table corresponding to the circle 233 adjacent to the outside of the circle 233 corresponding to the first dimension table is the second dimension. The table corresponding to the circle 233 adjacent to the outer side of the circle 233 of the second dimension table is the third dimension, and the table corresponding to the circle 233 adjacent to the outer side is the fourth dimension. In the example of FIG. 11, the customer table, the product table, and the employee table are the second dimension, the prefecture table, the customer type table, the category table, the belonging table, and the salary table are the third dimension, and the department table that is located outside the belonging table is The fourth dimension.

  As shown in FIG. 11, the first dimension table and the second dimension table, the second dimension table and the third dimension table, and the third dimension table and the fourth dimension table are attributes that are foreign keys, respectively. Adjacent by sharing as a contact.

  Note that the card 233 corresponding to each attribute in FIG. 11 can be selected by the operation unit 24. When the user clicks on the card 232 by the operation unit 24, attribute information is displayed. That is, the control unit 10 of the DB processing apparatus 1 also associates information of each attribute and transmits it to the terminal apparatus 2 together with schema drawing information. Further, a circle 233 corresponding to each table can also be selected. When the user clicks on the circle 233 using the operation unit 24 for the circle 233, information on the corresponding table is displayed. Note that the name of each table may be automatically determined by the control unit 10 of the DB processing apparatus 1 using a word common to the attribute name. It is also possible to select the circle 233 above and specify the table information displayed at that time in an edit format.

  Each card 232 on the “modeling deck” shown in FIG. 11 is configured to accept selection on the screen of the display unit 23. The user can drag the card 232 with the operation unit 24 to move the position (hierarchy) in the table. The user can also grab and move the circle 233 by the operation unit 24 and integrate it with another circle 233.

  FIG. 12 is an explanatory diagram of a drawing example after the schema is changed. FIG. 12 shows a display example when the card 232 corresponding to the attribute of “prefecture name” in FIG. 11 is moved into the circle 233 corresponding to the “customer” table. By this operation, the attribute of “prefecture name” is also added to the “customer” table, and the table is denormalized by the operation of the user. Further, in the “modeling deck” shown in FIG. 9, it is possible to accept the arrangement (addition) of a new table by dragging and dropping the circular icon 234. The contents of the accepted operation are transmitted from the terminal device 2 to the DB processing device 1 via the DBIF providing device 3 in a language such as JSON and accepted (S114).

  Furthermore, an “OK” button 235 is displayed on the “modeling deck” shown in FIGS. With the “OK” button, a DB is constructed with the schema drawn on the “modeling deck” for the input table (S115).

  FIG. 13 is an explanatory diagram showing the constructed DB. As shown in FIG. 13, a relational DB is constructed. As shown in FIG. 13, after the third normalization was performed, a part of the network was further denormalized (shown in bold lines) due to the complicated networking and based on the user operation shown in FIG. 12. DB is constructed.

  In this way, the relational schema is automatically generated mechanically to the extent that the user performs an operation of uploading the table-format relation (table) created by the user to the DB processing apparatus 1 via the DBIF providing apparatus 3. Thereby, even if it is a user who does not have the technical knowledge with respect to DB and the technique for handling DB, it becomes possible to update DB easily.

  Further, the schema is visualized and drawn as shown in FIG. 11 by a method different from the conventional ER diagram. Thereby, it is possible to intuitively grasp the schema of the relation DB uploaded by itself. Thereby, even the user who does not have knowledge about access to the DB can intuitively grasp the schema of the automatically generated DB.

  In addition, since each attribute and table in the visualized schema is an object, and selection and movement operations are accepted, the user can intuitively move the object on the screen as shown in FIG. It is also possible to adjust the structure in the database by operation. Thereby, even if it is a user who does not have the technical knowledge with respect to DB and the technique for handling DB, it becomes possible to update DB easily.

  In the first embodiment, when a user performs an operation of uploading local data, the relation to be uploaded is analyzed. However, the analysis target is not limited to the upload target, but may be an existing DB already stored in the DB group 4. As a result, the schema of the existing DB is visualized, and the schema of the existing DB can be changed intuitively.

(Embodiment 2)
FIG. 14 is a block diagram illustrating a configuration of the information processing system according to the second embodiment. The configuration of the information processing system in the second embodiment is the same as the configuration in the first embodiment except that the DB processing device 1 performs processing referring to learning data for the DB group 4. Therefore, the same reference numerals are assigned to configurations common to those in Embodiment 1, and detailed description thereof is omitted. The DB group 4 in the second embodiment includes learning data 41 in addition to a management DB in which various types of data (actual data) converted into DBs, information indicating relationships between tables in the DBs, and tables in the DBs are recorded.

  The learning data 41 includes a synonym dictionary, a co-occurrence dictionary, and the like, and has dictionary data for DB attribute names in advance and a corpus including attribute names and attribute name relations (tables). For example, “sales”, “sales”, “order”, “sales”, and “Sales” are described as synonyms in the dictionary data. For example, the co-occurrence dictionary describes that “address”, “prefecture”, and “phone number” are attribute names existing in the same table. The corpus is a set of combinations of primary key and non-key attributes that are created in the past by using a table created by a designer or based on SQL learning as a sentence. For example, the first corpus including “order date / product name / quantity / prefecture name”, “order date / time / product number / quantity / unit price / amount”, “order number / product number / quantity / amount / address”, etc. , “Customer name / customer number / customer type”, “customer number / customer name / customer type / address”, “customer name / customer type / prefecture / age”, etc. Of corpus groups.

  Note that the corpus group of the learning data 41 may be sequentially aggregated and learned according to the addition of the DB from each user based on the initial data created in advance. The learning data 41 may be created for each user account or account group and learned in accordance with each user's thinking tendency.

  FIG. 15 is a flowchart illustrating an example of a processing procedure executed by the DB processing apparatus 1 according to the second embodiment. Of the processing procedures shown in FIG. 15, procedures common to the processing procedures shown in the flowchart of FIG. 4 of the first embodiment are denoted by the same step numbers, and detailed description thereof is omitted.

  The control unit 10 of the DB processing apparatus 1 extracts from the corpus group of learning data 41 a type of corpus that approximates the attribute in the normalized relation by dividing the table in S109 before generating a complex network in S111. (Step S1101). In step S1101, the control unit 10 first calculates the appearance frequency and inverse document frequency (TFIDF: Term Frequency, Inverse Document Frequency) in the relation, and the TFIDF for each corpus. Then, the control unit 10 matches (aligns) the attribute name in the relation and the attribute name in each corpus with the attribute name of consent by referring to the synonym dictionary data. Then, using a cosine distance (cosine similarity function), it is determined whether or not to approximate any corpus in the corpus group, and an approximate corpus is specified.

  In step S1101, for example, when one of the relations obtained by dividing from the user relation is “sales ID / order date / product ID / quantity / customer ID / employee ID”, the control unit 10 performs the above-described first operation. The corpus can be extracted as an approximate corpus.

  The control unit 10 extracts an attribute name that is a co-occurrence pair with the attribute name included in the relation obtained by dividing in S109 from the extracted approximate corpus (step S1102). At this time, the attribute that becomes the co-occurrence pair is a non-key attribute, and is extracted so as to be an unconnected attribute and an attribute that does not exist in the hierarchy (table) (words that are not duplicated in different hierarchies). Furthermore, the average of all co-occurrence rates in the approximate corpus may be calculated, and a filter may be applied according to the difference (distance) from the calculated average. That is, the control unit 10 extracts only attribute names having a co-occurrence rate that is substantially equivalent to the average, or extracts similar attribute names by referring to the co-occurrence rates of attribute names that have been selected by a selection operation described later. You may do it.

  In step S1102, for example, when “prefecture name” appears frequently in the extracted first corpus, the control unit 10 extracts “prefecture name” as a co-occurrence pair. In relations created in the past, the attribute of “prefecture name” is empirically added to a table containing the order date, product ID, quantity, etc., so that sales can be classified by region later. In some cases, the join operation process is omitted. By extracting the co-occurrence pair from the corpus described above, a DB that realizes the omission of the join operation processing can be created.

  The control unit 10 lists the extracted attribute names and transmits them to the terminal device 2, and the display unit 23 receives which attribute name is selected (step S1103). Then, when generating the complex network in step S111, the control unit 10 performs denormalization using the attribute name selected in step S1103. Specifically, in a one-to-many dependency relationship, the selected attribute (non-key attribute) is absorbed on the child side (higher dimension side) or the attribute selected in the one-to-many dependency relationship ( Non-key attribute) is absorbed by the parent side (low-dimensional side) or one-to-one dependency relationship (for example, “prefecture ID” and “prefecture name”, “customer type ID” and “customer” in FIG. 10) Type name ”) and the like.

  In step S1103, the control unit 10 may give priority to the list without accepting a selection from the user, and connect the top few attributes in co-occurrence pairs. In the above example, “prefecture name” is absorbed in the first dimension relation including the sales ID.

  The control unit 10 separately stores the relation changed based on the operation received in S114 in association with the user account in the learning data 41 (step S1141), and constructs a DB (S115).

  In this way, the user does not cause an abnormality at the time of updating by performing the operation of uploading the table-like relation (table) created by himself / herself to the DB processing apparatus 1 via the DBIF providing apparatus 3, In addition, it is possible to obtain a relational schema complete with denormalization in consideration of a load such as arithmetic processing. That is, even a user who does not have expertise in the DB and the technology for handling the DB can easily update the DB.

  Note that the control unit 10 may also refer to the learning data when calculating the functional dependency between attributes in step S107. As a result, it is possible to construct a DB that is empirically considered appropriate using the relationship between attributes in the DB created in the past as collective intelligence.

  By referring to the synonym dictionary, the co-occurrence dictionary, and the corpus included in the learning data 41, it is possible to comprehensively analyze the DB input and constructed by different designers. When the attributes defined in the consent are also defined as different attribute names, the attributes defined with the same name may also have different logical meanings in the DB. By the process based on the learning data of the DB processing apparatus 1 according to the second embodiment, different DBs can be integrated, and big data can be analyzed. Further, the integrated DB functions as a collective intelligence for the relation (table) to be added next.

  Further, the schema is visualized and drawn as shown in FIGS. 11 and 12 by a method different from the conventional ER diagram. Thereby, it is possible to intuitively grasp the schema of the relation DB uploaded by itself. Thereby, even the user who does not have knowledge about access to the DB can intuitively grasp the schema of the automatically generated DB.

  It should be thought that embodiment disclosed this time is an illustration and restrictive at no points. The scope of the present invention is defined by the terms of the claims, rather than the meanings described above, and is intended to include any modifications within the scope and meaning equivalent to the terms of the claims.

1 DB processing device (central device)
DESCRIPTION OF SYMBOLS 10 Control part 11 Memory | storage part 13 Communication part 1P DB processing program 2 Terminal device (external device)
20 control unit 23 display unit 231 interface 3 DBIF providing device

Claims (11)

In a database processing program for causing a computer communicating with a database to execute processing on the database,
In the computer,
An analysis step to analyze database relations,
Associating objects with multiple attributes in the analyzed relation,
A database processing program that executes a drawing step of arranging and drawing an associated object along an outer shape of a predetermined shape. Accepts selection and placement operations for the drawn object,
In the computer,
Receiving an arrangement of the object according to an operation;
The database processing program according to claim 1, further comprising executing a step of changing the relation of the database according to the accepted arrangement. The drawing step includes
When it is analyzed that the database is divided into a plurality of relations by the analysis, a predetermined shape corresponding to the number of divisions of the relation is adjacent, and an object corresponding to an attribute common between the relations is arranged in an adjacent place, The database processing program according to claim 1 or 2, wherein an object having an attribute belonging to each of a plurality of relations is drawn on an outer shape other than the adjacent portion. The analyzing step includes a step of identifying a hierarchical relationship between the plurality of relations;
The database processing program according to claim 3, wherein the drawing step draws the upper relation so as to be arranged in the center of the screen. The size at which the predetermined shape in which the object corresponding to the attribute belonging to each of the plurality of relations is arranged is drawn indicates the degree of variation in the database of the attribute value of the attribute belonging to the relation. The database processing program according to claim 3 . The analysis step includes
Decomposing the database by attribute,
Calculating, for each attribute, the size of variation in the database of the attribute value of the attribute;
Assigning a rank to the attribute based on the calculated magnitude;
Extracting candidate keys based on the given rank;
Identifying functional dependencies between decomposed attributes based on candidate keys;
Creating a decision tree between attributes based on relational dependencies;
The database according to any one of claims 1 to 5, further comprising: dividing a plurality of relations based on the created decision tree; and creating a hierarchical network based on the decision tree. Processing program. The analysis step includes
The database processing program according to claim 6, further comprising: generating a complex network in which attributes that are not directly parent-child relationships are linked in the hierarchical network. In a database processing method by a computer that executes processing on a database,
The computer is
Analyzing database relationships,
Associating objects with multiple attributes in the analyzed relation,
A database processing method, characterized in that screen information is created by arranging and drawing an associated object along an outer shape of a predetermined shape. The computer
Decomposing the database by attribute,
Calculating, for each attribute, the size of variation in the database of the attribute value of the attribute;
Assigning a rank to the attribute based on the calculated magnitude;
Extracting candidate keys based on the given rank;
Identifying functional dependencies between decomposed attributes based on candidate keys;
Creating a decision tree between attributes based on relational dependencies;
The database according to claim 8, wherein the database is analyzed by executing a step of dividing into a plurality of relations based on the created decision tree, and a step of creating a hierarchical network based on the decision tree. Processing method. In the database processing apparatus comprising means for communicating with each of the database and the external apparatus, and transmitting the processing results in the database to the external apparatus,
An analysis means for analyzing database relations;
Means for associating an object with each of the plurality of attributes included in the analyzed relation, creating a screen information drawn by arranging the associated object along a contour of a predetermined shape, and A database processing apparatus. The analysis means includes
Means to decompose the database by attribute,
Means for calculating the size of variation in the database of the attribute value of the attribute for each attribute;
Means for assigning a rank to the attribute based on the calculated magnitude;
Means for extracting candidate keys based on the given rank;
Means for identifying functional dependencies between decomposed attributes based on candidate keys;
Means to create a decision tree between attributes based on relational dependencies;
The database processing apparatus according to claim 10, comprising: means for dividing a plurality of relations based on the created decision tree; and means for creating a hierarchical network based on the decision tree.

Images