Module 1

Introduction to Database
What is Data?
 Data is a collection of a distinct small unit of information. It can be used in a variety of
forms like text, numbers, media, bytes, etc. it can be stored in pieces of paper or
electronic memory, etc.

What is Database?
 A database is an organized collection of data, so that it can be easily accessed and
managed.
 You can organize data into tables, rows, columns, and index it to make it easier to find
relevant information.
 The database is a collection of inter-related data which is used to retrieve, insert and delete the
data efficiently. It is also used to organize the data in the form of a table, schema, views, and
reports, etc.
 There are many databases available like MySQL, Sybase, Oracle, MongoDB, Informix,
PostgreSQL, SQL Server, etc.
 A cylindrical structure is used to display the image of a database.

DBMS
o Database management system is software that is used to manage the database. For
example: MySQL, Oracle, etc are a very popular commercial database which is used in
different applications.
o DBMS provides an interface to perform various operations like database creation, storing
data in it, updating data, creating a table in the database and a lot more.
o It provides protection and security to the database. In the case of multiple users, it also
maintains data consistency.
Characteristics of DBMS
o Data integrity: The precision, completeness, and continuity of data is known as data
integrity A DBMS provides tools for enforcing data integrity. Data integrity also
ensures that the information is protected from outside influences.
o Data Security: In data security constraints, the values of data and access
controls that restrict who can access the data.
o Data Independence: Data independence refers characteristic of being able to modify
the schema at one level of the database system without altering the schema at the next
higher level.
o Concurrency control: A DBMS provides mechanisms for controlling concurrent
access to the database, to ensure that multiple users can access the data
without conflicting with each other.
o Transaction control: Transaction control enforces database integrity by ensuring that
batches of SQL operations run completely or not at all. The transaction control
commands are BEGIN, COMMIT, and ROLLBACK.
o Data Backup and recovery: A DBMS provides mechanisms for backing up and
recovering the data in the event of a system failure.

Difference between File System and DBMS

Basis DBMS Approach File System Approach

Meaning DBMS is a collection of data. In DBMS, The file system is a collection of data. In
the user is not required to write the this system, the user has to write the
procedures. procedures for managing the database.

Sharing of data Due to the centralized approach, data Data is distributed in many files, and it
sharing is easy. may be of different formats, so it isn't
easy to share data.

Data DBMS gives an abstract view of data The file system provides the detail of the
Abstraction that hides the details. data representation and storage of data.

Security and DBMS provides a good protection It isn't easy to protect a file under the file
Protection mechanism. system.

Recovery DBMS provides a crash recovery The file system doesn't have a crash
Mechanism mechanism, i.e., DBMS protects the mechanism, i.e., if the system crashes
user from system failure.
 while entering some data, then the
content of the file will be lost.

Manipulation DBMS contains a wide variety of The file system can't efficiently store and
Techniques sophisticated techniques to store and retrieve the data.
retrieve the data.

Concurrency DBMS takes care of Concurrent access In the File system, concurrent access has
Problems of data using some form of locking. many problems like redirecting the file
while deleting some information or
updating some information.

Where to use Database approach used in large File system approach used in large
systems which interrelate many files. systems which interrelate many files.

Cost The database system is expensive to The file system approach is cheaper to
design. design.

Data Due to the centralization of the In this, the files and application programs
Redundancy database, the problems of data are created by different programmers so
and redundancy and inconsistency are that there exists a lot of duplication of
Inconsistency controlled. data which may lead to inconsistency.

Structure The database structure is complex to The file system approach has a simple
design. structure.

Data In this system, Data Independence In the File system approach, there exists
Independence exists, and it can be of two types. no Data Independence.
o Logical Data Independence
o Physical Data Independence

Integrity Integrity Constraints are easy to Integrity Constraints are difficult to

Constraints apply. implement in file system.

Data Models In the database approach, 3 types of In the file system approach, there is no
data models exist: concept of data models exists.
o Hierarchal data models
o Network data models
o Relational data models
Flexibility Changes are often a necessity to the The flexibility of the system is less as
content of the data stored in any compared to the DBMS approach.
system, and these changes are more
easily with a database approach.

