Relational Database Design and Implementation

Clearly Explained
 
 
Morgan Kaufmann (Verlag)
  • 4. Auflage
  • |
  • erschienen am 15. April 2016
  • |
  • 712 Seiten
 
E-Book | ePUB mit Adobe DRM | Systemvoraussetzungen
E-Book | PDF mit Adobe DRM | Systemvoraussetzungen
978-0-12-849902-3 (ISBN)
 

Relational Database Design and Implementation: Clearly Explained, Fourth Edition, provides the conceptual and practical information necessary to develop a database design and management scheme that ensures data accuracy and user satisfaction while optimizing performance.

Database systems underlie the large majority of business information systems. Most of those in use today are based on the relational data model, a way of representing data and data relationships using only two-dimensional tables. This book covers relational database theory as well as providing a solid introduction to SQL, the international standard for the relational database data manipulation language.

The book begins by reviewing basic concepts of databases and database design, then turns to creating, populating, and retrieving data using SQL. Topics such as the relational data model, normalization, data entities, and Codd's Rules (and why they are important) are covered clearly and concisely. In addition, the book looks at the impact of big data on relational databases and the option of using NoSQL databases for that purpose.


  • Features updated and expanded coverage of SQL and new material on big data, cloud computing, and object-relational databases
  • Presents design approaches that ensure data accuracy and consistency and help boost performance
  • Includes three case studies, each illustrating a different database design challenge
  • Reviews the basic concepts of databases and database design, then turns to creating, populating, and retrieving data using SQL


Jan L. Harrington, author of more than 35 books on a variety of technical subjects, has been writing about databases since 1984. She retired in 2013 from her position as professor and chair of the Department of Computing Technology at Marist College, where she taught database design and management, data communications, computer architecture, and the impact of technology on society for 25 years.
  • Englisch
  • San Francisco
  • |
  • USA
Elsevier Science
  • 61,75 MB
