An Alternative Approach to Teaching Database Normalization: A Simple Algorithm and an Interactive e-Learning Tool

Journal of Information Systems Education, Fall 2006 by Kung, Hsiang-Jui, Tung, Hui-Lien

ABSTRACT

The relational data model is an important concept covered in the systems analysis and design course. It has been difficult to motivate students to learn database normalization because they find the subject dry and theoretical. An alternative approach has been developed to give students an easy-to-follow algorithm and an interactive, hands-on e-learning tool. The approach is suitable for database normalization in systems analysis and design and in database management courses. This paper describes the alternative approach and its effectiveness in teaching database normalization. The effectiveness of the approach has been evaluated in an exercise and a survey. The paper shows that the approach reduces error rate and increases students' perceived ease, confidence and performance of the normalization approach.

Keywords: Database Normalization, Relational Data Model, Functional Dependency, Third Normal Form

(ProQuest Information and Learning: ... denotes formula omitted.)

1. INTRODUCTION

Database design is important in business software development since virtually every business application uses a database management system. Databases have to be normalized to the third normal form (3NF) when relational database management systems are used. Databases not normalized to 3NF will stumble upon insertion, deletion, and updating anomalies. Database normalization has been a welldeveloped field since the introduction of Codd's seminal work on normal forms in 1970. Bernstein (1976), Diederich and Milton (1988), Concepcion and Villafuerte (1990), and Rosenthal and Reiner (1994) proposed algorithms and tools to synthesize a normalized database using functional dependencies. Maier (1988) indicated that relational data model theory (normalization) tends to be complex for the average designers. Jarvenpaa and Machesky (1989), Bock and Ryan (1993), and Batra and Antony (1994) showed that the relational data model leads to poor designer performance. The students' poor performance of normalization indicated that teaching normalization is a challenge to IS/IT educators.

The traditional database normalization technique has often relied on the definition of normal forms. Some database textbooks include normalization algorithms to find the canonical cover by removing extraneous attributes of functional dependencies (FDs) and then converting each FD in the canonical cover to a relation/table (Silberschatz, Korth, and Sudarshan, 2002). The normalization algorithms often require extensive programming/algorithm backgrounds that most Information Systems (IS)/Information Technology (IT) students lack. Most systems analysis and design (SA&D) textbooks rely on the definition of normal forms in their coverage of database normalization (Hoffer, George, and Valacich, 2005; Avison and Fitzerald, 2002). A table is in first normal form (INF) if each domain contains simple values. The second normal form (2NF) tables are in INF and non-key attributes depend on the whole key (no partial dependency). A table is in third normal form (3NF) if that table is in 2NF and non-key attributes do not depend on other non-key attribute(s) (no transitive dependency). Applying the traditional normalization, students need to find out which normal form a relation is in. If a table is in the first normal form but not 2NF, students have to remove those attributes (from the 1NF table) that cause partial dependency to create another table/relation. This step will ensure all the tables are in 2NF. If a table is in 2NF but not 3NF, students have to remove those attributes causing transitive dependency to create another table. To master the traditional normalization technique, students have to understand the concepts of partial and transitive dependencies clearly.

Teaching database normalization in IS/IT classes is challenging since neither curriculum includes relational algebra or algorithms. Moreover, normalization requires practice, and students, therefore, have to spend considerable time in order to master the concept and, even then, are often not successful. This paper explores an alternative approach that contains a simple normalization algorithm and an interactive e-learning tool to improve IS/IT students' learning of database normalization. Using this approach, the instructor is able to present and demonstrate to students the normalization steps interactively. The e-learning tool may be accessed at any time via the Internet.

The main objective of this paper is to describe the alternative normalization approach and its effectiveness in teaching and learning about normalization. The remainder of this paper is organized as follows: Section 2 describes the alternative approach, and Section 3 illustrates research design and data collection procedures. The effectiveness of the alternative approach is evaluated and interpreted in Section 4. Retention of the normalization skill is tested in Section 5, and Section 6 concludes the paper.

2. THE ALTERNATIVE NORMALIZATION APPROACH