A Data Model Validation Approach for Relational Database Design Courses

Introduction Several critical steps make up the database life cycle, but data model validation is often overlooked in course projects that teach relational database design. This is unsurprising, because while most database texts stress the importance of validation, few provide an instructional method for performing validation. If a flawed data model is not validated and corrected, SQL queries and the design of forms and reports can be frustrating or even hopeless. This is especially the case if students have failed to include one or more foreign keys, making it impossible to correctly link tables using their data model. When students are assigned a relational database design project they are generally directed to follow a specific approach for design and normalization. They first identify objects and relations, using techniques such as analysis of report requirements, functional analysis, transaction analysis, or scenario analysis. They next develop an entity-relationship diagram, and finally they normalize the resulting design. Before implementing the design through table creation, the model should be validated to insure that the design is complete. The validation should confirm that all of the data items that appear on a form or report correspond to attributes in the entities, and that the entities are correctly linked. This paper presents an instructional method for validating a relational database design in order to insure that the database includes a complete attribute set as well as the requisite links between tables. The method also helps students to better understand how data are retrieved from multiple tables so that they are able to write more complex queries. Literature Review An extensive literature review uncovered few, if any, useful instructional methods for data model validation. Wilson (1986) describes the structure of a database design course, but makes no mention of data model validation. Zamperoni and Lohr-Richter (1993) present a complex validation model based on a quantitative dependency graph, but such an approach is too arcane for most students. Haerder (1978) describes the structure of relationships between entities, but makes no mention of data model validation to insure that all relationships are accounted for. Teorey and Fry (1980) uses a processing matrix in which each transaction and report is represented in terms of the attributes required. It may be possible to adapt this approach for validation, but it is not presented as such. Balouch (1999) makes a brief reference to logical access maps (LAMs). He cites an unnamed source that states that a logical access map "graphically depicts the route of a sequence of actions that act upon the entities and relationships of a data design." It further states that a LAM "provides a useful check that all the attributes required for a particular transaction are present and can be accessed." While the concept sounds similar to the one upon which this paper is based, differing definitions of LAMs exist in the literature and no similar definition could be found. Chundur (2001) not only recognizes the need for validation, but also suggests an approach. He notes that designers must insure that the logical data model supports the transactions required by the user view, and those transactions can be determined from the user's requirements specification. He states that given the ER diagram, the data dictionary and the primary key/foreign entity links, the designer can perform a manual validation. However, he provides no details on how to go about performing such a validation As noted previously, few texts provide a method for data model validation. Kroenke (2003) notes that the most common form of validation is to show the data model to the users and obtain their feedback. While that may accurately reflect real-world practices, textbook cases do not provide real users, leading students to skip validation if no alternate approach is provided. …