Chapter 6 Database Concepts and Management Systems

Information Systems: Theory & Practice

• This is a course on foundations of Business Intelligence, Databases, and Information Management.
• A professor named Dr. Paul Drews is teaching the course.

Learning Objectives

• Understand the problems of managing data resources in a traditional file environment.
• Learn the capabilities of database management systems (DBMS), focusing on relational DBMS.
• Identify the tools and technologies for accessing information from databases to enhance business performance and decision-making.
• Examine the importance of information policy, data administration, and data quality assurance for firm data resources.

Astro Case

• A business case study is introduced.
• Development of IT plan and building analytics are part of the IT plan
• Implementation of systems requires training employees.
• Using AWS Data Lake, AWS Storage Service and Elastic Compute Cloud are relevant technologies.
• The case explores business challenges like growing competition, need for new services, legacy infrastructure, etc. and the solutions including real-time customer analysis, content curation and multi-channel advertising.
• The case study highlights a structure for effective management between;
  • Management
  • Organization
  • Technology
  • Information system
  • Business solutions

Agenda

• The course will cover topics like managing data in traditional file environments.
• The use of database management systems will be discussed, including tools for improving business performance.
• Managing organizational data resources will also be covered, in addition to relevant tools.

Managing Data in a Traditional File Environment

• File Organization Concepts:
  • Database: a collection of related files.
  • File: a collection of records of the same type.
  • Record: a collection of related fields.
  • Field: a collection of characters (word/s or number/s).
  • Entity: a person, place, or thing.
  • Attribute: characteristic or quality of an entity.
  • Example: Attributes DATE and GRADE belong to the entity COURSE.

The Data Hierarchy

• Data organization in computers starts from the bit (0 or 1).
• Bits group to form bytes representing characters, numbers, or symbols.
• Related fields form records; collections of records create files.
• Related files are organized into databases.
• Bit, Byte, Field, Record, File, Database.

Managing Data in a Traditional File Environment (problems)

• Data Redundancy: Duplicate data in multiple files.
• Data Inconsistency: Same attribute having different values in different files.
• Program-Data Dependence: Changes in a program requiring changes to data accessed by the program.
• Lack of Flexibility: Difficulty adapting to changing needs.
• Poor Security: Lack of control over data access.
• Lack of Data Sharing and Availability: Difficulty in sharing data across departments effectively.

Traditional File Processing

• Data is maintained separately by departments.
• Requires separate application programs based on data kept in separate files for each department.
• This approach creates many separate and isolated data files.

Capabilities of Database Management Systems (DBMS)

• Serving multiple applications by centralizing and controlling redundant data.
• Interfacing between applications and physical data files.
• Offering separate logical and physical views of data.
• Solving the problems inherent in traditional file environments by controlling redundancy and eliminating inconsistency.
• Separating programs from data, thus enabling organizations to centrally manage data and security,

Human Resources Database with Multiple Views

• A database can provide multiple views of data to different users depending on their specific needs.
• The example demonstrates alternative views available based on departmental needs.
• Different departments within an organization may require varying access to the same database.

Capabilities of Database Management Systems – Relational DBMS

• Data is stored in two-dimensional tables with rows and columns.
• Each table is dedicated to a specific entity (e.g., customer)
• Rows represent records, columns represent attributes.
• Key fields uniquely identify each record.
• Primary key in one table can act as a foreign key in another, creating relationships.

Relational Database Tables

• Databases are organized as two-dimensional tables.
• Tables contain data for entities, with columns representing attributes and rows representing individual records.
• Primary keys identify records uniquely within a table.
• Foreign keys create relationships between tables.

Capabilities of Database Management Systems – Relational DBMS Operations

• SELECT: Selecting records based on criteria.
• JOIN: Combining data from multiple tables.
• PROJECT: Selecting specific columns of data from one or more tables.

The Three Basic Operations of a Relational DBMS

• Demonstrating use of SELECT, JOIN, and PROJECT operations utilizing tables
• Showing how these tools enable integration and selection from different database tables.

Capabilities of Database Management Systems

• Data Definition Capability: Defining database structure and field characteristics.
• Data Dictionary: Storing definitions of data elements.
• Querying and Reporting: Manipulating and retrieving data, using tools such as SQL and report generation capabilities.

Microsoft Access Data Dictionary Features

• Microsoft Access offers basic data dictionary capabilities.
• Features allow for viewing of data characteristics such as field size and format

Example of an SQL Query

• Demonstrate a standard query language.
• Select records from multiple lists (using columns from different tables) for particular items matching specific criteria/conditions

An Access Query

• Illustrating the query building tools in Microsoft Access to execute the SQL query.
• Demonstrate how tables, fields, and selection criteria are used to construct queries.

Capabilities of Database Management Systems – Designing Databases

• Designing databases involves conceptual design (abstract model), physical design (how the database is organized), and normalization (reducing redundancy).

Capabilities of Database Management Systems – Normalization

Focuses on the streamlining of data through complex process and grouping of data, resulting in minimized redundancies and awkward many-to-many relationships.

Capabilities of Database Management Systems – Referential Integrity Rules and Entity-Relationship Diagrams

• Referential integrity rules ensure consistency in relational database relationships.
• Entity-relationship diagrams illustrate relationships between data entities. A correct diagram is crucial for a successful database.

An Unnormalized Relation for Order

