Chapter 7 – Databases and Data Warehouses
• Data are raw facts that describe the characteristics of an event. Characteristics for a sales event can include a date, item number, item description, quantity
ordered, customer name, or shipping details.
• Information is data converted into a meaningful and useful context. Information from sales events could include the best-selling item, the worst-selling item,
the best customer, or the worst customer.
Organizational Data and Information
• Organizational data and information come at different levels, formats, and “granularities.”
• Granularity refers to the extent of detail within the data and information (fine and detailed vs. coarse and abstract). On one end of the spectrum is coarse
granularity, or highly summarized data or information. At the other end is fine granularity, or data or information that contains a great amount of detail. If
employees are using a supply chain management system to make decisions, they might find that their suppliers are sending data and information in different
formats and granularities, and at different levels.
• Successfully collecting, compiling, sorting, and finally analyzing data and information from multiple levels, in varied formats, exhibiting different granularities
can provide tremendous insight into how an organization is performing
The Value of Transactional Data and Analytical Information
• Transactional data encompasses all of the data contained within a single business process or unit of work, and its primary purpose is to support performing
daily operational tasks
• Analytical information, on the other hand, encompasses all organizational information, and its primary purpose is to support the performance of higher-level
analysis tasks. Analytical information is used when making important ad hoc decisions such as whether the organization should build a new manufacturing
plant or hire additional sales personnel
Value of Timely Data and Information
• Real-time data are immediate, up-to-date data
• Real-time information is immediate, up-to-date information
• Real-time system provides real-time transactional data and/or real-time analytical information in response to query requests
• The growing demand for real-time data and information stems from organizations' need to make faster and more effective decisions, keep smaller
inventories, operate more efficiently, and track performance more carefully.
• Data and information also need to be timely so that they meet employees' needs—but no more. If employees can absorb data or information only on an
hourly or daily basis, there is no need to gather real-time data or information in smaller increments.
• Most people request real-time data and information without understanding one of the biggest pitfalls associated with real-time data and information—
continual change. Continual change can cause confusion in communication because it needs to be checked all the time. So this approach might not speed
up decision making but will slow it down.
The value of Quality Data and Information
• Business decisions are only as good as the quality of the data and information used to make the decisions. •
• Using the wrong data or information can lead to making the wrong decision. Making the wrong decision can cost time, money, and even reputations.
• Bad data or information can cause serious business ramifications such as:
o Inability to accurately track customers
o Difficulty identifying the organization's most valuable customers
o Inability to build strong relationships with customers, which increases their buyer power
• Quality data and information do not automatically guarantee that every decision made is going to be a good one, since people ultimately make decisions. But
high-quality data and information ensure that the basis of the decisions is accurate.
Storing Transactional Data
• They are stored in a database.
• Operational-based information systems, such as SCM and CRM systems, access and maintain transactional data stored in databases
• Records retrieved from such databases can be used to provide answers to questions and to help make decisions.
• The computer program used to manage and query a database is known as a database management system (DBMS)
• Typically, a given database has a structural description of the type of raw facts held in that database: This description is known as a schema.
• The schema describes the objects that are represented in the database and the relationships among them.
• The schema describes the objects that are represented in the database and the relationships among them. There are a number of different ways of
organizing a schema; that is, of modelling the database structure. These are known as database models (or data models). The most commonly used model
today is the relational model, which represents all data in the form of multiple related tables each consisting of rows and columns. This model represents
relationships by using values common to more than one table. Other models, such as the hierarchical model and the network model, use a more explicit
representation of relationships.
Relational Database Fundamentals
• Database maintains data about various types of objects (e.g., inventory), events (e.g., transactions), people (e.g., employees), and places (e.g., warehouses)
• hierarchical database model, data are organized into a tree-like structure that allows repeating data using parent/child relationships, in such a way that it
cannot have too many relationships. Hierarchical structures were widely used in the first mainframe database management systems. However, owing to their
restrictions, hierarchical structures often cannot be used to relate to structures that exist in the real world
• Network database model is a flexible way of representing objects and their relationships. Where the hierarchical model structures data as a tree of records,
with each record having one parent record and many children, the network model allows each record to have multiple parent and child records, forming a
lattice structure • Relational database model is a type of database that stores data in the form of logically related two-dimensional tables. This text focuses on the relational
database model because it is the most prevalent form of database structure used in business.
Entities, Entity Classes, and Attributes
• Entity is a person, place, thing, transaction, or event about which data are stored.
• Entity class (often called a table) is a collection of similar entities. The entity classes of interest are CUSTOMER, ORDER, ORDER LINE, PRODUCT, and
DISTRIBUTOR. Notice that each entity class (the collection of similar entities) is stored in a different two-dimensional table
• Attributes, also called fields or columns, are characteristics or properties of an entity class. The attributes for CUSTOMER include Customer ID, Customer
Name, Contact Name, and Phone. Attributes for PRODUCT include Product ID, Product Description, and Price.
Keys and Relationships
• primary key is a field (or group of fields) that uniquely identifies a given entity in a table. In CUSTOMER, the Customer IDuniquely identifies each entity
(customer) in the table and is the primary key. Primary keys are important because they provide a way of distinguishing each entity in a table.
• foreign key in the relational database model is a primary key of one table that appears as an attribute in another table and acts to provide a logical
relationship between the two tables.
Databases give us many advantages like: flexibility, scalability and performance, reduced data redundancy, data integrity, data security
• Databases provide flexibility in allowing each user to access the data in whatever way best suits his or her needs.
• physical view of data deals with the physical storage of data on a storage device such as a hard disk
• logical view of data focuses on how users logically access data to meet their particular business needs
• A database has only one physical view, it can easily support multiple logical views. One user might want a customer report presented in alphabetical format,
in which case last name appears before first name. Another user might want customer names appearing as first name and then last name. Both are easily
achievable, but are different logical views of the same physical data.
Increased Scalability and Performance
• Scalability refers to how well a system can adapt to increased demands
• Performance measures how quickly a system performs a certain process or transaction
Reduced Data Redundancy
• Data redundancy is the duplication of data, or storing the same data in multiple places. Redundant data occurs because organizations frequently capture
and store the same data in multiple locations.
• The primary problem with redundant data is that it is often inconsistent, making it difficult to determine which values are the most current or most accurate
• Eliminating data redundancy saves space, makes performing updates easier, and improves quality.
Increased Data Integrity
• Data integrity is a measure of the quality of data
• integrity constraints are rules that help ensure the quality of data. Integrity constraints are defined and built into the database
• The database (more appropriately, the DBMS) ensures that users can never violate these constraints. There are two types of integrity constraints: (1)
relational integrity constraints, and (2) business-critical integrity constraints. • Relational integrity constraints are rules that enforce basic and fundamental data constraints. For example, a referential integrity constraint would not allow
someone to create an order for a non-existent customer, provide a mark-up percentage that was negative, or order zero pounds of raw materials from a
• Business-critical integrity constraints enforce business rules vital to an organization's success and often require more insight and knowledge than
relational integrity constraints.
Increased Data Security
• Databases offer many security features, including passwords, access levels, and access controls.
Database management system (DBMS) is software through which users and application programs interact with a database.
Data Driven Websites
• The pages on a Web site must change according to what a site visitor is interested in browsing
• A visitor to the Web site selects Porsche, enters the price range he or she is interested in, and clicks “Go.” The visitor is presented with information on
available cars within the price range and an invitation to purchase or request more information from the company
• Data-driven Web site is an interactive Web site kept constantly updated and relevant to customer needs by using a database. Data-driven Web sites are
especially useful when the site offers a great deal of information, products, or services. Web site visitors are frequently angered if they are buried under an
avalanche of information when searching a site
Data-driven Web Site Business Advantages
Integrating Data among Multiple Databases
• integration allows separate systems to communicate directly with each other • Without integrations, an organization will (1) spend considerable time entering the same data in multiple systems, and (2) suffer from the low quality and
inconsistency typically embedded in redundant data.
• most integrations do not eliminate redundant data, they can ensure its consistency across multiple systems
• Two integration methods: first is to create forward and backward integrations that link processes (and their underlying databases) in the value chain. The
second integration method builds a central data repository
• forward integration takes data entered into a given system and sends it automatically to all downstream systems and processes
• backward integration takes data entered into a given system and sends it automatically to all upstream systems and processes