Search-based testing of relational schema integrity constraints scross multiple database management systems

Slide 1

Slide 1 text

Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems Gregory M. Kapfhammer1 & Phil McMinn2 & Chris J. Wright2 1Allegheny College, USA 2University of Shefﬁeld, UK Sixth IEEE International Conference on Software Testing, Veriﬁcation and Validation (ICST 2013) Tuesday, March 19, 2013

Slide 2

Slide 2 text

Introduction Testing Technique Empirical Study Conclusion Motivation Databases Are Everywhere! Relational Database Management Systems Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Introduction Testing Technique Empirical Study Conclusion Motivation Databases Are Everywhere! Deployment Locations for Databases Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 5

Slide 5 text

Introduction Testing Technique Empirical Study Conclusion Motivation Databases Are Everywhere! Deployment Locations for Databases Database Application Server Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 6

Slide 6 text

Introduction Testing Technique Empirical Study Conclusion Motivation Databases Are Everywhere! Deployment Locations for Databases Database Application Server Mobile Phone or Tablet Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 7

Slide 7 text

Introduction Testing Technique Empirical Study Conclusion Motivation Databases Are Everywhere! Deployment Locations for Databases Database Application Server Mobile Phone or Tablet Ofﬁce and Productivity Software Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 8

Slide 8 text

Introduction Testing Technique Empirical Study Conclusion Motivation Databases Are Everywhere! Deployment Locations for Databases Database Application Server Mobile Phone or Tablet Ofﬁce and Productivity Software Government Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 9

Slide 9 text

Introduction Testing Technique Empirical Study Conclusion Motivation Databases Are Everywhere! Deployment Locations for Databases Database Application Server Mobile Phone or Tablet Ofﬁce and Productivity Software Government Astrophysics Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 10

Slide 10 text

Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema Relational Database Management System Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 11

Slide 11 text

Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema Relational Database Management System E-commerce Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 12

Slide 12 text

Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema Relational Database Management System E-commerce Schema Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema Relational Database Management System E-commerce Schema State Schema Integrity Constraints PRIMARY KEY Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 16

Slide 16 text

Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema Relational Database Management System E-commerce Schema State Schema Integrity Constraints PRIMARY KEY FOREIGN KEY Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 17

Slide 17 text

Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema Relational Database Management System E-commerce Schema State Schema Integrity Constraints PRIMARY KEY FOREIGN KEY Arbitrary CHECK Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema Relational Database Management System E-commerce Schema State State Relational Components Tables Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 20

Slide 20 text

Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema Relational Database Management System E-commerce Schema State State Relational Components Tables Rows Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 21

Slide 21 text

Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema Relational Database Management System E-commerce Schema State State Relational Components Tables Rows Columns Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 22

Slide 22 text

Introduction Testing Technique Empirical Study Conclusion Challenges Relational Database Schema Relational Database Management System E-commerce Schema State 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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas The highlighted integrity constraints determine what data is valid 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

Slide 45

Slide 45 text

Slide 46

Slide 46 text

Slide 47

Slide 47 text

Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas The highlighted integrity constraints determine what data is valid 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) ); Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 48

Slide 48 text

Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas The highlighted integrity constraints determine what data is valid 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) ); Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 49

Slide 49 text

Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas The highlighted integrity constraints determine what data is valid 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) ); Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 50

Slide 50 text

Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas The highlighted integrity constraints determine what data is valid 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) ); Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

Introduction Testing Technique Empirical Study Conclusion Test Data Generation Defects in Relational Schemas Defect: The schema does not contain the correct primary key! 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

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

Slide 61

Slide 61 text

Slide 62

Slide 62 text

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

Introduction Testing Technique Empirical Study Conclusion Test Data Generation Goals and Stages of Test Data Generation Goal of test data generation? INSERT INTO T1 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

Slide 65

Slide 65 text

Introduction Testing Technique Empirical Study Conclusion Test Data Generation Goals and Stages of Test Data Generation Goal of test data generation? INSERT INTO T1 VALUES(1, Jan-08-99, ... ) INSERT INTO T1 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

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

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

