search based testing of relational schema integrity
play

Search-Based Testing of Relational Schema Integrity Constraints - PowerPoint PPT Presentation

Aug 03, 2023 •32 likes •1.82k views

Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems Gregory M. Kapfhammer 1 & Phil McMinn 2 & Chris J. Wright 2 1 Allegheny College, USA 2 University of Sheffield, UK Sixth IEEE

  1. Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema INSERT ✗ Relational Database Management System Schema Schema State E-commerce The Relational Schema is Working Correctly Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  2. Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema INSERT ✗ Relational Database Management System Schema Schema State State E-commerce The Relational Schema is Working Correctly Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  3. Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema Relational Database Management System Schema State E-commerce The Relational Schema is Not Working Correctly The Relational Schema is Working Correctly Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  4. Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema ✓ INSERT ✗ Relational Database Management System Schema State E-commerce The Relational Schema is Not Working Correctly The Relational Schema is Working Correctly Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  5. Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema ✓ INSERT ✗ Relational Database Management System Schema Schema State E-commerce The Relational Schema is Not Working Correctly The Relational Schema is Working Correctly Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  6. Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema ✓ INSERT ✗ Relational Database Management System Schema Schema State State E-commerce The Relational Schema is Not Working Correctly The Relational Schema is Working Correctly Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  7. Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema ✗ INSERT ✓ Relational Database Management System Schema State E-commerce The Relational Schema is Not Working Correctly The Relational Schema is Working Correctly Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  8. Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema ✗ INSERT ✓ Relational Database Management System Schema Schema State E-commerce The Relational Schema is Not Working Correctly The Relational Schema is Working Correctly Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  9. Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema ✗ INSERT ✓ Relational Database Management System Schema Schema State State E-commerce The Relational Schema is Not Working Correctly The Relational Schema is Working Correctly Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  10. Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema SELECT ✓ Relational Database Management System Schema Schema State State E-commerce The Relational Schema is Not Working Correctly The Relational Schema is Working Correctly Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  11. Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema SELECT ✓ SELECT ✓ Relational Database Management System RESULT ✗ Schema Schema State State E-commerce The Relational Schema is Not Working Correctly The Relational Schema is Working Correctly Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  12. Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema Not working correctly! SELECT ✓ SELECT ✓ Relational Database Management System RESULT ✗ Schema Schema State State E-commerce The Relational Schema is Not Working Correctly The Relational Schema is Working Correctly Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  13. Introduction Testing Technique Empirical Study Conclusion Challenges Need for Relational Schema Testing The Data Warehouse Institute reports that North American organizations experience a $611 billion annual loss due to poor data quality Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  14. Introduction Testing Technique Empirical Study Conclusion Challenges Need for Relational Schema Testing The Data Warehouse Institute reports that North American organizations experience a $611 billion annual loss due to poor data quality Scott W. Ambler argues that the “virtual absence” of database testing — the validation of the contents, schema, and functionality of the database — is the primary cause of this loss Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  15. Introduction Testing Technique Empirical Study Conclusion Challenges Need for Relational Schema Testing The Data Warehouse Institute reports that North American organizations experience a $611 billion annual loss due to poor data quality Scott W. Ambler argues that the “virtual absence” of database testing — the validation of the contents, schema, and functionality of the database — is the primary cause of this loss This paper presents SchemaAnalyst , a search-based system for testing the complex integrity constraints in relational schemas Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  16. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  17. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); The highlighted integrity constraints determine what data is valid Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  18. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); The highlighted integrity constraints determine what data is valid Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  19. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); The highlighted integrity constraints determine what data is valid Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  20. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); The highlighted integrity constraints determine what data is valid Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  21. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas CREATE TABLE FlightAvailable ( FLIGHT ID CHAR(6) NOT NULL, SEGMENT NUMBER INT NOT NULL, FLIGHT DATE DATE NOT NULL, ECONOMY SEATS TAKEN INT, BUSINESS SEATS TAKEN INT, FIRSTCLASS SEATS TAKEN INT, PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER), FOREIGN KEY(FLIGHT ID, SEGMENT NUMBER) REFERENCES Flights(FLIGHT ID, SEGMENT NUMBER) ); The highlighted integrity constraints determine what data is valid Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  22. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas CREATE TABLE FlightAvailable ( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , FLIGHT DATE DATE NOT NULL , ECONOMY SEATS TAKEN INT, BUSINESS SEATS TAKEN INT, FIRSTCLASS SEATS TAKEN INT, PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER), FOREIGN KEY(FLIGHT ID, SEGMENT NUMBER) REFERENCES Flights(FLIGHT ID, SEGMENT NUMBER) ); The highlighted integrity constraints determine what data is valid Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  23. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas CREATE TABLE FlightAvailable ( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , FLIGHT DATE DATE NOT NULL , ECONOMY SEATS TAKEN INT, BUSINESS SEATS TAKEN INT, FIRSTCLASS SEATS TAKEN INT, PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , FOREIGN KEY(FLIGHT ID, SEGMENT NUMBER) REFERENCES Flights(FLIGHT ID, SEGMENT NUMBER) ); The highlighted integrity constraints determine what data is valid Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  24. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas CREATE TABLE FlightAvailable ( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , FLIGHT DATE DATE NOT NULL , ECONOMY SEATS TAKEN INT, BUSINESS SEATS TAKEN INT, FIRSTCLASS SEATS TAKEN INT, PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , FOREIGN KEY(FLIGHT ID, SEGMENT NUMBER) REFERENCES Flights(FLIGHT ID, SEGMENT NUMBER) ); The highlighted integrity constraints determine what data is valid Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  25. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas Defect: The schema does not contain the correct primary key! Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  26. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); Defect: The schema does not contain the correct primary key! Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  27. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas CREATE TABLE FlightAvailable ( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , FLIGHT DATE DATE NOT NULL , ECONOMY SEATS TAKEN INT, BUSINESS SEATS TAKEN INT, FIRSTCLASS SEATS TAKEN INT, PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , FOREIGN KEY(FLIGHT ID, SEGMENT NUMBER) REFERENCES Flights(FLIGHT ID, SEGMENT NUMBER) ); Defect: The schema does not contain the correct primary key! Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  28. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas CREATE TABLE FlightAvailable ( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , FLIGHT DATE DATE NOT NULL , ECONOMY SEATS TAKEN INT, BUSINESS SEATS TAKEN INT, FIRSTCLASS SEATS TAKEN INT, PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , FOREIGN KEY(FLIGHT ID, SEGMENT NUMBER) REFERENCES Flights(FLIGHT ID, SEGMENT NUMBER) ); Question: What kind of INSERT (s) will reveal this defect? Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  29. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas INSERT INTO Flights VALUES(’UA20’, 1, ... ) ✓ Question: What kind of INSERT (s) will reveal this defect? Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  30. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas INSERT INTO Flights VALUES(’UA20’, 1, ... ) ✓ INSERT INTO Flights VALUES(’UA20’, 2, ... ) ✗ Question: What kind of INSERT (s) will reveal this defect? Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  31. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas INSERT INTO Flights VALUES(’UA20’, 1, ... ) ✓ INSERT INTO Flights VALUES(’UA20’, 2, ... ) ✗ Explanation: A flight with two different segments is no longer allowed! Question: What kind of INSERT (s) will reveal this defect? Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  32. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas SchemaAnalyst automatically generates these INSERT s and this data! INSERT INTO Flights VALUES(’UA20’, 1, ... ) ✓ INSERT INTO Flights VALUES(’UA20’, 2, ... ) ✗ Explanation: A flight with two different segments is no longer allowed! Question: What kind of INSERT (s) will reveal this defect? Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  33. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Search-Based Testing with SchemaAnalyst Test Suite Test Suites Generator Representation Schema Test suites Generator Mutants Mutation and Analysis Scores Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  34. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Search-Based Testing with SchemaAnalyst Test Suite Test Suites Generator Representation Schema Test suites Generator Mutants Mutation and Analysis Scores Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  35. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Search-Based Testing with SchemaAnalyst Test Suite Test Suites Generator Representation Schema Test suites Generator Mutants Mutation and Analysis Scores Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  36. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Search-Based Testing with SchemaAnalyst Test Suite Test Suites Generator Representation Schema Test suites Generator Mutants Mutation and Analysis Scores Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  37. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Goals and Stages of Test Data Generation Goal of test data generation? Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  38. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Goals and Stages of Test Data Generation Goal of test data generation? INSERT INTO T 1 VALUES(1, Jan-08-99, ... ) ✓ Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  39. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Goals and Stages of Test Data Generation Goal of test data generation? INSERT INTO T 1 VALUES(1, Jan-08-99, ... ) ✓ INSERT INTO T 1 VALUES(1, Jan-08-99, ... ) ✗ Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  40. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Goals and Stages of Test Data Generation Goal of test data generation? INSERT INTO T 1 VALUES(1, Jan-08-99, ... ) ✓ INSERT INTO T 1 VALUES(1, Jan-08-99, ... ) ✗ INSERT INTO T n VALUES(true, ’L-20’, ... ) ✓ INSERT INTO T n VALUES(false, ’L-1’, ... ) ✗ Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  41. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Goals and Stages of Test Data Generation CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  42. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Goals and Stages of Test Data Generation CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); Stage 1: Generate rows of data to satisfy the integrity constraints Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  43. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Goals and Stages of Test Data Generation CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); Stage 1: Generate rows of data to satisfy the integrity constraints Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  44. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Goals and Stages of Test Data Generation CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  45. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Goals and Stages of Test Data Generation CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); Stage 2: Generate rows of data to negate a constraint Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  46. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Goals and Stages of Test Data Generation CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); Stage 2: Generate rows of data to negate a constraint Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  47. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Goals and Stages of Test Data Generation CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); A fitness function computes a numeric value minimized by search Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  48. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Goals and Stages of Test Data Generation CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); Data’s fitness is closer to zero when nearer to a primary key value Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  49. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Goals and Stages of Test Data Generation CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); Types, primary and foreign keys, UNIQUE , NOT NULL , and CHECK Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  50. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Goals and Stages of Test Data Generation CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); See the paper for more details about the computation of fitness Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  51. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V i Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  52. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  53. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i V k Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  54. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i V k Use the defaults to form the initial values of the INSERT variables Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  55. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i V k Use exploratory moves to determine the correct direction for search Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  56. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i V k Use exploratory moves to determine the correct direction for search Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  57. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i V k Use exploratory moves to determine the correct direction for search Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  58. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i V k Use exploratory moves to determine the correct direction for search Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  59. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i V k Use exploratory moves to determine the correct direction for search Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  60. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i V k Use exploratory moves to determine the correct direction for search Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  61. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i V k Use pattern moves to accelerate the improvements in fitness Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  62. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i V k Use pattern moves to accelerate the improvements in fitness Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  63. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i V k Use pattern moves to accelerate the improvements in fitness Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  64. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i V k Use pattern moves to accelerate the improvements in fitness Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  65. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i V k Use pattern moves to accelerate the improvements in fitness Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  66. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i V k Use pattern moves to accelerate the improvements in fitness Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  67. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i V k Use pattern moves to accelerate the improvements in fitness Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  68. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i V k Use pattern moves to accelerate the improvements in fitness Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  69. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i V k AVM terminates when the fitness is zero or an exploration cycle fails Use pattern moves to accelerate the improvements in fitness Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  70. Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method V j V i V k Restart AVM with random column values when an exploration cycle fails AVM terminates when the fitness is zero or an exploration cycle fails Use pattern moves to accelerate the improvements in fitness Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  71. Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Mutation Operators for Schemas CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  72. Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Mutation Operators for Schemas CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); Use mutation analysis to assess the adequacy of INSERT s and values Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  73. Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Mutation Operators for Schemas CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); Primary Keys : Remove, replace, and add column operators Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

  74. Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Mutation Operators for Schemas CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL , SEGMENT NUMBER INT NOT NULL , ORIGINAL AIRPORT CHAR(3), DEPART TIME TIME, DEST AIRPORT CHAR(3), ARRIVE TIME TIME, MEAL CHAR(1), PRIMARY KEY(FLIGHT ID, SEGMENT NUMBER) , CHECK(MEAL IN (’B’, ’L’, ’D’, ’S’)) ); Primary Keys : Remove, replace, and add column operators Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Recommend

Translating an ER diagram to a relational schema Given an ER diagram, we can look for a relational

Translating an ER diagram to a relational schema Given an ER diagram, we can look for a relational

Inf1-DA 20102011 I: 36 / 117 Translating an ER diagram to a relational schema Given an ER diagram, we can look for a relational schema that closely approximates the ER design. The translation is approximate because it is not always feasible

755 views • 16 slides
Translation from SQL into the relational algebra Consider the following relational schema:

Translation from SQL into the relational algebra Consider the following relational schema:

Translation from SQL into the relational algebra Consider the following relational schema: Student(snum, sname, major, level, age) Class(name, meets at, room, fid) Enrolled(snum, cname) Faculty(fid, fname, deptid) Task Translate

257 views • 13 slides
Relational Schema Design Goal of relational schema design is to avoid anomalies and redundancy

Relational Schema Design Goal of relational schema design is to avoid anomalies and redundancy

Relational Schema Design Goal of relational schema design is to avoid anomalies and redundancy Update anomaly: one occurrence of a fact is changed, but not all occurrences Deletion anomaly: valid fact is lost when a tuple is deleted

1.18k views • 101 slides
DISCUSSION DTD to relational schema 5min XML is powerful when there is an agreement among

DISCUSSION DTD to relational schema 5min XML is powerful when there is an agreement among

2/24/2009 XML DTD Semi-structured Schema for XML SGML E.g. Relational Databases for Querying XML Emerging as a [ * ] = zero or more Documents: standard [ + ] = one or more Limitations and

621 views • 6 slides
The Relational Data Model Lecture 6 1 Outline Relational Data Model Functional

The Relational Data Model Lecture 6 1 Outline Relational Data Model Functional

The Relational Data Model Lecture 6 1 Outline Relational Data Model Functional Dependencies Logical Schema Design Reading Chapter 8 2 1 The Relational Data Model Relational Physical Data Schema storage Modeling Have

484 views • 30 slides
A .NET-based Test-Data Generator for Combinatorial Grammar- and Schema-based Testing Vadim

A .NET-based Test-Data Generator for Combinatorial Grammar- and Schema-based Testing Vadim

A .NET-based Test-Data Generator for Combinatorial Grammar- and Schema-based Testing Vadim Zaytsev with: Ralf L ammel (VU), Wolfram Schulte (MSR) 14 April 2004 Grammar ware grammars grammar-dependent software In this project:

450 views • 17 slides
Optimization of Logical Queries Task: Consider the following relational schema: Emp(eid, did,

Optimization of Logical Queries Task: Consider the following relational schema: Emp(eid, did,

Optimization of Logical Queries Task: Consider the following relational schema: Emp(eid, did, sal, hobby) Dept(did, dname, floor, phone) Finance(did, budget, sales, expenses) For the following SQL statement: 1. Translate the query

917 views • 48 slides
Schema Refinement and Normal Forms [R&G] Chapter 19 CS4320 1 The Evils of Redundancy

Schema Refinement and Normal Forms [R&G] Chapter 19 CS4320 1 The Evils of Redundancy

Schema Refinement and Normal Forms [R&G] Chapter 19 CS4320 1 The Evils of Redundancy Redundancy is at the root of several problems associated with relational schemas: redundant storage, insert/delete/update anomalies Integrity

801 views • 25 slides
Schema Refinement and Normal Forms CS430/630 Lecture 16 Slides based on Database Management

Schema Refinement and Normal Forms CS430/630 Lecture 16 Slides based on Database Management

Schema Refinement and Normal Forms CS430/630 Lecture 16 Slides based on Database Management Systems 3 rd ed, Ramakrishnan and Gehrke Why Schema Refinement? We have learnt the advantages of relational tables but how to

360 views • 14 slides
Query by Analogical Examples: Relational Search Using Web Search Engine Indices by: Lohit Jain

Query by Analogical Examples: Relational Search Using Web Search Engine Indices by: Lohit Jain

Query by Analogical Examples: Relational Search Using Web Search Engine Indices by: Lohit Jain (11390) Advisor: Prof Amitabha Mukerjee Motivation Relational search is an effective way to obtain information in an unknown field for users. For

475 views • 16 slides
Schema Refinement and Normal Forms UMass Amherst Feb 14, 2007 Slides Courtesy of R.

Schema Refinement and Normal Forms UMass Amherst Feb 14, 2007 Slides Courtesy of R.

Schema Refinement and Normal Forms UMass Amherst Feb 14, 2007 Slides Courtesy of R. Ramakrishnan and J. Gehrke, Dan Suciu 1 Relational Schema Design name Conceptual ER Model Person buys Product Design price name ssn Relational

348 views • 33 slides
Professional Integrity to Testing & Certification Practitioners Integrity Training for PCSTP

Professional Integrity to Testing & Certification Practitioners Integrity Training for PCSTP

Professional Integrity to Testing & Certification Practitioners Integrity Training for PCSTP Applicants Hong Kong Ethics Development Centre Independent Commission Against Corruption Competence Requirement Integrity Management

574 views • 23 slides
Search Based Test Data Generation for Server-side Web Application Testing Nadia Alshahwan and

Search Based Test Data Generation for Server-side Web Application Testing Nadia Alshahwan and

Search Based Test Data Generation for Server-side Web Application Testing Nadia Alshahwan and Mark Harman CREST Centre University College London Automated web application testing using search based software Engineering (ASE 2011) Hill

588 views • 15 slides
CS411 Database Systems 04: Relational Schema Design Kazuhiro Minami Primary Goal: Minimize

CS411 Database Systems 04: Relational Schema Design Kazuhiro Minami Primary Goal: Minimize

CS411 Database Systems 04: Relational Schema Design Kazuhiro Minami Primary Goal: Minimize Redundancy Basic approach: decompose an original schema into sub-schemas R(A 1 ,,A n ) => S(B 1 ,,B m ) and T(C 1 ,,C k ) such that

593 views • 36 slides
Outline So far, we studied schema design. CS 235: How to manipulate data?

Outline So far, we studied schema design. CS 235: How to manipulate data?

Outline So far, we studied schema design. CS 235: How to manipulate data? Relational algebra Introduction to Databases Elegant theoretical framework Not so elegant in practice SQL Svetlozar Nestorov Relational

603 views • 3 slides
Simple Description of Relational Databases Simple Diagrams A schema is represented by a

Simple Description of Relational Databases Simple Diagrams A schema is represented by a

Simple Description of Relational Databases Simple Diagrams A schema is represented by a networked diagram Nodes represent tables Name of the table labels the node Interior of the node are the name of the attributes Underline

501 views • 22 slides
EXERCISE FROM LAST TIME Translate the following ER Diagram into a relational database schema.

EXERCISE FROM LAST TIME Translate the following ER Diagram into a relational database schema.

EXERCISE FROM LAST TIME Translate the following ER Diagram into a relational database schema. Vehicle(VIN,cost,registration) Option(ID,expiry,cost) TaxDisc(VIN,duration,class) Fitted(VIN,ID) or augment Option() to be

397 views • 12 slides
Paolo Tonella tonella@fbk.eu Web testing Crawler Matteo Biagiola, Filippo Ricca, Paolo Tonella:

Paolo Tonella tonella@fbk.eu Web testing Crawler Matteo Biagiola, Filippo Ricca, Paolo Tonella:

Search Based Testing of Web Applications Paolo Tonella tonella@fbk.eu Web testing Crawler Matteo Biagiola, Filippo Ricca, Paolo Tonella: Search Based Path and Input Data Generation for Web Application Testing . 9th Int. Symposium on Search

645 views • 21 slides
Models@run.time for Object-Relational Mapping Supporting Schema Evolution Sebastian Gtz and

Models@run.time for Object-Relational Mapping Supporting Schema Evolution Sebastian Gtz and

Models@run.time for Object-Relational Mapping Supporting Schema Evolution Sebastian Gtz and Thomas Khn 10th International Workshop on Models@run.time. 29th September 2015, Ottawa, Canada Motivation Context : Enterprise Software following

151 views • 11 slides
Concepts of the Relational Model Relation Schemata An attribute is just a name. A relation

Concepts of the Relational Model Relation Schemata An attribute is just a name. A relation

Concepts of the Relational Model Relation Schemata An attribute is just a name. A relation schema is (formally) just a name R for the schema, together with a set A of attributes. Write R( A ). Example: Let Name , ID_number , and Major

891 views • 12 slides
Aspelmeier, Jeffery E. (1998, March). Working models and the relational schema; Social information

Aspelmeier, Jeffery E. (1998, March). Working models and the relational schema; Social information

Aspelmeier, Jeffery E. (1998, March). Working models and the relational schema; Social information processing as a link between parent-child and peer relationships. Talk presented at the 1998 Kent Psychology Forum, April 26-29, 1998. In this

590 views • 19 slides
Schema Matching in a Large Scale Schema Matching in a Large Scale Personal Schema Based Querying

Schema Matching in a Large Scale Schema Matching in a Large Scale Personal Schema Based Querying

Schema Matching in a Large Scale Schema Matching in a Large Scale Personal Schema Based Querying Personal Schema Based Querying Marko Smiljani , Maurice van Keulen, Willem Jonker Dutch Dutch-Belgian Database Day Belgian Database Day -

442 views • 25 slides
MongoDB By Bharath Subramanyam Relational Databases started to become popular in the 80s and

MongoDB By Bharath Subramanyam Relational Databases started to become popular in the 80s and

MongoDB By Bharath Subramanyam Relational Databases started to become popular in the 80s and have been widely used since then Properties of Relational Databases- Relational Fixed Schema Databases High Level Query Language (SQL)

468 views • 25 slides
Search-based Testing of Procedural Programs: Iterative Single-Target or Multi-Target Approach?

Search-based Testing of Procedural Programs: Iterative Single-Target or Multi-Target Approach?

Search-based Testing of Procedural Programs: Iterative Single-Target or Multi-Target Approach? Simone Scalabrino Giovanni Grano Dario Di Nucci Rocco Oliveto Andrea De Lucia The overall cost of testing has been estimated at being at

568 views • 39 slides

More recommend