Figure 7: Case 1 (a) my names relvar
Figure 9: Case 2 (a) my names relvar
Figure 11: Case 3 (a) my names relvar
Figure 13: Case 4 (a) my names relvar
7.3.2 Cartesian Product
Figure 15: Case 1 product of my names and your names
The Cartesian product for case 1 shown in Figure 15 is the correct result for Tables 14, 15, and 16.
Figure 16: Case 2 product of my names and your names
The Cartesian product for case 2 shown in Figure 16 is the correct result for Tables 17, 18, and 19.
Figure 17: Case 3 product of my names and your names
The Cartesian product for case 3 shown in Figure 17 is the correct result for Tables 20, 21, and 22.
Figure 18: Case 4 product of my names and your names
The Cartesian product for case 4 shown in Figure 18 is correct the result for Tables 25, 24, and 25.
7.3.3 Set Union
MyKU is verified correct for set union using the first three test cases. The fourth test case fails to correctly remove duplicate rows. The solution to this problem is described with Figure 22.
Figure 19: Case 1 set union of my names and your names
Figure 20: Case 2 set union of my names and your names
Figure 21: Case 3 set union of my names and your names
Figure 22: Case 4 set union of my names and your names
The set union for case 4 shown in Figure 22 is not the correct result for Table 29. MyKU failed to correctly create the set union using first, mi, and last columns because it must have a key to connect the UNKNOWN rows to MISSING rows. To ensure that the key is available, the query rewrite logic adds the key to any projection of unknown and missing if it is not included. In this case “Hugh Darwen” is not identified as a duplicated row because each row has a unique key. Correcting this problem requires the fix for the duplicate removal problem
7.3.4 Project
The test cases for projection rely on set union as an intermediate result. The rows in the derived union of tables are created before columns are projected and duplicate rows are not removed from the results. MyKU fails to correctly project attributes from an intermediate set union for the reasons described in sections 7.2.2 and 7.2.3. Additional explanation of the duplicate removal problem for set union can be found with Figure 22.
Figure 23: Case 1 project from my names union your names
The projection of attributes from my names for case 1 shown in Figure 23 is not the correct result for Table 30. MyKU did not remove the duplicate rows from tables result UNKNOWN and result MISSING where PK is equal to 7.
Figure 24: Case 2 project from my names union your names
The projection of attributes from my names for case 2 shown in Figure 24 is not the correct result for Table 31. MyKU did not remove the duplicate rows from tables result KNOWN where mi is ’D’ or result UNKNOWN and
Figure 25: Case 3 project from my names union your names
The projection of attributes from my names for case 3 shown in Figure 25 is not the correct result for Table 32. MyKU did not remove the duplicate rows from tables result UNKNOWN and result MISSING where PK is equal to 7.
Figure 26: Case 4 project from my names union your names
The projection of attributes from my names for case 4 shown in Figure 26 is not the correct result for Table 33. MyKU did not remove the duplicate rows from tables result UNKNOWN and result MISSING where PK is equal to 7.
7.3.5 Restrict
MyKU is verified correct for row restriction for test cases one and three. MyKU failed on test case two in which the restriction was to be applied to each table in a derived KNOWN/UNKNOWN relvar. MyKU failed on test case four which required the standard SQL rewritten from extended SQL to include a subquery on the KNOWN and UNKNOWN tables.
Figure 27: Case 1 restrict of middle initial ’F’
The restriction shown for case 1 in Figure 27 is the correct result for Table 38.
Figure 28: Case 1 restrict of middle initial maybe ’F’
The restriction for case 1 shown in Figure 28 is an example of the MAYBE modifier in the KNOWN/UNKNOWN model matching applicable, but missing middle initials. This is the correct result from chapter 6 for test case 1 in Table 39.
Figure 29: Case 1 restrict of middle initial ’F’ or maybe ’F’
The restriction for case 1 shown in Figure 29 is an example of query that combines exact matches and maybe-matches in the KNOWN/UNKNOWN model. This is the correct result from chapter 6 for test case 1 in Table 40.
MyKU failed to correctly restrict the Cartesian product of my names and your names to rows where middle initials in the product are equal as shown for test case 2 in Tables 41, 42, and 43. In this case the match criteria must be applied to each com- ponent of a derived intermediate result relvar. While SQL can be written to do this specific query, MyKU does not support the necessary recursive query rewrite capa- bility to create a correct result. The fix for the recursive querying (subqueries) (see section 7.2.2) is planned for future work in section 10.1.1 and requires implementing the KNOWN/UNKNOWN model at a lower level within the DBMS.
Figure 30: Case 3 restrict of my names union your names on equal and maybe
The restriction shown for case 3 in Figure 30 is the correct result for Table 44. The query select a row by known first and last names or those whose middle initial may be ’J’.
CHAPTER 8 Feasibility Study
The feasibility study evaluates user perception and understanding of missing data and its representation. First, standard SQL and the KNOWN/UNKNOWN SQL extensions were introduced and explained in a tutorial using hands-on examples (see Appendices I and J). Next, the users executed a series of database queries from a script (see Appendix K). This script recorded query results and answers to questions about the results. The final script questions solicited participant opinions about the clarity of the KNOWN/UNKNOWN model and alternative results presentations. The goal was to evaluate the KNOWN/UNKNOWN model’s ability to represent missing data in a meaningful way that makes it understandable and solves the problem of providing adequate information about missing data.
The Virginia Commonwealth University (VCU) Institution Review Board (IRB) evaluated the study proposal and determined it to be exempt from federal regu- lations requiring documented informed consent. Participation was optional, risks and benefits were clear, and participants were allowed to leave the study when they chose.
8.1 Participant recruitment
Study participants were offered an SQL tutorial and a chance to participate in a research study. Recruitment efforts included a brief presentation to undergraduate computer science and information system classes where flyers were distributed pro- moting SQL training for those willing to assist with database research (see Appendix H). Flyers were posted on bulletin boards in the School of Engineering and the Busi- ness School. Fifty-five students voluntarily attended one of three SQL tutorials and thirty-six participated in this study.
There was a two-fold purpose for the SQL tutorial. First to attract feasibility study participants. Secondly to ensure sufficient proficiency with SQL to successfully participate in the study.