Teaching Database Modeling and Design: Areas of Confusion and Helpful Hints.

Introduction Database design has been an integral part of MIS curricula for decades. This field is wellestablished, with an abundant supply of research papers and textbooks, many of which have undergone several revisions. Yet, lack of clarity and inaccuracies in the presentation of some basic database modeling and design concepts persist in textbooks. This, in turn, creates confusion and difficulty for students, and even for faculty. This paper identifies such problems in several areas and provides helpful hints for faculty and students. Specifically, the paper discusses the practical role of normalization in database design, addresses the confusion in representing attributes with repeating values, discusses how to remove inconsistencies in defining relations and first normal form, simplifies the process of identifying candidate keys to normalize relations, clarifies the conditions under which insertion and deletion anomalies may occur, and sheds light on the confusion in defining weak entities. The discussions here are based on an examination of seventeen database textbooks (Connolly & Begg, 2005; Date, 2004; Elmasri & Navathe, 2007; Frost & Van Slyke, 2006; Gillenson, 2005; Hoffer, Prescott, & McFadden, 2007; Kifer, Bernstein, & Lewis, 2006; Kroenke, 2006; Mannino, 2007; Post, 2005; Pratt & Adamski, 2008; Riccardi, 2003; Rob & Coronel, 2006; Rob & Semaan, 2004; Ullman & Widom, 2008; Umanath & Scamell, 2007; Watson, 2005). Practical Significance of Normalization Because several areas discussed here are related to normalization, we first examine the practical role of normalization and normal forms in designing databases. Database design may use a variety of inputs, including existing/proposed forms, reports, queries and transactions, data stored in spread sheets and legacy systems, and entities/attributes identified from knowledge of the business. Thus, for example, to develop a database for a new inventory control system to replace an existing spreadsheet-based system, the designer might use existing spreadsheets, reports, and additional data items represented by entities, such as vendors and product lines, that are identified from user requirements and knowledge of the business. The Top-down Approach In order to translate the data from various sources to a well-designed database, the top-down approach popularly used in relational database design uses three basic steps: 1) Create a conceptual schema that is represented by entity-relationship models, 2) Create a logical schema by mapping the conceptual schema to relation schemas using mapping rules, and 3) Apply normalization rules to test the goodness of the design and to improve the design, if necessary. In general, if the attributes, entities, and their relationships are identified correctly, the proper application of mapping rules should result in normalized relations. However, because E-R modeling is an intuitive process, errors could occur in identifying entities and their relationships and in applying mapping rules, resulting in un-normalized relations. Consider a segment of an order form for a mail-order nursery, shown in Table 1. Experience with student assignments indicates that inexperienced designers are likely to make mistakes in identifying entities and their relationships. Two common mistakes are: 1) Correctly recognize Customer as an entity, but incorrectly group the rest of the information as a single Order entity with a one-to-many relationship between Customer and Order. Applying the mapping rules to this conceptual schema would result in an un-normalized Order schema with data redundancy: Order (Order#, Date, Cust#, Item#, Description, Qty, Unit Price). 2) Correctly identify all three entities--Customer, Order, and Item--but incorrectly identify the relationship between Order and Item as one-to-many, resulting in an un-normalized Item schema: Item(Item#, Order#, Description, and Unit Price). …