Slide 69

Slide 69 text

Introduction Testing Technique Empirical Study Conclusion Test Data Generation Goals and Stages of Test Data Generation Stage 1: Generate rows of data to satisfy the integrity constraints 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

Slide 70

Slide 70 text

Slide 71

Slide 71 text

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

Slide 72

Slide 72 text

Introduction Testing Technique Empirical Study Conclusion Test Data Generation Goals and Stages of Test Data Generation Stage 2: Generate rows of data to negate a constraint 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

Slide 73

Slide 73 text

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 ﬁtness 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

Slide 74

Slide 74 text

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 ﬁtness 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

Slide 75

Slide 75 text

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

Slide 76

Slide 76 text

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 ﬁtness Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 77

Slide 77 text

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

Slide 78

Slide 78 text

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

Slide 79

Slide 79 text

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

Slide 80

Slide 80 text

Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method Vi Vj Vk 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

Slide 81

Slide 81 text

Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method Vi Vj Vk 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

Slide 82

Slide 82 text

Slide 83

Slide 83 text

Slide 84

Slide 84 text

Slide 85

Slide 85 text

Slide 86

Slide 86 text

Slide 87

Slide 87 text

Introduction Testing Technique Empirical Study Conclusion Test Data Generation Alternating Variable Method Vi Vj Vk Use pattern moves to accelerate the improvements in ﬁtness Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 88

Slide 88 text

Slide 89

Slide 89 text

Slide 90

Slide 90 text

Slide 91

Slide 91 text

Slide 92

Slide 92 text

Slide 93

Slide 93 text

Slide 94

Slide 94 text

Slide 95

Slide 95 text

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

Slide 96

Slide 96 text

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

Slide 97

Slide 97 text

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

Slide 98

Slide 98 text

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 INSERTs and values Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 99

Slide 99 text

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

Slide 100

Slide 100 text

Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Mutation Operators for Schemas Primary Keys: Remove, replace, and add column operators 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

Slide 101

Slide 101 text

Slide 102

Slide 102 text

Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Mutation Operators for Schemas Primary Keys: Remove, replace, and add column operators 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(ORIGINAL AIRPORT, 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

Slide 103

Slide 103 text

Slide 104

Slide 104 text

Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Mutation Operators for Schemas Primary Keys: Remove, replace, and add column operators 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, DEST AIRPORT), 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

Slide 105

Slide 105 text

Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Mutation Operators for Schemas UNIQUE: Handle in a fashion similar to the primary key operator 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

Slide 106

Slide 106 text

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’)) ); NOT NULL: Reverse the status for all non-primary key columns Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 107

Slide 107 text

Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Mutation Operators for Schemas NOT NULL: Reverse the status for all non-primary key columns 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

Slide 108

Slide 108 text

Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Mutation Operators for Schemas NOT NULL: Reverse the status for all non-primary key columns CREATE TABLE Flights( FLIGHT ID CHAR(6) NOT NULL, SEGMENT NUMBER INT NOT NULL, ORIGINAL AIRPORT CHAR(3) NOT NULL, 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

Slide 109

Slide 109 text

Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Mutation Operators for Schemas CHECK: Remove the constraint for each of the checked columns 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

Slide 110

Slide 110 text

Slide 111

Slide 111 text

Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Mutation Operators for 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) ); Foreign Keys: Remove each column from the key Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 112

Slide 112 text

Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Mutation Operators for Schemas Foreign Keys: Remove each column from the key 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) ); Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 113

Slide 113 text

Slide 114

Slide 114 text

Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Calculating the Mutation Score MD = |K ∪ Q| |K ∪ N| Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 115

Slide 115 text

Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Calculating the Mutation Score MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 116

Slide 116 text

Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Calculating the Mutation Score MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 117

Slide 117 text

Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Calculating the Mutation Score MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 118

Slide 118 text

Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Calculating the Mutation Score MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 119

Slide 119 text

Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Calculating the Mutation Score MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 120

Slide 120 text

Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Calculating the Mutation Score MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 121

Slide 121 text

Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Calculating the Mutation Score MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 122

Slide 122 text

Introduction Testing Technique Empirical Study Conclusion Relational Schema Mutation Calculating the Mutation Score MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| MD = |K ∪ Q| |K ∪ N| Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 123

Slide 123 text

Introduction Testing Technique Empirical Study Conclusion Conﬁguration Case Study Schemas Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints BankAccount 2 9 0 1 5 2 0 8 BookTown 23 69 1 0 17 11 0 29 Cloc 2 10 0 0 0 0 0 0 CoffeeOrders 5 20 0 4 9 5 0 18 CustomerOrder 7 32 1 7 27 7 0 42 Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 124

Slide 124 text

Slide 125

Slide 125 text

Slide 126

Slide 126 text

Slide 127

Slide 127 text

Slide 128

Slide 128 text

Slide 129

Slide 129 text

Slide 130

Slide 130 text

Introduction Testing Technique Empirical Study Conclusion Conﬁguration Case Study Schemas Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints DellStore 8 52 0 0 36 0 0 36 Employee 1 7 3 0 0 1 0 4 Examination 2 21 6 1 0 2 0 9 Flights 2 13 1 1 6 2 0 10 FrenchTowns 3 14 0 2 13 0 8 23 Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 131

Slide 131 text

Introduction Testing Technique Empirical Study Conclusion Conﬁguration Case Study Schemas Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints DellStore 8 52 0 0 36 0 0 36 Employee 1 7 3 0 0 1 0 4 Examination 2 21 6 1 0 2 0 9 Flights 2 13 1 1 6 2 0 10 FrenchTowns 3 14 0 2 13 0 8 23 Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 132

Slide 132 text

Introduction Testing Technique Empirical Study Conclusion Conﬁguration Case Study Schemas Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints Inventory 1 4 0 0 0 1 1 2 Iso3166 1 3 0 0 2 1 0 3 JWhoisServer 6 49 0 0 44 6 0 50 NistDML181 2 7 0 1 0 1 0 2 NistDML182 2 32 0 1 0 1 0 2 Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 133

Slide 133 text

Introduction Testing Technique Empirical Study Conclusion Conﬁguration Case Study Schemas Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints Inventory 1 4 0 0 0 1 1 2 Iso3166 1 3 0 0 2 1 0 3 JWhoisServer 6 49 0 0 44 6 0 50 NistDML181 2 7 0 1 0 1 0 2 NistDML182 2 32 0 1 0 1 0 2 Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 134

Slide 134 text

Introduction Testing Technique Empirical Study Conclusion Conﬁguration Case Study Schemas Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints Inventory 1 4 0 0 0 1 1 2 Iso3166 1 3 0 0 2 1 0 3 JWhoisServer 6 49 0 0 44 6 0 50 NistDML181 2 7 0 1 0 1 0 2 NistDML182 2 32 0 1 0 1 0 2 Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 135

Slide 135 text

Introduction Testing Technique Empirical Study Conclusion Conﬁguration Case Study Schemas Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints NistDML183 2 6 0 1 0 0 1 2 NistWeather 2 9 5 0 2 2 0 9 NistXTS748 1 3 1 0 1 0 1 3 NistXTS749 2 7 1 1 3 2 0 7 Person 1 5 1 0 5 1 0 7 Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 136

Slide 136 text

Introduction Testing Technique Empirical Study Conclusion Conﬁguration Case Study Schemas Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints Products 3 9 4 2 5 3 0 14 Residence 2 6 3 1 2 2 0 8 RiskIt 13 56 0 10 15 11 0 36 UnixUsage 8 32 0 7 9 7 0 23 Usda 10 67 0 0 30 0 0 30 Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 137

Slide 137 text

Introduction Testing Technique Empirical Study Conclusion Conﬁguration Case Study Schemas Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints Products 3 9 4 2 5 3 0 14 Residence 2 6 3 1 2 2 0 8 RiskIt 13 56 0 10 15 11 0 36 UnixUsage 8 32 0 7 9 7 0 23 Usda 10 67 0 0 30 0 0 30 Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 138

