15 - Database in a Nutshell - DBLC Trends and More

Background Knowledge for Database Area

The database field sits at the intersection of three main areas:

• Artificial Intelligent
• Computer Programming
• Database Management

These three areas converge into Database Administration

Think of this like a Venn diagram where database administration is the sweet spot where coding skills, AI knowledge, and database expertise all come together - like being a chef who needs to know ingredients (data), cooking techniques (programming), and how to run a kitchen efficiently (management).

Database System Development Life Cycle (DBLC)

Understanding SDLC

Before discussing DBLC, we need to understand the broader context:

• System Development Life Cycle (SDLC) is a series of stages describing the process for developing a system
• In many cases, "system" is replaced by "software," becoming Software Development Life Cycle (same acronym: SDLC)
• This emphasizes the series of stages describing the process for developing software

SDLC & ISLC & DBLC

• System Development Life Cycle (SDLC) is a series of stages describing the process for developing a system
• Typically, the term "system" in SDLC refers to an information system
• Thus, an Information System Development Life Cycle (ISLC) is a series of stages describing the process for developing an information system

Information & Database Systems

Are they the same or different?

Information System components:

• Information Technology
• People's activities (e.g., operations, decision making)
• Hardware/Computer Network
• Data
• Software

Database System (subset of Information System):

• DBMS
• Database Application Software
• Operating System Software
• Database
• Data Model
• Database Software

Think of an information system as a complete restaurant operation (staff, kitchen, ordering system, inventory), while the database system is specifically the kitchen and recipe management system - it's a crucial part but not the whole operation.

SDLC Stages

The traditional SDLC consists of five main stages:

1. Planning
2. Analysis
3. Design
4. Implementation
5. Maintenance

These stages form a cyclical process.

DBLC Stages

The Database Development Life Cycle includes:

1. Database Planning
2. System Definition
3. Requirements Collection and Analysis
4. Database Design
5. Application Design
6. Prototyping (optional)
7. Implementation
8. Database Conversion and Loading
9. Testing
10. Operational Maintenance

Stage 1: Database Planning

Key Activities:

• Clearly define the mission statement which defines the major goal of the database system
• Identify the mission objectives which describe particular tasks that the database system must support and those tasks must be aligned with the major goal
• Develop standards that govern:
  • How data will be collected
  • How the format should be specified
  • What documentation will be needed
  • How design and implementation should proceed

Like planning a library: the mission might be "provide accessible information to the community," objectives could be "catalog 10,000 books by year-end," and standards would define how to categorize books, label shelves, and organize the card catalog.

Stage 2: System Definition

Key Activities:

• Define scope and boundaries of the database:

  • What will be included/not included in database system
  • What other systems interact with the database system

• Identify major users (both current and future), e.g.:

  • With respect to job role (e.g., manager, supervisor, and staff)
  • With respect to enterprise application area (e.g., marketing, personnel, and inventory)

• Identify user views: what is required of a database system from the perspective of each kind of user

Stage 3: Requirement Collection and Analysis

Key Activities:

• Use fact-finding techniques (covered in next lecture)

• Collect and analyze requirements which is information about the part of the enterprise to be served by the database, e.g.:

  • A description of the data used or generated
  • The details of how data is to be used or generated
  • Any additional requirements for the new database system

• Analyze requirements: Functional / Non-functional

• Come up with requirements specifications

Users' Requirement Specifications

Data requirement specifications:

• What kinds of data are needed for each user view
• What the characteristics of the required data are
• What the constraints on the required data are

Transaction requirement specifications:

• How to manipulate the required data with respect to the user view
• Manipulation includes: data entry, data querying, data insertion, data deletion, and data modification

System Requirement Specifications

Examples include:

• Initial database size
• Database rate of growth
• Types and average number of record searches
• Networking and shared access requirements
• System performance
• System security
• Backup and recovery
• Legal issues

Stage 4: Database Design

Objective:

• Construct and develop a data model that will support enterprise's mission statement and mission objectives for the required database system

Stage 5: Application Design

Key Points:

• Parallel to database design
• Not possible to complete application design until the database design has taken place
• There must be a flow of information between application design and database design

Two parts:

1. Design user interface
2. Design application program

User Interface Design

Forms:

• A user interface that allows the application to receive data from the user and keep it into the database

Reports:

• A user interface that allows the application to display the processed data to the user

• Knowledge of UX/UI can be applied

User Interface Design Requirements

• Ensure that all the functionality stated in the users' requirement specifications is present

• Support a variety of database transactions which are actions, or series of actions, that access or change the content of the database:

  • Retrieval transactions: Used to retrieve data for display on the screen or in the production of a report

  • Modification transactions: Used to insert new data items, delete out-of-date data, and update existing data items in the database

  • Mixed transactions: Involve both the retrieval and the updating of data

