Unit 11
Unit 11 Content
A database is a collection of information that is organized so that it can easily be accessed, managed, and updated.
Databases can be classified according to types of content: bibliographic, full-text, numeric, and images.
The structure is achieved by organizing the data according to a database model
Without databases, you could not store and retrieve large quantities of information easily. Even very large databases can provide the information you request in seconds.
Databases provide additional advantages:
They enable information sharing.
They provide data centralization.
They promote data integrity.
They allow the flexible use of data.
Databases are more complex to construct and administer than tables (such as an Excel spreadsheet table).
They also can be time-consuming and expensive to set up. It is helpful to have an experienced database administrator assist with the construction of large databases.
Data privacy concerns also arise when using databases.
Despite the increased complexity of databases and the issues surrounding privacy, however, the advantages of databases far outweigh the administrative disadvantages.
A category of information in a database is stored in a field. Fields are displayed in columns.
Each field is identified by a field name, which is a way of describing the field.
In a database, fields have other characteristics to describe them (aside from the field name), such as field data types and field size.
The group of data for one item across related fields is called a record.
A group of related records is called a table.
Tables usually are organized by a common subject
Primary Key
To keep database records distinct, each record should have one field that has a value unique to that record. This unique field is called a primary key.
Establishing a primary key and ensuring that it is unique makes it impossible to duplicate records.
Types of databases
The three major types of databases in use are relational, object-oriented, and multidimensional databases.
Of these three, relational databases still have the largest market share.
A relational database organizes data in table format by logically grouping similar data into relations (or tables that contain related data).
In relational databases, tables are logically linked to each other by including their primary keys in other tables with related information.
Microsoft Access is an example of a relational database
An object-oriented database stores data in objects, not in tables.
Objects contain not only data, but methods for processing or manipulating that data.
This allows object-oriented databases to store more types of data (such as unstructured data) than relational databases and to access that data faster.
A multidimensional database stores data in multiple dimensions as opposed to relational databases, which store data in two-dimensional tables.
Multidimensional databases organize data in a cube format.
Each data cube has a measure attribute, which is the main type of data that the cube is tracking.
The two main advantages of multidimensional databases are that they can easily be customized to provide information to a variety of users and they can process data much faster than pure relational databases.
Querying data
A database query is a question or inquiry you ask the database so that it provides you with the records you wish to view.
When you query a database, you instruct it to search for a particular piece of data.
Queries also enable you to have the database select and display records that match certain criteria.
All modern DBMSs contain a query language that the software uses to retrieve and display records.
The most popular query language today is structured query language, or SQL.
Outputting data
The most common form of output for any database is a printed (or electronic) report.
Businesses routinely summarize the data within their databases and compile summary data reports.
Database systems can also be used to export data to other applications.
Exporting data involves putting it into an electronic file in a format that another application can understand.
An example would be exporting of online banking statements to Quickbooks.
Relational database operations
Relational databases operate by organizing data into various tables based on logical groupings.
In relational databases, the links between tables that define how the data is related are referred to as relationships.
To establish a relationship between two tables, both tables must have a common field (or column).
In databases, the goal is to reduce data redundancy by recording data only once.
This process is called normalization of the data.
To get tables to work together, a foreign key is used.
A foreign key is the primary key of another table that is included in a table for purposes of establishing relationships with that other table.
Data storage
A data warehouse is a large-scale electronic repository of data that contains and organizes in one place all the data related to an organization.
Individual databases contain a wealth of information, but each database’s information usually pertains to one topic.
Data warehouses consolidate information from disparate sources to present an enterprise-wide view of business operations.
Data in the data warehouse is organized by subject.
Most databases focus on one specific operational aspect of business operations.
Populating data warehouse
Source data for data warehouses can come from three places:
Internal sources (such as company databases);
External sources (suppliers, vendors, and so on);
Customers or visitors to the company website.
Companies can use software on their websites to capture information about each click that users make as they navigate through the site
Data mining
Data mining is the process by which great amounts of data are analyzed and investigated.
The objective is to spot significant patterns or trends within the data that would otherwise not be obvious.
The main reason businesses mine data is to understand their customers better.
Data mining enables managers to sift through data in a number of ways.
Each method produces different information that managers can then base their decisions on.
The following are five things managers do to make their data meaningful:
Classification:
Before mining, managers define data classes that they think will be helpful in spotting trends.
They then apply these class definitions to all unclassified data to prepare it for analysis.
Estimation:
When managers classify data, the record either fits the classification criteria or it doesn’t.
Estimation enables managers to assign a value, based on some criterion, to data.
For example, assume a bank wants to send out credit card offers to people who are likely to be granted a credit card.
The bank may run the customer data through a program that assigns them a score based on where they live, their household income, and their average bank balance.
This provides managers with an estimate of the most likely credit card prospects so that they can include them in the mailing.
Affinity grouping or association rules:
When mining data, managers can also determine which data goes together.
In other words, they can apply affinity grouping or association rules to the data.
For example, suppose analysis of a sales database indicates that two items are bought together 70 percent of the time.
Based on this data, managers might decide that these items should be pictured on the same page in the next mail-order catalogue they send out.
Clustering:
Clustering involves organizing data into similar subgroups, or clusters.
It is different from classification in that there are no predefined classes.
The data-mining software makes the decision about what to group together, and it is up to managers to determine whether the clusters are meaningful.
For example, the data-mining software may identify clusters of customers with similar buying patterns.
Further analysis of the clusters may reveal that certain socioeconomic groups have similar buying patterns.
Description and visualization:
Often, the purpose of data mining is merely to describe data so managers can visualize it.
Sometimes having a clear picture of what is going on with the data helps people to interpret it in new and different ways.
Chapter 11 notes:
Mozilla introduced new Terms of Use for Firefox, raising user concerns.
The updated terms grant Mozilla a license to use information input through the browser.
Users worry Mozilla may access personal data like passwords and browsing history.
Mozilla clarified that it doesn’t own user data, only processes it as per Firefox Privacy Notice.
The company emphasized it doesn’t sell or buy user data, but collects technical data for browser improvement.
Critics argue Mozilla's approach is similar to big tech companies, undermining its privacy stance.
Some users are considering switching to browsers like Google Chrome or Brave due to declining trust in Mozilla.
Personal data is generated by devices and apps, stored in large data centers, and used for marketing and product development.
The GDPR in the UK and EU sets a global standard for data protection.
GDPR allows individuals to control what personal data is stored and how it’s used.
Privacy statements must be clear, and customers must opt-in and can withdraw consent at any time.
GDPR applies to any company with a digital presence in the EU, including major U.S. companies like Facebook, Google, and Uber.
In the U.S., the FTC has issued best practices for businesses to protect consumer privacy.
The FTC recommends that Congress create legislation for stronger consumer privacy protections.
Databases store, manage, and use data generated by everyday actions like using ATMs, shopping online, or registering for classes.
A database is a collection of related data that can be stored, sorted, organized, and queried.
Databases make data more meaningful and useful by organizing it in specific structures.
Example: Amazon uses a database to categorize and filter products to improve the consumer experience and manage inventory.
Advantages of databases:
Efficiently manage large amounts of data.
Enable information sharing through centralized data.
Promote data integrity, ensuring data is accurate and reliable.
Centralized databases allow for data access from one location, eliminating the need for multiple updates.
Data integrity ensures accuracy by preventing errors like duplicate entries or data outside of defined ranges.
Disadvantages
Large, complex databases can be costly and time-consuming to set up and maintain.
Require experienced database administrators (DBAs) to design, construct, and manage them effectively.
Databases vary from simple to complex, depending on the data that needs to be managed.
Flat databases are simple tables or lists, useful for organizing basic data (e.g., using Word or Excel).
Flat databases can cause problems like:
Data duplication (redundancy), as information may be repeated across different lists.
Inconsistency when data is not updated across all lists.
Inappropriate or invalid data entry.
Incomplete data due to lack of checks for required information.
Relational databases, like Microsoft Access, organize data into tables based on logical groupings and link them through common fields.
Relationships in relational databases:
One-to-many: One record in a table can relate to multiple records in another.
One-to-one: Each record in one table corresponds to a single record in another.
Many-to-many: Multiple records in one table relate to multiple records in another, often requiring an associate table.
Normal forms (1NF to 5NF) are guidelines to structure databases efficiently, reducing redundancy and ensuring logical design.
1NF ensures unique column names, separate related data, and unique identifiers (primary keys).
2NF avoids duplicate data, and 3NF ensures all columns relate to the primary key.
4NF and 5NF apply in more complex situations.
Normalization ensures data is organized efficiently by removing redundancy and inconsistency, improving data integrity and database efficiency.
Object-oriented databases store data in objects, allowing them to handle a wider variety of data types (e.g., audio, video, images) and access it faster than relational databases.
Object-oriented databases require data conversion and use query languages like Object Query Language (OQL) for data management.
Graph databases track relationships between data, ideal for social networks, recommendation engines, and fraud detection.
Graph databases store both discrete data and relationships, making data insights faster to gather. Neo4j is a free, open-source option.
Multidimensional databases organize data in cubes, with measure and feature attributes, allowing analysis from multiple perspectives.
These databases are faster and more customizable than relational databases, processing data efficiently for large-scale applications like eBay and Amazon.
Multidimensional databases are commonly used in data warehouses and OLAP systems for quick data analysis.
NoSQL databases manage large amounts of unstructured data generated from social media, online activities, and websites.
Unlike relational and object-oriented databases, NoSQL databases store data in documents (like file folders) rather than tables or objects.
NoSQL databases provide flexibility and fast access to data but don’t offer the same consistency as relational databases.
Developed by companies like Google, Amazon, and Facebook, NoSQL databases are designed to handle massive online data more effectively.
Businesses may need both NoSQL and SQL databases to manage different types of data.
Low-code and no-code platforms allow businesses to manage databases without requiring advanced programming skills.
These platforms use simple interfaces with drag-and-drop or form-based input methods, making database management accessible to non-programmers.
Popular low-code database tools include Airtable, Stackby, and Actiondesk.
Databases are created and managed with application software like Oracle or Microsoft Access for data capture and analysis.
Once designed and tested, databases are populated with records, and users can extract and present data.
A database management system (DBMS) handles four main operations: storing and defining data, viewing, adding, deleting, and modifying data, querying data, and outputting data.
In relational databases, data is organized into fields, records, and tables:
Fields are categories of data (columns) with unique field names.
Records are groups of related fields (rows).
Tables are groups of related records.
Field names should be unique, avoid spaces, and may include table identifiers to prevent confusion across tables.
Data types are assigned to fields to ensure correct data entry, e.g., text, numbers, dates, currency, etc.
Common data types include Short Text, Long Text, Number, Date/Time, Yes/No, and Hyperlink.
Additional field properties include field size (defines the character limit), default value (preset data for fields), and caption (a user-friendly display name for fields).
Careful planning is required to define fields, especially when determining the necessary categories and ensuring efficient data handling for sorting and reporting.
In most fields, multiple records can share the same value, but each record must have a unique primary key to maintain data integrity.
A primary key ensures that records are distinct, such as a student ID in the Student Information table.
A good primary key must have unique values and prevent duplicate records. It’s important for tracking entities like orders or students.
Primary keys don’t have to be pre-existing values (e.g., Social Security numbers), and serial numbers are often used as primary keys.
In relational databases, primary keys in one table are linked to foreign keys in related tables to establish relationships.
Referential integrity ensures that data across related tables is synchronized, preventing data mismatches between tables.
Databases like Spotify use vast systems to manage music, users, playlists, and payments, analyzing data to provide recommendations and track user behaviors.
Large databases often use a data dictionary (or schema) to organize components like tables, queries, and forms, and to define relationships between tables.
Metadata in the data dictionary includes field names, data types, descriptions, properties, and field sizes, helping to organize and validate the data.
Data dictionaries are usually hidden from users to prevent accidental changes but can be accessed through features like the Database Documenter in Microsoft Access.
Data can be manually entered into a database or imported from other digital formats (e.g., spreadsheets, web data) to save time and reduce errors.
Imported data must match the database format, and nonconforming data is flagged for modification.
Input forms help make manual data entry more efficient by guiding users and ensuring correct record updates.
Validation ensures data integrity by setting rules for acceptable data in fields, such as range checks, completeness checks, and consistency checks.
A range check ensures values fall within a specified range (e.g., pay rates between $7.25 and $15.50).
Completeness checks ensure all required fields are filled before submission, displaying error messages when fields are left empty.
Consistency checks compare values across fields to verify data (e.g., ensuring a birth date is earlier than an enrollment date).
Alphabetic and numeric checks ensure correct data types are entered (e.g., only letters in a name field, numbers in a price field).
Data can be viewed one record at a time using forms, or all records can be viewed and reordered in tables.
Records can be sorted in ascending or descending order using the sort feature (e.g., sorting by last name in alphabetical order).
Filters can temporarily display records that match certain criteria, useful for quick analyses, but only for fields in the same table.
Queries retrieve data from one or more tables and can be saved to run again for updated results.
Queries allow selecting specific fields and applying criteria (e.g., creating a list of students in a specific dorm).
Modern databases offer wizards and tools to guide query creation, such as Access's Simple Query Wizard or Design View.
SQL (Structured Query Language) is used for querying, and can be viewed and edited in tools like Access.
Reports are used to present data in a more organized and readable format, often including summaries and totals.
Data can be exported to other applications like Excel or Word for further analysis or inclusion in reports.
Data can be converted to formats like PDF and XML for use in other software.
SQL (Structured Query Language) is used to extract data from databases in relational and object-oriented systems.
SQL uses relational algebra with variables (table names, field names) and operations (select, from, where).
A select query retrieves a subset of data based on specified criteria; the typical format includes field names, the table (from), and selection criteria (where).
A WHERE statement in SQL allows further restriction of data based on conditions, such as GPA or state.
A join query links two or more tables using a common field to extract relevant data from both.
Join queries are similar to select queries, but the FROM statement contains two table names, and the WHERE statement defines the relationship between tables and selection criteria.
The AND in a join query ensures both parts of the condition must be true for results to be returned.
Data is typically stored in a single database for small enterprises but becomes complex as organizations grow and require multiple, department-specific databases.
Data warehouses and data marts help store and combine data from various department-specific databases into a centralized, accessible location.
A data warehouse consolidates data from multiple sources into a single large repository to provide an enterprise-wide view for better decision-making and trend analysis.
Data warehouses obtain data from internal sources (sales, billing), external sources (vendors, suppliers), and clickstream data (website user behavior).
Data warehouses store time-variant data, allowing analysis of both current and historical data for decision-making and projections.
Data for a warehouse is extracted, transformed, and loaded (ETL) into the system, with data staging used to reformat and process data before it's moved into the warehouse.
Data dashboards are real-time visualization tools displaying key performance indicators (KPIs) from various corporate data sources for better decision-making and collaboration.
Data in a warehouse is organized by subject (e.g., sales, inventory), not by department, to create comprehensive reports from combined data.
For example, data from separate TV and cell phone sales databases would be consolidated into a single Electronics Sales subject in a data warehouse, allowing for a holistic view of all electronic sales.
Data in a data warehouse can be extracted and analyzed using OLAP software, which allows flexible views and easy manipulation of data.
Data marts are smaller, specialized subsets of data warehouses that focus on specific business components (e.g., sales).
Data mining involves analyzing large datasets to identify patterns and trends not immediately obvious.
Businesses use data mining to better understand customers, improve marketing strategies, and optimize product placement.
Techniques used in data mining include:
Anomaly detection: Identifying outliers or unusual data, useful in fraud detection.
Association/affinity grouping: Identifying items that are frequently bought together, aiding in product placement.
Classification: Organizing data into predefined categories for analysis (e.g., good vs. bad credit risks).
Clustering: Grouping similar data without predefined categories to spot patterns.
Estimation/regression: Predicting values (e.g., sales or property values) based on historical data.
Visualization: Using graphs and charts to better understand data trends and relationships.
Data-mining techniques help managers make informed decisions by revealing insights within data.
Hadoop is a platform used to manage big data, storing and processing large datasets across multiple servers using parallel processing.
Organizations generate, acquire, process, and store large amounts of data daily, but data protection remains challenging.
A data breach occurs when information is exposed to unauthorized individuals.
Thousands of data breaches happen annually, primarily affecting businesses.
Individuals should consider the purpose, legitimacy, and protection of their data before sharing it.
Ask why the data is needed and whether its collection serves legitimate, important purposes.
Ensure data is protected through policies and that access is limited to necessary personnel.
Inquire if the data will be used for purposes beyond the original intent, such as marketing or sharing with other companies.
Be cautious about sharing information that could be used for identity theft, like Social Security numbers or birth dates.
Evaluate the data protection practices of organizations holding your information, especially in light of past breaches.
Be vigilant and mindful of how your data is used to protect your privacy and avoid misuse.
Making business decisions requires timely, accurate data for areas like product development, marketing, and procurement.
An information system gathers and analyzes data through databases, application programs, and procedures.
Information systems perform data acquisition, processing, storage, and provide outputs to make information useful.
Different types of information systems exist for operational, managerial, and strategic decision-making.
Transaction-Processing Systems (TPS) track everyday business transactions like orders and payroll.
TPS transactions can be processed in real-time or through batch processing for efficiency.
TPS ensures transaction accuracy through atomicity, consistency, isolation, and durability (ACID test).
Management Information Systems (MIS) provide timely, accurate information to aid decision-making, based on data from TPSs.
MIS generates detailed, summary, and exception reports for managers to make strategic decisions.
Decision Support Systems (DSS) help managers analyze data, simulate outcomes, and make informed decisions.
DSS integrates data from multiple sources and allows for the inclusion of personal insights in decision-making.
DSS gets data from both internal and external sources.
Internal data comes from the company’s own systems (e.g., TPS), providing details on customers, orders, and inventory.
External data includes third-party sources like customer demographics, mailing lists, or government statistics.
A model management system helps build management models using data for decision-making.
Management models are analysis tools that use internal and external data to evaluate business situations.
Business Intelligence (BI) systems help executives make informed decisions using large data sets (big data).
BI systems often use data from warehouses or marts to provide valuable insights for business strategies.
ERP systems centralize data across all business functions, improving accessibility and decision-making.
ERPs use a common database for various areas like human resources, accounting, and manufacturing.
Knowledge-based systems enhance decision-making by simulating human intelligence.
Expert systems replicate decision-making processes of human experts to solve specific problems (e.g., medical diagnoses).
Natural Language Processing (NLP) systems allow users to communicate with computers using natural language.
AI involves creating machines that think like humans, though no machine has fully replicated human intelligence yet.
Knowledge-based systems support decision-making by incorporating human judgment and fuzzy logic.
Fuzzy logic allows systems to consider probabilities and experiential learning, offering more flexibility than rigid algorithms.
Virtual agents, powered by AI, handle customer service tasks by answering questions, saving businesses on labor costs.
Mobile business intelligence allows access to data through smartphones and tablets, improving decision-making.
Mobile systems interact in real-time with customers and business partners, enhancing service and productivity.
Mobile BI systems integrate with other business systems like knowledge-based and ERP to deliver key metrics and dashboards.
Examples of mobile BI applications include Oracle’s Business Intelligence Enterprise Edition and SAP’s BusinessObjects Explorer.
Executives can monitor key performance indicators and metrics remotely on their mobile devices.
Mobile BI improves business processes, employee productivity, and customer service by providing on-demand access to critical information.
Advancements in mobile BI include collaboration, cloud computing, and leveraging social data.
Security and privacy remain areas for improvement in mobile business intelligence.
Chapter Review Summary:
Part 1: Database Fundamentals
Learning Outcome 11.1: You will be able to explain the basics of databases, including the most common types of databases and the functions and components of relational databases in particular.
The Need for Databases
Objective 11.1 Explain what a database is and why databases are useful.
Databases are electronic collections of related data that can be organized so that data is more easily accessed and manipulated. Properly designed databases cut down on data redundancy and duplicate data by ensuring that relevant data is recorded in only one place. This also helps eliminate data inconsistency, which comes from having different data about the same transaction recorded in different places. When databases are used, multiple users can share and access information at the same time.
Some of the main advantages of using databases are that they can manage large amounts of data efficiently, enable information sharing, and promote data integrity. Data sharing is made possible because databases provide data centralization, a shared source that everyone can access. Data centralization also promotes better data integrity.
Database Types
Objective 11.2 Describe features of flat databases.
A flat database, often represented as a list or simple table, is used to organize simple data. Tables created in Microsoft Word or in a Microsoft Excel spreadsheet serve as a flat database.
Flat databases are subject to several types of problems, including data redundancy, data inconsistency, inappropriate data, and incomplete data.
Objective 11.3 Describe features of relational databases.
A relational database operates by organizing data into various tables based on logical groupings and then creating a relationship between tables by linking through a common field.
There are three main types of relationships. A one-to-many relationship has a record appearing only once in a table while also appearing many times in a related table. A one-to-one relationship is when there is only one instance of a record in a table and only one corresponding record in a related table. A many-to-many relationship has multiple instances of records in both the primary and related tables.
Objective 11.4 Describe features of object-oriented databases.
Object-oriented databases store data in objects rather than in tables and provide methods for processing or manipulating the data. Object-oriented databases are best for handling unstructured data, such as media files and extremely large documents.
Objective 11.5 Describe features of multidimensional databases.
Multidimensional databases store data that can be analyzed from different dimensions, or perspectives. Multidimensional databases organize data with three dimensions, giving it a cubelike format. In addition to the field and record dimensions of other databases, a measure attribute is also used.
Objective 11.6 Describe how dynamic, web-created data is managed in a database.
NoSQL databases store the unstructured data generated from web-based applications such as data from social media sharing and other online activities, online personal settings, photos, and videos. In addition, NoSQL databases store website-usage metrics, location-based information, and clickstream data.
NoSQL systems group related information into folder-like documents that hold a variety of data instead of the rigid row-and-column format of tables.
Using Databases
Objective 11.7 Describe how relational databases organize and define data.
Database management systems (DBMSs) are specially designed applications (such as Microsoft Access) that interact with the user, other applications, and the database itself to capture and analyze data. The main operations of a DBMS are creating databases, entering data, viewing (or browsing) data, sorting (or indexing) data, extracting (or querying) data, and outputting data.
Data is organized using fields, records, and tables. A category of information in a database is stored in a field. Fields are assigned a field name and a data type that indicates what type of data can be stored in the field. Common data types include short text, long text, numeric, calculated, date/time, yes/no, object linking and embedding (OLE) object, and hyperlink. A group of related fields is a record. A group of related records is a table or file. Field properties reflect data dictionary details. Some common field properties include field size, default value, and captions.
To keep records distinct, each record must have one field that has a value unique to that record. This unique field is a primary key (or a key field).
Objective 11.8 Describe how data is inputted and managed in a database.
Input forms are used to control how new data is entered in a database as well as how changes are made to data. Data validation helps ensure that only valid data is entered in a field. Common validation rules include a range check, a completeness check, a consistency check, and an alphabetic or numeric check.
Queries and filters are used to display a subset of data. A query language is used to extract records from a database. Almost all relational databases today use Structured Query Language (SQL). However, most DBMSs include wizards that enable you to query the database without learning a query language. Reports are used to output data from a database, or data can be transferred to another software application for further distribution or modification.
Part 2: How Businesses Use Databases
Learning Outcome 11.2: You will be able to explain how businesses use data warehouses, data marts, and data mining to manage data and how business information systems and business intelligence are used to make business decisions.
Data Warehousing and Storage
Objective 11.9 Explain what data warehouses and data marts are and how they are used.
A data warehouse is a large-scale collection of data that contains and organizes in one place all the relevant data for an organization. Data warehouses often contain information from multiple databases. Because it can be difficult to find information in a large data warehouse, small slices of the data warehouse, called data marts, are often created.
Data usually goes through an extraction and transformation process to remove data from other databases and transform it so the collected data is formatted similarly. Then, data is stored in data staging until it is ready or needed to go to the data warehouse.
The information in data marts pertains to a single department within the organization.
Data warehouses and data marts consolidate information from a wide variety of sources to provide comprehensive pictures of operations or transactions within a business.
Objective 11.10 Describe data mining and how it works.
Data mining is the process by which large amounts of data are analyzed to spot otherwise hidden trends. Through processes such as classification, estimation, affinity grouping, clustering, and description (visualization), data is organized so that it provides meaningful information that managers can use to identify business trends.
Using Databases to Make Business Decisions
Objective 11.11 Describe the main types of business information systems and how they are used by business managers.
An information system is a software-based solution used to gather and analyze information. All information systems perform similar functions, including acquiring, processing, and storing data and providing the user with a means to output the results into meaningful and useful information.
A transaction-processing system (TPS) is used to keep track of everyday business activities.
A management information system (MIS) provides timely and accurate information that enables managers to make critical business decisions.
A decision support system (DSS) is designed to help managers develop solutions for specific problems. A model management system is software that assists in building analysis tools for DSSs.
Executive managers use business intelligence systems to analyze and interpret data to make informed decisions about how best to run a business.
An enterprise resource planning (ERP) system is a large software system that gathers information from all parts of a business and integrates it to make it readily available for decision making.
A knowledge-based system provides intelligence to decision making. There are several kinds of knowledge-based systems. An expert system tries to replicate the decision-making process of human experts to solve specific problems. A natural language processing (NLP) system uses natural spoken or written language rather than a computer programming language to communicate with a computer. Artificial intelligence (AI) attempts to create computers that think like humans.
Key Terms:
alphabetic check
artificial intelligence (AI)
batch processing
binary large object (BLOB)
business intelligence (BI)
business intelligence system
caption
clickstream data
completeness check
consistency check
data centralization
data dictionary (database schema)
data inconsistency
data integrity
data mart
data mining
data redundancy
data staging
data type (field type)
data warehouse
database
database administrator (DBA) (database designer)
database management system (DBMS)
decision support system (DSS)
default value
detail report
enterprise resource planning (ERP) system
exception report
expert system
field
field constraint
field name
field properties
field size
filter
flat database
foreign key
fuzzy logic
information system
input forms
join query
knowledge-based system
management information system (MIS)
many-to-many relationship
metadata
model management system
multidimensional database
natural language processing (NLP) system
normalization
NoSQL database
numeric check
object-oriented database
Object Query Language (OQL)
one-to-many relationship
one-to-one relationship
online analytical processing (OLAP)
online transaction processing (OLTP)
primary key field
query
query language
range check
real-time processing
record
referential integrity
relational algebra
relational database
relationship
select query
structured (analytical) data
Structured Query Language (SQL)
summary report
table (file)
time-variant data
transaction-processing system (TPS)
unstructured data
validation
validation rule
IT Activities notes:
Advantages of Using Databases
A database is a collection of related data that can be easily stored, organized, and queried. Databases provide many advantages over simple Excel lists.
Databases allow you to organize, view, and print the data they contain in a variety of ways to suit individual users’ needs. For example, Ron’s admissions office can create a report showing total student enrollment, while the financial aid office can create a report showing which students are behind in payments.
By centralizing data in one place, databases make it easier for users to share data. And, because users only have to enter data into one place in the database, they reduce the possibility of errors being introduced when data is entered or updated.
Through a series of validation and consistency checks, databases ensure that the data stored in the database is accurate and reliable.
Databases can manage larger amounts of data and can process that data more efficiently than can Excel spreadsheets.
Types of Databases
There are three main types of databases:
Relational databases include only data, which is organized into separate logical groupings (or tables) that contain related data. For example, at a college, a database could have a table with student contact information and another table with class registration information.
Object-oriented databases store data in objects, not tables. These objects contain not only data, but also methods for processing or manipulating that data. Object-oriented databases are adept at handling unstructured data such as audio and video clips, pictures, and extremely large documents.
Multidimensional databases store data in multiple dimensions. This distinguishes them from a relational database, which stores data in two-dimensional tables. Multidimensional databases organize data in a cube format.
Database Components
Databases have three main data storage components.
A category of information in a database is stored in a field. Fields are displayed in columns. Each field is identified by a field name. Here City, State, and Class Code are field names.
A group of related fields is called a record. Here, each row of data for a student is a record.
A group of related records is called a table (or file). Tables usually are organized by a common subject. This group of related records containing student information constitutes a table.
Data Types
When you create a database, you assign each field in the database a field name. You also indicate what type of data can be stored in the field. This is called the data type (or field type). The following are common data types in Microsoft Access:
Short Text fields store text and/or numbers.
Number fields store values that can be used to perform calculations.
Calculated fields store the contents of a calculation.
Date/Time fields store dates and times.
Long Text fields store long pieces of text.
OLE Object fields store objects like pictures or video clips.
Hyperlink fields store hyperlinks.
Creating Database Tables
When you create a database, you must organize data into various tables based on logical groupings. Each table in the database should contain a related group of data on a single topic.
For example, at a college you might find a number of different tables in a database:
A table that includes student contact information (names, addresses, and so on)
A table that includes course information (names of courses, credits per course, and so on)
A table that includes financial aid information (types of financial aid, amount paid, and so on)
When tables are created properly, the goal is to reduce data redundancy by recording data only once. This process is called normalization of the data.
Primary Key
To understand how tables work together in databases, you first have to understand primary keys.
Each record in a database table is assigned a primary key (or key field) to ensure that the record is unique and won’t be confused with other records. For example, in your student record, a primary key might be your student ID or Social Security number because no other student at your school will have the same number as you do.
In this table, two students have the same name. However, the records are never confused with each other because each student is assigned a unique Student ID. The Student ID field is the primary key. By establishing a primary key and ensuring that it is unique, it is not possible to duplicate records in a table.
Foreign Keys
ResidenceID is a common field between these tables. It is a primary key in the Residence Halls table and a foreign key in the Residence Assignments table. StudentID is a common field between these tables. It is a primary key in the Student Information table and a foreign key in the Residence Assignments table.
In relational databases, "relationships" are established among tables to allow the data in the tables to be shared.
In order to establish a relationship between two tables, the primary key of one table is included in the related table. For example, a college database would have one table with student contact information (name, city, state, phone, and so on) and another table with residence assignment (ResidenceID, room number and so on).
These two tables would be linked by a primary key such as a student ID number. When the primary key of one table is included to establish a relationship with another table, it is called a foreign key in the related table. Foreign keys in related tables have to contain the same type of data as that in the primary key.
A database is a collection of related data that can be easily stored, organized, and queried. Databases enable multiple users to share and access data simultaneously. There are three main types of databases: relational databases, object-oriented databases, and multidimensional databases. A category of information in a database is stored in a field. A group of related fields is called a record, and a group of related records is a table. When you create a database, you must organize data into various tables based on logical groupings. In relational databases, "relationships" are established among tables using primary keys and foreign keys.
Cheat Sheet
A. Advantages of Using Databases
Databases provide many advantages:
Databases allow you to organize, view, and print data in a variety of ways.
Databases make it easier for users to share data.
Databases ensure that the data is accurate and reliable.
Databases can manage and process large amounts of data.
B. Types of Databases
There are three main types of databases:
Relational databases include only data, which is organized into tables.
Object-oriented databases store data in objects that also contain methods for processing and manipulating that data.
Multidimensional databases store data in multiple dimensions, organized in a cube format.
C. Database Terminology
Databases have three main components:
A category of information in a database is stored in a field.
A group of related fields is called a record.
A group of related records is called a table (or file).
D. Data Types
When you create a database, you indicate what type of data can be stored in the field.
Short Text fields store text and/or numbers.
Number fields store values that can be used to perform calculations.
Calculated fields store the contents of a calculation.
Date/Time fields store dates and times.
Long Text fields store long pieces of text.
OLE Object fields store objects like pictures or video clips.
Hyperlink fields store hyperlinks.
E. Creating Database Tables
When you create a database, you organize data into various tables. Each table should contain a related group of data on a single topic. When tables are created properly, the data only needs to be recorded once. This process is called normalization of the data.
F. Getting Tables to Work Together
1. Primary Keys
Each record in a database table is assigned a primary key to ensure that the record is unique and won’t be confused with other records.
2. Foreign Keys
In order to establish a relationship between two tables, the primary key of one table is included in the other table. When the primary key of one table is included to establish a relationship with another table, it is called a foreign key in the second table.
Glossary
Alphabetic check: Confirms that only textual characters (such as “Gwen”) are entered in a field.
Binary Large Object (BLOB): Data encoded in binary form.
Browsing: An option with most databases where you view records.
Calculated field (or computed field): A numeric field that stores the contents of a calculation, which is generated with a formula in the numeric field.
Completeness check: Ensures that all fields defined as “required” have data entered into them.
Consistency check: Compares the values of data in two or more fields to determine if these values are reasonable.
Data centralization: Ensuring data integrity; instead of being in multiple lists that have to be maintained, the information is maintained in only one place.
Data dictionary (or database schema): Description of the data. This description is contained in the database’s files.
Data inconsistency: Each time the information in the list changes, multiple lists must be updated and if not, then inconsistent data results.
Data integrity: When the data contained in the database is accurate and reliable.
Data redundancy: Duplicated data between lists.
Data type (or field type): Indicates what type of data can be stored in the field.
Database: Collection of related data that can be easily stored, sorted, organized, and queried.
Database administrator (or database designer): An individual trained in the design and building of databases, to assist with the construction of large databases.
Database-Management System (DBMS): Specially designed application software (such as Oracle Database or Microsoft Access) that interacts with the user, other applications, and the database to capture and analyze data.
Database query: A question you ask the database so that it provides you with the records you wish to view.
Date/Time field: Holds data such as birthdays and due dates.
Default value: The value the database uses for the field unless the user enters another value.
Export: Involves putting data into an electronic file in a format that another application can understand.
Field: A database stores each category of information in a field. Fields are displayed in columns.
Field constraint: A property that must be satisfied for an entry to be accepted into the field.
Field name: Each field is identified by a field name, which is a way of describing the field.
Field size: Defines the maximum number of characters or numbers that a field can hold.
Foreign key: The primary key of another table that is included for purposes of establishing relationships with that other table.
Hyperlink field: Stores hyperlinks to Web pages.
Input form: Provides a view of the data fields to be filled, with appropriate labels to assist database users in populating the database.
Join query: When you want to extract data that is in two or more tables, you use a join query. The query actually links (or joins) the two tables using the common field in both tables and extracts the relevant data from each.
Long Text field: Like a text field but can hold long pieces of text.
Many-to-many relationship: Characterized by records in one table being related to multiple records in a second table and vice versa.
Metadata: Data that describes other data; an integral part of the data dictionary.
Multidimensional database: Stores data in more than two dimensions.
Normalization: In databases, the goal is to reduce data redundancy by recording data only once. This process is called normalization of the data.
Number field: Stores values that can be used to perform calculations.
Numeric check: Confirms that only numbers are entered in the field.
Object-oriented database: Stores data in objects, rather than in tables.
Object Query Language (OQL): Similar in many respects to SQL (structured query language), a standard language used to construct queries to extract data from databases.
OLE Object field: Holds items such as pictures, video clips, or documents.
One-to-many relationship: Characterized by a record in one table and occurring most frequently in relational databases.
One-to-one relationship Indicates that for each record in a table there is only one corresponding record in a related table.
Primary key (or key field): To keep records distinct, each record must have one field (the primary key) that has a value unique to that record.
Query: A question or inquiry.
Query language: Software used to retrieve and display records. A query language consists of its own vocabulary and sentence structure, which you use to frame the requests.
Range check: Ensures that the data entered into the database falls within a certain range of numbers.
Record: A group of related fields.
Referential integrity: Means that for each value in the foreign key of one table, there is a corresponding value in the primary key of the related table.
Relation: A table that contains related data.
Relational algebra: The use of English-like expressions that have variables and operations, much like algebraic equations.
Relational database: Organizes data in table format by logically grouping similar data into a relation (a table that contains related data).
Relationship: A link between tables that defines how the data is related.
Select query: Displays a subset of data from a table (or tables) based on the criteria you specify.
Sort: Involves organizing data in a new fashion.
Short Text field: Hold any combination of alphanumeric data (letters or numbers).
Structured (analytical) data: Relational databases excel in the storage of structured (analytical) data.
Structured Query Language (SQL) A type of query language used by almost all relational and object relational databases to extract records from a database.
Table (or file): A group of related records.
Unstructured data: Object-oriented databases are more adept at handling unstructured data that includes nontraditional data such as audio clips (including MP3 files), video clips, pictures, and extremely large documents.
Validation: Process of ensuring that data entered into the database is correct (or at least reasonable) and complete.
Validation rule: Set up in the database to alert the user if a clearly wrong entry is entered in the field instead of a valid entry.
4) What data type can I use for a field if I have to store a page of text?
Correct.
You can use memo fields. They are text fields that are used to hold long pieces of text.
Data Warehouses
Data in a data warehouse is organized by subject such as types of products sold by a retailer. Each product’s data, such as revenue, costs, warranty details, and other features, is collected in an individual database. Then the individual product data is consolidated in a data warehouse to provide summary data such as total revenue generated by all products. Managers then use the consolidated information to produce comprehensive reports.
Data warehouses can get data from three sources:
Internal sources: Sales, billing, inventory, and customer databases all provide a wealth of information. Spreadsheets and other ad hoc analysis tools may contain data that can be loaded into the data warehouse.
External sources: Vendors and suppliers often provide data regarding product specifications, shipment methods and dates, billing information, and so on.
Clickstream data: Software used on company websites to capture information about each click that users make as they navigate through the site is referred to as clickstream data. Using clickstream data–capture tools, a company can determine which webpages users visit most often, how long users stay on each webpage, which sites directed users to the company site, and user demographics. Such data can provide valuable clues as to what a company needs to improve on its website to stimulate sales.
Data Marts
Small slices of the data warehouse, each called a data mart, are often created so that companies can analyze a related set of data that is grouped together and separated out from the main body of data in the data warehouse. Whereas data warehouses have an enterprise-wide depth, the information in data marts pertains to a single component of the business. Data can be extracted using powerful OLAP query tools, or it can be stored in specialized data marts for use by specific employee groups.
For instance, if you need accurate sales-related information and you don’t want to wade through customer service data, accounts payable data, and product shipping data to get it, a data mart that contains information relevant only to the sales department can be created to make the task of finding this data easier.
Data Mining
Data-mining techniques are used when businesses want to understand their customers better so they can learn the types of customers who buy their products and what motivates customers to make purchases. Using this type of information, businesses can tailor their marketing and promotional techniques so they are most effective.
Data mining enables managers to sift through data in several ways. Each technique produces different information on which managers can then base their decisions. The figure describes the different data mining techniques:
Information Systems
All information systems perform similar functions, including acquiring data, processing that data into information, storing the data and information, and providing the user with several output options with which to make the information meaningful and useful as is shown in figure.
There are several types of information systems. Each system responds to the needs of an organization, and is generally specific to the type of information required. Generally, the information can be categorized as operational, managerial, and strategic.
Transaction-processing system (TPS)
A transaction-processing system (TPS) is an operational-level system that keeps track of everyday business transactions or activities such as order tracking, order processing, payroll, and cash management.
Transactions can be entered in real time or through batch processing. Real-time processing is used to track activities as they happen. Online transaction processing (OLTP) ensures that data entered through a website or mobile app is processed and current. Batch processing is used to track transactions that are accumulated until a certain point is achieved and then several transactions are processed at once. Batch processing is used for activities that aren’t time sensitive.
Management Information System (MIS)
Management information systems (MIS) were a direct outgrowth of transaction-processing systems. Management information systems helped to organize and output information in more useful forms than TPS.
MISs generate three types of reports:
Detail Report. Lists transactions that occur during a certain period.
Summary Report. Consolidates detailed data and usually includes charts or graphs.
Exception Report. Lists unusual conditions or items that need attention by the system users.
Decision Support System (DSS)
Managers use decision support systems (DSSs) to develop solutions for specific problems. With a DSS, managers can make queries of data to evaluate the impact of a decision before it is implemented. A DSS not only uses data from databases and date warehouses but also enables users to add their own insights and experiences and apply them to the solution. Many data-related systems work together to provide the user of the DSS with a broad base of information.
Decision support systems get their data from internal and external sources as well as demographic data purchased from third-party providers, mailing lists, or government-provided statistics (such as census data).
There are several types of decision support systems.
Model management system. Software that assists in building management models. A management model is an analysis tool that is used in decision making. Model management systems typically contain financial and statistical analysis tools.
Knowledge-based system. Provides intelligence that supplements a user’s intellect to make the DSS more effective. Knowledge-based systems include expert systems that try to replicate human decision- making processes (such as a doctor in a remote location). Another type of knowledge based system is a natural language processing (NLP) system that enables users to communicate with computer systems using a natural spoken or written language rather than computer programming. Amazon Alexa is an NLP system.
Artificial intelligence. A branch of computer science that deals with the attempt to create computers that think like humans.
Databases and the models provided by model management systems tend to be analytical and mathematical. Knowledge-based systems and artificial intelligence provides opportunities to add experience by enabling the use of fuzzy logic. Fuzzy logic incorporates experiential learning by considering probabilities, thus making the algorithms used in databases and other systems more flexible and to consider a wider range of possibilities than conventional algorithms.
Business Intelligence (BI)
Business Intelligence (BI) is the ability to improve business decision making with databases and other fact-based support systems. A business intelligence system is another form of business information system and is used primarily at the executive level. It enables executives and senior managers to make informed decisions about how best to run an organization. Often business intelligence systems use information from data warehouses and data marts and can enable managers to identify new opportunities and implement effective business strategies.
An enterprise resource planning (ERP) system accumulates information from multiple departments that are relevant to running a business, such as human resources and accounting. Having such information in one database makes the management and compensation of employees more streamlined.
When organizations begin to accumulate massive amounts of data, both from internal and external sources, using data warehouses, data marts, and dating mining, these organizations can tap into a wealth of information far better than by using a single database or by using multiple unrelated databases throughout the organization.
In addition, executives and managers throughout an organization have a wide range of business information systems that can be used to make more informed decisions. Such systems include transaction-processing systems (TPS), management information systems (MIS), decision support systems (DSS), business intelligence (BI) systems, and enterprise resource planning (ERP) systems.
Cheat Sheet
A. Data warehousing
A data warehouse is a large-scale collection of data that contains and organizes in one place all the relevant data for an organization.
Data warehouses often contain information from multiple databases.
B. Data marts
Small slices of the data warehouse are created to look at data from single departments of the organization.
C. Data mining
Data mining is the process by which large amounts of data are analyzed to spot otherwise hidden trends.
Through processes such as classification, estimation, affinity grouping, clustering, and description, data is organized so that it provides meaningful information.
D. Business information systems
An information system is a software-based solution used to gather an analyze organizational information.
E. A transaction-processing system (TPS) is used to keep track of everyday business activities.
F. A decision support system (DSS) is designed to help managers develop solutions for specific problems.
A model management system is software that assists in building analysis tools for DSSs.
A knowledge-based system provides intelligence to make the DSS more effective.
G. There are several types of knowledge-based systems including expert systems, natural language systems, and artificial intelligence.
H. Business intelligence systems are used by executive managers to analyze and interpret data to make informed decisions about how to best run a business.
I. An enterprise resource planning (ERP) system is a large software system that gathers information from all parts of a business and integrates it to make it readily available for decision making.
Glossary
Artificial intelligence (AI): The branch of computer science that deals with the attempt to create computers that think like humans.
Batch processing: The accumulation of transaction data until a certain point is reached, at which time several transactions are processed at once.
Business intelligence (BI): The ability to improve business decision making with databases and other fact-based support systems.
Business intelligence system: Systems used to analyze and interpret data to enable managers to make informed decisions about how best to run a business..
Clickstream data: Software used on company websites to capture information about each click that users make as they navigate through the site.
Data mart: Small slices of a data warehouse grouped together and separated from the main body of data in the data warehouse so that related sets of data can be analyzed.
Data mining: The process by which great amounts of data are analyzed and investigated. The objective is to spot significant patterns or trends within the data that would otherwise not be obvious.
Data staging: The act of formatting data from source databases before fitting it into a data warehouse; it consists of three steps: extraction, transformation, and storage.
Data warehouse: A large-scale collection of data that contains and organizes in one place all the data from an organization’s multiple databases.
Decision support system (DSS): A type of business intelligence system designed to help managers develop solutions for specific problems.
Detail report: A report generated by a management information system that provides a list of the transactions that occurred during a certain period.
Enterprise resource planning (ERP) system: A business intelligence system that accumulates in a central location all information relevant to running a business and makes it readily available to whoever needs it to make decisions.
Exception report: A report generated by a management information system that shows conditions that are unusual or that need attention by system users.
Expert system: A system that tries to replicate the decision-making processes of human experts to solve specific problems.
Fuzzy logic: Enables the interjection of experiential learning into knowledge-based systems by allowing the consideration of probabilities.
Information system: A system that includes data, people, procedures, hardware, and software that help in planning and decision making; a software-based solution used to gather and analyze information.
Knowledge-based system: A business intelligence system that provides intelligence that supplements the user’s own intellect and makes the decision support system more effective.
Management information system (MIS): A type of business intelligence system that provides timely and accurate information that enables managers to make critical business decisions.
Model management system: Software that assists in building management models in decision support systems; an analysis tool that, with internal and external data, provides a view of a particular business situation for the purposes of decision making.
Natural language processing (NLP) system: A knowledge-based business intelligence system that enables users to communicate with computer systems using a natural spoken or written language instead of a computer programming language.
Online analytical processing (OLAP): Software that provides standardized tools for viewing and manipulating data in a data warehouse.
Online transaction processing (OLTP): The real-time processing of database transactions online.
Real-time processing: The processing of database transactions in which the database is updated while the transaction is taking place.
Summary report: A report generated by a management information system that provides a consolidated picture of detailed data; these reports usually include some calculation or visual displays of information.
Time-variant data: Data that doesn’t pertain to one time period.
Transaction-processing system (TPS): A type of business intelligence system for keeping track of everyday business activities.