CHAPTER 6

Database

Mr. Roshan Bhattarai

 Organizing Business Data
• Many businesses do not have timely, accurate or
relevant information
• Data in organizations have been poorly organized and
maintained
• Inaccurate, untimely and irrelevant information leads to
–poor planning,
–poor decision making,
–poor controlling,
–poor coordinating etc
which leads to business failure/potential loss
 File organization terms and concepts
Data Hierarchy

1. Bit : 0, 1 (smallest unit of data)

2. Byte (Character) : 8 bits (Eg: 0100 1000- Letter H in ASCII)
3. Field : Collection of interrelated characters
(Eg: Name – Ram , Address – Kathmandu)
4. Record : Collection of interrelated fields
(Eg: Information of Ram (Name, Address and Phone)
5. File : Collection of interrelated records
(Eg: Information of all students of BBA 1 st semester)
6. Database : Collection of interrelated files
(Eg: Information of all students of BBA and MBA, Faculty members, staffs,
and owners of Ace College)
 The Database approach to Data Management
Database: Systematic and organized collection of
interrelated data centrally, so that it can
be retrieved easily in the future

DBMS: Software that permits an organization to

centralize data, manage them efficiently and
provide access to stored data by application
programs

Solves many problems of the traditional

data file approach
 Components of DBMS
Data definition language (DDL):
• Specifies content and structure of database
• Used to create database tables and to define characteristics
of the fields in each table
Data manipulation language (DML):
• Used to access and manipulate information in databases
• Used to Add, Change, Delete, and Retrieve data in a
database
Data dictionary (Meta data):
• Stores definitions of data elements and their characteristics
 Introduction to Data Processing
• Data : collection of unorganized raw facts (INPUT)
• Processing : series of actions or operations that convert input
(data) into output(information).
• Information: Processed data , can be used to help people
make decisions (OUTPUT)
• “Data processing is defined as series of actions or operations
that converts data into useful information.”

Actions and Operations

DATA INFORMATION

Fig: data processing

 Data Vs Information

Data Information
• Data are raw facts (INPUT) • Information is processed
data (OUTPUT)
• Data are usually in • Information is usually in
unorganized form organized form

• Data is usually not used in • Information is usually used

decision making in decision making
– Eg: Sales data is items name, – Sales information is overall
amount of sales etc sales of the day, sales
amount by salespersons
 File Processing
• is the process of creating, storing and accessing content of files
• can be either sequential or direct/random access file processing
Sequential File Processing
• working through the input file one record at a time in the
sequence
Magnetic tape : the major example of sequential file processing
• Suppose there are 8 music tracks stored in tape.
• If you want to listen track 6, then it cannot be directly listened
• All previous tracks i.e. track 1, track 2…..track 5 should be listened
or forwarded. Only then track 6 can be listened

Figure: Sequential File Processing

Advantages of Sequential File Processing
• Easy to organize and maintain
• Relatively inexpensive I/O media and devices can be used for
the storage and processing of such files
• It is efficient and economical file organization in case of
applications in which there are a large number of file records
to be updated at regularly scheduled intervals.
• There is no overhead in address generation.
• Applications such as payroll processing, billing and statement
preparation, and bank cheque processing meet these
conditions.
Disadvantages of Sequential File Processing
• The access to, and retrieval of a record is slow and indirect.
• It is inefficient and uneconomical for applications in which
files need to updated randomly /frequently
• accumulation of transactions into batches is required before
processing them.
• Transactions must be sorted and placed in sequence prior to
processing.
Direct-access File processing
• The file processing in which data can be directly recorded and
accessed without sequence
• CD-ROM, Hard Disk, DVD-ROM, Pen drive etc are some example
of storage devices in which data are processed randomly.
• For example, if there are 100 mp3 music tracks in a CD and you
want to listen 75th track, it doesn’t require to view all previous
tracks. You can directly access the 75th track.

Figure: Direct-access File Processing

Advantages of Direct-access File Processing
• The access to, and retrieval of a record is quick and direct.
• Transactions need not be sorted and placed in sequence prior
to processing.
• Accumulation of transactions into batches is not required
before processing them.
Disadvantages of Direct-access File Processing
• relatively expensive hardware and software resources are
required
• Address generation overhead is involved for accessing each
record
• May be less efficient in the use of storage space than
sequentially organized files.
 Advantages of using a Computerized
Database/DBMS
1. Centralized control of data
2. Redundancy can be reduced (no data duplication)
3. Inconsistency can be avoided (no different content in same
files at multiple places)
4. Data can be shared/Easy Access
5. Security restriction can be applied (Data Security)
6. Synchronization (simultaneous access from multiple
locations)
7. Backup and Recovery
8. Flexibility in reporting
 Disadvantages of using a Computerized
Database/DBMS
• Cost of Hardware
• Cost of Software
• Maintenance cost
• Training and development cost
• Skillful technical manpower required
• High cost of database failure (risk level is high)
• Complexity and size
 E-R (Entity-Relationship) Model

• The Entity-Relationship (ER) data model allows us to

– describe the data involved in a real-world enterprise in terms of
entities/objects and their relationships
• widely used to develop an initial database design
• A basic component of the model is the Entity-Relationship
diagram, which is used to visually represent data objects.
 Symbols used in ER diagram
• Different symbols are used to represent entity, its attributes
and its relationship with other entities

Entity Multi-valued Attribute

Relationships

Attributes

Key Attribute

Derived Attribute
 E-R diagram for Library Management
System
• Entities: Book, Student, Librarian
• Attributes
– Book (B_code, B_title, B_price, B_author, B_edition,
B_publication)
– Student (S_roll, S_name, S_gender, S_phone, S_dob,
S_address, S_program)
– Librarian (L_id, L_name, L_shift, L_phone, L_gender,
L_address)
 S_name B_title
S_roll B_code B_author
S_phone

Student requests Book

B_price

S_dob S_age B_publisher

verifies issues

Librarian

L_id L_phone

L_name

Fig: ER diagram of Library Management

System
 E-R diagram for Banking System
• Entities: Customer, Employee, Loan, Account
• Attributes
– Customer (C_id, C_name, C_phone, C_address, C_dob,
C_occupation, C_gender)
– Employee (E_id, E_name, E_gender, E_phone, E_dob,
E_address, E_dept, E_designation)
– Loan (L_id, L_type, L_amount, L_issuedate,
L_maturitydate, L_rate)
– Account (A_id, A_type, A_balance, A_status)
 L_amount
L_id L_type

Loan
E_id
C_name applies approves

Customer deals_with Employee

C_phone E_name
C_id creates/
requests
verifies
A_no A_type

Account
Fig: ER diagram of Banking System
 E-R diagram for Hospital Management
System
• Entities: Doctor, Patient, Ward, Medicine, Employee
• Attributes:
– Doctor (D_id, D_name, D_dept, D_specilization, D_phone,
D_address, D_gender)
– Patient (P_id, P_name, P_phone, P_gender, P_address)
– Ward (W_id, W_name, W_type)
– Medicine (M_id, M_name, M_price, M_mdate, M_edate,
M_company)
– Employee (E_id, E_degsination, E_name, E_dept,
E_gender)
 W_name
W_id W_type
E_name
E_id
Ward
manages
P_id
Employee assigned

admits
E_dept Patient
E_degn
hires
P_name
attends
takes/
bills
M_name

Doctor
prescribes
Medicine
D_id M_id
D_name
Fig: ER diagram of Hospital Management
D_phone System
 E-R diagram for College Management
System
• Entities: Student, Course, Lecturer, Admin
• Attributes:
– Student (S_id, S_name, S_program, S_address, S_dob,
S_gender, S_phone, S_email)
– Course (C_code, C_name, C_credit)
– Lecturer (L_id, L_name, L_qualification, L_experience,
L_email, L_dept)
– Admin (A_id, A_designation, A_phone, A_address,
A_email, A_dept)
 L_name
L_id L_dept
C_id
Lecturer
S_name facilitate
evaluates s

registers
Student Course

S_phone C_name
S_id
admits
A_name offers
A_id

Admin

A_dept A_degn

Fig: ER diagram of Banking System

 RDBMS- Relational Database
Management System
• An RDBMS is a DBMS designed specifically for relational
databases.
• Relational database refers to a database that stores data in a
structured format (2-D tables), using rows and columns
• This makes it easy to locate and access specific values within
the database.
• It is "relational" because the values within each table are
related to each other, tables may also be related to other tables
• The relational structure makes it possible to run queries across
multiple tables at once.
• RDBMS is a software that executes queries on the data,
including adding, updating, and searching for values.
• Examples include Oracle Database, MySQL, Microsoft SQL
Server, Microsoft Access, IBM DB2 etc. 
• Examples of non-relational databases include Apache HBase,
IBM Domino etc.
• The data in an RDBMS is stored in database objects which are
called as tables (relations).
• Table is basically a collection of related data entries and it
consists of numerous columns and rows.
• It is the most common and simplest form of data storage in a
relational database.
• The following is an example of a customer table
CUSTOMER
• Every table is broken up into smaller entities called fields.
• A field is a column in a table that is designed to maintain
specific information about every record in the table.
• The fields in the CUSTOMER table consist of ID, NAME, AGE,
ADDRESS and SALARY.

• A Record is also called as a row of data is each individual

entry that exists in a table (also called a tuple).

For eg, there are 7 records in the above CUSTOMER table

 Primary Key and Foreign Key
Primary Key
• A primary key is chosen by the database designer as the principal
means of identifying entities within an entity set.
• A primary key is an attribute assigned to an object/entity which
uniquely identifies it (eg: StudentID, EmployeeID etc)
• Primary keys must be chosen with care.
• The name of a person is obviously not sufficient, because there may
be many people with the same name.
• In US, the social-security number attribute of a person would be a
primary key since it uniquely identifies a particular person.
Table name Attributes/fields
Employee
Headings { EmpID EmpName EmpAddress EmpGender

{
Emp001 Ram Newroad M
Emp002 Rita Kalanki F tuples/records
body Emp003 Gopal Gaushala M
Foreign Key
• A foreign key refers to a field (in a referencing table) that uniquely
identifies a primary key field of some other table (referenced table).
• It establishes a link between records in two different tables in a
database. 
• A foreign key is a reference to a key in another table meaning that the
referencing table has, as one of its attributes, the values of a key in
the referenced table.
Foreign Key
• The figure below shows relational model. Primary
Primary Key
Manager
Employee Key
MID Mdept Mlevel EmpID
EmpID EmpName EmpAdd EmpGdr
M01 Finance M1 Emp002
Emp001 Ram Newroad M
M02 Marketing M2 Emp004
Emp002 Rita Kalanki F
M03 Operations M1 Emp005
Emp003 Gopal Gaushala M
Emp004 Shyam Koteshwor M Referencing relation
Emp005 Hari Patan M
Referenced relation
• Here, EmpID and MID are the primary keys in Employee table
and Manager table respectively.
• These two tables are interlinked through EmpID attribute in
the Manager table, which is a foreign key in Manager table.
• Database management system packages based on the
relational model can link data elements from various tables to
provide information to users.
• The relational model can relate data in any one file with data
in another file if both files share a common data element or
field.
• Because of this, information can be created by retrieving data
from multiple files even if they are not all stored in the same
physical location.
Basic Operations on Relational Databases
There are three operations on relational databases:
• The SELECT operation selects all records in a table that meet a
certain criterion
• The JOIN operation joins or links two or more tables
• The PROJECT operation creates a subset of columns designed
to meet the information needs of the user

THANK YOU!!