Home // DBKDA 2011, The Third International Conference on Advances in Databases, Knowledge, and Data Applications // View article

A Field Analysis of Relational Database Schemas in Open-source Software

Authors:
Fabien Coelho
Alexandre Aillos
Samuel Pilot
Shamil Valeev

Keywords: open-source software; database quality survey; automatic schema analysis; relational model; SQL

Abstract:
The relational schemas of 407 open-source projects storing their data in MySQL or PostgreSQL databases are investigated by querying the standard information schema, looking for various issues. These SQL queries are released as the Salix free software. As it is fully relational and relies on standards, it may be installed in any compliant database to help improve schemas. The overall quality of the surveyed schemas is poor: a majority of projects have at least one table without any primary key or unique constraint to identify a tuple; data security features such as referential integrity or transactional back-ends are hardly used; projects that advertise supporting both databases often have missing tables or attributes. PostgreSQL projects have a better quality compared to MySQL projects, and it is even better for projects with PostgreSQL-only support. However, the difference between both databases is mostly due to MySQL-specific issues. An overall predictor of bad database quality is that a project chooses MySQL or PHP, while good design is found with PostgreSQL and Java. The few declared constraints allow to detect latent bugs, that are worth fixing: more declarations would certainly help unveil more bugs. Our survey also suggests some features of MySQL and PostgreSQL as particularly error-prone. This first survey on the quality of relational schemas in open-source software provides a unique insight in the data engineering practice of these projects.

Pages: 9 to 15

Copyright: Copyright (c) IARIA, 2011

Publication date: January 23, 2011

Published in: conference

ISSN: 2308-4332

ISBN: 978-1-61208-115-1

Location: St. Maarten, The Netherlands Antilles

Dates: from January 23, 2011 to January 28, 2011