Slide 138 text

Introduction Testing Technique Empirical Study Conclusion Conﬁguration Case Study Schemas Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints Products 3 9 4 2 5 3 0 14 Residence 2 6 3 1 2 2 0 8 RiskIt 13 56 0 10 15 11 0 36 UnixUsage 8 32 0 7 9 7 0 23 Usda 10 67 0 0 30 0 0 30 Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 139

Slide 139 text

Introduction Testing Technique Empirical Study Conclusion Conﬁguration Case Study Schemas Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints Totals 111 542 27 40 231 68 11 377 Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 140

Slide 140 text

Introduction Testing Technique Empirical Study Conclusion Conﬁguration Data Generation Techniques DBMonster Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 141

Slide 141 text

Introduction Testing Technique Empirical Study Conclusion Conﬁguration Data Generation Techniques DBMonster SchemaAnalyst Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 142

Slide 142 text

Introduction Testing Technique Empirical Study Conclusion Conﬁguration Data Generation Techniques DBMonster SchemaAnalyst HSQLDB SQLite Postgres Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 143

Slide 143 text

Introduction Testing Technique Empirical Study Conclusion Conﬁguration Data Generation Techniques DBMonster SchemaAnalyst HSQLDB SQLite Postgres HSQLDB Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 144

Slide 144 text

Introduction Testing Technique Empirical Study Conclusion Conﬁguration Data Generation Techniques DBMonster SchemaAnalyst HSQLDB SQLite Postgres HSQLDB SQLite Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 145

Slide 145 text

Introduction Testing Technique Empirical Study Conclusion Conﬁguration Data Generation Techniques DBMonster SchemaAnalyst HSQLDB SQLite Postgres HSQLDB SQLite Postgres Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 146

Slide 146 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Constraint Coverage Results Schema AVM (%) DBMonster (%) Flights 100.0 70.0 FrenchTowns 100.0 70.0 Inventory 100.0 75.0 Iso3166 100.0 50.0 JWhoisServer 100.0 50.0 Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 147

Slide 147 text

Slide 148

Slide 148 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Constraint Coverage Results Schema AVM (%) DBMonster (%) NistDML181 100.0 75.0 NistDML182 100.0 50.0 NistDML183 100.0 100.0 NistXTS748 100.0 72.2 NistXTS749 100.0 21.4 Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 149

Slide 149 text

Slide 150

Slide 150 text

Slide 151

Slide 151 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Constraint Coverage Results Schema AVM (%) DBMonster (%) Residence 100.0 62.5 RiskIt 100.0 4.1 Products 96.4 59.3 UnixUsage 97.8 59.3 Usda 100.0 50.0 Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 152

Slide 152 text

Slide 153

Slide 153 text

Slide 154

Slide 154 text

Slide 155

Slide 155 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Quasi-Mutant Results Number of Mutants Hsqldb Postgres SQLite 0 20 40 60 80 100 120 CustomerOrder Non−Quasi Quasi Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 156

Slide 156 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Quasi-Mutant Results Number of Mutants Hsqldb Postgres SQLite 0 50 100 150 JWhoisServer Non−Quasi Quasi Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 157

Slide 157 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Quasi-Mutant Results Number of Mutants Hsqldb Postgres SQLite 0 50 100 150 DellStore Non−Quasi Quasi Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 158

Slide 158 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Summary: Quasi-Mutant Results Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 159

Slide 159 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Summary: Quasi-Mutant Results None Some Some Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 160

Slide 160 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Summary: Quasi-Mutant Results None Some Some Few quasi-mutants means that the mutation scores are good effectiveness indicators Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 161

Slide 161 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Mutation Score Results DBMonster SchemaAnalyst Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 162

Slide 162 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Mutation Score Results DBMonster SchemaAnalyst JWhoisServer DBI=62, MD = 0.7 DBI=300, MD = 0.2 Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 163

