A Practical Guide to Database Design

431 Pages·2018·44.94 MB·English

by  Rex Hogan

A Practical Guide to
D atabase D esign
A Practical Guide to
D atabase Design
Second Edition
Rex Hogan
CRC Press
Taylor & Francis Group
6000 Broken Sound Parkway NW, Suite 300
Boca Raton, FL 33487-2742
© 2018 by Taylor & Francis Group, LLC
CRC Press is an imprint of Taylor & Francis Group, an Informa business
No claim to original U.S. Government works
Printed on acid-free paper
International Standard Book Number-13: 978-1-1385-7806-7 (Hardback)
This book contains information obtained from authentic and highly regarded sources. Reasonable efforts have been made
to publish reliable data and information, but the author and publisher cannot assume responsibility for the validity of all
materials or the consequences of their use. The authors and publishers have attempted to trace the copyright holders of all
material reproduced in this publication and apologize to copyright holders if permission to publish in this form has not been
obtained. If any copyright material has not been acknowledged please write and let us know so we may rectify in any future
reprint.
Except as permitted under U.S. Copyright Law, no part of this book may be reprinted, reproduced, transmitted, or utilized
in any form by any electronic, mechanical, or other means, now known or hereafter invented, including photocopying,
microfilming, and recording, or in any information storage or retrieval system, without written permission from the
publishers.
For permission to photocopy or use material electronically from this work, please access www.copyright.com (http://www.
copyright.com/) or contact the Copyright Clearance Center, Inc. (CCC), 222 Rosewood Drive, Danvers, MA 01923, 978-750-
8400. CCC is a not-for-profit organization that provides licenses and registration for a variety of users. For organizations that
have been granted a photocopy license by the CCC, a separate system of payment has been arranged.
Trademark Notice: Product or corporate names may be trademarks or registered trademarks, and are used only for
identification and explanation without intent to infringe.
Library of Congress Cataloging‑in‑Publication Data
Names: Hogan, Rex, 1944- author.
Title: A practical guide to database design / Rex Hogan.
Description: Second edition. | Boca Raton : Taylor & Francis, CRC Press,
2017. | Includes index.
Identifiers: LCCN 2017050960 | ISBN 9781138578067 (hardback : alk. paper)
Subjects: LCSH: Database design.
Classification: LCC QA76.9.D26 H64 2017 | DDC 005.74/3--dc23
LC record available at https://lccn.loc.gov/2017050960
Visit the Taylor & Francis Web site at
http://www.taylorandfrancis.com
and the CRC Press Web site at
http://www.crcpress.com
Visit the eResources at
http://crcpress.com/9781138578067
To my wife Cindy, for always being there for me.
Contents
Introduction, xiii
Author, xv
Chapter 1 ◾ O verview of Databases 1
1.1 WHAT’S A “DATABASE”? 1
1.2 GUARANTEED ACCURACY AND AVAILABILITY OF DATA 2
1.2.1 Atomicity 2
1.2.2 Consistency 3
1.2.3 Isolation 3
1.2.4 Durability 3
1.3 DYNAMIC ALTERATION OF DESIGN 3
1.4 DYNAMIC QUERIES—ANY DATA, ANY TIME 5
1.5 REFERENTIAL INTEGRITY ENFORCEMENT 6
1.6 BACKUP/RECOVERY 8
1.7 FAILOVER 9
1.8 TYPICAL INSTALLATION 10
QUESTIONS 13
REFERENCES 14
Chapter 2 ◾ D ata Normalization 15
2.1 INTRODUCTION 15
2.2 THE LANGUAGE OF NORMALIZATION 16
2.3 CREATING THE ENTITY/ATTRIBUTE LIST 17
2.3.1 Te Order Entry Model 18
2.4 CLEANING UP THE ENTITY/ATTRIBUTE LIST 20
2.4.1 Problem Type 1—Synonyms 20
2.4.2 Problem Type 2—Homonyms 20
vii
viii ◾ Contents
2.4.3 Problem Type 3—Redundant Information 20
2.4.4 Problem Type 4—Mutually Exclusive Data 21
2.4.5 Problem Type 1—Synonyms 21
2.4.6 Problem Type 2—Homonyms 23
2.4.7 Problem Type 3—Redundant Information 23
2.4.8 Problem Type 4—Mutually Exclusive Data 23
2.5 NORMALIZATION 24
2.5.1 First Normal Form 25
2.5.1.1 Requirement 1—Keys to Create Uniquenes 25
2.5.1.2 Requirement 2—Attributes Can Have Only One Value 26
2.5.2 Second Normal Form 29
2.5.3 Tird Normal Form 31
2.6 CREATING THE DATA MODEL 35
2.7 FOURTH NORMAL FORM 37
2.8 FIFTH NORMAL FORM 37
QUESTIONS 39
REFERENCES 40
Chapter 3 ◾ D atabase Implementation 41
3.1 LOGICAL TO PHYSICAL DESIGN 41
3.2 USAGE PATH ANALYSIS 42
3.3 TABLE KEY AND COLUMN DATA TYPES 44
3.4 INDEXES 45
3.5 TABLE CREATION 46
3.5.1 Using Microsof Access 47
3.5.2 Using SQL Server 56
3.5.3 Using Oracle 72
QUESTIONS 73
Chapter 4 ◾ N ormalization and Physical Design Exercise 75
4.1 INTRODUCTION 75
4.2 CREATING THE ENTITY/ATTRIBUTE LIST 75
4.3 MOVING TO THIRD NORMAL FORM 78
4.4 THE PHYSICAL DATA MODEL 88
QUESTIONS 91
Contents  ◾  ix
Chapter 5 ◾ T he erwin Data Modeling Tool 93
5.1 WHAT IS A DATA MODELING TOOL? 93
5.2 WHY DO I NEED A DATA MODELING TOOL? 93
5.3 REVERSE ENGINEERING 93
5.4 CHANGE MANAGEMENT 94
5.5 DOWNLOAD AND INSTALL ERWIN TRIAL SOFTWARE 95
5.6 CREATE THE UNIVERSITY LOGICAL DATA MODEL 96
5.7 CREATE THE UNIVERSITY PHYSICAL DATA MODEL 109
5.8 CREATE AN SQL SERVER UNIVERSITY DATABASE 114
QUESTIONS 15
REFERENCE 16
Chapter 6 ◾ U sing Microsoft Access 117
6.1 OVERVIEW 117
6.2 MODIFICATIONS TO THE DATABASE DESIGN 118
6.3 LOADING DATA INTO TABLES 118
6.4 CREATING QUERIES 119
6.4.1 Create a Customer-Credit_Card Query 119
6.4.2 Create a Query Using SQL Commands 125
6.4.3 Filtering Query Results 125
6.5 USING FORMS 129
6.5.1 Create a Form to Update Advertised_Items 129
6.5.2 Create a Form to Add a New Customer 141
6.5.3 Generating a Master Screen for Users 146
6.6 GENERATING REPORTS 146
6.6.1 Using Reports to View a Customer Order 146
6.7 DEPLOYING ACCESS FOR A TEAM OF USERS 153
6.7.1 Linking to an SQL Server or Oracle Database 155
6.8 THE ROLE OF PASS-THROUGH QUERIES 155
QUESTIONS 156
Chapter 7 ◾ U sing SQL Server 157
7.1 OVERVIEW 157
7.1.1 Advantages 157
7.1.2 Change Management for SQL Server 157