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 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. Cover Half Title Title Page Copyright Page Dedication Table of Contents Introduction Author CHAPTER 1: Overview of Databases 1.1 WHAT’S A “DATABASE”? 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—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 LIST 2.3.1 The Order Entry Model 2.4 CLEANING UP THE ENTITY/ATTRIBUTE LIST 2.4.1 Problem Type 1—Synonyms 2.4.2 Problem Type 2—Homonyms 2.4.3 Problem Type 3—Redundant Information 2.4.4 Problem Type 4—Mutually Exclusive Data 2.4.5 Problem Type 1—Synonyms 2.4.6 Problem Type 2—Homonyms 2.4.7 Problem Type 3—Redundant Information 2.4.8 Problem Type 4—Mutually Exclusive Data 2.5 NORMALIZATION 2.5.1 First Normal Form 2.5.1.1 Requirement 1—Keys to Create Uniqueness 2.5.1.2 Requirement 2—Attributes Can Have Only One Value 2.5.2 Second Normal Form 2.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 Tool 5.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 Results 6.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 7.3 CREATING DATABASES 7.3.1 Create an SQLSvrLogs Database 7.3.2 Create the University Database 7.3.2.1 Table Definitions 7.3.2.2 Creating Indexes 7.3.2.3 Index Maintenance 7.3.2.4 Referential Integrity Constraints 7.4 USER ROLES 7.5 AUTHORIZED USERS 7.6 BACKUP/RECOVERY 7.7 LOADING DATA INTO TABLES 7.8 CREATING VIEWS 7.9 MANUAL QUERIES AND EDITS 7.10 USING STORED PROCEDURES 7.11 USING SQL SERVER AGENT QUESTIONS CHAPTER 8: Using Perl to Extract and Load Data 8.1 WHY PERL? 8.2 PERL VERSUS PYTHON 8.3 WINDOWS VERSUS UNIX 8.4 REVIEW KEY MATCHING FEATURES 8.5 MONITOR SQL SERVER LOGS 8.6 MONITORING WINDOWS LOGS 8.7 OTHER APPLICATIONS AND USES 8.8 LOADING DATA INTO TABLES 8.9 SUMMARY QUESTIONS REFERENCE CHAPTER 9: Building User Interfaces 9.1 MICROSOFT ACCESS IN A TYPICAL OFFICE ENVIRONMENT 9.1.1 General Capabilities 9.1.2 Advantages 9.1.3 Disadvantages 9.2 USE MICROSOFT ACCESS AS GUI 9.2.1 General Capabilities 9.2.2 Advantages 9.2.3 Disadvantages 9.3 .NET FRAMEWORK 9.3.1 General Capabilities 9.3.2 Advantages 9.3.3 Disadvantages 9.4 PHP 9.4.1 General Capabilities 9.4.2 Advantages 9.4.3 Disadvantages 9.5 JAVA 9.5.1 General Capabilities 9.5.2 Advantages 9.5.3 Disadvantages QUESTIONS CHAPTER 10: Creating the University Database Application 10.1 CREATE TABLES FOR UNIVERSITY ENVIRONMENT 10.2 CREATE RELATIONSHIPS TO ENFORCE REFERENTIAL INTEGRITY 10.3 DESIGN A SCREEN TO ADD NEW STUDENTS 10.4 CREATE A SCREEN TO ENROLL STUDENTS IN A CLASS 10.5 CREATE A SCREEN TO ASSIGN GRADES 10.6 CREATE SCREENS TO ENTER MIDTERM AND FINAL GRADES 10.7 CREATE A “MAIN” MENU 10.8 DEVELOPING APPLICATIONS FOR UNIVERSITY ADMINISTRATORS QUESTIONS CHAPTER 11: PHP Implementation and Use 11.1 WHY PHP? 11.2 SYSTEM COMPONENTS 11.3 DESIGN OF WEB-BASED INTERFACE 11.3.1 User Logon Options 11.3.2 User Authentication 11.3.3 Home Page User Options 11.3.4 Review/Check Warning Records 11.3.5 Review/Check Error Records 11.4 SCRIPT LOGIC 11.4.1 Warning Records Logic 11.4.2 Error Records Logic QUESTIONS REFERENCES APPENDIX A: WARNING MESSAGES APPENDIX B: ERROR MESSAGES APPENDIX C: UNIVERSITY DDL APPENDIX D: SEARCH FOR TERMS APPENDIX E: SQL SERVER LOG CHECK INDEX 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 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.