Module: I

Introduction to Database System

Define the following terms.
Data
Facts that can be recorded or stored.
E.g. Person Name, Age, Gender and Weight etc.
Information
When data is processed, organized, structured or presented in a given context so as to
make it useful, it is called information.
Database
A Database is a collection of inter-related (logically-related) data.
E.g. Books Database in Library, Student Database in University etc.
DBMS (Database Management System)
A database management system is a collection of inter-related data and set of programs
to manipulate those data.
DBMS = Database + Set of programs
E.g. MS SQL Server, Oracle, My SQL, SQLite, MongoDB etc.
Metadata
Metadata is data about data.
Data such as table name, column name, data type, authorized user and user access
privileges for any table is called metadata for that table.
Data dictionary
Data dictionary is an information repository which contains metadata.
It is usually a part of the system catalog.
Data warehouse
A data warehouse is an information repository which stores data.
It is designed to facilitate reporting and analysis.
Field
A field is a character or group of characters that have a specific meaning.
It is also called a data item. It is represented in the database by a value.
For Example customer id, name, society and city are all fields for customer Data.
Record
A record is a collection of logically related fields.
For example, collection of fields (id, name, address & city) forms a record for customers.

DBMS Module 1​ ​ ​ ​ ​ ​ ​ Made By- Sanyam Sheth

📘 Concepts and Definitions: Database,
Database System and Database Environment
A Database is an organized collection of related data that is stored and managed in a structured
way so that it can be easily accessed, updated, and maintained.

Data in a database is logically connected and represents real-world entities such as students,
employees, products, customers, etc. Unlike traditional file systems where data is stored
separately in multiple files, a database integrates data into a single centralized structure.

For example, in a college database:

●​ Student information​

●​ Faculty information​

●​ Course details​

●​ Examination records​

All this data is stored in a structured and related manner.

Characteristics of a Database:

●​ Data is organized and structured.​

●​ Data represents real-world entities.​

●​ Data is shared among multiple users.

DBMS Module 1​ ​ ​ ​ ​ ​ ​ Made By- Sanyam Sheth

A Database System consists of the database itself along with the software that manages it. This
software is known as the Database Management System (DBMS).

In simple words:

Database System = Database + DBMS + Application Programs + Users

A DBMS is software that allows users to create, maintain, and control access to the database.

Examples of popular DBMS software include:

●​ Oracle Database​

●​ MySQL

Database Environment

A Database Environment refers to the complete setup in which a database system operates. It
includes all the components that interact to manage and use data effectively.

The database environment consists of the following components:

1. Hardware

Physical devices such as computers, servers, storage devices, and network equipment where the
database resides.

2. Software

Includes:

●​ DBMS software​

●​ Operating system​

●​ Application programs​

●​ Utility software​

3. Data

DBMS Module 1​ ​ ​ ​ ​ ​ ​ Made By- Sanyam Sheth

The actual stored data in the database, along with metadata (data about data).

4. Procedures

Rules and instructions for designing, using, and maintaining the database.

5. People (Users)

Different types of users interact with the database:

●​ Database Administrator (DBA)​

●​ Database Designers​

●​ Application Programmers​

●​ End Users​

The database environment ensures proper coordination among hardware, software, data,
procedures, and users.

Data Item

A Data Item, also called a Field or Attribute, is the smallest unit of meaningful data stored in a
database.

It represents a single piece of information about an entity. In a table structure, a field corresponds
to a column.

Record
A Record is a collection of related fields that describe a single entity instance. In a relational
table, a record corresponds to a row (also called a tuple).

File

A File is a collection of related records stored together.

In traditional file systems, data was stored in separate files. For example:

DBMS Module 1​ ​ ​ ​ ​ ​ ​ Made By- Sanyam Sheth

 ●​ Student file​

●​ Employee file​

●​ Product file​

Each file contains many records of the same type.

Metadata

Metadata means “data about data.”

It describes the structure, properties, and characteristics of actual stored data.

For example:

●​ Table name​

●​ Column names​

●​ Data types​

●​ Constraints​

●​ Size of fields​

●​ Relationships between tables​

If we say:​
Student_ID is INTEGER and PRIMARY KEY,

This description is metadata.

Metadata helps the DBMS understand how data is organized and how it should be processed.

Without metadata, the database system cannot interpret stored data properly.

DBMS Module 1​ ​ ​ ​ ​ ​ ​ Made By- Sanyam Sheth

System Catalog

A System Catalog is a special set of tables maintained by the DBMS that stores metadata.

It is also called a Data Dictionary.

The system catalog contains information about:

●​ Tables​

●​ Views​

●​ Indexes​

●​ Users​

●​ Constraints​

●​ Storage details​

●​ Access permissions​

Whenever a user creates a table, the DBMS automatically updates the system catalog with its
structure information.

For example, when you run a command to show all tables in a database, the DBMS retrieves this
information from the system catalog.

Thus:

System Catalog = Collection of Metadata stored by DBMS.

It plays an important role in query processing, security management, and database

administration.

Data Warehouse

