0% found this document useful (0 votes)

8 views513 pages

Veri Yönetimi

Chapter 1 of 'Modern Database Management' introduces key concepts in database management, including definitions of databases, data, and information, as well as the advantages and disadvantages of file processing. It outlines the database approach, emphasizing the importance of data models, entities, and relationships, while also discussing the components of the database environment and the database development process. The chapter concludes with an overview of the evolution of database systems and the various types of enterprise applications.

Uploaded by

kutayoyunda768

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

8 views513 pages

Veri Yönetimi

Uploaded by

kutayoyunda768

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

Modern Database Management

Thirteenth Edition

Chapter 1
The Database Environment
and Development Process

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Definitions
• Database: organized collection of logically related data
• Data: stored representations of meaningful objects and
events
– Structured: numbers, text, dates
– Unstructured: images, video, documents
• Information: data processed to increase knowledge in
the person using the data
• Metadata: data that describes the properties and context
of user data

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 1-1 Converting Data to
Information (1 of 2)
(a) Data in context

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 1-1 Converting Data to
Information (2 of 2)
(b) Summarized data

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Table 1-1 Example Metadata for Class
Roster
• Descriptions of the properties or characteristics of the data, including
data types, field sizes, allowable values, and data context

Data Item Data Item Data Item Metadata Metadata Metadata Metadata
Name Type Length Min imum Maximum Description Source
Blank Blank

Course Alphanumeric 30 Course ID and Academic Unit

name
Section Integer 1 1 9 Section number Registrar
Blank Blank

Semester Alphanumeric 10 Semester and year Registrar

Blank Blank

Name Alphanumeric 30 Student name Student IS

Blank Blank

ID Integer 9 Student ID (SSN) Student IS

Blank Blank

Major Alphanumeric 4 Student major Student IS

GPA Decimal 3 0.0 4.0 Student grade Academic Unit

point average

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Disadvantages of File Processing
• Program-Data Dependence
– All programs maintain metadata for each file they use
• Duplication of Data
– Different systems/programs have separate copies of the same
data
• Limited Data Sharing
– No centralized control of data
• Lengthy Development Times
– Programmers must design their own file formats
• Excessive Program Maintenance
– 80% of information systems budget
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 1-2 Old File Processing Systems
at Pine Valley Furniture Company

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
The Database Approach (1 of 2)
• Data models
– Graphical diagram capturing nature and relationship of data
– Enterprise Data Model – high-level entities and relationships for the
organization
– Project Data Model – more detailed view, matching data structure in
database or data warehouse

• Entities
– Noun form describing a person, place, object, event, or concept
– Composed of attributes

• Relationships
– Between entities
– Usually one-to-many (1: N) or many-to-many (M : N), but can also be
one-to-one (1:1)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 1-3 Comparison of Enterprise-and
Project-Level Data Models
(a) Segment of an enterprise (b) Segment of a project data model
data model

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
The Database Approach (2 of 2)
• Relational Databases
• Database technology
involving tables (relations)
representing entities and
primary/foreign keys
representing relationships
(see Figure 1-17)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Advantages of the Database Approach
• Program-data independence

• Planned data redundancy

• Improved data consistency

• Improved data sharing

• Increased application development productivity

• Enforcement of standards

• Improved data quality

• Improved data accessibility and responsiveness

• Reduced program maintenance

• Improved decision support

• New, specialized personnel

• Installation and management cost and complexity
• Conversion costs
• Need for explicit backup and recovery
• Organizational conflict

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Components of the Database
Environment
• Data modeling and design tools – automated tools used to design databases and
application programs

• Repository – centralized storehouse of metadata

• Database Management System (DBMS) – software for managing the database

• Database – storehouse of the data

• Application Programs – software using the data

• User Interface – text, graphical displays, menus, etc. for user

• Data/Database Administrators – personnel responsible for maintaining the

database

• System Developers – personnel responsible for designing databases and software

• End Users – people who use the applications and databases

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 1-6 Components of the Database
Environment

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
The Database Development Process
• SDLC
– System Development Life Cycle
– Detailed, well-planned development process
– Time-consuming, but comprehensive
– Long development cycle
• Prototyping
– Rapid application development (RAD)
– Cursory attempt at conceptual data modeling
– Define database during development of initial prototype
– Repeat implementation and maintenance activities with
new prototype versions
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Systems Development Life Cycle (SDLC)
• The traditional methodology used to develop, maintain, and replace
information systems
• Five main steps:

1. Planning – preliminary understanding of business situation.

Enterprise model and conceptual data modeling.
2. Analysis – thorough analysis of business situation, leading to
functional requirements. Detailed conceptual data modeling.
3. Design – logical and physical database design, to develop
technology and organization.
4. Implementation – writing programs, building databases, testing,
installing, training, and documenting.
5. Maintenance – monitoring, repairing, and enhancing.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 1-8 Database Development Activities During
the Systems Development Life Cycle (SDLC)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Alternative Information Systems
Development Approaches

• Traditional SDLC: methodical, structured, and time

consuming
• Rapid Application Development (RAD): faster and more
adaptive, especially when a database is already in place
• Several flavors:
– Prototyping
– Agile methodologies
– eXtreme programming
– Scrum
– DSDM (dynamic system development methodologies)
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 1-9 The Prototyping Methodology
and Database Development Process

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 1-10 Three-Schema Architecture
• Different people have different views
of the database

• External Schema
– User Views
– Subsets of Conceptual Schema
– Can be determined from
business-function/data entity
matrices
– DBA determines schema for
different users

• Conceptual Schema (ER models)

• Internal Schema (logical and physical

structures)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Managing People Involved in Database
Development

• Project – a planned undertaking of related activities to

reach an objective that has a beginning and an end
• Initiated and planned in planning stage of SDLC
• Executed during analysis, design, and implementation
• Closed at the end of implementation

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Database Project Team Members (1 of 2)
• Business analysts – analyze business situation and
establish requirements
• Systems analysts – like business analysts, but also have
technical expertise for overall information systems
• Database analysts and data modelers – analysts who
focus on database
• Users – the “customers” communicate their needs to
analysts
• Programmers – coders of the programs that interact with
the database
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Database Project Team Members (2 of 2)
• Database architects – establish standards for data in
business units
• Data administrators – responsible for existing databases,
ensuring data integrity and consistency
• Project managers – oversee the projects, manage the
personnel
• Other technical experts – network, operating system,
documentation, etc.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Evolution of Database Systems
• Driven by four main objectives:
– Need for program-data independence in order to
reduce maintenance
– Desire to manage more complex data types and
structures
– Ease of data access for less technical personnel
– Need for more powerful decision support platforms

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 1-11 The Range of Database
Technologies: Past and Present (1 of 4)
(a) Evolution of database technologies

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 1-11 The Range of Database
Technologies: Past and Present (2 of 4)
(b) Database architectures

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 1-11 The Range of Database
Technologies: Past and Present (3 of 4)
(b) Database architectures

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 1-11 The Range of Database
Technologies: Past and Present (4 of 4)
(b) Database architectures

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
The Range of Database Applications
• Personal Databases
– Typical size in the megabytes
– Intended for one user
• Departmental Multi-Tiered Client/Server Databases
– Typical size in the gigabytes
– Intended for several users, usually doesn’t exceed 100,
department-wide
• Enterprise Applications
– Typical size in the gigabytes, terabytes, or even petabytes
– Intended for a very large user base, company wide

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 1-12 Multi-Tiered Client/Server
Database Architecture

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Types of Enterprise Applications
• Enterprise Systems (typically involve relational databases)
– Backbone of an organization
– Enterprise resource planning (ERP)
– Customer relationship management
– Supply chain management
– Human resource management and payroll

• Data Warehouses (typically involve relational databases)

– Integrates data from multiple data sources
– Maintain historical data
– Help identify patterns and trends

• Data Lakes (often don’t involve relational databases)

– Large integrated repository for internal and external data that does not
follow a predefined schema
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 1-13 An Example of an Executive
Dashboard

( [Link] )
Courtesy Tableau Software

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Pine Valley Furniture
• PVF is a fictional furniture company
• Will be used as a case throughout the book
• Chapter 1 topics include
– Database Evolution at Pine Valley Furniture Company
– Project planning
– Analyzing database requirements
– Designing the database
– Using the database
– Administering the database
– Future of databases at PVF

Chapter 2
Modeling Data in the
Organization

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
E-R Model Constructs
• Entities:
– Entity instance – person, place, object, event, concept (often
corresponds to a row in a table)
– Entity Type – collection of entities (often corresponds to a table)
• Relationships:
– Relationship instance – link between entities (corresponds to
primary key–foreign key equivalencies in related tables)
– Relationship type – category of relationship; link between entity
types
• Attributes:
– Properties or characteristics of an entity or relationship type
(often corresponds to a field in a table)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Business Rules
• Are statements that define or constrain some aspect of
the business
• Are derived from policies, procedures, events, functions
• Assert business structure
• Control/influence business behavior
• Are expressed in terms familiar to end users
• Are automated through DBMS software

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
A Good Business Rule Is
• Declarative – what, not how
• Precise – clear, agreed-upon meaning
• Atomic – one statement
• Consistent – internally and externally
• Expressible – structured, natural language
• Distinct – non-redundant
• Business-oriented – understood by business people

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
A Good Data Name Is
• Related to business, not technical, characteristics
• Meaningful and self-documenting
• Unique
• Readable
• Composed of words from an approved list
• Repeatable
• Written in standard syntax

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Definitions
• Explanation of a term or fact
– Term – word or phrase with specific meaning
– Fact – association between two or more terms
• Guidelines for good data definition
– A concise description of essential data meaning
– Gathered in conjunction with systems requirements
– Accompanied by diagrams
– Achieved by consensus and iteratively refined

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Entities
• Entity
– a person, a place, an object, an event, or a concept in
the user environment about which the organization
wishes to maintain data
• Entity type
– a collection of entities that share common properties
or characteristics
• Entity instance
– a single occurrence of an entity type

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Entity Type and Entity Instances
Figure 2-3 Entity type EMPLOYEE with two instances

Entity type: EMPLOYEE

Attributes Attribute Data Type Example Instance Example Instance

Employee Number Char (10) 64217836 53410197
Name Char (25) Michelle Brady David Johnson
Address Char (30) 100 Pacific Avenue 450 Redwood Drive
City Char (20) San Francisco Redwood City
State Char (2) CA CA
Zip Code Char (9) 98173 97142
Date Hired Date 03-21-1992 08-16-1994
Birth Date Date 06-19-1968 09-04-1975

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
An Entity…
• Should Be:
– An object that will have many instances in the
database
– An object that will be composed of multiple attributes
– An object that we are trying to model
• Should Not Be:
– A user of the database system
– An output of the database system (e.g., a report)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-4 Example of Inappropriate
Entities
(a) System user
(Treasurer) and output
(Expense Report)
shown as entities

b) E-R diagram with only

the necessary entities

Relationships
• Strong entity
– exists independently of other types of entities
– has its own unique identifier
▪ identifier underlined with single line
• Weak entity
– dependent on a strong entity (identifying owner); cannot
exist on its own
– does not have a unique identifier (only a partial identifier)
– entity box and partial identifier have double lines
• Identifying relationship
– links strong entities to weak entities
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-5 Example of a Weak Entity and
Its Identifying Relationship

Weak entity with identifying relationship.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Guidelines for Naming Entities
• Singular noun
• Specific to organization
• Concise, or abbreviation
• For event entities, the result not the process
• Name consistent for all diagrams

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Guidelines for Defining Entities
• “An X is…”
• Describe unique characteristics of each instance
• Explicit about what is and is not the entity
• When an instance is created or destroyed
• Changes to other entity types
• History that should be kept

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Attributes
• Attribute – property or characteristic of an entity or
relationship type
• Classifications of attributes:
– Required versus Optional
– Simple versus Composite
– Single-Valued versus Multivalued
– Stored versus Derived
– Identifier

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-6 Entity Type STUDENT With
Required and Optional Attributes
Entity type: STUDENT

Attributes Attribute Data Type Required or Optional Example Instance Example Instance

Student ID Char (10) Required 28-618411 26-844576

Student Name Char (40) Required Michael Grant Melissa Kraft

Home Address Char (30) Required 314 Baker St reet 1422 Heft Ave

Home City Char (20) Required Centerville Miami

Home State Char (2) Required OH FL

Home Zip Code Char (9) Required 45459 33321

Blank

Major Char (3) Optional MIS

Required – must have a value for every entity (or relationship) instance with which it is
associated
Optional – may not have a value for every entity (or relationship) instance with which it
is associated
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-7 A Composite Attribute
• Composite attribute – An attribute that has meaningful
component parts (sub-attributes)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-8 Entity With Multivalued Attribute
(Skill) and Derived Attribute (Years Employed)

• Multivalued
– May take on more than one value for a given entity (or relationship)
instance
– An employee can have more than one skill

• Derived
– Values can be calculated from related attribute values (not physically
stored in the database)
– Years employed calculated from date employed and current date

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Identifiers (Keys)
• Identifier (Key) – an attribute (or combination of
attributes) that uniquely identifies individual instances of
an entity type
• Simple versus Composite Identifier
• Candidate Identifier – an attribute that could be an
identifier; it satisfies the requirements for being an
identifier

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Criteria for Identifiers
• Choose Identifiers that
– Will not change in value
– Will not be null
• Avoid intelligent identifiers (e.g., containing locations or
people that might change)
• Substitute new, simple keys for long, composite keys

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-9 Simple and Composite
Identifier Attributes
a) Simple identifier attribute

b) Composite identifier attribute

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Naming Attributes
• Name should be a singular noun or noun phrase
• Name should be unique
• Name should follow a standard format
• Similar attributes of different entity types should use the
same qualifiers and classes

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Defining Attributes (1 of 2)
• State what the attribute is and possibly why it is
important
• Make it clear what is and is not included in the
attribute’s value
• Include aliases in documentation

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Defining Attributes (2 of 2)
• State source of values
• State whether attribute value can change once set
• Specify whether required or optional
• State min and max number of occurrences allowed
• Indicate relationships with other attributes

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Modeling Relationships
• Relationship Types v s Relationship Instances
ersu

– The relationship type is modeled as lines between

entity types. The relationship instance is between
specific entity instances
• Relationships can have attributes
– These describe features pertaining to the association
between the entities in the relationship
• Two entities can have more than one type of relationship
between them (multiple relationships)
• Associative Entity – combination of relationship and entity

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-10 Relationship Type and
Instances
(a) Relationship
type (Completes)

b) Relationship
instances

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Degree of Relationships
• Degree of a relationship is the number of entity types
that participate in it
– Unary Relationship
– Binary Relationship
– Ternary Relationship

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
From Figure 2-2 Degrees of
Relationships
Unary – entities of the same entity type related to each other
Binary – entities of one type related to entities of another
Ternary – entities of three different types involved in the same
relationship

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Cardinality of Relationships
• One-to-One
– Each entity in the relationship will have exactly one
related entity
• One-to-Many
– An entity on one side of the relationship can have
many related entities, but an entity on the other side
will have a maximum of one related entity
• Many-to-Many
– Entities on both sides of the relationship can have
many related entities on the other side

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-12 Examples of Relationships of
Different Degrees (1 of 3)

a) Unary relationships

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-12 Examples of Relationships of
Different Degrees (2 of 3)

b) Binary relationships

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-12 Examples of Relationships of
Different Degrees (3 of 3)

c) Ternary relationship

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-15 Using Relationships and
Entities to Link Related Attributes
Multivalued attributes can be represented as relationships

(a) Multivalued attribute versus relationships via bill-of-materials structure

(b) Composite, multivalued attribute versus relationship

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Cardinality Constraints
• Cardinality Constraints — the number of instances of one
entity that can or must be associated with each instance
of another entity
• Minimum Cardinality
– If zero, then optional
– If one or more, then mandatory
• Maximum Cardinality
– The maximum number

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-17 Examples of Cardinality
Constraints (1 of 3)

a) Mandatory cardinalities

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-17 Examples of Cardinality
Constraints (2 of 3)

(b) One optional, one mandatory cardinality

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-17 Examples of Cardinality
Constraints (3 of 3)

c) Optional cardinalities

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-21 Examples of Multiple
Relationships (1 of 2)

a) Employees and departments

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-21 Examples of Multiple
Relationships (2 of 2)

b) Professors and courses (fixed lower limit constraint)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Associative Entities
• When should a relationship with attributes instead be an
associative entity?
– All relationships for the associative entity should be many
– The associative entity could have meaning independent of
the other entities
– The associative entity preferably has a unique identifier,
and should also have other attributes
– The associative entity may participate in other
relationships other than the entities of the associated
relationship
– Convert ternary relationships to associative entities

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-11 An Associative Entity
An associative entity is an entity. It has attributes. It is also a relationship. It serves to link
other entities together in a many-to-many relationship.

(a) Attribute on a relationship

(b) An associative entity (Certificate)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-13 Representing a Bill-of-Materials
Structure (1 of 2)
Two ways to represent a bill-of-materials structure

(a) Many-to-many relationship

(c) Associative entity

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-13 Representing a Bill-of-Materials
Structure (2 of 2)
A bill-of-materials is a hierarchy of items. Here, bikes are composed of handle
bars, transmissions, wheels, etc. Transmissions include their own sub-parts,
brakes and derailers.

(b) Two Item bill-of-materials structure instances

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-18 Cardinality Constraints in a
Ternary Relationship
The three relationships are marked by their mandatory or optional constraints.
#1 states that a vendor may or may not supply parts to warehouses. #2 states
that each part must come from a vendor and go to a warehouse. #3 says a
warehouse must have at least one part from a vendor.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-19 Simple Example of Time
Stamping
Time stamp – a time value that is associated with a data value, often indicating
when some event occurred that affected the data value.
The Price History attribute is both multivalued and composite.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 2-22 Data Model for Pine Valley Furniture
Company in Microsoft Visio Notation

Different modeling
software tools may have
different notation for the
same constructs.

Chapter 3
The Enhanced E-R Model

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Supertypes and Subtypes
• Enhanced E-R (EER) model: extends original E-R model with
new modeling constructs
• Subtype: A subgrouping of the entities in an entity type that
has attributes distinct from those in other subgroupings
• Supertype: A generic entity type that has a relationship with
one or more subtypes
• Attribute Inheritance:
– Subtype entities inherit values of all attributes and
relationships of the supertype
– An instance of a subtype is also an instance of the
supertype

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 3-1 Basic Notation for
Supertype/Subtype Relationships (1 of 2)

a) EER notation

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 3-1 Basic Notation for
Supertype/Subtype Relationships (2 of 2)

b) Microsoft Visio Notation

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 3-2 Employee Supertype with
Three Subtypes

All employee subtypes will have employee number, name,

address, and date hired
Each employee subtype will also have its own attributes

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Relationships and Subtypes
• Relationships at the supertype level indicate that all
subtypes will participate in the relationship
• The instances of a subtype may participate in a
relationship unique to that subtype. In this situation, the
relationship is shown at the subtype level

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 3-3 Supertype/Subtype Relationships
in a Hospital

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Generalization and Specialization
• Generalization: The process of defining a more general
entity type from a set of more specialized entity types.
Bottom-Up
• Specialization: The process of defining one or more
subtypes of the supertype and forming supertype/subtype
relationships. Top-Down

a) Three entity types: Car, Truck, and Motorcycle

All these types of vehicles have common attributes

b) Generalization to Vehicle supertype

We put the shared attributes in a supertype. Note: no
subtype for motorcycle, since it has no unique attributes

a) Entity type Part

Note: Routing Number only applies if part is manufactured
in house.
Supplier only applies if part is purchased from a supplier.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 3-5 Example of Specialization (2 of 2)
b) Specialization to Manufactured Part and Purchased Part
Multivalued composite attribute replaced by associative entity
relationship to another entity

• Completeness Constraints: Whether an instance of a

supertype must also be a member of at least one
subtype
– Total Specialization Rule: Yes (double line)
– Partial Specialization Rule: No (single line)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 3-6 Examples of Completeness
Constraints (1 of 2)

a) Total specialization rule

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 3-6 Examples of Completeness
Constraints (2 of 2)

b) Partial specialization rule

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Constraints in Supertype/Subtype
Relationships (1 of 2)

• Disjointness Constraints: Whether an instance of a

supertype may simultaneously be a member of two (or
more) subtypes
– Disjoint Rule: An instance of the supertype can be
only One of the subtypes
– Overlap Rule: An instance of the supertype could be
more than one of the subtypes

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 3-7 Examples of Disjointness
Constraints (1 of 2)

a) Disjoint rule

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 3-7 Examples of Disjointness
Constraints (2 of 2)

b) Overlap rule

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Constraints in Supertype/Subtype
Relationships (2 of 2)

• Subtype Discriminator: An attribute of the supertype

whose values determine the target subtype(s)
– Disjoint – a simple attribute with alternative values to
indicate the possible subtypes
– Overlapping – a composite attribute whose subparts
pertain to different subtypes. Each subpart contains a
Boolean value to indicate whether or not the instance
belongs to the associated subtype

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 3-8 Introducing a Subtype
Discriminator (Disjoint Rule)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 3-10 Example of Supertype/Subtype
Hierarchy

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Entity Clusters
• EER diagrams are difficult to read when there are too
many entities and relationships.
• Solution: Group entities and relationships into entity
clusters.
• Entity cluster: Set of one or more entity types and
associated relationships grouped into a single abstract
entity type

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 3-13 Entity Clustering for Pine
Valley Furniture Company (1 of 2)

a) Possible entity clusters

(using Microsoft Visio)
Related groups of entities
could become clusters

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 3-13 Entity Clustering for Pine
Valley Furniture Company (2 of 2)

b) EER diagram for entity clusters (using Microsoft Visio)

Detail for a single cluster

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Packaged Data Models
• Predefined data models
• Could be universal or industry-specific
• Universal data model = a generic or template data model
that can be reused as a starting point for a data modeling
project (also called a “pattern”)

• Use proven model components

• Save time and cost
• Less likelihood of data model errors
• Easier to evolve and modify over time
• Aid in requirements determination
• Easier to read
• Supertype/subtype hierarchies promote reuse

• Many-to-many relationships enhance model flexibility

• Vendor-supplied data model fosters integration with
vendor’s applications
• Universal models support interorganizational systems

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 3-15 Party, Party Role, and Role Type in
a Universal Data Model (1 of 2)

a) Basic Party universal data model

Packaged data models are generic models that can be customized for a
particular organization’s business rules.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 3-15 Party, Party Role, and Role Type in
a Universal Data Model (2 of 2)

b) Party supertype/subtype hierarchy

Chapter 4
Logical Database Design
and the Relational Model

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Components of Relational Model
• Data structure
– Tables (relations), rows, columns
• Data manipulation
– Powerful SQL operations for retrieving and modifying
data
• Data integrity
– Mechanisms for implementing business rules that
maintain integrity of manipulated data

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Relation
• A relation is a named, two-dimensional table of data.

• Consists of rows (records) and columns (attribute or field)

• Requirements for a table to qualify as a relation:

– It must have a unique name.
– Every attribute value must be atomic (not multivalued, not composite).
– Every row must be unique (can’t have two rows with exactly the same
values for all their fields).
– Attributes (columns) in tables must have unique names.
– The order of the columns must be irrelevant.
– The order of the rows must be irrelevant.

Note: All relations are in 1st Normal form.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Correspondence with E-R Model
• Relations (tables) correspond with entity types and with
many-to-many relationship types.
• Rows correspond with entity instances and with many-to-
many relationship instances.
• Columns correspond with attributes.
• Note: The word relation (in relational database) is not
the same as the word relationship (in E-R model).

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Key Fields
• Keys are special fields that serve two main purposes:
– Primary keys are unique identifiers of the relation.
Examples include employee numbers, social security
numbers, etc. This guarantees that all rows are unique.
– Foreign keys are identifiers that enable a dependent
relation (on the many side of a relationship) to refer to its
parent relation (on the one side of the relationship).
• Keys can be simple (a single field) or composite (more than
one field).
• Keys are usually used as indexes to speed up the response to
user queries.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-3 Schema for Four Relations
(Pine Valley Furniture Company

(a) EER notation

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Integrity Constraints (1 of 2)
• Domain Constraints
– Allowable values for an attribute (includes data types
and restrictions on values)
• Entity Integrity
– No primary key attribute may be null. All primary key
fields Must contain data values.
• Referential Integrity
– Rules that maintain consistency between the rows of
two related tables.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Integrity Constraints (2 of 2)
• Referential Integrity – rule states that any foreign key value (on
the relation of the many side) MUST match a primary key value
in the relation of the one side.(Or the foreign key can be null.)
– For example: Delete Rules
▪ Restrict – don’t allow delete of “parent” side if related
rows exist in “dependent” side
▪ Cascade – automatically delete “dependent” side rows
that correspond with the “parent” side row to be deleted
▪ Set-to-Null – set the foreign key in the dependent side

to null if deleting from the parent side → not

allowed for weak entities
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-5 Referential Integrity Constraints
(Pine Valley Furniture Company)

Referential integrity constraints are drawn via arrows from

dependent to parent table

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-6 SQL Table Definitions
Referential integrity constraints are implemented with foreign key to
primary key references.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Transforming EER Diagrams into
Relations (1 of 7)

• Mapping Regular Entities to Relations

– Simple attributes: E-R attributes map directly onto the
relation
– Composite attributes: Use only their simple,
component attributes
– Multivalued attributes: Become a separate relation
with a foreign key taken from the superior entity

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-8 Example of Mapping a
Regular Entity
(a) CUSTOMER entity type

(b) CUSTOMER relation

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-9 Example of Mapping a
Composite Attribute
(a) CUSTOMER entity type with composite attribute

(b) CUSTOMER relation with address detail

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-10 Example of Mapping an
Entity with a Multivalued Attribute
(a) EMPLOYEE entity type with multivalued attribute

(b) EMPLOYEE and EMPLOYEE SKILL relations

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Transforming EER Diagrams into
Relations (2 of 7)

• Mapping Weak Entities

– Becomes a separate relation with a foreign key taken
from the superior entity
– Primary key composed of:
▪ Partial identifier of weak entity
▪ Primary key of identifying relation (strong entity)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-11 Example of Mapping a
Weak Entity
(a) Weak entity DEPENDENT

(b) Relations resulting from weak entity

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Transforming EER Diagrams into
Relations (3 of 7)

• Mapping Binary Relationships

– One-to-Many – Primary key on the one side
becomes a foreign key on the many side
– Many-to-Many – Create a new relation with the
primary keys of the two entities as its primary key
– One-to-One – Primary key on mandatory side
becomes a foreign key on optional side

Relationship
(a) Relationship between CUSTOMER and ORDER entities

(b) CUSTOMER and ORDER relations with a foreign key in ORDER

Relationship
(a) Completes relationship (M : N )

(b) Three resulting relations

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-14 Example of Mapping a
Binary 1:1 Relationship
1 colon 1

(a) In Charge relationship (binary 1: 1)

(b) Resulting relations

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Transforming EER Diagrams into
Relations (4 of 7)
• Mapping Associative Entities
– Identifier Not Assigned
▪ Default primary key for the association relation is
composed of the primary keys of the two entities

(as in M : N relationship)

– Identifier Assigned
▪ It is natural and familiar to end-users
▪ Default identifier may not be unique

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-15 Example of Mapping an
Associative Entity (1 of 2)
(a) An associative entity

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-15 Example of Mapping an
Associative Entity (2 of 2)
(b) Three resulting relations

Composite primary key formed from the two foreign keys

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-16 Example of Mapping an
Associative Entity with an Identifier (1 of 2)
(a) SHIPMENT associative entity

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-16 Example of Mapping an
Associative Entity with an Identifier (2 of 2)
(b) Three resulting relations

Primary key differs from foreign keys

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Transforming EER Diagrams into
Relations (5 of 7)

• Mapping Unary Relationships

– One-to-Many – Recursive foreign key in the same
relation
– Many-to-Many – Two relations:
▪ One for the entity type
▪ One for an associative relation in which the
primary key has two attributes, both taken from
the primary key of the entity

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-17 Example of Mapping a
Unary 1: M Relationship
1 colon N

(a) EMPLOYEE entity with unary relationship

(b) EMPLOYEE relation with recursive foreign key

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-18 Example of Mapping a
Unary M : N Relationship
M colon N

(a) Bill-of-materials relationship Contains (M : N )

(b) ITEM and COMPONENT relations

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Transforming EER Diagrams into
Relations (6 of 7)

• Mapping Ternary (and n-ary) Relationships

– One relation for each entity and one for the
associative entity
– Associative entity has foreign keys to each entity in
the relationship

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-19 Example of Mapping a
Ternary Relationship (1 of 2)
(a) PATIENT TREATMENT ternary relationship with associative entity

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-19 Example of Mapping a
Ternary Relationship (2 of 2)
(b) Four resulting relations

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Transforming EER Diagrams into
Relations (7 of 7)
• Mapping Supertype/Subtype Relationships
– One relation for supertype and for each subtype
– Supertype attributes (including identifier and subtype
discriminator) go into supertype relation
– Subtype attributes go into each subtype; primary key
of supertype relation also becomes primary key of
subtype relation
– 1:1 relationship established between supertype and
each subtype, with supertype as primary table

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-21 Example of Mapping
Supertype/Subtype Relationships to Relations

These are implemented as one-to-one relationships.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Normalization
• Primarily a tool to validate and improve a logical design
so that it satisfies certain constraints that avoid
unnecessary duplication of data
• The process of decomposing relations with anomalies to
produce smaller, well-structured relations

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Well-Structured Relations
• Relations that contain minimal data redundancy and
allow users to insert, delete, and update rows without
causing data inconsistencies
• Goal is to avoid anomalies
– Insertion Anomaly – adding new rows forces user to
create duplicate data
– Deletion Anomaly – deleting rows may cause a loss
of data that would be needed for other future rows
– Modification Anomaly – changing data in a row
forces changes to other rows because of duplication

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Example–Figure 4-2b
EMPLOYEE2
EmpID Name DeptName Salary CourseTitle DateCompleted

100 Margaret Simpson Marketing 48,000 SPSS 6/19/2018

100 Margaret Simpson Marketing 48,000 Surveys 10/7/2018

140 Alan Beeton Accounting 52,000 Tax Acc 12/8/2018

110 Chris Lucero Info Systems 43,000 Visual Basic 1/12/2018

110 Chris Lucero Info Systems 43,000 C++ 4/22/2018

Blank Blank

190 Lorenzo Davis Finance 55,000

150 Susan Martin Marketing 42,000 SPSS 6/19/2018

150 Susan Martin Marketing 42,000 Java 8/12/2018

Question: Is this a relation? Answer: Yes; unique rows and no multivalued attributes
Question: What’s the primary key? Answer: Composite — Emp ID, CourseTitle
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Anomalies in This Relation (1 of 2)
• Insertion – can’t enter a new employee without having
the employee take a class (or at least empty fields of
class information)
• Deletion – if we remove employee 140, we lose
information about the existence of a Tax Acc class
• Modification – giving a salary increase to employee 100
forces us to update multiple records

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Functional Dependencies and Keys
• Functional Dependency: The value of one attribute (the
determinant) determines the value of another attribute
• Candidate Key:
– A unique identifier. One of the candidate keys will
become the primary key
▪ E.g., perhaps there is both credit card number and
SS# in a table…in this case both are candidate
keys.
– Each non-key field is functionally dependent on every
candidate key.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
First Normal Form
• No multivalued attributes
• Every attribute value is atomic
• Fig. 4-25 is not in 1st Normal Form (multivalued attributes)
→ it is not a relation.

• Fig. 4-26 is in 1st Normal form.

• All relations are in 1st Normal Form.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4.25 Invoice Data (Pine Valley
Furniture Company)

Table with multivalued attributes, not in 1st normal form.

This is not a relation.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4.26 Invoice Relation (1NF)
(Pine Valley Furniture Company)

This is a relation, but not a well-structured one.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Anomalies in This Relation (2 of 2)
• Insertion – if new product is ordered for order 1007 of
existing customer, customer data must be re-entered,
causing duplication
• Deletion – if we delete the Dining Table from Order
1006, we lose information concerning this item’s finish
and price
• Update – changing the price of product ID 4 requires
update in multiple records

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Second Normal Form
• 1NF plus every non-key attribute is fully functionally
dependent on the ENTIRE primary key
– Every non-key attribute must be defined by the entire
key, not by only part of the key
– No partial functional dependencies

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-27 Functional Dependency
Diagram for Invoice

OrderID → OrderDate, CustomerID, CustomerName, CustomerAddress

CustomerID → CustomerName, CustomerAddress ProductID →

ProductDescription, ProductFinish, ProductStandardPrice OrderID, ProductID

→ OrderQuantity

Therefore, not in 2nd Normal Form

Getting it into Second Normal Form

Partial dependencies are removed, but there are still transitive
dependencies

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Third Normal Form
• 2NF PLUS no transitive dependencies (functional
dependencies on non-primary-key attributes)
• Note: This is called transitive, because the primary key is
a determinant for another attribute, which in turn is a
determinant for a third
• Solution: Non-key determinant with transitive
dependencies go into a new table; non-key determinant
becomes primary key in the new table and stays as
foreign key in the old table

Getting it into Third Normal Form

Transitive dependencies are removed.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-30 Relational Schema for
Invoice Data (Microsoft Visio Notation)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Merging Relations
• View Integration – Combining entities from multiple E-R
models into common relations
• Issues to watch out for when merging entities from different
E-R models:
– Synonyms – two or more attributes with different names
but same meaning
– Homonyms – attributes with same name but different
meanings
– Transitive dependencies – even if relations are in 3NF
prior to merging, they may not be after merging
– Supertype/subtype relationships – may be hidden prior to
merging
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 4-31 Enterprise Keys
(a) Relations with enterprise key

(b) Sample data with enterprise key

Chapter 5
Introduction to SQL

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
SQL Overview
• Structured Query Language – often pronounced
“Sequel”
• The standard for Relational Database Management
Systems (RDBMS)
• RDBMS: A database management system that
manages data as a collection of tables in which all
relationships are represented by common values in
related tables

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
History of SQL
• 1970 – E. F. Codd develops relational database concept

• 1974-79 – System R with Sequel (later SQL) created at IBM Research Lab

• 1979 – Oracle markets first relational DB with SQL

• 1981 – SQL/DS first available RDBMS system on DOS/VSE

– Others followed: INGRES (1981), IDM (1982), DG/SGL (1984), Sybase
(1986)

• 1986 – ANSI SQL standard released

– Major ANSI standard updates in 1989, 1992, 1999, 2003, 2006, 2008,
2011, 2016

• Today – SQL is supported by most major database vendors

Is SQL a standard? No longer certified by NIST.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Original Purpose of SQL Standard
• Specify syntax/semantics for data definition and manipulation
• Define data structures and basic operations
• Enable portability of database definition and application
modules
• Specify minimal (level 1) and complete (level 2) standards
• Allow for later growth/enhancement to standard (referential
integrity, transaction management, user-defined functions,
extended join operations, national character sets)

• Reduced training costs

• Productivity
• Application portability
• Application longevity
• Reduced dependence on a single vendor
• Cross-system communication

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
SQL Environment
• Catalog
– A set of schemas that constitute the description of a database
• Schema
– The structure that contains descriptions of objects created by a user
(base tables, views, constraints)
• Data Definition Language (DDL)
– Commands that define a database, including creating, altering, and
dropping tables and establishing constraints
• Data Manipulation Language (DML)
– Commands that maintain and query a database
• Data Control Language (DCL)
– Commands that control a database, including administering privileges
and committing data

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 5-1 A Simplified Schematic of a Typical SQL
Environment, as Described by the SQL:2016 Standards

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
SQL Data Types
• Strings
– CHARACTER (n), VARYING CHARACTER (n)
• Binary
– Binary Large Object (BLOB)
• Number
– Numeric (precision, scale), Decimal (p, s), Integer
• Temporal
– Timestamp, Timestamp with local time zone
• Boolean
– True or False values
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 5-4 DDL, DML, DCL, and the
Database Development Process

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
SQL Database Definition
• Data Definition Language (DDL)
• Major CREATE statements:
– CREATE SCHEMA – defines a portion of the
database owned by a particular user
– CREATE TABLE – defines a new table and its
columns
– CREATE VIEW – defines a logical table from one or
more tables or views
• Other CREATE statements: CHARACTER SET,
COLLATION, TRANSLATION, ASSERTION, DOMAIN

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Steps in Table Creation
1. Identify data types for attributes
2. Identify columns that can and cannot be null
3. Identify columns that must be unique (candidate keys)
4. Identify primary key–foreign key mates
5. Determine default values
6. Identify constraints on columns (domain specifications)
7. Create the table and associated indexes

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 5-5 General Syntax of the CREATE TABLE
Statement Used in Data Definition Language

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
The Following Slides Create Tables for
This Enterprise Data Model

(from Chapter 1, Figure 1-3)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 5-6 SQL Database Definition Commands for
Pine Valley Furniture Company (Oracle 12c)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Non-Nullable Specifications
Some primary keys are composite– composed of multiple
attributes

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Identifying Foreign Keys and
Establishing Relationships

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Integrity Controls
• Referential integrity – constraint that ensures that foreign
key values of a table must match primary key values of a
related table in 1: N relationships

• Restricting:
– Deletes of primary records
– Updates of primary records
– Inserts of dependent records

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 5-7 Ensuring Data Integrity
Through Updates
Relational integrity is enforced via the primary-key to foreign-key match

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Changing Tables
• ALTER TABLE statement allows you to change column
specifications:
– ALTER TABLE table_name alter_table_action;
• Table Actions:
– ADD [COLUMN] column_definition
– ALTER [COLUMN] column_name SET DEFAULT default-
value
• Example (adding a new column with a default value):
– ALTER TABLE CUSTOMER_T ADD CustomerType
VARCHAR2 (10) DEFAULT ‘Commercial’;

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Removing Tables
• DROP TABLE statement allows you to remove tables
from your schema:
– DROP TABLE Customer_T

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
INSERT Statement
• Adds one or more rows to a table
• Inserting into a table:
– INSERT INTO Customer_T VALUES (001, ‘Contemporary
Casuals’, ‘1355 S. Himes Blvd.’, ‘Gainesville’, ‘FL’, ‘32601’);
• Inserting a record that has some null attributes requires identifying
the fields that actually get data:
– INSERT INTO Product_T (ProductID, ProductDescription,
ProductFinish, ProductStandardPrice) VALUES (1, ‘End Table’,
‘Cherry’, 175, 8);
• Inserting from another table:
– INSERT INTO CaCustomer_T SELECT * FROM Customer_T
WHERE CustomerState = ‘CA’;

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Creating Tables With Identity Columns
Inserting into a table does not require explicit customer ID entry or
field list.

INSERT INTO Customer_T VALUES (‘Contemporary Casuals’, ‘1355

S. Himes Blvd.’, ‘Gainesville’, ‘FL’, ‘32601’);

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
DELETE Statement
• Removes rows from a table
• Delete certain rows
– DELETE FROM Customer_T WHERE
CustomerState = ‘HI’;
• Delete all rows
– DELETE FROM Customer_T;

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
UPDATE Statement
• Modifies data in existing rows
– UPDATE Product_T SET
ProductStandardPrice = 775 WHERE
ProductID = 7;

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
MERGE Statement
Makes it easier to update a table. It allows combination of
Insert and Update in one statement.
Useful for updating master tables with new data.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Schema Definition
• Control processing/storage efficiency:
– Choice of indexes
– File organizations for base tables
– File organizations for indexes
– Data clustering
– Statistics maintenance

• Creating indexes
– Speed up random/sequential access to base table data
– Example
▪ CREATE INDEX Name_IDX ON Customer_T(CustomerName);
▪ This makes an index for the CUSTOMERNAME field of the
CUSTOMER_T table

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
SELECT Statement
• Used for queries on single or multiple tables
• Clauses of the SELECT statement:
– SELECT: List the columns (and expressions) to be returned
from the query
– FROM: Indicate the table(s) or view(s) from which data will be
obtained
– WHERE: Indicate the conditions under which a row will be
included in the result
– GROUP BY: Indicate categorization of results
– HAVING: Indicate the conditions under which a category
(group) will be included
– ORDER BY: Sorts the result according to specified criteria

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
SELECT Example
• Find products with standard price less than $275

• Comparison operators include

– = Equal to
– > Greater than
– >= Greater than or equal to
– < Less than
– <= Less than or equal to
– <> Not equal to
– != Not equal to
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
SELECT Example Using Alias
• Alias is an alternative column or table name

SELECT [Link] AS Name,

[Link]
FROM Customer_T AS Cust WHERE Name =
‘Home Furnishings’;

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
SELECT Example Using a Function
• Using the COUNT aggregate function to find totals

SELECT COUNT (*) FROM OrderLine_T

WHERE OrderID = 1004;

• Note: With aggregate functions you can’t have single-

valued columns included in the SELECT clause, unless
they are included in the GROUP BY clause.

• AND, OR, and NOT Operators for customizing

conditions in WHERE clause

SELECT ProductDescription, ProductFinish,

ProductStandardPrice
FROM Product_T
WHERE ProductDescription LIKE ‘%Desk’
OR ProductDescription LIKE ‘%Table’
AND ProductStandardPrice > 300;

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 5-8 Boolean Query a Without the
Use of Parentheses
By default, processing order of Boolean operators is NOT, then
AND, then OR

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
SELECT Example–Boolean Operators
• With parentheses…these override the normal precedence of
Boolean operators

SELECT ProductDescription, ProductFinish,

ProductStandardPrice
FROM Product_T
WHERE (ProductDescription LIKE ‘%Desk’
OR ProductDescription LIKE ‘%Table’)
AND ProductStandardPrice > 300;

With parentheses, you can override normal precedence rules. In

this case parentheses make the OR take place before the AND.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 5-9 Boolean Query B With Use of
Parentheses
With parentheses, you can override normal precedence rules. In this
case parentheses make the OR take place before the AND.

• Sort the results first by STATE, and within a state by the

CUSTOMER NAME

SELECT CustomerName, CustomerCity, CustomerState

FROM Customer_T
WHERE CustomerState IN (‘FL’, ‘TX’, ‘CA’, ‘HI’)
ORDER BY CustomerState, CustomerName;

• Note: The IN operator in this example allows you to

include rows whose CustomerState value is either FL, TX,
CA, or HI. It is more efficient than separate OR conditions.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Categorizing Results Using GROUP BY
Clause
• For use with aggregate functions
– Scalar aggregate: single value returned from SQL query
with aggregate function
– Vector aggregate: multiple values returned from SQL
query with aggregate function (via GROUP BY)

SELECT CustomerState, COUNT (CustomerState)

FROM Customer_T
GROUP BY CustomerState

• You can use single-value fields with aggregate functions if

they are included in the GROUP BY clause

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Qualifying Results by Categories Using
the HAVING Clause

• For use with GROUP BY

SELECT CustomerState, COUNT (CustomerState)

FROM Customer_T
GROUP BY CustomerState
HAVING COUNT (CustomerState) > 1;

• Like a WHERE clause, but it operates on groups

(categories), not on individual rows. Here, only those
groups with total numbers greater than 1 will be included
in the final result.
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
A Query With Both WHERE and HAVING
SELECT ProductFinish, AVG (ProductStandardPrice)
FROM Product_T
WHERE ProductFinish IN (‘Cherry’, ‘Natural Ash’, ‘Natural
Maple’, ‘White Ash’)
GROUP BY ProductFinish
HAVING AVG (ProductStandardPrice) < 750
ORDER BY ProductFinish;

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 5-10 SQL Statement Processing
Order
SELECT [ALL/DISTINCT] column_list
FROM table list
[WHERE conditional expression]
[GROUP BY group_by_column_list]
[HAVING conditional expression]
[ORDER BY order_by_column_list]

(based on van der Lans, 2006, p. 100)

Chapter 6
Advanced SQL

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Processing Multiple Tables (1 of 2)
• Join
– A relational operation that causes two or more tables with
a common domain to be combined into a single table or
view
• Equi-join
– A join in which the joining condition is based on equality
between values in the common columns; common
columns appear redundantly in the result table
• Natural (inner) join
– An equi-join in which one of the duplicate columns is
eliminated in the result table

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Processing Multiple Tables (2 of 2)
• Outer join
– A join in which rows that do not have matching values
in common columns are nonetheless included in the
result table (as opposed to inner join, in which rows
must have matching values in order to appear in the
result table)
• Union join
– Includes all data from each table that was joined

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 6-2 Visualization of Different Join Types,
With the Results Returned in the Shaded Area

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
The Following Slides Create Tables for
This Enterprise Data Model
(from Chapter 1, Figure 1-3)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 6-1 Pine Valley Furniture Company Customer_T
and Order_T Tables, With Pointers From Customers to
Their Orders

With pointers from orders to the customers who placed

them

Result:
Customerid Customerid Customername Orderid
1 1 Contemporary Casuals 1001
8 8 California Classics 1002
15 15 Mountain Scenes 1003
5 5 Impressions 1004
3 3 Home Furnishings 1005
2 2 Value Furniture 1006
11 11 American Euro Lifestyles 1007
12 12 Battle Creek Furniture 1008
4 4 Eastern Furniture 1009
1 1 Contemporary Casuals 1010
10 rows selected. Blank Blank Blank

What are the customer IDs and names of all customers, along with the order IDs
for all the orders they have placed?
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Equi-Join Example – Alternative Syntax
An INNER Join

INNER JOIN clause is an alternative to WHERE clause,

and is used to match primary and foreign keys.
An INNER join will only return rows from each table that
have matching rows in the other.
This query produces the same results as the previous equi-
join example.
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Outer-Join Example
List the customer name, ID number, and order number for all customers.
Include customer information even for customers that do not have an order.

LEFT OUTER JOIN clause causes rows from the first mentioned table
(customer) to appear even if there is no corresponding order data.

Unlike an INNER join, this will include customer rows with no matching order
rows.

For the tables in figure 6.1, this will return 16 rows. That’s because there are 15
customers, and one of these customers has 2 orders.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Result
Customerid Customername Orderid
Note two rows for 1 Contemporary Casuals 1001
customer #1 1 Contemporary Casuals 1010
2 Value Furniture 1006
Contemporary 3 Home Furnishings 1005
Casuals. 4 Eastern Furniture 1009
5 Impressions 1004
Also note that
Blank

6 Furniture Gallery
Blank

7 Period Furniture
several customers 8 California Classics 1002
don’t have orders. 9 M & H Casual Furniture
Blank

Blank

10 Seminole Interiors

This is because of 11 American Euro Lifestyles 1007

12 Battle Creek Furniture 1008
the left outer join. 13 Heritage Furnishings
Blank

Blank

14 Kaneohe Homes
15 Mountain Scenes 1003
Blank Blank

16 rows selected.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Multiple Table Join Example
Assemble all information necessary to create an invoice for order
number 1006.
Each pair of tables requires an equality-check condition in the WHERE
clause, matching primary keys against foreign keys.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 6-4 Results From a Four-Table Join
(Edited for Readability)

All rows returned from this query will pertain to OrderID

1006.
Note that the full query results include columns from four
different tables.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Self Join Example
What are the employee ID and name of each employee and the name
of his or her supervisor (label the supervisor’s name Manager)?

Result:

Employeeid Employeename Manager

123-44-347 Jim Jason Robert Lewis

The same table is used on both sides of the join; distinguished using
table aliases. See the next slide for details.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 6-5 Example of a Self-Join
Self join involve tables that implement 1-to-many unary
relationships.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Subqueries
• Subquery – placing an inner query (SELECT statement)
inside an outer query
• Options:
– In a condition of the WHERE clause
– As a “table” of the FROM clause
– Returning a field for the SELECT clause
– Within the HAVING clause
• Subqueries can be:
– Noncorrelated – executed once for the entire outer query
– Correlated – executed once for each row returned by the
outer query
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Subquery Example
What are the name and address of the customer who
placed order number 1008?

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Alternative Approach, Using a Join
What are the name and address of the customer who
placed order number 1008?

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 6-6 Graphical Depiction of Two Ways to
Answer a Query With Different Types of Joins (1 of 2)

a) Join query approach

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 6-6 Graphical Depiction of Two Ways to
Answer a Query With Different Types of Joins (2 of 2)

