Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Smelly Relations: Measuring and Understanding Database Schema Quality

Smelly Relations: Measuring and Understanding Database Schema Quality

Presented at ICSE 2018 SEIP track in Gothenburg.

Tushar Sharma

May 31, 2018
Tweet

More Decks by Tushar Sharma

Other Decks in Programming

Transcript

  1. Smelly Relations: Measuring and Understanding Database Schema Quality Tushar Sharma,

    Marios Fragkoulis, Stamatia Rizou, Magiel Bruntink, Diomidis Spinellis This work is funded by SENECA project under Marie-Skłodowska Curie Actions Innovative Training Networks ITN-EID. Grant agreement number 642954.
  2. Code Smells …certain structures in the code that suggest (sometimes

    they scream for) the possibility of refactoring. - Kent Beck
  3. Database smells Database smells are the characteristics of database code,

    database system, or stored data that indicate violation of the recommended best practices and potentially affect the quality of the software system in a negative way. Classification • Schema smells • Query smells • Data smells <!>
  4. Overview of the study Research questions Results DbDeo Catalog of

    database schema smells <!> <!> 2568 open-source and 357 industrial repositories Developers’ survey
  5. Database schema smells Compound attribute When a column is used

    to store a non-atomic attribute <!> <!> Id Title ISBN Authors … Book 123 Refactoring.. 0128013974 Girish S., Ganesh S., Tushar S. …
  6. Database schema smells Index abuse When indexes are used poorly.

    • Missing indexes • Insufficient indexes • Unused indexes <!> <!>
  7. Database schema smells 1. Compound attribute 2. Adjacency list 3.

    Superfluous key 4. Missing constraints 5. Metadata as data 6. Polymorphic association 7. Multicolumn attribute 8. Clone table 9. Values in attribute definition 10. Index abuse 11. God table 12. Meaningless name 13. Overload attribute names <!> <!>
  8. Developers’ survey • Online survey to understand developers’ perspective about

    the significance of database smells. • Presented database smells and asked them to rate based on their importance and usefulness 0 3 6 9 12 15 18 21 24 27 0 1 2 3 4 5-10 11-20 / >10 >20 / - Software development experience in years #Database applications developed Received 52 complete responses
  9. 0 5 10 15 20 25 30 35 40 45

    50 CA AL SK MC MD PA MA CT VA IA GT MN OA Respondents Don’t know Recommended practice Neither a smell nor a recommended practice Database schema smell Both a smell and a recommended practice depending on the context Clearly marked smells More context- sensitive
  10. Mining Repositories Attributes Industry OSS Repositories (initial) 840 16,057 Repositories

    with SQL statements 357 2,568 Files 2,559,984 3,297,932 LOC 220,489,273 409,155,497 SELECT 51,652 74,096 CREATE TABLE 18,907 50,682 INSERT 74,416 66,830 UPDATE 10,454 29,002 CREATE INDEX 7,152 10,798
  11. RQ1. Occurrence patterns of database smells Smells Occurrences Avg. smell

    density I OSS I OSS CA 5,517 7,966 0.04 0.04 AL 733 297 0.15 0.02 GT 4,428 5,507 0.44 0.24 VA 85 326 0.00 0.02 MD 944 1,003 0.16 0.09 MA 1,624 3,137 0.10 0.07 CT 101 3,704 0.00 0.05 OA 1,814 7,300 0.20 0.21 IA 12,643 9,475 1.25 1.76
  12. RQ1. Occurrence patterns of database smells Smells Occurrences Avg. smell

    density I OSS I OSS CA 5,517 7,966 0.04 0.04 AL 733 297 0.15 0.02 GT 4,428 5,507 0.44 0.24 VA 85 326 0.00 0.02 MD 944 1,003 0.16 0.09 MA 1,624 3,137 0.10 0.07 CT 101 3,704 0.00 0.05 OA 1,814 7,300 0.20 0.21 IA 12,643 9,475 1.25 1.76 Most frequently occurring smell Index abuse
  13. RQ1. Occurrence patterns of database smells Smells Occurrences Avg. smell

    density I OSS I OSS CA 5,517 7,966 0.04 0.04 AL 733 297 0.15 0.02 GT 4,428 5,507 0.44 0.24 VA 85 326 0.00 0.02 MD 944 1,003 0.16 0.09 MA 1,624 3,137 0.10 0.07 CT 101 3,704 0.00 0.05 OA 1,814 7,300 0.20 0.21 IA 12,643 9,475 1.25 1.76 Adjacency list prone to occur more in industrial projects OSS projects report more Clone table
  14. RQ2. Size and smell density Smell density • Number of

    database smells per ten SQL statements • LOC vs smell density • ⍴ = 0.2420 (p-value = 3.724 × 10-6) for Industry • ⍴ = 0.0006 (p-value = 0.9731) for OSS • Database size vs smell density • ⍴ = 0.7338 (p-value < 2.2 × 10-16) for Industry • ⍴ = 0.6174 (p-value < 2.2 × 10-16) for OSS Strong correlation between database size and smell density.
  15. RQ3. Does the nature of code (type of the application,

    or usage of ORM frameworks) affect the smell density?
  16. RQ3. Nature of code vs smell density Application types •

    Desktop • Mobile • Web 0 0.4 0.8 1.2 1.6 2 2.4 2.8 Desktop (1998, 346) Mobile (40, 2) Web (530, 9) Average smell density Open-source Industry
  17. RQ3. Nature of code vs smell density ORM (Object-Relational Mapping)

    frameworks • 19 well-known frameworks identified 0 0.5 1 1.5 2 2.5 3 Projects using ORM (681, 238) Rest of the projects (1887, 199) Average smell density Open-source Industrial The difference is not statistically significant! Thus, ORM frameworks do not bring immunity from database smells.
  18. 0.00 0.10 0.20 0.30 0.40 0.50 0.60 0.70 0.80 CT

    AL GT VA MD MA CT OA IA Average co-occurrence Open-source Industry RQ4. Co-occurrence among smells Smells show considerably higher correlations in industrial projects.
  19. Qualitative analysis Overloaded attributes • Attributes, even the primary keys,

    with identical names but with different types. • “ID” is the most popularly used name for a primary key. • > 40% in OSS, ~11% in Industrial code • Up to 13 different types used for “ID”
  20. Qualitative analysis Overloaded attributes and clone tables • Many instances

    of these smells observed in tests or in example code while manual exploration. • It highlights the quality deficit and possibly casual mindset while writing test or example code.
  21. Opportunities • Tool support • Native support within IDEs •

    Training and awareness • Database standards • Database APIs • CHECK clause
  22. DbDeo – Database Smells Detection Tool DbDeo – An open-source

    tool to • extract embedded SQL statements and • detect database schema smells https://github.com/tushartushar/DbDeo