Database Management Concepts

• A database is an organized collection of structured information or data stored electronically.
• Databases are managed by a Database Management System (DBMS), allowing users to create, retrieve, update, and delete data efficiently.
• A database can be thought of as an electronic filing system.
• Traditional paper-based filing systems are slow, inflexible, and prone to errors.
• Database servers are a centralized repository for storing, managing, and retrieving data, enabling efficient user and application access and manipulation.
• Databases streamline operations, enable data-driven decision-making, and improve customer experience across industries.
• Databases have become important in modern computing, enabling efficient, secure, and reliable data management.

Examples of databases in management sectors

• Customer Databases track customer purchases, preferences, and details.
• Financial Records: Banks and financial institutions maintain databases with transactions, balances, and loan information.
• Employee Records Databases are used by HR for detailed employee information like personal details, employment history, job performance, and benefits.
• Inventory Management Databases are used by retailers, warehouses, and manufacturers to track stock levels, order histories, and supplier details.

Evolution of Databases

• The shift from paper to digital databases started in the mid-20th century.

1960s: The Birth of Digital Databases

• Hierarchical and Network System (IMS) was the first widely adopted database system.
• Databases organized data in a tree-like structure.

1970s: The Relational Model

• E.F. Codd introduced the Relational Model.
• Data is stored by organizing data into tables.
• Structured Query Language (SQL) allows querying data.

1980s-1990: Rise of Commercial DBMS and Advanced DBMS

• Commercial Database Management Systems (DBMS) and Advanced DBMS (ADBMS), such as Oracle, Microsoft SQL Server, and IBM DB2, emerged.
• Features included integrity, security, multi-user access, and transaction management.

2000s to Present: NoSQL and Big Data

• MongoDB, Cassandra, and CouchDB cater to the demands of big data, handling unstructured data and supporting social media, e-commerce, and real-time analytics.
• Improved performance along with large data volumes.

Importance of Data in Business Decision Making

• Data plays a key role in data-driven decision-making.
• Data-driven decision making improves decision accuracy, reduce risks and enhance operational efficiency

Data can be used to identify

• Opportunities, track consumer behavior, and optimize processes.

Example in Marketing

• Targeted Advertising: Facebook, Instagram, TikTok, and Google use data analytics to deliver personalized ads by analyzing user browsing history, interests, and demographics.
• Tailored marketing campaigns increase customer engagement and conversions.

Example in Finance:

• Credit Risk Assessment uses predictive analytics to assess lending risk by analyzing historical credit data and guiding decisions on loan approvals and interest rates.

Real-world Success Stories

• Netflix analyzes viewing patterns to offer personalized content recommendations.
• YouTube uses behavior data like watch history and engagement metrics to personalize recommendations and optimize content strategies.

Benefits of data

• Improving Efficiency
• Gaining Customer Insights
• Predictive Analysis

Challenges to data

• Volume
• Accuracy
• Decision-Making Biases
• Data Privacy and Compliance
• Resistance to change
• Skill Gap

Data and Information Types

• Data is a collection of raw facts, figures, or observations that, by itself, have no meaningful information.
• Data include numbers, characters, symbols, or media files.
• Information is processed, organized, and structured data that holds meaning and can be used for decision making.
• Information shows relationships between data points and is easily presented and interpreted.

Structured Data

• Structured data is highly organized:
• It is easily searchable and stored in a fixed format or schema.
• It resides in relational databases or spreadsheets.

Characteristics of Structured Data:

• Predefined Format: Data is organized in a fixed format where each field has a specific purpose and data type (e.g., text, number, date, Boolean).
• Easy to Search: It can be easily searched using standard algorithms, and queries can be performed quickly and efficiently.
• Consistency: All data follows the same pattern, making it easier to analyze and process.
• Organized Schema: It follows a well-defined schema or data model, ensuring each field has a specified type and purpose and avoids duplication or data type mismatch.
• Highly Scalable: Performance should not degrade even with large data volumes.
• Examples include index types of information, employee details in an Excel sheet, and customer information in a database.

Unstructured Data

• Unstructured data lacks a predefined format, making it complex to store and analyze using traditional databases.
• It often comes in large volumes and is text-heavy.
• Examples include social media posts, content from emails, videos, and images.

Characteristics of Unstructured Data:

• No Predefined Format: It does not follow a specific structure or format.
• Difficult to Search: It can be challenging to find and retrieve data promptly.
• High Volume: Generated in large quantities from sources like social media, emails, and multimedia content.
• Variety: It comes in many forms, from text documents to multimedia files.
• Advanced Processing Needs: It requires advanced processing techniques for analysis and interpretation.
• A DBMS is a software system that facilitates the creation, management, and manipulation of databases.
• Acts as an interface between end users, applications, and the database itself, ensuring data is stored, organized, retrieved, updated, and deleted effectively.
• Provides a systematic way to ensure data integrity, security, and consistency.

Functions of a DBMS

• Data Storage, Retrieval, and Management
• Data Security: User authentication and authorization for read, write, and delete operations.
• Data Integrity and Consistency: Maintaining accurate and consistent data, preventing discrepancies.
• Backup and Recovery: Regularly saving database copies for safety and restoration.
• Concurrency Control: Managing multiple users without causing data inconsistency.
• Data Dictionary Management
• Transaction Management: Ensuring transactions are completed successfully or not at all, maintaining integrity.

Advantages of DBMS

• Improved Data Sharing: Data sharing among users and applications.
• Increased Data Security: Advanced security measures, helping organizations comply with regulations and safeguard information.
• Reduced Data Redundancy: Minimizes data duplication by centralizing data storage, leading to more efficient data management and reduced storage costs.
• Better Data Integration: Integrates data from different sources, providing a unified view for comprehensive analysis.
• Enhanced Data Management: Streamlines data handling processes, improving efficiency in data entry, retrieval, and reporting.

Disadvantages of DBMS

• Complexity: Requires specialized knowledge and training.
• Cost: Including initial setup, licensing, and ongoing maintenance.
• Performance Overhead: Large volumes of transaction data can impact performance.
• Data Vulnerability: Offers security but is still a target for cyberattacks.
• Dependency on the DBMS Vendor: Difficult to switch or integrate with new technologies if dependent on a specific vendor.

Types and Components of DBMS

• Components:
  • Data: Actual information stored on the database.
  • Hardware: Physical devices such as hard drives and network storage.
  • Software: DBMS software itself and any related application or client.
  • Database Access Language: Used to access and manipulate data (e.g., SQL).
  • Database Engine: Core software component.
  • Query Processor
  • Data Dictionary
  • Report Writer: Tools for creating formatted reports.

Types of DBMS:

• Relational DBMS (RDBMS): Common type (e.g., MySQL, Oracle, Microsoft SQL Server).
• Object-Oriented DBMS: Stores data as objects, methods, and attributes.
• Document DBMS: Operates on documents (e.g., JSON, XML).
• Key-Value: Uses key-value pairs (e.g., Redis, Amazon DynamoDB).
• Graph DBMS: Represents data in a graph format (e.g., Neo4j, Amazon Neptune).
• Column-Oriented: Stores data tables as columns rather than rows (e.g., Apache Cassandra, HBase).
• Relational Database Management System (RDBMS) are designed to store and manage data in a structured format using interlinked tables linked by keys and relationships.
• RDBMS maintain data integrity, reduce redundancy, and use primary and foreign keys.

Tables

• A table in a relational database is a collection of related data organized into rows and columns, where each table represents a specific entity, records (rows) and fields (columns).
• Columns (Fields): Define attributes or characteristics of the entity. Each column has a specific data type such as text, integer, date, boolean.
• Rows (Records): Contains the data for each instance of the entity, with each row holding a unique set of information.
• Relationships between tables are established through keys, such as primary keys (PK) ans foriegn keys (FK).
• Primary Key (PK): A column or combination of columns that uniquely identifies each row, such as CustomerID or OrderID.
• Foreign Key (FK): A column that links one table to another by referencing the primary key of the related table
• Queries are commands that interact with a relational database.
• Users can retrieve, manipulate, and update data stored in tables.
• Structured Query Language (SQL) is the standard language for querying relational databases.

