Introduction to Database Management System

Fundamentals of Data and Database Concepts

  • Data:

    • Represents raw, unorganized facts that require processing.
    • Must be interpreted by a human or machine to derive meaning.
    • Lacks context and is inherently meaningless on its own.

  • Information:

    • Created once raw data is processed, organized, and structured into a meaningful context.
    • Possesses clear meaning, context, and purpose.

  • Database:

    • An organized collection of structured and interrelated information, typically stored electronically within a computer system.
    • Refers to software designed to store, manage, and organize data efficiently.
    • Analogy: Functions like a physical file cabinet where data is organized into different sections called tables. When a specific file is needed, the relevant section (table) is accessed to retrieve the required file (data).

Data flow from raw data to database storage and server processing

Database Management System (DBMS) Overview

  • Definition:

    • Software designed to manage a large collection of structured, related data.
    • Facilitates effective storage, manipulation, and retrieval of data as required.
    • Acts as an operational interface between stored data and software applications.

  • Core Functions and Capabilities:

    • Data Retrieval: Data can be queried and retrieved using Structured Query Language (SQL) queries and relational algebra.
    • Security: Implements robust security measures to protect sensitive data against unauthorized access.
    • Backup and Recovery: Provides built-in mechanisms to ensure data backup and recovery in the event of system failure.

  • Real-World Examples of DBMS Software:

    • MySQL
    • Microsoft Access
    • Oracle
    • FoxPro
    • IBM DB2
    • Primary Real-World Application Example: Banking systems, where software tracks all financial transactions and account balances.

Interaction between End User, Database Applications, DBMS, and Database

File Processing System

  • Definition:

    • A conventional approach to arranging and organizing files on a storage medium, such as a hard disk.
    • Groups files into directories, which can contain additional subfolders and files to assist in file retrieval.

  • Core Operations:

    • Manages basic file operations, file naming conventions, access control rules, and directory structures.

  • Examples of File Systems:

    • NTFS: New Technology File System.
    • EXT: Extended File System.

Directory hierarchy showing Master File Directory, User File Directory, and Files

  • Disadvantages of File Processing Systems:
    • Data Redundancy: Duplication of identical data across multiple files.
    • Data Inconsistency: Discrepancies among multiple copies of the same data.
    • Lack of Data Integration: Difficulty in combining data stored in disparate files.
    • Limited Data Sharing: Inability to seamlessly share data across different applications or users.
    • Less Secure: Inadequate security mechanisms to restrict unauthorized access.
    • Decentralization of Data: Data is scattered across various locations without centralized control.

Comparative Analysis: File System vs. DBMS

  • Structure:

    • File System: Method of arranging files within a storage medium on a computer.
    • DBMS: Dedicated software designed specifically for managing structured databases.

  • Data Redundancy:

    • File System: Redundant data can easily be present.
    • DBMS: Eliminates data redundancy.

  • Backup and Recovery:

    • File System: Does not provide an inbuilt mechanism for data backup and recovery upon data loss.
    • DBMS: Provides in-house tools for automated backup and recovery even if data loss occurs.

  • Query Processing:

    • File System: Lacks efficient query processing capabilities.
    • DBMS: Contains highly efficient query processing features.

  • Consistency:

    • File System: Offers less data consistency.
    • DBMS: Ensures higher data consistency through the process of normalization.

  • Complexity:

    • File System: Less complex to handle compared to a DBMS.
    • DBMS: Involves higher handling complexity compared to a file system.

  • Security Constraints:

    • File System: Provides minimal security compared to a DBMS.
    • DBMS: Offers advanced security mechanisms to control access.

  • Cost:

    • File System: Less expensive implementation.
    • DBMS: Incurs a comparatively higher cost.

  • User Access:

    • File System: Allows only one user to access data at a single time.
    • DBMS: Allows concurrent data access by multiple users at a time.

