Transcript Chapter-3

Chapter 3
Database Management
Dr. Hassan Ismail
1
Chapter 3: Objectives
From text book



Understand why databases are important
to modern organizations
Understand how databases work
Understand how organizations can
maximize their strategic potential with
databases
2
Database Management for
Strategic Advantage


Database – a collection of related data organized
in a way to facilitate data searches
Publisher can use databases to:





Create a book
Track book sales
Set salaries and wages
Pay employees
DB management systems allow organization to retrieve, store
and analyze information easily .
3
Traditional File Environment
Each business function developed its files
accounting
Finance
Sales
& Marketing
file1
file2
file3
file1
file2
file3
file1
file2
file4
4
Traditional File Environment

Field: title – column

Record: a group of fields – entity

File: a group of records – table

Key: unique field to identify record

Database: a group of files + relationships
5
Problems





Data Redundancy and Confusion
Program-Data Dependence
Lack of Flexibility
Poor Security
Lack of Data-Sharing and Availability
6
DataBase Management System
DBMS is a software that permits an organization to centralize data,
manage them, and access by application programs
3 components
1
Data
Definition Language
Defines each data
element as it appears in
the database
2
Data
Manipulation Language
Language that end users
and programmers use to
manipulate data (SQL)
Structured Query Language
3
Data
Dictionary
For storing and
organizing information
about the data
maintained in DB
7
Advantages of the Database Approach

Program-data independence

Easy to change business needs

Minimal data redundancy

Single copy of data stored

Improved data consistency




Improved data sharing
Increased productivity of
application development



Enforcement of standards

Improved data quality

Improved data accessibility




Reduced program maintenance
Avoid redundancy => more data is
more consistent
Easy to deploy and control data from
centralized system
Data standards make it easier to
build and modify applications.
Centralized system make it easier
enforce rules for data
creation/modification/naming/deleti
on.
Centralized control, no redundancy,
enhance data quality
Easy to access from inside or outside
organization
Information changed in the central
DB is replicated seamlessly
throughout all applications.
8
…Foundation Concepts





DBMS : is used to interact with the databases, DBMS is a
software application with which you create, store
organize, and retrieve data from a single databases
Example: MS Access is an example of DBMS .
In DBMS, the individual Database is a collection of
related attributes about entities:
Entity: is collection of data that belong to the same
domain, or same meaning like people/class/ anima/
university etc…
Attribute (or Field) is an individual information that
describe the entity.
Entity is like table, where each row is a record and each column is
an attribute.
9
Effective Management of DB

The database administrator (DBA) is responsible
of the development and management of the
organization’s databases, he will:



Work with programmers and system analysts to design and
implement the database
Work with users and managers to establish database policies
Implement security features and establishes database
permissions
10
Key Database Activities

Entering and Querying Data



Form: GUI which presents the information to the user
in an intuitive way so that the user can easily see and
enter the data.
Structured Query Language (SQL): is the most
common language used to interface with databases
Query by example (QBE): it enables us to fill out a
grid, or template, in order to construct a sample data
we would like to see.
11
Key Database Activities

Creating Database Reports

Report – a compilation of data that is organized and produced in
printed format



Example:
quarterly sales report for restaurant.
Report Generators: retrieves data from database and manipulate
and display it in a useful format.
Design Database




Must be organized
Few or no redundancies
Data model – a map of entity relationships
Keys



Primary key - example Student ID
Combination primary key - example Student ID + Course ID + Section + Term
Secondary key- example Major: Used to identify one or more records within a
table that share a common value.
12
13
Key Database Activities

Database Associations



One-to-one (teams to stadiums)
One-to-many (player to team)
Many-to-many (players to games)
14
Key Database Activities

Entity-Relationship Diagramming (ERD)


Commonly used when designing databases
One draws entities (tables) as boxes and lines
between entities to show relationships
15
Key Database Activities

The Relational Model of Databases

Entities linked by a common key field



Records = rows
Fields = columns
Other models exist



Hierarchical
Network
Object-oriented model
16
Key Database Activities

Normalization


A technique for making complex databases more
efficient and more easily handled by the DBMS
Eliminates data redundancy
17
18
19
Key Database Activities

Data Dictionary

A document that explains each piece of
information in the database


Field name
Data type




Numeric, text, date/time
Useful for sorting and allocating storage
Is this field a key field?
Business rules


Update authority
Valid data values
20
How Organizations Get the
Most from Their Data

Linking Web Sites to Databases

Example: Amazon


2.5 million titles
Managing online data effectively
21
How Organizations Get the
Most from Their Data

Data Mining





A method for better understanding data
Information on customers, products, markets, etc.
Drill down: from summary to more detailed data
Sort and extract information
Trends, correlations, forecasting, statistics
22
How Organizations Get the
Most from Their Data

Data Mining

Online Transaction Processing (OLTP)



Immediate automated responses to user requests
Multiple concurrent transactions
A big part of interactive Internet e-commerce
23
How Organizations Get the
Most from Their Data

Data Mining

Online Analytical Processing (OLAP)




Graphical software tools that provide complex analysis of
data stored in a database
Drills down to deeper levels of consolidation
Time series and trend analysis
“What if” and “why” questions
24
How Organizations Get the
Most from Their Data

Data Mining

Merging Transaction and Analytical Processing


Real-time OLAP diminishes performance because the
database must be “locked” during execution time
Solution: replicate transactions on a 2nd database server
25
How Organizations Get the
Most from Their Data

Data Mining

Merging Transaction and Analytical Processing

Operational Systems



Interact with customers and run a business in real time
Examples: Order processing, reservation systems
Informational Systems

Support decision making based on stable point-in-time or
historical data
26
How Organizations Get the
Most from Their Data

Data Warehousing




Integrating multiple large databases into a single
repository
Queries, analysis, and processing
Purpose: put key business information into the hands of
decision makers
Cost: millions
27
How Organizations Get the
Most from Their Data

Data Marts





Instead of one large data warehouse, many organizations
create multiple data marts
Each contains a subset of the data
Example: finance, inventory, personnel
Each data mart is customized for particular DSS
applications
Cost: typically less than $1 million
28