Unit 3: Managing Data Resource

What is Data Management?

Data management refers to the process of collecting, storing, organizing, protecting, and maintaining data so that it is accurate, available, and secure for business use. Data is a valuable resource, like money or equipment – it needs proper management to support decision-making and operations.

Need for Data Management

Challenges of Data Management

Data Independence

Data independence means the ability to change the data structure (like table format) without changing the application programs that use the data.

For example: You change a field name in the database, but your reporting software still works fine.

Data Redundancy

Data redundancy means storing the same piece of data in multiple places. Example: A customer's address is saved in both the billing system and shipping system.

Data Consistency

Data consistency means that all versions of the same data match across systems and databases. Example: If a customer updates their email, it should change in all departments' records.

Data Administration

Data administration is the role of managing and controlling data policies, procedures, and standards in an organization.

Summary Table

What is a Database Management System (DBMS)?

A Database Management System (DBMS) is software that helps in storing, organizing, managing, and retrieving data in a structured way. Example: MySQL, Oracle, MS Access, PostgreSQL

🔹 Key Functions of DBMS:

• Data storage and retrieval
• Data security and access control
• Data backup and recovery
• Elimination of data redundancy
• Ensuring data consistency

Types of DBMS

Relational DBMS is the most commonly used type in business.

Basic Components of a Database

A. Fields (Columns): A field represents a single piece of data or attribute in a table. Example: Name, Email, Age.

B. Records (Rows):A record is a complete set of related data in a table – a combination of fields. Example: Row 1: [John, john@gmail.com, 25]

C. Table: A table is a collection of records and fields arranged in rows and columns.

Tables store all the data in a DBMS.

D. View: A view is a virtual table based on one or more actual tables.

• It shows only selected data and does not store data physically.
• Useful for security and customization – only required data is shown.

Example: A manager sees only employee name and salary, not address or ID.

E. Reports: Reports are formatted outputs of data used for decision-making. 

• They present summarized and structured information (e.g., monthly sales report). 
• Often include charts, graphs, and summaries. 
• Reports help in analyzing trends, performance, and KPIs.

F.  Queries: A query is a question or request to extract specific data from a database.

• Written using a query language like SQL (Structured Query Language).

Example: SELECT Name, Age FROM Employees WHERE Age > 25;

• Queries help in finding specific data quickly from a large database.

Summary Table

Data Warehouse

A Data Warehouse is a central storage system where data from different sources (departments, databases, etc.) is collected, cleaned, and stored for reporting and analysis. Think of it as a digital storage house where a company’s historical and current data is kept in an organized way to help in decision-making.

Characteristics of a Data Warehouse

Uses of Data Warehouse

 Data Mining

Data Mining is the process of extracting useful patterns, trends, and relationships from large sets of data using algorithms and statistical methods.

🧠 It's like "digging" through data to find hidden knowledge for better decisions.

🔍 Techniques of Data Mining

Business Intelligence (BI)

Business Intelligence (BI) refers to technologies and tools that turn raw data into meaningful insights for better business decisions.

 BI = Data + Analytics + Tools for smarter business decisions

Components of Business Intelligence

Uses of Business Intelligence

Summary Table

Creation of Table

A table is the core structure in a database where data is stored in rows and columns.

Syntax to Create a Table in SQL:

CREATE TABLE Employees (

    EmpID INT PRIMARY KEY,

    Name VARCHAR(50),

    Department VARCHAR(30),

    Salary DECIMAL(10, 2)

);

Explanation:

• EmpID: Integer data type and primary key (unique identifier).
• Name: Character string of up to 50 characters.
• Department: Department name.
• Salary: Decimal value with 10 digits (2 after decimal).

After creation, data can be inserted using INSERT command.

Creating a View

A view is a virtual table that is based on a SQL query. It does not store data physically but displays data from one or more tables.

🔹 Syntax to Create a View:

CREATE VIEW HR_View AS

SELECT Name, Department

FROM Employees

WHERE Department = 'HR';

Uses of Views

• Display only necessary data.
• Improve security (restrict access).
• Simplify complex queries.

Generating Reports (Conceptual)

In DBMS lab environments like MS Access, Oracle Reports, or MySQL with tools like PHPMyAdmin, reports are used to format and present data.

🔹 Common Report Features

• Tabular format
• Charts/Graphs
• Totals and summaries
• Filters (e.g., by date, department)

📊 In real projects, reports are often created using tools like Crystal Reports, Power BI, or Tableau, which connect to your database.

Basics of SQL (Structured Query Language)

SQL is the language used to communicate with a database. Below are basic SQL operations:

A. Insert Data

INSERT INTO Employees (EmpID, Name, Department, Salary)

VALUES (1, 'John', 'HR', 50000.00);

B. Update Data

UPDATE Employees

SET Salary = 55000.00

WHERE EmpID = 1;

C. Delete Data

DELETE FROM Employees

WHERE EmpID = 1;

Running Queries

A. Select Query – to view data

SELECT * FROM Employees;

B. Conditional Query

SELECT Name, Salary

FROM Employees

WHERE Department = 'Finance' AND Salary > 60000;

C. Order By

SELECT * FROM Employees

ORDER BY Salary DESC;

D. Group By with Aggregate Functions

SELECT Department, AVG(Salary) AS AvgSalary

FROM Employees

GROUP BY Department;

Summary Table