Database Management System

Prepared by: Mr. Kayvin L. Malawig

Credits to: Jeffrey Hofler: V.Ramesh: Heikki TopiReference : Modern Database Management System 10th Edition

Go.od LucK !

All information systems create, read, update and delete (CRUD) data.

This data is stored in files and databases A FILE collection of similar records

◦ Files or conventional files are unrelated to each other except in the code of an application program.

◦ Data storage is built around the applications that use the files.

◦ Ex. CUSTOMER FILE, ORDER FILE, and PRODUCT FILE

D’BASE MNGMNT. SYS.

FEATURES:

No Database Computer File Processing System

METHODS: Data Store Data Manipulation Retrieval Of Large Files Of Data

TRADITIONAL

FILE

PROCESSING

SYSTEM

Disadvantages: Program Data Independence

◦ File descriptions are stored within each database application program that accesses a given file.

Duplication Of Data◦ Because applications are often developed

independently in file processing systems, unplanned duplicate data files are the rule rather than the exception.

Limited Data Sharing◦ With the traditional file processing approach, each

application has its own private files, and users have little opportunity to share data outside their own applications.

TRADITIONAL FILE PROCESSING SYSTEM

Disadvantages: Lengthy Development Times

◦ Each new application requires developer . . .◦ The lengthy development times required are

inconsistent with today’s fast-paced business environment.

Excessive Program Maintenance◦ The preceding factors all combined to create a

heavy program maintenance load in organizations that relied on traditional file processing systems.

TRADITIONAL FILE PROCESSING SYSTEM

ADVANTAGE DISADVANTAGE

CONVENTIONAL FILES

•Relatively easy to design and implement• Ex. ACC0UNTS RECEIVABLE, PAYROLL

•Processing Speed

•Data independence from applications increases adaptability and flexibility

DATABASE•Superior scalability

•Ability to share data across applications

•Less, and controlled redundancy (total redundancy is not achievable

•Harder to adapt to sharing across applications

•Harder to adapt to new requirements

•Need to duplicate attributes in several files

•Database technology is more complex than file technology

•Somewhat slower performance

•Investment in DBMS and database experts

•Need to adhere to design principles to realize benefits

•Increased vulnerability inherent in the use of shared data

THE PROS AND CONS

Database ◦ a collection of interrelated files◦ is not merely a collection of files.◦ The records in each files must allow for relationships to the records

of other files.◦ Records in one file (or table) are physically related to records in

another file (or table).◦ Applications are built around the integrated database.

* Data – resource that must be controlled and managed...

* Data Architecture – defines how the business use both files and databases to store all of the organization’s data; the file and the database technology to be used; and the administrative structure set up to manage the data resource.

DATABASE MANAGEMENT SYSTEM

Operational (transactional) Database◦ Developed to support day-to-day operation and

business transaction processing for major information systems.

Data Warehouse◦ Store data extracted from the operational database

data mining – extraction of data by the end users Data Administrator

◦ Data planning , definition, architecture & mngmnt. Database Administrator

◦ Responsible for the database technology, database design and construction consultation, security, backup and recovery and performance tuning.

DATABASE MNGMNT SYSTEM

Database Architecture◦ refers to the database technology including the

database engine, database utilities, database CASE tools for analysis and design, and database application development tools

Database Management System (DBMS)◦ Is a specialized computer software available from

computer vendors that is used to create, access control, and manage the database.

Database Engine◦ Core of DBMS; responds to specific commands to

create database structures and then to create, read, update, and delete records in the database

DATABASE MNGMNT SYSTEM

Data Definition Language (DDL)◦ Language used by a DBMS to define a database

or a view of a database. Data Manipulation Language (DML)

◦ DBMS language used to create, read, update and delete records.

Propriety Language and Tools ◦ Used PC-based DBMS that provides simple

graphical user interface to create tables and both form-based environment and scripting language.

DBASE MNGMNT SYSTEM

Relational Database Management System (RDBMS)◦ Relational Database

A database that implements data a series of two – dimensional tables that are related via foreign keys.

◦ Schema the physical, relational database implementation of the data

model◦ Relation

simple-two dimensional tables with row as records and columns as fields.

◦ Triggers a program embedded within a table and is automatically

invoked by updates to another table.◦ Stored Procedures

a program embedded in a table that can be called from an application program.

◦ Metadata data or specification of data.

DATABASE MNGMNT SYSTEM

DBA and Database Staff◦ Handle the technical details and cross-application

issues in the design of database. Computer-assisted system engineering

(CASE)◦ can automatically generate SQL code to construct

the database structure.

Goals of Database Design◦ A database should provide for efficient storage,

update and retrieval of data.◦ A database should be reliable◦ A database should be adaptable and scalable to

new and unforeseen requirements and applications.

Modern Database Design

The Database Schema◦ Database Schema

Physical model or blueprint for a database. Rules and Regulation in transforming logical data

model to physical data model 1. Each fundamental, associative, and weak entity is

implemented as a separate table. Table names may have to be formatted according to the naming rules and size limitations of the DBMS.

Modern Database Design

Logical Entity Named Physical Table NamedProduct Name tblProdName

Member Ordered Product

tblMemberOrdProd

◦ The prefix and compression of spaces is consistent with contemporary naming standards and guidelines in modern programming languages.a. The primary key is implemented as such and implemented

as index into the table.b. Each secondary key is implemented as its own index in the

tablec. Index should be created for any non-key attributes that were

identified as sub setting criteria requirements.d. Each foreign key will be implemented as such. The inclusion

of this key implements the relationships in the data model and allows tables to be joined in SQL and application programs.

e. Attributes will be implemented with fields.

Modern Database DesignLogical Entity Named Physical Table Named

Product Name tblProdNameMember Ordered

ProducttblMemberOrdProd

i. Datatype - each DBMS supports different data types and terms.

ii. Size of the fields. Different DBMS express precision of real numbers differently.

iii. Null or not null. Different DBMS may require different reserved words to express this property

Null is a value that ma be assigned to an attribute when no other value applies or which when the applicable value is known.

Modern Database Design

Personal ,Workgroup, and Department Database ◦ Personal computer and local network database

technology that allow the users to develop personal and departmental databases.

Enterprise Database◦ Organization’s Database

Types of Database