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

▶
search based testing of relational schema integrity
SMART_READER_LITE
LIVE PREVIEW

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

Aug 03, 2023 32 likes •1.83k 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

slide-1
SLIDE 1

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 Sheffield, UK

Sixth IEEE International Conference on Software Testing, Verification and Validation (ICST 2013) Tuesday, March 19, 2013

slide-2
SLIDE 2

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

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-4
SLIDE 4

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

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

Introduction Testing Technique Empirical Study Conclusion Motivation

Databases Are Everywhere!

Deployment Locations for Databases Database Application Server Mobile Phone

  • r 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

Introduction Testing Technique Empirical Study Conclusion Motivation

Databases Are Everywhere!

Deployment Locations for Databases Database Application Server Mobile Phone

  • r Tablet

Office 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

Introduction Testing Technique Empirical Study Conclusion Motivation

Databases Are Everywhere!

Deployment Locations for Databases Database Application Server Mobile Phone

  • r Tablet

Office 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

Introduction Testing Technique Empirical Study Conclusion Motivation

Databases Are Everywhere!

Deployment Locations for Databases Database Application Server Mobile Phone

  • r Tablet

Office 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

slide-40
SLIDE 40

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

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

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

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

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

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-46
SLIDE 46

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-47
SLIDE 47

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

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

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

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

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

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

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

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

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

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

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 flight 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

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 flight 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

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

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-61
SLIDE 61

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-62
SLIDE 62

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-63
SLIDE 63

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

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

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

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

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

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

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

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-71
SLIDE 71

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

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

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

slide-74
SLIDE 74

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

slide-75
SLIDE 75

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

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

slide-77
SLIDE 77

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

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

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

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

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

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-83
SLIDE 83

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-84
SLIDE 84

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-85
SLIDE 85

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-86
SLIDE 86

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-87
SLIDE 87

Introduction Testing Technique Empirical Study Conclusion Test Data Generation

Alternating Variable Method

Vi Vj Vk

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

slide-88
SLIDE 88

Introduction Testing Technique Empirical Study Conclusion Test Data Generation

Alternating Variable Method

Vi Vj Vk

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

slide-89
SLIDE 89

Introduction Testing Technique Empirical Study Conclusion Test Data Generation

Alternating Variable Method

Vi Vj Vk

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

slide-90
SLIDE 90

Introduction Testing Technique Empirical Study Conclusion Test Data Generation

Alternating Variable Method

Vi Vj Vk

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

slide-91
SLIDE 91

Introduction Testing Technique Empirical Study Conclusion Test Data Generation

Alternating Variable Method

Vi Vj Vk

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

slide-92
SLIDE 92

Introduction Testing Technique Empirical Study Conclusion Test Data Generation

Alternating Variable Method

Vi Vj Vk

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

slide-93
SLIDE 93

Introduction Testing Technique Empirical Study Conclusion Test Data Generation

Alternating Variable Method

Vi Vj Vk

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

slide-94
SLIDE 94

Introduction Testing Technique Empirical Study Conclusion Test Data Generation

Alternating Variable Method

Vi Vj Vk

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

slide-95
SLIDE 95

Introduction Testing Technique Empirical Study Conclusion Test Data Generation

Alternating Variable Method

Vi Vj Vk

Use pattern moves to accelerate the improvements in fitness AVM terminates when the fitness 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

Introduction Testing Technique Empirical Study Conclusion Test Data Generation

Alternating Variable Method

Vi Vj Vk

Use pattern moves to accelerate the improvements in fitness AVM terminates when the fitness 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

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

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

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

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

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-102
SLIDE 102

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

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-104
SLIDE 104

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

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

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

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

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

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

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-111
SLIDE 111

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

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

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-114
SLIDE 114

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

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

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

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

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

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

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

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

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

Introduction Testing Technique Empirical Study Conclusion Configuration

Case Study Schemas

Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints BankAccount 2 9 1 5 2 8 BookTown 23 69 1 17 11 29 Cloc 2 10 CoffeeOrders 5 20 4 9 5 18 CustomerOrder 7 32 1 7 27 7 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

Introduction Testing Technique Empirical Study Conclusion Configuration

Case Study Schemas

Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints BankAccount 2 9 1 5 2 8 BookTown 23 69 1 17 11 29 Cloc 2 10 CoffeeOrders 5 20 4 9 5 18 CustomerOrder 7 32 1 7 27 7 42

Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

slide-125
SLIDE 125

Introduction Testing Technique Empirical Study Conclusion Configuration

Case Study Schemas

Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints BankAccount 2 9 1 5 2 8 BookTown 23 69 1 17 11 29 Cloc 2 10 CoffeeOrders 5 20 4 9 5 18 CustomerOrder 7 32 1 7 27 7 42

Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

slide-126
SLIDE 126

Introduction Testing Technique Empirical Study Conclusion Configuration

Case Study Schemas

Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints BankAccount 2 9 1 5 2 8 BookTown 23 69 1 17 11 29 Cloc 2 10 CoffeeOrders 5 20 4 9 5 18 CustomerOrder 7 32 1 7 27 7 42

Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

slide-127
SLIDE 127

Introduction Testing Technique Empirical Study Conclusion Configuration

Case Study Schemas

Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints BankAccount 2 9 1 5 2 8 BookTown 23 69 1 17 11 29 Cloc 2 10 CoffeeOrders 5 20 4 9 5 18 CustomerOrder 7 32 1 7 27 7 42

Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

slide-128
SLIDE 128

Introduction Testing Technique Empirical Study Conclusion Configuration

Case Study Schemas

Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints BankAccount 2 9 1 5 2 8 BookTown 23 69 1 17 11 29 Cloc 2 10 CoffeeOrders 5 20 4 9 5 18 CustomerOrder 7 32 1 7 27 7 42

Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

slide-129
SLIDE 129

Introduction Testing Technique Empirical Study Conclusion Configuration

Case Study Schemas

Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints BankAccount 2 9 1 5 2 8 BookTown 23 69 1 17 11 29 Cloc 2 10 CoffeeOrders 5 20 4 9 5 18 CustomerOrder 7 32 1 7 27 7 42

Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

slide-130
SLIDE 130

Introduction Testing Technique Empirical Study Conclusion Configuration

Case Study Schemas

Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints DellStore 8 52 36 36 Employee 1 7 3 1 4 Examination 2 21 6 1 2 9 Flights 2 13 1 1 6 2 10 FrenchTowns 3 14 2 13 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

Introduction Testing Technique Empirical Study Conclusion Configuration

Case Study Schemas

Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints DellStore 8 52 36 36 Employee 1 7 3 1 4 Examination 2 21 6 1 2 9 Flights 2 13 1 1 6 2 10 FrenchTowns 3 14 2 13 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

Introduction Testing Technique Empirical Study Conclusion Configuration

Case Study Schemas

Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints Inventory 1 4 1 1 2 Iso3166 1 3 2 1 3 JWhoisServer 6 49 44 6 50 NistDML181 2 7 1 1 2 NistDML182 2 32 1 1 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

Introduction Testing Technique Empirical Study Conclusion Configuration

Case Study Schemas

Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints Inventory 1 4 1 1 2 Iso3166 1 3 2 1 3 JWhoisServer 6 49 44 6 50 NistDML181 2 7 1 1 2 NistDML182 2 32 1 1 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

Introduction Testing Technique Empirical Study Conclusion Configuration

Case Study Schemas

Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints Inventory 1 4 1 1 2 Iso3166 1 3 2 1 3 JWhoisServer 6 49 44 6 50 NistDML181 2 7 1 1 2 NistDML182 2 32 1 1 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

Introduction Testing Technique Empirical Study Conclusion Configuration

Case Study Schemas

Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints NistDML183 2 6 1 1 2 NistWeather 2 9 5 2 2 9 NistXTS748 1 3 1 1 1 3 NistXTS749 2 7 1 1 3 2 7 Person 1 5 1 5 1 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

Introduction Testing Technique Empirical Study Conclusion Configuration

Case Study Schemas

Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints Products 3 9 4 2 5 3 14 Residence 2 6 3 1 2 2 8 RiskIt 13 56 10 15 11 36 UnixUsage 8 32 7 9 7 23 Usda 10 67 30 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

Introduction Testing Technique Empirical Study Conclusion Configuration

Case Study Schemas

Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints Products 3 9 4 2 5 3 14 Residence 2 6 3 1 2 2 8 RiskIt 13 56 10 15 11 36 UnixUsage 8 32 7 9 7 23 Usda 10 67 30 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

Introduction Testing Technique Empirical Study Conclusion Configuration

Case Study Schemas

Schema Tables Columns Checks Foreign keys Not Nulls Primary keys Uniques Total Constraints Products 3 9 4 2 5 3 14 Residence 2 6 3 1 2 2 8 RiskIt 13 56 10 15 11 36 UnixUsage 8 32 7 9 7 23 Usda 10 67 30 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

Introduction Testing Technique Empirical Study Conclusion Configuration

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

Introduction Testing Technique Empirical Study Conclusion Configuration

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

Introduction Testing Technique Empirical Study Conclusion Configuration

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

Introduction Testing Technique Empirical Study Conclusion Configuration

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

Introduction Testing Technique Empirical Study Conclusion Configuration

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

Introduction Testing Technique Empirical Study Conclusion Configuration

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

Introduction Testing Technique Empirical Study Conclusion Configuration

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

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

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-148
SLIDE 148

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

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-150
SLIDE 150

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-151
SLIDE 151

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

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-153
SLIDE 153

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-154
SLIDE 154

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-155
SLIDE 155

Introduction Testing Technique Empirical Study Conclusion Results Analysis

Quasi-Mutant Results

Number of Mutants H s q l d b P

  • s

t g r e s S Q L i t e 2 4 6 8 1 1 2 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

Introduction Testing Technique Empirical Study Conclusion Results Analysis

Quasi-Mutant Results

Number of Mutants H s q l d b P

  • s

t g r e s S Q L i t e 5 1 1 5 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

Introduction Testing Technique Empirical Study Conclusion Results Analysis

Quasi-Mutant Results

Number of Mutants H s q l d b P

  • s

t g r e s S Q L i t e 5 1 1 5 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

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

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

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

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

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

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

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

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

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

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

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-169
SLIDE 169

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-170
SLIDE 170

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%

  • f 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

Introduction Testing Technique Empirical Study Conclusion Results Analysis

Efficiency 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

Introduction Testing Technique Empirical Study Conclusion Results Analysis

Efficiency 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

Introduction Testing Technique Empirical Study Conclusion Results Analysis

Efficiency 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-174
SLIDE 174

Introduction Testing Technique Empirical Study Conclusion Results Analysis

Efficiency 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 competi- tive data genera- tion 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

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

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 efficiency is competitive with DBMonster’s

Kapfhammer, McMinn, and Wright March 19, 2013 Search-Based Testing of Relational Schema Integrity Constraints Across Multiple Database Management Systems

slide-177
SLIDE 177

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 efficiency 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

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 efficiency 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

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 efficiency 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