Slide 163 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Mutation Score Results DBMonster SchemaAnalyst JWhoisServer DBI=62, MD = 0.7 DBI=300, MD = 0.2 NistDML181 DBI=7, MD = 0.6 DBI=13,650, MD = 0.5 Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 164

Slide 164 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Mutation Score Results DBMonster SchemaAnalyst (0.29, 0.59, 0.65, 0.70, 0.89) (0.0, 0.11, 0.41, 0.52, 0.68) Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 165

Slide 165 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Mutation Score Results DBMonster SchemaAnalyst (0.29, 0.59, 0.65, 0.70, 0.89) (0.0, 0.11, 0.41, 0.52, 0.68) DBMonster crashes for six schemas! CustomerOrder Flights NistDML182 NistXTS748 Person RiskIt Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 166

Slide 166 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Mutation Score Results DBMonster SchemaAnalyst (0.29, 0.59, 0.65, 0.70, 0.89) (0.0, 0.11, 0.41, 0.52, 0.68) DBMonster crashes for six schemas! CustomerOrder Flights NistDML182 NistXTS748 Person RiskIt Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 167

Slide 167 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Mutation Score Results DBMonster SchemaAnalyst (0.29, 0.59, 0.65, 0.70, 0.89) (0.0, 0.11, 0.41, 0.52, 0.68) DBMonster crashes for six schemas! CustomerOrder Flights NistDML182 NistXTS748 Person RiskIt Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 168

Slide 168 text

Slide 169

Slide 169 text

Slide 170

Slide 170 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Mutation Score Results DBMonster SchemaAnalyst SchemaAnalyst’s mutation score is higher than DB- Monster’s for 96% of the schemas (0.29, 0.59, 0.65, 0.70, 0.89) (0.0, 0.11, 0.41, 0.52, 0.68) Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 171

Slide 171 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Efﬁciency Results DBMonster SchemaAnalyst Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 172

Slide 172 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Efﬁciency Results DBMonster SchemaAnalyst (0.41, 1.09, 1.90, 5.07, 36.52) (1.50, 3.01, 5.21, 16.79, 639.93) Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 173

Slide 173 text

Slide 174

Slide 174 text

Introduction Testing Technique Empirical Study Conclusion Results Analysis Efﬁciency Results DBMonster SchemaAnalyst (0.41, 1.09, 1.90, 5.07, 36.52) (1.50, 3.01, 5.21, 16.79, 639.93) SchemaAnalyst exhibits competitive data generation times that are less variable Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 175

Slide 175 text

Introduction Testing Technique Empirical Study Conclusion Summary Important Contributions 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

Slide 176

Slide 176 text

Slide 177

Slide 177 text

Introduction Testing Technique Empirical Study Conclusion Summary Important Contributions This paper presents SchemaAnalyst, a search-based system for testing the complex integrity constraints in relational schemas The empirical study demonstrates that Schema- Analyst’s efﬁciency is competitive with DBMonster’s SchemaAnalyst almost always covers 100% of the constraints in the 25 chosen relational schemas Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 178

Slide 178 text

Introduction Testing Technique Empirical Study Conclusion Summary Important Contributions This paper presents SchemaAnalyst, a search-based system for testing the complex integrity constraints in relational schemas The empirical study demonstrates that Schema- Analyst’s efﬁciency is competitive with DBMonster’s SchemaAnalyst almost always covers 100% of the constraints in the 25 chosen relational schemas SchemaAnalyst’s mutation score is higher than DBMonster’s for 96% of the schemas Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

Slide 179

Slide 179 text

Introduction Testing Technique Empirical Study Conclusion Summary Important Contributions This paper presents SchemaAnalyst, a search-based system for testing the complex integrity constraints in relational schemas The empirical study demonstrates that Schema- Analyst’s efﬁciency is competitive with DBMonster’s SchemaAnalyst almost always covers 100% of the constraints in the 25 chosen relational schemas SchemaAnalyst’s mutation score is higher than DBMonster’s for 96% of the schemas http://www.schemaanalyst.org Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems