Assessing the Occurrence of Blocking Operations in Database Schema Evolution: A Case Study

During the evolution of a database schema, some schema-changing operations (e.g., the “ALTER TABLE” command) require the underlying database management system to lock tables until the opera-tion is finished. We call these schema-changing operations blocking operations. During the execution of blocking operations, a soft-ware application may behave abnormally, varying from a slow page loading to an error caused by a web request taking too long to return. Despite their potential negative impact on important qual-ity attributes, blocking operations have not yet been empirically investigated in the context of software evolution. To fill this gap, we conducted a large industrial case study in the context of a Brazilian software company. We analyzed 1,499 atomic schema-changing operations from a period of 6 years to explore which blocking operations the developers frequently performed during the evolution of the database schema of a target system. The intention behind this case study is better understanding the problem in its original context to outline strategies to correct or mitigate it in the future. Our results show that blocking operations were very common, though not all of them seemed to cause observable downtime periods. We also present some mitigating strategies already in use by the devel-opment team of the target system to cope with blocking operation during software evolution, avoiding their negative impact.

Mapping Common Errors in Entity Relationship Diagram Design of Novice Designers

Data modeling in the context of database design is a challenging task for any database designer, even more so for novice designers. A proper database schema is a key factor for the success of any information systems, hence conceptual data modeling that yields the database schema is an essential process of the system development. However, novice designers encounter difficulties in understanding and implementing such models. This study aims to identify the difficulties in understanding and implementing data models and explore the origins of these difficulties. This research examines the data model produced by students and maps the errors done by the students. The errors were classified using the SOLO taxonomy. The study also sheds light on the underlying reasons for the errors done during the design of the data model based on interviews conducted with a representative group of the study participants. We also suggest ways to improve novice designer's performances more effectively, so they can draw more accurate models and make use of advanced design constituents such as entity hierarchies, ternary relationships, aggregated entities, and alike. The research findings might enrich the data body research on data model design from the students' perspectives.

Combined recommender algorithms for enterprises

The authors describe an approach to the implementation of combined algorithms for recommender systems considered with the example of a separate module of the information system of an enterprise. We propose four groups of criteria, which, using various approaches, allows one to explore and evaluate objects in relation to the problem of developing recommendations. During a research database schema required for the implementation of the algorithm has been developed, as well as the software.

Interface for Composing Queries That Include Subqueries for Complex Databases

Currently, there exist tools for query composition that can be used by users who are not skillful in a DB query language such as SQL. Many tools are difficult to use, and others cannot answer some queries that inexperienced users would like to formulate. This chapter describes the new functionality developed for a query composition interface, which allows inexperienced users to compose queries that involve one, two, or three subqueries without the need to have any knowledge of neither SQL nor the database schema. The experimental tests showed that users could easily compose queries that involve one subquery. Furthermore, the new functionality preserves the domain independence of the previous version of the interface. Additionally, like the earlier version, the latest release offers language independence; that is, it can be configured for other languages similar to Spanish, for example English, French, Italian, and Portuguese.

Oracle 19c, SQL Server 2019, Postgresql 12 and MySQL 8 database systems comparison

This article presents a comparative analysis of four popular database technologies. Commercial Oracle Database and SQL Server systems are compared with open source database management systems: PostgreSQL and MySQL. These systems have been available on the market for over a dozen years. Versions released in 2019 were selected for testing and comparasion. For the purposes of the comparative analysis, a database schema was developed and instantiated. Then, test scenarios were developed. They were prepared on the basis of the most popular operations performed with the use of database systems.

DSP: Schema Design for Non-Relational Applications

The way a database schema is designed has a high impact on its performance in relational databases, which are symmetric in nature. While the problem of schema optimization is even more significant for NoSQL (“Not only SQL”) databases, existing modeling tools for relational databases are inadequate for this asymmetric setting. As a result, NoSQL modelers rely on rules of thumb to model schemas that require a high level of competence. Several studies have been conducted to address this problem; however, they are either proprietary, symmetrical, relationally dependent or post-design assessment tools. In this study, a Dynamic Schema Proposition (DSP) model for NoSQL databases is proposed to handle the asymmetric nature of today’s data. This model aims to facilitate database design and improve its performance in relation to data availability. To achieve this, data modeling styles were aggregated and classified. Existing cardinality notations were empirically extended using synthetically generated queries. A binary integer formulation was used to guide the mapping of asymmetric entities from the application’s conceptual data model to a database schema. An experiment was conducted to evaluate the impact of the DSP model on NoSQL schema production and its performance. A profound improvement was observed in read/write query performance and schema production complexities. In this regard, DSP has significant potential to produce schemas that are capable of handling big data efficiently.