SQL commands:

• SELECT: Retrieves data from one or more tables.
• INSERT: Adds new records to a table.
• UPDATE: Modifies existing records in a table.
• DELETE: Removes records from a table.
• Queries enable extraction based on set conditions
• Queries can join multiple tables to get comprehensive insights, combining customer and order information.

Reports present data in a structured format to support decision-making.

• Often include tables, charts, and visuals to highlight metrics and trends.
• Data Visualization converts raw data into interactive charts, graphs, and dashboards.
• Visual tools help to interpret complex data, identify insights, and support data-driven decisions.
• Reports and visualization aid in identifying trends and patterns, communicating insights in an understandable format and enabling more informed, timely decisions.

Introduction to SQL

• SQL is a standardized programming language used for managing and manipulating relational databases.

Key benefits of SQL

• Ease of Use: With simple commands it is easy to learn and apply.
• Efficient Data Retrieval: Can retrieve large amounts data quickly
• Flexibility: Can use various tables at once SQL commands fall into the following four categories:
• Data Definition Language (DDL): Define and modify the database object's strcuture: CREATE, ALTER, DROP, TRUNCATE.
• DATA Manipulation Language (DML): Manipulates data within a database: SELECT, INSERT, UPDATE, DELETE.
• Data Control Language (DCL): Controls access to data within a database:GRANT, REVOKE.
• Transaction Control Language (TCL): Manages changes made DML statements: COMMIT, ROLLBACK, SAVEPOINT.
• An understanding of data storage and retrieval in databases is vital for designing efficient database systems.

Areas where an understanding helps

• Designing tables, columns, and relations.
• Writing queries.
• Troubleshooting performance issues.

Data Storage

• Physical Storage: Hard drives (HDDs) and Solid-state drives (SSDs).
• Files and Pages: Databases organize into files, further divided into smaller sections.

Smaller Sections

• Tables and Indexes: Relational databases data is logically organized into tables
• Record Storage: Data resides within tables in records (rows)
• Data Types: Integer, text, and dates, being some examples.
• Compression: Aids in reducing storage space.
• Data retrieval consists of accessing and retrieving data with querying.

Data retrieval

• Select specific columns and rows, relationship-based tables, apply filters and aggregations for precise search results.
• Parsing, optimization, and execution help query processing.
• Access Methods: Table scan, row scan, and other DB level scans, can be used.
• Access is affected by Buffer Management and intermediated storage.
• Concurrency control exists so multiple users can use the database simultaneously.
• Transaction management is when database operations are treated as a single unit.
• Big Data refers to the years of information age in how organizations collect, analyze, and utilize vast data amounts.
• Big Data is extremely complex data sets. Big data is hard to analyze using traditional methods, and also reveals trends in human behavior.

Characteristics of Big Data

• Volume
• Velocity
• Variety

Big Data is sourced from

• Social Media: Facebook, Twitter, and Instagram
• Internet of Things (IoT): Smart home and devices(rings, watches, and glasses).
• Transactional Data: An organization's purchase history records business data.
• Machine Data: Industrial equipment and sensor generated data.
• Biometrics: Genetics, facial recognition, retinal scans and handwriting data.
• Human-Generated Data: Email, voice recording, surveys, and conversation data.
• Big Data has applications across a variety of business sectors that improve decision-making, optimize operations and enhance customer experience
• Retail companies can streamline supply chains by tailoring marketing towards analytical buying patterns.
• In Finance, Fraud detection, risk management and identifying anomalies are a couple examples.
• Analyzing historical data after transaction patterns and identifying real-time anomalies is Healthcare can use analytics in treatment plans and predict health risks.
• Analyzing customer usage patterns and feedback can enhance personalization when it comes to services.

Ethical Guidelines

• Data Security involves practices and technologies that protect sensitive information and ensure confidententility, integrity, and availability with authorized access.
• Ethical considerations focus on fair moral responsibilities, emphasizing privacy rights, informed consent, and data security.
• Ethical data handling is achieved through legal frameworks and transparency in the event one handles individual data. Protecting it from bias can also help.
• Ethics also play a part into creating a trusted organization and respecting moral social values.