Unit 1.

Introduction

Concepts and Applications

Objective and Evolution
Data Abstraction and Data Independence
Schema and Instances
Concepts of DDL, DML and DCL
Data , Information
• Data refers to raw, unorganized facts and • Information is a processed,
figures that can be processed to produce organized data which gives logical
information. It can be in various forms meaning
such as numbers, text, images, or sounds. – Ram is a student of CSIT 7th
Data itself does not carry any specific semester.
meaning until it is interpreted and
organized.
• Example : student ,Ram, CSIT, 7th,
Semester

2
Numerical, Text, and Media Files

• The classification into numerical, text, and media files is based on the nature and content of
the data.
• Numerical Data:
– Definition: Consists of numerical values, either discrete or continuous.
– Examples: Integers, real numbers, statistical data.
– Characteristics: Suited for mathematical operations and analysis. Common in scientific
and financial datasets.
• Text Data:
– Definition: Consists of textual information.
– Examples: Documents, emails, articles.
– Characteristics: Involves natural language, often requiring natural language processing
(NLP) techniques for analysis.
• Media Files:
– Definition: Includes a combination of various types of media, such as audio, image, and
video.
– Examples: Audio files (MP3), image files (JPEG), video files (MP4).
– Characteristics: Requires specialized tools for processing and analysis. Often involves
large amounts of unstructured data. 3
Structured, Unstructured, and Semi-
Structured Data:
• The classification of data into structured, unstructured, and semi-structured is based on the
organization and format of the data.
• Structured Data:
– Definition: Organized in a specific format with a well-defined schema.
– Examples: Relational databases, spreadsheets, tables.
– Characteristics: Follows a fixed structure, making it easy to query and analyze. Often
found in traditional database systems.
• Unstructured Data:
– Definition: Lacks a predefined data model or structure.
– Examples: Text documents, images, videos, social media posts.
– Characteristics: No clear organization; may include free-form text, multimedia, and other
content. Requires more advanced methods for analysis.
• Semi-Structured Data:
– Definition: Falls between structured and unstructured data. It has some organizational
properties but doesn't conform strictly to a rigid schema.
– Examples: JSON, XML, log files.
– Characteristics: Contains some level of structure, often in the form of tags or keys. More
flexible than structured data but not as free-form as unstructured data. 4
Structured, Unstructured, and Semi-
Structured Data

5
 Database

• A database is an organized collection of data, stored and accessed

electronically. It allows for efficient storage, retrieval, and manipulation of
data.
• Databases are used to store and manage large amounts of structured and
unstructured data, and they can be used to support a wide range of
activities, including data storage, data analysis, and data management.
• There are many different types of databases, including relational databases,
object-oriented databases, and NoSQL databases, and they can be used in a
variety of settings, including business, scientific, and government
organizations.
• Examples of databases could be: Database for Educational Institute , a
Bank, Library, Ticket Reservation system etc.

6
 Database Management System

• A Database Management System (DBMS) is software that provides tools and functionalities to create, manage, and
manipulate databases. It acts as an intermediary between users and databases, enabling efficient data management and
ensuring that data is easily accessible, secure, and consistently managed.
• Core Functions of a DBMS
• Data Definition
– Schema Definition: Defines the structure and organization of data in the database, including tables, columns, data
types, and constraints.
– Schema Modification: Allows changes to the database schema without affecting the applications that use the data.
– Schema Deletion: Facilitates the removal of database structures that are no longer needed.
• Data Manipulation
– Data Insertion: Adds new data to the database.
– Data Update: Modifies existing data in the database.
– Data Deletion: Removes data from the database.
• Data Retrieval
– Query Processing: Enables users to retrieve specific data using query languages such as SQL (Structured Query
Language).
– Report Generation: Produces reports based on the data stored in the database.
• User Administration
– User Management: Registers and monitors users, defining their access rights and roles.
– Security Management: Enforces security policies to protect data from unauthorized access and breaches.
– Concurrency Control: Manages simultaneous data access to ensure data consistency and prevent conflicts.
– Performance Monitoring: Tracks and optimizes the performance of database operations.
– Backup and Recovery: Handles data backup and recovery to prevent data loss and restore data in case of system
failures. 7
 Database Management System