Applications of Database Management Systems

  • Banking:

    • Used to store detailed customer account records, transaction histories, payment activities, deposits, and loan processing.

  • Airlines:

    • Used for processing passenger flight reservations and maintaining updated flight schedules.

  • Universities:

    • Stores comprehensive student records, examination marks/results, course registration details, and college organizational details.

  • HR Management:

    • Maintains records on employee profiles, salary structures, payroll details, tax deductions, and paycheck generation.

  • Sales:

    • Stores commercial transactions, customer profiles, product catalogs, and accounting metrics including profit and loss.

Categorization of DBMS Users

  • Naive User:

    • Unsophisticated end-users who lack technical knowledge of DBMS internals.
    • Frequently interact with the database through daily applications to retrieve desired outcomes.

  • Database Administrator (DBA):

    • Responsible for managing, configuring, and securing the database environment.
    • Grants access privileges and ensures only authorized users can access or modify stored data.

  • Sophisticated Users:

    • Technical professionals such as engineers, scientists, and business analysts who are well-versed in database concepts.
    • Develop database applications and interact with the database by directly executing SQL queries through the query processor.

  • Specialized Users:

    • Sophisticated users who write custom database applications outside traditional data-processing frameworks.
    • Develop systems such as Computer-Aided Design (CAD) systems, knowledge-based systems, and expert systems.

Key Advantages of DBMS

  • Reduced Redundancy:

    • Minimizes duplicate data storage across tables.

  • Reduced Inconsistency:

    • Ensures uniform updates across all instances of data.

  • Data Security:

    • Restricts unauthorized access.
    • Utilizes robust security features to ensure only authorized users access confidential enterprise data.

  • Sharing of Data:

    • Provides credentials (username and password) to authenticate users.
    • Enables simultaneous data sharing among multiple users for efficient access and management.

  • Backup and Recovery:

    • Incorporates a backup and recovery subsystem responsible for restoring database integrity following hardware or software failures.
    • Example: If a system crashes mid-execution of a complex update program, the recovery subsystem restores the database to its precise state prior to program execution.

  • Data Integrity:

    • Guarantees that database contents remain accurate, reliable, and valid, preventing erroneous information from being stored.

Key Disadvantages of DBMS

  • High Cost:

    • Significant expenditure required for acquiring hardware and DBMS software, increasing organizational budgets.

  • System Complexity:

    • DBMS software is inherently complex, requiring extensive user and administrator training.

  • Centralized Failure Risks:

    • Centralizing integrated data into a single database creates a single point of failure; power disruptions or storage media corruption can damage the entire database.

  • Concurrency Data Issues:

    • Simultaneous write operations by multiple concurrent users can occasionally result in data loss.

  • Performance Bottlenecks:

    • Extremely large database sizes can degrade system performance and impact operational smoothness.

Relational Database Management System (RDBMS) Concepts

  • Relation:

    • A table containing logically organized and related data structured in rows and columns.

  • Tuple:

    • A single row within a relation representing a discrete data record.

  • Attribute:

    • A single column within a relation representing a specific property or data field.

  • Degree:

    • The total number of attributes (columns) present in a relation.
    • Example: A table with 44 columns has a degree of 44.

  • Cardinality:

    • The total number of tuples (rows) present in a relation.
    • Example: A table with 44 data rows has a cardinality of 44.

  • Primary Key:

    • A set of one or more attributes that uniquely identifies each tuple in a relation.

  • Candidate Key:

    • Any attribute or combination of attributes within a relation capable of uniquely identifying records, serving as potential candidates for the primary key.

  • Alternate Key:

    • Any candidate key that was not selected as the primary key.

  • Foreign Key:

    • A non-key attribute in a current table whose values are derived from the primary key of another related table.

Anatomy of a Relational Database Table showing rows, columns, degree, cardinality, domain, and primary key

Referential Integrity

  • Definition:

    • A system of strict rules enforced by a DBMS to ensure that relationships between records in linked tables remain valid.

  • Function:

    • Prevents users from accidentally deleting or modifying related data across tables.

  • Mechanism:

    • Ensured and operationalized specifically through the implementation of foreign keys.