Almost all modern software systems from small mobile applications to large enterprise suites are using databases and database management systems to store and manage data collections and ensure their persistence and security. However, dominating Agile software development methodologies do not assume engineers focus only on database design or any other specific field. In Agile projects, database design usually belongs to the scope of software developers responsible for server-side programming. Hence, a lack of specialized skills and experience may lead to database design shortcomings or even errors causing time and money expenses at the later stages of software development projects. When software requirements are elicited, database entities and relations are captured in business rules - brief and precise descriptions of a considered domain. Therefore, in this paper, we propose a computational technology and a software tool for the translation of business rules into database creation scripts to reduce time and cost expenses at the development or maintenance stages of software engineering projects. In this study we use the finite-state machine based approach to parse business rules, association rules learning, and naming conventions to detect data types using attribute names, and the most widely-used database management systems to perform experiments.
Today is hard to imagine software applications that do not use a database (DB) to keep user data.
In general, a database is the collection of raw facts (i.e. end-user data), which is stored in a structured
manner, and metadata that describes this structure and how end-user data is managed. Databases are
handled by the specialized system software called the Database Management System (DBMS). The
DBMS is responsible for controlling the database structure’s persistence and security [
Currently, software applications of different complexity and data storage requirements, from food
delivery mobile applications to large enterprise information systems (IS), keep their data collections
in databases managed by DBMS. As a result, database design is a crucial component of practically
any software engineering project and may call for specialized engineers with strong expertise in data
modeling and DB development. This approach may work for waterfall or iterative projects when task
delegation to various specialists or even teams and big project teams are common things [
However, according to Agile practices dominating in software engineering projects, DB design is
done by the same software engineers responsible for the back-end (i.e. server-side) development, or
even by full-stack developers responsible for both front-end and back-end programming [
This makes engineers responsible for DB design and development replaceable, while the whole
software engineering process is more flexible. Nevertheless, the lack of database design skills, proper
training, and experience, as well as the time and staff resources to focus more precisely on database
schema correspondence to a domain, may lead to design mistakes and further time and monetary
expenses for fixing errors made at the DB design stage. It is well-known though, that the cost of error
fixing increases exponentially during the project and may be several times higher at the development
stage than at the design stage [
Business rules are used as sources of DB design requirements, helping to detect necessary entities,
attributes, and relationships. According to [
Database BR are definitions of data model components, including entities, attributes, relationships,
and constraints [
Therefore, this paper aims at reducing time losses and related costs occurring at the late stages of software engineering projects when the DB design is handled by inexperienced or untrained engineers responsible for server-side or full-stack development. The goal could be achieved by developing the computational technology and software tool for the translation of BR into DB creation scripts, such as Data Definition Language (DDL) statements of Structured Query Language (SQL).
The rest of this paper is structured as follows: the state-of-the-art analysis is given in sub-section 1.1, the formal problem statement is given in 1.2, materials and methods for DB schema generation from BR are outlined in section 2 and its respective sub-sections, while the software tool development and usage to solve the considered problem is described in section 3. The analysis of obtained results includes validation of SQL scripts generated from BR for compliance with the most popular DBMS. In the last section conclusion and future work in this field are formulated.
According to the “DB-Engines Ranking” website that ranks DBMSs according to their popularity,
the most widely used are relational database management systems: Oracle, MySQL, Microsoft SQL
Server, and PostgreSQL [
Existing studies in the field of automatic DB schema generation from business rules demonstrate
different approaches. In [
Let us also represent fact business rules using controlled language structures of limited grammar
and complexity. Hence, the structure of fact business rules [
fact :: {each} entity _ name {has} attribute _ name . (1)
Hence, a deterministic finite-state machine (DFSM) [
Tokens, S, s0 , , F , (2) where:
Tokens is the finite non-empty set of tokens of each business rule; S is the finite non-empty set of states; s0 is the initial state, s0 S ; : S Tokens S is the state-transition function; F is the set of final states, F S .
The proposed DFSM is given in Fig. 1 below using the UML state diagram.
Besides the initial state s0 S , the set of states S includes the following ones:
s1 is the state to which the DFSM transfers after the “each” token is passed as input;
s2 is the state to which the DFSM transfers after the token, which corresponds the following
conditions, is passed as input:
token "has"token StopWords,
(3)
where StopWords is the set of stop words, which can be filtered using the NLTK (Natural Language
Toolkit) package [
when the state machine transfers to s4 , the given token is added to a set that forms the name of attribute AttributeName .
As given above, the proposed DFSM (Fig. 1) takes fact business rules given according to (1) and produces ER model components: entity names and attribute names. The further problem includes the detection of relationships between extracted entities, as well as data types assignment to attributes.
Let us formally describe a relational database structure using the following equations:
Entities Entityi ,i 1,2,, n, i 1,2,, n : Entityi EntityNamei ,Attributeki , k 1,2,, q , i 1,2,, q : Attributeki AttributeNameki , AttributeTypeki , : Attributes DataTypes DataTypej , j 1,2,, m,
AttributeTypeki DataTypes, : Attributes Entity PK , Entity FK , Entity PK Entities Entity FK Entities,
(5) Entities is the set of entities; Entityi is the i -th entity of a database structure, i 1,2,, n ; n is the number of entities in a database structure; Attributes is the set of attributes; EntityName i is the name of i -th entity, i 1,2,, n ; Attributeki is the k -th attribute of i -th entity, k 1,2,, q ; AttributeNameki is the name of k -th attribute of i -th entity, k 1,2,, q ; AttributeTypeki , is the data type of k -th attribute of i -th entity, k 1,2,, q ; q is the number of attributes in a database structure; DataTypes is the set of all data types that can be used in a database structure;
m is the number of data types used in a database structure; is the function that defines a data type DataTypej , j 1,2,, m for each attribute; Entity PK , Entity FK is the pair of entities that form a relationship; Attributes is the sub-set of attributes that form relationships, Attributes Attributes ;
Entity FK is the child entity; where:
is the function that defines a pair of parent and child entities for each common attribute given in the sub-set Attributes .
Using the proposed formal description of a relational database structure (5), the input data in the form of business rules (1) processed by the DFSM (Fig. 1) will be structured to generate SQL scripts. 3.2.
Let us consider the example of the following business rules describing a domain of students and courses:
“Each student has a student ID. Each student has a full name. Each course has a course number. Each course has a title. Each course has a number of credits. Each enrollment has a student ID. Each enrollment has a course number. Each enrollment has a grade.”
Using the proposed DFSM, we obtain the following entities and their attributes. All input tokens combined to obtain entity names and attribute names values should be merged using the underscore (i.e., “_”) separator to simplify the further generation of syntactically correct SQL code. Stop words should be removed according to equations (3) and (4).
The initial information on the database structure, which is extracted from the business rules given above, is demonstrated in Table 1.
Relationships between entities should be created using common attributes – primary keys (PK) in parent entities and foreign keys (FK) in child entities. To detect relationships, we propose to check all pairs of entities and find intersections of their attributes. An entity that firstly mentions the common attribute should be considered as parent, while other entities – as child (Fig. 2).
Using extracted entities and their respective attributes (Table 1), we can apply the algorithm given above (Fig. 2) to detect relationships between entities (Table 2).
The following essential problem includes the data types suggestion for attributes using their names
only. In this study we use the “Spider 1.0 NLP Dataset” created and supported by Yale students. This
dataset includes 166 databases with various tables that cover 138 different domains [
Using the metadata descriptions of database tables from the “Spider” dataset, we prepared the set
of attribute names and corresponding data types. These pairs are used by the association rule learning
Apriori algorithm (Fig. 3) [
Hence, Lift 1 means the data type depends on the attribute and this association rule can be used with a certain level of confidence. The data type suggestion algorithm is given in Fig. 4 below. DataType default (if pairs with a given attribute do not exist) should be suggested:
Lift max max LiftAttribute DataType j .
j1,2,,m The suggested data type should be chosen if the following condition is satisfied:
LiftAttribute DataTypej Lift max , j 1,2,, m, where is some tolerance limit or stopping criteria, let us use 106 .
The “Spider” dataset includes the following data types assigned to different database columns:
DataTypes "number","time","text","boolean" . (11) The default data type is chosen to be a text type, DataTypedefault "text".
As it is demonstrated in Table 3, attributes “course_number” and “number_credits” are not present in the “Spider” data set. Therefore, an alternative technique should be proposed to augment the used association rules learning algorithm. 3.5.
Let us consider the following sets of keywords that will be used to augment the association rules algorithm by checking how attribute names match naming conventions:
Knum, Ktime , Kbool ,
(9) (10)
Therefore, the mapping between keywords and data types they describe can be formally described as the following function: : Knum Ktime Kbool DataTypes . (13)
The data type suggestion algorithm based on attribute naming conventions is shown in Fig. 5.
Hence, the proposed algorithm (Fig, 5) allowed to suggest the “number” data type for attributes “course_number” and “number_credits” previously identified as text attributes (see Table 3). 3.6.
The proposed computational technology includes steps described in sub-sections above and given formally using the following IDEF0 diagram (Fig. 6).
The final step shown in Fig. 6 includes SQL code generation using the developed software tool.
A A1, A2 , A3 is the set of interacting algorithms [
A2 is the attribute data types suggestion algorithm based on association rules;
A3 is the attribute data types suggestion algorithm based on naming conventions;
R A A describes connections between algorithms [
To perform experiments and validate the proposed computational technology, the software tool for business rules translation into SQL data definition scripts should be developed.
The software tool should read a business rules text file as the input, when started by a user, and generate the SQL scripts file as the output. Also users should be able to modify keywords depending on used attributes naming convention (see Table 4).
The UML use case diagram demonstrating the main functional capabilities of the software tool for business rules translation into database creation scripts is demonstrated in Fig. 7 below.
As it is shown in the use case diagram (Fig. 7) above, some usage scenarios are gray-colored. This means such activities are tightly related to the software tool but executed independently using other software: the business rules text file is created using any third-party text editor; obtained SQL scripts are adjusted and deployed to DBMS using ant third-party client tool.
The developed computational technology in terms of software components is shown in Fig. 8.
The component diagram above (Fig. 8) demonstrates structural elements mentioned in equations (14) and (15), as well as describes connections between DFSM, algorithms, and SQL code generation software module (for better perception, these components are colored differently).
As for the SQL code generation module, this part of the software tool should solve two tasks: build statements according to the SQL syntax; substitute generic data types (11) by specific SQL data types.
According to the “DB-Engines Ranking” resource [
Therefore, the corresponding data types used in these DBMS [
The software is created using the Python programming language and works as a command-line
tool, which takes several arguments, including the name of the business rules text file, the name of the
output SQL scripts file, and the name of the target DBMS. It also uses third-party Python modules:
Pandas [
Let us use business rules we introduced in sub-section 2.1 and put them into the input file (Fig. 9).
As the result, the software produces the SQL scripts file. The sample output is shown in Fig. 10.
SQL code was produced for various DBMS and tested using “SQL Fiddle” service (Fig. 11) [
As it is demonstrated in Fig. 11 above, input business rules (see Fig. 9) were translated into SQL scripts used to successfully create database schemas for Oracle 11g R2, MySQL 5.6, Microsoft SQL Server 2017, and PostgreSQL 9.6. Obviously, obtained scripts require adjustments of data types and, possibly some other constraints, before being deployed to DBMS servers.
The software tool performance was tested using different amounts of entities and attributes written in business rules (Table 6).
These results were also depicted in a chart (Fig. 12) of processing time (in sec.) and SQL scripts size (in LOC) growth as the number of attributes increases.
The exploratory data analysis measures [
The statistical results (see Table 7) demonstrate the minimum processing time of 3 entities and 8 attributes is 0.04 sec. with the produced 22 lines of SQL code. The maximum processing time of 3000 entities and 8000 attributes is 39.13 sec. with the produced 22000 lines of SQL code.
The obtained results (see Table 7) also show the processing time of 25% of business rules is below 0.17 sec. for the input data below 19 entities and 50 attributes, and the output data below 138 lines of SQL code. The processing time of another 25% of business rules is above 5.87 sec. for the input data above 525 entities and 1400 attributes, and the output data above 3850 lines of SQL code.
The remaining 50% of business rules have the median processing time of 1.08 sec. for the input data of 105 entities and 280 attributes, and the output data of 770 lines of SQL code.
The statistical analysis of performed experiments proves the proposed computational technology and software tool are capable of processing large volumes of business rules and generate SQL DDL statements for various popular DBMS in reasonable time. The performance measurements were done on the medium-tier workstation with the Intel Core i5-10210U processing unit of 1.60GHz frequency, 16 GB memory, and 64-bit Windows 10 operating system.
The software tool is developed as the command-line utility, which takes the input business rules from a text file and generates the output database schema creation scripts according to the proposed computational technology (Fig. 6). The developed software tool is capable of generating SQL scripts compatible with different popular DBMS using various SQL data types supported by these database management systems (see Table 5).
In this study, we considered the most popular relational DBMS: Oracle, Microsoft SQL Server,
MySQL, and PostgreSQL [
The proposed technique has several limitations. The current implementation of the proposed idea
assumes the usage of only controlled language statements with a restricted structure, defined for fact
business rules by equation (1). Also, the detection of relationships between entities could fail if do not
mention primary and foreign keys as common entity attributes explicitly. However, it is a common
practice to name key attributes as we consider in our approach – among 166 databases present in the
“Spider” dataset [
In this study, we considered the problem of database script generation from natural language text descriptions given as controlled language structures – fact business rules. Fact business rules are brief and precise statements that describe entities of a certain domain. Thus, we formalized their processing using the approach based on a deterministic finite-state machine (Fig. 1). However, extracted entities and attributes are still not sufficient for database design. Therefore, we have proposed an algorithm for relationship detection using the identification of common attributes among different entities (Fig. 2). This algorithm is based on basic concepts of relational databases – primary and foreign keys. To suggest data types for each of the detected attributes, we used the algorithm based on association rules learning (Fig. 4). In addition, we used the other algorithm (Fig. 5) based on naming conventions of attributes to cover those attribute names did not mentioned in the training data set for association rules learning. The proposed computational technology includes the DFSM, proposed algorithms, and the SQL code generation toolkit, and describes the interaction of all of the mentioned components.
In the future, the proposed computational technology should be augmented by algorithms that can detect constraints in business rules (such as null arguments, unique indexes, allowed value ranges for columns, etc.). Also, a web-based software solution should be developed for a better user experience.