• Examples of DBMS
– MySQL: An open-source relational database management system.
– Oracle Database: A multi-model database management system developed by Oracle Corporation.
– Microsoft SQL Server: A relational database management system from Microsoft.
– PostgreSQL: An open-source, advanced relational database management system.
– MongoDB: A NoSQL document-oriented database.
• Benefits of Using a DBMS
– Data Consistency and Integrity: Ensures that data remains accurate and consistent across the
database.
– Security: Provides robust security features to protect sensitive data.
– Efficient Data Management: Facilitates efficient storage, retrieval, and manipulation of data.
– Backup and Recovery: Ensures data is regularly backed up and can be recovered in case of failures.
– Scalability: Allows the database to grow with the amount of data and user load.
– Data Independence: Protects applications from changes in the database schema and storage
structures.
• A Database Management System (DBMS) is crucial for systematic and efficient data management. It
provides a structured environment to define, manipulate, retrieve, and manage data while ensuring security,
consistency, and integrity. By serving as an intermediary between the users and the database, the DBMS
simplifies data handling and enables robust data operations in various applications.

8
Database Management System

9
Traditional file processing system

• A traditional file processing system is an older method of managing and organizing

data on a computer. In this approach, data is stored in separate files, and each file is
designed for a specific application or purpose. Programs are created to manipulate
these files directly. This method was prevalent before the widespread adoption of
database management systems (DBMS). Here are key characteristics of traditional
file processing systems:
• File-Oriented Structure:
– Data is organized into files, each representing a collection of records. These
files are often specific to particular applications.
• Program-File Dependency:
– Programs are closely linked to the structure of the files. Any change in the file
structure may require modifications to all programs accessing that file.
• Data Redundancy:
– The same data may be duplicated in multiple files, leading to redundancy. This
redundancy can result in inconsistencies and increased storage requirements.

10
Traditional file processing system

• Here are key characteristics of traditional file processing

systems:
• Limited Data Sharing:
– Data sharing between different applications can be
challenging. Each program typically manages its own set of
files, making it difficult to integrate data across
applications.
• Data Dependence:
– Programs are dependent on the structure and format of the
data files. If the structure changes, programs need to be
updated accordingly.

11
Traditional file processing system
• Here are key characteristics of traditional file processing systems:
• Security and Integrity Challenges:
– Ensuring data security and integrity is primarily the responsibility of individual programs. There may
be a lack of centralized control over data access and modifications.
• Concurrent Access Issues:
– Managing concurrent access to files by multiple users or programs can be complex. File locking
mechanisms may be used to prevent conflicts, but they can be prone to issues.
• Scalability Challenges:
– As the volume of data and the number of users increase, traditional file processing systems may
become less efficient and harder to maintain.
• Maintenance Difficulties:
– Maintenance tasks, such as adding new fields or modifying file structures, can be time-consuming and
error-prone. Changes may require modifications to multiple programs.
• Limited Query Capabilities:
– Traditional file processing systems are not well-suited for complex queries and reporting. Retrieving
specific information from the data may require custom programming for each query.
• Despite these limitations, traditional file processing systems were widely used in the early days of
computing. They were suitable for simpler applications with limited data volumes and user interactions.
The advent of database management systems addressed many of the challenges posed by traditional file
processing systems, providing improved data organization, management, and security.

12
 Functionalities of a DBMS
• A Database Management System (DBMS) provides essential functionalities that facilitate the
efficient creation, organization, manipulation, and retrieval of data within a database. Here's
an elaboration on the key functionalities typically offered by a DBMS:
• Data Definition Language (DDL)
– Database Schema Definition: Allows defining the structure of the database, including
tables, their fields (columns), data types (e.g., integers, strings), and relationships
between tables.
– Constraints: Defines rules to maintain data integrity, such as primary keys, unique
constraints, foreign keys, and check constraints.
• Data Manipulation Language (DML)
– Data Insertion: Enables adding new data records (rows) into the database.
– Data Retrieval: Supports querying and retrieving specific data based on user-defined
criteria using SQL or other query languages.
– Data Modification: Allows updating existing data records in the database.
– Data Deletion: Facilitates removing data records from the database.
• Data Integrity
– Entity Integrity: Ensures that each row in a table is uniquely identified by a primary key,
preventing duplicate records.
– Referential Integrity: Maintains consistency in relationships between tables by enforcing
foreign key constraints, ensuring that references between tables remain valid. 13
 Functionalities of a DBMS
• Here's an elaboration on the key functionalities typically offered by a DBMS:
• Concurrency Control
– Manages simultaneous access to the database by multiple users or applications to prevent data
inconsistencies and conflicts.
– Implements locking mechanisms, transactions, and isolation levels to ensure that transactions are
executed in a controlled and predictable manner.
• Transaction Management
– ACID Properties: Ensures transactions are Atomic (indivisible), Consistent (follows all integrity
constraints), Isolated (executes independently of other transactions), and Durable (results are
persistent and not lost due to system failures).
• Security
– Access Control: Defines user roles, permissions, and privileges to restrict unauthorized access to
sensitive data and operations.
– Authentication and Authorization: Verifies user identities and controls what actions they can
perform within the database.
• Data Independence
– Logical Data Independence: Separates the application programs from the physical database
structure, allowing changes in the database schema without affecting the applications.
– Physical Data Independence: Separates the logical schema from the physical storage structure,
enabling changes in storage technology without affecting application programs.

14
 Functionalities of a DBMS
• Here's an elaboration on the key functionalities typically offered by a DBMS:
• Backup and Recovery
– Provides mechanisms for creating backups of the database to protect against data loss due to hardware
failures, disasters, or human errors.
– Supports recovery processes to restore the database to a consistent state after failures, ensuring data
availability and reliability.
• Query Optimization
– Analyzes and optimizes the execution plans of queries to improve performance, reduce response
times, and minimize resource consumption.
– Uses techniques such as indexing, query rewriting, and execution plan evaluation to achieve efficient
query processing.
• Data Dictionary Management
– Maintains metadata or a data dictionary that stores information about the database schema,
relationships, constraints, and other data definitions.
– Provides a centralized repository for managing and accessing database metadata, aiding in database
administration and development tasks.
• Data Communication
– Facilitates communication between the database and application programs through APIs (Application
Programming Interfaces), query languages (e.g., SQL), and data exchange formats.
– Supports connectivity protocols and standards to enable seamless interaction between databases and
diverse applications.
• These functionalities collectively enable a DBMS to effectively manage data, ensure data integrity, enforce
security, optimize performance, support concurrent access, and provide mechanisms for backup and
recovery. Different DBMS products may offer additional features and capabilities tailored to specific 15
requirements and use cases, making them essential tools in modern data-driven applications and enterprises.
Advantages of Using the DBMS Approach
• The Database Management System (DBMS) approach offers several significant advantages over traditional
file-based systems or ad-hoc data management approaches. Here are the key advantages:
• Data Integrity and Accuracy
– Enforcement of Constraints: DBMS enforces data integrity constraints such as primary key and
foreign key relationships. This ensures that data is accurate and consistent across the database.
– Reduction of Data Duplication: By centralizing data and enforcing constraints, DBMS reduces the
likelihood of data duplication and inconsistencies that can occur in file-based systems.
• Data Security
– Access Control: DBMS provides robust security features to control access to data. It includes
authentication mechanisms to verify user identities and authorization mechanisms to grant specific
permissions based on roles or privileges.
– Encryption: Supports encryption of sensitive data to protect it from unauthorized access or breaches,
ensuring data confidentiality.
• Data Independence
– Logical and Physical Independence: DBMS achieves logical data independence by separating the
application programs from the database schema. Changes to the logical structure (schema) do not
necessitate changes in the application programs. It also provides physical data independence, allowing
changes in the physical storage structure without affecting the logical data organization.
• Concurrent Data Access
– Concurrency Control: DBMS manages concurrent access to data by multiple users or applications. It
implements locking mechanisms and transaction management to ensure that transactions are executed
in isolation and maintain data consistency, even in multi-user environments. 16
Advantages of Using the DBMS Approach
• Here are the key advantages:
• Data Centralization
– Unified View of Data: DBMS centralizes data in a single database, providing a unified
and consistent view of data across the organization. This reduces data redundancy and
facilitates efficient data management and decision-making processes.
– Efficient Data Retrieval: Offers optimized query processing and indexing techniques to
enable quick and efficient data retrieval. It supports complex queries and analysis,
enhancing decision-making capabilities.
• Data Dictionary and Metadata Management
– Metadata Repository: Maintains a data dictionary or metadata repository that stores
information about the database structure, data definitions, relationships, and constraints.
This provides a centralized reference for data management and ensures consistency in
data definitions.
• Backup and Recovery
– Robust Backup Mechanisms: Implements robust mechanisms for data backup and
recovery to prevent data loss due to system failures, disasters, or human errors. This
ensures data availability and business continuity.
– Point-in-Time Recovery: Allows organizations to recover the database to a specific
transactional state before data corruption or failure occurred.
17
Advantages of Using the DBMS Approach
• Here are the key advantages:
• Maintenance and Administration
– Simplified Administration: DBMS streamlines database maintenance tasks such as
updates, backups, performance tuning, and security management. This reduces
administrative overhead and ensures efficient operation of the database system.
• Data Modeling and Design
– Support for Data Modeling: Facilitates the use of data modeling tools to design and
visualize the database structure. DBMS supports normalization processes to eliminate
data redundancy and improve data integrity, aligning with best practices in database
design.
• Data Consistency and Redundancy Control
– Consistent Data Representation: Ensures consistent data representation across the
organization, minimizing redundancy through normalization techniques. This reduces the
chances of conflicting or inconsistent data and supports accurate decision-making.
• Overall, the DBMS approach provides organizations with a robust framework for managing
and leveraging their data assets effectively. It supports data integrity, security, centralized
management, efficient data retrieval and analysis, and streamlined administration, making it
indispensable in today's data-driven environments.

