JPH1078970A - Data base design support system and tool and recording medium - Google Patents

Abstract

PROBLEM TO BE SOLVED: To shorten the time needed for the design and normalization of a data base by eliminating an ambiguity among the data items and securing the reversion to the data item that can serve as a definite entity. SOLUTION: A key extraction part 42 deletes the derivative and repetitive items out of all data items and extracts an identification key. A relevant table production part 43 produces a data item relevant table to show how the decision of a certain item is related to the decision of other items. A relation production part 44 shows the relations among those items contained in the data item relevant table in the form of models. An integration processing part 45 integrates the items having the 1:1 relations and excludes the items having the 'multiple : multiple' relations. An item addition part 46 adds the relevant items which are newly generated to the data item relevant table. Then an item integration part 47 integrates one of both items with the other which have such a relation where one or more other items are decided when a certain item is decided.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

The present invention relates to a so-called relational database system, that is, to a logical design technique of a database management system, and more particularly to a database design support system and a tool for database normalization work. The present invention also relates to a recording medium storing a program for forming these systems and tools.

[0002]

2. Description of the Related Art When a database management system, a so-called relational database system, is designed by a normal design algorithm, many data items whose subordination relations are ambiguous occur, so that it takes time to design and furthermore, it takes time to perform data maintenance and the like. I will. Further, if the database is normalized, data items whose subordination relations are ambiguous can be eliminated or reduced, but the normalization operation requires a great deal of time. Therefore, there is a need for a relational database design tool algorithm that can realize stable database design and shorten the time required for normalization work.

[0003] Conventional database normalization will be described. Initially, Codd proposed normalization up to the third normal form, but later on Boyce / Cod
d) extended third normal form and R. A fourth normal form by R. Fagin has been proposed. In a conventional database design tool, slips and form data are sequentially normalized from the first normalization, and the resulting results of the third normal form (or fourth normal form) are subjected to respective relations. The standard data, which is the final form of the logical data model, is set by focusing on the identification key.

In a system that actually designs such a database, a designer spends an enormous amount of time of several months designing a database. Normalization itself is a seemingly simple logical system, but in practice, it takes a long time because the design work is performed by sequentially charting several hundred files. The time required for database design depends on the scale of the system, but there are cases where the design is spent for eight months.

[0005] By performing normalization on a database once designed, a data structure that provides stable performance should be expected. However, in reality, when mounted and used, I / O (input / output) occurs frequently, and a problem often occurs that high performance cannot be obtained. For this reason, it is often the case that an easy conclusion is made that "it is better not to perform normalization in order to reduce I / O".

FIG. 2 shows a conventional normalization method.
This will be described in detail with reference to FIGS. FIG. 22 shows a flowchart of a conventional normalization process. The actual normalization is performed by an operator, that is, a database designer, operating the database according to this procedure. FIG. 23 schematically shows the normalization work in each stage.

[0007] 1. First Normalization Work (Step S1) When the normalization process is started, first, (1) a part to be repeated is found in the non-normal form. Before separating the repeated part, that is, the repeated part, (2) find the key of the repeated part to be separated. (3) When separating the repetitive part, a non-normal key is attached to the data set to be separated.

Incidentally, the key of the repetition part may not exist in the data item of the non-normal form. For example, in FIG. 23, among the keys 1 and 2 and the data items "1" to "10" of the data set R0 in the non-normal form, "7" to "10" are assumed to be the repetition parts. Therefore, as shown in FIG. 23, non-normal keys, that is, “key 1” and “key 2” are attached to separate the data set of the repetition part. Therefore, the data set of the first normal form is a data set R including “key 1”, “key 2”, and data items “1” to “6”.
1, “key 1”, “key 2”, data item “7” to
And a data set R2 consisting of “10”.

[0009] 2. Second Normalization Operation (Step S2) Data items that depend only on a part of the composite key are separated together with the key. Note that the key is also left in the original data set. For example, in FIG. 23, assuming that data items “5” and “6” in one data set R1 of the first normal form are determined only by “key 2”, these data items “5” and “6” Is separated with "key 2". Therefore, the data set of the second normal form is
A data set R11 including “key 1”, “key 2”, and data items “1” to “4”; a data set R12 including “key 2”, data items “5” and “6”; , "Key 2", and a data set R2 including data items "7" to "10".