• Illustrates tables before normalization: showcasing repeating groups or data elements.
• Indicates a high degree of repetition which can result in increased redundancy, inconsistencies, and inefficiency.

Normalized Tables Created from Order

• Illustrate tables after normalization to show how repeating groups have been broken up into smaller relations.
• Relate the normalized relational tables to the entity-relationship diagram in Figure 6-11.

An Entity-Relationship Diagram

• Shows relationships among database entities: supplier, part, line item, and order.

Your Task

• Design a T-shirt webshop database.
• Identify relevant entities.
• Define relevant attributes for the entities.
• Design database tables and columns.

Capabilities of Database Management Systems – Non-relational databases

• Describe the "NoSQL" model, which is more flexible.
• Data across distributed machines.
• Handling large volumes of data.

Capabilities of Database Management Systems – Databases in the Cloud

• Cloud databases (like Amazon Relational Database Service, Microsoft SQL Azure, Private clouds) appeal to start-ups, smaller businesses.

Agenda

• Cover topics related to managing data in traditional file environments, database management systems, and data tools and techniques, to improve performance and decision-making.

Tools for Improving Business Performance and Decision Making – Blockchain

• Discusses distributed ledgers to maintain a growing list of records and transactions.
• Blockchain is used for financial transactions, supply chains, and medical records, also a foundation for cryptocurrencies.

How Blockchain Works

• Outlines a process for tracking and verifying information through a P2P network.
• Describes verification, transactions, and order fulfillment using this technology.

Tools for Improving Business Performance and Decision Making – Big Data

• Overviewing big data.
• Massive sets of unstructured and semi-structured data.
• Petabytes, exabytes of data exceeding capabilities of traditional databases.

Big Data - Key Characteristics

• Value (Clinically relevant data), Volume (Data in Petabytes/Exabytes exceeding database capacity, Velocity, Variety, Veracity,and Variability are key characteristics and examples

Tools for Improving Business Performance and Decision Making – Contemporary Tools

• Covers tools such as data warehouses, data marts, Hadoop, in-memory computing, and analytic platforms used in contemporary business intelligence.

Tools for Improving Business Performance and Decision Making – Data Warehouse

• Storing of current and historical data from operational transaction systems.
• Standardization and centralization of data for enterprise-wide use.

Tools for Improving Business Performance and Decision Making – Data Marts

• Describing them as subsets of data warehouses used by specific users.
• Focus on particular subjects or business lines.

Contemporary Business Intelligence Infrastructure

• Shows how different data sources are managed and analyzed using ETL, Hadoop, data warehouses, and data marts, within a data-centric approach using a data platform and analytics.

Tools for Improving Business Performance and Decision Making – Hadoop

• Hadoop enables distributed processing of large data sets on inexpensive computers.
• Key components include HDFS (file system) and MapReduce (data processing).

Tools for Improving Business Performance and Decision Making – In-Memory Computing

• Using computer RAM for storage to avoid delays in retrieving data.
• Speeding up processing from hours to seconds.
• Optimized hardware is needed.

Tools for Improving Business Performance and Decision Making – Analytical Tools

• Using tools to help users with consolidating, analyzing, and accessing data.
• These methods help users to improve their business decisions based on data

Tools for Improving Business Performance and Decision Making – Online Analytical Processing – OLAP

• Enabling rapid, online answers to ad hoc queries by utilizing multidimensional data analysis.

Multidimensional Data Model

• Showing how data is visualized and analyzed in multidimensional format using a cube-like model.

Tools for Improving Business Performance and Decision Making – Data Mining

• Discovering hidden patterns and relationships within data sets.
• Inferring rules to predict future behavior (e.g., customer patterns).

Tools for Improving Business Performance and Decision Making – Text Mining

• Extracts key elements from large unstructured data sets
• Examples: e-mails, call center transcripts, legal cases, patent descriptions
• Includes sentiment analysis to detect opinions

Tools for Improving Business Performance and Decision Making – Web Mining

• Discovering useful patterns from web data.
• Understanding customer behavior and evaluating website effectiveness.
• Web content, structure, and usage mining are included.

Tools for Improving Business Performance and Decision Making – Databases and the Web

• Various companies utilize the web to expose internal databases to customers or partners through web interface configurations such as a web server.

Linking Internal Databases to the Web

• Diagram illustrating the process of connecting internal databases to the web using typical components (client-web browser and database servers).

Managing the Firm's Data Resources

• Establishing an information policy encompasses firm rules, procedures, and roles for efficient data sharing, management, and standardization.
• Data governance policies and procedures regulate data availability, usability, integrity, and security.
• Database administration ensures the creation and maintenance of databases.

Managing the Firm's Data Resources – Data Quality

• Addresses the issue of data accuracy and completeness.
• More than 25% of data in Fortune 1000 company databases might be faulty or incomplete.
• Strategies identified to identify and resolve data inconsistencies.
• Structured data audits are shown.

Managing the Firm's Data Resources – Ensuring Data Quality

• Establishing practices for data quality checks to enhance accuracy, completeness, and consistency.
• Describing methods for identifying and correcting faulty data.

Tasks for This Week

• Include assignments for reading chapter 6, viewing videos, etc

Contact

• Providing contact information for the professor.

Test your knowledge on fundamental database concepts and the role of database management systems. This quiz covers various aspects, including hierarchy components, entity-relationship diagrams, and the differences between relational and non-relational databases. Perfect for students studying database management in academic courses.