18
Data abstraction

19
Data abstraction

• In the context of Database Management Systems (DBMS), data abstraction

refers to the concept of hiding the complex implementation details of the
database from the users and application programs, while exposing a
simplified and well-defined interface for interacting with the data. This
abstraction is typically achieved through the use of three levels of
abstraction in a database system:
• Physical Level:
– This is the lowest level of abstraction and deals with how the data is
actually stored on the storage media (e.g., hard disks).
– It involves details such as data structures, file organization, indexing
methods, and storage mechanisms.
– Changes at this level should not affect the higher levels of abstraction.

20
Data abstraction

• Logical Level:
– This level deals with how the data is represented and organized in the database
regardless of the underlying physical storage details.
– It defines the schema of the database, including tables, relationships,
constraints, and integrity rules.
– Changes at this level should not impact the way applications interact with the
data.
• View Level or External Level:
– This is the highest level of abstraction, and it represents how the data appears
to the end-users or application programs.
– It involves creating virtual tables or views that provide a specific subset of the
data for particular users or applications.
– Users interact with the database at this level without needing to understand the
complex underlying structures.
• The idea behind these levels of abstraction is to provide a clear separation between
the physical storage details, the logical organization of the data, and the way users
21
perceive and interact with the data.
Data Independence

22
Data Independence

• Data independence is a key concept in database management systems

(DBMS) that refers to the separation of the logical and physical aspects of
the data, allowing changes in one level to occur without affecting the other
levels. There are two types of data independence:
• Physical Data Independence:
– Physical data independence refers to the ability to modify the physical
storage structures or devices without affecting the conceptual or logical
schema.
– Changes at the physical level might include altering the file
organization, index structures, or storage mechanisms (e.g., moving
from one type of storage device to another).
– The goal is to allow for modifications to the database storage system
without requiring changes to the way data is logically represented or
accessed.
23
Data Independence

