Preface
This book is about information systems, focusing on information modeling and relational database systems. It is written primarily for data modelers and database practitioners as well as students of computer science or information management. It should also be useful to anyone wishing to formulate the information structure of business domains in a way that can be readily understood by humans yet easily implemented on computers. In addition, it provides a simple conceptual framework for understanding what database systems really are, and a thorough introduction to SQL and other key topics in data management.
A major part of this book deals with fact-oriented modeling, a conceptual modeling approach that views the world in terms of simple facts about objects and the roles they play. Originating in Europe, fact-orientation is today used worldwide and comes in many flavors, including the Semantics of Business Vocabulary and Business Rules (SBVR) approach adopted in 2007 by the Object Management Group. The version of fact-orientation described in this book is second generation Object-Role Modeling (ORM 2), and is based on extensions to NIAM (Natural-language Information Analysis Method).
Two other popular notations for information modeling are Entity-Relationship (ER) diagrams and Unified Modeling Language (UML) class diagrams. For conceptual information analysis, the ORM method has several advantages over the ER and UML approaches. For example, ORM models can be easily verbalized and populated for validation with domain experts, they are more stable under changes to the business domain, and they typically capture more business rules in diagram form.
However ER diagrams and UML class diagrams are good for compact summaries, and their structures are closer to the final implementation, so they also have value. Hence the coverage includes chapters on data modeling in ER and UML, and indicates how ER and UML data models can be easily generated from ORM models.
To make the text more approachable to the general reader with an interest in databases, the language is kept simple, and a formalized, mathematical treatment is deliberately avoided. Where necessary, relevant concepts from elementary logic and set theory are discussed prior to their application. Most of the material in this book has been class tested in courses to both industry and academia, and the basic ORM method has been taught successfully even at the high school level. The content is modularized, so that instructors wishing to omit some material may make an appropriate selection for their courses.
The first chapter motivates the study of conceptual modeling, and briefly compares the ORM, ER, and UML approaches. It also includes an historical and structural overview of information systems. Chapter 2 provides a structural background, explaining the conceptual architecture of, and development frameworks for, information systems. It introduces a number of key concepts that are dealt with more thoroughly in later chapters, and should be read in full by the reader with little or no database experience.
Chapter 3 is fundamental. Following an overview of conceptual modeling language criteria and the ORM Conceptual Schema Design Procedure (CSDP), this chapter covers the first three steps of the CSDR The first step (verbalizing familiar examples in terms of elementary facts) may seem trivial, but it should not be rushed, as it provides the foundation for the model. The rest of this chapter covers the basic graphical notation for fact types, and then offers guidance on how to classify objects into types and identify information that can be arithmetically derived.
Chapter 4 begins the task of specifying constraints on the populations of fact types. The most important kind of constraint (the uniqueness constraint) is considered in detail. Then some checks on the elementarily of the fact types are discussed. This chapter also introduces the join and projection operations at the conceptual level—the relational version of these operations is important in the later work on relational databases.
Chapter 5 covers mandatory role constraints, including a check for detecting information that can be logically derived. Reference schemes are then examined in some depth. Some of the more complex reference schemes considered here could be skipped in a short course. The CSDP steps covered so far are then reviewed by applying them in a case study, and the logical derivation check is then considered.
Chapter 6 covers value, set comparison (subset, equality, and exclusion), and sub-typing constraints. Section 6.6 deals with advanced aspects of subtyping—though important in practice, the material in this section could be skimmed over in a first reading.
Chapter 7 deals with the final step of the conceptual schema design procedure. Less common constraints are considered (e.g., occurrence frequencies and ring constraints), and final checks are made on the design. Sections 7.3-7.5 are somewhat advanced, and could be skipped in a short course.
Chapter 8 discusses the Entity Relationship (ER) approach, starting with Chen’s original notation then moving on to the three most popular notations in current use: the Barker ER notation, the Information Engineering notation, and the IDEF1X notation (actually a hybrid of ER and relational notations). Comparisons with ORM are included along the way.
Chapter 9 examines the use of UML class diagrams for data modeling, including a detailed comparison with ORM. Business rule constructs in ORM with no graphic counterpart in UML are identified and then captured in UML using user-defined constraints or notes.
Chapter 10 considers several advanced aspects of information modeling, such as join constraints, historical fact types, collection types, open/closed world semantics, and higher-order types. The discussion of deontic rules and nominalization is fundamental to understanding the SBVR flavor of fact-orientation. This chapter is technically the most challenging in the book, and could be skipped in an introductory course.
Chapter 11 describes how a conceptual model may be implemented in a relational database system. The first three sections are fundamental to understanding how a conceptual schema may be mapped to a relational schema. Section 11.4 considers advanced mapping aspects, and could be omitted in a short course.
Chapter 12 provides a foundational introduction to relational databases and SQL queries. Section 12.1 covers relational algebra—although not used as a practical query language, the algebra is important for understanding the basic relational operations supported by SQL. Section 12.2 provides an overview of how the relational model of data compares with data models adopted by some relational database management systems. Sections 12.3-12.14 cover the main features of SQL, with attention to the SQL-89, SQL-92, SQL: 1999, SQL:2003, and SQL:2008 standards, and some popular dialects.
Chapter 13 discusses further aspects of SQL (e.g., data definition, triggers, and stored procedures), the use of other languages such as XML in conjunction with SQL, and introduces some practical issues such as security, metadata and concurrency.
Chapter 14 discusses how and when to transform one schema into another schema at the same level (conceptual or logical). Sections 14.1-14.4 examine the notion of conceptual schema equivalence, and ways in which conceptual schemas may be reshaped. As one application of this theory, section 14.5 specifies a procedure for optimizing a database design by performing conceptual transformations before mapping. Section 14.6 provides a concise coverage of normalization theory, including some new insights. Section 14.7 briefly considers denormalization and low-level optimization. Sections 14.8-14.9 illustrate the role of conceptual optimization in database re-engineering, and conclude with a discussion of data migration and query transformation. Sections 14.4, 14.5, 14.7, 14.8, and 14.9 are of an advanced nature and may be skipped in a short course. In a very short course, the whole chapter could be skipped.
Chapter 15 broadens the treatment of information systems analysis by examining behavioral aspects of business using process and state models. The fundamental concepts underlying business processes and workflows are explained, including popular graphical notations, process patterns, and process standards. Some ways of integrating behavioral models with information models are also considered.
Chapter 16 examines other modeling aspects and trends. Topics covered include data warehousing, conceptual query languages, schema abstraction mechanisms, further design aspects, ontologies and the semantic web, post-relational databases (e.g. object databases and object-relational databases) and metamodeling. Though these topics are important and interesting, they could be omitted in a short course.
In line with the ORM method, this text adopts a “cookbook” approach, with plenty of diagrams and examples. Each chapter begins with a brief overview, and ends with a chapter summary of the major points covered, with chapter notes to provide fine points and further references. One of the major features of the book is its large number of exercises, which have been thoroughly class-tested. A bibliography of all cited references is included at the end of the book, where you will also find glossaries of technical symbols and terms for ORM, ER, and UML (class diagrams only). A comprehensive index provides easy access to explanations of technical topics.
For readers familiar with the previous edition of this book, the major differences are now summarized. The coverage of ORM and UML has substantially updated to cover their latest versions (ORM 2 and UML 2), which necessitated the redrawing of almost all diagrams in the earlier edition. Whole new chapters have been added (Advanced Modeling Issues, Using Other Database Objects, and Process and State Modeling), as well as new chapter sections (e.g. ontologies and the semantic web). All previous chapters have been substantially revised, with several topics covered in greater depth, and there are many new exercises. The new content has led to a much larger book, which now has two coauthors, with Terry Halpin responsible for Chapters 1-11, 14, 16, most of Chapter 12, and part of Chapter 13, and Tony Morgan responsible for Chapter 15, part of Chapter 12, and most of Chapter 13.
U.S. spelling is used throughout the book. U.S. punctuation rules have also been used, except for quoted items, where item separators and sentence terminators (e.g., commas and periods) appear after, rather than just before, closing quotes.