Table Of ContentObject-Role Modeling Workbook
Data Modeling Exercises using ORM and NORMA
Object-Role
Modeling
Workbook
Data Modeling Exercises
using ORM and NORMA
Terry Halpin
INTI International University
Technics Publications
New Jersey
Published by:
2 Lindsley Road
Basking Ridge, NJ 07920 USA
http://www.TechnicsPub.com
Cover design by Mark Brye
All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or
mechanical, including photocopying, recording or by any information storage and retrieval system, without written
permission from the publisher, except for the inclusion of brief quotations in a review.
The author and publisher have taken care in the preparation of this book, but make no expressed or implied warranty of
any kind and assume no responsibility for errors or omissions. No liability is assumed for incidental or consequential
damages in connection with or arising out of the use of the information or programs contained herein.
All trademarks are property of their respective owners and should be treated as such.
Copyright © 2016 by Terry Halpin
ISBN, print ed. 9781634621045
ISBN, Kindle ed. 9781634621052
ISBN, ePub ed. 9781634621069
First Printing 2016
Library of Congress Control Number: 2015957065
Table of Contents
Foreword
Preface
1 Review of ORM Basics
1.1 Introduction
1.2 ORM’s Conceptual Schema Design Procedure
1.3 A Worked Example
1.4 Review Exercises
2 Documenting Models
2.1 Generating Model Reports
2.2 Vocabulary Glossaries
3 Relational Mapping
3.1 Overview of ORM’s Relational Mapping Procedure
3.2 Downloading and Viewing a Sample NORMA Model
3.3 Controlling Table and Column Names in NORMA
3.4 Subtype Mapping Options
3.5 Annotating Relational Schemas
4 Modeling Exercises
4.1 Academic Conference
4.2 Course Prerequisites
4.3 Concert Bookings
4.4 British Monarchy
4.5 Malaysia Database
4.6 Nobel Prize Awards
4.7 Academy Awards
5 Transforming and Optimizing Schemas
5.1 Conceptual Schema Transformations
5.2 Conceptual Schema Optimization
5.3 Database Reengineering
5.4 Solar System Exercise
6 Some Data Model Patterns
6.1 Temporal Aspects
6.2 Collection Types
Appendix A: ORM Symbol Glossary
Appendix B: Further Resources
Answers
Index
Foreword
Dr. Herman Balsters
Associate Professor at the Faculty of Economics and Business
University of Groningen, The Netherlands,
and Director of Contracts11
It gives me great personal pleasure to write this foreword, one of the main reasons
being that I think that any book written by Terry Halpin is not only a gem to read,
but is also an important and practically useful book. I have known Terry personally
since I stumbled into an ORM Workshop that was given in Cyprus in 2005. I had
never heard about ORM before and I wanted to find out a bit more.
After having attended the first hour at that workshop, I became an immediate
addict: my interest in ORM and fact-based modelling has never ceased. This not
only has to do with Terry Halpin himself, who I consider as a genius (be it a very
humble one) and a great teacher, but also with the artifacts that he has introduced to
the data modelling community: the ORM language and the NORMA tool. Before
that day in 2005, I had tried very many data modelling languages, and not one of
them really satisfied me at all. After being introduced to ORM, I knew right away
that this was the way to do it.
ORM is not only very expressive, it is also completely accurate; no ambiguities
allowed. ORM’s graphical language and the wonderful NORMA tool provide great
productivity in analyzing and constructing the most complex data models you
could possibly conceive.
I have used ORM both in academia and in business. I have taught hundreds of
students how to model and build databases using Terry’s books and the NORMA
tool. Together with Terry, I have co-authored a number of scientific papers on
ORM, and together we chaired and co-chaired the annual ORM Workshop in the
years 2010-2013. As a professional consultant and designer of information
systems, I have found no other language that is better equipped to do the job than
ORM.
In practice, we come across very many badly designed databases. Inspection of
those databases often quickly reveals that there was no proper data model at all
supporting the database design. These badly designed databases (containing
redundancies, lack of semantics, and full of ambiguities) are, unfortunately, more
the norm than the exception.
Badly designed databases lead to unadaptable, unintegrated, unmanageable and
therefore expensive database implementations. Since databases are at the
cornerstone of almost all software systems, it can be regarded as a shame that we
do not devote more attention to the role of data modeling when it comes to
designing databases.
Luckily we have Terry Halpin and his ORM language to help us shed light on
how to arrive at proper, well-understood, and manageable database designs and
implementations. An important practical aspect of this book is that it systematically
describes how to use the NORMA tool to generate relational database schemas
from ORM models, including a treatment of optimization issues.
This particular book is a real complement to other books on ORM written by
Terry Halpin. One of the main assets of this book is the wonderful collection of
data modeling exercises that Terry has compiled. The exercises are challenging,
interesting, and typically based on real-life examples. Another main asset is the
detailed explanation of how to use the NORMA tool. I’m sure that even advanced
users of ORM and NORMA can benefit greatly from the challenges presented by
the modelling exercises and the modelling skills explained in the book using
NORMA.
In a way, this book is technical. It’s about modelling, and about the difficult
aspects of data modelling that you can encounter when trying to model your
business domain. Data modelling is never easy, and it involves being both careful
and creative. This book shows various pitfalls that you can come across in
modelling, and how to solve them. The carefully selected modelling exercises pose
a challenge even to the most experienced modeller.
The practical side of this book is that it shows how to systematically use the
NORMA tool together with your knowledge of ORM to address these challenges.
Once you have experienced these challenges, this book helps you to become a
better modeller than you were previously. The carefully crafted solutions to the
modeling exercises are described in great detail. As ever, Terry Halpin explains the
material—which is often intrinsically complex—as only the great teachers can: he
uses simple and straightforward language, and his solutions follow a completely
logical sequence of reasoning. I often envy Halpin’s style of writing and
explaining.
Another asset of the book is the introduction of a completely textual modelling
language, which verbalizes the content of ORM diagrams. The verbalizations
offered in the book are given in such a way that non-technical domain experts can
easily read and write fact-based specifications of the domain to be modelled.
Another benefit of these verbalizations is that they offer the possibility to validate
the correctness of the models, since such textual specifications can be easily
understood, and therefore assessed, by non-technical domain experts. One can
easily move back and forth between the verbalizations and their graphical ORM
model counterparts.
It seemed difficult for me to consider a book on ORM that could be a real
complement to all of the other books on ORM that Terry Halpin has written. This
book, however, has convinced me that such a book could be written. Indeed, this is
a workbook on ORM: by working through this book you can become a better,
accomplished data modeller and database designer than you were previously.
I therefore highly recommend this book to any student, teacher, or professional
interested in constructing information systems. The world needs high-quality
information systems, and this book provides an excellent resource to help one
attain that goal.
Preface
This book focuses on Object-Role Modeling (ORM), a fact-based modeling
approach that expresses the information requirements of any business domain
simply in terms of objects that play roles in relationships. All facts of interest are
treated as instances of attribute-free structures known as fact types, where the
relationship may be unary (e.g. Person smokes), binary (e.g. Person was born on Date),
ternary (e.g. Customer bought Product on Date), or longer (e.g. Book in Year in Region sold
NrCopies).
Fact types facilitate natural expression, are easy to populate with examples for
validation purposes, and have greater semantic stability than attribute-based
structures such as those used in Entity Relationship Modeling (ER) or the Unified
Modeling Language (UML).
All relevant facts, constraints and derivation rules are expressed in controlled
natural language sentences that are intelligible to users in the business domain
being modeled. This allows ORM data models to be validated by business domain
experts who are unfamiliar with ORM’s graphical notation. For the data modeler,
ORM’s graphical notation covers a much wider range of constraints than can be
expressed in industrial ER or UML class diagrams, and thus allows rich
visualization of the underlying semantics.
This book is a sequel to my previous book Object-Role Modeling
Fundamentals, and assumes some basic knowledge of the ORM approach as well
as some familiarity with the freeware NORMA tool for designing ORM schemas
and generating relational database schemas. The relevant background on ORM can
be found in my previous book, or in Halpin & Morgan (2008). The relevant back
ground on NORMA can be found in my previous book, or from NORMA labs that
are freely available from websites such as www.orm.net or
www.ORMFoundation.org.
Written in easy-to-understand language, the book illustrates each topic with
simple examples, and explains how to use the NORMA tool to implement the ideas
discussed. Unlike my previous book, this book includes a large number of practical
exercises to promote expertise in the techniques covered, with answers provided to
all the exercise questions. It also digs deeper into various ORM topics discussed in
my previous book as well as exploring further ORM topics not covered there.
Hence, it is intended as a workbook for honing one’s skills in ORM.
Chapter 1 provides a brief review of the ORM’s conceptual schema design
procedure (CSDP), and includes a worked example to illustrate the CSDP as well
as NORMA’s support for automated verbalization and relational mapping. The
chapter concludes with some review exercises to consolidate the ideas discussed.
Chapter 2 discusses in detail how to use the NORMA tool to generate reports
