Database Solutions: A Step by Step Guide to Building Databases / Edition 1
by Thomas Connolly, Carolyn BeggISBN-10: 0201674769
ISBN-13: 9780201674767
Pub. Date: 12/23/1999
Publisher: Addison-Wesley
Database Solutions:A Step-by-Step Guide to Building Databases by Thomas Connolly & Carolyn Begg
Do you carry the world on your shoulders? Does every request for a new business system from Senior Management land on your desk? Are you responsible for designing and creating the databases that keep your business running? Would you like to makeOverview
Database Solutions:A Step-by-Step Guide to Building Databases by Thomas Connolly & Carolyn Begg
Do you carry the world on your shoulders? Does every request for a new business system from Senior Management land on your desk? Are you responsible for designing and creating the databases that keep your business running? Would you like to make the design and build process smoother, quicker and more reliable?- Recipe for disaster:
- Take one RDMS
- Follow instructions on packet
- Recipe for success:
- Take one RDMS n any of the major commercial products will do***Oracle***Informix***SQL Server***Access***Paradox
- Add one thorough reading of Database Solutions if you are an inexperienced database designer, or one recap of the methodology if you are an old hand
- Use the design and implementation frameworks to plan your timetable
- Measure out a common data model that fits your requirements and adapt as necessary
- Weigh up advice and tips from the authors - experienced professionals
- Make life easy. Look in this book and CD-ROM for:
- Step-by-step instructions on designing and building databases
- Advice and tips on building a successful database
- Comprehensive guidance on avoiding pitfalls and traps
- How to create data models using the new design notation, UML (with appendices mapping to Chen's notation and the Crow's Feet notation)
- Shortcuts: 15 sample data models to adapt to your needs, chosen from 7 common business areas, plus SQL scripts for each
- Two full length, coded exampledatabases written on Microsoft Access 97 and Oracle 8
- Evaluation copy of Rational's UML visual modelling tool, Rose.
0201674769B04062001
Product Details
- ISBN-13:
- 9780201674767
- Publisher:
- Addison-Wesley
- Publication date:
- 12/23/1999
- Series:
- Database Solutions Series
- Edition description:
- Older Edition
- Pages:
- 256
- Product dimensions:
- 7.38(w) x 9.08(h) x 0.85(d)
Table of Contents
| Preface | xv | |
| Part 1 | Background | |
| 1 | Introduction | 3 |
| 1.1 | Examples of the use of database systems | 3 |
| 1.2 | Database approach | 5 |
| 1.2.1 | The database | 5 |
| 1.2.2 | The Database Management System (DBMS) | 6 |
| 1.2.3 | Views | 6 |
| 1.2.4 | Components of the DBMS environment | 8 |
| 1.2.5 | DBMS architectures | 8 |
| 1.3 | Functions of a DBMS | 10 |
| 1.4 | Database design | 15 |
| 1.5 | Advantages and disadvantages of DBMSs | 15 |
| 2 | The relational model | 18 |
| 2.1 | What is a data model? | 19 |
| 2.2 | Terminology | 19 |
| 2.2.1 | Relational data structure | 19 |
| 2.2.2 | Properties of relational tables | 22 |
| 2.2.3 | Relational keys | 23 |
| 2.2.4 | Representing relational databases | 25 |
| 2.3 | Relational integrity | 27 |
| 2.3.1 | Nulls | 28 |
| 2.3.2 | Entity integrity | 28 |
| 2.3.3 | Referential integrity | 28 |
| 2.3.4 | Business rules | 29 |
| 2.4 | Relational languages | 29 |
| 3 | The database application lifecycle | 32 |
| 3.1 | The software crisis | 32 |
| 3.2 | The information systems lifecycle | 33 |
| 3.3 | The database application lifecycle | 34 |
| 3.4 | Database planning | 34 |
| 3.5 | System definition | 36 |
| 3.5.1 | User views | 36 |
| 3.6 | Requirements collection and analysis | 37 |
| 3.7 | Database design | 39 |
| 3.8 | DBMS selection | 41 |
| 3.9 | Application design | 41 |
| 3.9.1 | Transaction design | 42 |
| 3.9.2 | User interface design | 42 |
| 3.10 | Prototyping | 43 |
| 3.11 | Implementation | 44 |
| 3.12 | Data conversion and loading | 44 |
| 3.13 | Testing | 45 |
| 3.14 | Operational maintenance | 45 |
| Part 2 | Database analysis and design techniques | |
| 4 | Fact-finding | 51 |
| 4.1 | When are fact-finding techniques used? | 52 |
| 4.2 | What facts are collected? | 52 |
| 4.3 | Fact-finding techniques | 54 |
| 4.3.1 | Examining documentation | 54 |
| 4.3.2 | Interviewing | 54 |
| 4.3.3 | Observing the business in operation | 56 |
| 4.3.4 | Research | 56 |
| 4.3.5 | Questionnaires | 56 |
| 4.4 | The StayHome case study | 58 |
| 4.4.1 | An overview | 58 |
| 4.4.2 | Database planning | 61 |
| 4.4.3 | System definition | 67 |
| 4.4.4 | Requirements collection and analysis | 69 |
| 4.4.5 | Database design | 79 |
| 5 | Entity-Relationship modeling | 81 |
| 5.1 | Entities | 82 |
| 5.2 | Relationships | 83 |
| 5.2.1 | Degree of a relationship | 84 |
| 5.2.2 | Recursive relationships | 85 |
| 5.3 | Attributes | 85 |
| 5.3.1 | Simple and composite attributes | 85 |
| 5.3.2 | Single-valued and multi-valued attributes | 86 |
| 5.3.3 | Derived attributes | 86 |
| 5.3.4 | Keys | 87 |
| 5.4 | Strong and weak entities | 88 |
| 5.5 | Multiplicity constraints on relationships | 89 |
| 5.5.1 | One-to-one (1:1) relationships | 90 |
| 5.5.2 | One-to-many (1:*) relationships | 91 |
| 5.5.3 | Many-to-many (*:*) relationships | 92 |
| 5.5.4 | Multiplicity for non-binary relationships | 93 |
| 5.5.5 | Cardinality and participation constraints | 95 |
| 5.6 | Attributes on relationships | 95 |
| 5.7 | Design problems with ER models | 96 |
| 5.7.1 | Fan traps | 97 |
| 5.7.2 | Chasm traps | 97 |
| 6 | Normalization | 102 |
| 6.1 | Introduction | 103 |
| 6.2 | Data redundancy and update anomalies | 103 |
| 6.2.1 | Insertion anomalies | 104 |
| 6.2.2 | Deletion anomalies | 105 |
| 6.2.3 | Modification anomalies | 105 |
| 6.3 | First normal form (1NF) | 106 |
| 6.4 | Second normal form (2NF) | 107 |
| 6.5 | Third normal form (3NF) | 111 |
| Part 3 | Logical database design | |
| 7 | Overview of the methodology | 119 |
| 7.1 | Introduction to the database design methodology | 119 |
| 7.1.1 | What is a database design methodology? | 120 |
| 7.1.2 | What are the aims of a database design methodology? | 120 |
| 7.1.3 | Why build data models? | 121 |
| 7.1.4 | Critical success factors in database design | 124 |
| 7.2 | Overview of the database design methodology | 124 |
| 8 | Logical database design - Step 1 | 129 |
| Step 1 | Build local logical data model for each view | 130 |
| Step 1.1 | Identify entities | 130 |
| Step 1.2 | Identify relationships | 133 |
| Step 1.3 | Identify and associate attributes with entities or relationships | 137 |
| Step 1.4 | Determine attribute domains | 142 |
| Step 1.5 | Determine candidate and primary key attributes | 143 |
| Step 1.6 | Specialize/Generalize entities (optional step) | 146 |
| Step 1.7 | Remove features not compatible with the relational model | 147 |
| Step 1.8 | Check that model supports user transactions | 154 |
| 9 | Logical database design -- Step 2 | 159 |
| Step 2 | Create and check tables for each local logical data model | 159 |
| Step 2.1 | Create tables for local logical data model | 160 |
| Step 2.2 | Check table structures using normalization | 171 |
| Step 2.3 | Check tables support user transactions | 172 |
| Step 2.4 | Define integrity constraints | 175 |
| Step 2.5 | Review local logical data model with users | 180 |
| 10 | Logical database design - Step 3 | 181 |
| 10.1 | The Business view of StayHome | 182 |
| 10.1.1 | Users' requirements specification | 182 |
| 10.1.2 | Local logical data model | 184 |
| Step 3 | Build and check global logical data model | 184 |
| Step 3.1 | Merge local logical data models into global model | 187 |
| Step 3.2 | Check global logical data model | 194 |
| Step 3.3 | Check for future growth | 195 |
| Step 3.4 | Review global logical data model with users | 196 |
| 11 | Advanced modeling techniques | 197 |
| 11.1 | Specialization/Generalization | 198 |
| 11.1.1 | Superclasses and subclasses | 198 |
| 11.1.2 | Superclass/Subclass relationships | 198 |
| 11.1.3 | Attribute inheritance | 199 |
| 11.1.4 | Specialization process | 200 |
| 11.1.5 | Generalization process | 200 |
| 11.1.6 | Constraints on superclass/subclass relationships | 202 |
| 11.2 | Creating tables to represent specialization/generalization | 204 |
| Part 4 | Physical database design | |
| 12 | Physical database design - Step 4 | 211 |
| 12.1 | Comparison of logical and physical database design | 212 |
| 12.2 | Overview of the physical database design methodology | 213 |
| Step 4 | Translate global logical data model for target DBMS | 214 |
| Step 4.1 | Design base tables for target DBMS | 215 |
| Step 4.2 | Design business rules for target DBMS | 222 |
| 13 | Physical database design - Step 5 | 227 |
| 13.1 | Understanding system resources | 228 |
| 13.2 | Step 5 Design physical representation | 230 |
| Step 5.1 | Analyze transactions | 231 |
| Step 5.2 | Choose file organizations | 237 |
| Step 5.3 | Choose indexes | 240 |
| 13.3 | File organizations and indexes for StayHome with Microsoft Access 97 | 245 |
| 13.3.1 | Guidelines for choosing indexes | 245 |
| 13.3.2 | Indexes for StayHome | 246 |
| 14 | Physical database design - Step 6 | 249 |
| Step 6 | Consider the introduction of controlled redundancy | 249 |
| Step 6.1 | Consider derived data | 251 |
| Step 6.2 | Consider duplicating columns or joining tables together | 253 |
| 15 | Physical database design - Step 7 | 267 |
| Step 7 | Design security mechanisms | 267 |
| Step 7.1 | Design user views | 268 |
| Step 7.2 | Design access rules | 270 |
| 16 | Physical database design - Step 8 | 276 |
| Step 8 | Monitor and tune the operational system | 276 |
| 17 | Sample StayHome queries using SQL and QBE | 279 |
| 17.1 | Introduction to Microsoft SQL and QBE | 279 |
| 17.1.1 | SQL | 279 |
| 17.1.2 | QBE (Query-By-Example) | 281 |
| 17.2 | Sample queries for StayHome | 282 |
| Part 5 | Second worked example | |
| 18 | Perfect Pets - Logical database design | 293 |
| 18.1 | Perfect Pets | 293 |
| 18.1.1 | Data requirements | 293 |
| 18.1.2 | Transaction requirements | 296 |
| 18.2 | Using the logical database design methodology | 297 |
| 19 | Perfect Pets - Physical database design | 313 |
| 19.1 | Using the physicial database design methodology | 313 |
| What next? | 339 | |
| Appendices | ||
| A | Alternative data modeling notations | 343 |
| A.1 | ER modeling using the Chen notation | 343 |
| A.2 | ER modeling using the Crow's Feet notation | 343 |
| B | Summary of the database design methodology | 350 |
| C | File organizations and indexes | 357 |
| C.1 | Basic concepts | 358 |
| C.2 | Heap files | 359 |
| C.3 | Ordered files | 360 |
| C.4 | Hash files | 362 |
| C.5 | Indexes | 362 |
| C.5.1 | Types of indexes | 363 |
| C.5.2 | Secondary indexes | 363 |
| C.5.3 | Multilevel indexes | 364 |
| C.5.4 | B[superscript +]-Trees | 365 |
| D | Common data models | 367 |
| D.1 | Customer order entry | 368 |
| D.2 | Inventory control | 371 |
| D.3 | Asset management | 373 |
| D.4 | Project management | 375 |
| D.5 | Course management | 378 |
| D.6 | Human resource management | 381 |
| D.7 | Payroll management | 385 |
| D.8 | Vehicle rentals | 387 |
| D.9 | Student accommodation | 390 |
| D.10 | Client transportation | 393 |
| D.11 | Publisher printing | 395 |
| D.12 | County library | 397 |
| D.13 | Real estate rentals | 400 |
| D.14 | Travel agent | 403 |
| D.15 | Student results | 407 |
| Glossary | 411 | |
| References | 419 | |
| Index | 421 |
Customer Reviews
Average Review: