Database Systems: Design, Implementation, & Management
Learning Objectives
Define the difference between data and information.
Describe what a database is, various types, and why databases are valuable assets for decision making.
Explain the importance of database design.
Trace how modern databases evolved from file systems.
Understand flaws in file system data management.
Outline the main components of the database system.
Describe the main functions of a database management system (DBMS).
Fundamentals of Data and Information
Data Characteristics in Today's World:
Ubiquitous: Abundant, global, and present everywhere.
Pervasive: Unescapable, prevalent, and persistent.
Databases make data persistent and shareable in a secure way through specialized structures that allow computer-based systems to store, manage, and retrieve data rapidly.
Data versus Information:
Data: Consists of raw facts that have not yet been processed to reveal meaning to the end user. Data serves as the fundamental building block of information.
Information: Results from processing raw data to reveal meaning. Information requires context and serves as the bedrock of knowledge.
Quality Attributes of Information: Information must be accurate, relevant, and timely.
Database and DBMS Concepts
Database: A shared, integrated computer structure that stores:
End-User Data: Raw facts of interest to the end user.
Metadata: Data about data, through which end-user data is integrated and managed. Metadata describes data characteristics and relationships.
Database Management System (DBMS):
A collection of programs that manages the database structure and controls access to the data stored in the database.
Acts as an intermediary between the user (or application programs) and the database.
Role and Advantages of the DBMS:
Enables data to be shared across the organization.
Presents the end user with an integrated view of data.
Provides more efficient and effective data management.
Improves data sharing, security, integration, access, decision-making, and user productivity.
DBMS Intermediary Architectural View:
End users connect via workstation computers or web interfaces (
http://).Application requests are sent from users to the DBMS, which processes data requests and returns formatted data.
The DBMS presents a single, integrated view of data retrieved from the underlying database structure, containing metadata alongside end-user data domains such as Customers, Invoices, and Products.
Categorization and Types of Databases
Classification by Number of Users:
Single-User Database: Supports one user at a time.
Desktop Database: A single-user database that runs on a personal computer.
Multiuser Database: Supports multiple concurrent users simultaneously.
Workgroup Database: Supports a small number of users or a specific department.
Enterprise Database: Supports many users across multiple departments throughout an organization.
Classification by Location:
Centralized Database: Supports data located at a single physical site.
Distributed Database: Supports data distributed across different geographic or physical sites.
Cloud Database: Created and maintained using cloud data services that provide defined performance measures for the database environment.
Classification by Data Type and Usage:
General-Purpose Database: Contains a wide variety of data used in multiple disciplines or domain areas.
Discipline-Specific Database: Contains data focused on specific subject areas or specialized domains.
Operational Database: Designed primarily to support a company's day-to-day operations (also known as transactional or production databases).
Classification for Decision Support and Analytics:
Analytical Database: Stores historical data and business metrics used exclusively for tactical or strategic decision making.
Data Warehouse: Stores data in a format optimized specifically for decision support.
Online Analytical Processing (OLAP): A set of tools for retrieving, processing, and modeling data from the data warehouse.
Business Intelligence (BI): Captures and processes business data to generate information that supports strategic decision making.
Classification by Degree of Data Structure:
Unstructured Data: Data that exists in its original (raw) state, without applied structural formatting.
Structured Data: Data resulting from formatting, where structure is applied based on the specific type of processing to be performed.
Semistructured Data: Data that has been processed to some extent, containing markers or tags to separate data elements.
Extensible Markup Language (XML): A standard language used to represent data elements in a textual format with explicit structure tags.
Importance of Database Design
Database design focuses on designing the database structure that will be used to store and manage end-user data.
Well-Designed Database: Facilitates data management and consistently generates accurate and valuable information.
Poorly Designed Database: Causes difficult-to-trace errors, data redundancies, and inaccurate reports that lead to poor operational and strategic decision making.
Evolution of File System Data Processing
Historical Stages:
Manual File Systems: Organized paper records kept in file folders and physical filing cabinets.
Computerized File Systems: Data Processing (DP) specialists created custom computer-based systems to track data and generate required routine reports.
File System Redux: Modern end-user productivity tools, such as spreadsheet programs like Microsoft Excel.
Basic File Terminology:
Data: Raw facts, such as a telephone number, a birth date, a customer name, and a year-to-date (YTD) sales value. Data has little meaning unless organized in a logical manner.
Field: A character or group of characters (alphabetic or numeric) that has a specific meaning. Used to define and store data.
Record: A logically connected set of one or more fields that describes a person, place, or thing. For example, a customer record consists of name, address, phone number, date of birth, credit limit, and unpaid balance.
File: A collection of related records. For example, a file containing data about all students currently enrolled at Gigantic University.
Structure of a Simple File System:
Sales Department: Maintains its own isolated
SALES fileandCUSTOMER file, accessed via separate File Management Programs and File Report Programs.Personnel Department: Maintains its own isolated
AGENT file, accessed via separate File Management Programs and File Report Programs.Each department operates in complete isolation with standalone software routines and separate files.
Flaws and Problems in File System Data Processing
General File System Challenges:
Lengthy development times for new applications.
Difficulty obtaining quick answers or ad hoc queries.
Complex system administration across fragmented files.
Lack of security controls and limited data sharing capabilities.
Requirement for extensive, complex computer programming to perform simple modifications.
Structural and Data Dependence:
Structural Dependence: A condition where access to a file is dependent on its physical structure. Changing a file structure requires modifying every application program that accesses that file.
Structural Independence: A condition where the file structure can be modified without affecting application programs' ability to access data.
Data Dependence: A condition where data access changes whenever data storage characteristics change.
Data Independence: A condition where data storage characteristics can be changed without impacting application programs' capability to access data.
Practical Significance: The distinction between data dependence and independence highlights the crucial difference between logical format (how the user views data) and physical format (how data is physically stored).
Data Redundancy and Islands of Information:
Data Redundancy: Unnecessarily storing the same data at different places within an organization.
Islands of Information: Scattered data locations controlled by different departments, increasing the probability of inconsistent versions of the same data.
Consequences of Uncontrolled Data Redundancy:
Poor data security.
Data inconsistency (conflicting statements of fact).
Increased rate of data-entry errors.
Loss of data integrity.
Data Anomalies:
Data anomalies develop when not all required changes are applied across redundant data stores.
Update Anomalies: Inconsistencies caused by updating a data element in one file but failing to update it in another location.
Insertion Anomalies: Inability to insert a record into a file without creating dummy data or violating structural constraints.
Deletion Anomalies: Unintentional loss of data caused by deleting a record that contains necessary references to other data elements.
The Database System Environment
Comparison of Architecture:
In a File System: The Personnel department (Employees), Sales department (Customers, Sales, Inventory), and Accounting department (Accounts) maintain separate, disconnected data silos.
In a Database System: All departments (Personnel, Sales, Accounting) send access requests through a centralized DBMS, which manages a single database containing Employees, Customers, Sales, Inventory, and Accounts.
Database System Definition:
An organization of components that define and regulate the collection, storage, management, and use of data within a database environment.
Stores logically related data in a single logical data repository, which may be physically distributed across multiple storage facilities.
DBMS software eliminates most file system flaws, including data inconsistency, data anomalies, data dependence, and structural dependence.
Modern DBMS software defines, stores, and manages all data structures, relationships between structures, and physical access paths.
Five Main Components of a Database System:
Hardware: Physical devices including computers, storage devices, network infrastructure, and servers.
Software: Includes the Operating System, DBMS software, and Application Programs.
People:
System Administrator: Supervises database and overall system operations.
Database Administrator (DBA): Manages the DBMS, writes and enforces procedures, and maintains databases.
Database Designer: Designs database structures (conceptual, logical, physical).
Systems Analysts and Programmers: Write application programs and build user solutions.
End Users: Use application programs to execute daily business processes.
Procedures: Rules, standards, and instructions governing database usage, design, and administration (written and enforced by DBAs and system administrators).
Data: The collection of raw facts and metadata managed within the database system.
Functions of a Database Management System (DBMS)
1. Data Dictionary Management:
Stores definitions of data elements and their relationships (metadata).
Automatically looks up structural definitions when programs access data.
2. Data Storage Management:
Creates complex structures required for data storage.
Includes Performance Tuning, which optimizes storage and access structures to ensure efficient retrieval and updating.
3. Data Transformation and Presentation:
Transforms entered data into required logical data structures.
Formats data for presentation to conform to logical end-user expectations.
4. Security Management:
Creates and enforces security rules and user authorization.
Ensures privacy and prevents unauthorized access to sensitive data.
5. Multiuser Access Control:
Employs sophisticated concurrency algorithms to ensure multiple users can access the database simultaneously without compromising data integrity.
6. Backup and Recovery Management:
Provides system routines to recover the database after hardware or software failure.
7. Data Integrity Management:
Enforces integrity rules to minimize data redundancy and maximize data consistency.
8. Database Access Languages and Application Programming Interfaces:
Query Language: Allows users to specify what data to retrieve without having to specify how to retrieve it physically.
Structured Query Language (SQL): The de facto standard query language and data access interface supported by the vast majority of DBMS vendors.
9. Database Communication Interfaces:
Accepts and processes end-user requests across multiple, diverse network environments and protocols.
Disadvantages and Challenges of Database Systems
Increased Costs: High software, hardware, training, and maintenance expenses.
Management Complexity: Requires dedicated, highly skilled technical staff to administer complex enterprise database systems.
Maintaining Currency: Ongoing necessity to keep software, hardware, and staff skills up to date with rapidly evolving technology.
Vendor Dependence: Reliance on specific vendor platforms makes switching DBMS software difficult and costly.
Frequent Upgrade/Replacement Cycles: Regular updates and hardware replacements are needed to maintain vendor support and performance standards.
Database Career Opportunities
Database Developer:
Description: Create and maintain database-based applications.
Sample Skills Required: Programming, database fundamentals, SQL.
Database Designer:
Description: Design and maintain databases.
Sample Skills Required: Systems design, database design, SQL.
Database Administrator:
Description: Manage and maintain DBMS and databases.
Sample Skills Required: Database fundamentals, SQL, vendor courses.
Database Analyst:
Description: Develop databases for decision support reporting.
Sample Skills Required: SQL, query optimization, data warehouses.
Database Architect:
Description: Design and implementation of database environments (conceptual, logical, and physical).
Sample Skills Required: DBMS fundamentals, data modeling, SQL, hardware knowledge, etc.
Database Consultant:
Description: Help companies leverage database technologies to improve business processes and achieve specific goals.
Sample Skills Required: Database fundamentals, data modeling, database design, SQL, DBMS, hardware, vendor-specific technologies, etc.
Database Security Officer:
Description: Implement security policies for data administration.
Sample Skills Required: DBMS fundamentals, database administration, SQL, data security technologies, etc.
Cloud Computing Data Architect:
Description: Design and implement the infrastructure for next-generation cloud database systems.
Sample Skills Required: Internet technologies, cloud storage technologies, data security, performance tuning, large databases, etc.
Data Scientist:
Description: Analyze large amounts of varied data to generate insights, relationships, and predictable behaviors.
Sample Skills Required: Data analysis, statistics, advanced mathematics, SQL, programming, data mining, machine learning, data visualization.