Information Technology & Data Analytics
jenny2000000
03-Lesson_03.pdf
Lesson 3: Databases and Data Warehouses
Information Technology & Data Analytics
October 4, 2021
Information Technology & Data Analytics
Chapter 3
Databases and Data Warehouses: Supporting the Analytics-Driven Organization
7
Information Technology & Data Analytics
1. List and describe the key characteristics of a relational database.
2. Define the 5 software components of a DBMS.
3. List and describe the key characteristics of a data warehouse.
4. Define the 5 major types of data-mining tools.
5. List key considerations in information ownership.
STUDENT LEARNING OUTCOMES
8
Information Technology & Data Analytics
Database and Data Warehouses: Introduction
11
Information Technology & Data Analytics
➢ Business intelligence (BI)
• collective information that gives you the ability to make effective, important, and strategic business decisions
➢ Analytics
• the science of fact-based decision making
➢ Both are huge in today’s business world
INTRODUCTION
12
Information Technology & Data Analytics
➢ Businesses use many IT tools to manage and organize information
➢ Online transaction processing (OLTP)
• Gathering and processing information and updating existing information to reflect the processed information
➢ Online analytical processing (OLAP)
• Manipulation of information to support decision making
INTRODUCTION
13
Information Technology & Data Analytics
➢ OLTP
• Supports operational processing
• Sales orders, accounts receivable, etc.
• Supported by operational databases & DBMSs
➢ OLAP
• Helps build business intelligence
• Supported by data warehouses and data-mining tools
INTRODUCTION
14
Information Technology & Data Analytics
OLTP, OLAP, and BI
15
Information Technology & Data Analytics
Relational Database Model
17
Information Technology & Data Analytics
➢ Database – collection of information that you organize and access according to the logical structure of the information
➢ Relational database – series of logically related two-dimensional tables or files for storing information
• Relation = table = file
• Most popular database model
RELATIONAL DATABASE MODEL
18
Information Technology & Data Analytics
➢ Collections of information
➢ Created with logical structures
➢ Include logical ties within the information
➢ Include built-in integrity constraints
Database Characteristics
19
Information Technology & Data Analytics
Database – Collection of Information
20
Information Technology & Data Analytics
➢ Data dictionary – contains the logical structure for the information in a database
Database – Created with Logical Structures
21
Before you can enter information into a database,
you must define the data dictionary for all the tables and their fields. For example, when you create the Truck table, you must specify that it will have
three pieces of information and that Date of Purchase is a field
in Date format.
Information Technology & Data Analytics
Customer Number is the primary key for Customer and appears in Order as a foreign
key
➢ Primary key – field (or group of fields) that uniquely describes each record
➢ Foreign key – primary key of one file that appears in another file
Database – Logical Ties within the Information
22
Information Technology & Data Analytics
Database – Logical Ties within the Information
23
Information Technology & Data Analytics
➢ Integrity constraints – rules that help ensure the quality of information
➢ Data dictionary, definitions of data elements, what they mean and the values they can have
• Attribute Name, Optional/Required, Attribute type
• Oracle: read-only set of tables that provides information about the database (allocated space, values, integrity constraints, user names, privileges, roles, auditing)
➢ Foreign keys – must be found as primary keys in another file
• E.G., a Customer Number in the Order Table must also be present in the Customer Table
Databases – Built-In Integrity Constraints
24
Information Technology & Data Analytics
Database Management System (DBMS)
25
Information Technology & Data Analytics
➢ Database management system (DBMS) – helps you specify the logical organization for a database and access and use the information within it
DATABASE MANAGEMENT SYSTEM TOOLS
26
Information Technology & Data Analytics
1. DBMS engine
2. Data definition subsystem
3. Data manipulation subsystem
4. Application generation subsystem
5. Data administration subsystem
5 Components of a DBMS
27
Information Technology & Data Analytics
➢ DBMS engine – accepts logical requests and converts them into the physical equivalents, and access the database and data dictionary on a storage device
• Physical view – how information is physically arranged, stored, and accessed on a storage device
• Logical view – how you need to arrange and access information to meet your needs
DBMS Engine
28
Information Technology & Data Analytics
➢ Data definition subsystem – helps you create and maintain the data dictionary and structure of the files in a database
➢ The data dictionary helps you define: • Field names • Data types (numeric, etc.) • Form (do you need an area code) • Default value • Is an entry required, etc.
➢ Also responsible for modifying, adding and deleting the DB structure
➢ Remember that some vendors also include other information, such as size, users and constraints
Data Definition Subsystem
29
Information Technology & Data Analytics
➢ Data manipulation subsystem – helps you add, change, and delete information in a database and query it to find valuable information
➢ Most often your primary interface
➢ Includes views, report generators, query-by-example tools, and structured query language
Data Manipulation Subsystem
30
Information Technology & Data Analytics
➢ View – allows you to see the contents of a database file, make changes, and query it to find information
Data Manipulation Subsystem - View
31
Information Technology & Data Analytics
➢ Report generator – helps you quickly define formats of reports and what information you want to see in a report
Data Manipulation Subsystem: Report Generator
32
Information Technology & Data Analytics
➢ QBE tool – helps you graphically design the answer to a question
Data Manipulation Subsystem: Query-by-Example Tool
33
Information Technology & Data Analytics
➢ SQL – standardized fourth-generation query language found in most DBMSs used to manage relational databases and to perform operations with the data it contains • Developed in 1974 by IBM • First commercial implementation in 1979 by Relational Software Inc.
(Oracle)
➢ Sentence-structure equivalent to QBE
➢ Mostly used by IT professionals
➢ Allows to perform not only queries, but also create tables, handle, data, manage users, and perform various operations
Data Manipulation Subsystem: Structured Query Language
34
Information Technology & Data Analytics
➢ Examples:
• SELECT first_name, last_name, hire_date FROM employee
Data Manipulation Subsystem: Structured Query Language
35
Information Technology & Data Analytics
➢ Examples:
Data Manipulation Subsystem: Structured Query Language
36
Information Technology & Data Analytics
➢ Application generation subsystem – contains facilities to help you develop transaction-intensive applications
➢ Mainly used by IT professionals
Application Generation Subsystem
37
Information Technology & Data Analytics
➢ Data administration subsystem – helps you manage the overall database environment by providing facilities for:
• Backup and recovery
• Security management
• Query optimization
• Reorganization
• Concurrency control
• Change management
➢ Usually handled by the Database Administrator (DBA)
Data Administration Subsystem
38
Information Technology & Data Analytics
➢ Backup and recovery – for backing up information and restarting (recovering) from a failure • It means protecting your data
• Backup – copy of information in storage ▪ Storage can be: hard drives, solid state drives, tapes, DVDs,
storage appliances, cloud
• Recovery – process of reinstalling the backup information in the even the information was lost
• Some other related strategies: ▪ Replication – mostly used for Disaster Recovery Planning (DRP) ▪ Snapshot
Data Administration Subsystem
39
Information Technology & Data Analytics
➢ Security management
• Who has access to what? To do what?
• CRUD – Create, Read, Update, Delete
➢ Query optimization
• To minimize response times for large, complex queries
• Efficiency
➢ Reorganization
• For physically rearranging the structure of the information according to how you most often access it
• Reorganize files, indexes, etc.
Data Administration Subsystem
41
Information Technology & Data Analytics
➢ Concurrency control
• What happens if two people attempt to make changes to the same record?
• Maintain validity while there are multiple changes and access
➢ Change management
• Assess the impact of structural changes in the overall database
• How many files, tables, indexes, structures would be affected?
Data Administration Subsystem
42
Information Technology & Data Analytics
➢ Object Oriented Databases • A very funny video illustrates them • Objects have properties and methods • Encapsulation, Abstraction, Inheritance, Polymorphism
➢ Document Stores
➢ Graph Databases - Data represented as related nodes • Neo4j has an instructional video series that explains it in detail
➢ Object Oriented Relational Databases • Objects can also be in a relationship!
Other Database Models: Non- relational
43
Information Technology & Data Analytics
Database Warehouse and Data Mining
44
Information Technology & Data Analytics
➢ Help you build and work with BI and some forms of knowledge
➢ Data warehouse – collection of information (from different sources) that supports business analysis activities and decision making
DATA WAREHOUSES AND DATA MINING
45
Information Technology & Data Analytics
➢ Multidimensional • Rows, columns, and layers • Hypercube
➢ Support decision making (OLAP), not transaction processing (OLTP) • Contain summaries of information • Not every detail can be gathered
➢ Data is • Stored • Cleansed • Transformed • Catalogued
Data Warehouse Characteristics
46
Information Technology & Data Analytics
➢ Data is
• Stored
• Cleansed
• Transformed
• Catalogued
➢ Most use ETL (Extract, Transform, Load)
• Staging
• ODS (operational data store)
• Data warehouse DB (dimensions)
• Star schema (facts)
Data Warehouse Characteristics
47
Information Technology & Data Analytics
➢ Data-mining tools – software tools you use in a data warehouse environment
• Query-and-reporting tools
• Artificial intelligence
• Multidimensional analysis tools
• Digital dashboards
• Statistical tools
The Tool Set of the Analytics Professional
48
Information Technology & Data Analytics
The Tool Set of the Analytics Professional
49
Information Technology & Data Analytics
➢ Query-and-reporting tools
• similar to QBE tools, SQL, and report generators
➢ Artificial intelligence
• tools to help you “discover” information and trends
➢ Multidimensional analysis (MDA tools)
• slice-and-dice techniques for viewing multidimensional information
The Tool Set of the Analytics Professional
50
Information Technology & Data Analytics
➢ Digital dashboard • displays key information on a computer screen tailored to the
needs and wants of an individual • Key performance indicator (KPI) – most essential information
used in any analytics initiative
➢ Statistical tools • For applying mathematical models to data warehouse
information • Trending
The Tool Set of the Analytics Professional
51
Information Technology & Data Analytics
Digital Dashboard
52
Information Technology & Data Analytics
The Analytics Life Cycle
53
Information Technology & Data Analytics
➢ ETL is a three-step process
1. Extract needed information from its source
2. Transform the data into a standardized format
3. Load the transformed data into a data warehouse
➢ Data-mining tools applied after this process is completed
Extraction, Transformation, and Loading (ETL)
54
Information Technology & Data Analytics
➢ Data mart – subset of a data warehouse in which only a focused portion of the data warehouse information is kept
Data Marts
55
Information Technology & Data Analytics
➢ Do you really need one, or does your database environment support all your functions?
➢ Do all employees need a big data warehouse or a smaller data mart?
➢ How up-to-date must the information be?
➢ What data-mining tools do you need?
Data Warehouse Considerations
56
Information Technology & Data Analytics
Information Ownership
57
Information Technology & Data Analytics
➢ Information is a resource you must manage and organize to help the organization meet its goals and objectives
➢ You need to consider
• Strategic management support
• Sharing information with responsibility
• Information cleanliness
INFORMATION OWNERSHIP
58
Information Technology & Data Analytics
➢ CIO – Chief Information Officer • Every aspect of an organization’s information resource
➢ CTO – Chief Technology Officer • The underlying IT infrastructure and user-facing technologies
➢ CSO – Chief Security Officer • Technical aspects for security of information
➢ CPO – Chief Privacy Officer • information is used in an ethical way
Strategic Management Support
59
Information Technology & Data Analytics
➢ Two other important functions in information management
• Data administration
– function that plans for, oversees the development of, and monitors the information resource
• Database administration
– function responsible for the more technical and operational aspects of managing organizational information
Strategic Management Support
60
Information Technology & Data Analytics
➢ Everyone can share – while not consuming – information
➢ But someone must “own” it by:
• Providing only the required information to the right people
• Accepting responsibility for its quality and accuracy
Sharing Information
61
Information Technology & Data Analytics
➢ Related to ownership and responsibility for quality and accuracy
➢ No duplicate information
➢ No redundant records with slightly different data, such as the spelling of a customer name
➢ GIGO – if you have garbage information you get garbage information for decision making
Information Cleanliness
62
Information Technology & Data Analytics
1. List and describe the key characteristics of a relational database.
2. Define the 5 software components of a DBMS.
3. List and describe the key characteristics of a data warehouse.
4. Define the 5 major types of data-mining tools.
5. List key considerations in information ownership.
STUDENT LEARNING OUTCOMES
63
Questions?
Thank you!
