Chapter #02
DATA BASE MANAGEMENT SYSTEM
DATA
Data
A necessity for almost any enterprise to carry out its
business. Consists of raw facts, and when organized may be
transformed into information
Database
A collection of data organized to meet users’ needs
Database management system (DBMS)
A group of programs that manipulate the database and
provide an interface between the database and the user of
the database or other application programs
DBMS ‘DISCUSSION’ (1)
A collection of programs that enables you to store, modify, and
extract information from a database. The following
are examples of database applications:
computerized library systems
automated teller machines
flight reservation systems
computerized parts inventory systems
From a technical standpoint, DBMSs can differ widely. The terms
DBMS ‘DISCUSSION’ (2)
relational, network, flat, and hierarchical all refer to the way a
DBMS organizes information internally. The internal organization can
affect how quickly and flexibly you can extract information.
Requests for information from a database are made in the form of a
query, which is a stylized question. For example, the query
SELECT ALL WHERE NAME = "SMITH" AND AGE > 35
requests all records in which the NAME field is SMITH and the AGE
DBMS ‘DISCUSSION’ (3)
field is greater than 35. The set of rules for constructing queries is
known as a query language. Different DBMSs support different
query
languages, such as called SQL (structured query language).
The information from a database can be presented in a variety of
formats. Most DBMSs include a report writer program that enables
you to output data in the form of a report. Many DBMSs also include
a graphics component that enables you to output information in the
form of graphs and charts.
DATA MANAGEMENT
Without managing data organizations can not
Pay employee
Send out bills
Order new inventory
Produce information to assist managers in decision
making
THE HIERARCHY OF DATA
THE HIERARCHY OF DATA
“Together, characters, fields, records, files
and databases form the hierarchy of data.”
1. Characters:-
A basic building block of information
Consisting of uppercase letters, lowercase
letters, numeric digits, or special symbols.
THE HIERARCHY OF DATA
2. Field:-
A combination of one or more characters is called filed.
It describe an aspect of a business object or activity.
A field is identified by field name.
Data type specifies the type of data that can be stored in the field
Field size defines the maximum characters that can be stored in a
field.
For example employee_no, name, designation and salary
THE HIERARCHY OF DATA
3. Record:-
“A collection of related fields .”
A student record is a collection of fields
about one student.
For example her name, address, Ph# ,
marks date etc
THE HIERARCHY OF DATA
4. File:-
“A collection of related records.”
A file is also known as data set.
For example an student file is a collection
of all students record.
THE HIERARCHY OF DATA
5. Database:-
“A collection of integrated and related files.”
For example collectively personnel file,
department file, payroll file
DATA ENTITIES
DATA ENTITIES
Entity:-
“An entity is anything about which data is
stored in the database.”
Example of entity a person, event, place etc
ATTRIBUTE
“A characteristic of an entity.”
Attributes are selected to capture the
relevant characteristics of entities such as
students or customers.
The specific value of an attribute is called a
data item.
KEYS AND ATTRIBUTES
Employee # Last name First name Hire date Dept. #
005-10-6321 Johns Francine 10-7-65 257
549-77-1001 Buckley Bill 2-17-79 650
098-40-1370 Fiske Steven 1-5-85 598
Key field Entities
Attributes (fields)
(records)
KEY
“A field or set of fields in a record that is used to identify the
record.”
Its purpose is to access record according to the requirement.
Types of Keys:-
Primary Key
Secondary Key/Alternate Key
Composite Key/Concatenate Key
Foreign key
1. PRIMARY KEY
The attribute or set of attributes that uniquely
identifies the record.
A relation has only one primary key.
Each value in primary key attribute must be
unique.
e.g.
Roll number
2. SECONDARY KEY
Any key other than primary key used to
identify records uniqely
e.g.
First name and last name of employees
A primary key that consists of two or more attributes is
known as composite key.
For example Registration Number and Subject name.
A foreign key is an attribute or set of attributes in a
relation whose value match a primary key in another table.
For example roll number in student table and roll number
in instructor table.
Traditional Approach\ Traditional File Processing System
“An approach where separate data files are
created and stored for each application
program.”
Data Files Application Users
programs
Payroll Reports
Payroll
programs
Invoicing Reports
Invoicing programs
Inventory
Inventory Reports
control
control programs
Management
Management Reports
inquiries
inquiries programs
Traditional Approach
Disadvantages:---
1. Data Redundancy:--
A duplication of data in one or two files.
Flaw is that changes to the data might be in
one file and not in other file.
The order processing department might have
updated its file to new address but the billing
department is still sending bills to old address
Traditional Approach
Disadvantages:---
2. Data Integrity:--
The degree to which the data in anyone file is
accurate.
It is achieved by eliminating the data redundancy.
Business operation requires a high degree of data
integrity which is not achieved by file system.
For example keeping a customer's address in only
one file is not possible
TRADITIONAL APPROACH
Disadvantages:---
Program-data dependence
A situation in which program and data organized for one
application are incompatible with programs and data
organized differently for another application
Traditional Approach
Disadvantages:---
3. Lengthy development time:--
4. Limited data sharing :--
Database Approach to Data Management
A database is a collection of logically related files.
An approach whereby a pool of related data is
shared by multiple application programs.
A software DBMS is used to use the database
approach to data management.
Database Management Software:
A group of programs that manipulate the database and
provide interface between database and its users and
other programs
Database Approach to Data Management
Reports
Payroll
program
Payroll data
Inventory Inventory
Reports
data program
Database
management
Invoicing system
Data Invoicing
Reports
program
Other
data
Other Reports
programs
Database Interface Applications programs Users
ADVANTAGES
Improved strategic use of corporate data
Reduced data redundancy
Improved data integrity
Easier modification and updating
Data and program independence
Better access to data and information
Standardization of data access
A framework for program development
Better overall protection of the data
Shared data and information resources
DISADVANTAGES
Relatively
high cost of purchasing and operating a
DBMS in a mainframe operating environment
Increased cost of specialized staff
Increased vulnerability
Traditional Vs. Database Approach
1. The programs and data 1. The programs and
are interdependent. data are
2. The data may be independent of
duplicated in different each other.
files that cause data 2. The data is not
redundancy. duplicated, so no
3. The same data in data redundancy.
different files may be 3. The data appears
different that cause only once so it is
data inconsistency. always consistent.
Traditional Vs. Database Approach
4. The data is distributed in 4. The data is stored at
different files and one place and can
cannot be shared. be shared easily.
5. The data integrity checks 5. It provide constraints
are difficult to apply on for data integrity.
files. 6. It provide data
6. It provide poor security security.
as data is widely spread. 7. It is very complex
7. It is less complex system.
system. 8. It is costly system
8. The cost is very less than and more chance of
DBMS. failure
TRADITIONAL VS. DATABASE
APPROACH
TYPES OF DATA BASE
Flat file
single user
Multiple users
DATABASE MANAGEMENT ADMINISTRATION
Database administration is the function of
managing and maintaining database
management systems (DBMS) software.
POPULAR DATABASE MANAGEMENT SYSTEMS
Microsoft Access
SQL SEVER
Oracle
My SQL
DATA BASE ADMINISTRATOR
Is a skilled and trained Information System
professional who directs all activities related to
an organization’s database including providing
security from invasions
DATABASE APPLICATION
• Data Where house
• Data Marts
• Data Mining
DATA WAREHOUSE
Data warehouse
A relational database management system designed
specifically to support management decision making
Current evolution of Decision Support Systems (DSSs)
Data mart
A subset of a data warehouse for small and medium-size
businesses or departments within larger companies
DATA WAREHOUSE
Relational
databases
Data
Hierarchical extraction
databases process
Network
databases Data
cleanup
process
Flat files
Spreadsheets Data
End user access wharehouse
Query and
analysis
tools
DATA WAREHOUSE
A database that collects business information from many sources
in the enterprise, covering all aspects of the company’s processes,
products, and customers.
DATA MART
A subset of a data warehouse.
DATA MINING
An information analysis tool that involves
the automated discovery of patterns and
relationships in a data warehouse
RDBMS
RDBMS stands for Relational database management
system.
“RDBMS is used for maintaining
relational database.”
It is based on relational model.
DBMS FUNCTIONS
DBMS perform many functions but some of them are here.
1. Providing a user view
2. Creating and modifying the database
3. Storing and retrieving data
4. Manipulating data and generating reports
DBMS FUNCTIONS
1. Providing a user view
DBMS contain schema and subschema
Schema:
A description of entire database.
SCHEMA
SCHEMA DESCRIPTION
SCHEMA NAME IS XXXX
AUTHOR XXXX
DATE XXXX
FILE DESCRIPTION
FILE NAME IS XXXX
ASSIGN XXXX
FILE NAME IS XXXX
ASSIGN XXXX
AREA DESCRIPTION
AREA NAME IS XXXX
RECORD DESCRIPTION
RECORD NAME ISXXXX
RECORD ID IS XXXX
LOCATION MODE ISXXXX
WITHIN XXX AREA FROM XXXX THRU XXXX
SET DESCRIPTION
SET NAME IS XXXX
ORDER IS XXXX
MODE IS XXXX
MEMBER IS XXXX
.
.
SCHEMAS AND SUBSCHEMAS
Schema
A description of the entire database. In a relational
database, the schema defines the tables, the fields in
each table, and the relationships between fields
and tables.
SCHEMAS AND SUBSCHEMAS
Subschema
Afile that contains a description of a subset of the
database and identifies which users can perform
modifications on the data items in that subset
It defines user view.
User View
The portion of the database a user can
access and manipulate.
THE USE OF SCHEMAS AND SUBSCHEMAS
description of entire database.
description of
subset of schema.
DBMS FUNCTIONS
2. Creating and modifying the database
Schema and Subschema are entered into DBMS via DDL
DDL stands for data definition language.
“DDL is a collection of instructions and
commands used to define and describe data and
data relationships in a specific database.”
DD stands for Data Dictionary
“DD is the detailed description of data in a
database.”
Data Dictionary
NORTHWESTERN MANUFACTURING
PREPARED BY: D. BORDWELL
DATE: 04 AUGUST
APPROVED BY: J. EDWARDS DATE: 13 OCTOBER
VERSION: 3.1
PAGE: 1 OF 1
DATA ELEMENT NAME: PARTNO
DESCRIPTION: INVENTORY PART NUMER
OTHER NAMES: PTNO
VALUE RANGE: 100 TO 5000
DATA TYPE: NUMERIC
POSITIONS: 4 POSITIONS OR COLUMNS
DBMS FUNCTIONS
Typical Uses of a Data Dictionary
Provide a standard definition of terms and data elements
Assist programmers in designing and writing programs
Simplify database modification
Reduce data redundancy
Increase data reliability
Speed program development
Ease modification of data and information
DBMS FUNCTIONS
3. Storing and retrieving data
DBMS provide interface between application programs and
database.
An application request for data through DBMS.
When more persons or programs access the same data
simultaneously, then it causes a problem.
Concurrency control is used to avoid the problem by locking
all other programs except one which updated the record
DBMS FUNCTIONS
4. Creating and modifying the database
Manipulating data and generating reports
DBMS is used by end users and managers to generate
reports.
Reports are generated with the help of either
QBE(Query-By-Example):-
A GUI way to generate reports.
DML (Data Manipulation Language)
Areused to manipulate data in a database.
SQL is its example
SQL STRUCTURED QUERY LANGUAGE
DATA MODELING
Data model
Enterprise data modeling
Entity-relationship diagrams
DATA MODELLING
“ Data modeling involves understanding a
business problem and analyzing the data and
information needed to deliver a solution.”
DATA MODELING
Data model
A map or diagram of entities and their relationships
Enterprise data modeling
Data modeling done at the level of the entire organization
DATA MODELLING
• Example of data model.
• Entity–relationship [ER]diagram:-
The data models that use the basic
graphical symbols to show the
organization of and relationships
between data.
• Boxes shows entities or data items.
• Diamond shows relationship between
entities and data items.
RELATIONSHIPS
Relationships are the connections and
interactions between the entities instances, e.g.,
Program and Student are linked
HOW TO IDENTIFY RELATIONSHIPS?
Up to you
If there is no proper name of the association
in the system then participants’ names of
abbreviations are used
NAMING
STUDENT and CLASS have ENROLL relationship
However, it can also be named as STD_CLS
SYMBOL FOR RELATIONSHIPS
DEPT EMP
STD BOOK
ENTITY-RELATIONSHIP DIAGRAM FOR A CUSTOMER
ORDERING DATABASE
Attributes
Last name Colour
Entities
First 1 M
Customer Order Product Name
name
1:M one-to-many
relationship
Identification Identification
number number
DATA MODEL
• “A diagram of data entities and their
relationships.”
• It’s a tool used by data designer to show the
logical relationships among data.
DATA MODEL
There are many types of data models but
mostly used are
1. Hierarchical Data model
2. Network Data Model
3. Relational Data Model
HIERARCHICAL DATABASE MODEL
Hierarchical database model
A data model in which data are organized in a top-
down, or inverted tree structure
HIERARCHICAL DATABASE MODEL
It arranges records in hierarchy like an organizational
charts.
Each record in this model is called a node or segment.
A node represent a particular entity.
The top–most node is called root.
Each node is the subordinate of the next higher node.
A higher level node is called parent and lower level node
is called child.
A parent can have one or many child nodes.
A child node can have only one parent node.
HIERARCHICAL DATABASE MODEL
Project 1
Department A Department B Department C
Employee Employee Employee Employee Employee Employee
1 2 3 4 5 6
NETWORK DATA MODEL
An expansion of the hierarchical database model with a
Parent-child member relationship in which a child may
have many parents
Project 1 Project 2
Department A Department B Department C
RELATIONAL MODELS
“A database model that describe data using a standard
tabular format with all data elements placed in two-
dimensional tables, called relations, that are the
logical equivalent of files.”
Each row represents data entity.
Each columns represents attributes.
The allowable values for data attributes is called
domain.
For example negative values for pay rate is not allowed.
It is more flexible than other models
RELATIONAL MODELS
• It allows data manipulation which includes
– Selecting:-
• Means eliminates rows according to certain
criteria.
– Projecting:-
• Means eliminates the column in a table.
– Joining:-
• Means joins two or more tables.
– Linking:-
• Means links two or more tables using common
data attributes.
RELATIONAL DATABASE MODEL
76
Project Number Description Dept. Number
155 Payroll 257
498 Widgets 632
226 Sales manager 598
Dept. Number Dept. Name Manager SSN
257 Accounting 421-55-99993
632 Manufacturing 765-00-3192
598 Marketing 098-40-1370
SSN Last Name First Name Hire Date Dept. Number
005-10-6321 Johns Francine 10-7-65 257
549-77-1001 Buckley Bill 2-17-79 650
098-40-1370 Fiske Steven 1-5-85 598