b) Subquery approach

• Noncorrelated subqueries:
– Do not depend on data from the outer query
– Execute once for the entire outer query
• Correlated subqueries:
– Make use of data from the outer query
– Execute once for each row of the outer query
– Can use the EXISTS and ALL operators

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Example of a Correlated Subquery
List the details about the product with the highest standard price.

Result:

Productdescription Productfinish Productstandardprice

Dining Table Natural Ash 800

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Another Correlated Subquery
What are the order IDs for all orders that have included furniture
finished in natural ash?

A correlated subquery always refers to an attribute from a table

referenced in the outer query.

a) Processing a noncorrelated subquery

b) Processing a correlated subquery

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Derived Table (Subquery in the FROM
Clause of the Outer Query)
What are the order I Ds for all orders that have included furniture
finished in natural ash?

Here, the subquery forms the derived table used in the FROM
clause of the outer query. The AvgPrice column from the
subquery is used in the SELECT clause of the outer query.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
UNION — Combining Queries
Combine the output
(union of multiple
queries) together into a
single result table

With UNION queries,

the quantity and data
types of the attributes in
the SELECT clauses of
both queries must be
identical. Result:

CustomerID CustomerName OrderedQuantity Quantity

1 Contemporary Casuals 1 Smallest Quantity
2 Value Furniture 1 Smallest Quantity
1 Contemporary Casuals 10 Largest Quantity

A CASE expression Result:

acts like an if-then Productdescription
statement. It allows you End Table

to choose what will ####

####
appear in a column of ####
the result set, Writers Desk

depending on a ####
####
condition. ####

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
More Complicated SQL Queries
• Production databases contain hundreds or even
thousands of tables, and tables could include
hundreds of columns.
• So, sometimes query requirements can be very
complex.
• Sometimes it’s useful to combine queries, through the
use of Views.
• If you use a view (which is a query), you could have
another query that uses the view as if it were a table.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Using a View in Your Query
For each
salesperson, list
his or her biggest-
selling product.
The view:

The query using

the view:

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Tips for Developing Queries
• Be familiar with the data model (entities and relationships)
• Understand the desired results
• Know the attributes desired in results
• Identify the entities that contain desired attributes
• Review ERD
• Construct a WHERE equality for each link
• Fine tune with GROUP BY and HAVING clauses if needed
• Consider the effect on unusual data

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Query Efficiency Considerations
• Instead of SELECT *, identify the specific attributes in
the SELECT clause; this helps reduce network traffic of
result set
• Limit the number of subqueries; try to make everything
done in a single query if possible
• If data is to be used many times, make a separate
query and store it as a view

• Understand how indexes are used in query processing

• Keep optimizer statistics up to date
• Use compatible data types for fields and literals
• Write simple queries
• Break complex queries into multiple simple parts
• Don’t nest one query inside another query
• Don’t combine a query with itself (if possible avoid self-
joins)

• Create temporary tables for groups of queries

• Combine update operations
• Retrieve only the data you need
• Don’t have the DBMS sort without an index
• Learn!
• Consider the total query processing time for ad hoc
queries

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Using and Defining Views
• Dynamic View
– A “virtual table” created dynamically upon request by
a user
– No data actually stored; instead data from base table
made available to user
– Based on SQL SELECT statement on base tables or
other views
• Materialized View
– Copy or replication of data, data actually stored
– Must be refreshed periodically to match
corresponding base tables
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
A Sample Create View Command

• View has a name

• View is based on a SELECT statement
• CHECK_OPTION works only for updateable views and
prevents updates that would create rows not included in
the view

• Simplify query commands

• Assist with data security
• Enhance programming productivity
• Contain most current base table data
• Use little storage space
• Provide customized view for user
• Establish physical data independence

• Use processing time each time view is referenced

• May or may not be directly updateable
• As with all SQL constructs, you should use views with
discretion

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Routines and Triggers
• Routines
– Program modules that execute on demand
• Functions
– routines that return values and take input parameters
• Procedures
– routines that do not return values and can take input or
output parameters
• Triggers
– routines that execute in response to a database event
(INSERT, UPDATE, or DELETE)
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 6-12 Triggers Contrasted With Stored
Procedures (Based on Mullins, 1995)

Procedures and functions are called explicitly. Triggers are

event-driven.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 6-13 Simplified Trigger Syntax in
SQL:2008

Example DML trigger

Example DDL trigger

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 6-14 Syntax for Creating a
Routine in SQL:2011

Example DDL trigger

Calling the procedure

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Dictionary Facilities
• System tables that store metadata
• Users usually can view some of these tables
• Users are restricted from updating them
• Examples in Oracle 12c
– DBA_TABLES – descriptions of tables
– DBA_USERS – information about the users of the system
• Examples in Microsoft SQL Server 2016
– [Link] – table and column definitions
– [Link] – table index information

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
SQL Enhancements/Extensions (1 of 2)
• User-defined data types (UDT)
– Subclasses of standard types or an object type
• Analytical functions (for OLAP)
– CEILING, FLOOR, SQRT, RANK, DENSE_RANK, ROLLUP,
CUBE, SAMPLE,
– WINDOW – improved numerical analysis capabilities
• New Data Types
– BIGINT, MULTISET (collection), XML
• CREATE TABLE LIKE
– create a new table similar to an existing one
• MERGE
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
SQL Enhancements/Extensions (2 of 2)
• Programming extensions
• Persistent Stored Modules (SQL/PSM)
• Capability to create and drop code modules
• New statements: CASE, IF, LOOP, FOR, WHILE, etc.
• Makes SQL into a procedural language
• Oracle has propriety version called PL/SQL, and
Microsoft SQL Server has Transact/SQL

Chapter 7
Databases in Applications

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Client/Server Architectures
• Networked computing model
• Processes distributed between clients and servers
• Client – Workstation (PC, smartphone, tablet) that
requests and uses a service
• Server – Powerful computer (PC/mini/mainframe) that
provides a service
• For DBMS, server is a database server
• For the Internet, server is a Web server

GUI interface (e.g.

through a browser)

Procedures, functions,
programs

DBMS activities

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Application Partitioning
• Placing portions of the application code in different
locations (client v s server) after it is written
ersu

• Advantages
– Improved performance
– Improved interoperability
– Balanced workloads

• Fat client – a client PC that is responsible for processing

presentation logic, extensive application and business
rules logic, and many DBMS functions
• Thin client – an application where the client (PC)
accessing the application primarily provides the user
interfaces and some application processing, usually with
no or limited local data storage

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 7-2 Common Logic
Distributions (1 of 2)
(a) Two-tier client-server environments

Processing logic could be at client (fat client), server (thin client), or both
(distributed environment).

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 7-2 Common Logic
Distributions (2 of 2)
(b) n-tier client/server environments

Processing logic will be at application server or Web server.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Web Application Components
• Database server – hosts the DBMS
– e.g., Oracle, SQL Server, Informix, MS Access, MySql
• Web server – receives and responds to browser requests using
HTTP protocol
– e.g., Apache, Internet Information Services (IIS)
• Application server – software building blocks for creating
dynamic Web sites
– e.g., MS [Link] framework, Java EE, PHP
• Web browser – client program that sends Web requests and
receives Web pages
– e.g., Internet Explorer, Mozilla Firefox, Apple Safari, Google
Chrome
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 7-3 A Database-Enabled
Intranet/Internet Environment

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 7-5 Information Flow in a
Three-Tier Architecture
(a) Static page request

(b) Dynamic page request

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Middleware and APIs
• Middleware – software that allows an application to
interoperate with other software without requiring user to
understand and code low-level operations
• Application Program Interface (API) – routines that an
application uses to direct the performance of procedures
by the computer’s operating system
• Common database APIs – ODBC, ADO .NET, JDBC

1. Identify and register a database driver.

2. Open a connection to a database.
3. Execute a query against the database.
4. Process the results of the query.
5. Repeat steps 3–4 as necessary.
6. Close the connection to the database.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 7-7 Database Access from a
Java Program
Java implementation of database access to an Oracle database
using JDBC

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 7-8 System Architecture of
Sample Application
Python application for database
processing.
Model class creates instances of
data from table queries.
Serializer class transforms
database data into a form that can
be used by the client (JSON).
View classes display in browsers
for view or edit.

• Stored procedures
– Code logic embedded in DBMS
– Improve performance, but proprietary
• Transactions
– Involve many database updates
– Either all must succeed, or none should occur
• Database connections
– Maintaining an open connection is resource-
intensive
– Use of connection pooling
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Advantages and Disadvantages of
Stored Procedures

• Advantages
– Performance improves for compiled SQL statements
– Reduced network traffic
– Improved security
– Improved data integrity
– Thinner clients
• Disadvantages
– Programming takes more time
– Proprietary, so algorithms are not portable

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Benefits of Three-Tier Architectures
• Scalability
• Technological flexibility
• Long-term cost reduction
• Better match of systems to business needs
• Improved customer service
• Competitive advantage
• Reduced risk

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Transaction Integrity: ACID Rules
• Atomic
– Transaction cannot be subdivided
• Consistent
– Constraints don’t change from before transaction to
after transaction
• Isolated
– Database changes not revealed to users until after
transaction has completed
• Durable
– Database changes are permanent
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 7-19 An SQL Transaction
Sequence (Pseudocode)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Controlling Concurrent Access
• Problem
– In a multi-user environment, simultaneous access
to data can result in interference and data loss
(lost update problem)
• Solution – Concurrency Control
– Managing simultaneous operations against a
database so that data integrity is maintained and
the operations do not interfere with each other in a
multi-user environment

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 7-20 Lost Update (No Concurrency
Control in Effect)

Simultaneous access causes updates to cancel each other.

A similar problem is the inconsistent read problem.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Concurrency Control Techniques
• Serializability
– Finish one transaction before starting another
• Locking Mechanisms
– The most common way of achieving serialization
– Data that is retrieved for the purpose of updating is
locked for the updater
– No other user can perform update until unlocked

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 7-21 Updates with Locking
(Concurrency Control)
Locking solves the lost update problem

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Locking Mechanisms
• Locking level:
– Database – used during database updates
– Table – used for bulk updates
– Block or page – very commonly used
– Record – only requested row; fairly commonly used
– Field – requires significant overhead; impractical
• Types of locks:
– Shared lock – Read but no update permitted. Used when just
reading to prevent another user from placing an exclusive lock
on the record
– Exclusive lock – No access permitted. Used when preparing to
update

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Deadlock
• An impasse that results when
two or more transactions
have locked common
resources, and each waits for
the other to unlock their
resources
• Figure 7-22 shows the
problem of deadlock
• John and Marsha will wait
forever for each other to
release their locked
resources!

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Managing Deadlock
• Deadlock prevention:
– Lock all records required at the beginning of a transaction
– Two-phase locking protocol
▪ Growing phase
▪ Shrinking phase
– May be difficult to determine all needed resources in advance
• Deadlock Resolution:
– Allow deadlocks to occur
– Mechanisms for detecting and breaking deadlock
▪ Resource usage matrix
▪ Back out one deadlock at a time
▪ Rerun transaction
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Versioning
• Optimistic approach to concurrency control
• Instead of locking
• Assumption is that simultaneous updates will be infrequent
• Each transaction can attempt an update as it wishes
• The system will create a new version of a record instead of replacing
the old one
• When a conflict occurs, accept one user’s update and inform the
other user that its update needs to be tried again.
• Use of rollback and commit for this

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 7-24 The Use of Versioning
Better performance than locking

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Security
• Database Security: Protection of the data against
accidental or intentional loss, destruction, or misuse
• Increased difficulty due to Internet access and
client/server technologies

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Threats to Data Security
• Accidental losses attributable to:
– Human error
– Software failure
– Hardware failure
• Theft and fraud
• Loss of privacy or confidentiality
– Loss of privacy (personal data)
– Loss of confidentiality (corporate data)
• Loss of data integrity
• Loss of availability (e.g., through sabotage)
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 7-25 Possible Locations of Data
Security Threats
Threats come from many sources and vulnerabilities exist in multiple
places within an information system.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Client–Server Application Security
• Static HTML files are easy to secure
– Standard database access controls
– Place Web files in protected directories on server
• Dynamic pages are harder
– User authentication
– Session security
– SSL for encryption
– Restrict number of users and open ports
– Remove unnecessary programs

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Privacy
• W3C Web Privacy Standard
– Platform for Privacy Protection (P3P)
• Addresses the following:
– Who collects data
– What data is collected and for what purpose
– Who is data shared with
– Can users control access to their data
– How are disputes resolved
– Policies for retaining data
– Where are policies kept and how can they be accessed

