Database Systems and Data Management

Objectives

• Distinguish data from information and knowledge.
• Identify six benefits gained through the use of high-quality data.
• Define the components of the data hierarchy, including attribute, entity, record, file, and database.
• Define the term database management system.
• Identify six functions performed by a database management system.
• Define the roles of the database schema, data definition language, and data manipulation language.
• Define the term data cleansing.
• Identify seven key questions that must be answered when designing a database.
• Identify six fundamental characteristics of the relational database model.
• State the purpose of data normalization.
• Identify two key benefits of enforcing the ACID properties on SQL databases.
• Identify two advantages associated with Database as a Service (DaaS).
• Distinguish between data management and data governance.
• Identify three factors driving the need for data management.
• Identify four key responsibilities of the data governance team.
• Define the role of a database administrator.

Why Learn about Database Systems and Data Management?

• Databases capture data about changes for:
  • Analysis and decision making.
  • Recognizing new challenges and opportunities.
  • Tracking progress toward meeting key goals.
  • Identifying when a change in tactics or strategy is needed.
• It's crucial to understand database systems and data management.

Database Fundamentals

• Database: A well-designed, organized, and carefully managed collection of data.
  • Should help an organization achieve its goals.
  • Can contribute to organizational success.
  • Provides managers and decision-makers with timely, accurate, and relevant information built on data.
  • Most organizations have multiple databases.

Data, Information, and Knowledge

• Data: Raw facts.
• Information: A collection of organized and processed data.
  • Has additional value beyond the value of the individual facts.
• Knowledge: Provides awareness and understanding of a set of information.
  • Shows how information can support a specific task or be used to reach a decision.
• Types of data:
  • Alphanumeric data: Numbers, letters, and other characters.
  • Audio data: Sounds, noises, or tones.
  • Image data: Graphic images and pictures.
  • Video data: Moving images or pictures.

The Value of Information

• Value of Information: Directly linked to how it helps decision-makers achieve their organization’s goals.
• Helps people perform tasks efficiently and effectively.

Benefits Gained Through Use of High-Quality Data

• Improves Decision Making:
  • Using high-quality data in decision-making removes guesswork and risk-taking.
• Increases Customer Satisfaction:
  • Leads to high customer satisfaction.
  • Bad data causes unfavorable data errors.
• Increases Sales:
  • Provides accurate consumer targeting and communications.
  • Enables successful up-sell and cross-sell suggestions.
• Improves Innovation:
  • Improves worker efficiency, product and/or service quality, or the customer experience.
• Raises Productivity:
  • Employees can focus on the core mission.
  • No need to correct data errors.
• Ensures Compliance:
  • Ensures compliance with requirements.

Characteristics of Quality Information

• Accessible: Information should be easily accessible by authorized users in the right format and at the right time.
• Accurate: Error-free; inaccurate data can lead to inaccurate information (garbage in, garbage out).
• Complete: Contains all important facts.
• Economical: Relatively economical to produce; balance the value of information with the cost of producing it.
• Relevant: Important to the decision-maker.
• Reliable: Can be trusted by users; depends on the reliability of the data-collection method and source.
• Timely: Delivered when it is needed.
• Verifiable: Can be checked to ensure correctness, possibly by checking multiple sources.

The Data Hierarchy

• Entity: Person, place, or thing (object) for which data is collected, stored, and maintained.
• File: Collection of entities.
• Attribute: Characteristic of an entity.
• Domain: Range of allowable values for a data attribute.
• Data item: Specific value of a data attribute.
• Record: Collection of attributes about a specific entity.
• Primary key: Attribute or set of attributes that uniquely identifies the record.
• Foreign key: Attribute in one table that refers to the primary key in another table.

The Database Approach

• Database approach to data management: Multiple information systems share a pool of related data.
• Database management system (DBMS): Group of programs provided by the DBMS supplier.
  • Programs used to access and manage a database.
  • Provides an interface between the database and its users and other application programs.

Database Activities

• Providing a user view:
  • Schema: Description defining the database’s logical and physical structure.
  • Identifies the tables and the attributes in each table.
  • Identifies the relationships between attributes and tables.
  • DBMS can reference a schema to access requested data in relation to another piece of data.
• Creating and modifying the database:
  • Data definition language (DDL): Collection of instructions and commands.
  • Defines and describes data and relationships in a specific database.
  • Data dictionary: Detailed description of data stored in the database.
  • Adherence to data dictionary standards makes it easy to share data among organizations.
• Storing and retrieving data:
  • DBMS function: Interface between application program and database to obtain data.
  • Request it through the DBMS.
  • Concurrency control: Addresses situations where two or more users or applications access the same record at the same time.
• Manipulating data and generating reports:
  • Database provides reports and important information.
  • Company uses DBMS to manage database.
  • Databases can use Query by Example (QBE): Visual approach to developing database queries or requests.
  • Data manipulation language (DML): A specific language provided with a DBMS.
  • Allows users to access and modify the data, make queries, and generate reports.
• Security management:
  • Ensures data is protected against access by unauthorized users, physical damage, operating system failure, and simultaneous updating.
  • Defines and enforces user access privileges.
  • Grants users minimum privileges to do their jobs.
• Backup and recovery:
  • Backup management: Make backup copies of the database.
  • Backup copies can be used to restore the database.

Data Cleansing

• Data cleansing: Detects and then corrects or deletes incomplete, incorrect, inaccurate, or irrelevant records residing in a database.
  • Improves the quality of the data used in decision-making.
  • Different from data validation.
• Methods:
  • Cross-checking data against a validated data set.
  • Using data enhancement to augment the data in a database by adding related information.

Database Design

• Necessary to keep data well organized.
• Database design: store all relevant data, provide quick access and easy modification, and reflect organization’s business processes.
• Considerations: content and access, logical structure and physical organization, response time, archiving, security.
• Data modeling: Tool used to design a database.
  • Occurs at organizational level, called enterprise data modeling.
  • Occurs at specific business application level.
• Enterprise data model: Identifies data entities and data attributes of greatest interest to the organization.
  • Identifies their associated standard data definitions, data length and format, domain of valid values, and any business rules for their use.
• Entity-relationship (ER) diagram: Data model used to analyze and communicate data needs.
  • Works at the individual project or application level.
  • Uses graphical symbols to identify data entities, their attributes, and the relationships among the entities of interest.
  • Many notation styles exist for drawing an ER diagram.

Relational Databases

• Relational database: Data is placed in two-dimensional tables, or relations.
  • Fundamental characteristics:
  • Data is organized into relations.
  • Rows represent entities and columns represent attributes.
  • Rows are uniquely identified by a primary key.
  • Column table data: Integer number, decimal number, date, text, etc.
    • Constrained to be a certain type, length, or to have a value between two limits.
  • Primary and foreign keys enable table relationships.
  • User queries perform operations on the database.

Manipulating Data in a Relational Database

• Selecting: Eliminating rows according to certain criteria.
• Projecting: Eliminating columns in a table.
• Joining: Combining two or more tables through common data attributes to create a new table.
• Data normalization: Eliminates data redundancy.

SQL Databases

• SQL: Special-purpose programming language used for accessing and manipulating relational database data.
• SQL databases conform to ACID properties: Atomicity, Consistency, Isolation, Durability.
  • Guarantees that database transactions are processed reliably and ensures the integrity of data in the database.
• SQL Command Examples:
  • SELECT ClientName, Debt FROM Client WHERE Debt > 1000: Displays clients (ClientName) and the amount they owe the company (Debt) from a database table called Client; the query would only display clients who owe the company more than $1,000 (WHERE Debt > 1000).
  • SELECT ClientName, ClientNum, OrderNum FROM Client, Order WHERE Client.ClientNum=Order.ClientNum: Combines data from two tables: the Client table and the Order table (FROM Client, Order). The command creates a new table with the client name, client number, and order number (SELECT ClientName, ClientNum, OrderNum). Both tables include the client number, which allows them to be joined. This ability is indicated in the WHERE clause, which states that the client number in the Client table is the same as (equal to) the client number in the Order table (WHERE Client.ClientNum=Order.ClientNum).
  • GRANT INSERT ON Client to Guthrie: Allows Bob Guthrie to insert new values or rows into the Client table.

Popular Relational Database Management Systems

• Open-Source Relational DBMS: MySQL, PostgreSQL, MariaDB, SQL Lite, CouchDB, MongoDB
• Relational DBMS for Individuals and Workgroups: Microsoft Access, IBM Lotus Approach, Google Base, OpenOffice Base, Airtable, Knack
• Relational DBMS for Workgroups and Enterprise: Oracle, IBM DB2, Sybase Adaptive Server, Teradata, Microsoft SQL Server, Progress OpenEdge
• Database as a service (DAAS): Database stored on a service provider’s servers.
  • Database accessed by service subscriber over the Internet.
  • Database administration handled by the service provider.
  • DaaS advantage: Eliminates the installation, maintenance, and monitoring of in-house databases.

Data Management

• Data management: Integrated set of functions that defines the processes by which data is obtained, certified fit for use, stored, secured, and processed.
  • Ensures data accessibility, reliability, and timeliness meet the data users’ needs.
• Data governance: Defines roles, responsibilities, and processes.
  • Ensures data can be trusted and used by the entire organization.
  • Ensures people are identified and in place who are responsible for fixing and preventing issues with data.
• Database administrator (DBA): Skilled and trained IS professional.
  • Holds discussions with business users to define their data needs.
  • Applies database programming languages to craft a set of databases to meet those needs.
  • Tests and evaluates databases.
  • Monitors database performance and implements changes to improve response time for user queries.
  • Assures data is secure.
• Data steward: Typically a non-IS employee who manages critical data entities or attributes.
• Data lifecycle management (DLM): Policy-based approach that manages enterprise’s data flow from initial acquisition or creation and storage until data becomes outdated and is deleted.

Summary

• Well-designed and well-managed databases help with decision-making.
• High-quality data benefits include improved decision making, increased customer satisfaction, increased sales, improved innovation, raised productivity, and ensured compliance.
• A strong data management program is needed to ensure high-quality data.
• Data governance is the core component of data management.