Introduction

Modern Database Management

12th Edition
Jeffrey A. Hoffer, V. Ramesh,
Heikki Topi
Why do we need data?

Information: Context helps users Graphical displays turn data into useful
understand data information that managers can use for
decision making and interpretation
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

3
Definition: Metadata

 Data that describe the properties and context of user data

Data about the data

Descriptions of the properties or characteristics of the data,

including data types, field sizes, allowable values, and data
context
Why do we need databases?
How large are typical databases?
How large are typical databases?

• Designed to support one user

 Personal databases • Reside on Personal
computers/Laptops/smartphones
• Provide the ability to store data
• However the data cannot easily be shared

Steward - Personal Database Manager

How large are typical databases?

• Each member of the workgroup has a

 Personal databases computer
• They are linked by means of a network
 Workgroup databases • Each computer has a copy of the user
interface application (client) Drawback: A lot of functionality is
• The database is on the server programmed on the client computers
How large are typical databases?

• The user interface is accessible on

user’s computers (or smartphones)
 Personal databases • The application/Web server contains
 Workgroup databases the business logic required to do
business transactions
 Departmental/Divisional databases • It ‘talks’ to the database server
How large are typical databases?

• They support the entire organization

 Personal databases • An organization may have multiple
 Workgroup databases databases

 Departmental/Divisional databases
 Enterprise applications
Enterprise resource planning (ERP) systems
(transactional)
Data warehousing implementations Walmart’s operational system was
2.5 Petabytes (250 bytes) in 2008
And Apple’s Teradata system for
icloud is significantly larger
 What do we do with a database?

Enterprise Resource Planning (ERP) Recording every single event that

may take place in an organization:
Central db integrates all enterprise functions - Orders
(manufacturing, finance, sales, marketing, inventory, - Payments
accounting, human resources) - Class registration..
Contain the current operational data of the enterprise

Data Warehouse Harrah’s (now part of the Caesar’s

Integrated decision support system derived from various Entertainment casino empire)
understands how much money
operational databases particular gamblers can afford to
Contains collections of data from different db and lose in a day before they won’t
historical data come back the next day.
History of databases
History of databases
History of databases

The database now consists of a collection of records connected

to one another through links (pointers).

Network Model:

Hierarchical Model:

If an account belongs to more than

one customers, the data will have to
be replicated.

Note that: the set of all customer and account records is organized in the form
of a rooted tree, where the root of the tree is a dummy node.
A hierarchical database is a collection of such rooted trees, and hence forms a forest.
History of databases
Codd’s contribution was revolutionary:
1. He separated the data from the
physical information storage, and
2. He described a framework for
storing and retrieving data using
simple rows and tables

The breakthrough earned Codd a Turing Award.

History of databases

1970s: Two major relational database system

prototypes were created between the years 1974
and 1977, and they were the Ingres, which was
developed at UBC, and System R, created at IBM San
Jose

1980s: Structured Query Language, or SQL, became the

standard query language and Relational database
systems became a commercial success

1990s: new client tools for application development

were released, and these included the Oracle Developer,
PowerBuilder, VB, and others.
- The advent of the Internet led to exponential growth of
the database industry. Average desktop users began to
use client-server database
History of databases

2010s: NoSQL databases are becoming

popular.
- They reject the ‘relational
structuring of data’ and offer
increased speed
- The jury is undecided over whether
NoSQL will supplant the relational
model.
 Why Databases?

What is the difference with File Processing Systems?

Disadvantages of File Processing:

1. Program-Data Dependence
All programs maintain metadata for each file they use
Changes in the data file means changes in the program that is used to access it
Difficult to locate all the programs that access this file
Error prone process

Change of a field’s length in Customer data file

means changes in the code of up to 5 programs used
to access the data

19
Disadvantages of File Processing:

2. Duplication of Data
Different systems/programs have separate copies of the same data
Different departments of the same company are not always synchronized
Waste of storage space
Increased effort to maintain all the data
No reliable metadata: for example the same field may have different name,
datatype or length in different files

 The biggest problem:

 Data changes in one file could cause
Duplicate Data inconsistencies
 Compromises in data integrity

20
Disadvantages of File Processing:

3. Limited Data Sharing

No centralized control of data
Cloud facilities are not always allowed, when dealing with sensitive data

Limitation in available Information:

It is difficult to create a report requested by the
managers, if the data needed for the report must be
drawn from different departments

21
Disadvantages of File Processing:

4. Lengthy Development Times

For a new application new data are required
The Programmers must design new file formats
The Programmers must design new programs for accessing the new data
The Programmers must write documentation for all that ….

Waste of time and in today’s market:

time is money

22
Disadvantages of File Processing:

5. Excessive Program Maintenance

All the above combined together means an excessive amount of time and
money dedicated to maintaining data files.
Could be up to 80% of information systems
budget

23
 Why Databases?

A way to overcome all the flaws of file processing!

24
Solution: The Database Approach

This is the solution:

- Central repository of shared data
- Data is managed by a controlling agent
- Stored in a standardized, convenient form

What it requires?
A Database Management System (DBMS)
Database Management System

 A software system that is used to create, maintain, and provide

controlled access to user databases

Order Filing
System

Invoicing Central database

DBMS
System
Contains employee,
order, inventory,
Payroll pricing, and
customer data
System

DBMS manages data resources like an operating system manages hardware

resources
Database Management System
Advantages
1. Program-data independence
o The separation of data description (metadata) from the application programs
that access the data is called data Independence
o Changes in the metadata do not lead to changes in the application programs

2. Planned data redundancy

o Depends on the design of the database
o Good design means little to no redundancy

3. Improved data consistency

For example in this file:

• Capital and lower case
• Dates written in different formats
• First and last name written in different order
• Undefined values in different format
Database Management System
More Advantages

4. Enforcement of standards
o One central management by the DBA

5. Improved data quality

o Enforced by database constraints

6. Increased Data Security

o Concurrent access Control mechanisms
Example:
Imagine two users accessing the same data at the same time.
They both try to change the same field.
Which of the two entries should be maintained?
Database Management System
And More Advantages
7. Improved data sharing
o A database is designed as a shared corporate resource
o Access is granted to users

8. Improved data accessibility and responsiveness

o Easy access to data with SQL (Structured Query Language)

9. Increased application development productivity

o Initial requirements may be high
o But new applications are developed extremely fast

10. Reduced program maintenance

o Easy to make changes to data and their metadata

11. Improved decision support

o Some databases are designed for fast decision support (DW)
The Database Approach – Definitions

30
Elements of the Database Approach:
1. Data models
Graphical system capturing the nature and relationships 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

Enterprise Data Model

Project Data Model

Elements of the Database Approach:
2. Entity Types
Noun form describing a person, place, object, event, or concept
Composed of attributes

Entity types:
CUSTOMER, ORDER, PRODUCT

Attributes:
The data you are interested in
capturing about an entity.
Example: Customer name or address
Data are recorded for many
customers. Each customer’s
information is an Entity Instance of
CUSTOMER entity type
Elements of the Database Approach:
3. Relationships
Between entities
Can be one-to-one (1:1), one-to-many (1:M) or many-to-many (M:N)

One customer may

place many orders, but
each order is placed by
a single customer
 One-to-many
relationship
Elements of the Database Approach:
3. Relationships
Between entities
Usually one-to-many (1:M) or many-to-many (M:N)

One order has many

order lines; each order
line is associated with
a single order
 One-to-many
relationship
Elements of the Database Approach:
3. Relationships
Between entities
Usually one-to-many (1:M) or many-to-many (M:N)

One product can be in

many order lines, each
order line refers to a
single product
 One-to-many
relationship
Elements of the Database Approach:
3. Relationships
Between entities
Usually one-to-many (1:M) or many-to-many (M:N)

Therefore, one order

involves many
products and one
product is involved in
many orders

 Many-to-many
relationship
Elements of the Database Approach:
4. Relational Databases
Entities are represented as Relations
Relationships are represented by means of common field values. They
are referred to as: foreign keys

Attribute

ENTITY
Instance

ENTITY (TYPE)
Relational Model:

Consists of:
• Data structure
• Tables (relations), rows, columns
• Data integrity
• Mechanisms for implementing business rules that maintain
integrity of manipulated data
• Data manipulation
• Powerful SQL operations for retrieving and modifying data
Relational Model – Data Structure:
In Flat Files everything is stored in one table:

In Relational Databases the data are separated into Relations, such that every table describes one and only one topic
(ENTITY): STUDENT data COURSE data TRAINER data

In Relational Databases the relationships between the data are maintained by Foreign keys:

 A table may have one primary key and

any number of Foreign keys

 A foreign Key is the Primary key of

another table.
Relational Model – Data Structure:

In Relational Databases the data are separated into Relations, such that every table describes one and only one topic
(ENTITY):

In Relational Databases the relationships between the data are maintained by Foreign keys.

In Relational Databases data redundancy is minimized.

Relational Model – Data Structure:
In Relational Databases using Foreign keys it is easy to recover the relationships among the data:

Example: Which students are taking classes held by Charles Hill?

Relational Model in more details:

Attribute

ENTITY
Instance

ENTITY

In Relational Databases the data are separated into Relations.

Relational Model in more details:
Relations

Is a Relation the same as a Table?

A relation is a named, two-dimensional table of data
A relation consists of rows (records-instances) and columns
(attribute or field) the same as a table
- But a Relation has additional Requirements:
 It must have a unique name.
 Every attribute value must be atomic (single valued)
 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
Relational Model in more details:
Relations

Another Example:
EMPLOYEE(EmployeeID, EmployeeName, DeptName, Salary, CourseTitle, DateCompleted)

A relation is a named, two-dimensional table of data

A relation consists of rows (records-instances) and columns
(attribute or field) the same as a table

- But take a look at the data:

Relational Model in more details:
Relations

Example:
EMPLOYEE(EmployeeID, EmployeeName, DeptName, Salary, CourseTitle, DateCompleted)

So, if we do not allow multivalued attributes we get a Relation:

Relational Model in more details:
Key Attributes

oKeys are special fields that serve two main purposes and are
1. Primary keys

2. Foreign keys

oKeys can be simple (a single field) or composite (more than one field)

oKeys usually are used as indexes to speed up the response to user queries
Relational Model in more details:
Key Attributes
1. Primary keys
 are unique identifiers of the relation
 They guarantee that all rows are unique
 Examples include employee numbers, social security numbers, etc
 Can be simple or Composite
Relational Model in more details:
Key Attributes
2. Foreign keys
 are identifiers that enable a dependent relation to refer to its parent relation
SAMPLE SCHEMA

Primary Key
Foreign Key (The Customer
that places an order must already exist
in the Customer table)

Combined, these are a composite

primary key (uniquely identifies the
order line)…individually they are
foreign keys
 Quiz
Identify all primary and foreign keys
For the foreign keys also identify the dependent and parent relation
 Quiz 2
Identify all primary and foreign keys
For the foreign keys also identify the dependent and parent relation
Relational Model:

Consists of:
• Data structure
• Tables (relations), rows, columns
• Data integrity
• Mechanisms for implementing business rules that maintain
integrity of manipulated data
• Data manipulation
• Powerful SQL operations for retrieving and modifying data
Relational Model – Data Integrity:

Integrity constraints
• They are Rules

• They limit acceptable values and actions

• Their purpose is to facilitate maintaining the accuracy and integrity of

data in the database.
Relational Model – Data Integrity:

Integrity constraints are of 3 major types:

o Domain Constraints
Allowable values for an attribute
o Entity Integrity
No primary key attribute may be null. All primary key
fields MUST have data.
o Referential Integrity
A rule that states that either each foreign key value must
match a primary key value in another relation or the
foreign key value must be null
Relational Model – Data Integrity:
o Referential Integrity in more detail:
Referential Integrity constraint is can be used with several rules that protect the integrity
of data
For example: Delete Rules
• Restrict–don’t allow delete of “parent” side if related rows exist in “dependent” side:
Cannot delete a customer if there are orders he placed in the ORDER relation
• Cascade–automatically delete “dependent” side rows that correspond with the “parent” side
row to be deleted
Delete the customer and the orders he placed in the ORDER relation
• Set-to-Null–set the foreign key in the dependent side to null if deleting from the parent side 
not allowed for weak entities
Delete the customer and set the Customer ID of his orders to NULL

55
Relational Model:

Consists of:
• Data structure
• Tables (relations), rows, columns
• Data integrity
• Mechanisms for implementing business rules that maintain
integrity of manipulated data
• Data manipulation
• Powerful SQL operations for retrieving and modifying data
CREATING RELATIONAL TABLES

 Create a table that describes

one Entity
 Define attributes
 Define Domain Constraints
(datatypes)
 Define Entity Integrity (PK)
 Define Referential Integrity
(FK)

57
Is that all?
Components of Database Environment:
o Database: A Collection of data

o DBMS: Software system used to create, maintain

and control access to the database

o Repository: Knowledge base for all metadata

o CASE tools (Computer Aided Software Engineering):

 Tools used to design databases and
 Create the data model and the database

o Application Programs and User Interface:

 Used to provide information to users (SQL)
 Allow users to interact with the database
People of Database Environment:
o DBA
The person responsible for the management of the
database
Manages technical issues such as storage and performance
o System developer
The person who designs and implements new applications
They may use CASE tools to develop new tables and add
data
o End User
Users who may add or delete data
They may also request information such as reports
o Other People Involved:
 Systems analysts: includes computer systems requirements
 Business analysts: gather requirements
 Database analysts and data modelers: design of the database component
 Programmers
 Database architects: data quality
 Data administrators: responsible for data quality
Definitions:
DATABASE SCHEMA

The internal schema: is the underlying design and

implementation. Has two Parts:
Logical: e.g relational covered in Chapter 4
Physical: e.g Oracle covered in Chapter 5
Definitions:
DATABASE SCHEMA

The conceptual schema: is a complete and coherent

view of the enterprise’s data
The data architect view or DBA
E-R models–covered in Chapters 2 and 3

The internal schema: is the underlying design and

implementation. Has two Parts:
Logical: e.g relational covered in Chapter 4
Physical: e.g Oracle covered in Chapter 5
Definitions:
DATABASE SCHEMA
The external schema: Different people have
different views of the database…these are subsets
of Conceptual Schema
For managers and end users

The conceptual schema: is a complete and coherent

view of the enterprise’s data
The data architect view or DBA
E-R models–covered in Chapters 2 and 3

The internal schema: is the underlying design and

implementation. Has two Parts:
Logical: e.g relational covered in Chapter 4
Physical: e.g Oracle covered in Chapter 5