Chapter 8
Physical Database Design
and Database Infrastructure

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Physical Database Design
• Purpose – translate the logical description of data into
the technical specifications for storing and retrieving
data
• Goal – create a design for storing data that will provide
adequate performance and ensure database
integrity, security, and recoverability

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Information Needed for Physical Design
• Normalized relations, including estimates for the range of
the number of rows in each table
• Definitions of each attribute, along with physical
specifications such as maximum possible length
• Descriptions of where and when data are used in various
ways (entered, retrieved, deleted, and updated), including
typical frequencies of these events
• Expectations or requirements for response time and data
security, backup, recovery, retention, and integrity
• Descriptions of the technologies (database management
systems) used for implementing the database
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Critical Decisions for Physical Design
• Choosing the storage format (called data type) for each
attribute from the logical data model
• Giving the DBMS guidance regarding how to group attributes
from the logical data model into physical records
• Giving the DBMS guidance on how to arrange similarly
structured records in secondary memory (file organization)
• Selecting structures (including indexes and the overall
database architecture) for storing and connecting files to
make retrieving related data more efficient
• Preparing strategies for handling queries against the
database that will optimize performance (query optimization)
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Physical Design for Regulatory
Compliance

• Sarbanes- Oxley Act (SOX) – protect investors by

improving accuracy and reliability
• Committee of Sponsoring Organizations (COSO) of the
Treadway Commission
• IT Infrastructure Library (ITIL)
• Control Objectives for Information and Related
Technology (COBIT)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Three Areas of SOX Audits
• IT change management
– Processes by which changes to operational systems
and databases are authorized
• Logical access to data
– Security procedures to prevent unauthorized access
– Personnel controls and physical access controls
• IT operations
– Policies and procedures for day-to-day management
of infrastructure, applications, and databases

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 8-1 Composite Usage Map (Pine
Valley Furniture Company)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Designing Fields
• Field: smallest unit of application data recognized by
system software
• Field design
– Choosing data type
– Coding, compression, encryption
– Controlling data integrity

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Common Data Types (1 of 2)
• VARCHAR2(length) max 400 characters
– Variable-length character data. A string that is shorter than the
maximum length will consume only the required space. NVARCHAR2 is
Unicode.

• CHAR(length) max 200 characters

– Fixed length character data. NCHAR is Unicode.

• CLOB
– Character large object, capable of storing up to 4 gigabytes of one
variable length character data field

• NUMBER
– Positive or negative number. NUMBER(5) means a 5 digit integer.
NUMBER(5,2): 5 digits, two to the right of the decimal point.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Common Data Types (2 of 2)
• DATE
– Can represent from Jan 1 4712 BC to Dec 31 9999 AD
– Stores century, year, month, day, hour, minute, second
• TIMESTAMP
– Like a date. Can include fractional seconds, and time
zones.
• BLOB
– Binary large object, can store up to 4 gigabytes
– Used for photos, sound clips, etc.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 8-2 Example of a Code Lookup
Table (Pine Valley Furniture Company)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Controlling Data Integrity
• Default value – assumed value if no explicit value
• Range control – allowable value limitations (constraints
or validation rules)
• Null value control – allowing or prohibiting empty fields
• Referential integrity – range control (and null value
allowances) for foreign-key to primary-key match-ups

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Handling Missing Data
• Substitute an estimate of the missing value (e.g., using a
formula)
• Construct a report listing missing values
• In programs, ignore missing data unless the value is
significant (sensitivity testing)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Denormalization
• Transforming normalized relations into non-normalized
physical record specifications
• Benefits:
– Can improve performance (speed) by reducing
number of table lookups (i.e. reduce number of
necessary join queries)
• Costs (due to data duplication)
– Wasted storage space
– Data integrity/consistency threats

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 8-3 A Possible Denormalization Situation:
Two Entities With One-to-One Relationship

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 8-4 A Possible Denormalization Situation: A
Many-to-Many Relationship With Non-Key Attributes

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Denormalize With Caution
• Denormalization can
– Increase chance of errors and inconsistencies
– Reintroduce anomalies
– Force reprogramming when business rules change
• Perhaps other methods could be used to improve
performance of joins
– Organization of tables in the database (file
organization and clustering)
– Proper query design and optimization

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Partitioning
• Horizontal Partitioning: Distributing the rows of a logical
relation into several separate tables
– Useful for situations where different users need
access to different rows
– Three types: Key Range Partitioning, Hash
Partitioning, or Composite Partitioning
• Vertical Partitioning: Distributing the columns of a logical
relation into several separate physical tables
– Useful for situations where different users need
access to different columns
– The primary key must be repeated in each file
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Partitioning Pros and Cons
• Advantages of Partitioning
– Efficiency: records used together are grouped together
– Local optimization: each partition can be optimized for
performance
– Security: data not relevant to users are segregated
– Recovery and uptime: smaller files take less back up time
– Load balancing: partitions stored on different disks, reduces
contention

• Disadvantages of Partitioning
– Inconsistent access speed: slow retrievals across partitions
– Complexity: non-transparent partitioning
– Extra space or update time: duplicate data; access from multiple
partitions

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Designing Physical Database Files
• Physical File
– A named portion of secondary memory allocated for
the purpose of storing physical records
– Tablespace – named logical storage unit in which
data from multiple tables/views/objects can be stored
• Tablespace components
– Segment – a table, index, or partition
– Extent – contiguous section of disk space
– Data block – smallest unit of storage

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
File Organizations
• Types of file organizations
– Heap – no particular order
– Sequential
– Indexed
– Hashed
• Factors for selecting file organization
– Fast data retrieval and throughput
– Efficient storage space utilization
– Protection from failure and data loss
– Minimizing need for reorganization
– Accommodating growth
– Security from unauthorized use
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 8-7 Comparison of File
Organizations (1 of 3)
(a) Sequential

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Indexed File Organizations
• Storage of records sequentially or nonsequentially with
an index that allows software to locate individual
records
• Index: a table or other data structure used to determine
in a file the location of records that satisfy some
condition
• Primary keys are automatically indexed
• Other fields or combinations of fields can also be
indexed; these are called secondary keys (or
nonunique keys)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 8-7 Comparison of File
Organizations (2 of 3)
(b) Indexed

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 8-7 Comparison of File
Organizations (3 of 3)
(c) Hashed

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Clustering Files
• In some relational DBMSs, related records from
different tables can be stored together in the same
disk area
• Useful for improving performance of join operations
• Primary key records of the main table are stored
adjacent to associated foreign key records of the
dependent table
• e.g. Oracle has a CREATE CLUSTER command

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Unique and Nonunique Indexes
• Unique (primary) Index
– Typically done for primary keys, but could also apply
to other unique fields
– CREATE UNIQUE INDEX CustIndex_PK ON
Customer_T(CustomerID);
• Non unique (secondary) index
– Done for fields that are often used to group individual
entities (e.g., zip code, product category)
– CREATE INDEX DescIndex_FK ON
Product_T(Description);

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
When to Use Indexes (1 of 2)
1. Use on larger tables
2. Index the primary key of each table
3. Index search fields (fields frequently in WHERE
clause)
4. Fields in SQL ORDER BY and GROUP BY
commands
5. When there are >100 values but not when there are
<30 values

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
When to Use Indexes (2 of 2)
6. Avoid use of indexes for fields with long values; perhaps
compress values first
7. If key to index is used to determine location of record,
use surrogate (like sequence number) to allow even
spread in storage area
8. DBMS may have limit on number of indexes per table
and number of bytes per indexed field(s)
9. Be careful of indexing attributes with null values; many
DBMSs will not recognize null values in an index search
[Link] a query optimizer
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Query Optimization
• Parallel query processing – possible when working in
multiprocessor systems
• Overriding automatic query optimization allows for query
writers to preempt the automated optimization
• Oracle example:

SELECT /+ FULL(Order_T) PARALLEL(Order_T,3) /

COUNT(*)
FROM Order_T
WHERE Salesperson = “Smith”;

• /* */ clause is a hint to override Oracle’s default query plan

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Dictionaries and Repositories
• Data dictionary
– Documents data elements of a database
• System catalog
– System-created database that describes all
database objects
• Information Repository
– Stores metadata describing data and data
processing resources

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 8-8 Three Components of the Repository
System Architecture (Based on Bernstein, 1996)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Database Software Security Features
• Views or subschemas
• Integrity controls
• Authorization rules
• User-defined procedures
• Encryption
• Authentication schemes
• Backup, journalizing, and checkpointing

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Views and Integrity Controls
• Views
– Subset of the database that is presented to one or more
users
– User can be given access privilege to view without
allowing access privilege to underlying tables
• Integrity Controls
– Protect data from unauthorized use
– Domains – set allowable values
– Assertions – enforce database conditions
– Triggers – prevent inappropriate actions, invoke special
handling procedures, write to log files

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Authorization Rules
• Controls incorporated in the data
management system
• Restrict:
– access to data
– actions that people can take on data

• Authorization matrix
for: Subject Object Action Constraint
Sales Dept. Customer record Insert Credit limit LE $5000
– Subjects Order trans. Customer record Read None
– Objects Terminal 12 Customer record Modify Balance due only

– Actions Acctg. Dept. Order record Delete None

Ann Walker Order record Insert Order aml LT $2000
– Constraints Program AR4 Order record Modify None

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 8-10 Implementing Authorization
Rules
(a) Authorization table for subjects (salespersons)

Blank

Customer records Order records

Read Y Y
Insert Y Y
Modify Y N
Delete N N

(b) Authorization table for objects (orders)

Blank
Salespersons Order entry Accounting
(password BATMAN) (password JOKER) (password TRACY)
Read Y Y Y
Insert N Y N
Modify N Y Y
Delete N N Y

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 8-12 Basic Two-Key Encryption
Encryption – the
coding or
scrambling of data
so that humans
cannot read them
Secure Sockets
Layer (SSL) is a
popular encryption
scheme for TCP/IP
connections.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Authentication Schemes (1 of 2)
• Goal – obtain a positive identification of the user
• Passwords: First line of defense
– Should be at least 8 characters long
– Should combine alphabetic and numeric data
– Should not be complete words or personal
information
– Should be changed frequently

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Authentication Schemes (2 of 2)
• Strong Authentication
– Passwords are flawed:
▪ Users share them with each other
▪ They get written down, could be copied
▪ Automatic logon scripts remove need to explicitly
type them in
▪ Unencrypted passwords travel the Internet
• Possible solutions:
– Two factor – e.g., smart card plus PIN
– Three factor – e.g., smart card, biometric, PIN

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Database Recovery
• Mechanism for restoring a database quickly and
accurately after loss or damage
• Recovery facilities:
– Backup Facilities
– Journalizing Facilities
– Checkpoint Facility
– Recovery Manager

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Back-up Facilities
• DBMS copy utility that produces backup copy of the
entire database or subset
• Periodic backup (e.g. nightly, weekly)
• Cold backup – database is shut down during backup
• Hot backup – selected portion is shut down and backed
up at a given time
• Backups stored in secure, off-site location

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Journalizing Facilities
• Audit trail of transactions and database updates
• Transaction log – record of essential data for each
transaction processed against the database
• Database change log – images of updated data
• Before-image – copy before modification
• After-image – copy after modification

From the backup and logs, databases can be restored in case of damage or loss

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Checkpoint Facilities
• DBMS periodically refuses to accept new transactions
• Therefore, the system is in a quiet state
• Database and transaction logs are synchronized
• This allows recovery manager to resume processing
from short period, instead of repeating entire day

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Recovery Manager
• Recovery Manager – DBMS module that restores the
database to a correct condition when a failure occurs and
then resumes processing user requests
• Recovery and Restart Procedures
– Disk Mirroring – switch between identical copies of
databases
– Restore/Rerun – reprocess transactions against the
backup (only done as a last resort)
– Backward Recovery (Rollback) – apply before images
– Forward Recovery (Roll Forward) – apply after images
(preferable to restore/rerun)
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 8-14 Basic Recovery
Techniques (1 of 2)
(a) Rollback

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Responses to Database Failures (1 of 2)
• Aborted transaction
– Rollback (preferred)
– Rollforward/return transactions to state just prior to
abort
• Incorrect data (update inaccurate)
– Rollback (preferred)
– Reprocess transactions without inaccurate data
updates
– Compensating transactions

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Responses to Database Failures (2 of 2)
• System failure (database intact)
– Switch to duplicate database (preferred)
– Rollback
– Restart from checkpoint (rollforward)
• Database destruction
– Switch to duplicate database (preferred)
– Rollforward
– Reprocess transactions

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Disaster Recovery
• Develop a detailed written disaster recovery plan, and
test this regularly
• Choose and train a multidisciplinary team to carry out
the plan
• Establish a backup data center at an off-site location,
located a sufficient distance from the primary site
• Send backup copies of databases to the backup data
center on a scheduled basis

• Cloud computing
– Provisioning/acquiring computing services on
demand using centralized resources accessed
through public Internet or private networks
• Infrastructure-as-a-Service (IaaS)
– Cloud service involving hardware and various
types of systems software resources
• Platform-as-a-Service (PaaS)
– Cloud service involving hardware and various
types of systems software resources

• Software-as-a-Service (SaaS)
– Cloud service involving software
solutions/applications intended to directly address
the needs of a noncomputing activity
• Database-as-a-Service (DBaaS)
– Cloud service involving data management cloud
platform service

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Benefits of Cloud-Based Data
Management Services
• No need for initial investments in hardware, physical facilities, and
systems software
• Significantly lower need for internal expertise in the management of
the database infrastructure
• Better visibility of overall costs of data management
• Increased level of flexibility (elasticity) in situations when capacity
needs to fluctuate significantly
• Allows organizations to explore new data management technologies
more easily
• Mature cloud service providers have expertise to provide a high level
of availability, reliability, and security

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Downside of Cloud-Based Data
Management Services
• Existing systems do not yet provide capacity using a model that
would automatically adapt to the changing requirements targeting
the system
• Current systems are not yet providing full consistency guarantees
in a highly distributed environment
• Live migration is still a challenging task that requires manual
planning, initiation, and management
• It is challenging to be able to monitor the extent to which cloud
providers are maintaining their Service Level Agreement (SLA)
commitments
• DBaaS solutions are still struggling to find fully scalable models for
providing ACID support for transactions

Chapter 9
Data Warehousing and Data
Integration

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Definitions
• Data Warehouse
– A subject-oriented, integrated, time-variant, non-updatable
collection of data used in support of management decision-
making processes
▪ Subject-oriented: e.g., customers, patients, students,
products
▪ Integrated: consistent naming conventions, formats,
encoding structures; from multiple data sources
▪ Time-variant: can study trends and changes
▪ Non-updatable: read-only, periodically refreshed
• Data Mart
– A data warehouse that is limited in scope

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Need for Data Warehousing
• Integrated, company-wide view of high-quality
information (from disparate databases)
• Separation of operational and informational systems
and data (for improved performance)

• Inconsistent data values

