Database System

Sekolah Tinggi Ilmu Statistik

Jakarta

Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik

Referensi
T. Connoly, C. Begg, Database Systems, Edisi 4, England: Addison Wesley, 2005.
Jeffrey A. Hoffer, Mary Prescott, Heiki Topi, Edisi 8, Modern Database Management,
Prentice Hall, 2007.
Access Database Design & Programming, 3rd edition, Steven Roman, O’Reilly, January
2002.
Database Systems: The Complete Book, Hector Garcia-Molina, Jeffrey D. Ulman, Jennifer
Widom, Prentice Hall.
Microsoft Access 2010TM An Essential Guide (Level 1), The University of Reading, 2011.
Microsoft Access 2010TM An Intermediate Guide (Level 2), The University of Reading,
2011.
Using and Designing Access 2010 databases – Practical workbook, University of Bristol.
Said Mirza Pahlevi, Tujuh Langkah Praktis Pembangunan Basis Data.

Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik

Penilaian
Tugas/Quiz : 20%
UTS : 30%
UAS : 50%

Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik

Lecture 1
Introduction to Databases

Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik

 Objectives
Some brief definitions and common uses of database systems.

Characteristics and problems of file-based systems.

Meaning of the term database and Database Management System (DBMS).

Components of DBMS Environment

Roles in the Database Environment

Advantages and disadvantages of DBMSs

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Some Definitions
Database
• A collection of related data.

Database Management System (DBMS)

• A software that manages and control accesses to the database.

Database Application
• A program that interacts with the database at some point in its
execution.

Database System
• A collection of application programs that interact with the database.

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Examples of Database
Applications
Purchases from the supermarket
• Bar code reader  app prog  DB (update stock number)

Purchases using your credit card

• Card reader  app prog customer DB & stolen DB

Booking a holiday at the travel agents

• Ensure not to overbook the seat

Using the local library

• Bar code reader  app prog book DB (borrow & return)

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Examples of Database
Applications
Taking out insurance
• A broker accesses several insurance company databases

Using the Internet

• Bookstore to buy books and purchase by using a credit card
(both uses DB)

Studying at university
• Student database

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 File-Based Systems
Collection of application programs that perform services for
the end users (e.g. reports).

Each program defines and manages its own data.

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
File-Based Systems

Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik

 Limitations of File-Based
Approach
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.

Duplication of data
• Same data is held by different programs.
• Wasted space and potentially different values and/or different
formats for the same item.
• It costs time & money to enter data more than once.
• Loss of data integrity.

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Limitations of File-Based
Approach
Program – Data dependence
• File structure is defined in the program code.
• It is difficult to change the file structure.
• Need to change both the file and accessing programs.
 e.g., change PropertyForRent address field from 40 to 41. Creating one-off program &
modify all accessing programs

Incompatible file formats

• Programs are written in different languages (e.g., COBOL & C), and so
cannot easily access each other’s files.

Fixed Queries of application programs

• Programs are written to satisfy particular functions.
• Any new requirement needs a new program.

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Database Approach
The file-based approach limitations arose:

• 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.

Result:
• The database and Database Management System (DBMS).

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Database
Definition: shared collection of logically related data and a
description of this data, designed to meet the information needs of
an organization.
• Shared large repository of data with metadata (self-describing).
• This enables program–data independence.

Logically related data comprises entities, attributes, and

relationships of an organization’s information.
• Entities: branch and staff.
• Property: branchNo and staffNo.
• Relationship: a branch has staff.

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Database Management System
(DBMS)
A software system that enables users to define, create,
maintain, and control access to the database.

(Database) application program

• A computer program that interacts with database by issuing

an appropriate request (SQL statement) to the DBMS.

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Database Management System
(DBMS)

Figure 1.5

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 DBMS Functionalities
Data Definition Language (DDL).

• Permits specification of data types, structures and any data

constraints.

• All specifications are stored in the database.

Data Manipulation Language (DML).

• General enquiry facility (query language) of the data.

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 DBMS Functionalities
Controlled access to database may include:

• A security system

• An integrity system

