Download - Database Chapter 01
-
8/10/2019 Database Chapter 01
1/59
CHAPTER 1:INTRODUCTION
1
Database Application Development
SSK 3408
-
8/10/2019 Database Chapter 01
2/59
Learning Objectives2
Define the following key terms:
Data, database and Database Management
System (DBMS), metadata.
Describe the advantages of using databaseapproach
Describe components of database
environment Briefly describe the evolution of database
systems.
-
8/10/2019 Database Chapter 01
3/59
Introduction3
Over the past two decades, there has beenenormous growth in the number and theimportance of database applications.
To store, manipulate and retrieve data in nearlyevery type of organizationbusiness, healthcare, education, government and libraries.
Database technology can be used by anindividual up to large number of users accessing
databases. The knowledge derived from databases can be
used:
Competitive advantage
Customer support
-
8/10/2019 Database Chapter 01
4/59
Introduction ..cont.4
DataBase Management System (DBMS) isone of the most powerful tools that can beused in building business databaseapplication.
Provide many features that representsignificant advantages over traditionalprogramming methods.
Understand the concepts of database design,queries and application building will reducethe time for developing complex applications
-
8/10/2019 Database Chapter 01
5/59
Build a Business Application5
Tools:
Database Design
SQL (queries)
Programming
Design
SQL
Program
Design
SQL
Program
Best:
Spend your timeon design and SQL.
Worst:
Compensate for poor design
and limited SQL with programming.
-
8/10/2019 Database Chapter 01
6/59
Basic Concepts and Definitions
Database:
A collection of data stored in a standardized format, designed to beshared by multiple users.
It may be of any size and complexity.
Data: Stored representation of objects and events that have meaning and
importance in the users environment.
Structured data or Unstructured/Multimedia data
Information:
Data that have been processed in such a way as to increase knowledgeof the person who uses the data.
Databases today may contain either data or information.
Metadata:
Data that describes the properties and context of user data
6
-
8/10/2019 Database Chapter 01
7/59
Example of Data7
-
8/10/2019 Database Chapter 01
8/59
8
Figure 1-1a Converting Data into Information
Context helps users understand data
-
8/10/2019 Database Chapter 01
9/59
9
Graphical displays turn data into usefulinformation that managers can use for
decision making and interpretation
-
8/10/2019 Database Chapter 01
10/59
-
8/10/2019 Database Chapter 01
11/59
Drawbacks of File Processing
Systems11
Program-Data Dependence
Duplication of Data
Inconsistent data
Limited data sharing
Lengthy development times
Excessive program maintenance
-
8/10/2019 Database Chapter 01
12/59
Program-data Dependency12
-
8/10/2019 Database Chapter 01
13/59
Problems with Data
Dependency File description are stored within each application
program that access a given file.
Consequently, any changes to a file structure requireschanges to the file description for all programs that
access to the file. It is often difficult to locate all programs affected by
such changes and consequently errors are oftenintroduced when making such changes.
Each application program must have its ownprocessing routines for reading, inserting, updatingand deleting data.
Lack of coordination and central control.
13
-
8/10/2019 Database Chapter 01
14/59
14
Duplicate
Data
-
8/10/2019 Database Chapter 01
15/59
Problems with Data
Redundancy
Waste of space to have duplicate data
Causes more maintenance headaches
The biggest problem:
When data changes in one file, could cause
inconsistencies
Compromises data integrity.
15
-
8/10/2019 Database Chapter 01
16/59
-
8/10/2019 Database Chapter 01
17/59
Example of File Method
Add to file (e.g. Cell phone)
Write code to copy employeefile and add empty cell phoneslot.
Find all programs that useemployee file.
Modify file definitions.
Modify reports (as needed)
Recompile, fix new bugs.
Easier: Keep two employee
files?
17
File Division
01 Employees
02 ID
02 Name
02 Address
01 Department
02 ID
02 . . .
COBOL
112 Davy Jones 999 Elm Street . . .
113 Peter Smith 101 Oak St . ..
Employee File
More programs
File Division
01 Employees
...
02 Cell Phone
-
8/10/2019 Database Chapter 01
18/59
Solution: The Database
Approach
Central repository of shared data
Data is managed by a controlling agent
Stored in a standardized, convenient form. Therefore, requires a database management
system (DBMS)
18
Order Filing
System
Invoicing
System
Payroll
System
DBMSCentral database
Contains employee,
order, inventory,
pricing, and
customer data
-
8/10/2019 Database Chapter 01
19/59
DBMS
A software system that is used to create, maintain, and
provide controlled access to user databases.
It provides systematic method of creating, updating,
storing, and retrieving data in a database.
It enables end users and application programmers to
share data and it enables data to be shared among
multiple application rather than propagated and stored in
new files for every new application.
It also provides facilities for controlling data access,
enforcing data integrity, managing concurrency control,
and restoring a database.
19
-
8/10/2019 Database Chapter 01
20/59
DBMS20
It provides interface between the various database applications for organizational
users and database
It allows users to share data and to query, access and update the stored data.
-
8/10/2019 Database Chapter 01
21/59
-
8/10/2019 Database Chapter 01
22/59
-
8/10/2019 Database Chapter 01
23/59
One customer may place
many orders, but each
order is placed by a
single customer
One-to-many
relationship
-
8/10/2019 Database Chapter 01
24/59
One order has many order
lines; each order line is
associated with a single order
One-to-many relationship
-
8/10/2019 Database Chapter 01
25/59
One product can be in many
order lines, each order line
refers to a single product
One-to-many relationship
-
8/10/2019 Database Chapter 01
26/59
Therefore, one order involves
many products and one product
is involved in many orders
Many-to-many relationship
-
8/10/2019 Database Chapter 01
27/59
Relationships established in special columns that provide links between tables
-
8/10/2019 Database Chapter 01
28/59
Client/server
system
architecture
-
8/10/2019 Database Chapter 01
29/59
29
Application program functions:
insertingnew data,
updatingexisting data,
deletingexisting data,
readingdata for display
Th R f D t b
-
8/10/2019 Database Chapter 01
30/59
The Range of Database
Applications30
-
8/10/2019 Database Chapter 01
31/59
Pearson Education Limited, 2004
31
Two-Tier Client-Server
Client manages main business and dataprocessing logic and user interface.
Server manages and controls access todatabase.
-
8/10/2019 Database Chapter 01
32/59
Pearson Education Limited, 2004
32
Two-Tier Client-Server
-
8/10/2019 Database Chapter 01
33/59
Pearson Education Limited, 2004
33
Three-Tier C-S Architecture
User interface layer runs on client.
Business logic and data processing layer
middle tier runs on a server (applicationserver).
DBMS stores data required by the middletier. This tier may be on a separate server(database server).
-
8/10/2019 Database Chapter 01
34/59
Pearson Education Limited, 2004
34
Three-Tier C-S Architecture
A E l f P l
-
8/10/2019 Database Chapter 01
35/59
An Example of Personal
Database35
A E l f W k
-
8/10/2019 Database Chapter 01
36/59
An Example of Workgroup
Database36
A E l f E i D
-
8/10/2019 Database Chapter 01
37/59
An Example of Enterprise Data
Warehouse37
-
8/10/2019 Database Chapter 01
38/59
Advantages of Database Approach38
Program-data independence
Data-description are stored in repository not
inside the application program
Minimal data redundancy
Database design goal to integrate data files
into a single, logical structure.
Improved data consistency
By eliminating redundancy, reduce the
opportunities for inconsistencies.
Improved data sharing
A database is designed to shared corporate
resource. Authorized internal and external
users are granted permission to use the
database.
Increased productivity of application
development
New development without worry the file
design
DBMS provides high level productivity tools
Enforcement of standards
Such as naming convention, data
quality standard and uniform
procedures for accessing,
updating and protecting data
Improved data quality
Through Integrity constraint
Improved data accessibility and
responsiveness
Without the knowledge of
programming experience can
retrieve and display data, even it
crosses departmental boundaries. Reduce program maintenance
C t d Ri k f D t b
-
8/10/2019 Database Chapter 01
39/59
Costs and Risks of Database
Approach39
C t f D t b
-
8/10/2019 Database Chapter 01
40/59
CASE Toolscomputer-aided software engineering
Repositorycentralized storehouse of metadata
Database Management System (DBMS)software formanaging the database
Databasestorehouse of the data
Application Programssoftware using the data
User Interfacelanguages, menus, and other facilities bywhich users interact with various system components such as
CASE tools, DBMS etc. Data/Database Administratorspersonnel responsible for
maintaining the database
System Developerspersonnel responsible for designingdatabases and software
End Userspeople who use the applications and databases
Components of Database
Environment40
C t f D t b
-
8/10/2019 Database Chapter 01
41/59
Components of Database
Environment41
Hardware
PC, Mainframe, Networkcomputer
Software
Programming Language Data
Procedures
Instructions and rules
People
Data and Database
Administrator, SystemDeveloper, End-user
Database Management
-
8/10/2019 Database Chapter 01
42/59
Database Management
Approach
Data is most important
Data defined first
Standard format
Access through DBMS
Queries, Reports, Forms
Application Programs
3GL Interface
Data independence
Change data definition without changing
code
Alter code without changing data
Move/split data without changing code
42
All Data
DBMS
Program1 Program2Queries
Reports
-
8/10/2019 Database Chapter 01
43/59
Modifying Data with DBMS
Add cell number to
employee table
Open table definition
Add data element
If desired, modify reports
Use report writer
No programming
Existing reports, queries,
code will all run as beforewith no changes.
43
Field Name Data Type Description
EmployeeID Number Autonumber..
TaxpayerID Text Federal ID
LastName Text
FirstName Text. . .
Phone Text
. . .
CellPhone Text Cellular . . .
-
8/10/2019 Database Chapter 01
44/59
DBMS Features/Components
Database engine
Storage, retrieval,
update
Enforce businessrules
Performance and
ability to handlelarge problems
Data dictionary
Query Processor
Utilities
Security
Report writer
Forms generator
(input screens)
Applicationgenerator
Communications
3GL Interface
44
-
8/10/2019 Database Chapter 01
45/59
DBMS Components45
All Data
Database EngineData Dictionary
Security
Query Processor
FormBuilder
ReportWriter
Communication
Network
3GL
Connector
Program
Application
Generator
-
8/10/2019 Database Chapter 01
46/59
DBMS Engine, Security, Utilities46
Data
Tables
ProductItemID Description
887 Dog food
946 Cat food
OrderOrderID ODate
9874 3-3-97
9888 3-9-97
CustomerCustomerID Name
1195 Jones
2355 Rojas
User Identification
Access RightsSecurity
Concurrency and
Lock Manager
UtilitiesBackup and
Recovery
Administration
Database
EngineProduct
ItemID Integer, Unique
Description Text, 100 char
Customer
CustomerID Integer, Unique
Name Text, 50 char Data
Dictionary
-
8/10/2019 Database Chapter 01
47/59
DBMS Query Processor47
All Data
Database Engine
Data Dictionary
Query Processor
-
8/10/2019 Database Chapter 01
48/59
-
8/10/2019 Database Chapter 01
49/59
Report Writer (Oracle)49
-
8/10/2019 Database Chapter 01
50/59
DBMS Input Form50
All Data
Database Engine
Data Dictionary
Query Processor
Form Builder
Input
Form
Design
-
8/10/2019 Database Chapter 01
51/59
Example of Commercial DBMS51
Oracle
Ingres
Informix (Unix) DB2, SQL/DS (IBM)
Access (Microsoft)
SQL Server (Microsoft +)
Many older (Focus, IMS, ...)
Many limited PC (dBASE, Paradox, )
-
8/10/2019 Database Chapter 01
52/59
Significance of using DBMS52
Applications change a lot, but same data.
Need for ad hocquestions and queries.
Need to reduce development times. Need shared data.
Improve quality of data.
Enable users to do more development.
-
8/10/2019 Database Chapter 01
53/59
Flat files - 1960s - 1980s
Hierarchical1970s - 1990s
Network1970s - 1990s
Relational1980s - present
Object-oriented1990s - present
Object-relational1990s - present
Data warehousing1980s -present
Web-enabled1990s - present
Evolution of Database Systems53
-
8/10/2019 Database Chapter 01
54/59
54
Customers
Orders
Item Description Quantity
998 Dog Food 12
764 Cat Food 11
Items
Customer
Order
Items Ordered
To retrieve data, you
must start at the top
(customer). When you
retrieve a customer, you
retrieve all nested data.
Hierarchical Database
-
8/10/2019 Database Chapter 01
55/59
55
Customer
OrderItems
Ordered
Items
Entry point
Entry point
Network Database
-
8/10/2019 Database Chapter 01
56/59
56
Customer(CustomerID, Name,
Order(OrderID, CustomerID, OrderDate,
ItemsOrdered(OrderID, ItemID, Quantity,
Items(ItemID, Description, Price,
Data is stored in separate sets of data
The tables are not physically connected.
Instead, data is linked between column
Ex: retrieve order, database can match customer and order
by customerID
Relational Database
-
8/10/2019 Database Chapter 01
57/59
57
CustomerCustomerID
Name
Add CustomerDrop Customer
Change Address
OrderOrderID
CustomerID
NewOrderDeleteOrder
OrderItem
OrderIDItemID
OrderItem
DropOrderItem
Item
ItemIDDescription
New Item
Sell Item
Buy Item
Government
CustomerContactName
ContactPhoneDiscount,
NewContact
Commercial
Customer
ContactNameContactPhone
NewContact
Object3 component
- name
- a set of properties or attributes
- a set of methods or functions
Object-Oriented Database
-
8/10/2019 Database Chapter 01
58/59
Reference:
Hoffer, J.A, Ramesh, V., Topi, H. 2011. Modern
Database Management. 10thEdition, Pearson
Education Limited.
Post, G.V. 2005. Database ManagementSystems: Designing and Building Business
Application, 3rdEdition, McGraw Hill.
58
-
8/10/2019 Database Chapter 01
59/59
Summary59
In this chapter you learned how to:
Define the following key terms:
Data, database, Information, Metadata and Database
Management System (DBMS).
Describe the advantages of using database
approach
Describe components of database environment
Briefly describe the evolution of databasesystems.