The catalog is HERE. All the queries in the catalog are open-source.
Among other things there are collections that are referenced in the scientific papers:
- Find problems about integrity constraints
- Find problems about base tables
- Find problems about names
- Lexicon bad smells and linguistic antipatterns
The fresh statistics about the catalog content can be seen from HERE.
It is a part of the work that has been published in the papers:
- Eessaar, E., 2020. Automating Detection of Occurrences of PostgreSQL Database Design Problems. In: Databases and Information Systems: 14th International Baltic Conference 2020 (DB&IS 2020), Tallinn, Estonia, June 16–19, 2020. Eds. Robal, T., Haav, H.-M., Penjam, J., Matulevièius, R. Switzerland: Springer International Publishing, ISBN: 978-3-030-57671-4, pp. 276–189. (Communications in Computer and Information Science; 1243) (SpringerLink)
- Presentation video: https://youtu.be/rinVuqJLjbk
- Eessaar, E., 2021. The Usage of Declarative Integrity Constraints in the SQL Databases of Some Existing Software. In: Software Engineering and Algorithms, Vol 1: 10th Computer Science On-line Conference 2021 (CSOC 2021), April 29, - May 2, 2021. Ed. Silhavy, R. Switzerland: Springer International Publishing, ISBN: 978-3-030-77441-7, pp. 375-390. (Lecture Notes in Networks and Systems: 230) (SpringerLink)
- Refers to the collection: Find problems about integrity constraints
- Eessaar, E., 2022. On the Design of Base Tables in the SQL Databases of Some Existing Software. In: Software Engineering Perspectives in Systems, Vol 1: 11th Computer Science On-line Conference 2022 (CSOC 2022), April 26, - April 30, 2022. Ed. Silhavy, R. Switzerland: Springer International Publishing, ISBN: 978-3-031-09069-1, pp. 309-324. (Lecture Notes in Networks and Systems: 501) (2022) (SpringerLink).
- Refers to the collection: Find problems about base tables
- Eessaar, E., 2023. On the Naming of Database Objects in the SQL Databases of Some Existing Software. In: Software Engineering Research in System Science, Vol 1: 12th Computer Science On-line Conference 2023 (CSOC 2023), April 26, - April 29, 2023. Silhavy, R., Silhavy, P. (eds). Switzerland: Springer International Publishing, ISBN: 978-3-031-35311-6, pp. 534-550. (Lecture Notes in Networks and Systems: 722) (2023) (SpringerLink).
- Refers to the collections: Find problems about names, Lexicon bad smells and linguistic antipatterns
Additional materials to the catalog of PostgreSQL system-catalog based queries that are used for getting an overview of the state of the database design or finding the occurrences of specific problems in it.
The files in the project catalog provide examples.
- Example_smelly - SQL statements for creating PostgreSQL database base tables that have a number of different database design problems. The statements are syntactically correct. Quite probably one could even implement a working software on top of these. However, the design has many problems that make it more difficult to understand, learn, use, maintain, and extend the database and software that uses it.
- Example_process - The comments bring out design problems in the tables that are presented in the file Example_smelly. The comments refer to the problems by using the initial database object names (identifiers) from the file Example_smelly. The SQL statements in the file codify refactorings that are needed in the database to fix the problems. The statements are ordered, i.e., they take into account changes that have been made in the database schema with the previous statements.
- Example_cleaned - SQL statements for creating PostgreSQL database base tables where all the problems have been fixed. If one creates tables of Example_smelly and applies changes from Example_process, then the resulting database design is like it is depticted in this file.
- HERE is a result of executing the queries from the collection Find flaws automatically based on the database that is depicted in the file Example_smelly.
- HERE is a result of executing the queries from the collection Find flaws by overview based on the database that is depicted in the file Example_smelly.