Practical Issues in Database Management
A Reference for the Thinking Practitioner
Published on 2. June 2000
Book
Paperback/Softback
288 pages
978-0-201-48555-4 (ISBN)
Description
Databas(e)ics clearly explains the key concepts users and database professionals need to understand in order to build well-designed databases that answer business questions accurately and efficiently. Fabian Pascal, one of the industry's leading experts, identifies ten critical, recurring issues that both database users and vendors often fail to address appropriately. Pascal demonstrates why understanding these fundamentals is so important, providing detailed examples and solutions designed to help users escape the key pitfalls of database development.KEY TOPICS:Among the topics covered: unstructured data and complex data types; business rules and enforcing data integrity; keys; duplicates; normalization; entity subtypes and supertypes; data hierarchies and recursive queries; redundancy; quota queries; and how to handle missing information. Along the way, Pascal offers no-holds-barred assessments of how well current SQL implementations and commercial products address each issue. Databas(e)ics, in short, is a complete guide to building databases right the first time, so they don't have to be rebuilt later.MARKET:For all DBAs, developers, managers, and end-users that need to understand the best ways to design and implement database systems.
More details
Persons
Fabian Pascal is an independent industry analyst, consultant, author, and lecturer specializing in database management. He is the author of two previous books, Understanding Relational Databases and SQL and Relational Basics, and has contributed extensively to many industry publications.
0201485559AB04062001
0201485559AB04062001
Content
Foreword.
Preface.
1. Careful What You Wish For: Data Types and Complexity.
The Issue.
Fundamentals.
"Simple" Types.
System-Defined Types.
User-Defined Types.
Data Type Support.
On Type "Atomicity."
"Complex" Types.
Practical Implications.
Relational Domains versus Object Classes.
Database Design.
Relational Structure versus Object Manipulation.
DBMS Implementation.
"Domains."
"Universal" DBMSs.
Conclusion and Recommendations.
Appendix 1A: Possible Representations for Image Types.
Appendix 1B: Graphics File Follies.
Appendix 1C: Biometric Tools Ready to Take Off.
Appendix 1D: Search Engine Failures.
Appendix 1E: "Complex" Types and Operators: An Internet Illustration.
Appendix 1F: Java and Database Synergy.
2. The Rule of Rules: Integrity.
The Issue.
Fundamentals.
Business Rules.
Integrity Constraints.
Domain Constraints.
Column Constraints.
Table Constraints.
Database Constraints.
Database Correctness.
Base versus Derived Constraints.
Integrity Enforcement.
Integrity Rules.
DBMS Support.
Practical Implications.
SQL and Integrity.
Domain Rules.
Column Rules.
Table and Database Rules.
Procedural Support.
Conclusion and Recommendations.
Appendix 2A: A Note on SQL's OVERLAPS Operator.
3. A Matter of Identity: Keys.
The Issue.
Fundamentals.
Simple versus Composite Keys.
Natural versus Surrogate Keys.
Candidate versus Primary Keys.
Foreign Keys.
Referential Integrity and Primary Keys.
DBMS Support.
Practical Implications.
SQL and Keys.
Conclusion and Recommendations.
4. Don't Get Duped by Dupes: Duplicate Rows.
The Issue.
Fundamentals.
Determining Entity Types.
"Hidden" Information.
A Relational Bonus.
Practical Implications.
SQL and Duplicates.
Duplicate Removal.
Countability.
Addressability.
Correctness and Interpretability of Results.
Essential Order and Performance Optimization.
Conclusion and Recommendations.
Appendix 4A: Duplicate Removal in SQL.
Appendix 4B: Language Redundancy and Duplicates.
5. The Key, the Whole Key, and Nothing but the Key: Normalization.
The Issue.
Fundamentals.
Repeating Groups.
First Normal Form.
Column Dependencies.
Functional Dependencies.
Second Normal Form.
Third Normal Form.
Multivalued Dependencies.
Fourth Normal Form.
Join Dependencies.
Fifth Normal Form.
Practical Implications.
SQL and Multivalued Columns.
"Denormalization" and Performance.
Conclusion and Recommendations.
6. Neither Distinct nor the Same: Entity Supertypes and Subtypes.
The Issue.
Fundamentals.
Entity Types, Attributes, and Relationships.
A Special Case.
DBMS Support.
Practical Implications.
Multikey References.
SQL Subtables and Supertables.
Conclusion and Recommendations.
7. Climbing Trees in SQL: Data Hierarchies.
The Issue.
Fundamentals.
Nodes and Links.
"Explode" Queries.
Recurring Nodes.
Practical Implications.
SQL and Trees.
Conclusion and Recommendations.
8. Not Worth Repeating: Redundancy.
The Issue.
Fundamentals.
Duplicate Rows.
Within-Table Duplicates.
Cross-Table Duplicates.
Entity Subtypes and Supertypes.
Column Dependencies.
Functional Dependencies.
Dependency on Part of the Key.
Indirect Dependency.
Multivalued Dependencies.
Join Dependencies.
Derived Information.
Redundancy Control.
Denormalized Designs.
Derived Information.
Practical Implications.
SQL and Keyless Tables.
SQL and Cross-Table Duplicates.
SQL and "Denormalization."
SQL and Derived Information.
Conclusion and Recommendations.
9. Will SQL Come to Order: Quota Queries.
The Issue.
Fundamentals.
Ambiguities.
The Declarative Solution.
Practical Implications.
SQL and Quota Queries.
Conclusion and Recommendations.
10. What You Don't Know Can Hurt You: Missing Information.
The Issue.
Fundamentals.
Meaningless Assertions.
Empty Assertions.
Missing Information as Metadata.
DBMS Support.
Many-Valued Logic.
Practical Implications.
SQL NULLs.
NULLs and 4VL.
NULLs and 3VL.
2VL and Metadata.
Conclusion and Recommendations.
Index. 0201485559T04062001
Preface.
1. Careful What You Wish For: Data Types and Complexity.
The Issue.
Fundamentals.
"Simple" Types.
System-Defined Types.
User-Defined Types.
Data Type Support.
On Type "Atomicity."
"Complex" Types.
Practical Implications.
Relational Domains versus Object Classes.
Database Design.
Relational Structure versus Object Manipulation.
DBMS Implementation.
"Domains."
"Universal" DBMSs.
Conclusion and Recommendations.
Appendix 1A: Possible Representations for Image Types.
Appendix 1B: Graphics File Follies.
Appendix 1C: Biometric Tools Ready to Take Off.
Appendix 1D: Search Engine Failures.
Appendix 1E: "Complex" Types and Operators: An Internet Illustration.
Appendix 1F: Java and Database Synergy.
2. The Rule of Rules: Integrity.
The Issue.
Fundamentals.
Business Rules.
Integrity Constraints.
Domain Constraints.
Column Constraints.
Table Constraints.
Database Constraints.
Database Correctness.
Base versus Derived Constraints.
Integrity Enforcement.
Integrity Rules.
DBMS Support.
Practical Implications.
SQL and Integrity.
Domain Rules.
Column Rules.
Table and Database Rules.
Procedural Support.
Conclusion and Recommendations.
Appendix 2A: A Note on SQL's OVERLAPS Operator.
3. A Matter of Identity: Keys.
The Issue.
Fundamentals.
Simple versus Composite Keys.
Natural versus Surrogate Keys.
Candidate versus Primary Keys.
Foreign Keys.
Referential Integrity and Primary Keys.
DBMS Support.
Practical Implications.
SQL and Keys.
Conclusion and Recommendations.
4. Don't Get Duped by Dupes: Duplicate Rows.
The Issue.
Fundamentals.
Determining Entity Types.
"Hidden" Information.
A Relational Bonus.
Practical Implications.
SQL and Duplicates.
Duplicate Removal.
Countability.
Addressability.
Correctness and Interpretability of Results.
Essential Order and Performance Optimization.
Conclusion and Recommendations.
Appendix 4A: Duplicate Removal in SQL.
Appendix 4B: Language Redundancy and Duplicates.
5. The Key, the Whole Key, and Nothing but the Key: Normalization.
The Issue.
Fundamentals.
Repeating Groups.
First Normal Form.
Column Dependencies.
Functional Dependencies.
Second Normal Form.
Third Normal Form.
Multivalued Dependencies.
Fourth Normal Form.
Join Dependencies.
Fifth Normal Form.
Practical Implications.
SQL and Multivalued Columns.
"Denormalization" and Performance.
Conclusion and Recommendations.
6. Neither Distinct nor the Same: Entity Supertypes and Subtypes.
The Issue.
Fundamentals.
Entity Types, Attributes, and Relationships.
A Special Case.
DBMS Support.
Practical Implications.
Multikey References.
SQL Subtables and Supertables.
Conclusion and Recommendations.
7. Climbing Trees in SQL: Data Hierarchies.
The Issue.
Fundamentals.
Nodes and Links.
"Explode" Queries.
Recurring Nodes.
Practical Implications.
SQL and Trees.
Conclusion and Recommendations.
8. Not Worth Repeating: Redundancy.
The Issue.
Fundamentals.
Duplicate Rows.
Within-Table Duplicates.
Cross-Table Duplicates.
Entity Subtypes and Supertypes.
Column Dependencies.
Functional Dependencies.
Dependency on Part of the Key.
Indirect Dependency.
Multivalued Dependencies.
Join Dependencies.
Derived Information.
Redundancy Control.
Denormalized Designs.
Derived Information.
Practical Implications.
SQL and Keyless Tables.
SQL and Cross-Table Duplicates.
SQL and "Denormalization."
SQL and Derived Information.
Conclusion and Recommendations.
9. Will SQL Come to Order: Quota Queries.
The Issue.
Fundamentals.
Ambiguities.
The Declarative Solution.
Practical Implications.
SQL and Quota Queries.
Conclusion and Recommendations.
10. What You Don't Know Can Hurt You: Missing Information.
The Issue.
Fundamentals.
Meaningless Assertions.
Empty Assertions.
Missing Information as Metadata.
DBMS Support.
Many-Valued Logic.
Practical Implications.
SQL NULLs.
NULLs and 4VL.
NULLs and 3VL.
2VL and Metadata.
Conclusion and Recommendations.
Index. 0201485559T04062001