Welcome to BITP 1323
DATABASE
SEMESTER 2/2014/2015
23 February - 4 June 2015
LE
UR
T
C
DITP 1333
AGENDA: WEEK 1
Database
Introduction
Introduction to Data & Information
Traditional File-Base System vs
Database Approach
Roles in DB Environment
DBMS History
DBMS Advantages & Disadvantages
Database
Environment
ANSI-SPARC Architecture
Data Model and Conceptual Modeling
Functions of DBMS
Traditional File Based
System
vs
Database Approach
Database
Introduction
DBMS
Advantages &
Disadvantages
Roles in
DB
Environment
DBMS
History
Introduction
Database concept has been
UNIVERSITY
widely used in large or small TOUR AGENT
organization.
CLINIC
This concept arises from the
need to build a lot of data
processing systems.
Organization store data about
their daily activities and their
interaction between external
environments.
Introduction
Database
Introduction
Digital Data
Digitaldataarestoredincomputersasfiles.Often,dataare
arrayedintabularform.Forthisreason,datafilesareoften
calledtables.
Adatabaseisacollectionoftables.
Businessesandgovernmentagenciesthatservelarge
clienteles,suchastelecommunicationscompanies,airlines,
creditcardfirms,andbanks,relyonextensivedatabasesfor
theirbilling,payroll,inventory,andmarketingoperations.
Databasemanagementsystemsareinformationsystemsthat
peopleusetostore,update,andanalyzenongeographic
databases.
Introduction
Database
Introduction
Information vs Data
Information can
be
defined
as
data
that
has
been
processed UNIVERSITY
and are useful
information
Employees
Students
Courses
name
address
Data referred to staff number
data
as the facts of any
object or event to
be
stored
in
computer media.
TOUR AGENT
CLINIC
Employees
Holiday Packages
Booking
Employees
Medicine
Patients
Package name
Package price
name
address
allergies
Introduction
Database
Introduction
Convert data into Information
For example the list below will be useless because we do not know
what the purpose of the data collected. Assumptions can be made
to the existing data, but it does not make the information we have is
accurate and relevant.
Groceries
499
400
99
School
200
200
Medical
900
35
865
By adding text and applying some appropriate structure
(Figure 2) to the data, it will change the way we interpret the
collected data.
(a) Data in context
(b) Summarized
data
Real World
Entity
UNIVERSITY
Environment
STUDENT
File
Attribute Name
Name
Record
Attribute Value
Address
Age
Faculty
Department
1 Mariam
Perak
23
FTMK
SE
2 Zulaikha
Johor
24
FTMK
MM
3 Zaki
Selangor
22
FTMK
SE
4 Alif
Terengganu
23
FTMK
SE
Traditional File-based System
Database
Introduction
vs Database Approach
File-based System is
a Collection of application programs that
perform services for the end-user such
as the production of reports. Each
program defines and manages its own
data.
Traditional File-based System
Database
Introduction
File-based system approach
Before the database system
is introduced, the data is
stored in a separate file and
stored on magnetic tape or
diskette.
data
processing
applications have their own
input and output for the
application executable
Student application
program
Course application
program
Each
Finance application
program
Student File
student name,
student address,
matrices number
Course File
course name, course
code,
Finance File
customer number,
customer name
Traditional File-based System
Database
Introduction
vs Database Approach
FILE-BASED SYSTEM
Applications developed in an ad-hoc
Data requirements for applications derived
independently
Data files developed for individual
applications
DATABASE APPROACH
SYSTEM
Centralization of information management
Data shared by different groups of users
and application programs
Provision of multiple interfaces
Application programs are data dependent
Representation of complex relationships
between data
Integrity constraint handling
Advanced facilities for backup and
recovery
Database
Introduction
Limitation
of file-based systems
DATA
DEPENDENCE
SEPARATION
OF DATA
L
B
I
T
A
P
M
INCO
E
L
I
F
S
E
T
A
M
FOR
FIX
QU ED
AP ERI
N PLIC S OF
AT
PR
IO
OG
RA
MS
REDU
NDAN
Y
C
DUPL
IC
OF DA ATION
TA
Database
Database
Introduction
Database is a shared collection of logically related
data, and a description of this data, designed to meet
the information needs of an organisation.
database is a
large repository of data
can be accessed simultaneously
by department and users
holds data data that is logically
related. (relationships between
entities)
holds organizations operational
data and description of data
(catalog)
Database
Introduction
Database
Components
User Data is stored in a table.
Example:
STUDENT TABLE
Name
INDEX is used for faster data
retrieval.
Example:
Indexing using matric number
Address
Age
Faculty
Enroll Date
Perak
23
FTMK
1/9/2014
2 Zulaikha Johor
24
FTMK
1/9/2014
1 D123
3 Zaki
Selangor
22
FTMK
1/9/2014
4 Aliff
Terengganu 23
FTMK
1/9/2014
1 Mariam
Metadata is data about data
Example:
Metadata for STUDENT TABLE
Field Name
Data Type
Size
Name
VARCHAR
23
Address
VARCHAR
24
Age
NUMBER
22
Faculty
VARCHAR
23
Enroll Date
DATE
Matric No Name
Address
Age
Enroll Date
Mariam
Perak
23
1/9/2014
2 D213
Zulaikha
Johor
24
1/9/2014
3 D312
Zaki
Selangor
22
1/9/2014
4 D411
Aliff
Terengganu
23
1/9/2014
Metadata Application : keep the
structure and format of reports,
queries, forms and other
applications.
Database
Introduction
ROLES
in Db environment
supervises
writes and
enforces
procedures
and standards
DATABASE
ADMINISTRATOR
ANALYST
SYSTEM
ADMINISTRATOR
manages
designs
END USERS
use
DB DESIGNER
PROGRAMMERS
DBMS utilities
DBMS
write
application
programs
hardware
access
DATA
Database
Introduction
DBMS
DATABASE MANAGEMENT SYSTEM
Collection of programs that manages
database structure and controls access
to data
Possible to share data among multiple
applications or users
Makes data management more efficient
and effective
REF
http://education-portal.com/ac
ademy/lesson/what-is-a-databas
e-management-system-purpose-an
d-function.html
Database
Introduction
DBMS
History
Early manual system
Before-1950s
Data was stored as paper
records.
Lot of man power involved.
Lot of time was wasted.e.g. when
searching
Therefore inefficient.
Revolution began
1950s and early 1960s:
Data processing using magnetic
tapes for storage
Tapes provide only sequential
access
Punched cards for input
Late 1960s and 1970s:
Hard disks allow direct access to
data
Data stored in files
Known as File Processing System
Database
Introduction
DBMS
History
File-based system
Adequate for small applications
Drawbacks
Separation and isolation of data Each program
maintains its own set of data.
Users of one program may be unaware of potentially
useful data held by other programs.
Database Approach
Arose because:
Definition of data was embedded in
application programs, rather than being stored
separately and independently.
No control over access and manipulation of
data beyond that imposed by application
programs.
Duplication of data
Result:
Same data is held by different locations.
The database and Database Management
Wasted space and potentially different values and/or System (DBMS).
different formats for the same item.
Data dependence
File structure is defined in the program code.
Incompatible file formats
Programs are written in different languages, and so
cannot easily access each others files.
Fixed Queries/Proliferation of application programs
Programs are written to satisfy particular functions.
Any new requirement needs a new program.
Database
Introduction
DBMS
History
Eachnewapproachtodatabasedesignrequiredthe
useofincreasinglypowerfulcomputerstoachieve
satisfactoryperformanceforlargevolumesofdata.
MODULARITY CONTROL
Fully
modular
Object-Oriented
emergedasanewsolutiontoproblemsraisedby
bothhierarchicalandnetworkdesigns
Relational
Network
wasdevelopedasaresponsetothe
limitationsofhierarchicaldesigns
Hierarchical
Fully
integrated
istoovercometheshortcomingsof
fileorientedsystems
File- oriented
1950
1960
1970
TIME
1980
1990
Database
Introduction
specific data files for specific
programs. This was efficient
from a processing standpoint,
but soon led to complications
from a business standpoint.
DBMS
History
File- oriented
SALES REP
ALI
SALES REP
AMINAH
ACC STAFF
CHONG
INVOICE SYSTEM
ORDER SYSTEM
CUSTOMER
MASTER FILE
INVENTORY
MASTER FILE
ACC STAFF
AHMAD
BACK
ORDER FILE
INVENTORY
PRICING
FILE
File-oriented systems tended to
be slow, hard to maintain,
and very cumbersome when
business
processes
required
trading
data
across
organizational
functions
or
departments: too often the
programs in one department
could not read the data used by
programs in another.
moving data across functional
boundaries (to track a business
process, for example) was
extremely
difficult,
if
not
impossible.
Example:
CUSTOMER
RECEIPT FILE
It would be very difficult for a sales
representative to tell a customer
what the expected price changes on
back-ordered products might be
since prices reside with Accounting
while inventory information resides
with the Orders Department; both
sets of data are accessed by
different people using different
systems; and the file formats used
Database
Introduction
DBMS
Hierarchical Database
ORDER
SYSTEM
Customer
Number
(b)
Order(s)
Figure 1
product price
(a)
Name &
Address
Product
Number
Backorder(s
)
product price
The advantages of separating data
from programs using a DBMS is
shown in Figure 1.
Now any sales rep can use the
database to answer customer's
questions about orders, and any
member of the accounting staff can
use the database to generate
invoices.
Hierarchical database begins to
solve
the
data
fragmentation
problems suggested by the fileoriented design and offers a way to
share data for multiple purposes
across
organizational boundaries.
INVOICING
SYSTEM
Example:
A hierarchical database is designed
for rapid searches of orders by
customer number. Each customer
number is linked to a customer name
and address and one or more orders.
By searching to find what orders are
associated with a customer number,
sales representatives can find what
products are included in any order,
and search the database separately
to find which orders are backordered.
Accounting staff can accumulate
order quantities and prices by
customer
number
to
generate
Database
Introduction
DBMS
Network Database
Customer
Number
ORDER
SYSTEM
Product
Number
Name &
Address
Order(s)
product price
(a)
Backorder(s
)
product price
Figure 2
explicit
pathway
Evolved in part to solve navigational
problems encountered in hierarchical
designs.
In practice, the two types of
databases often appear quite similar.
Network designs, however, build
more sophisticated links between
database
records
than
do
hierarchical
approaches.
In
particular, network designs enable
multiple paths between records.
INVOICING
For,SYSTEM
example, an explicit pathway
is maintained between product
numbers, orders, and prices (see the
arrow labeled (a) in Figure 2).
Maintaining this link would make it
easier to traverse from customer
orders to backordered products to
determine the intersection of
the two sets .
Example:
Which records exist in both
groups. The result would list what
customers
had
products
on
backorder, and describe pricing for
those products. In this sense, the
network database would make it
easier to collect pricing information
without the potentially cumbersome
Database
Introduction
DBMS
Relational Database
CUSTOMER TABLE
PRODUCT TABLE
Cust #
Cust_Nam
e
Prod #
Prod_Nam
e
100
Ali
10
Mini Ipad
200
Aminah
20
Iphone 6
300
Ahmad
30
laptop
ORDER TABLE
Cust #
Prod #
300
20
100
30
PRICE TABLE
Prod #
Prod_Nam
e
10
RM 1000
20
RM 2800
30
RM 3900
BACKORDER TABLE
A manager wants to know how much revenue is represented
byThe
backordered
product.
Backorder
table describes that Product 30 was
backorders as of June.
The Order table identifies Customer 100 as having ordered
Product 30.
The Price table lists the price of Product 30 as RM 3900.
The Product table provides the name of Product 30 and the
Customer table provides the name of Customer 100.
Prod #
Date
30
June 2014
Database
Introduction
DBMS
Query : Who is back-ordered and for what revenue?
RESULT
Prod #
Cust #
Prod_Name Cust_Name Date
Price
30
100
laptop
RM 3900
Ali
June 2014
Database
Introduction
DBMS
Object-Oriented
Inheritance
OBJECT
Attribute
Attribute
Att Value
Att Value
INHERITANCE
Specialization
SPECIALIZATION
CUSTOMER
ADDRESS
Attribute
Attribute
Att Value
Att Value
ALI
Street
Location Code
SG Address
Address
Credit limit
Melaka
RM 1000
Street
Zip Code
MY Address
Street
Postal Code
Database
Introduction
TYPES
of Database
There are different ways to refer to
databases (and DBMSs). Based on :
number of concurrent users,
geographic location of the data,
how they will be used and on the
time sensitivity of the information
gathered (example: service sales,
payment, etc) .
Database
Introduction
TYPES
of Database
NUMBER OF USERS
DATABASE TYPE
DESCRIPTION
Single-user
Supports only a single user at a
time. Usually only works on a
PC
Desktop
Single user database that runs
on a PC also called "desktop
database"
Multi-user
Supports several users at the
same time. Can be PC or
mainframe based.
Workgroup
Small multi-user database
(usually 50 users or less)
Enterprise
Large multi-user database.
Usually runs on a mainframe.
14
Database
Introduction
TYPES
of Database
LOCATION
Supports data located at a single site
Supports data distributed across several sites
Database
Introduction
TYPES
of Database
USE
Transactional (or production):
Supports a companys day-to-day operations
Data warehouse:
Stores data used to generate information required to make tactical or
strategic decisions
Often used to store historical data
Structure is quite different
16
Database
Introduction
DBMS
Software
INGRESS
INFORMIX
SYBASE
MY SQL
ORACLE
MICROSOFT
ACCESS
MICROSOFT
SQL SERVER
Database
Introduction
DBMS
why?
Databasemanagementsystemsarevaluablebecausethey
providesecuremeansofstoringandupdatingdata.
Databaseadministratorscanprotectfilessothatonlyauthorized
userscanmakechanges.
DBMSprovidetransactionmanagementfunctionsthatallow
multipleuserstoeditthedatabasesimultaneously.
Inaddition,DBMSalsoprovidesophisticatedmeanstoretrieve
datathatmeetuserspecifiedcriteria.Inotherwords,theyenable
userstoselectdatainresponsetoparticularquestions.A
questionthatisaddressedtoadatabasethroughaDBMSis
calledaquery.
Database
Introduction
DBMS
Advantages & Disadvantages
Advantages
1.control of redundant data can
be done because the data is
stored in one location
2.higher consistency of data
can be done with the control
of redundant data
3.More information can be
generated from the same
data.
4.More economical in terms of
manpower, storage and cost
5.additional concurrent control
6.better security, such as the
use of passwords
Disadvantages
1.the complexity of the
development process will have an
impact on system performance
2.DBMS size is quite large and
involve a lot of storage
3.high costs in building a DBMS and
the provision of hardware
4.high costs in the transition from
traditional file system to a
database system
5.impact of failure is higher
because users and applications
depend on DBMS
ANSI-SPARC
Architecture
Database
Environment
Data Model
and
Conceptual
Design
Function
of DBMS
Database
Environment
ANSI-SPARC consists
ARCHITECTURE
of
three
levels.
External Level
View 1
View 2
mapping
Conceptual Level
mapping
Internal/Physical Level
DATABASE
View 3
proposed by the
Standard
Planning
and
Requirements
Comittess of the
American
Standards
Institute Comittee
on Computers and
Information
Processing (ANSI /
40
SPARC).
Database
Environment
ANSI-SPARC
ARCHITECTURE
The purpose of this architecture is to distinguish between
the way the physical display of the database and describe
how users view the database because:
each user have a different view of the same data
needs and views of the users of the data may
change over time
end-users should not interfere with the complex
structure of the database storage
changes to the logical database by the DBA should
not involve all users
Database
Environment
ANSI-SPARC
ARCHITECTURE
External Level
Conceptual
Level
Internal/Physi
cal Level
View 1
View 2
CUSTOMER TABLE
Cust #
Cust_Name
100
Ali
Designers View
CUSTOMER TABLE
Cust #
Cust_Nam
e
Cust_Salar
Cust_B.O.D
y
100
Ali
1000
2 JUNE 1981
CUSTOMER TABLE
Cust #
Cust_Name
Cust_Salary
Cust_B.O.D
100
Ali
1000
2 JUNE 1981
CREATETABLECUSTOMER
(
cust_novarchar(12),
cust_namevarchar(20),
cust_salarynumber,
cust_dobdate)
);
The DBMS View physical
representation of the
database
on
the
computer. This level
describes how the data
id
stored
in
the
database. (e.g: storage
space allocation , record
descriptions for storage)
Database
Environment
Database System
Components
Database system is composed of five main parts:
1.
Hardware
2.
Software
Operating system software
DBMS software
Application programs and utility software
3.
People
4.
Procedures
5.
Data
40
Database
Environment
Data Model
and Conceptual Modeling
Designers, programmers, and end users
see data in different ways
Different views of same data lead to design
that do not reflect organizations operation
Data modeling reduces complexities of
database design
Database
Environment
Data Model
and Conceptual Modeling
a
simple
Data Modelling is :
representations
of
complex real-world data
structures
(Often
graphical)
a model: an abstraction
of a real-world object
or event
Useful in understanding
complexities of the realworld environment
is
iterative
and
progressive
Important in order to / because:
Facilitate
interaction
among
the
designer,
applications programmer,
and end user
End users have different
views and needs for data
Data model organizes data
for various users
Database
Environment
Data Model
and Conceptual Modeling
ENTITY
ATTRIBUTE
RE
T
A
L
IP
H
S
N
IO
CONSTRAINT
Model
Building
Blocks
Database
Environment
Data Model
and Conceptual Modeling
Business Rules
Descriptions of policies, procedures
Apply to any organization
Description of operations to create/enforce
actions
Must be in writing and up to date
Must be easy to understand
Database
Environment
Data Model
and Conceptual Modeling
Translate Bussiness Rules => Data Model
nouns translate into entities
Verbs translate into relationships
Relationships are bidirectional
Database
Environment
Data Model
and Conceptual Modeling
Conceptual Modelling
Represents global view of the entire database
All external views integrated into single global view: conceptual
schema
ER model most widely used
ERD graphically represents the conceptual schema
Provides a relatively easily understood macro level view of data
environment
Independent of both software and hardware
Does not depend on the DBMS software used to implement the
model
Does not depend on the hardware used in the implementation of
the model
Changes in hardware or software do not affect database design
at the conceptual level
Database
Environment
Data Model
and Conceptual Modeling
Business Rules
Conceptual Modelling
A student can enroll at least one
or more subjects and a subjects can
be enrolled with more than one
students.
It is an optional for a lecturer to
teach and a lecturer also can teach
more than one subjects.
Enrollment
must
have
one
subjects and subject can be offered
more than one as it is also optional
for a subject to be offered in any
semester.
Database
Environment
DBMS
Functions
Performs functions that guarantee integrity and consistency
of data.
Data transformation and presentation
Security management
Multiuser access control
Backup and recovery management
Data integrity management
Database access languages and application programming
interfaces
Database communication interfaces
44
SUMMARY
Data are raw facts. Information is the result
of processing data to reveal its meaning.
To implement and manage a database, use
a DBMS.
Database design defines the database
structure.
A well-designed database facilitates data
management and generates accurate and
valuable information.
A poorly designed database can lead to bad
decision making, and bad decision making
can lead to the failure of an organization.
50
SUMMARY
Databases were preceded by file systems.
Limitations of file system data management:
requires extensive programming
system administration complex and difficult
making changes to existing structures is difficult
security features are likely to be inadequate
independent files tend to contain redundant data
DBMSs were developed to address file systems
inherent weaknesses
51