• Missing data
– See figure 9-1 for examples

• No single system of records

• Multiple systems not synchronized
• Organizational need to analyze activities in a balanced
way
• Customer relationship management
• Supplier relationship management

• Operational system – a system that is used to run a

business in real time, based on current data; also called
a system of record
• Informational system – a system designed to support
decision making based on historical point-in-time and
prediction data for complex queries or data-mining
applications

Primary purpose Run the business on a current basis Support managerial decision making

Type of data Current representation of state of Historical point in time (snapshots) and
the business predictions
Primary users Clerks, salespersons, administrators Managers, business analysts,
customers
Scope of usage Narrow, planned, and simple Broad, ad hoc, complex queries and
updates and queries analysis
Design goal Performance: throughput, availability, Ease and low cost of flexible access
reliability; alignment with business rules and use
Volume Many constant updates and queries Periodic batch updates and queries
on one or a few table rows requiring many or all rows

The goals, purposes, and usage of information systems and data warehouses
are very different from those of operational systems and OLTP databases

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Warehouse Architectures
• Independent Data Mart
• Dependent Data Mart and Operational Data Store
• Logical Data Mart and Real-Time Data Warehouse
• Three-Layer architecture

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 9-2 Independent Data Mart Data
Warehousing Architecture
Data marts are mini-warehouses, limited in scope. Separate ETL for each
independent data mart. Data access complexity due to multiple data marts

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Limitations of Independent Data Marts
• Separate ETL process for each data mart leads to
redundant data and processing efforts
• Inconsistency between data marts
• Difficult to drill down for related facts between data marts
• Excessive scaling costs the more applications are built
• High cost for obtaining consistency between marts

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 9-3 Dependent Data Mart With
Operational Data Store: 3-Level Architecture
Operational data store (ODS) provides option for transforming current data.
Single ETL for enterprise data warehouse (EDW). Dependent data marts
loaded from EDW.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 9-4 Logical Data Mart and Real
Time Warehouse Architecture
ODS and data warehouse are one and the same. Near real-time ETL for data
warehouse. Data marts are Not separate databases, but logical views of the
data warehouse. Easier to create new data marts

• Data Warehouse
– Application independent
– Centralized, possibly enterprise-wide
– Planned
• Data Mart
– Specific DSS application
– Decentralized by user area
– Organic, possibly not planned

Subjects, and Sources

• Data Warehouse
– Data is historical, detailed, and summarized
– Data it lightly denormalized
– Multiple subjects
– Many internal and external sources
• Data Mart
– Data has some history, is detailed and summarized
– Data is highly denormalized
– One central subject or concern to users
– Few internal and external sources
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Warehouse V s Data Mart: Other
ersu

Characteristics
• Data Warehouse
– Flexible
– Data oriented
– Long life
– Large
– Single complex structure
• Data Mart
– Restrictive
– Project oriented
– Short life
– Starts small, becomes large
– Multi-, semi-complex structures, together complex
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 9-5 Three-Layer Data Architecture
for a Data Warehouse

– Status – before and after images

– Event – something that causes changes to the
status. Typical example: a transaction.
• Transient v s Periodic Data
ersu

– Transient – changes to existing records are written

over previous records, destroying previous data
content
– Periodic – data is never physically altered or deleted
after being added to the store

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Other Data Warehouse Changes
• New descriptive attributes
• New business activity attributes
• New classes of descriptive attributes
• Descriptive attributes become more refined
• Descriptive data are related to one another
• New source of data

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Derived Data
• Objectives
– Ease of use for decision support applications
– Fast response to predefined user queries
– Customized data for particular target audiences
– Ad-hoc query support
– Data mining capabilities
• Characteristics
– Detailed (mostly periodic) data
– Aggregate (for summary)
– Distributed (to departmental servers)
• Most common data model = dimensional model (usually
implemented as a star schema)
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 9-9 Components of a Star Schema
Excellent for ad-hoc queries, but bad for online transaction processing
Dimension tables contain descriptions about the subjects of the business Fact
tables contain factual or quantitative data

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Surrogate Keys
• Dimension table keys should be surrogate (non-
intelligent and non-business related), because:
– Business keys may change over time
– Helps keep track of non-key attribute values for a
given production key
– Surrogate keys are simpler and shorter
– Surrogate keys can be same length and format for all
keys

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Grain of the Fact Table
• Granularity of Fact Table: What level of detail do you
want?
– Transactional grain – finest level
– Aggregated grain – more summarized
– Finer grains brings better market basket analysis
capability
– Finer grain implies more dimension tables, more rows
in fact table
– In Web-based commerce, finest granularity is a click

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Duration of the Database
• Natural duration – 13 months or 5 quarters
• Financial institutions may need longer duration
• Older data is more difficult to source and cleanse

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Size of Fact Table
• Depends on the number of dimensions and the grain of the fact table
• Number of rows = product of number of possible values for each
dimension associated with the fact table
• Example: Assume the following for Figure 9-11:
– Total number of stores = 1,000
– Total number of products = 10,000
– Total number of periods = 24 (two years’ worth of monthly data)
• Total rows calculated as follows (assuming only half the products
record sales for a given month):
Total rows = 1,000 stores  5,000 active products  24 months
–
= 120,000,000 rows

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Variations of the Star Schema
• Multiple Facts Tables
– Can improve performance
– Often used to store facts for different combinations of
dimensions
– Conformed dimensions
• Factless Facts Tables
– No non-key data, but foreign keys for associated
dimensions
– Used for:
▪ Tracking events
▪ Inventory coverage

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 9-13 Conformed Dimensions
Two fact tables connect two star schemas. Conformed dimension is
associated with multiple fact tables.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 9-14 Factless Fact Table
No data in fact table, just keys associating dimension records. Fact
table forms an n-ary relationship between dimensions

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Normalizing Dimension Tables
• Multivalued Dimensions
– Facts qualified by a set of values for the same business subject
– Normalization involves creating a table for an associative entity
between dimensions
• Hierarchies
– Sometimes a dimension forms a natural, fixed-depth hierarchy
– Design options
▪ Include all information for each level in a single denormalized
table
▪ Normalize the dimension into a nested set of 1: N
table relationships

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 9-15 Multivalued Dimension
A helper table is an associative entity that implements a M : N
relationship between dimension and fact.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 9-16 Fixed Product Hierarchy
Dimension hierarchies help to provide levels of aggregation for users
wanting summary information in a data warehouse.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Slowly Changing Dimensions (SCD)
• How to maintain knowledge of the past
• Kimball’s approaches:
– Type 1: just replace old data with new (lose historical
data)
– Type 2: for each changing attribute, create a current
value field and several old-valued fields (multivalued)
– Type 3: create a new dimension table row each time
the dimension object changes, with all dimension
characteristics at the time of change — most common
approach

1. Use atomic facts

2. Create single-process fact tables
3. Include a date dimension for each fact table
4. Enforce consistent grain
5. Disallow null keys in fact tables

6. Honor hierarchies
7. Decode dimension tables
8. Use surrogate keys
9. Conform dimensions
[Link] requirements with actual data

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Integration
• Data integration creates a unified view of business data
• Other possibilities:
– Application integration
– Business process integration
– User interaction integration
• Any approach requires changed data capture (CDC)
– Indicates which data have changed since previous
data integration activity

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Techniques for Data Integration
• Consolidation (ETL)
– Consolidating all data into a centralized database
(like a data warehouse)
• Data federation (EII)
– Provides a virtual view of data without actually
creating one centralized database
• Data propagation (EAI and EDR)
– Duplicate data across databases, with near real-
time delay

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
The Reconciled Data Layer (1 of 2)
• Typical operational data is:
– Transient, not historical
– Not normalized (perhaps due to denormalization for
performance)
– Restricted in scope, not comprehensive
– Sometimes poor quality, containing inconsistencies
and errors

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
The Reconciled Data Layer (2 of 2)
• After ETL, data should be:
– Detailed, not summarized yet
– Historical, periodic
– Normalized, 3rd normal form or higher
– Comprehensive, enterprise-wide perspective
– Timely, data should be current enough to assist
decision making
– Quality controlled, accurate with full integrity

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
The ETL Process
• ETL = Extract, transform, and load
– Capture/Extract
– Scrub or data cleansing
– Transform
– Load and Index
• When is ETL done?
– During initial load of Enterprise Data Warehouse
(EDW)
– During subsequent periodic updates to EDW

• Selection – the process of partitioning data according to

predefined criteria
• Joining – the process of combining data from various
sources into a single table or view
• Normalization – the process of decomposing relations
with anomalies to produce smaller, well-structured
relations
• Aggregation – the process of transforming data from
detailed to summary level

Chapter 10
Big Data Technologies

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Introduction
• Big Data
– Data that exist in very large volumes and
many different varieties (data types) and that
need to be processed at a very high velocity
(speed).
• Analytics
– Systematic analysis and interpretation of
data—typically using mathematical, statistical,
and computational tools—to improve our
understanding of a real-world domain.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Characteristics of Big Data (1 of 2)
• The Five V’s of Big Data
– Volume – much larger quantity of data than typical
for relational databases
– Variety – lots of different data types and formats
– Velocity – data comes at very fast rate (e.g. mobile
sensors, Web click stream)
– Veracity – traditional data quality methods don’t
apply; how to judge the data’s accuracy and
relevance?
– Value – big data is meaningless if it does not
provide value toward some meaningful goal
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Characteristics of Big Data (2 of 2)
• Schema on Read, rather than Schema on Write
– Schema on Write – preexisting data model, how
traditional databases are designed (relational
databases)
– Schema on Read – data model determined later,
depends on how you want to use it (XML, JSON)
– Capture and store the data, and worry about how you
want to use it later
• Data Lake
– A large integrated repository for internal and external
data that does not follow a predefined schema
– Capture everything, dive in anywhere, flexible access
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 10-1 Examples of JSON and XML

JSON = JavaScript Object Notation XML = eXtensible Markup Language

Traditional
database
design

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
NoSQL
• NoSQL = Not Only SQL
• A category of recently introduced data storage and retrieval
technologies not based on the relational model
• Scaling out rather than scaling up
• Natural for a cloud environment
• Supports schema on read
• Largely open source
• Not ACID compliant!
• BASE – basically available, soft state, eventually consistent

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
NoSQL Classifications (1 of 2)
• Key-value stores
– A simple pair of a key and an associated collection of
values. Key is usually a string. Database has no
knowledge of the structure or meaning of the values.
– Example: Redis
• Document stores
– Like a key-value store, but “document” goes further
than “value”. Document is structured so specific
elements can be manipulated separately.
– Example: MongoDB

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
NoSQL Classifications (2 of 2)
• Wide-column stores
– Rows and columns. Distribution of data based on
both key values (records) and columns, using
“column groups/families”
– Example: Apache Cassandra
• Graph-oriented database
– Maintain information regarding the relationships
between data items. Nodes with properties.
Connections between nodes (relationships) can also
have properties.
– Example Neo4j
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 10-3 Four-Part Figure Illustrating
NoSQL Databases
Some of the example
structures have been
adapted from Kauhanen
(2010)

Blank

Key-Value Store Document Store Column Oriented Graph

Performance high high high variable

Scalability high variable/high high variable

Flexibility high high moderate high

Complexity none low low high

Functionality variable variable (low) minimal graph theory

Source: [Link]/bscofield/nosql-codemash-2010 . Courtesy of Ben Scofield.

• BSON-based storage format (Binary JSON)

• Collections
– Equivalent to tables in a relational database
– A set of documents intended to be stored together

• Documents
– Equivalent to rows in a relational database
– Documents do not need to have the same structure (unlike rows)
– _id property for uniquely identifying a row

• Relationships
– _id property serves as “primary key”
– Another document can have a “foreign” key as another JSON property
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 10-5 Sample MongoDB
Collections
a) A document in the Product collection b) A document in the Author
collection

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Hadoop
• Hadoop is an open source implementation framework of
MapReduce
• MapReduce is an algorithm for massive parallel processing
of various types of computing tasks
• Hadoop Distributed File System (HDFS) is a file system
designed for managing a large number of potentially very
large files in a highly distributed environment
• Hadoop is the most talked about Big-Data data management
product today
• Hadoop is a good way to take a big problem and allow many
computers to work on it simultaneously
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Hadoop Distributed File System (HDFS)
• A file system, not a DBMS, not relational
• Breaks data into blocks and distributes them on various
computers (nodes) throughout a Hadoop cluster
• Each cluster consists of a NameNode (master server) and
some DataNodes (slaves)
• Overall control through YARN (“yet another resource
allocator”)
• No updates to existing data in files, just appending to files
• “Move computation to the data”, not vice versa

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
MapReduce
• Enables parallelization of data storage and computational
problem solving via many commodity servers
• Programmers don’t have to be experts at parallel processing
• Core idea – divide a computing task so that multiple nodes
can work on it at the same time
• Each node works on local data doing local processing
• Two stages:
– Map stage – divide for local processing
– Reduce stage – integrate the results of the individual
map processes

MapReduce: Simplified Data Processing on Large Clusters, Jeff Dean, Sanjay Ghemawat, Google, Inc.,

[Link] . Courtesy of the authors.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Other Hadoop Components
• Pig
– A tool that integrates a scripting language and an execution
environment intended to simplify the use of MapReduce
– Useful development tool
• Hive
– Supports management and querying of large data sets
– Hive QL – SQL-like language for managing Hadoop data
– Useful for ETL tasks
• HBase
– A wide-column store database that runs on top of HDFS
– Not as popular as Cassandra
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 10-16 Sample Data Set for Pig and
Hive Examples

Sample data in CSV (comma separated values). This is just a text file.

LOAD reads the data as tuples. USING specifies the separator between
fields. AS specifies the names and data types for each item on a line.
DUMP returns the results.

FILTER is like a WHERE clause in SQL. FOREACH loops through

the rows. GENERATE specifies the values returned (kind of like the
SELECT clause in SQL).

Hive is a SQL-like language. Like Pig, it can apply to CSV text files and
incorporated Schema on Read.

• Some vendors are bringing together traditional data

warehousing and big data capabilities
• Examples
– HP HSAVEn – Hewlett Packard technologies
combined with Hadoop open source and an analytics
engine
– Teradata Aster – integrate SQL, graph analysis,
MapReduce, R
– IBM Big Data Platform – combine IBM technologies
with Hadoop, JSON Query Language (JAQL),DB2,
Netezza
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 10-31 Teradata Aster Discovery
Portfolio

Source: [Link] . Courtesy of Teradata Corporation.

Source: [Link]

. Courtesy of Teradata Corporation.

Source: [Link]

. Courtesy of Teradata Corporation.

Chapter 11
Analytics and its
Implications

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Learning Objectives
11.1 Define terms
11.2 Articulate the differences between descriptive,
predictive, and prescriptive analytics
11.3 Describe the impact of advances in analytics on
data management technologies and practices
11.4 Analyze and articulate the implications and
potential consequences of the use of analytics
technologies on societies, organizations, and individuals

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Introduction
• Analytics
– Systematic analysis and interpretation of data—
typically using mathematical, statistical, and
computational tools—to improve our understanding
of a real-world domain
• Business Intelligence
– A set of methodologies, processes, architectures,
and technologies that transform raw data into
meaningful and useful information.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Types of Analytics
• Descriptive analytics
– Describes the past status of the domain of interest using a
variety of tools through techniques such as reporting, data
visualization, dashboards, and scorecards
• Predictive analytics
– Applies statistical and computational methods and models to
data regarding past and current events to predict what might
happen in the future
• Prescriptive analytics
– Uses results of predictive analytics along with optimization and
simulation tools to recommend actions that will lead to a desired
outcome

BI&A 1.0 – Focus on structured quantitative data largely from relational

databases
BI&A 2.0 – Include data from the Web (Web interaction logs, customer
reviews, social media)
BI&A 2.0 – Include data from mobile devices, (location, sensors, etc.)
and Internet of Things

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Use of Descriptive Analytics
• Descriptive analytics was original emphasis of BI
• Reporting of aggregate quantitative query results
• Tabular or data visualization displays
• Dashboard – a few key indicators
• Scorecard – like a dashboard, but broader range
• OLAP – online analytical processing

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
SQL OLAP Querying
• SQL is generally not an analytic language, but it can be
used for analysis.
• However, OLAP extensions to SQL make this easier.
• OLAP queries should support:
– Categorization – e.g., group data by dimension
characteristics
– Aggregation – e.g., create averages per category
– Ranking – e.g., find customer in some category with
highest average monthly sales

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Regular SQL Query
Which customer has bought the most of each product we sell? Show the
product ID and description, customer ID and name, and the total quantity
sold of that product to that customer; show the results in sequence by
product ID.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
OLAP SQL Query
Consider a SalesHistory table (columns Territory ID, Quarter, and
Sales) and the desire to show a three-quarter moving average of sales.

TerritoryID Quarter Sales 3QtrAverage

Atlantic 1 20 20
Atlantic 2 10 15
Atlantic 3 6 12
Atlantic 4 29 15
East 1 5 5
East 2 7 6
East 3 12 8
East 4 11 10
Blank Blank Blank

• Online Analytical Processing (OLAP) – the use of a set of

graphical tools that provides users with multidimensional
views of their data and allows them to analyze the data
using simple windowing techniques
• Relational OLAP (ROLAP) – OLAP tools that view the
database as a traditional relational database in either a
star schema or other normalized or denormalized set of
tables
• Multidimensional OLAP (MOLAP) – OLAP tools that load
data into an intermediate structure, usually a three- or
higher-dimensional array.
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 11-3 Slicing a Data Cube
Slicing, dicing, pivoting, and drill-down are useful cube operations

SofTowel 3-pack $100

SofTowel 6-pack $50

Brand Package size Color Sales

(b) Drill-down with color SofTowel 2-pack White $30
attribute added SofTowel 2-pack Yellow $25
SofTowel 2-pack Pink $20
SofTowel 3-pack White $50
SofTowel 3-pack Green $25
SofTowel 3-pack Yellow $25
SofTowel 6-pack White $30
SofTowel 6-pack Yellow $20

Starting with summary data, users can obtain details for particular cells.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 11-5 Sample Pivot Table With Four Dimensions:
Country (Pages), Resort Name (Rows), Travel Method,
and No. of Days (Columns)

Country (pages), Resort Name (rows), Travel Method, and No. of

Days (columns)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Visualization
• Representation of data in graphical and multimedia
formats for human analysis
• “A picture tells a thousand words”
• Without showing precise values, graphs and charts can
depict relationships in the data
• Often used in dashboards, as shown in next slide

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Figure 11-7 Sample Dashboard
Business Performance
Management (BPM)
systems allow
managers to measure,
monitor, and manage
key activities and
processes to achieve
organizational goals.
Dashboards are often
used to provide an
information system in
support of BPM.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Predictive Analytics
• Statistical and computational methods that use data
regarding past and current events to form models
regarding what might happen in the future
• Examples: classification trees, linear and logistic
regression analysis, machine learning, neural networks,
time series analysis, Bayesian modeling

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Mining Tools
• Knowledge discovery using a sophisticated blend of
techniques from traditional statistics, artificial intelligence,
and computer graphics
• Goals:
– Explanatory – explain observed events or conditions
– Confirmatory – confirm hypotheses
– Exploratory – analyze data for new or unexpected
relationships
• Text mining – Discovering meaningful information
algorithmically based on computational analysis of
unstructured textual information
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Mining Techniques (1 of 2)
• Regression
– Test or discover relationships from historical data
• Decision tree induction
– Test or discover if … then rules for decision propensity
• Clustering and signal processing
– Discover subgroups or segments
• Affinity
– Discover strong mutual relationships
• Sequence association
– Discover cycles of events and behaviors
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Mining Techniques (2 of 2)
• Case-based reasoning
– Derive rules from real-world case examples
• Rule discovery
– Search for patterns and correlations in large data sets
• Fractals
– Compress large databases without losing information
• Neural nets
– Develop predictive models based on principles
modeled after the human brain

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Typical Data Mining Applications (1 of 3)
• Profiling populations
– Developing profiles of high-value customers, credit
risks, and credit card fraud
• Analysis of business trends
– Identifying markets with above-average (or below-
average) growth
• Target marketing
– Identifying customers (or customer segments) for
promotional activity

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Typical Data Mining Applications (2 of 3)
• Usage analysis
– Identifying usage patterns for products and services
• Campaign effectiveness
– Comparing campaign strategies for effectiveness
• Product affinity
– Identifying products purchased concurrently or identifying
the characteristics of shoppers for certain product groups
• Customer retention and churn
– Examining the behavior of customers who left for
competitors to prevent remaining customers from leaving

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Typical Data Mining Applications (3 of 3)
• Profitability analysis
– Determining which customers are profitable, given
the total set of activities the customer has with the
organization
• Customer value analysis
– Determining where valuable customers are at
different stages in their life
• Upselling
– Identifying new products or services to sell to a
customer based on critical events and lifestyle
changes
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
KNIME Example of Predictive Analytics

• Credit scoring
– Takes past financial data to produce a credit score
– Starts with decision trees, neural networks, and
support vector machine (SVM) algorithms for initial
model
– Next uses Predictive Modeling Markup Language
(PMML)
• Marketing
– Churn analysis – predicting which customers will
leave using clustering via k-means algorithm
– Social media analysis using association rules
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Use of Prescriptive Analytics
• Use of optimization and simulation tools for prescribing
the best action to take
• Example applications
– Making trading decisions in securities and stock
market
– Making pricing decisions for airlines and hotels
– Making product recommendations (e.g. Amazon and
Netflix)
• Often requires predictive analytics and game theory

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Languages and Tools for Analytics
• R
– An open source statistical programming environment for data
analytics supported by a large developer community and
extended by thousands of packages for a variety of purposes
• Python
– A general-purpose, cross-platform, open source programming
language of choice for projects that require integration of
analytical capabilities with other types of computing needs
• Apache SPARK
– An open source analytics environment for large, heterogeneous
data sets that provides capabilities from analytics to the
maintenance of broadly distributed data storage systems

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Management Infrastructure for
Analytics
• Scalability
– ability to add capacity (processing resources, storage space, and
connectivity) based on changes in demand
• Parallelism
– capability to process, transfer, and access data in multiple
chunks at the same time
• Low latency
– high speed in various processing and data access and writing
tasks
• Data optimization
– skills needed to design optimal storage and processing
structures
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Technologies Enabling Infrastructure
Advances in Data Management
• Massively parallel processing (MPP)
– divide a computing task (such as query processing) between
multiple processors, speeding it up significantly
• In-memory DBMS
– keep the entire database in primary memory, thus enabling
significantly faster processing
• In-database analytics
– no need to move large quantities of data to separate analytics
tools for processing
• Columnar DBMS
– reorient the data in the storage structures, leading to efficiencies
in data warehousing and analytics applications
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Big Data and Analytics Impact:
Applications

• Business
• E-government and politics
• Science and technology
• Smart health and well-being
• Security and public safety

• Personal privacy v s collective benefit

ersu

• Ownership and access

• Data/algorithm quality and reuse
• Transparency and validation
• Demands for workforce capabilities and education

Chapter 12
Data and Database
Administration with Focus
on Data Quality

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Ineffective Data Administration Leads to
Poor Data Quality
• Multiple definitions, inconsistent representation of the same data
entity/elements
• Missing key data elements
• Low data quality levels due to inappropriate sources of data or timing
of data transfers
• Inadequate familiarity with existing data
• Poor and inconsistent query response time, excessive database
downtime, and either stringent or inadequate controls
• Lack of access to data due to damaged, sabotaged, or stolen files or
due to hardware failures
• Embarrassment to the organization
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data and Database Administration
Definitions

• Data Administration
– A high-level function that is responsible for the
overall management of data resources in an
organization, including maintaining corporate-wide
definitions and standards
• Database Administration
– A technical function that is responsible for physical
database design and for dealing with technical
issues such as security enforcement, database
performance, and backup and recovery

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Database Administration Functions
• Analyzing and designing databases
• Selecting DBMS and software tools
• Installing/upgrading DBMS
• Tuning database performance
• Improving query processing performance
• Managing data security, privacy, and integrity
• Data backup and recovery

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Traditional Data Administration
Functions
• Data policies, procedures, standards
• Planning
• Data conflict (ownership) resolution
• Managing the information repository for:
– Data definitions, business rules, and data relationships
– Automated data modeling and design tools
– Applications that access and manipulate data
– Database management systems
• Internal marketing of DA concepts

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Trends in Database Administration
• Increased use of procedural logic
• Proliferation of Internet-based applications
• Increased use of mobile smart devices
• Cloud computing and database/data administration

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Open Source DB Management (1 of 2)
• Open Source DBMSs: alternative to proprietary packages
– Examples: MySQL, PostgreSQL
• Advantages:
– Pool of volunteer developers and testers
– Less expensive than proprietary packages
– Source code available for modification
• Disadvantages
– Absence of complete documentation
– Ambiguous licensing concerns
– Not as feature-rich as proprietary DBMSs
– Vendors may not have certification programs

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Open Source DB Management (2 of 2)
• Considerations when selecting an open source DBMS
– Features
– Support
– Ease of use
– Stability
– Speed
– Training
– Licensing

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Governance
• Data governance
– High-level organizational groups and processes
overseeing data stewardship across the organization
• Data steward
– A person responsible for ensuring that organizational
applications properly support the organization’s data
quality goals

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Requirements for Data Governance
• Sponsorship from both senior management and business
units
• A data steward manager to support, train, and coordinate
data stewards
• Data stewards for different business units, subjects,
and/or source systems
• A governance committee to provide data management
guidelines and standards

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Importance of Data Quality
• If the data are bad, the business fails. Period.
– GIGO – garbage in, garbage out
– Sarbanes-Oxley (SOX) compliance by law sets data
and metadata quality standards
• Purposes of data quality
– Minimize IT project risk
– Make timely business decisions
– Ensure regulatory compliance
– Expand customer base

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Characteristics of Quality Data (1 of 2)
• Uniqueness
– Each entity exists only once within the database
• Accuracy
– Data correctly represents the real-life objects it models
• Consistency
– Values for data in one data set agree with the values for
related data in another data set
• Completeness
– Data having assigned values if they need to have values

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Characteristics of Quality Data (2 of 2)
• Timeliness
– Data is available when it is needed without excessive
delays
• Currency
– Data is recent enough to be useful
• Conformance
– Data is stored, exchanged, or presented in a format as
specified by their metadata
• Referential integrity
– Data that refer to other data are unique and satisfy
requirements to exist
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Causes of Deteriorated Data Quality
• External data sources
– Lack of control over data quality
• Redundant data storage and inconsistent metadata
– Proliferation of databases with uncontrolled
redundancy and metadata
• Data entry
– Poor data capture controls
• Lack of organizational commitment
– Not recognizing poor data quality as an
organizational issue
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Steps in Data Quality Improvement
• Get business buy-in
• Perform data quality audit
• Establish data stewardship program
• Improve data capture processes
• Apply modern data management principles and
technology
• Apply total quality management (TQM) practices

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Business Buy-In
• Executive sponsorship
• Building a business case
• Prove a return on investment (ROI)
• Avoidance of cost
• Avoidance of opportunity loss

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Quality Audit
• Statistically profile all data files
• Document the set of values for all fields
• Analyze data patterns (distribution, outliers, frequencies)
• Verify whether controls and business rules are enforced
• Use specialized data profiling tools

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Stewardship Program
• Roles:
– Oversight of data stewardship program
– Manage data subject area
– Oversee data definitions
– Oversee production of data
– Oversee use of data
• Report to: business unit v s IT organization?
ersu

• Chief data officer

– Executive level position accountable for all data-
related activities in the enterprise
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Improving Data Capture Processes
• Automate data entry as much as possible
• Manual data entry should be selected from preset
options
• Use trained operators when possible
• Follow good user interface design principles
• Immediate data validation for entered data

• Software tools for analyzing and correcting data quality

problems:
– Pattern matching
– Fuzzy logic
– Expert systems
• Sound data modeling and database design

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
TQM Principles and Practices
• TQM – Total Quality Management
• TQM Principles:
– Defect prevention
– Continuous improvement
– Use of enterprise data standards
• Balanced focus
– Customer
– Product/Service
– Strong foundation of measurement

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Availability
• Cost of downtime by business type
– Financial services/retail brokerage – $6.45 million
– Financial services/credit authorization – $2.6 million
– Retail/catalog sales center – $90,000
– Travel/reservation centers – $89,500
– Logistics/shipping services – $28,250
– Based on Mullins (2012, p. 273).

Availability Minutes Hours Cost per Year

99.999% 5 .08 $8,000

99.99% 53 .88 $88,000

99.9% 526 8.77 $877,000

99.5% 2,628 43.8 $4,380,000

99% 5,256 87.6 $8,760,000

Based on Mullins (2012, p. 273).

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Measures to Ensure Availability
• Hardware failures – provide redundancy for fault
tolerance
• Loss of data – database mirroring
• Human error – standard operating procedures, training,
documentation
• Maintenance downtime – automated and non-disruptive
maintenance utilities
• Network problems – careful traffic monitoring, firewalls,
and routers

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Master Data Management (MDM)
• Disciplines, technologies, and methods to ensure the
currency, meaning, and quality of reference data within
and across various subject areas
• Three main architectures
– Identity registry – master data remains in source
systems; registry provides applications with location
– Integration hub – data changes broadcast through
central service to subscribing databases
– Persistent – central “golden record” maintained; all
applications have access. Requires applications to
push data. Prone to data duplication.
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Modern Database Management
Thirteenth Edition

Chapter 13
Distributed Databases

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Definitions
• Distributed Database: A single logical database spread
physically across computers in multiple locations that are
connected by a data communications link
• Decentralized Database: A collection of independent
databases on non-networked computers
• These are not the same thing

• Business unit autonomy and distribution

• Data sharing
• Data communication reliability and costs
• Multiple application vendors
• Database recovery
• Transaction and analytic processing

Source: Based on Bell and Grimson (1992)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Distributed Database Options (1 of 2)
• Homogeneous
– same DBMS at each node
– Autonomous – independent DBMSs
– Non-autonomous – central, coordinating DBMS
– Easy to manage, difficult to enforce
• Heterogeneous
– different DBMSs at different nodes
– Systems – with full or partial DBMS functionality
– Gateways – simple paths are created to other databases
without the benefits of one logical database
– Difficult to manage, preferred by independent organizations
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Distributed Database Options (2 of 2)
• Systems (Heterogeneous) – supports some or all functionality
of one logical database
• Full DBMS Functionality – all distributed DB functions
• Partial-multidatabase – some distributed DB functions
– Federated – supports local databases for unique data
requests
▪ Loose Integration – local databases have own schemas
▪ Tight Integration – local databases use common
schema
– Un-federated – requires all access to go through a central,
coordinating module

• Data is distributed across all the nodes

• Same DBMS at each node
• All data is managed by the distributed DBMS (no
exclusively local data)
• All access is through one, global schema
• The global schema is the union of all the local schema

All nodes have identical DBMSs

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Typical Heterogeneous Environment
• Data distributed across all the nodes
• Different DBMSs may be used at each node
• Local access is done using the local DBMS and schema
• Remote access is done using the global schema

Nodes can have different DBMSs

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Major Objectives
• Location Transparency
– User does not have to know the location of the data
– Data requests automatically forwarded to appropriate
sites
• Local Autonomy
– Local site can operate with its database when
network connections fail
– Each site controls its own data, security, logging,
recovery

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Significant Trade-Offs
• Synchronous Distributed Database
– All copies of the same data are always identical
– Data updates are immediately applied to all copies
throughout network
– Good for data integrity
– High overhead, slow response times
• Asynchronous Distributed Database
– Some data inconsistency is tolerated
– Data update propagation is delayed
– Lower data integrity
– Less overhead, faster response time
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Advantages of Distributed Over
Centralized Databases

• Increased reliability/availability
• Local control over data
• Modular growth
• Lower communication costs
• Faster response for certain queries

• Software cost and complexity

• Processing overhead
• Data integrity exposure
• Slower response for certain queries

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Replication Advantages
• Reliability
• Fast response
• May avoid complicated distributed transaction integrity
routines (if replicated data is refreshed at scheduled
intervals)
• Decouples nodes (transactions proceed even if some
nodes are down)
• Reduced network traffic at prime time (if updates can be
delayed)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Data Replication Disadvantages
• Additional requirements for storage space
• Additional time for update operations
• Complexity and cost of updating
• Integrity exposure of getting incorrect data if replicated
data is not updated simultaneously
• Data replication is better when used for non-volatile
(read-only) data

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Types of Data Replication
• Push Replication
– updating site sends changes to other sites
• Pull Replication
– receiving sites control when update messages
will be processed

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Types of Push Replication (1 of 2)
• Snapshot Replication
– Changes periodically sent to master site
– Master collects updates in log
– Full or differential (incremental) snapshots
– Dynamic v s shared update ownership
ersu

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Types of Push Replication (2 of 2)
• Near Real-Time Replication
– Broadcast update orders without requiring
confirmation
– Done through use of triggers
– Update messages stored in message queue until
processed by receiving site

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
When to Use Data Replication
• Data timeliness–high tolerance for out-of-date data may
be required
• DBMS capabilities–if DBMS cannot support multi-node
queries, replication may be necessary
• Performance implications–refreshing may cause
performance problems for busy nodes
• Network heterogeneity–complicates replication
• Network communication capabilities–complete refreshes
place heavy demand on telecommunications

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Horizontal Partitioning
• Different rows of a table at different sites
• Advantages
– Data stored close to where it is used improves efficiency
– Local access optimization leads to better performance
– Only relevant data being available helps with security
– Unions across partitions improves ease of query
• Disadvantages
– Accessing data across partitions leads to inconsistent
access speed
– No data replication increases backup vulnerability

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Vertical Partitioning
• Different columns of a table at different sites
• Advantages and disadvantages are the same as for
horizontal partitioning except that combining data across
partitions is more difficult because it requires joins
(instead of unions)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Five Distributed Database Strategies
• Centralized database, distributed access
• Replication with periodic snapshot update
• Replication with near real-time synchronization of
updates
• Partitioned, one logical database
• Partitioned, independent, nonintegrated segments

• Organizational forces – funding, autonomy, security

• Frequency and locality or clustering of reference to data
• Growth and expansion needs
• Technological capabilities
• Costs of managing complex technologies
• Need for reliable service

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Table 13-1 Comparison of Distributed
Database Design Strategies (1 of 2)
Strategy Reliability Expandability Communications Manageability Data Consistency
Overhead

Centralized Poor: Poor: Very High: Very Good: One Excellent:

Highly dependent Limitations are High traffic to one monolithic site All users always
on central server barriers to site requires little have the same
performance coordination data

Replicated with Good: Very Good: Low to Medium: Very Good: Medium:
snapshots Redundancy and Cost of Not constant, but Each copy is like Fine as long as
tolerated delays additional copies periodic snapshots every other one delays are
may be less than can cause bursts tolerated by
linear of network traffic business needs

Synchronized Excellent: Very Good: Medium: Medium: Medium to Very

replication Redundancy and Cost of Messages are Collisions add Good:
minimal delays additional copies constant, but some some complexity Close to precise
may be low and delays are to manageability consistency
synchronization tolerated
work only linear

Strategy Reliability Expandability Communications Manageability Data Consistency

Overhead

Integrated Very Good: Very Good: Low To Medium: Difficult: Very Poor:
partitions Effective use of New nodes get Most queries are Especially difficult Considerable effort;
partitioning and only data they local, but queries for queries that and inconsistencies
redundancy need without that require data need data from not tolerated
changes in from multiple sites distributed tables,
overall database can cause a and updates must
Design temporary Load be tightly
coordinated

Decentralize Good: Good: Low: Very Good: Low:

With Depends on only New sites Little if any need Easy for each site, No guarantees of
independent local database independent of to pass data or until there is a consistency; in fact,
partitions availability existing ones queries across the need to share data pretty sure of
network (if one across sites Inconsistency
exists)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Functions of a Distributed DBMS (1 of 2)
• Locate data with a distributed data dictionary
• Determine location from which to retrieve data and
process query components
• Translate between nodes with different local DBMSs
• Provide data management functions: security,
concurrency, deadlock control, query optimization,
failure recovery
• Provide data consistency (via multiphase commit
protocols)

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Functions of a Distributed DBMS (2 of 2)
• Provide a single logical database, physically distributed
(needs global primary key control)
• Be scalable
• Replicate data and stored procedure
• Transparently use residual computing power to improve
database processing performance
• Permit different nodes to run different DBMSs
• Allow different versions of application code to reside on
different nodes

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Local Transaction Steps
1. Application makes request to distributed DBMS.
2. Distributed DBMS checks distributed data repository
for location of data. Finds that it is local.
3. Distributed DBMS sends request to local DBMS.
4. Local DBMS processes request.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Global Transaction Steps (1 of 2)
1. Application makes request to distributed DBMS.
2. Distributed DBMS checks distributed data repository for
location of data. Finds that it is remote.
3. Distributed DBMS routes request to remote site.
4. Distributed DBMS at remote site translates request for
its local DBMS if necessary, and sends request to local
DBMS.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Global Transaction Steps (2 of 2)
5. Local DBMS at remote site processes request.
6. Local DBMS at remote site sends results to distributed
DBMS at remote site.
7. Remote distributed DBMS sends results back to
originating site.
8. Distributed DBMS at originating site sends results to
application.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Distributed DBMS Transparency
Objectives
• Location Transparency
– User/application does not need to know where data resides
• Replication Transparency
– User/application does not need to know about duplication
• Failure Transparency
– Either all or none of the actions of a transaction are
committed
– Each site has a transaction manager
▪ Logs transactions and before and after images
▪ Concurrency control scheme to ensure data integrity
– Requires special commit protocol
Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Two-Phase Commit Protocol (1 of 2)
• Prepare Phase
– A message is broadcast to every participating site,
asking whether that site is willing to commit its
portion of the transaction at that site. Each site
returns an “OK” or “not OK” message.
– An “OK” says that the remote site promises to allow
the initiating request to govern the transaction at the
remote database.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Two-Phase Commit Protocol (2 of 2)
• Commit Phase
– The originating site collects the messages from all sites.
– If all are “OK,” it broadcasts a message to all sites to
commit the portion of the transaction handled at each
site.
– If one or more responses are “not OK,” it broadcasts a
message to all sites to abort the transaction.
– If the transaction fails during the commit phase it is in
limbo.
– A limbo transaction can be identified by a timeout or
polling.

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Concurrency Control
• Concurrency Transparency
– Design goal for distributed database
– Appearance of serial transactions, even though
simultaneous transactions are occurring
• Time stamping
– Concurrency control mechanism
– Assign globally unique timestamp to each transaction
– Alternative to locks in distributed databases

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Query Optimization
• Query optimization plan: for a multi-site join the
distributed DBMS must decide where to access the data
and how to proceed with the join. Three step process:

1. Query decomposition
2. Data localization
3. Global optimization

• Semijoin operation: only the joining attribute of the query

is sent from one site to the other, rather than all selected
attributes

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Evolution of Distributed DBMS (1 of 2)
• “Unit of Work”
– All of a transaction’s steps
• Remote Unit of Work
– SQL statements originated at one location can be
executed as a single unit of work on a single remote
DBMS

Copyright © 2022, 2019, 2016 Pearson Education, Inc. All Rights Reserved
Evolution of Distributed DBMS (2 of 2)
• Distributed Unit of Work
– Different statements in a unit of work may refer to
different remote sites
– All databases in a single SQL statement must be at a
single site
• Distributed Request
– A single SQL statement may refer to tables in more
than one remote site
– May not support replication transparency or failure
transparency

This work is protected by United States copyright laws and is

provided solely for the use of instructors in teaching their
courses and assessing student learning. Dissemination or sale of
any part of this work (including on the World Wide Web) will
destroy the integrity of the work and is not permitted. The work
and materials from it should never be made available to students
except by instructors using the accompanying text in their
classes. All recipients of this work are expected to abide by these
restrictions and to honor the intended pedagogical purposes and
the needs of other instructors who rely on these materials.

CH01 PPT-1
No ratings yet
CH01 PPT-1
36 pages
Database Management Overview and Benefits
No ratings yet
Database Management Overview and Benefits
37 pages
Modern Database MGT Ch01 Lecture Notes
100% (3)
Modern Database MGT Ch01 Lecture Notes
37 pages
Database Management Overview and Process
No ratings yet
Database Management Overview and Process
37 pages
Modern Database Management Slides - ch01
100% (1)
Modern Database Management Slides - ch01
37 pages
Chapter 01 - The Database Environment and Development Process-2
No ratings yet
Chapter 01 - The Database Environment and Development Process-2
43 pages
CH01 PPT
No ratings yet
CH01 PPT
39 pages
Lec 1 2
No ratings yet
Lec 1 2
31 pages
CH 01
No ratings yet
CH 01
23 pages
The Database Environment and Development Process
No ratings yet
The Database Environment and Development Process
28 pages
Database Environment Overview and Development
No ratings yet
Database Environment Overview and Development
54 pages
The Database Environment and Development Process
No ratings yet
The Database Environment and Development Process
53 pages
CH01 - PPT - Updated - Recent Without
No ratings yet
CH01 - PPT - Updated - Recent Without
25 pages
Database Environment Overview
No ratings yet
Database Environment Overview
50 pages
Database Environment & Development Process
No ratings yet
Database Environment & Development Process
22 pages
The Database Environment and Development Process
No ratings yet
The Database Environment and Development Process
55 pages
Database Environment Overview and Development
No ratings yet
Database Environment Overview and Development
53 pages
Hoffer Mdm12e PP Ch01
No ratings yet
Hoffer Mdm12e PP Ch01
60 pages
Database Environment Roles Explained
No ratings yet
Database Environment Roles Explained
49 pages
1-The Database Environment and Development Process
No ratings yet
1-The Database Environment and Development Process
30 pages
Database Environment and Development Overview
0% (1)
Database Environment and Development Overview
47 pages
Intro To Database and Transaction (Part 2)
No ratings yet
Intro To Database and Transaction (Part 2)
29 pages
Database Environment and Development Overview
No ratings yet
Database Environment and Development Overview
51 pages
Hoffer Mdm11e PP Ch01 GE
No ratings yet
Hoffer Mdm11e PP Ch01 GE
53 pages
Understanding Database Systems Basics
No ratings yet
Understanding Database Systems Basics
67 pages
Database Environment Overview
No ratings yet
Database Environment Overview
53 pages
Database Fundamentals Overview
No ratings yet
Database Fundamentals Overview
50 pages
Hoffer Edm PP Ch01
No ratings yet
Hoffer Edm PP Ch01
51 pages
01 Introduction To Database
No ratings yet
01 Introduction To Database
43 pages
Database Management System Overview
No ratings yet
Database Management System Overview
6 pages
Database Environment and Development Overview
No ratings yet
Database Environment and Development Overview
387 pages
Database Development and Management Overview
No ratings yet
Database Development and Management Overview
52 pages
Overview of Database Environment
No ratings yet
Overview of Database Environment
52 pages
Database Development and Management Overview
No ratings yet
Database Development and Management Overview
18 pages
Laudon Mis16 PPT Ch06 KL CE
No ratings yet
Laudon Mis16 PPT Ch06 KL CE
65 pages
DB 1
No ratings yet
DB 1
29 pages
Understanding Database Structures
No ratings yet
Understanding Database Structures
60 pages
Advanced Database Management Concepts
No ratings yet
Advanced Database Management Concepts
19 pages
Database Environment and Development Overview
No ratings yet
Database Environment and Development Overview
25 pages
Understanding Data and Databases
No ratings yet
Understanding Data and Databases
28 pages
Database Management Overview and Benefits
No ratings yet
Database Management Overview and Benefits
48 pages
The Database Environment and Development Process
No ratings yet
The Database Environment and Development Process
334 pages
Importance of Databases in Data Management
No ratings yet
Importance of Databases in Data Management
9 pages
Database Systems Course Overview
No ratings yet
Database Systems Course Overview
15 pages
Database Environment & Development Proce
No ratings yet
Database Environment & Development Proce
30 pages
Database Environment and Development Guide
No ratings yet
Database Environment and Development Guide
51 pages
Comprehensive Database Management Guide
No ratings yet
Comprehensive Database Management Guide
62 pages
Expanded Notes - Introduction To Database Systems
No ratings yet
Expanded Notes - Introduction To Database Systems
7 pages
Understanding Database Management Systems
No ratings yet
Understanding Database Management Systems
25 pages
The Database Environment and Development Process: Modern Database Management 10 Edition
No ratings yet
The Database Environment and Development Process: Modern Database Management 10 Edition
49 pages
Database Environment Overview and Benefits
No ratings yet
Database Environment Overview and Benefits
55 pages
Components of Database System Environment
No ratings yet
Components of Database System Environment
37 pages
Database Principles and Design Fundamentals
No ratings yet
Database Principles and Design Fundamentals
83 pages
Chapter 1
No ratings yet
Chapter 1
50 pages
Intro DBMS
No ratings yet
Intro DBMS
26 pages
Database Planning, Design, and Administration: © Pearson Education Limited 1995, 2005
No ratings yet
Database Planning, Design, and Administration: © Pearson Education Limited 1995, 2005
56 pages
Data Management and Ethics Syllabus
No ratings yet
Data Management and Ethics Syllabus
52 pages