• A concurrency control system

• A recovery control system

• A user-accessible catalog.

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Views
Allows each user to have his or her own view of the database.

• e.g., contract department wants to see for a rental property

shown in Figure 1.5

A view is essentially some subset of the database.

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Views - Benefits
Reduce complexity.
• A view based on necessities.

Provide a level of security.

• Excluding data that some users should not see.

Provide a mechanism to customize the appearance of the database.

• Call field rent as Monthly Rent.

Present a consistent, unchanging picture of the structure of the database,

even if the underlying database is changed.
• Field added or removed, relationships changed, renamed etc.

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Components of DBMS
Environment

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Components of DBMS
Environment
Hardware

• Can range from a PC to a network of computers.

• Client-server architecture (backend & frontend).

Software

• DBMS, operating system, network software (if necessary) and

also the application programs (3GL such as Java, VB etc. & 4GL
such as SQL embedded in 3GL).

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Components of DBMS
Environment
Data

• Operational data used by the organization and a description of

this data called the schema.

Procedures

• Instructions and rules that should be applied to the design and

use of the database and DBMS.
 Log in, start/stop DBMS, backup etc.

People

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Roles in the Database
Environment
Data Administrator (DA)

• Manage the data resource (DB planning, conceptual/logical

DB design etc.)

Database Administrator (DBA)

• Physical realization of DB (physical DB design &

implementation, security and integrity control etc.)

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Roles in the Database
Environment
Database Designers (Logical and Physical)

• Conceptual/logical DB
 Identifying the data (entities & attributes), and their relationships &
constraints
 Understand the data and business rules (main characteristics of the
data as viewed by the organization)

• Physical DB design
 Deciding how the logical DB design is to be physically realized
 e.g., mapping logical DB design into a set of tables and integrity
constraints

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Roles in the Database
Environment
Application Programmers

• Creating program to access the DB (retrieve, insert, update &

delete data)

End Users

• Naïve: unaware of the DBMS

• Sophisticated: familiar with the structure of the database and the

provided database facilities

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Advantages of DBMSs
Control of data redundancy
• Can be duplicated for performance reason

Data consistency
• Store data in one place

More information from the same amount of data

• Data integration

Sharing of data
• The database belongs to the entire organization; not to departments

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Advantages of DBMSs
Improved data integrity (constraints)
• Validity and consistency of stored data
Improved security
• Usernames and passwords for authorization
• Access by operation types: retrieval, insert, update & delete
Enforcement of standards
• Data formats, naming conventions, document standards, update
procedures and access rules
Economy of scale
• Combining operational data into one database and managing
them can result in cost savings

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Advantages of DBMSs
Balance conflicting requirements
• The database is under control of a DBA that can make best decisions
base on user needs

Improved data accessibility and responsiveness

• The integrated data is directly accessible to the end users by using
program, query language and report writers

Increased productivity
• DBMS provides all the low level file-handling routines (typical in
application programs)

• Also provides 4th generation environment

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Advantages of DBMSs
Improved maintenance through data independence
• DBMS separates data descriptions from the applications

Increased concurrency
• DBMS manages concurrent database access

Improved backup and recovery services

• Minimize the amount of processing that is lost following a
failure

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Disadvantages of DBMSs
Complexity
• Database design, access policy etc.

Size
• A large piece of software requiring substantial memory to run

Cost of DBMS
• Depends on the user number, maintenance cost etc.

Additional hardware costs

• Disk storage, dedicated machines for DBMS servers etc.

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Disadvantages of DBMSs
Cost of conversion

• Converting existing apps to run on new DBMS & hardware

• Staff training

Performance

• DBMS is developed for general application compared to a file-based system

Higher impact of a failure

• Centralization of resource increases the vulnerability of the system.

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik
 Kuis
Sebutkan dua contoh database system/applications
• Jelaskan fungsi database nya
• Jelaskan operasi yang digunakan pada database tersebut

DR. SAID MIRZA PAHLEVI, [Link]. Sekolah Tinggi Ilmu Statistik (STIS) Sekolah Tinggi Ilmu Statistik