by
Rod Stephens (
Find this book)
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.
This book does not
focus on actually "creating" the database. The details of database
construction are different for different database tools so, to remain as
generally as useful as possible, this book doesn't concentrate on any
particular database system. You can apply the techniques described here
equally to whatever database tool you use whether it's Access, SQL
Server, Oracle, MySQL, or some other database product.
"Most
database products include free editions that you can use for smaller
projects. For example, SQL Server Express Edition, Oracle Express
Edition, and MySQL Community Server are all free."
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. -- Publisher Marketing
