Tasks selected for the research

Several tasks ranging from ill-defined to well-defined were chosen to explore the applicability of the model supporting dialogues. We tested our model in conceptual database design, logical database design, data normalization and fraction addition. Conceptual database design was discussed in Section 2.2.3. We now discuss logical database design, data normalization and fraction addition in detail.

Logical database design: A database schema is the end result of the conceptual database design process. It is detailed enough to be used by database developers as a blueprint for implementing the database. The information contained in the database schema will be used to define the relations, primary and foreign keys. As there are no database management systems (DBMS) based on conceptual data models, the high-level database schema needs to be translated to a schema supported by the chosen DBMS; this process is known as the logical database design (Elmasri &

82

Navathe, 2010). Logical database design is usually taught in introductory database courses at the undergraduate level, via a series of lectures and paper-based exercises.

Logical database design is a well-defined task: a student is expected to follow the algorithm consisting of seven well-defined steps. Each step in the algorithm maps one concept from the ER diagram by either creating a new relation, or altering previously created relations by adding foreign keys and attributes. The seven steps of the algorithm are as follows:

 Step 1: Map all regular entity types and their simple attributes.  Step 2: Map all weak entity types and their simple attributes.  Step 3: Map all 1:1 relationship types.

 Step 4: Map all 1:N relationship types.  Step 5: Map all M:N relationship types.  Step 6: Map all multivalued attributes.  Step 7: Map all N-ary relationship types.

Even though the algorithm is well-defined and short, students typically find it hard to learn and apply consistently. Therefore, providing adaptive tutorial dialogue support to discuss the errors in student solutions would be beneficial to acquire a deep understanding of this domain.

Data Normalization: Data normalization is the process of refining an existing relational database schema to ensure that all relations are of high quality (Elmasri & Navathe, 2010). Normalization is normally taught in introductory database courses at undergraduate level. Relevant concepts are introduced in a series of lectures followed by paper-based exercises. Data normalization is very theoretical compared to other topics in the area of databases due to the many algorithms and multiple definitions that need to be learnt. As a result, students find it very difficult to gain a robust understanding of normalization (Kung & Tung, 2006; Phillip, 2007). Students are required to have a good understanding of the relational data model, especially various types of keys (primary, candidate, foreign keys and superkeys). They are also expected to understand the concept of functional dependencies, which specify constraints on the values of sets of attributes. The

83

definitions of various normal forms are also important and finally the various algorithms that are required for normalizing relations must be understood (Elmasri & Navathe, 2010).

Data normalization is a well-defined instructional task. Each problem consists of a relation whose schema is provided, and a set of functional dependencies (which does not have to be complete). For example, the student might be given a relation R(A, B, C, D, E) (please note that typically students are not given semantics of the attributes, although that can also be provided) and the following set of functional dependencies:

{A→BC, CD→E, AC→E, B→D, E→AB}

The normalization procedure as implemented in NORMIT (Mitrovic et al., 2012), an ITS that teaches data normalization consists of eleven tasks described below. Please note that we refer to elements of the procedure as tasks rather than steps, as each of them contains a number of actions the student has to perform, including in some cases relatively complex algorithms. Therefore we refer to them as tasks to make it clear that the tasks are relatively complex compared to what is generally assumed by a step in the ITS research.

The first eight tasks are necessary to determine the highest normal form the relation is in. If the relation is not in BCNF, the student needs to apply the relational synthesis algorithm to derive an improved database schema. The relational synthesis algorithm is implemented via tasks 9-11.

1. Identify the candidate keys for the given table. There may be one or more keys depending on the given relations. For example, A, E, BC and CD are the candidate keys for the above problem. To explore various possibilities for candidate keys, the student can compute the closure of a set of attributes. Therefore, finding the closure is a subtask of task 1.

2. Find the closure of a given set of attributes. This task consists of an algorithm that is used in several other tasks, including finding candidate keys. In the above problem, to specify BC as the key for relation R, we need to determine that its closure consists of all attributes of relation R.

3. Identify prime attributes. Prime attributes are those attributes that belong to any candidate keys. In the above problem, A, B, C, D and E are the prime attributes.

4. Simplify functional dependencies (FDs) using the decomposition rule, if necessary. In this task, a functional dependency with more than one attribute on the right-hand side (RHS) is

84

replaced with as many FDs as there are attributes on RHS. Each new FD has the same left- hand side as the starting one, and just a single attribute as its RHS. In the above problem, A → BC would be replaced by the two FDs: A → B and A → C. Similarly E → AB would be replaced by E → A and E → B.

5. Determine the normal forms for the given relation. In this task, the student needs to determine whether the relation is in 1NF, 2NF, 3NF or BCNF. In this step, the expected answer is limited to Yes or No responses.

6. If the student indicated that the relation is not in 2NF, he/she expected to identify FDs that violate that form (i.e. partial FDs).

7. If the student indicated that the relation is not in 3NF, he/she needs to identify FDs that violate that form (i.e. transitive FDs).

8. If the student specified that the relation is not in BCNF, he/she will be asked to identify FDs that violate that form (i.e. FDs which do not contain a superkey on their left hand sides (LHSs).

9. For relations that are not in BCNF, reduce LHS of FDs. This task checks whether some of the attributes on the LHS can be dropped while still having a valid functional dependency. 10.Find a minimal cover, which is a minimal set of FDs that still capture all the necessary

information. In this task, the students need to apply the algorithm for checking whether a FD is redundant (and therefore can be dropped from the minimal cover) or not.

11.Decompose the table using the minimal cover.

Fraction addition: Adding two fractions is an important part of the mathematics curriculum. In order to calculate the sum of two fractions correctly, a series of steps have to be followed sequentially. There are different approaches for fraction addition, but we used an approach consisting of four steps. Initially, the least common denominator (LCD) of the two fractions has to be found. Then the two fractions have to be converted to have LCD as their denominator. The sum of the numerators of the converted fractions becomes the numerator of the resulting fraction, with LCD as its denominator. Finally, the resulting fraction has to be simplified, if possible to produce the final result.

85