[0010] 3. Third normalization work (step S3) A dependency on data items other than the primary key is checked, and a new data set is created together with the key. The key at that time is also left in the original data set as a reference key. For example,
In FIG. 23, assuming that the data item “3” in the data set R11 of the second normal form depends on the data item “2”, a new data set is created by these data items “2” and “3”. At this time, the data item “2” is a key of a new data set. Therefore,
The data set of the third normal form is “key 1”, “key 2”,
A data set R111 including data items “1”, “2” and “4”, a data set R112 including data items “2” and “3”, “key 2”, data items “5” and “6” A data set R12 consisting of
The data set R2 includes “key 2” and data items “7” to “10”.

4. Fourth Normalization Operation (Step S4) Data items containing a plurality of values are excluded in consideration of multi-value dependencies.

5. Fifth Normalization Work (Step S5) In the table data that has been subjected to the fourth normalization, the possibility of generating a row that cannot exist when the projection and the combined normal form are performed is eliminated. 6. Integration (Step S6) The data set is organized and integrated using the identification key. In order to organize / integrate a data set using this identification key, 1: 1
Integration and many: many exclusions are also included. As described above, the normalization processing ends.

[0013]

[Problem to be Solved by the Invention] By the way, in designing a database, after setting standard data, it is reviewed whether there are any unnatural data items, and in the process of organizing and integrating, "Why Such a data item
Are there data items and relations that seem strange? It is important to review these questionable data items as part of the normalization process. The data items are subject to review based on what identification key they depend on, but the main cause of data items whose dependency relationships are ambiguous is the dependency relationship that is performed in the second normalization or the third normalization. Analysis is not complete. However, conventionally, it is not clear how to actually analyze the dependency relationship.

The data items of a form include data items transcribed from other forms and ledgers, and derived data obtained by calculating some data items. When these data items are present, the dependency analysis is not straightforward. Eventually, it will be divisive and belong to some relation, but this will create unnatural data items. However, in addition to the simple ones described above, in general, relations between various types of data are often intertwined to create unnatural data items.

As described above, in a conventional relational database design or an algorithm in a design tool used for the design, a large number of data items whose dependency relations are ambiguous are generated, which takes time for the design, data maintenance, and the like. It takes time. Also, the normalization work itself requires a long time for the normalization work because the designer himself examines each data item and its relation individually while looking at and comparing the data items. .

The present invention has been made in view of the above-mentioned circumstances, and resolves ambiguity between data items by a simple and clear algorithm and assigns the data items to a clear data management target, that is, a data item to be an entity. It is an object of the present invention to provide a database design support system and a tool capable of shortening the time required for database design and normalization. Another object of the present invention is to provide a recording medium storing a program for forming these systems and tools.

[0017]

In order to achieve the above object, a database design support system according to a first aspect of the present invention provides, for all data items, a unique identification key other than a derived data item and a repeated data item. Key extraction means for extracting a data item that can be used to determine, for the data item, how the determination of a certain data item is related to the determination of another data item; A relation table creating means for creating a data item relation table indicated by categories of "determined", "always determined by one or more", and "sometimes determined by one or more", and data items between the data items in the data item relation table. Relation creating means for modeling and expressing the relation of
And an item for integrating the data items having the relationship of (1) and (2), and excluding the data items having the many: many relationship, and adding the related data item newly generated by the integrating device to the data item association table. An additional means, and an item integrating means for integrating the one data item into the other data item in such a relationship that one or more other data items are always determined when one of the data items is determined.

[0018] The database design support system may further include an entity extracting means for selecting an entity and a relation of a data item to be processed in advance and providing the entity and the relation to the key extracting means.

The association table creating means may include means for forming the data item association table by a matrix in which target data items are arranged in rows and columns. The item integration means may include means for performing integration on condition that the one data item is not affected by other data items.

The item integrating means identifies whether or not the one data item to be integrated should be left as a resource-related data item, and determines whether the data item to be left as a resource-related data item is Means may be provided for providing an identification code uniquely corresponding to the item, and integrating the identification code with the other data item.

According to a second aspect of the present invention, there is provided a database design support tool comprising key extraction support means for extracting a data item which can be a unique identification key, and determining one data item by determining another data item. Are defined as "always unique", "sometimes uniquely determined", "always one or more", and "sometimes one".
Association table creation means for creating an item association table indicated by a category of "determined by more than one", relation creation support means for modeling and expressing the relationship between data items of the item association table, and 1: 1 in the relation.
And a data item association table for integrating the data items having the relations of (1) and (2) and excluding the data items having the many: many relations. And item integration for integrating the one data item into the other data item in such a relationship that if one data item is determined, at least one other data item is always determined. Support means.

[0022] The database design support tool may further include an entity extraction support means for preselecting an entity and a relation of a data item to be processed and providing the selected data item to the key extraction means. The association table creation support means may include means for displaying a matrix in which target data items are arranged in rows and columns to form the item association table.

[0023] The item integration support means may include means for performing integration on condition that the one data item is not affected by other data items. The item integration support means identifies whether or not the one data item to be integrated should be left as a resource-based data item. A means for providing a uniquely corresponding identification code and integrating this identification code with the other data item may be included.

A database design support system and a tool according to the present invention extract a data item that can be a unique identification key other than a derived data item and a repeated data item for all data items, and extract certain data from the data items. "Always unique" describes how field decisions are related to other data field decisions,
"Sometimes it is uniquely determined", "At least one is always determined",
And a data item relation table indicated by the category of "sometimes determined by one or more" is created, and a relation between data items is modeled and expressed for each data item in the data item relation table, and a 1: 1 relation in the relation is expressed. Integrate related data items and exclude many: related data items, add newly generated related data items to the data item relation table, and make sure that when one data item is determined, the other The normalization of the database is realized by integrating the one data item having the relationship that one or more data items are determined into the other data item. This system and tool can reasonably achieve the normalization of the database design with a clear algorithm, resolve the ambiguity between data items, and assign them to a clear data management target, that is, a data item that is an entity. This makes it possible to design a stable database and reduce the time required for normalization.

A program for realizing the above-described functions may be stored and distributed to a computer.

[0026]

Embodiments of the present invention will be described below with reference to the drawings. A database design support system according to an embodiment of the present invention will be described with reference to FIGS.

FIG. 1 schematically shows the configuration of a database design support system according to an embodiment of the present invention. The database design support system shown in FIG. 1 includes a display unit 1, an input unit 2, an output unit 3, a CPU (central processing unit) 4, and a database unit 5.

The display section 1 displays display information such as characters and images output from the CPU 4 using a display element such as a CRT display or a liquid crystal display panel. The input unit 2 includes a keyboard, a mouse, and the like, and inputs information to the CPU 4 by an operation of an operator, that is, a database designer. The output unit 3 includes a printer or the like for outputting a hard copy, and outputs information from the CPU 4 as a hard copy or as data to another device.

The CPU 4 exchanges data with the display unit 1, the input unit 2, the output unit 3, and the database unit 5 by executing a program stored in a storage device (not shown), for example. To realize various functions in this system. The database unit 5 stores a database, and is accessed by the CPU 4 to normalize the stored database. The CPU 4 includes an entity extraction unit 41, a key extraction unit 42, an association table creation unit 43,
It includes a relation creation unit 44, an integration processing unit 45, an item addition unit 46, and an item integration unit 47.

The entity extracting unit 41 extracts the entities and relations of the data items that are considered "ambiguous or unnatural" among the data items constituting the database stored in the database unit 5. The entity extraction unit 41 also extracts nearby entities and relations affecting these entities. When the number of data items in the entire database is small, or when it is desired to normalize the entire database, the entity extraction unit 41 selects all data items as processing targets without substantially extracting data items. You may do so.

The key extraction unit 42
For all data items selected in step 1, search for derived and repeated data items, delete the relevant derived and repeated data items, and search for and extract data items that can be unique identification keys I do.

The association table creation unit 43 creates a data item association table indicating how a decision on a certain data item is related to a decision on another data item. The data item relation table shows that all data items
It indicates whether or not the decision of a certain data item is related to the decision of another data item, and if it is related, it is always determined uniquely (the number of decisions is "1"), and sometimes it is uniquely determined (the number of decisions is sometimes “0” or “1”), “at least one is determined (determined number is“ 1 ”or more)”, and “one or more is determined occasionally (determined number is“ 0 ”or more)”. The data item association table is configured, for example, as a data item association matrix in which the above-described related information is expressed by a matrix (see FIGS. 3 and 4). The relation creating unit 44 models and expresses a relation between data items for each data item of the data item association table created by the association table creating unit 43 (see FIGS. 5 and 6).

The integration processing unit 45 integrates data items having a 1: 1 relationship in the above-mentioned relation and excludes data items having a many: many relationship. The item adding unit 46 adds the newly generated related data item to the data item association table by the processing of the integration processing unit 45.

The item integrating section 47 integrates one data item having a relationship that one or more data items are always determined when one data item is determined, into the other data item. That is, the item integration unit 47 integrates the data item A with the data item B if there is a data item B that has a relationship that at least one data item A is determined whenever the data item B is determined. However, this integration is performed on the condition that the data item B is not affected by other data items. Upon integration,
Whether the data item to be integrated should be left as a data item of the resource system is identified. If it is to be left as a resource system, an identification code uniquely corresponding to the data item B to be integrated is provided, and this identification code is integrated into the data item A.

Next, an analysis design operation by the database design support system configured as shown in FIG. 1 will be described with reference to a flowchart shown in FIG. [Procedure 1] Extraction of applicable entity (Step S11) The entity extracting unit 41 selects and enumerates entities and relations of data items considered to be "ambiguous" or "unnatural". At this time, nearby entities and relations affecting these entities are also selected and listed. As described above, when the number of data items in the entire database is small or when it is desired to normalize the entire database, all data items are selected as processing targets without substantially extracting data items. You may do so. If it is always planned to select all data items as processing targets, the processing of the entity extracting unit 41 and step S11 is unnecessary.

[Procedure 2] Extraction of identification key (step S1)
2) For all the data items selected by the entity extraction unit 41 in step S11, the key extraction unit 42
The derived data item and the repeated data item are searched and extracted and deleted, and a data item that can be a unique identification key is further searched and extracted. This is almost the same processing as the conventional first normalization operation.

[Procedure 3] Creation of Data Item Association Table (Step S13) The association table creation unit 43 shows, for the data items, data indicating how the determination of one data item is related to the determination of another data item. Create an item association table. The data item association table indicates, for all data items, whether or not the determination of one data item is related to the determination of another data item. In this case, the relation is expressed by the following four categories.

(A) It is always uniquely determined (the determined number is "1"). (b) It is sometimes uniquely determined (the determined number is “0” or “1”). (c) One or more are always determined (the number of decisions is "1" or more). (d) Sometimes one or more are determined (the number of determinations is "0" or more).

For example, how the data item B is determined when the data item A is determined is represented by a symbol as shown in FIG. The data item association table is expressed as a data item association matrix configured by arranging the symbols described above in a matrix of each data item, for example, as shown in FIG. In FIG. 4, when the data item B does not depend on the determination of the data item A, it is blank.

The deciding factor of whether or not to be determined is
Don't make transitive decisions. Connect only directly determined data items. For example, in the example of FIG. 4, even if the order number (1) is determined, the unit (10) is unknown. That is, when the data item X is determined, the data item Y is determined, and when the data item Y is determined, the data item Z is determined.
Once data item X is determined, it is not determined that data item Z is determined. That is, it is not determined that the unit (10) is determined from the product code (8) ordered with the order number (1) of xxx.

[Procedure 4] Creating Relation (Step S14) The relation creating unit 44 performs the following processing on each data item of the data item association matrix created in step S13.
The relation between the data items is modeled as shown in FIG. 5, and is expressed as an ER diagram showing the entity and the relation as shown in FIG.

[Procedure 5] Integration (Step S15) (Procedure 5-1) The integration processing unit 45 integrates data items having a 1: 1 relationship in the ER diagram of the relation expressed in Step S14. I do. (Procedure 5-2) Further, the integration processing unit 45 excludes data items having a many: many relationship in the ER diagram of the relation represented in step S14. Step S
As a result of the processing of No. 15, the ER diagram in FIG. 6 is rewritten into an ER diagram as shown in FIG.

[Procedure 6] Adding a new item to the item relation table (step S16) The item adding section 46 adds the newly generated related data item to the data item relation matrix in step S15, and adds it to FIG. A data item association matrix as shown is obtained.

[Procedure 7] Re-creation of relation (Step S17) Based on the data item relation matrix generated in Step S16, the item integrating unit 47 always includes one data item if one data item is determined. One data item having the relationship determined as described above is integrated with the other data item, and a relation is re-created.

As described above, the item integrating unit 47 integrates the data item A with the data item A if there is a data item B that is always determined to be one or more when the data item B is determined. However, this integration is performed on the condition that the data item B is not affected by other data items. Upon integration, it is determined whether or not the integration target data item should be left as a resource data item. If it is to be left as a resource system, an identification code uniquely corresponding to the data item B to be integrated is provided, and this identification code is integrated into the data item A.

For example, in the example of FIGS. 7 and 8, the unit (10)
And the unit price (11) are absorbed in the product code group. However, if a unit price code is required, the unit code is assigned, the product code is integrated, and the unit code and unit price are left. The final result thus obtained is shown in FIG.
This result is almost the same as the result when the normalization work (third normalization) is performed.

The examples shown in FIGS. 4 to 9 will be specifically described. If the order number (1) is determined as shown in the data item relation matrix shown in FIG. 4, the order date (2) and the person in charge
(3), designated delivery date (4), supplier code (5), supplier name (6),
And the ordering address (7) must be uniquely determined, and the product code
(8) and one or more product names (9) are always determined.

Once the order date (2) is determined, the order number (1),
And one or more supplier code (5) is always determined. Person in charge (3)
Is determined, one or more order numbers (1) and supplier codes (5) are always determined. Once the designated delivery date (4) is decided, order number
(1) and one or more supplier code (5) are always determined. Once the supplier code (5) is determined, the order number (1) and order date
(2) The person in charge (3) and the designated delivery date (4) are sometimes determined at least once, and the supplier name (6) and the supplier address (7) are always uniquely determined.

Once the supplier name (6) is determined, one or more order numbers (1) are sometimes determined, and the supplier code (5) is always uniquely determined. Once the supplier address (7) is determined, one or more order numbers (1) are sometimes determined, and the supplier code (5) is always uniquely determined. Once the product code (8) is determined, one or more order numbers (1) are sometimes determined, and the product name (9), unit (10), and unit price (1
1) is always uniquely determined. Once the product name (9) is determined, one or more order numbers (1) are sometimes determined, and the product code (8) is always uniquely determined.

When the unit (10) is determined, one or more product codes (8) are always determined. If unit price (11) is decided, product code
(8) is always decided at least one.

In the ER diagram shown in FIG. 6, the supplier code (5), the supplier name (6), and the supplier address (7) can be integrated, and the product code (8) and the product name (9) can also be integrated. In this case, a new data item of order number + product code is added.

As a result, an ER diagram as shown in FIG. 7 is formed. As shown in FIG. 8, if the quantity (12) is determined, a plurality of order numbers + commodity codes are always determined. Once the delivery location (13) is determined, the order number +
One or more product codes are always decided. And the order number +
Once the product code is determined, the quantity (12) and the designated delivery location (1
3) is always determined uniquely.

In the final result, as shown in FIG.
Using the order number and the product code as keys, the quantity and the designated delivery location are integrated into the ordered product group. Using the order number as a key, the order date, the person in charge, the designated delivery date, and the supplier code are integrated into the order group.
This order group is subordinate to the order product group using the order number as a key. Using the supplier code as a key, the supplier name and the supplier address are integrated into a supplier group, and the supplier group is subordinate to the order group using the supplier code as a key. Using the product code as a key, the product name, unit and unit price are integrated into the product group,
The product group is subordinate to the ordered product group using the product code as a key. Note that, in the figure, the data item with the mark FK indicates a data item that has a data item or a group that is subordinate to the data item.

The difference between the normalization method in the database design support system described above and the conventional normalization method is as follows. (1) In the conventional method, identification of dependency relationships is left to a human being, that is, a designer, as it is conceived. Therefore, all data items cannot be identified in detail, resulting in oversight or omission. . (2) In the conventional method, as the data model becomes more complicated, the analysis of the dependency relationship becomes less clear and the state of the data model becomes more frequent. In addition, even if there is a transitive dependency, the user may find that the data to be managed is lost after the design is divisible and implemented. No matter how much you understand the theory of normalization, when you try to apply it to a real system, you have to deal with hundreds of files, for example.

(3) In the above-described method, all the data items are decomposed from the beginning, and the data items to be determined for the data items are developed on a matrix.
By this operation, the lump of entities is derived, and at the same time, the optionality between the entities (always or occasionally) and the cardinality (unique or plural) are determined. For complete subordination, partial subordination, and transitive subordination, which correspond to conventional second and third normalization operations, all relationships between data items are identified. Further, the elimination of the multi-valued subordinate is performed by the exclusion of the many: many.

Therefore, the conventional problem that a large number of data items whose subordination relations are ambiguous occur, that the design rework and the data maintenance are troublesome, and that the normalization work takes a long time is solved. The time required for the design and normalization work can be reduced. In addition, unnatural data does not appear after mounting, and maintenance is not required, and data inconsistency does not occur.

In the above description, in each of the entity extraction unit 41, the key extraction unit 42, the association table creation unit 43, the relation creation unit 44, the integration processing unit 45, the item addition unit 46, and the item integration unit 47, The analysis of the relationship between the entity and the relation between the items is performed. However, the analysis in each of these parts is not always easy, and there is a case where it is better to leave individual judgment to a designer. Therefore, the determination element in each unit may be narrowed down to a plurality of candidates and presented to the designer via the display unit 1 so that the designer can select the candidate by an interactive operation.

Further, a design support tool for easily performing a database design in accordance with the above-described method may be configured such that all decision elements are left to a designer. In this case, creation of a data item relation table, creation of an element of an ER diagram indicating a relation, and the like are automatically performed. That is, the entity extraction unit 41 and the key extraction unit 4
2. an entity extraction support unit for supporting each operation by a designer, instead of the association table creation unit 43, the relationship creation unit 44, the integration processing unit 45, the item addition unit 46, and the item integration unit 47; The key extraction support unit, the association table creation support unit, the relation creation support unit, the integration processing support unit, the item addition support unit, and the item integration support unit.

FIGS. 10 and 11 show another specific example.
FIG. 4 shows an ER diagram before and after integration of data items of a local government database design. The data items of gender and gender code in FIG. 10 are integrated in FIG. The data items of the job title code and job title in FIG. 10 are integrated in FIG. The data items of the affiliation code, affiliation name and affiliation short name in FIG. 10 are integrated in FIG. The data items of the adoption category code and the adoption name in FIG. 10 are integrated in FIG.

The data items of the post code and the post name in FIG. 10 are integrated in FIG. The data items of the municipal code, the prefecture name, the municipal name, the post office zip code and the post office address in FIG. 10 are integrated in FIG. The data items of the transfer branch code and the branch name in FIG. 10 are integrated in FIG. The data items of the transfer financial institution code and the financial institution name in FIG.
1 is integrated. FIGS. 12 to 15 show, as still another specific example, E-data in an example of a database design of a university.
The R diagram is shown. FIG. 12 shows data items such as hobby, date of birth, gender, department name, student number, address, subject code, subject name, evaluation code, and evaluation description.

In FIG. 13, the data items of the subject code and the subject name are integrated, and the data items of the evaluation code and the evaluation description are integrated. In FIG. 14, data items "student number + hobby" and "student number + subject code" are added.

In FIG. 15, "Student number +
The data item of “hobby” and the hobby are integrated as “student number + hobby”. The data items of the student number, date of birth, gender, address, and department name in FIG. 14 are integrated as “student number + date of birth + gender + address + department name”. An evaluation code is further added to the data item "student number + subject code" in FIG. 14, and a data item "student number + subject code + evaluation code" is generated. This is the result of normalization.

For comparison, FIGS. 16 to 21 show examples in which the same data as in FIGS. 12 to 15 are normalized by a conventional algorithm.

As shown in FIG. 16, the original data set R0
Is the student number, date of birth, gender, address, hobby, department name,
It consists of data items of subject code, subject name, evaluation code and evaluation explanation. In this case, the student number is the key, and the subject code, subject name, evaluation code, and evaluation description are repeated parts, that is, repeated parts.

FIG. 17 shows a first normal form that has undergone the first normalization. A student number is used as a key, and a data set R1 including a date of birth, gender, address, department name, and hobby, and a student number and subject code And a data set R2 including a subject name, an evaluation code, and an evaluation description.

FIG. 18 shows a second normal form after the second normalization, in which a student number is used as a key, and a data set R1 including a date of birth, gender, address, department name and hobby, a student number and a subject code And a data set R21 composed of an evaluation code and an evaluation description, and a data set R22 composed of a subject code and a subject name.

FIG. 19 shows a third normal form that has undergone the third normalization. The student number is used as a key, and a data set R1 including the date of birth, gender, address, department name, and hobby, and a student number and subject code , A data set R21 'including an evaluation code, a data set R22 including a subject code as a key, a data set R22 including a subject name, and a data set R23 including an evaluation code as a key and further including an evaluation description. .

FIG. 20 shows a fourth normalization form after the fourth normalization, in which a student number is used as a key, a data set R11 including a date of birth, gender, address, and department name, and a student number is used as a key. A data set R12 consisting of hobbies, a student number and a subject code as a key, a data set R21 'consisting of an evaluation code, a subject code as a key, a data set R22 consisting of a subject name, and an evaluation code as a key, Further, the data set is separated into a data set R23 including evaluation explanations.

FIG. 21 is an ER diagram of the result of FIG. 20, and it can be seen that it is substantially the same as FIG. As described above, according to the method of the present invention, it is possible to easily obtain a result equivalent to the conventional normalization operation.

It should be noted that the database design support system and tool of the present invention can be realized using an ordinary computer system without using a dedicated system. For example, a medium (floppy disk, CD-RO) storing a program for executing the above-described operation in a computer
M), the video server of the video-on-demand system that executes the above processing can be configured.

The medium for supplying the program to the computer may be a communication medium (a medium that temporarily and fluidly stores the program, such as a communication line, a communication network, or a communication system). For example, the program may be posted on a bulletin board (BBS) of a communication network and distributed via the network. Then, by starting this program and executing it in the same manner as other application programs under the control of the OS, the above-described processing can be executed.

[0072]

As described above, according to the present invention, the ambiguity between data items is eliminated by a simple and clear algorithm, and the data items are assigned to clear data management targets, that is, data items to be entities. And a database design support system and tool that can reduce the time required for database design and normalization. Further, it is possible to provide a recording medium recording a program capable of generating these database design support systems and tools.

[Brief description of the drawings]

FIG. 1 is a block diagram schematically showing a configuration of a database design support system according to an embodiment of the present invention.

FIG. 2 is a flowchart illustrating an operation of the database design support system of FIG. 1;

FIG. 3 is a diagram showing meanings of symbols for explaining the operation of the database design support system of FIG. 1;

FIG. 4 is a diagram showing a data item association matrix for explaining the operation of the database design support system of FIG. 1;

FIG. 5 is a diagram showing a correspondence relationship between data item association matrices and ER diagrams for explaining the operation of the database design support system of FIG. 1;

FIG. 6 is an ER diagram for explaining the operation of the database design support system of FIG. 1;

FIG. 7 is a re-created ER diagram for explaining the operation of the database design support system of FIG. 1;

8 is a diagram showing a data item association matrix to which items for explaining the operation of the database design support system of FIG. 1 are added.

FIG. 9 is a diagram showing a final result of analysis design for explaining the operation of the database design support system of FIG. 1;

FIG. 10 is an ER diagram in another specific example for explaining the operation of the database design support system of FIG. 1;

FIG. 11 is an ER diagram for explaining the operation of the database design support system of FIG. 1 after the integration processing in the specific example of FIG. 10;

FIG. 12 is an ER diagram in another specific example for explaining the operation of the database design support system in FIG. 1;

13 is an ER diagram illustrating the operation of the database design support system of FIG. 1 at the next point in the specific example of FIG. 12;

FIG. 14 is an ER diagram illustrating the operation of the database design support system of FIG. 1 at the next point in the specific example of FIG. 12;

FIG. 15 is a flowchart illustrating the operation of the database design support system of FIG. 1;
FIG.

FIG. 16 is a diagram showing an original data set for explaining processing when a conventional normalization method is used for a specific example similar to FIG. 12;

FIG. 17 is a diagram illustrating a data set of a first normal form for describing processing when a conventional normalization method is used for a specific example similar to FIG. 12;

FIG. 18 is a diagram illustrating a data set of a second normal form for describing processing when a conventional normalization method is used for a specific example similar to FIG. 12;

FIG. 19 is a diagram illustrating a data set of a third normal form for describing processing when a conventional normalization method is used for a specific example similar to FIG. 12;

FIG. 20 is a diagram illustrating a data set of a fourth normal form for describing processing when a conventional normalization method is used for a specific example similar to FIG. 12;

FIG. 21 is an ER diagram for explaining a processing result when a conventional normalization method is used for a specific example similar to FIG. 12;

FIG. 22 is a flowchart for explaining an algorithm in a conventional normalization method.

FIG. 23 is a schematic diagram for explaining an algorithm in the normalization method of FIG. 22;

[Explanation of symbols]

 DESCRIPTION OF SYMBOLS 1 Display part 2 Input part 3 Output part 4 CPU (central processing part) 5 Database part 41 Entity extraction part 42 Key extraction part 43 Related table preparation part 44 Relation preparation part 45 Integration processing part 46 Item addition part 47 Item integration part

Claims (7)

[Claims] 1. Key extraction means for extracting a data item that can be a unique identification key other than a derived data item and a repeated data item for all data items; Data item associations that indicate how they relate to the determination of data items by the categories of "always uniquely determined", "sometimes uniquely determined", "always one or more", and "sometimes one or more" A relation table creating means for creating a table, a relation creating means for modeling and expressing a relation between data items for each data item of the data item association table, and a data item having a 1: 1 relationship in the relation. An integrating means for integrating and eliminating data items having many: many relationships; An item adding means for adding a newly generated related data item to the data item relation table; and, if one data item is determined, the other data item is always set to 1
An item integrating means for integrating the one data item having a relation of being determined to be more than one into the other data item. 2. The database design support system according to claim 1, further comprising an entity extracting means for preselecting an entity and a relation of a data item to be processed and providing the selected data item to said key extracting means. 3. The association table creation means according to claim 1, wherein said association table creation means includes means for forming said data item association table by a matrix in which target data items are arranged in rows and columns. Database design support system. 4. The apparatus according to claim 1, wherein said item integrating means includes means for performing integration under a condition that said one data item is not affected by another data item. The database design support system described in the section. 5. The item integrating means identifies whether the one data item to be integrated should be left as a resource data item, and determines whether the data item to be left as a resource data item is The database design according to any one of claims 1 to 4, further comprising means for providing an identification code uniquely corresponding to the data item, and integrating the identification code into the other data item. Support system. 6. A key extraction support means for extracting a data item which can be a unique identification key, and a method for determining how a determination of a certain data item is related to a determination of another data item. An association table creating means for creating an item association table indicated by categories of "determined", "uniquely determined", "always one or more", and "sometimes determined one or more", and between data items of the item association table Relation creation support means for modeling and expressing the relation of the relations, and integration support for unifying data items having a 1: 1 relation in the relation and excluding data items having a many: many relation Means, an item addition support means for adding a related data item newly generated by the integration support means to the data item relation table; Over data item is always the other data item if Kimare 1
An item integration support means for integrating the one data item having a relationship of being determined by more than one item into the other data item. 7. A computer, comprising: key extraction means for extracting a data item that can be a unique identification key; and for the data item, how the determination of one data item is related to the determination of another data item. An association table creation means for creating an item association table indicated by categories of "always uniquely determined", "sometimes uniquely determined", "always one or more", and "sometimes one or more". Relation creation means for modeling and expressing a relation between data items; integration means for integrating data items having a 1: 1 relationship in the relation and excluding data items having a many: many relationship; Item adding means for adding a related data item newly generated by the converting means to the item relation table. If one of the data items is determined, Not the other data item 1
A recording medium storing a program for causing the one data item having a relation of being determined to be more than one to be integrated with the other data item.