978-0-12-849902-3 (9780128499023)
0128499028 (0128499028)
weitere Ausgaben werden ermittelt
  • Cover
  • Title Page
  • Copyright Page
  • Contents
  • Preface to the Fourth Edition
  • Changes in the Fourth Edition
  • What You Need to Know
  • Teaching Materials
  • Acknowledgments
  • Part I - Introduction
  • Chapter 1 - The Database Environment
  • Defining a Database
  • Lists and Files
  • Databases
  • Systems that Use Databases
  • Data "Ownership"
  • Service-Oriented Architecture (SOA)
  • Database Software: DBMSs
  • Database Hardware Architecture
  • Centralized
  • Client/Server
  • Distributed
  • Web
  • Remote Access
  • Cloud Storage
  • Advantages of Cloud Storage
  • Problems with Cloud Storage
  • Other Factors in the Database Environment
  • Security
  • Government Regulations and Privacy
  • Legacy Databases
  • Open Source Relational DBMSs
  • For Further Reading
  • Chapter 2 - Systems Analysis and Database Requirements
  • Dealing with Resistance to Change
  • The Structured Design Life Cycle
  • Conducting the Needs Assessment
  • Assessing Feasibility
  • Generating Alternatives
  • Evaluating and Choosing an Alternative
  • Creating Design Requirements
  • Alternative Analysis Methods
  • Prototyping
  • Spiral Methodology
  • Object-Oriented Analysis
  • For Further Reading
  • Part II - Relational Database Design Theory
  • Chapter 3 - Why Good Design Matters
  • Effects of Poor Database Design
  • Unnecessary Duplicated Data and Data Consistency
  • Data Insertion Problems
  • Data Deletion Problems
  • Meaningful Identifiers
  • The Bottom Line
  • Chapter 4 - Entities and Relationships
  • Entities and Their Attributes
  • Entity Identifiers
  • Single-Valued Versus Multivalued Attributes
  • Avoiding Collections of Entities
  • Documenting Entities and Their Attributes
  • Entities and Attributes for Antique Opticals
  • Domains
  • Documenting Domains
  • Practical Domain Choices
  • Basic Data Relationships
  • One-to-One Relationships
  • One-to-Many Relationships
  • Many-to-Many Relationships
  • Weak Entities and Mandatory Relationships
  • Documenting Relationships
  • The Chen Method
  • IE Style Diagrams
  • UML Style Diagrams
  • Basic Relationships for Antique Opticals
  • Dealing with Many-to-Many Relationships
  • Composite Entities
  • Documenting Composite Entities
  • Resolving Antique Opticals' Many-to-Many Relationships
  • N-Way Composite Entities
  • Relationships and Business Rules
  • Data Modeling Versus Data Flow
  • Schemas
  • For Further Reading
  • Chapter 5 - The Relational Data Model
  • Understanding Relations
  • Columns and Column Characteristics
  • Rows and Row Characteristics
  • Types of Tables
  • A Notation for Relations
  • Primary Keys
  • Primary Keys to Identify People
  • Avoiding Meaningful Identifiers
  • Concatenated Primary Keys
  • All-Key Relations
  • Representing Data Relationships
  • Referential Integrity
  • Concatenated Foreign Keys
  • Foreign Keys That Reference the Primary Key of Their Own Table
  • Views
  • The View Mechanism
  • Why Use Views?
  • The Data Dictionary
  • Sample Data Dictionary Tables
  • A Bit of History
  • For Further Reading
  • Chapter 6 - Relational Algebra
  • The Relational Algebra and SQL Example Database: Rare Books
  • The Sample Data
  • Making Vertical Subsets: Project
  • Making Horizontal Subsets: Restrict
  • Choosing Columns and Rows: Restrict and Then Project
  • Union
  • Join
  • A Non-Database Example
  • The Equi-Join
  • What's Really Going On: Product and Restrict
  • Equi-Joins over Concatenated Keys
  • Ø-Joins
  • Outer Joins
  • The Left Outer Join
  • The Right Outer Join
  • Choosing a Right versus Left Outer Join
  • The Full Outer Join
  • Valid Versus Invalid Joins
  • Difference
  • Intersect
  • For Further Reading
  • Chapter 7 - Normalization
  • Translating an ER Diagram into Relations
  • Normal Forms
  • First Normal Form
  • Understanding Repeating Groups
  • Handling Repeating Groups
  • Problems with First Normal Form
  • Second Normal Form
  • Understanding Functional Dependencies
  • Using Functional Dependencies to Reach 2NF
  • Problems with 2NF Relations
  • Third Normal Form
  • Transitive Dependencies
  • Boyce-Codd Normal Form
  • Fourth Normal Form
  • Multivalued Dependencies
  • Fifth Normal Form
  • Sixth Normal Form
  • For Further Reading
  • Chapter 8 - Database Design and Performance Tuning
  • Indexing
  • Deciding Which Indexes to Create
  • Clustering
  • Partitioning
  • Horizontal Partitioning
  • Vertical Partitioning
  • For Further Reading
  • Chapter 9 - Codd's Rules for Relational DBMSs
  • Rule 0: The Foundation Rule
  • Rule 1: The Information Rule
  • Rule 2: The Guaranteed Access Rule
  • Rule 3: Systematic Treatment of Null Values
  • Rule 4: Dynamic Online Catalog Based on the Relational Model
  • Rule 5: The Comprehensive Data Sublanguage Rule
  • Rule 6: The View Updating Rule
  • Rule 7: High-Level Insert, Update, Delete
  • Rule 8: Physical Data Independence
  • Rule 9: Logical Data Independence
  • Rule 10: Integrity Independence
  • Rule 11: Distribution Independence
  • Rule 12: Nonsubversion Rule
  • For Further Reading
  • Part III - Relational Database Design Practice
  • Chapter 10 - Introduction to SQL
  • A Bit of SQL History
  • Conformance Levels
  • SQL Environments
  • Interactive SQL Command Processors
  • GUI Environments
  • The Embedded SQL Dilemma
  • Elements of a SQL Statement
  • For Further Reading
  • Chapter 11 - Using SQL to Implement a Relational Design
  • Database Structure Hierarchy
  • Naming and Identifying Structural Elements
  • Schemas
  • Creating a Schema
  • Identifying the Schema You Want to Use
  • Domains
  • Tables
  • Column Data Types
  • Default Values
  • NOT NULL Constraints
  • Primary Keys
  • Foreign Keys
  • Additional Column Constraints
  • Requiring Unique Values
  • Check Clauses
  • Modifying Database Elements
  • Adding Columns
  • Adding Table Constraints
  • Modifying Columns
  • Changing Column Definitions
  • Changing Default Values
  • Changing Null Status
  • Changing Column Constraints
  • Deleting Table Elements
  • Renaming Table Elements
  • Deleting Database Elements
  • For Further Reading
  • Chapter 12 - Using CASE Tools for Database Design
  • CASE Capabilities
  • ER Diagram Reports
  • Data Flow Diagrams
  • The Data Dictionary
  • Code Generation
  • Sample Input and Output Designs
  • The Drawing Environment
  • For Further Reading
  • Chapter 13 - Database Design Case Study #1: Mighty-Mite Motors
  • Corporate Overview
  • Product Development Division
  • Manufacturing Division
  • Marketing and Sales Division
  • Current Information Systems
  • Reengineering Project
  • New Information Systems Division
  • Basic System Goals
  • Current Business Processes
  • Sales and Ordering Processes
  • Manufacturing, Inventory, and Shipping Processes
  • Product Testing and Support Function
  • Designing the Database
  • Examining the Data Flows
  • The ER Diagram
  • Creating the Tables
  • Generating the SQL
  • Chapter 14 - Database Design Case Study #2: East Coast Aquarium
  • Organizational Overview
  • Animal Tracking Needs
  • The Volunteer Organization
  • The Volunteers Database
  • Creating the Application Prototype
  • Creating the ER Diagram
  • Designing the Tables
  • Generating the SQL
  • The Animal Tracking Database
  • Highlights of the Application Prototype
  • Food Management
  • Handling Arriving Animals
  • Problem Analysis
  • Creating the ER Diagram
  • Creating the Tables
  • Generating the SQL
  • Chapter 15 - Database Design Case Study #3: SmartMart
  • The Merchandising Environment
  • Product Requirements
  • In-Store Sales Requirements
  • Web Sales Requirements
  • Personnel Requirements
  • Putting Together an ERD
  • Stores, Products, and Employees
  • Reference Entities
  • Circular Relationships
  • Mutually Exclusive Relationships
  • One-to-one Relationships
  • In-store Sales
  • Web Sales
  • Creating the Tables
  • Generating the SQL
  • Part IV - Using Interactive SQL to Manipulate a Relational Database
  • Chapter 16 - Simple SQL Retrieval
  • Revisiting the Sample Data
  • Choosing Columns
  • Retrieving All Columns
  • Retrieving Specific Columns
  • Removing Duplicates
  • Ordering the Result Table
  • Choosing Rows
  • Predicates
  • Relationship Operators
  • Logical Operators
  • Negation
  • Precedence ad Parentheses
  • Special Operators
  • Between
  • Like
  • In
  • Is Null
  • Performing Row Selection Queries
  • Using a Primary Key Expression to Retrieve One Row
  • Retrieving Multiple Rows
  • Using Simple Predicates
  • Using Complex Predicates
  • Using Between and Not Between
  • Nulls and Retrieval: Three-Valued Logic
  • Chapter 17 - Retrieving Data from More Than One Table
  • SQL Syntax for Inner Joins
  • Traditional SQL Joins
  • SQL-92 Join Syntax
  • Joins Over All Columns with the Same Name
  • Joins Over Selected Columns
  • Joins Over Columns with Different Names
  • Joining Using Concatenated Keys
  • Joining More Than Two Tables
  • SQL-92 Syntax and Multiple-Table Join Performance
  • Finding Multiple Rows in One Table: Joining a Table to Itself
  • Correlation Names
  • Performing the Same-Table Join
  • Outer Joins
  • Table Constructors in Queries
  • Avoiding Joins with Uncorrelated Subqueries
  • Using the IN Operator
  • Using the ANY Operator
  • Nesting Subqueries
  • Replacing a Same-Table Join with Subqueries
  • Chapter 18 - Advanced Retrieval Operations
  • Union
  • Performing Union Using the Same Source Tables
  • Performing Union Using Different Source Tables
  • Alternative SQL-92 Union Syntax
  • Negative Queries
  • Traditional SQL Negative Queries
  • Negative Queries Using the EXCEPT Operator
  • The EXISTS Operator
  • The EXCEPT and INTERSECT Operators
  • Performing Arithmetic
  • Arithmetic Operators
  • Operator Precedence
  • String Manipulation
  • Concatenation
  • UPPER and LOWER
  • TRIM
  • SUBSTRING
  • Date and Time Manipulation
  • Date and Time System Values
  • Date and Time Interval Operations
  • OVERLAPS
  • EXTRACT
  • CASE Expressions
  • Chapter 19 - Working With Groups of Rows
  • Set Functions
  • COUNT
  • SUM
  • AVG
  • MIN and MAX
  • Set Functions in Predicates
  • Changing Data Types: CAST
  • Grouping Queries
  • Forming Groups
  • Restricting Groups
  • Windowing and Window Functions
  • Ordering the Partitioning
  • Specific Functions
  • RANK
  • PERCENT_RANK
  • ROW_NUMBER
  • CUME_DIST
  • NTILE
  • Inverse Distributions: PERCENTILE_CONT and PERCENTILE_DISC
  • Chapter 20 - Data Modification
  • Inserting Rows
  • Inserting One Row
  • Copying Existing Rows
  • Updating Data
  • Deleting Rows
  • Deletes and Referential Integrity
  • Inserting, Updating, or Deleting on a Condition: MERGE
  • Chapter 21 - Creating Additional Structural Elements
  • Views
  • Deciding Which Views to Create
  • View Updatability Issues
  • Creating Views
  • Temporary Tables
  • Creating Temporary Tables
  • Loading Temporary Tables with Data
  • Disposition of Temporary Table Rows
  • Common Table Expressions (CTEs)
  • Creating Indexes
  • Part V - Database Implementation Issues
  • Chapter 22 - Concurrency Control
  • The Multiuser Environment
  • Transactions
  • The ACID Transaction Goal
  • Logging and Rollback
  • Recovery
  • Problems with Concurrent Use
  • Lost Update #1
  • Lost Update #2
  • Inconsistent Analysis
  • Dirty Reads
  • Nonrepeatable Read
  • Phantom Read
  • Solution #1: Classic Locking
  • Write or Exclusive Locks
  • Operation of Write/Exclusive Locks
  • Problem with Write/Exclusive Locks: Deadlock
  • Read or Shared Locks
  • Two-Phase Locking
  • Locks and Transaction Length
  • Solution #2: Optimistic Concurrency Control (Optimistic Locking)
  • Solution #3: Multiversion Concurrency Control (Timestamping)
  • Transaction Isolation Levels
  • Web Database Concurrency Control Issues
  • Distributed Database Issues
  • For Further Reading
  • Chapter 23 - Database Security
  • Sources of External Security Threats
  • Physical Threats
  • Hackers and Crackers
  • Types of Attacks
  • Sources of Internal Threats
  • Employee Threats
  • External Remedies
  • Securing the Perimeter: Firewalls
  • Handling Malware
  • Buffer Overflows
  • Physical Server Security
  • User Authentication
  • User IDs and Passwords (What the User Knows)
  • Login Devices (What User Has)
  • Biometrics (Who the User Is)
  • VPNs
  • Combating Social Engineering
  • Handling Other Employee Threats
  • Internal Solutions
  • Internal Database User IDs and Passwords
  • Authorization Matrices
  • Types of Access Rights
  • Using an Authorization Matrix
  • Database Implementations
  • Granting and Revoking Access Rights
  • Granting Rights
  • Revoking Rights
  • Who Has Access to What
  • Backup and Recovery
  • Backup
  • Disaster Recovery
  • The Bottom Line: How Much Security Do You Need?
  • For Further Reading
  • Chapter 24 - Data Warehousing
  • Scope and Purpose of a Data Warehouse
  • Obtaining and Preparing the Data
  • Data Modeling for the Data Warehouse
  • Dimensional Modeling Basics
  • Dates and Data
  • Data Warehouse Appliances
  • For Further Reading
  • Chapter 25 - Data Quality
  • Why Data Quality Matters
  • Recognizing and Handling Incomplete Data
  • Missing Rows
  • Missing Column Data
  • Missing Primary Key Data
  • Recognizing and Handling Incorrect Data
  • Wrong Codes
  • Wrong Calculations
  • Wrong Data Entered into the Database
  • Violation of Business Rules
  • Recognizing and Handling Incomprehensible Data
  • Multiple Values in a Column
  • Orphaned Foreign Keys
  • Recognizing and Handling Inconsistent Data
  • Inconsistent Names and Addresses
  • Inconsistent Business Rules
  • Inconsistent Granularity
  • Unenforced Referential Integrity
  • Inconsistent Data Formatting
  • Preventing Inconsistent Data on an Organizational Level
  • Fixing the Problem
  • Employees and Data Quality
  • For Further Reading
  • Part VI - Beyond the Relational Data Model
  • Chapter 26 - XML Support
  • XML Basics
  • XML Structure
  • Attributes
  • XML Document Structure
  • Tags
  • Declarations (Prologs)
  • Being "Well-Formed"
  • XML Schemas
  • SQL/XML
  • XML Publishing Functions
  • XMLCOMMENT
  • XMLPARSE
  • XMLROOT
  • XMLELEMENT
  • XMLFOREST
  • XMLATTRIBUTES
  • XMLCONCAT
  • The XML Data Type
  • XMLSERIALIZE
  • For Further Reading
  • Chapter 27 - Object-Relational Databases
  • Getting Started: Object-Orientation without Computing
  • Basic OO Concepts
  • Objects
  • Classes
  • Types of Classes
  • Types of Methods
  • Method Overloading
  • Class Relationships
  • Inheritance
  • Inheriting Attributes
  • Multiple Inheritance
  • Abstract Classes
  • Inheriting Methods: Polymorphism
  • Composition
  • Benefits of Object-Orientation
  • Where Objects Work Better Than Relations
  • Limitations of Pure Object-Oriented DBMSs
  • The Object-Relational Data Model
  • ER Diagrams for Object-Relational Designs
  • Features of the OR Data Model
  • SQL Support for the OR Data Model
  • An Additional Sample Database
  • SQL Data Types for Object-Relational Support
  • Row Type
  • Array Type
  • Manipulating Arrays
  • Multiset Type
  • Manipulating Multisets
  • User-Defined Data Types and Typed Tables
  • UDTs as Domains
  • UDTs as Classes
  • Creating Typed Tables Using UDTs
  • Inheritance
  • Reference (REF) Type
  • Dereferencing for Data Access
  • Methods
  • Defining Methods
  • Executing Methods
  • For Further Reading
  • Chapter 28 - Relational Databases and "Big Data": The Alternative of a NoSQL Solution
  • Types of NoSQL Databases
  • Key-Value Store
  • Document
  • Column
  • Graph
  • Other Differences Between NoSQL Databases and Relational Databases
  • Hardware Architecture Differences
  • Sharding
  • Replication
  • Combining Sharding and Replication
  • Data Access and Manipulation Techniques
  • Transaction Control: BASE Transactions
  • Benefits of NoSQL Databases
  • Problems with NoSQL Databases
  • Open Source NoSQL Products
  • For Further Reading
  • Part VII - Appendices
  • Appendix A - Historical Antecedents
  • File Processing Systems
  • Early File Processing
  • ISAM Files
  • Limitations of File Processing
  • File Processing on the Desktop
  • The Hierarchical Data Model
  • Characteristics of the Hierarchical Data Model
  • IMS
  • The Simple Network Data Model
  • Characteristics of a Simple Network
  • CODASYL
  • The Complex Network Data Model
  • Appendix B - SQL Programming
  • SQL Language Programming Elements
  • Variables and Assignment
  • Passing Parameters
  • Scope of Variables
  • Selection
  • IF
  • CASE
  • Iteration
  • LOOP
  • WHILE
  • REPEAT
  • Example #1: Interactive Retrievals
  • Example #2: Nested Modules
  • Executing Modules as Triggers
  • Executing Modules as Stored Procedures
  • Embedded SQL Programming
  • The Embedded SQL Environment
  • Using Host Language Variables
  • DBMS Return Codes
  • Retrieving a Single Row
  • Indicator Variables
  • Retrieving Multiple Rows: Cursors
  • Declaring a Cursor
  • Scrolling Cursors
  • Enabling Updates
  • Sensitivity
  • Opening a Cursor
  • Fetching Rows
  • Closing a Cursor
  • Embedded SQL Data Modification
  • Direct Modification
  • Indicator Variables and Data Modification
  • Integrity Validation with the Match Predicate
  • Modification Using Cursors
  • Deletion Using Cursors
  • Dynamic SQL
  • Immediate Execution
  • Dynamic SQL with Dynamic Parameters
  • Dynamic Parameters with Cursors
  • Step 1: Creating the Statement String
  • Step 2: Allocating the Descriptor Areas
  • Step 3: Prepare the SQL Statement
  • Steps 4 and 8: Describing Descriptor Areas
  • Step 5: Setting Input Parameters
  • Steps 6 and 7: Declaring and Opening the Cursor
  • Step 9: Setting the Output Parameters
  • Steps 11-13: Fetching Rows and Getting the Data
  • Steps 14 and 15: Finishing Up
  • Dynamic Parameters without a Cursor
  • Statements without Cursors or a Descriptor Area
  • Statements without Cursors but Using a Descriptor Area
  • SQLSTATE Return Codes
  • Appendix C - SQL Syntax Summary
  • Glossary
  • A
  • B
  • C
  • D
  • E
  • F
  • G
  • H
  • I
  • J
  • L
  • M
  • N
  • O
  • P
  • Q
  • R
  • S
  • T
  • U
  • V
  • W
  • X
  • Subject Index
  • Back cover