• Logical Data Independence:

– Logical data independence refers to the ability to modify
the logical schema (organization of data) without affecting
the external schema or application programs.
– Changes at the logical level might involve adding or
removing tables, modifying relationships between tables, or
changing constraints.
– The aim is to allow for modifications to the database
structure without requiring changes to the application
programs or external views that use the data.
• Achieving both types of data independence is a fundamental
goal in database design and management.
24
Schema
• Definition: A schema is a blueprint or structural design that
represents the logical organization of data in a database. It
defines the structure of the database, including tables, fields,
relationships, and constraints.
• Purpose: The schema provides a framework for organizing
and understanding how data is stored and related within a
database. It helps define the data types, relationships, and rules
that govern the data.
• In the context of databases, a schema can refer to:
– Database Schema: The overall structure of the database,
including tables, relationships, and constraints.
– Table Schema: The structure of an individual table,
specifying the columns, data types, and constraints.
25
Schema

26
Instance

• Definition: An instance refers to a specific occurrence or set

of data conforming to the structure defined by a schema. In
simpler terms, it represents the actual data stored in a database
at a particular point in time.
• Purpose: Instances are the concrete, real-world data that
adhere to the schema's specifications. Each row in a table
represents an instance of that table, and each value in a row
represents an instance of a particular attribute.
• In the context of databases, an instance can refer to:
– Database Instance: A specific snapshot or version of a
database at a particular moment.
– Table Instance (or Row): A specific set of data
conforming to the structure of a table. 27
28
Three-schema architecture

29
Three-schema architecture
• The three-schema architecture is a database architecture that was
proposed by the ANSI/X3/SPARC committee in the late 1970s. It
provides a framework for understanding the design and structure of a
database system by dividing the design into three interrelated
components or schemas. Each schema represents a different level of
abstraction and serves a specific purpose in the overall design and
management of data. The three schemas are:
• External Schema:
– Description: This schema represents how the data is viewed by
individual users or applications. It defines the specific data
elements and structures that are relevant to a particular user or
group of users.
– Purpose: The user schema focuses on providing a personalized
and simplified view of the database for end-users, shielding them
from the complexities of the underlying data model. Each user or
application may have its own view of the data, tailored to its
specific needs.
30
Three-schema architecture
• Logical Schema (or Conceptual Schema):
– Description: The logical schema represents the logical structure of the
entire database, independent of how data is viewed or accessed by end-
users or applications. It includes entities, relationships, and constraints,
providing a comprehensive and abstract representation of the data model.
– Purpose: The logical schema serves as an intermediary between the user
schema and the physical schema. It provides a conceptual framework that
is used to design and describe the overall structure of the database,
capturing the essential business rules and relationships.
• Physical Schema:
– Description: The physical schema represents how the data is stored and
organized at the physical level. It includes details such as file structures,
indexing mechanisms, and storage strategies. It is concerned with the
efficient storage and retrieval of data on the actual hardware.
– Purpose: The physical schema is closely tied to the implementation
details of the database on a specific hardware and software platform. It
addresses performance considerations, storage optimization, and other
aspects related to the physical storage and retrieval of data.
31
Three-schema architecture
• Illustrative Example:
• External Schema:
– User A: Views a simplified list of book titles and authors.
– User B: Accesses information about library members and their
borrowing history.
• Logical Schema:
– Entities: Books, Authors, LibraryMembers.
– Relationships: "Written by" (Books to Authors), "Borrowed by"
(Books to LibraryMembers).
• Physical Schema:
– Specifies how data is stored on disk, indexing strategies, and other
performance-related details.
• The three-schema architecture provides a clear separation of concerns,
making it easier to manage and maintain complex database systems. It
also facilitates changes and updates to the database without affecting
the overall structure seen by end-users 32
Database Language

• Database Language is a
special type of programming
language used to define and
manipulate a database.
• Based on their application,
database languages are
classified into four different
types: DDL, DML, DCL,
and TCL.

33
Data Definition Language

• DDL stands for Data Definition Language. It is used to define