Think of transactions like ATM operations: checking your balance is a retrieval transaction, depositing money is a modification transaction, and transferring money between accounts is a mixed transaction (retrieve from one, modify both).

Stage 6: Prototyping

Nature:

• Optional to the DBLC
• Construct a working model of a database system
• Allow database designers and users to visualize and evaluate how the final system will look and function

Two common strategies:

1. Requirements prototyping: Once the requirements are completely determined, the prototype is discarded

2. Evolutionary prototyping: Once the requirements are completely determined, the prototype is not discarded but with further development becomes the working database system

Stage 7: Implementation

Key Activities:

• Implement a database and its application with respect to the data model obtained from the design stage

• Implement security and integrity controls for the system

• Cover the use of Data Definition Language (DDL) of a selected DBMS to create a database structure and empty database files

• Cover the use of Data Manipulation Language (DML) of the selected DBMS, embedded with a host programming language (e.g., Python, Java, JavaScript, C, C++, C#)

Stage 8: Data Conversion and Loading

Key Activities:

• Transfer any existing data into the new database

• Re-configure the existing applications to run on the new database

• Normally, DBMS has provided tools for data conversion and loading

• Whenever conversion and loading are required, the process should be properly planned to ensure a smooth transition to full operation

Stage 9: Testing

Key Activities:

• Backup existing database may be needed

• Run the database and database system on a separate hardware system (if possible)

• Find errors

• Conform to specifications and requirements

• Users of the new database system should be involved in the testing process

• After testing is complete, the database system is ready to be "signed off" and handed over to the users

Stage 10: Operational Maintenance

Key Activities:

• Continuously monitor, maintain, and perhaps upgrade the implemented database system

• Monitor performance of the system

• May have to tune or reorganize database periodically

• When necessary, new requirements are incorporated into the database system through the preceding stages of the lifecycle

Your Future Career Path in Database Area

Database Users & Roles

Database Users:

• End-Users (Naïve & Sophisticated users)
• DB Developers 👉
• DB Designers (Logical & Physical Designers) 👉
• DB Administrators (DBA)
• Data Administrators

Who is Responsible for DBLC Stages?

Analysis Phase:

• DB Analyst

Design Phase:

• DB Designer
• Includes: Conceptual database design, Logical database design, Physical database design
• Also includes: Application design and DBMS selection (optional)

Implementation Phase:

• DB Developer
• Includes: Implementation, Data conversion and loading, Testing
• Optional: Prototyping

Maintenance Phase:

• DBA (DB Administrator)
• Includes: Operational maintenance

Future Careers in Database Area

Career paths include:

• DB Admin
• DB Designer
• DB Developer
• DB Architect
• DB Engineer
• DB Scientist
• Software Developer

Success factors (visualized as a career tree):

• Abilities
• Perseverance
• Regulation
• Success
• Leadership
• Contacts
• Education
• Learning
• Hard working
• Job Experience
• Knowledge
• CV

Trends and State of the Art in Database Area

The Data Explosion

A Day in Data (as of 2019)

Key statistics generated daily:

• 500m tweets are sent every day
• 294bn billion emails are sent
• 4PB of data created on Facebook (350m photos, 100m hours of video)
• 65bn messages sent on WhatsApp
• 3.9bn Google searches
• 95m photos and videos are shared on Instagram
• 4TB of data created by each connected car
• 28PB of new data from wearables

Total: 463EB of data will be created every day by 2025

An exabyte (EB) is 1 billion gigabytes - to put this in perspective, if each gigabyte were a grain of rice, 463 exabytes would be enough rice to cover a football field 100 feet deep!

Volume of Data Growth

Data volume in Zettabytes ($10^{21}$ bytes):

Reference: Statista 2021

360 Degree View of Business Customers

Customer 360 Cycle

The cycle includes touchpoints from:

• Ordering
• Service Call
• Location
• Channels
• Devices
• Network
• CRM
• Apps
• Billing
• Social

Actionable Data Insights

Businesses can use this to:

✓ Better understand and better engage your customers

✓ Respond to the convergence of customer expectations

✓ Driver of brand perception

Data is the Asset

Key Concept: In the modern business landscape, data has become one of the most valuable assets an organization can possess.

Aligning Business Strategy and Data Strategy

A Successful Data Strategy Links Business Goals with Technology Solutions

"Top-Down" alignment with business priorities:

• Business Strategy ↔ Data Strategy alignment through Data Governance

Managing the people, process, policies & culture around data:

• People
• Process
• Data Governance (Policy, Culture)

Leveraging & managing data for strategic advantage:

• Master Data Management
• Data Warehousing
• Business Intelligence
• Big Data Analytics
• Data Quality
• Data Architecture & Modeling

Coordinating & integrating disparate data sources:

• Data Asset Planning & Inventory
• Data Integration
• Metadata Management

"Bottom-Up" management & inventory of data sources:

• Database
• Big Data
• Unstructured Data
• Semi-structured Data
• Document & Content

Reference: Data Management Course by MUEG

Knowledge Management Pyramid

The hierarchy from bottom to top:

$\boxed{\text{Wisdom}}$ - Applied

$\boxed{\text{Knowledge}}$ - Context

$\boxed{\text{Information}}$ - Meaning

$\boxed{\text{Data}}$ - Raw

Think of this like cooking: Data is the raw ingredients (flour, eggs, sugar), Information is the recipe with measurements, Knowledge is understanding why certain ingredients work together, and Wisdom is being able to improvise and create new dishes based on that understanding.

Nature of DATA in Business

Visual representation showing the transformation of data:

1. DATA - Scattered, unorganized pieces
2. SORTED - Organized by type/category
3. ARRANGED - Structured in meaningful ways
4. PRESENTED VISUALLY - Clear visual representation for insights

Data Collection

Data flows from various sources:

Social Media & Communication:

• LinkedIn
• Instagram
• Facebook
• Email
• YouTube
• Twitter
• WhatsApp
• TV
• Newspapers
• User profiles

Collection Points:

• Marketing Tools
• Touchpoints
• Aftersales

Data Preparation

The Data Preparation Pipeline

\text{Discovery} \rightarrow \text{Structuring} \rightarrow \text{Cleaning} \rightarrow \text{Enriching & Blending} \rightarrow \text{Optimizing & Publishing}

YOU HAVE TO MAKE THIS MORE EFFICIENT → TO DRIVE MORE VALUE HERE

Time Distribution:

• 80% of Time Spent on preparation
• 20% of Time Spent on value generation

Definition:

Data Preparation is the process of cleaning, structuring and enriching raw data into a desired output for analysis.

Source: TRIFACTA

Data Analysis

Four Levels of Data Analysis

1. Descriptive Analytics

Question: What happened?

• Based on Live Data
• Tells what's happening in real time
• Accurate & Handy for Operations management
• Easy to Visualize
• Value: Low
• Complexity: Low

2. Diagnostic Analytics

Question: Why did that happen?

• Automated RCA (Root Cause Analysis)
• Explains "why" things are happening
• Helps troubleshoot issues
• Value: Medium
• Complexity: Medium

3. Predictive Analytics

Question: What's likely to happen?

• Based on historical data, and assumes a static business plans/models
• Helps Business decisions to be automated using algorithms
• Value: High
• Complexity: High

4. Prescriptive Analytics

Question: What to do next?

• Defines future actions – i.e., "What to do next?"
• Based on current data analytics, predefined future plans, goals, and objectives
• Advanced algorithms to test potential outcomes of each decision and recommends the best course of action
• Value: Very High
• Complexity: Very High

Source: © Arun Kottolli

Data Visualization

Definition

• The process of creating graphical representations of your information
• Need to choose the proper representation to suit the objectives

Popular Tools

• Tableau Software
• Power BI
• Superset
• Data Studio

Common Chart Types

Basic Charts:

• Bar chart
• Stacked bar chart
• Line graph
• Gantt chart
• Polar area diagram

Advanced Visualizations:

• Scatter plot
• Calendar heatmap
• Stacked area chart
• Sparkline
• Column sparkline

Business Data Analytics and Business Analyst (BA)

Business Data Analytics

Definition & Purpose:

• Aims to identify opportunities to grow, optimize, and improve the organization's business processes
• Often tasked with a specific area of business such as:
  • Supply chain management
  • Customer service
  • Global trade practices

Business Analyst (BA)

Role:

• In business, BA is a professional who analyzes the business needs and recommends solutions
• Acts as an intermediate between business company and in-house project's technical team (including SA, Programmer, DBA, DB Analyst and Designer, etc.) to define problems and recommend solutions

What Business Data Analytics Involves

Business Data Analytics involves:

• ✓ Working with and manipulating business data
• ✓ Extracting insights from data to information
• ✓ Using that information to enhance business performance

Example of Data Pipelines

The typical data pipeline consists of:

1. Data extraction (from database)
2. Data transformation (processing)
3. Data loading (to storage)
4. Data analysis (examination)
5. Data visualisation (presentation)

Think of this like an assembly line in a factory: raw materials (data) come in, get processed and refined (transformation), stored in warehouse (loading), quality checked (analysis), and finally packaged for customers (visualization).

Database TRENDS (2025 and Beyond)

From Data to Data Science

Data and the capability to extract useful knowledge from data are determined to be the key factors for data science.

Knowledge Discovery in Database (KDD) Pyramid:

$\text{Raw Fact} \rightarrow \boxed{\text{DATA}} \rightarrow \boxed{\text{INFORMATION}} \rightarrow \boxed{\text{KNOWLEDGE}} \rightarrow \boxed{\text{WISDOM}}$

The KDD process creates a feedback loop from WISDOM back to DATA.

Introduction to Data Science

Definition

Data science is an interdisciplinary field that uses scientific methods, processes, algorithms and systems to extract knowledge and insights from noisy, structured and unstructured data, and apply knowledge and actionable insights from data across a broad range of application domains (Provost and Fawcett, 2013).

Process-Oriented Definition

Data science involves principles, processes, and techniques for understanding phenomena via the automated analysis of data (Provost and Fawcett, 2013).

Data Science in Context

The relationship between different data processes:

Data-Driven Decision Making (across the firm) ↑ Automated DDD ↑ Data Science ↑ Data Engineering and Processing (including "Big Data" technologies) ↓ Other positive effects of data processing (e.g., faster transaction processing)

Reference: F. Provost, and T. Fawcett. Data Science for Business: What You Need to Know about Data Mining and Data. O'Reilly Media, 2013.

Big Data

Definition

• Big data means datasets that are too large for traditional data processing systems, and therefore require new processing technologies (Provost and Fawcett, 2013)

• Big Data consists of different types of key technologies like:

  • Hadoop
  • HDFS
  • NoSQL
  • MapReduce
  • MongoDB
  • Cassandra
  • PIG
  • HIVE
  • HBASE

  These work together to achieve the end goal like extracting value from data that would be previously considered impossible (Zakir, Seymour, and Berg, 2015)

Big Data Architecture

The typical Big Data workflow:

Data Sources:

• ERP systems
• Social media
• Web data
• IoT devices

Data Transformation:

• Enterprise Data Model
• Common Meta-Data

Storage:

• Data Warehouse
• Departmental Data Mart

Output:

• BI Reports
• Dashboard

Reference: http://www.ibmbigdatahub.com/blog/changing-face-business-intelligence

What is Data Analytics?

Definition (Wikipedia)

Analytics is the discovery and communication of meaningful patterns in data

Especially valuable in areas rich with recorded information, analytics relies on the simultaneous application of statistics, computer programming, and operations research to quantify performance

Data Analytics Process

The KDD (Knowledge Discovery in Databases) Process:

1. Data (from database)
2. Selection → Target Data
3. Preprocessing → Preprocessed Data
4. Transformation → Transformed Data
5. Data Mining → Patterns
6. Interpretation/Evaluation → Knowledge

The process includes a feedback loop from Knowledge back to Data.

Reference: Based on content in "From Data Mining to Knowledge Discovery", AI Magazine, Vol 17, No. 3 (1996)
http://www.aaai.org/ojs/index.php/aimagazine/article/view/1230

Analyzing Big Data

• Data analytics is concerned with extraction of actionable knowledge and insights from big data

• This is done by hypothesis formulation that is often based on conjectures gathered from experience and discovering correlations among variables

Reference: V. Rajaraman (2016), Big Data Analytics, General Article, Resonance, August 2016, pp.695-716.

Level of Analytics

From lowest to highest complexity and value:

1. Descriptive Analytics

Question: What happened?

• Lowest complexity, foundational level

2. Diagnostic Analytics

Question: Why did that happen?

• Medium-low complexity

3. Predictive Analytics

Question: What will happen?

• Medium-high complexity

4. Prescriptive Analytics

Question: "Best" course of action?

• Highest complexity and value

Each level builds upon the previous, increasing in both computational complexity (represented by brain icon) and business value (represented by computer icon).

Reference: Provost and Fawcett, "Data Science for Business"

Data Analytics Challenges

The Value Ladder

Moving from bottom to top requires increasing value:

Big Data → Processing → Reporting → Analytics

Big Data Level

• Containers and Feeds of Heterogeneous Data
• Output: Access to Structured and Unstructured Data

Processing Level

• Data Prepared for Analysis
• Output: Indexed, Organized and Optimized Data

Reporting Level

• Identification of Patterns and Relationships
• Output: An Evaluation Of What Happened in the Past

Analytics Level (Split into two)

Predictive Analytics:

• Sets Of Potential Future Scenarios

Prescriptive Analytics:

• Automatically Prescribe and Take Action

Reference: https://bcourse.berkeley.edu/

Think of this like learning to drive: Big Data is having access to a car, Processing is understanding the controls, Reporting is reviewing your past trips, Predictive Analytics is planning future routes, and Prescriptive Analytics is having GPS that automatically suggests the best route and adjusts in real-time.

Finally, Best of Luck!

Image: Luck vs. Hard Work illustration

Remember: Success in database management comes from both understanding the concepts and putting in consistent effort!

End of Lecture 15