Database Systems Fundamentals: Architecture, Concepts, and Applications

Fundamentals of Database Systems and Applications

  • Databases and database management technology serve as an essential foundation for daily modern operations, as vast amounts of data and events are recorded in dedicated database systems.
  • Diverse entities collect and maintain data structured specifically to support their domain requirements:
    • Government Agencies: Retain citizen records, facial imagery, and transaction histories to issue passports and national ID cards, facilitate voting elections, and collect taxes.
    • Commercial Enterprises: Maintain transaction logs of sales, purchases, product catalogs, and financial invoices.
    • Educational Institutions: Store information regarding student enrollment, course offerings, grade reports, and academic prerequisites.
  • Evolutionary Shift in Data Types:
    • Traditional Databases: Restricted primarily to text and numerical data formats.
    • Modern Databases: Expanded to accommodate rich media including images, digital audio, and video streams.
    • Advanced Applications: Integrated into complex domains including Artificial Intelligence (AI) and e-Commerce platforms.

Classification of Database Applications

  • Traditional Database Applications:
    • Focus on storing, updating, and querying structured textual and numeric records.
  • Multimedia Databases:
    • Store and manage non-textual digital media such as photographs, speech recordings, audio clips, and high-definition videos.
  • Geographic Information Systems (GIS):
    • Store, analyze, and manipulate spatial and cartographic data, weather patterns, and satellite imagery.
  • Data Warehouses and Online Analytical Processing (OLAP):
    • Extract, consolidate, and analyze business intelligence from immense datasets to support strategic decision-making.
  • Active and Real-Time Database Technology:
    • Execute automated tasks and monitor high-frequency control systems in industrial manufacturing and process control environments.

Fundamental Concepts and Core Definitions

  • Database:
    • A virtual repository housing organized data and information related to an individual, activity, enterprise, or country.
    • Technical Definition: A collection of logically related and stored files that can be updated, inserted, and retrieved according to user requirements.
    • Essential Properties:
    • Composed of logically related data items.
    • Contains known facts that can be recorded and possess implicit meaning.
    • Represents a Mini-World or Universe of Discourse (UoD): Any real-world modification in the domain is reflected directly within the database.
    • Built and maintained with an explicit, predefined purpose.
  • Database Management System (DBMS):
    • A specialized software package designed to create, maintain, and administer databases.
    • Enables data insertion, retrieval, and modification without forcing developers to construct custom file management routines.
    • Constructs the database by storing data onto physical storage media controlled by the DBMS software.
    • Operates independently from computer application software and operating system dependencies.
    • Serves as the software interface between application programs and physical data storage files.
  • Database System Environment:
    • Comprises the database itself, the DBMS software, application programs, stored meta-data, and the system users/programmers.

A simplified database system environment

Key Database Operations and Lifecycle Management

  • Defining a Database:
    • Specifying data types, structural relationships, and integrity constraints for the data to be stored.
  • Manipulating a Database:
    • Querying: Retrieving specific data items.
    • Updating: Performing insertion, deletion, and modification operations.
    • Generating formal analytical and operational reports.
  • Sharing a Database:
    • Allowing multiple users and concurrent application programs to access and manipulate the same database simultaneously without conflict.
  • Application Programs:
    • Access stored data by formulating queries and submitting them to the DBMS software.
  • Queries:
    • Formal data access requests that permit precise, user-friendly data retrieval.
  • Transactions:
    • Individual operational units executing read or write operations against stored database records.
  • Protection Subsystems:
    • System Protection: Shields the database against hardware disruptions and software crash faults.
    • Security Protection: Prevents unauthorized, illegal, or malicious access.
  • Database Maintenance:
    • Facilitates database schema expansion and capacity scaling as organizational requirements evolve over time.

Exemplary Academic Database System

  • Overview:
    • Demonstrates a database designed to manage university operations, tracking students, course offerings, section enrollments, grades, and academic prerequisites.
  • Recorded Entities and File Schemas:
    • STUDENT: Captures student identity, academic standing, and major.
    • COURSE: Stores academic course definitions, credit hours, and department ownership.
    • SECTION: Records specific course offerings in particular academic terms.
    • GRADE_REPORT: Maps individual student accomplishments within specific course sections.
    • PREREQUISITE: Represents mandatory course sequences.

A database that stores student and course information

  • Complete Tabular Instance Data:
    • STUDENT Table:
    • Record 1: Name = Smith, Student_number = 17, Class = 1, Major = CS
    • Record 2: Name = Brown, Student_number = 8, Class = 2, Major = CS
    • COURSE Table:
    • Record 1: Course_name = Intro to Computer Science, Course_number = CS1310, Credit_hours = 4, Department = CS
    • Record 2: Course_name = Data Structures, Course_number = CS3320, Credit_hours = 4, Department = CS
    • Record 3: Course_name = Discrete Mathematics, Course_number = MATH2410, Credit_hours = 3, Department = MATH
    • Record 4: Course_name = Database, Course_number = CS3380, Credit_hours = 3, Department = CS
    • SECTION Table:
    • Section_identifier 85: Course_number = MATH2410, Semester = Fall, Year = 07, Instructor = King
    • Section_identifier 92: Course_number = CS1310, Semester = Fall, Year = 07, Instructor = Anderson
    • Section_identifier 102: Course_number = CS3320, Semester = Spring, Year = 08, Instructor = Knuth
    • Section_identifier 112: Course_number = MATH2410, Semester = Fall, Year = 08, Instructor = Chang
    • Section_identifier 119: Course_number = CS1310, Semester = Fall, Year = 08, Instructor = Anderson
    • Section_identifier 135: Course_number = CS3380, Semester = Fall, Year = 08, Instructor = Stone
    • GRADE_REPORT Table:
    • Student_number 17, Section_identifier 112, Grade = B
    • Student_number 17, Section_identifier 119, Grade = C
    • Student_number 8, Section_identifier 85, Grade = A
    • Student_number 8, Section_identifier 92, Grade = A
    • Student_number 8, Section_identifier 102, Grade = B
    • Student_number 8, Section_identifier 135, Grade = A
    • PREREQUISITE Table:
    • Course_number = CS3380, Prerequisite_number = CS3320
    • Course_number = CS3380, Prerequisite_number = MATH2410
    • Course_number = CS3320, Prerequisite_number = CS1310
  • Sample Operational Queries:
    • Retrieving and displaying the complete grade transcript for student 17 or 8.
    • Generating a section roster of all students enrolled in the CS3380 Database course section offered in Fall 08, alongside their final assigned grades.
    • Querying all prerequisite course requirements for CS3380 (CS3320 and MATH2410).
  • Sample Update Transactions:
    • Modifying student Smith's class standing from year 1 to year 2.
    • Adding a newly scheduled SECTION for the CS3380 course in the current semester.
    • Inserting a new grade entry into GRADE_REPORT for student 8 in section 135.

Database Approach versus Traditional File Processing

  • Traditional File Processing Architecture:
    • Each independent user/application defines and manages its own separate data files.
    • Lack of a centralized framework connecting disparate application programs.
    • Key Structural Drawbacks:
    • Uncontrolled Data Redundancy: Duplicate data stored across multiple isolated files.
    • Program-Data Dependence: Changes to file structures force extensive modifications to all source code referencing those files.
    • File Incompatibility: Dissimilar file structures prevent integration across departments.
    • Weak Data Security and Integrity: Inconsistent access controls and rule enforcement.
    • Inefficient Data Sharing: Inability to support simultaneous concurrent file access safely.
  • Database Approach Architecture:
    • A single centralized repository maintains data defined once and subsequently accessed by multiple applications and users.

Fundamental Characteristics of the Database Approach

  • Self-Describing Nature of a Database System:
    • A database system contains not only the target data records, but also complete structural descriptions, data types, and constraints (Meta-data).
    • Meta-data is stored securely inside a centralized DBMS Catalog or Data Dictionary.
    • The DBMS Catalog is actively utilized by both DBMS internal software modules and end users who require structural schema details.

An example of a database catalog for the database in Figure 1.2

  • Structure of the DBMS Catalog (Figure 1.3 Example):
    • RELATIONS Catalog Table:
      • STUDENT (No_of_columns = 4)
      • COURSE (No_of_columns = 4)
      • SECTION (No_of_columns = 5)
      • GRADE_REPORT (No_of_columns = 3)
      • PREREQUISITE (No_of_columns = 2)
    • COLUMNS Catalog Table:
      • Column Name: Data_type = Character (30), Belongs_to_relation = STUDENT
      • Column Student_number: Data_type = Character (4), Belongs_to_relation = STUDENT
      • Column Class: Data_type = Integer (1), Belongs_to_relation = STUDENT
      • Column Major: Data_type = Major_type (Enumerated type containing all known majors), Belongs_to_relation = STUDENT
      • Column Course_name: Data_type = Character (10), Belongs_to_relation = COURSE
      • Column Course_number: Data_type = XXXXNNNN (Custom type: 4 alphabetic characters followed by 4 digits), Belongs_to_relation = COURSE
      • Column Prerequisite_number: Data_type = XXXXNNNN, Belongs_to_relation = PREREQUISITE
    • Program-Data Independence and Program-Operation Independence:
  • Program-Data Independence: Data storage structures are cataloged independently from application source code. Storage formats can be modified without altering application code.
  • Program-Operation Independence: Operations consist of an interface (operation name and parameter data types) and an implementation. Operational code can be altered internally without invalidating user applications.
    • Data Abstraction:
  • Provides conceptual data representations that deliberately hide low-level physical storage details and operation execution mechanisms.
  • Data Models: Conceptual abstraction tools using logical entities, attributes, and relationships to represent target domains.
    • Support for Multiple Views of Data:
  • View: A customized subset of the database formatted specifically for a given user role.
  • Virtual Data: Views frequently present derived data computed on the fly from primary storage files, rather than physically stored tables.
    • Multi-User Transaction Processing and Concurrency Control:
  • Concurrency Control Subsystems: Guarantee that simultaneous updates executed by multiple users do not create data inconsistency or state corruption.
  • Online Transaction Processing (OLTP): High-speed analytical database architecture engineered for concurrent real-time transactions.
  • Fundamental Transaction Properties:
    • Isolation Property: Each executing transaction is fully isolated from other concurrently running transactions, preventing interference (e.g., airline ticket selection occurring independently of concurrent seat searches).
    • Atomicity Property: Guarantees that all operations contained within a single transaction complete successfully, or all are entirely rolled back (all-or-nothing execution, e.g., a ticket booking transaction cannot commit unless payment processing succeeds).

Roles and Database Personnel

  • Actors on the Scene (Direct Database Users and Creators):
    • System Analysts: Determine end-user business requirements and define technical specification targets for database implementation.
    • Database Designers: Determine data contents, design logical schemas, choose storage structures, and establish system constraints.
    • Application Programmers: Translate system specifications into operational software code, perform debugging, testing, and long-term application maintenance.
    • Database Administrator (DBA):
    • Exercises centralized administrative control over the entire database environment.
    • Manages authorization and grants/revokes user access permissions.
    • Monitors resource usage and system performance.
    • Executes database backup routines and disaster recovery procedures.
    • Procures required hardware and software infrastructure resources.
    • Holds primary operational accountability for database integrity and errors.
    • End Users:
    • Parametric / Naive Users: Rely on standardized, pre-tested query interfaces and canned transactions (e.g., bank tellers, airline/hotel reservation agents).
    • Casual Users: Require distinct information at irregular intervals; utilize expressive high-level query languages (e.g., middle and top-level managers, seasonal analysts).
    • Sophisticated Users: Engineers, scientists, and data analysts who master the full technical capabilities of the DBMS to execute complex domain-specific requests.
    • Standalone Users: Maintain personal desktop databases using off-the-shelf application software (e.g., personal tax management software users).
  • Workers Behind the Scene (Database Tool and Platform Creators):
    • DBMS System Designers and Implementers: Build the underlying core DBMS software engines.
    • Tool Developers: Construct auxiliary utility software packages (e.g., GUI design tools, performance monitors, schema analyzers).
    • Operators and Maintenance Personnel: Manage physical computing hardware, power systems, and networking infrastructure supporting the database software.

Advantages and Technical Capabilities of DBMS

  • Controlling Data Redundancy:
    • Normalization techniques eliminate redundant data storage.
    • Controlled Redundancy (Denormalization): Strategically used in specific instances to accelerate query execution performance.
  • Restricting Unauthorized Access:
    • Security and authorization subsystems enforce role-based access privileges, restricting users to authorized operations.
  • Persistent Storage for Program Objects:
    • Overcomes the Impedance Mismatch Problem where object-oriented programming language data structures mismatch traditional database tables.
    • Object-Oriented DBMS (OODBMS) provides native persistence for complex data structures (e.g., C++ objects).
  • Efficient Query Execution and Optimization:
    • Indexes and Buffering: Memory buffering and indexing structures accelerate physical data access.
    • Query Optimizer Engine: Evaluates multiple execution strategies to select the most efficient execution plan for complex queries.
  • Robust Backup and Recovery Subsystems:
    • Automatically restore database state to a consistent state following hardware crashes or software failures.
  • Integrity Constraint Enforcement:
    • Referential Integrity: Mandates valid relationships between dependent records (e.g., every SECTION record must reference a valid existing COURSE record).
    • Key / Unique Constraints: Guarantees that primary keys hold non-null, completely unique identifiers across all table records (e.g., Course_number).
  • Active Triggers and Stored Procedures:
    • Trigger: A programmatic rule executed automatically by the DBMS upon a table modification event (INSERT, UPDATE, DELETE).
    • Stored Procedures: Server-side compiled routines designed to enforce complex organizational business logic directly inside the database.
  • Organizational and Economic Benefits:
    • Drastically reduces application development turnaround time.
    • Schema Flexibility: Schema modification can occur seamlessly without breaking independent application programs.
    • Instantaneous updates available system-wide in real time.
    • Economies of Scale: Centralizing computational resources and network capacity reduces total cost of ownership across departments.

Historical Evolution of Database Technology

  • Phase 1: Hierarchical and Network Database Models:
    • Engineered to manage massive volumes of uniform data structures in early mainframe computing.
  • Phase 2: Relational Database Model:
    • Introduced logical data abstraction, decoupling physical disk storage from conceptual schemas.
    • Built upon rigorous mathematical set theory (relational algebra) to offer flexible application query capabilities.
  • Phase 3: Object-Oriented Database Management Systems (OODBMS):
    • Emerging demand for handling highly complex data structures in specialized domains, such as Computer-Aided Design (CAD), Computer-Aided Manufacturing (CAM), and multimedia publishing.
  • Phase 4: Web Integration and Extensible Markup Language (XML):
    • XML established as the universal data exchange standard across heterogeneous web applications, databases, and e-commerce platforms.
  • Phase 5: Extended Database Functionality and Specialized Analytics:
    • Integration into Enterprise Resource Planning (ERP) and Customer Relationship Management (CRM) platforms.
    • Integration of Information Retrieval (IR) technology:
    • Blends traditional structured database management with unstructured text document search.
    • Utilizes keyword indexing, vector-space search models, automated text classification, and topic relevance scoring.

Guidelines for Omitting DBMS Software

  • Standard file systems or simple embedded files are preferred over a full DBMS under the following operational conditions:
    • Simple, small-scale database applications with static requirements that are guaranteed never to change.
    • Stringent real-time latency constraints that cannot tolerate DBMS software execution overhead.
    • Embedded hardware environments with severe physical memory and storage limitations.
    • Single-user environments where concurrent data access and multi-user security controls are entirely unnecessary.