Examples Oracle, SQL Server, Sybase etc. Cobol, C++ etc.

Data Abstraction
 Data abstraction is the procedure of concealing irrelevant or unwanted data from the end user.
 The main purpose of data abstraction is to hide irrelevant data and provide an abstract
view of the data. With the help of data abstraction, developers hide irrelevant data from
the user and provide them the relevant data. By doing this, users can access the data
without any hassle, and the system will also work efficiently.
 For example, if you go to a shop to buy a pair of shoes, you ask the shopkeeper to show
you the shoes of a certain company, and you also tell the shopkeeper about the size, color,
and material you want. Then, you will only see the specified things in the shoes, or will
you be asking the shopkeeper questions such as, where are these shoes made? From
where does the material come? What is the cost of the material? The answer to these
questions is NO. You will not ask these questions because these questions are of no use.
You do not care about these questions. You are only concerned about a few things, such
as the company, size, color, material, and how the shoes look. That is why these
unimportant details are kept hidden from the end user. This is the process we call data
abstraction.
 In DBMS, data abstraction is performed in layers which means there are levels of data
abstraction.

Levels of Data Abstractions in DBMS

1. Physical or Internal Level:

 The physical or internal layer is the lowest level of data abstraction in the database
management system. It is the layer that defines how data is actually stored in the
database. It defines methods to access the data in the database. It defines complex data
structures in detail, so it is very complex to understand, which is why it is kept hidden
from the end user.
 Data Administrators (DBA) decide how to arrange data and where to store data. The Data
Administrator (DBA) is the person whose role is to manage the data in the database at
the physical or internal level. There is a data center that securely stores the raw data in
detail on hard drives at this level.

2. Logical or Conceptual Level:

 The logical or conceptual level is the intermediate or next level of data abstraction. It
explains what data is going to be stored in the database and what the relationship is
between them.
 It describes the structure of the entire data in the form of tables. The logical level or
conceptual level is less complex than the physical level. With the help of the logical level,
Data Administrators (DBA) abstract data from raw data present at the physical level.

3. View or External Level:

 View or External Level is the highest level of data abstraction. There are different views
at this level that define the parts of the overall data of the database. This level is for the
end-user interaction; at this level, end users can access the data based on their queries.

Advantages of data abstraction in DBMS

 Users can easily access the data based on their queries.

 It provides security to the data stored in the database.
 Database systems work efficiently because of data abstraction.

Data Independence
o Data independence can be explained using the three-schema architecture.
o Data independence refers characteristic of being able to modify the schema at one level of the
database system without altering the schema at the next higher level.
There are two types of data independence:

1. Logical Data Independence

o Logical data independence refers characteristic of being able to change the conceptual schema
without having to change the external schema.
o Logical data independence is used to separate the external level from the conceptual view.
o If we do any changes in the conceptual view of the data, then the user view of the data would not
be affected.
o Logical data independence occurs at the user interface level.

2. Physical Data Independence

o Physical data independence can be defined as the capacity to change the internal schema without
having to change the conceptual schema.
o If we do any changes in the storage size of the database system server, then the Conceptual
structure of the database will not be affected.
o Physical data independence is used to separate conceptual levels from the internal levels.
o Physical data independence occurs at the logical interface level.
Architecture of Database Management System

Database Management System (DBMS) is software that allows access to data stored in a
database.

1. Query Processor: It interprets the requests (queries) received from end user via an
application program into instructions. It also executes the user request which is
received from the DML compiler.
Query Processor contains the following components –
 DML Compiler: It processes the DML statements into low level
instruction (machine language), so that they can be executed.
 DDL Interpreter: It processes the DDL statements into a set of table
containing meta data (data about data).
 Embedded DML Pre-compiler: It processes DML statements embedded
in an application program into procedural calls.
 Query Optimizer: It executes the instruction generated by DML Compiler.
2. Storage Manager: Storage Manager is a program that provides an interface
between the data stored in the database and the queries received. It is also known as
Database Control System. It maintains the consistency and integrity of the database by
applying the constraints and executing the DCL statements. It is responsible for
updating, storing, deleting, and retrieving data in the database.
It contains the following components –
 Authorization Manager: It ensures role-based access control, i.e.
checks whether the particular person is privileged to perform the
requested operation or not.
 Integrity Manager: It checks the integrity constraints when the
database is modified.
 Transaction Manager: It controls concurrent access by performing
the operations in a scheduled way that it receives the transaction.
Thus, it ensures that the database remains in the consistent state
before and after the execution of a transaction.
 File Manager: It manages the file space and the data structure
used to represent information in the database.
 Buffer Manager: It is responsible for cache memory and the
transfer of data between the secondary storage and main
memory.

3. Disk Storage: It contains the following components –

 Data Files: It stores the data.
 Data Dictionary: It contains the information about the structure of any
database object. It is the repository of information that governs the metadata.
 Indices: It provides faster retrieval of data item.
Database Administrator(DBA)
 The database administrator is in charge of managing the primary (database) and
secondary (DBMS and related software) (DBA).
 The DBA is in charge of granting access to the database, organizing and overseeing its use,
and procuring necessary hardware and software resources.
 A Database Administrator (DBA) is an individual or person responsible for controlling,
maintaining, coordinating, and operating a database management system.
 Managing, securing, and taking care of the database systems is a prime responsibility.
They are responsible and in charge of authorizing access to the database, coordinating,
capacity, planning, installation, and monitoring uses, and acquiring and gathering
software and hardware resources as and when needed.
 Their role also varies from configuration, database design, migration, security,
troubleshooting, backup, and data recovery.
 Database administration is a major and key function in any firm or organization that is
relying on one or more databases.

Different types of Database Users

Database users are categorized based up on their interaction with the database. These
are seven types of database users in DBMS.
1. Database Administrator (DBA): Database Administrator (DBA) is a person/team who
defines the schema and also controls the 3 levels of database. The DBA will then
create a new account id and password for the user if he/she need to access the
database. DBA is also responsible for providing security to the database and he
allows only the authorized users to access/modify the data base. DBA is responsible
for the problems such as security breaches and poor system response time.
 DBA also monitors the recovery and backup and provide technical support.
 The DBA has a DBA account in the DBMS which called a system or superuser
account.
 DBA repairs damage caused due to hardware and/or software failures.
 DBA is the one having privileges to perform DCL (Data Control Language)
operations such as GRANT and REVOKE, to allow/restrict a particular user from
accessing the database.
2. Naive / Parametric End Users: Parametric End Users are the unsophisticated who
don’t have any DBMS knowledge but they frequently use the database applications
in their daily life to get the desired results. For examples, Railway’s ticket booking
 users are naive users. Clerks in any bank is a naive user because they don’t have any
DBMS knowledge but they still use the database and perform their given task.
3. System Analyst :
System Analyst is a user who analyzes the requirements of parametric end users.
They check whether all the requirements of end users are satisfied.
4. Sophisticated Users: Sophisticated users can be engineers, scientists, business
analyst, who are familiar with the database. They can develop their own database
applications according to their requirement. They don’t write the program code but
they interact the database by writing SQL queries directly through the query
processor.
5. Database Designers: Data Base Designers are the users who design the structure of
database which includes tables, indexes, views, triggers, stored procedures and
constraints which are usually enforced before the database is created or populated
with data. He/she controls what data must be stored and how the data items to be
related. It is responsibility of Database Designers to understand the requirements of
different user groups and then create a design which satisfies the need of all the user
groups.
6. Application Programmers: Application Programmers also referred as System
Analysts or simply Software Engineers, are the back-end programmers who writes
the code for the application programs. They are the computer professionals. These
programs could be written in Programming languages such as Visual Basic,
Developer, C, FORTRAN, COBOL etc. Application programmers design, debug, test,
and maintain set of programs called “canned transactions” for the Naive
(parametric) users in order to interact with database.
7. Casual Users / Temporary Users: Casual Users are the users who occasionally
use/access the database but each time when they access the database they require
the new information, for example, Middle or higher level manager.
8. Specialized users : Specialized users are sophisticated users who write
specialized database application that does not fit into the traditional data-
processing framework. Among these applications are computer aided-design
systems, knowledge-base and expert systems etc.