This book is intended for IT professionals and students who want to
learn how to design, analyze, and understand databases. The
material will benefit those who want a better high-level
understanding of databases such as proposal managers, architects,
project managers, and even customers. The material will also
benefit those who will actually design, build, and work with
databases such as database designers, database administrators, and
programmers. In many projects, these roles overlap so the same
person may be responsible for working on the proposal, managing
part of the project, and designing and creating the database.
This book is aimed at IT professionals and students of all
experience levels. It does not assume that you have any previous
experience with databases or programs that use them. It doesn't
even assume that you have experience with computers. All you really
need is a willingness and desire to learn.
This book explains database design. It tells how to plan a
database's structure so the database will be robust, resistant to
errors, and flexible enough to accommodate a reasonable amount of
future change. It explains how to discover database requirements,
build data models to study data needs, and refine those models to
improve the database's effectiveness.
The book solidifies these concepts by working through a detailed
example that designs a realistic database. Later chapters explain
how to actually build databases using two common database products:
Access 2007 and MySQL.
The book finishes by describing some of the topics you need to
understand to keep a database running effectively such as database
maintenance and security.
This book explains database design. It tells how to determine
what should go in a database and how the database should be
structured to give the best results.
To remain database neutral, the book does not assume you are
using a particular database so you don't need any particular
software or hardware. To work through the Exercises, all you really
need is a pencil and some paper. You are welcome to type solutions
into your computer if you like but you may actually find working
with pencil and paper easier than using a graphical design tool to
draw pictures, at least until you are comfortable with database
design and are ready to pick a computerized design tool.
-
"Goals of Effective Database Design," explains the reasons why
people and organizations use databases. It explains a database's
purpose and conditions that it must satisfy to be useful. This also
describes the basic ACID (Atomicity, Consistency, Isolation,
Durability) and CRUD (Create, Read, Update, Delete) features that
any good database should have. It explains in high-level general
terms what makes a good database and what makes a bad database.
-
"Database Types," explains some of the different types of
databases that you might decide to use. These include flat files,
spreadsheets, hierarchical databases (XML), object databases, and
relational databases. The relational database is one of the most
powerful and most commonly used forms of database so it is the
focus of this book, but it is important to realize that there are
alternatives that may be more appropriate under certain
circumstances. This gives some tips on deciding which kind of
database might be best for a particular project.
-
"Relational Database Fundamentals," explains basic relational
database concepts such as tables, rows, and columns. It explains
the common usage of relational database terms in addition to the
more technical terms that are sometimes used by database theorists.
It describes different kinds of constraints that databases use to
guarantee that the data is stored safely and consistently.
-
"Understanding User Needs," explains how to learn about the
users' needs and gather user requirements. It tells how to study
the users' current operations, existing databases (if any), and
desired improvements. It describes common questions that you can
ask to learn about users' operations, desires, and needs, and how
to build the results into requirements documents and
specifications. This explains what use cases are and tells how to
use them and the requirements to guide database design and to
measure success.
-
"Translating User Needs into Data Models," introduces data
modeling. It explains how to translate the user's conceptual model
and the requirements into other more precise models that define the
database design rigorously. This describes several database
modeling techniques including user-interface models, semantic
object models, entity-relationship diagrams, and relational
models.
-
"Extracting Business Rules," explains how a database can handle
business rules. It explains what business rules are, how they
differ from database structure requirements, and how you can
identify business rules. This explains the benefits of separating
business rules from the database structure and tells how achieve
that separation.
-
"Normalizing Data," explains one of the biggest tools in
database design: normalization. Normalization techniques allow you
to restructure a database to increase its flexibility and make it
more robust. This explains the various forms of normalization,
emphasizing the stages that are most common and important: first,
second, and third normal forms (1NF, 2NF, and 3NF). It explains how
each of these kinds of normalization helps prevent errors and tells
why it is sometimes better to leave a database slightly less
normalized to improve performance.
-
"Designing Databases to Support Software Applications," explains
how databases fit into the larger context of application design and
lifecycle. This explains how later development depends on the
underlying database design. It discusses multi-tier architectures
that can help decouple the application and database design so there
can be at least some changes to either without requiring changes to
the other.
-
"Common Design Patterns," explains some common patterns that are
useful in many applications. Some of these techniques include
implementing various kinds of relationships among objects, storing
hierarchical and network data, recording temporal data, and logging
and locking.
-
"Common Design Pitfalls," explains some common design mistakes
that occur in database development. It describes problems that can
arise from insufficient planning, incorrect normalization, and
obsession with ID fields and performance.
-
"User Needs and Requirements," walks through the steps required
to analyze the users' problem, define requirements, and create use
cases. It describes interviews with fictitious customers that are
used to identify the application's needs and translate them into
database requirements.
-
"Building a Data Model," translates the requirements gathered in
the previous into a series of data models that precisely define the
database's structure. This builds user-interface models,
entity-relationship diagrams, semantic object models, and
relational models to refine the database's initial design. The
final relational models match the structure of a relational
database fairly closely so they are easy to implement.
-
"Extracting Business Rules," identifies the business rules
embedded in the relational model. It shows how to extract those
rules in order to separate them logically from the database's
structure. This makes the database more robust in the face of
future changes to the business rules.
-
"Normalization and Refinement," refines the relational model by
normalizing it. It walks through several versions of the database
that are in different normal forms. It then selects the degree of
normalization that provides a reasonable tradeoff between robust
design and acceptable performance.
-
"Microsoft Access," explains how to build a database with
Microsoft Access 2007. This explains enough to get started and to
use Access to build non-trivial databases. You can use other
versions of Access to work through this, although the locations of
menus, buttons, and other Access features are different in
different versions.
-
"MySQL," explains how to build a database with MySQL. This tells
where to download a free version of MySQL. It explains how to use
the MySQL Command Line Client as well as some useful graphical
tools including MySQL Query Browser and MySQL Workbench.
-
"Introduction to SQL," provides an introduction to SQL
(Structured Query Language). It explains how to use SQL commands to
add, insert, update, and delete data. By using SQL, you can help
insulate a program from the idiosyncrasies of the particular
database product that it uses to store data.
-
"Building Databases with SQL Scripts," explains how to use SQL
scripts to build a database. It explains the advantages of this
technique, such as the ability to create scripts to initialize a
database before performing tests. It also explains some of the
restrictions on this method, such as the fact that the user must
create and delete tables in specific orders to satisfy table
relationships.
-
"Database Maintenance," describes some of the database
maintenance issues that are part of any database application.
Though performing and restoring backups, compressing tables,
rebuilding indexes, and populating data warehouses are strictly not
database design tasks, they are essential to any working
application.
-
"Database Security," explains database security issues. It
explains the kinds of security that some database products provide.
It also explains some additional techniques that can enhance
database security such as using database views to appropriately
restrict the users' access to data.