A Data Warehouse is a centralized repository that stores large volumes of historical data
collected from different sources.

Unlike operational databases that handle daily transactions, a data warehouse is used for analysis
and decision-making.

DBMS Module 1​ ​ ​ ​ ​ ​ ​ Made By- Sanyam Sheth

A data warehouse has the following characteristics:

●​ Subject-oriented (organized around business subjects like sales, finance)​

●​ Integrated (data collected from multiple sources)​

●​ Time-variant (stores historical data)​

●​ Non-volatile (data is not frequently changed)​

Data is extracted from various operational databases, transformed, and loaded into the warehouse
using an ETL (Extract, Transform, Load) process.

Organizations use data warehouses for:

●​ Business intelligence​

●​ Data analysis​

●​ Reporting​

●​ Forecasting​

●​ Strategic planning​

Thus:

Database → Used for daily operations​

Data Warehouse → Used for analysis and decision support

Data Dictionary and Its Components

A Data Dictionary is a centralized repository that stores information about the structure of a
database. It contains definitions and descriptions of all data elements used in the database
system. Because it describes the data itself, it is often referred to as a metadata repository (data
about data).

The Data Dictionary helps maintain consistency, integrity, and control over the database system
by clearly defining how data is structured and used.

DBMS Module 1​ ​ ​ ​ ​ ​ ​ Made By- Sanyam Sheth

Whenever a table, view, or index is created in a DBMS, the system automatically stores its
description in special internal tables. These internal tables together form the Data Dictionary.

For example, if a table named STUDENT is created with fields:

●​ Student_ID (Integer, Primary Key)​

●​ Name (Varchar)​

●​ Age (Integer)

Data Administrator (DA) and Database Administrator (DBA)

DA (Data Administrator) DBA (Database Administrator)

The data administrator is a person in the The database administrator is a person in the
organization who controls the data of the organization who controls the design and the
database. use of the database.

DA determines what data to be stored in DBA provides necessary technical support for
database based on requirements of the implementing a database.
organization.

DA is involved more in the requirements DBA is involved more in the design,

gathering, analysis, and design phases. development, testing and operational phases.

DA is a manager or some senior level person DBA is a technical person having knowledge
in an organization who understands of
organizational requirements with respect to database technology.
data.

DA does not need to be a technical person, DBA does not need to be a business person,
but but any kind of knowledge about a
any kind of knowledge about database functionality of an organization can be more
technology can be more beneficiary. beneficiary.

DA is a business focused person, but, he/she DBA is a technically focused person, but,
should understand more about the database he/she should understand more about the
technology. business to administer the databases
effectively.

DBMS Module 1​ ​ ​ ​ ​ ​ ​ Made By- Sanyam Sheth

Functions and Responsibilities of DBA

DBA
The full name of DBA is Database Administrator.
Database Administrator is a person in the organization who controls the design and the
use of database.
Functions or Responsibilities of DBA are as under:
Schema Definition
DBA defines the logical schema of the database.
A schema refers to the overall logical structure of the database.
According to this schema, database will be designed to store required data for an
organization.
Storage Structure and Access Method Definition
DBA decides how the data is to be represented in the database.
Based on this, storage structure of the database and access methods of data is defined.
Defining Security and Integrity Constraints
DBA decides various security and integrity constraints.
DDL (Data Definition Language) provides facilities to specifying such constraints.
Granting of Authorization for Data Access
The DBA determines which user needs access to which part of the database.
According to this, various types of authorizations (permissions) are granted to different
users.
This is required to prevent unauthorized access of a database.
Liaison with Users
DBA is responsible to provide necessary data to user.
User should be able to write the external schema, using DDL (Data Definition Language).
Assisting Application Programmers
DBA provides assistance to application programmers to develop application programs.
Monitoring Performance
The DBA monitors performance of the system.
The DBA ensures that better performance is maintained by making change in physical or
logical schema if required.
Backup and Recovery
Database should not be lost or damaged.
The task of DBA is to backing up the database on some storage devices such as DVD, CD
or Magnetic Tape or remote servers.
In case of failures, such as flood or virus attack, Database is recovered from this backup.

DBMS Module 1​ ​ ​ ​ ​ ​ ​ Made By- Sanyam Sheth

Explain disadvantages of file system
Data Redundancy
It is possible that the same information may be duplicated in different files. This leads to
data redundancy.
Data redundancy results in memory wastage.
For example, consider that some customers have both kinds of accounts - saving and
current. In this case, data about customers such as name, address, e-mail and contact
number will be duplicated in both files, saving accounts file and current account file.
In other words, same information will be stored in two different locations (files). And, it
wastes memory.
Data Inconsistency
Due to data redundancy, it is possible that data may not be in consistent state.
For example, consider that an address of some customer changes. And, that customer has
both kinds of accounts. Now, it is possible that this changed address is updated in only
one file, leaving address in other file as it is. As a result of this, same customer will have
two different addresses in two different files, making data inconsistent.
Difficulty in Accessing Data
Accessing data is not convenient and efficient in file processing system.
For example, suppose, there is a program to find information about all customers. But,
what if there is a need to find out all customers from some particular city. In this case,
there are two choices here: One, find out all customers using available program, and then
extract the needed customers manually. Second, develop new program to get required
information. Both options are not satisfactory.
For each and every different kind of data access, separate programs are required. This is
neither convenient nor efficient.
Limited Data Sharing
Data are scattered in various files.
Different files may have different formats. And these files may be stored in different
folders (directories) may be of different computers of different departments.
So, due to this data isolation, it is difficult to share data among different applications.
Integrity Problems
Data integrity means that the data contained in the database is both correct and
consistent. For this purpose, the data stored in database must satisfy certain types of
constraints (rules).
For example, a balance for any account must not be less than zero. Such constraints are
enforced in the system by adding appropriate code in application programs. But, when
new constraints are added, such as balance should not be less than Rs. 5000, application
programs need to be changed. But, it is not an easy task to change programs whenever
required.
Atomicity Problems

DBMS Module 1​ ​ ​ ​ ​ ​ ​ Made By- Sanyam Sheth

Any operation on database must be atomic. This means, operation completes either 100%
or 0%.
For example, a fund transfer from one account to another must happen in its entirely.
But, computer systems are vulnerable to failure, such as system crash, virus attack. If a
system failure occurs during the execution of fund transfer operation, it may possible that
amount to be transferred, say, Rs. 500, is debited from one account, but is not credited
to another account.
This leaves database in consistent state. But, it is difficult to ensure atomicity in a file
processing system.
Concurrent Access Anomalies
Multiple users are allowed to access data simultaneously (concurrently). This is for the
sake of better performance and faster response.
Consider an operation to debit (withdrawal) an account. The program reads the old
balance, calculates the new balance, and writes new balance back to database. Suppose
an account has a balance of Rs. 5000. Now, a concurrent withdrawal of Rs. 1000 and Rs.
2000 may leave the balance Rs. 4000 or Rs. 3000 depending upon their completion time
rather than the correct value of Rs. 2000.
Here, concurrent data access should be allowed under some supervision.
But, due to lack of co-ordination among different application programs, this is not
possible in file processing systems.
Security Problems
Database should be accessible to users in a limited way.
Each user should be allowed to access data concerning his application only.
For example, a customer can check balance only for his/her own account. He/She should
not have access for information about other accounts.
But, in file processing system, application programs are added in an ad hoc manner by
different programmers. So, it is difficult to enforce such kind of security constraints.

Explain advantages (benefits) of DBMS over file management system.

Minimal Data Redundancy (Duplication)
Due to centralized database, it is possible to avoid unnecessary duplication of
information.
This leads to reduce data redundancy.
It prevents memory wastage and reduces extra processing time to get required data.
Shared Data
All authorized user and application program can share database easily.
Data Consistency
Data inconsistency occurs due to data redundancy.

DBMS Module 1​ ​ ​ ​ ​ ​ ​ Made By- Sanyam Sheth

With reduced data redundancy such type of data inconsistency can be eliminated.
This results in improved data consistency.
Data Access
DBMS utilizes a variety of techniques to retrieve data.
Required data can be retrieved by providing appropriate query to the DBMS.
Thus, data can be accessed in convenient and efficient manner.
Data Integrity
Data in database must be correct and consistent.
So, data stored in database must satisfy certain types of constraints (rules).
DBMS provides different ways to implement such type of constraints (rules).
This improves data integrity in a database.
Data Security
Database should be accessible to user in a limited way.
DBMS provides way to control the access to data for different user according to their
requirement.
It prevents unauthorized access to data.
Thus, security can be improved.
Concurrent Access
Multiple users are allowed to access data simultaneously.
Concurrent access to centralized data can be allowed under some supervision.
This results in better performance of system and faster response.
Guaranteed Atomicity
Any operation on database must be atomic. This means, operation must be executed
either 100% or 0%.
This type of atomicity is guaranteed in DBMS.

What is the Difference between File System and DBMS?

The file system is a collection of data and for any management with it, the user has to
write the procedures, while DBMS is a collection of data and user need not write the
procedures for handling the database. Learn about what is the difference between a file
system and DBMS from the table given.

DBMS Module 1​ ​ ​ ​ ​ ​ ​ Made By- Sanyam Sheth

Difference between File System and DBMS

FILE SYSTEM DBMS

Used to manage and organise the files A software to store and retrieve the user’s
stored in the hard disk of the computer data

Redundant data is present No presence of redundant data

Query processing is not so efficient Query processing is efficient

Data consistency is low Due to the process of normalisation, the

data consistency is high

Less complex, does not support More complexity in managing the data,
complicated transactions easier to implement complicated
transactions

Less security Supports more security mechanisms

Less expensive in comparison to Higher cost than the File system

DBMS

Does not support crash recovery Crash recovery mechanism is highly

supported

DBMS Module 1​ ​ ​ ​ ​ ​ ​ Made By- Sanyam Sheth

DBMS Module 1​ ​ ​ ​ ​ ​ ​ Made By- Sanyam Sheth