Dateiformat: EPUB
Kopierschutz: Adobe-DRM (Digital Rights Management)

Systemvoraussetzungen:

Computer (Windows; MacOS X; Linux): Installieren Sie bereits vor dem Download die kostenlose Software Adobe Digital Editions (siehe E-Book Hilfe).

Tablet/Smartphone (Android; iOS): Installieren Sie bereits vor dem Download die kostenlose App Adobe Digital Editions (siehe E-Book Hilfe).

E-Book-Reader: Bookeen, Kobo, Pocketbook, Sony, Tolino u.v.a.m. (nicht Kindle)

Das Dateiformat EPUB ist sehr gut für Romane und Sachbücher geeignet - also für "fließenden" Text ohne komplexes Layout. Bei E-Readern oder Smartphones passt sich der Zeilen- und Seitenumbruch automatisch den kleinen Displays an. Mit Adobe-DRM wird hier ein "harter" Kopierschutz verwendet. Wenn die notwendigen Voraussetzungen nicht vorliegen, können Sie das E-Book leider nicht öffnen. Daher müssen Sie bereits vor dem Download Ihre Lese-Hardware vorbereiten.

Weitere Informationen finden Sie in unserer E-Book Hilfe.


Dateiformat: PDF
Kopierschutz: Adobe-DRM (Digital Rights Management)

Systemvoraussetzungen:

Computer (Windows; MacOS X; Linux): Installieren Sie bereits vor dem Download die kostenlose Software Adobe Digital Editions (siehe E-Book Hilfe).

Tablet/Smartphone (Android; iOS): Installieren Sie bereits vor dem Download die kostenlose App Adobe Digital Editions (siehe E-Book Hilfe).

E-Book-Reader: Bookeen, Kobo, Pocketbook, Sony, Tolino u.v.a.m. (nicht Kindle)

Das Dateiformat PDF zeigt auf jeder Hardware eine Buchseite stets identisch an. Daher ist eine PDF auch für ein komplexes Layout geeignet, wie es bei Lehr- und Fachbüchern verwendet wird (Bilder, Tabellen, Spalten, Fußnoten). Bei kleinen Displays von E-Readern oder Smartphones sind PDF leider eher nervig, weil zu viel Scrollen notwendig ist. Mit Adobe-DRM wird hier ein "harter" Kopierschutz verwendet. Wenn die notwendigen Voraussetzungen nicht vorliegen, können Sie das E-Book leider nicht öffnen. Daher müssen Sie bereits vor dem Download Ihre Lese-Hardware vorbereiten.

Weitere Informationen finden Sie in unserer E-Book Hilfe.


Download (sofort verfügbar)

51,11 €
inkl. 19% MwSt.
Download / Einzel-Lizenz
ePUB mit Adobe DRM
siehe Systemvoraussetzungen
PDF mit Adobe DRM
siehe Systemvoraussetzungen
Hinweis: Die Auswahl des von Ihnen gewünschten Dateiformats und des Kopierschutzes erfolgt erst im System des E-Book Anbieters
E-Book bestellen

Unsere Web-Seiten verwenden Cookies. Mit der Nutzung dieser Web-Seiten erklären Sie sich damit einverstanden. Mehr Informationen finden Sie in unserem Datenschutzhinweis. Ok