CHAPTER 1:
INTRODUCTION
1
Database Application Development
SSK 3408
Learning Objectives
2
Define the following key terms:
Data, database and Database Management
System (DBMS), metadata.
Describe the advantages of using database
approach
Describe components of database
environment
Briefly describe the evolution of database
Introduction
3
Over the past two decades, there has been
enormous growth in the number and the
importance of database applications.
To store, manipulate and retrieve data in nearly
every type of organization – business, health care, education, government and libraries.
Database technology can be used by an
individual up to large number of users accessing databases.
The knowledge derived from databases can be
used:
Competitive advantage
Introduction ..cont.
4
DataBase Management System (DBMS) is
one of the most powerful tools that can be
used in building business database
application.
Provide many features that represent
significant advantages over traditional
programming methods.
Understand the concepts of database design,
queries and application building will reduce
the time for developing complex applications
Build a Business Application
5 Tools: Database Design SQL (queries) Programming De sig n SQL Pro g ra m Desig n SQL Prog ram Best:Spend your time
on design and SQL. Worst:
Compensate for poor design
Basic Concepts and Definitions
Database:
A collection of data stored in a standardized format, designed to be
shared 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 user’s environment.
Structured data or Unstructured/Multimedia data
Information:
Data that have been processed in such a way as to increase knowledge
of 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
Example of Data
8
Figure 1-1a Converting Data into Information
9
Graphical displays turn data into useful information that managers can use for
10
Descriptions of the properties or characteristics of the data, including data types, field sizes, allowable
Drawbacks of File Processing
Systems
11 Program-Data Dependence
Duplication of Data
Inconsistent data
Limited data sharing
Lengthy development times
Program-data Dependency
Problems with Data
Dependency
File description are stored within each application
program that access a given file.
Consequently, any changes to a file structure requires
changes 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 often introduced when making such changes.
Each application program must have its own
processing routines for reading, inserting, updating and deleting data.
Lack of coordination and central control.
14
Duplicate
Data
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.
File Processing Method
16 Data Definition File 1 … File 2 … Data Definition File A File 2 File C … Pay History Benefits Employee Employee Choices Files Programs Payroll BenefitsExample of File Method
Add to file (e.g. Cell phone)
Write code to copy employee
file and add empty cell phone slot.
Find all programs that use
employee 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
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 DBMS Central database Contains employee, order, inventory, pricing, and customer dataDBMS
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.
DBMS
20
• It provides interface between the various database applications for organizational users and database
Enterprise Data Model
Graphical model showing high-level entities and its relationships
used by the organization.
Relational Databases
Database technology involving tables (relations) representing
entities and primary/foreign keys representing relationships
Use of Internet Technology
Networks and telecommunications, distributed databases,
client-server and 3-tier architectures
Database Applications
Application programs used to perform database activities (create,
read, update, and delete) for database users
Elements of Database
Approach
One customer may place many orders, but each order is placed by a single customer
One-to-many relationship
One order has many order lines; each order line is
associated with a single order One-to-many relationship
One product can be in many order lines, each order line refers to a single product
Therefore, one order involves many products and one product is involved in many orders
Client/server system
29 Application program functions:
• inserting new data, •updating existing data, • deleting existing data, •reading data for display
The Range of Database
Applications
© Pearson Education Limited, 2004
31
Two-Tier Client-Server
Client manages main business and data
processing logic and user interface.
Server manages and controls access to
© Pearson Education Limited, 2004
32
© 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 (application
server).
DBMS – stores data required by the middle
tier. This tier may be on a separate server
(database server).
© Pearson Education Limited, 2004
34
An Example of Personal
Database
An Example of Workgroup
Database
An Example of Enterprise Data
Warehouse
Advantages of Database Approach
38
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
such as forms and reports generator.
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.
Costs and Risks of Database
Approach
CASE Tools – computer-aided software engineering 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 – languages, menus, and other facilities by
which users interact with various system components such as CASE tools, DBMS etc.
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
Components of Database
Environment
Components of Database
Environment
41 Hardware PC, Mainframe, Network computer Software Programming Language Data Procedures Instructions and rules People
Data and Database
Administrator, System Developer, End-user
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 Queries Program2
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 before with no changes.
43
Field Name Data Type Description
EmployeeID Number Autonumber.. TaxpayerID Text Federal ID LastName Text
FirstName Text . . .
Phone Text . . .
DBMS Features/Components
Database engine
Storage, retrieval,
update
Enforce business
rules
Performance and
ability to handle
large problems
Data dictionary
Query Processor
Utilities
Security
Report writer
Forms generator
(input screens)
Application
generator
Communications
3GL Interface
44DBMS Components
45 All Data Database Engine Data Dictionary Security Query Processor Form Builder Report Writer Communication Network 3GL Connector Program Application GeneratorDBMS Engine, Security, Utilities
46 Data Tables Product ItemID Description 887 Dog food 946 Cat food Order OrderID ODate 9874 3-3-97 9888 3-9-97 Customer CustomerID Name 1195 Jones 2355 Rojas User IdentificationAccess Rights Security
Concurrency and Lock Manager Utilities Backup and Recovery Administration Database Engine Product
ItemID Integer, Unique Description Text, 100 char
Customer
CustomerID Integer, Unique
Name Text, 50 char Data
DBMS Query Processor
47 All Data Database Engine Data Dictionary Query ProcessorDBMS Report Writer
48 All Data Database Engine Data Dictionary Query Processor Report Writer Report Format and QueryReport Writer (Oracle)
DBMS Input Form
50 All Data Database Engine Data Dictionary Query Processor Form Builder Input Form DesignExample of Commercial DBMS
51 Oracle
Ingres
Informix (Unix)
DB2, SQL/DS (IBM)
Access (Microsoft)
SQL Server (Microsoft +)
Many older (Focus, IMS, ...)
Significance of using DBMS
52
Applications change a lot, but same data.
Need for ad hoc questions and queries.
Need to reduce development times.
Need shared data.
Improve quality of data.
Flat files - 1960s - 1980s
Hierarchical – 1970s - 1990s
Network – 1970s - 1990s
Relational – 1980s - present
Object-oriented – 1990s - present
Object-relational – 1990s - present
Data warehousing – 1980s -
present
Web-enabled – 1990s - present
Evolution of Database Systems
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
55 Customer Order Items Ordered Items Entry point Entry point
Network Database
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
57 Customer CustomerID Name … Add Customer Drop Customer Change Address Order OrderID CustomerID … NewOrder DeleteOrder … OrderItem OrderID ItemID … OrderItem DropOrderItem … Item ItemID Description … New Item Sell Item Buy Item … Government Customer ContactName ContactPhone Discount, … NewContact Commercial Customer ContactName ContactPhone … NewContact Object – 3 component - name
- a set of properties or attributes - a set of methods or functions
Reference:
Hoffer, J.A, Ramesh, V., Topi, H. 2011. Modern
Database Management. 10
thEdition, Pearson
Education Limited.
Post, G.V. 2005. Database Management
Systems: Designing and Building Business
Application, 3
rdEdition, McGraw Hill.
Summary
59
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
