A Practical Guide to Database Design, Second Edition

"Fully updated and expanded from the previous edition, A Practical Guide to Database Design, Second Edition, is intended for those involved in the design or development of a database system or application. It begins by focusing on how to create a logical data model where data is stored "where it belongs." Next, data usage is reviewed to transform the logical model into a physical data model that will satisfy user performance requirements. Finally, it describes how to use various software tools to create user interfaces to review and update data in a database.Organized into 11 chapters, the book begins with an overview of the functionality of database management systems and how they guarantee the accuracy and availability of data. It then describes how to define and normalize data requirements to create a logical data model, then map them into an initial solution for a physical database. The book next presents how to use an industry-leading data modeling tool to define and manage logical and physical data models. After that, it describes how to implement a physical database using either Microsoft Access or SQL Server and how to use Microsoft Access to create windows interfaces to query or update data in tables. The last part of the book reviews software tools and explores the design and implementation of a database using as an example a much more complex data environment for a University. The book ends with a description of how to use PHP to build a web-based interface to review and update data in a database. "--Provided by publisher. Read more... Cover 1 Half Title 2 Title Page 4 Copyright Page 5 Dedication 6 Table of Contents 8 Introduction 14 Author 16 CHAPTER 1: Overview of Databases 18 1.1 WHAT’S A “DATABASE”? 18 1.2 GUARANTEED ACCURACY AND AVAILABILITY OF DATA 19 1.2.1 Atomicity 19 1.2.2 Consistency 20 1.2.3 Isolation 20 1.2.4 Durability 20 1.3 DYNAMIC ALTERATION OF DESIGN 20 1.4 DYNAMIC QUERIES—ANY DATA, ANY TIME 22 1.5 REFERENTIAL INTEGRITY ENFORCEMENT 23 1.6 BACKUP/RECOVERY 25 1.7 FAILOVER 26 1.8 TYPICAL INSTALLATION 27 QUESTIONS 30 REFERENCES 31 CHAPTER 2: Data Normalization 32 2.1 INTRODUCTION 32 2.2 THE LANGUAGE OF NORMALIZATION 33 2.3 CREATING THE ENTITY/ATTRIBUTE LIST 34 2.3.1 The Order Entry Model 35 2.4 CLEANING UP THE ENTITY/ATTRIBUTE LIST 37 2.4.1 Problem Type 1—Synonyms 37 2.4.2 Problem Type 2—Homonyms 37 2.4.3 Problem Type 3—Redundant Information 37 2.4.4 Problem Type 4—Mutually Exclusive Data 38 2.4.5 Problem Type 1—Synonyms 38 2.4.6 Problem Type 2—Homonyms 40 2.4.7 Problem Type 3—Redundant Information 40 2.4.8 Problem Type 4—Mutually Exclusive Data 40 2.5 NORMALIZATION 41 2.5.1 First Normal Form 42 2.5.1.1 Requirement 1—Keys to Create Uniqueness 42 2.5.1.2 Requirement 2—Attributes Can Have Only One Value 43 2.5.2 Second Normal Form 46 2.5.3 Third Normal Form 48 2.6 CREATING THE DATA MODEL 52 2.7 FOURTH NORMAL FORM 54 2.8 FIFTH NORMAL FORM 54 QUESTIONS 56 REFERENCES 57 CHAPTER 3: Database Implementation 58 3.1 LOGICAL TO PHYSICAL DESIGN 58 3.2 USAGE PATH ANALYSIS 59 3.3 TABLE KEY AND COLUMN DATA TYPES 61 3.4 INDEXES 62 3.5 TABLE CREATION 63 3.5.1 Using Microsoft Access 64 3.5.2 Using SQL Server 73 3.5.3 Using Oracle 89 QUESTIONS 90 CHAPTER 4: Normalization and Physical Design Exercise 92 4.1 INTRODUCTION 92 4.2 CREATING THE ENTITY/ATTRIBUTE LIST 92 4.3 MOVING TO THIRD NORMAL FORM 95 4.4 THE PHYSICAL DATA MODEL 105 QUESTIONS 108 CHAPTER 5: The erwin Data Modeling Tool 110 5.1 WHAT IS A DATA MODELING TOOL? 110 5.2 WHY DO I NEED A DATA MODELING TOOL? 110 5.3 REVERSE ENGINEERING 110 5.4 CHANGE MANAGEMENT 111 5.5 DOWNLOAD AND INSTALL ERWIN TRIAL SOFTWARE 112 5.6 CREATE THE UNIVERSITY LOGICAL DATA MODEL 113 5.7 CREATE THE UNIVERSITY PHYSICAL DATA MODEL 126 5.8 CREATE AN SQL SERVER UNIVERSITY DATABASE 131 QUESTIONS 132 REFERENCE 133 CHAPTER 6: Using Microsoft Access 134 6.1 OVERVIEW 134 6.2 MODIFICATIONS TO THE DATABASE DESIGN 135 6.3 LOADING DATA INTO TABLES 135 6.4 CREATING QUERIES 136 6.4.1 Create a Customer-Credit_Card Query 136 6.4.2 Create a Query Using SQL Commands 142 6.4.3 Filtering Query Results 142 6.5 USING FORMS 146 6.5.1 Create a Form to Update Advertised_Items 146 6.5.2 Create a Form to Add a New Customer 158 6.5.3 Generating a Master Screen for Users 163 6.6 GENERATING REPORTS 163 6.6.1 Using Reports to View a Customer Order 163 6.7 DEPLOYING ACCESS FOR A TEAM OF USERS 170 6.7.1 Linking to an SQL Server or Oracle Database 172 6.8 THE ROLE OF PASS-THROUGH QUERIES 172 QUESTIONS 173 CHAPTER 7: Using SQL Server 174 7.1 OVERVIEW 174 7.1.1 Advantages 174 7.1.2 Change Management for SQL Server 174 7.2 DATABASE CREATION/INSTALLATION 175 7.2.1 Installation Planning 175 7.2.2 Software Installation 177 7.3 CREATING DATABASES 187 7.3.1 Create an SQLSvrLogs Database 188 7.3.2 Create the University Database 191 7.3.2.1 Table Definitions 191 7.3.2.2 Creating Indexes 194 7.3.2.3 Index Maintenance 197 7.3.2.4 Referential Integrity Constraints 197 7.4 USER ROLES 201 7.5 AUTHORIZED USERS 203 7.6 BACKUP/RECOVERY 207 7.7 LOADING DATA INTO TABLES 212 7.8 CREATING VIEWS 219 7.9 MANUAL QUERIES AND EDITS 221 7.10 USING STORED PROCEDURES 224 7.11 USING SQL SERVER AGENT 225 QUESTIONS 227 CHAPTER 8: Using Perl to Extract and Load Data 230 8.1 WHY PERL? 230 8.2 PERL VERSUS PYTHON 236 8.3 WINDOWS VERSUS UNIX 236 8.4 REVIEW KEY MATCHING FEATURES 238 8.5 MONITOR SQL SERVER LOGS 239 8.6 MONITORING WINDOWS LOGS 242 8.7 OTHER APPLICATIONS AND USES 243 8.8 LOADING DATA INTO TABLES 244 8.9 SUMMARY 246 QUESTIONS 246 REFERENCE 247 CHAPTER 9: Building User Interfaces 248 9.1 MICROSOFT ACCESS IN A TYPICAL OFFICE ENVIRONMENT 248 9.1.1 General Capabilities 248 9.1.2 Advantages 249 9.1.3 Disadvantages 249 9.2 USE MICROSOFT ACCESS AS GUI 249 9.2.1 General Capabilities 249 9.2.2 Advantages 249 9.2.3 Disadvantages 250 9.3 .NET FRAMEWORK 250 9.3.1 General Capabilities 250 9.3.2 Advantages 250 9.3.3 Disadvantages 251 9.4 PHP 251 9.4.1 General Capabilities 251 9.4.2 Advantages 251 9.4.3 Disadvantages 252 9.5 JAVA 252 9.5.1 General Capabilities 252 9.5.2 Advantages 252 9.5.3 Disadvantages 252 QUESTIONS 252 CHAPTER 10: Creating the University Database Application 254 10.1 CREATE TABLES FOR UNIVERSITY ENVIRONMENT 254 10.2 CREATE RELATIONSHIPS TO ENFORCE REFERENTIAL INTEGRITY 258 10.3 DESIGN A SCREEN TO ADD NEW STUDENTS 261 10.4 CREATE A SCREEN TO ENROLL STUDENTS IN A CLASS 277 10.5 CREATE A SCREEN TO ASSIGN GRADES 285 10.6 CREATE SCREENS TO ENTER MIDTERM AND FINAL GRADES 288 10.7 CREATE A “MAIN” MENU 288 10.8 DEVELOPING APPLICATIONS FOR UNIVERSITY ADMINISTRATORS 289 QUESTIONS 290 CHAPTER 11: PHP Implementation and Use 292 11.1 WHY PHP? 292 11.2 SYSTEM COMPONENTS 293 11.3 DESIGN OF WEB-BASED INTERFACE 296 11.3.1 User Logon Options 297 11.3.2 User Authentication 298 11.3.3 Home Page User Options 300 11.3.4 Review/Check Warning Records 300 11.3.5 Review/Check Error Records 309 11.4 SCRIPT LOGIC 309 11.4.1 Warning Records Logic 309 11.4.2 Error Records Logic 311 QUESTIONS 312 REFERENCES 313 APPENDIX A: WARNING MESSAGES 314 APPENDIX B: ERROR MESSAGES 326 APPENDIX C: UNIVERSITY DDL 338 APPENDIX D: SEARCH FOR TERMS 422 APPENDIX E: SQL SERVER LOG CHECK 424 INDEX 426 Cover Half Title Title Page Copyright Page Dedication Table of Contents Introduction Author CHAPTER 1: Overview of Databases 1.1 WHATâ#x80 #x99 S A â#x80 #x9C DATABASEâ#x80 #x9D ? 1.2 GUARANTEED ACCURACY AND AVAILABILITY OF DATA 1.2.1 Atomicity 1.2.2 Consistency 1.2.3 Isolation 1.2.4 Durability 1.3 DYNAMIC ALTERATION OF DESIGN 1.4 DYNAMIC QUERIESâ#x80 #x94 ANY DATA, ANY TIME 1.5 REFERENTIAL INTEGRITY ENFORCEMENT 1.6 BACKUP/RECOVERY 1.7 FAILOVER 1.8 TYPICAL INSTALLATION QUESTIONS REFERENCES CHAPTER 2: Data Normalization 2.1 INTRODUCTION 2.2 THE LANGUAGE OF NORMALIZATION 2.3 CREATING THE ENTITY/ATTRIBUTE LIST2.3.1 The Order Entry Model 2.4 CLEANING UP THE ENTITY/ATTRIBUTE LIST 2.4.1 Problem Type 1â#x80 #x94 Synonyms 2.4.2 Problem Type 2â#x80 #x94 Homonyms 2.4.3 Problem Type 3â#x80 #x94 Redundant Information 2.4.4 Problem Type 4â#x80 #x94 Mutually Exclusive Data 2.4.5 Problem Type 1â#x80 #x94 Synonyms 2.4.6 Problem Type 2â#x80 #x94 Homonyms 2.4.7 Problem Type 3â#x80 #x94 Redundant Information 2.4.8 Problem Type 4â#x80 #x94 Mutually Exclusive Data 2.5 NORMALIZATION 2.5.1 First Normal Form 2.5.1.1 Requirement 1â#x80 #x94 Keys to Create Uniqueness 2.5.1.2 Requirement 2â#x80 #x94 Attributes Can Have Only One Value 2.5.2 Second Normal Form2.5.3 Third Normal Form 2.6 CREATING THE DATA MODEL 2.7 FOURTH NORMAL FORM 2.8 FIFTH NORMAL FORM QUESTIONS REFERENCES CHAPTER 3: Database Implementation 3.1 LOGICAL TO PHYSICAL DESIGN 3.2 USAGE PATH ANALYSIS 3.3 TABLE KEY AND COLUMN DATA TYPES 3.4 INDEXES 3.5 TABLE CREATION 3.5.1 Using Microsoft Access 3.5.2 Using SQL Server 3.5.3 Using Oracle QUESTIONS CHAPTER 4: Normalization and Physical Design Exercise 4.1 INTRODUCTION 4.2 CREATING THE ENTITY/ATTRIBUTE LIST 4.3 MOVING TO THIRD NORMAL FORM 4.4 THE PHYSICAL DATA MODEL QUESTIONS CHAPTER 5: The erwin Data Modeling Tool5.1 WHAT IS A DATA MODELING TOOL? 5.2 WHY DO I NEED A DATA MODELING TOOL? 5.3 REVERSE ENGINEERING 5.4 CHANGE MANAGEMENT 5.5 DOWNLOAD AND INSTALL ERWIN TRIAL SOFTWARE 5.6 CREATE THE UNIVERSITY LOGICAL DATA MODEL 5.7 CREATE THE UNIVERSITY PHYSICAL DATA MODEL 5.8 CREATE AN SQL SERVER UNIVERSITY DATABASE QUESTIONS REFERENCE CHAPTER 6: Using Microsoft Access 6.1 OVERVIEW 6.2 MODIFICATIONS TO THE DATABASE DESIGN 6.3 LOADING DATA INTO TABLES 6.4 CREATING QUERIES 6.4.1 Create a Customer-Credit_Card Query 6.4.2 Create a Query Using SQL Commands 6.4.3 Filtering Query Results6.5 USING FORMS 6.5.1 Create a Form to Update Advertised_Items 6.5.2 Create a Form to Add a New Customer 6.5.3 Generating a Master Screen for Users 6.6 GENERATING REPORTS 6.6.1 Using Reports to View a Customer Order 6.7 DEPLOYING ACCESS FOR A TEAM OF USERS 6.7.1 Linking to an SQL Server or Oracle Database 6.8 THE ROLE OF PASS-THROUGH QUERIES QUESTIONS CHAPTER 7: Using SQL Server 7.1 OVERVIEW 7.1.1 Advantages 7.1.2 Change Management for SQL Server 7.2 DATABASE CREATION/INSTALLATION 7.2.1 Installation Planning 7.2.2 Software Installation Fully updated and expanded from the previous edition, A Practical Guide to Database Design, Second Edition is intended for those involved in the design or development of a database system or application. It begins by illustrating how to develop a Third Normal Form data model where data is placed "where it belongs". The reader is taken step-by-step through the Normalization process, first using a simple then a more complex set of data requirements. Next, usage analysis for each Logical Data Model is reviewed and a Physical Data Model is produced that will satisfy user performance requirements. Finally, each Physical Data Model is used as input to create databases using both Microsoft Access and SQL Server. The book next shows how to use an industry-leading data modeling tool to define and manage logical and physical data models, and how to create Data Definition Language statements to create or update a database running in SQL Server, Oracle, or other type of DBMS. One chapter is devoted to illustrating how Microsoft Access can be used to create user interfaces to review and update underlying tables in that database as well as tables residing in SQL Server or Oracle. For users involved with Cyber activity or support, one chapter illustrates how to extract records of interest from a log file using PERL, then shows how to load these extracted records into one or more SQL Server "tracking" tables adding status flags for analysts to use when reviewing activity of interest. These status flags are used to flag/mark collected records as "Reviewed", "Pending" (currently being analyzed) and "Resolved". The last chapter then shows how to build a web-based GUI using PHP to query these tracking tables and allow an analyst to review new activity, flag items that need to be investigated, and finally flag items that have been investigated and resolved. Note that the book has complete code/scripts for both PERL and the PHP GUI. Mary Hays worked alone in compiling the 302 entries that make up Female Biography (1803). By contrast, producing a modern, critical edition of the work relied on the expertise of 168 scholars across 18 countries. Essays in this collection focus on the exhaustive research, editorial challenges and innovative responses involved in this project. This book contains a major update to the previous edition. It covers how to implement and manage the Database Management System (DBMS) itself, how to write scripts to extract and load data from source files, and how to develop user interfaces to view and update data within the database.