C5+

File Organization Terms and Concepts

  • Database: A group of related files.
  • File: A group of records of the same type.
  • Record: A group of related fields.
  • Field: A group of characters as word(s) or number(s).
  • Entity: A person, place, or thing on which we store information.
  • Attribute: Each characteristic or quality describing the entity.

The Data Hierarchy

The data hierarchy progresses from bits and bytes to fields, records, files, and databases.

  • Bit: The smallest unit of data, representing a 0 or 1.
  • Byte: A group of bits, often representing a character.
  • Field: A group of characters, such as a name or number.
  • Record: A collection of related fields.
  • File: A group of records of the same type.
  • Database: An organized collection of related files.

Problems with the Traditional File Environment

  • Data Redundancy: Duplication of data across different files.
  • Data Inconsistency: The same attribute may have different values in different files.
  • Program-Data Dependence: Changes in programs require changes to the data.
  • Lack of Flexibility: Difficult to produce ad hoc reports.
  • Poor Security: Limited control over data access.
  • Lack of Data Sharing and Availability: Information cannot flow freely across departments.

Database Management Systems (DBMS)

  • Database: Serves many applications by centralizing data and controlling redundant data.
  • Database Management System (DBMS):
    • Interfaces between applications and physical data files.
    • Separates logical and physical views of data.
    • Solves problems of traditional file environment.
    • Controls redundancy.
    • Eliminates inconsistency.
    • Uncouples programs and data.
    • Enables organization to centrally manage data and data security.

Relational DBMS

  • Represent data as two-dimensional tables.
  • Each table contains data on entity and attributes.
  • Table: Grid of columns and rows.
    • Rows (tuples): Records for different entities.
    • Fields (columns): Represents attribute for entity.
    • Key field: Field used to uniquely identify each record.
      • Primary key: Field in table used for key fields.
      • Foreign key: Primary key used in second table as look-up field to identify records from original table.

Operations of a Relational DBMS

  • SELECT: Creates subset of data of all records that meet stated criteria.
  • JOIN: Combines relational tables to provide user with more information than available in individual tables.
  • PROJECT: Creates subsets of columns in table, creating tables with only the information specified.

Capabilities of Database Management Systems

  • Data definition capability: Specifies structure of database.
  • Data dictionary: Stores definitions of data elements and their characteristics.
  • Querying and reporting:
    • Data manipulation language: Used to add, change, delete, retrieve data from database.
      • Structured Query Language (SQL): The standard data manipulation language for relational database management systems.
  • Many DBMS have report generation capabilities for creating polished reports (Microsoft Access).

Designing Databases

  • Conceptual design vs. physical design
    • Conceptual design describes how the data elements in the database are to be grouped.
    • Physical design deals with the physical arrangement of data on storage devices.
  • Normalization: Streamlining complex groupings of data to minimize redundant data elements and awkward many-to-many relationships.
  • Referential integrity: Rules used by RDBMS to ensure relationships between tables remain consistent.
  • Entity-relationship diagram (ERD): A visual representation of entities and their relationships.
  • A correct data model is essential for a system serving the business well.

Non-relational Databases and Databases in the Cloud

  • Non-relational databases: “NoSQL”
    • More flexible data model.
    • Data sets stored across distributed machines.
    • Easier to scale.
    • Handle large volumes of unstructured and structured data.
  • Databases in the cloud
    • Appeal to start-ups, smaller businesses.
    • Amazon Relational Database Service, Microsoft SQL Azure.
    • Private clouds.

The Challenge of Big Data

  • Big data: Massive sets of unstructured/semi-structured data from web traffic, social media, sensors, etc.
  • Volumes are too great for typical DBMS, often involving petabytes or exabytes of data.
  • Can reveal more patterns, relationships, and anomalies.
  • Requires new tools and technologies to manage and analyze.

Business Intelligence Infrastructure

  • Array of tools for obtaining information from separate systems and from big data.
  • Data warehouse: Stores current and historical data from many core operational transaction systems.
    • Consolidates and standardizes information for use across the enterprise, but data cannot be altered.
    • Provides analysis and reporting tools.
  • In-memory computing: Used in big data analysis.
    • Uses computer's main memory (RAM) for data storage to avoid delays in retrieving data from disk storage.
    • Can reduce hours/days of processing to seconds.
    • Requires optimized hardware.
  • Analytic platforms: High-speed platforms using both relational and non-relational tools optimized for large datasets.

Analytical Tools: Relationships, Patterns, Trends

  • Tools for consolidating, analyzing, and providing access to vast amounts of data to help users make better business decisions.
    • Multidimensional data analysis (OLAP).
    • Data mining.
    • Text mining.
    • Web mining.

Online Analytical Processing (OLAP)

  • Supports multidimensional data analysis.
    • Viewing data using multiple dimensions.
    • Each aspect of information (product, pricing, cost, region, time period) is a different dimension.
    • Example: How many washers sold in the East in June compared with other regions?
  • OLAP enables rapid, online answers to ad hoc queries.

Data Mining

  • Finds hidden patterns, relationships in datasets.
    • Example: customer buying patterns.
  • Infers rules to predict future behavior.
  • Types of information obtainable from data mining:
    • Associations.
    • Sequences.
    • Classification.
    • Clustering.
    • Forecasting.

Text Mining and Web Mining

  • Text mining: Extracts key elements from large unstructured data sets.
    • Sentiment analysis software.
  • Web mining: Discovery and analysis of useful patterns and information from the web.
    • Web content mining.
    • Web structure mining.
    • Web usage mining.

Databases and the Web

  • Many companies use the web to make some internal databases available to customers or partners.
  • Typical configuration includes:
    • Web server.
    • Application server/middleware/CGI scripts.
    • Database server (hosting DBMS).
  • Advantages of using the web for database access:
    • Ease of use of browser software.
    • Web interface requires few or no changes to the database.
    • Inexpensive to add web interface to system.

Establishing an Information Policy

  • Firm's rules, procedures, roles for sharing, managing, and standardizing data.
  • Data administration: Establishes policies and procedures to manage data.
  • Data governance: Deals with policies and processes for managing availability, usability, integrity, and security of data, especially regarding government regulations.
  • Database administration: Creating and maintaining the database.

Ensuring Data Quality

  • More than 25 percent of critical data in Fortune 1000 company databases are inaccurate or incomplete.
  • Before a new database is in place, a firm must:
    • Identify and correct faulty data.
    • Establish better routines for editing data once the database is in operation.
  • Data quality audit: A structured survey of the accuracy and level of completeness of the data in an information system.
  • Data cleansing: Activities for detecting and correcting data in a database that are incorrect, incomplete, improperly formatted, or redundant.