Database Management Systems Overview

Traditional file environments

• Data is managed in separate files by departments
• Redundant data is stored in multiple files
• Inconsistencies in data can occur
• Programs are directly linked to data, making changes difficult
• Limited flexibility in accessing and using the data
• Inadequate security measures
• Sharing and availability of data is restricted

Database Management Systems (DBMS)

• Centralize data & manages redundant data
• Interface between applications and physical data files
• Separates how data is logically viewed and physically stored
• Addresses the shortcomings of traditional file environments
• Controls data redundancy and eliminates inconsistencies
• Decoupling data from programs
• Enables centralized data management and security

Relational DBMS

• Data is organized in two-dimensional tables
• Each table represents an entity and its attributes
• Tables consist of rows (records) and columns (fields)
• Key field uniquely identifies each record
• Primary key is the key field within a table
• Foreign key is a primary key from one table used in a second table to identify records from the original table

Operations of a Relational DBMS

• SELECT: Retrieves a subset of data based on specified criteria
• JOIN: Combines tables to provide more comprehensive information than individual tables
• PROJECT: Creates a subset of columns, generating tables with specific information

Capabilities of Database Management Systems

• Data definition: Defines data structures and relationships
• Data dictionary: Stores metadata, defining data elements and relationships
• Querying and reporting: Retrieve and manipulate data
• Data manipulation language (DML), including Structured Query Language (SQL), is used to manipulate database data

Designing databases

• Conceptual design: Defines data requirements and relationships
• Physical design: Determines how data is stored and accessed
• Normalization: Optimizes data organization to minimize redundancy & improve data integrity
• Referential integrity: Ensures relationships between tables remain consistent
• Entity-relationship diagram: A visual representation of entities and their relationships

Non-relational Databases, Cloud Databases, and Blockchain

• Non-relational databases (NoSQL): More flexible data model, data distributed across machines, scalable, handle large volumes of structured and unstructured data
• Cloud databases: Suitable for start-ups and smaller businesses, examples: Amazon Relational Database Service, Microsoft SQL Azure
• Blockchain: Distributed ledger technology with encrypted records and transactions shared across a network, used for financial transactions, supply chain management, and medical records

Big Data

• Massive datasets of unstructured or semi-structured data
• Volumes exceeding the capacity of traditional DBMS
• Offers opportunities to identify patterns, relationships, and anomalies
• Requires specialized tools and technologies for management and analysis

Business Intelligence Infrastructure

• Data warehouse: Consolidates and standardizes data from operational systems for analysis & reporting
• Data mart: Focused subset of a data warehouse, specific to a business function or subject
• Hadoop: Enables distributed parallel processing of large datasets across multiple computers, components: Hadoop Distributed File System (HDFS), MapReduce, and Hbase
• In-memory computing: Uses RAM for data storage, reducing processing time, optimized hardware required
• Analytic platforms: High-speed platforms, combining relational and non-relational tools for large datasets

Analytical Tools

• Multidimensional data analysis (OLAP): Enables multidimensional data views for analysis, supports ad-hoc queries
• Data mining: Identifies patterns and relationships in data to predict behavior, types: associations, sequences, classification, clustering, forecasting
• Text mining: Extracts key elements from unstructured text data, includes sentiment analysis software
• Web mining: Analyzes web content, structure, and usage data to extract patterns and insights

Databases and the Web

• Web interface enables access to internal databases for customers or partners
• Components: Web server, Application server/middleware, Database server
• Benefits: Ease of use via web browser, minimal database modifications, cost-effective

Data Governance

• Establishes policies and procedures for managing data as an organizational asset
• Includes rules for sharing, dissemination, acquisition, standardization, classification, and inventorying information

Data Quality Assurance

• Addresses data accuracy and completeness
• Involves identifying and correcting data errors
• Establishing data editing routines for ongoing data quality
• Data quality audit: Assessing the quality of data
• Data cleansing: Removing inconsistencies and errors from data sets