database structure or pattern.
• It is used to create schema, tables, indexes, constraints, etc. in
the database.
• Using the DDL statements, you can create the skeleton of the
database.
• Data definition language is used to store the information of
metadata like the number of tables and schemas, their names,
indexes, columns in each table, constraints, etc.

34
Data Definition Language

Here are some tasks that come under DDL:

• Create: It is used to create objects in the database.
• Alter: It is used to alter the structure of the database.
• Drop: It is used to delete objects from the database.
• Truncate: It is used to remove all records from a table.
• Rename: It is used to rename an object.
• Comment: It is used to comment on the data dictionary.
• E.g.
create table account (
account-number char(10),
balance integer
) 35
Data Manipulation Language
• DML stands for Data Manipulation Language. It is used for accessing and
manipulating data in a database. It handles user requests.
• Here are some tasks that come under DML:
– Select: It is used to retrieve data from a database.
– Insert: It is used to insert data into a table.
– Update: It is used to update existing data within a table.
– Delete: It is used to delete all records from a table.
– Merge: It performs UPSERT operation, i.e., insert or update
operations.
– Call: It is used to call a structured query language or a Java
subprogram.
– Explain Plan: It has the parameter of explaining data.
– Lock Table: It controls concurrency.

36
Data Control Language

• DCL stands for Data Control Language. It is used to retrieve

the stored or saved data.
• The DCL execution is transactional. It also has rollback
parameters.
• (But in Oracle database, the execution of data control language
does not have the feature of rolling back.)
• Here are some tasks that come under DCL:
– Grant: It is used to give user access privileges to a
database.
– Revoke: It is used to take back permissions from the user.

37
• DDL (Data Definition Language):
– Definition: DDL is a subset of SQL (Structured Query Language) used to define
and manage the structure of a database. It includes commands for creating, altering,
and deleting database objects like tables, indexes, and views.
– Examples: CREATE, ALTER, DROP
• SDL (Storage Definition Language):
– Definition: SDL is a language or set of commands used to define how data is
physically stored in a database. It deals with aspects such as storage structures, file
organization, and indexing methods.
– Example: In some contexts, the specifications for how data is stored on disk or in
memory might be considered part of SDL.
• VDL (View Definition Language):
– Definition: VDL is a language or part of a language that allows the definition and
manipulation of views in a database. A view is a virtual table based on the result of a
SELECT query.
– Example: CREATE VIEW, ALTER VIEW, DROP VIEW.
• DML (Data Manipulation Language):
– Definition: DML is a subset of SQL used for manipulating data stored in the
database. It includes commands for querying, inserting, updating, and deleting data.
– Examples: SELECT, INSERT, UPDATE, DELETE.
38
• Procedural and Non-Procedural Languages:
– Procedural Language: A procedural language is one in which a program is written as a
series of procedures or routines. It explicitly outlines the steps to be taken to accomplish
a task. Examples include C, Pascal, and Fortran.
– Non-Procedural Language: A non-procedural language allows the programmer to
specify the desired result without explicitly specifying the steps to achieve it. SQL is a
non-procedural language commonly used for database operations. In SQL, you describe
the data you want, and the database engine determines how to retrieve it.
• Interfaces:
– Definition: In the context of databases, interfaces provide a means for different software
components to communicate with each other. They define how different entities interact
and exchange information.
– Examples:
• Database APIs: Application Programming Interfaces (APIs) like JDBC (Java
Database Connectivity) or [Link] (ActiveX Data Objects .NET) provide a way
for applications to interact with databases programmatically.
• ODBC (Open Database Connectivity): ODBC is an interface standard that enables
applications to connect to and interact with databases, providing a consistent
method of accessing different database systems.
• ORM (Object-Relational Mapping) Interfaces: ORM frameworks, such as
Hibernate for Java or Entity Framework for .NET, provide interfaces for mapping
between object-oriented code and relational database structures.
• Understanding these concepts is fundamental for anyone involved in database design,
development, or management, as they form the basis for defining and manipulating data in39a
database system.