Database Management System

Unit A - Introduction to database: Introduction to Data, Field, Record, File, Database, Database
management system, Characteristics of the Data Base, Database users, Schemas and Instances,
Database State, DBMS architecture and Data Independence, Data base languages & Interfaces,
components of a database system, Data Models: Record Based data model, Hierarchical Data Model
and Network data Model.

DATA - Data are facts, observations, or information, often organized into numbers, words, or
measurements, that can be collected, processed, and analyzed to provide context and meaning. This
processed information leads to valuable insights, which are then used for decision-making, problem-
solving, and informing strategies across various fields. Data can be broadly categorized as quantitative
(numerical) or qualitative (descriptive) and is fundamental to fields like statistics, computer science,
and data science.

What we do with Data -

 Collection: Data is gathered through observations, measurements, and research.

 Organization: Data is often structured intosssssss graphs, charts, or tables to make it easier to
analyze.

 Processing and Analysis: Raw data is transformed through cleaning, transformation, and analysis
to extract meaningful patterns and insights.

Types of Data:

 Quantitative Data: Data that can be measured and expressed as numbers, such as age, height, or
sales figures.

 Qualitative Data: Descriptive data that cannot be easily measured with numbers, such as
customer feedback or colors.

Why Data Matters -

 Informed Decision-Making: Organizations use data to make better, evidence-based decisions.

 Problem-Solving: Data helps identify the root causes of problems and develop e ective solutions.

 Performance Measurement: Data allows for the tracking and measurement of performance and
success over time.

 Innovation: Insights from data can lead to the development of new products and services tailored
to customer needs.

FIELD - A Field is a single, specific piece of data within a table, representing a single attribute of an
entity. It's essentially a column in a table, holding a particular type of information for all records in that
column. Fields are the fundamental building blocks of a database, providing the structure and
organization for storing and managing information.

Key Points -

 Structure: In a table, fields are represented as columns, while each row represents a record.

 Purpose: Fields are used to store specific information about an entity. For example, in an
"Employees" table, fields might include "Employee ID", "Name", "Position", and "Salary".
 Data Types: Each field has a defined data type (e.g., text, number, date, boolean) to ensure data
consistency and validity.

 Example: In a table about customers, a field could be "Customer Name," and it would contain the
names of all the customers.

 Relationships: Fields play a crucial role in defining relationships between di erent tables in a
relational database, often through primary and foreign keys.

RECORD - In a database management system (DBMS), a record represents a single, complete entry or
instance of data, typically stored as a row in a table. It's a collection of related data values, each
corresponding to a specific attribute or column in the table. Think of it like a row in a spreadsheet,
where each cell contains a piece of information about a particular entity. Records are the fundamental
units of data storage and retrieval in a DBMS, allowing for the e icient organization and management of
information.

Key Points -

 Structure: Records are organized into tables, with each table containing multiple records.

 Fields/Columns: Each record is composed of fields (also known as columns), which represent the
di erent attributes or characteristics of the entity.

 Primary Key: A primary key is a unique identifier for each record within a table, ensuring that each
record can be distinguished from others.

 Data Types: Fields within a record can store di erent data types, such as text, numbers, dates, etc.

 Example: A customer record in a customer table might contain fields like customer ID, name,
address, phone number, and email.

FILE- In the context of a Database Management System (DBMS), a file is a fundamental unit for storing
and organizing data on secondary storage devices, such as hard disks. It functions as a collection of
related records, which are themselves composed of fields or attributes.

Key Points -

 Collection of Records: A file is essentially a structured collection of individual records. Each

record represents a single entry in a database table and holds specific information about an entity.

 Physical Storage: Files are the means by which the logical structure of a database (tables, records)
is mapped onto the physical storage medium. They are divided into blocks on the disk, with each
block containing a number of records.

 File Organization: The way records are arranged within a file is known as file organization. This
organization method significantly impacts the e iciency of data retrieval, insertion, deletion, and
modification operations. Common file organization methods include sequential, indexed
sequential, direct (hashing), and clustered file organizations.

 Accessing Records: Records within a file can be accessed using various methods, often relying on
a primary key or other indexing structures to e iciently locate specific data.

 Relationship to Database: A database is a collection of files, each designed to store specific types
of data, such as customer information, product details, or order records. The DBMS manages these
files to ensure data integrity, consistency, and e icient access.
Problems with Traditional File-Based Systems

Before the introduction of modern DBMS, data was managed using basic file systems on hard
drives. While this approach allowed users to store, retrieve and update files as needed, it came with
numerous challenges

 Data Redundancy: Duplicate entries across files

 Inconsistency: Conflicting or outdated information

 Di icult Access: Manual file search required

 Poor Security: No control over data access

 Single-User Access: No support for collaboration

 No Backup/Recovery: Data loss was often permanent

DATABASE-

Database is an organized collection of structured data stored electronically in a computer system,

designed for easy access, management, and updating. It's typically controlled by a database
management system (DBMS), a software that acts as an interface between the database and users
or applications, allowing for e icient retrieval and manipulation of information. Databases can
contain various types of data, such as words, numbers, images, and files, and are used in
everything from managing personal contacts to supporting complex enterprise-level systems.

Key Points -

 Organized Data: Data is structured, often in tables with rows and columns, allowing for
e icient retrieval.

 Electronic Storage: Data is stored in a computer system, enabling large collections to be

managed.

 Database Management System (DBMS): Software that manages the database, handling tasks
like data storage, retrieval, and modification.

 Access & Manipulation: Users can easily query, manage, update, and delete data as needed.

 Scalability: Databases are built to hold large volumes of organized information and can be
scaled for di erent applications.

How It Works -

1. Data Collection: Raw data is gathered from various sources.

2. Structuring: The data is organized into a logical structure, often using tables, to define
relationships between di erent pieces of information.

3. Storage: The organized data is stored electronically on a computer system, sometimes on a

dedicated database server.

4. Management: A DBMS provides an interface and tools to interact with the database.

5. Access: Users or applications interact with the DBMS using queries, often written in Structured
Query Language (SQL), to retrieve, update, or insert data.
 Uses of Database -

 Personal Use: Storing contacts or a digital library catalog.

 Business: Managing customer information, sales records, and inventory.

 Healthcare: Storing patient health records to improve care.

 Social Media: Storing user profiles and content for personalized experiences.

 E-commerce: Handling online transactions and product information.

DATABASE MANAGEMENT SYSTEM –

A Database Management System (DBMS) is a software that allows users to create, define, store,
manage, and retrieve data in an organized and structured way. It acts as an interface between users or
applications and the database itself, ensuring data is accessible, secure, and consistent. Key functions
include data manipulation (create, update, delete), data security, data integrity, and data recovery.

What a DBMS does:

 Manages Data: Organizes large amounts of data, making it easy to find and use.

 Provides an Interface: Acts as a mediator, allowing users and applications to interact with the
database using various languages, such as SQL.

 Ensures Data Integrity: Maintains data accuracy and consistency by preventing inconsistencies
and ensuring that data adheres to defined rules.

 Handles Security: Protects data from unauthorized access through user authentication and
permissions.

 O ers Recovery and Backup: Provides mechanisms for backing up data and recovering it in case
of system failures.

 Supports Data Manipulation: Enables users to perform operations like adding, changing, and
deleting data within the database.

Types of DBMS -

There are several types of Database Management Systems (DBMS), each tailored to di erent data
structures, scalability requirements and application needs. The most common types are as follows:

1. Relational Database Management System (RDBMS)

 It organizes data into tables (relations) composed of rows and columns.

 Uses primary keys to uniquely identify rows and foreign keys to establish relationships between
tables.

 Queries are written in SQL (Structured Query Language), which allows for e icient data
manipulation and retrieval.

Examples: MySQL oracle, Microsoft SQL Server and Postgre SQL.

2. NoSQL DBMS
 They are designed to handle large-scale data and provide high performance for scenarios where
relational models might be restrictive.

 They store data in various non-relational formats, such as key-value pairs, documents, graphs or
columns.

 These flexible data models enable rapid scaling and are well-suited for unstructured or semi-
structured data.

Examples: MongoDB, Cassandra, DynamoDB and Redis.

3. Object-Oriented DBMS (OODBMS)

 It integrates object-oriented programming concepts into the database environment, allowing data
to be stored as objects.

 Supports complex data types and relationships, making it ideal for applications requiring advanced
data modeling and real-world simulations.

Examples: ObjectDB, db4o.

4. Hierarchical Database

 Organizes data in a tree-like structure, where each record (node) has a single parent and have
multiple children.

 This model is similar to a file system with folders and subfolders.

 It is e icient for storing data with a clear hierarchy, such as organizational charts or file
directories.

 Navigation is fast and predictable due to the fixed structure.

 It lacks flexibility and di icult to restructure or handle complex many-to-many relationships.

Example: IBM Information Management System (IMS).

5. Network Database

 It uses a graph-like model to allow more complex relationships between entities.

 Unlike the hierarchical model, it permits each child to have multiple parents, enabling many-to-
many relationships.

 Data is represented using records and sets, where sets define the relationships.

 It is more flexible than the hierarchical model and better suited for applications with complex data
linkages.

Example: Integrated Data Store (IDS), TurboIMAGE.

6. Cloud-Based Database

 They are hosted on cloud computing platforms like AWS, Azure or Google Cloud.

 They o er on-demand scalability, high availability, automatic backups and remote accessibility.
 These databases can be relational (SQL) or non-relational (NoSQL) and are maintained by cloud
service providers, reducing administrative overhead.

 They support modern application requirements, including distributed access and real-time
analytics.

Example: Amazon RDS (for SQL), MongoDB Atlas (for NoSQL), Google BigQuery.

CHARACTERISTICS OF THE DATA BASE –

 Data Storage: A database provides a centralized, structured location to store vast quantities of data
e iciently.

 Data Organization: Data is organized into logical structures like tables with rows and columns,
defining relationships between di erent data elements.

 Data Retrieval: Users can query the database to find specific information using various search
criteria.

 Data Manipulation: Databases allow users to insert new data, update existing data, and delete
unwanted data.

 Data Integrity: Databases enforce rules and constraints to maintain the accuracy, consistency, and
validity of the data.

 Data Security: Features like user authentication, access controls, and encryption protect data
from unauthorized access.

 Concurrency Control: Databases manage multiple users accessing and modifying the data
simultaneously without causing conflicts.

 Backup and Recovery: They provide mechanisms to create backups of the data and recover it in
case of system failure or other disasters.

LEVELS OF DBMS –

The three levels present in this architecture are Physical level, Conceptual level and External level.

Physical Level - This is the lowest level in the three level architecture. It is also known as the internal
level. The physical level describes how data is actually stored in the database. In the lowest level, this
data is stored in the external hard drives in the form of bits and at a little high level, it can be said that
the data is stored in files and folders. The physical level also discusses compression and encryption
techniques.

Conceptual Level - The conceptual level is at a higher level than the physical level. It is also known as
the logical level. It describes how the database appears to the users conceptually and the relationships
between various data tables. The conceptual level does not care for how the data in the database is
actually stored.

External Level - This is the highest level in the three level architecture and closest to the user. It is also
known as the view level. The external level only shows the relevant database content to the users in the
form of views and hides the rest of the data. So di erent users can see the database as a di erent view
as per their individual requirements.

DATABASE USERS –
Database users are individuals or applications that interact with a database, accessing, modifying, or
managing data and database structures. Their roles and permissions vary significantly based on their
specific responsibilities and the needs of the organization.

Common types of database users include:

 Database Administrators (DBAs): Responsible for the overall management and maintenance of
the database system, including security, performance tuning, backup and recovery, and user
access control.

 Database Designers: Involved in the initial design and creation of the database schema, defining
tables, relationships, and data integrity constraints.

 Application Programmers: Develop and maintain applications that interact with the database,
writing code to retrieve, insert, update, and delete data.

 End-Users: Interact with the database through applications or user interfaces to perform daily
tasks, such as entering data, generating reports, or querying information.

 Sophisticated Users: Directly interact with the database using query languages like SQL to retrieve
and analyze data, often for reporting or analytical purposes, without necessarily writing application
programs.

 Specialized Users: Develop and use specialized database applications, such as those for
computer-aided design (CAD), multimedia databases, or expert systems, which may require
specific knowledge or tools beyond typical database interactions.

Each type of user is granted specific privileges and permissions within the database to ensure data
security and integrity, allowing them to perform their designated tasks while preventing unauthorized
access or modifications.

DATABASE SCHEMA-

A database schema is the design or structure of a database that defines how data is organized and how
di erent data elements relate to each other. It acts as a blueprint, outlining tables, fields, relationships,
and rules that govern the data.

Key points:

 It defines how data is logically organized, including tables, fields, and relationships.

 It outlines the relationships between entities, such as primary and foreign keys.

 It helps resolve issues with unstructured data by organizing it in a clear, structured way.

 Database schemas guide how data is accessed, modified, and maintained.

In simple terms, the schema provides the framework that makes it easier to understand, manage, and
use data in a database. It’s created by database designers to ensure the data is consistent and
e iciently organized.

Types of Database Schemas

Physical Database Schema - A physical schema defines how data is stored in the storage system,
including the arrangement of files, indices and other storage structures. It specifies the actual code and
syntax needed to create the database structure. Essentially, it determines where and how the data is
stored in the physical storage medium.

The database administrator decides the storage locations and organization of data within the storage
blocks. This schema represents the lowest level of abstraction

Logical Database Schema - A logical database schema defines the logical structure of the data,
including tables, views, relationships, and integrity constraints. It describes how data is organized in
tables and how the attributes of these tables are connected. The logical schema ensures that the data
is stored in an organized manner, while maintaining data integrity.

Using Entity-Relationship (ER) modeling, the logical schema outlines the relationships between
di erent data components. It also defines integrity constraints to ensure the quality of data during
insertion and updates.

This schema represents a higher level of abstraction compared to the physical schema, focusing on
logical constraints and how the data is structured, without dealing with the physical storage details.

View Database Schema - The view schema is the highest level of abstraction in a database, focusing
on how users interact with the database. It defines the interface through which users can access and
manipulate data, without needing to understand the underlying storage mechanisms.

A database can have multiple view schemas, also known as subschemas, each providing a di erent
perspective of the data. These schemas describe only a part of the database.

DATABASE INSTANCE -

A database instance is a snapshot of a database at a specific moment in time, containing all the
properties described by a database schema as data values. Unlike database schemas, which are
considered the "blueprint" of a database, instances can change over time whereas it is very di icult to
modify the schema because the schema represents the fundamental structure of the database.
Database instance does not hold any information related to the saved data in database.

Database schema versus database instance

Aspect Database Schema Database Instance

Blueprint or design of the database Actual data stored in the database

Definition structure at a given time

Dynamic (changes with every data

Static (does not change frequently)
Nature modification)

Structure (tables, columns, data types,

State of the data in the database
Represents relationships)
Aspect Database Schema Database Instance

Example Table definitions, data types, constraints Actual rows of data in the tables

Change Changes infrequently (e.g., during schema Changes frequently with

Frequency design changes) transactions

DATABASE STATE – A database is always in one specific state. For example, these states include
ONLINE, OFFLINE, or SUSPECT.

State Definition

ONLINE Database is available for access. The primary filegroup is online, although the undo phase of
recovery may not have been completed.

OFFLINE Database is unavailable. A database becomes o line by explicit user action and remains o line
until additional user action is taken. For example, the database may be taken o line in order to
move a file to a new disk. The database is then brought back online after the move has been
completed.

RESTORING One or more files of the primary filegroup are being restored, or one or more secondary files are
being restored o line. The database is unavailable.

RECOVERING Database is being recovered. The recovering process is a transient state; the database will
automatically become online if the recovery succeeds. If the recovery fails, the database will
become suspect. The database is unavailable.

RECOVERY SQL Server has encountered a resource-related error during recovery. The database is not
PENDING damaged, but files may be missing or system resource limitations may be preventing it from
starting. The database is unavailable. Additional action by the user is required to resolve the
error and let the recovery process be completed.

SUSPECT At least the primary filegroup is suspect and may be damaged. The database cannot be
recovered during startup of SQL Server. The database is unavailable. Additional action by the
user is required to resolve the problem.

EMERGENCY User has changed the database and set the status to EMERGENCY. The database is in single-
user mode and may be repaired or restored. The database is marked READ_ONLY, logging is
disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is
primarily used for troubleshooting purposes. For example, a database marked as suspect can
be set to the EMERGENCY state. This could permit the system administrator read-only access
to the database. Only members of the sysadmin fixed server role can set a database to the
EMERGENCY state.

DBMS ARCHITECTURE –
A DBMS architecture defines how users interact with the database to read, write, or update information.
A well-designed architecture and schema (a blueprint detailing tables, fields and relationships) ensure
data consistency, improve performance and keep data secure.

Types of DBMS Architecture

There are several types of DBMS Architecture that we use according to the usage requirements.

 1-Tier Architecture

 2-Tier Architecture

 3-Tier Architecture

1-Tier Architecture

In 1-Tier Architecture, the user works directly with the database on the same system. This means the
client, server and database are all in one application. The user can open the application, interact with
the data and perform tasks without needing a separate server or network connection.

 A common example is Microsoft Excel. Everything from the user interface to the logic and data
storage happens on the same device. The user enters data, performs calculations and saves files
directly on their computer.

 This setup is simple and easy to use, making it ideal for personal or standalone applications. It does
not require a network or complex setup, which is why it's often used in small-scale or individual use
cases.

 This architecture is simple and works well for personal, standalone applications where no external
server or network connection is needed.

Advantages of 1-Tier Architecture

Below mentioned are the advantages of 1-Tier Architecture.

 Simple Architecture: 1-Tier Architecture is the most simple architecture to set up, as only a single
machine is required to maintain it.

 Cost-E ective: No additional hardware is required for implementing 1-Tier Architecture, which
makes it cost-e ective.

 Easy to Implement: 1-Tier Architecture can be easily deployed and hence it is mostly used in small
projects.

Disadvantages of 1-Tier Architecture

 Limited to Single User: Only one person can use the application at a time. It’s not designed for
multiple users or teamwork.

 Poor Security: Since everything is on the same machine, if someone gets access to the system,
they can access both the data and the application easily.

 No Centralized Control: Data is stored locally, so there's no central database. This makes it hard to
manage or back up data across multiple devices.

 Hard to Share Data: Sharing data between users is di icult because everything is stored on one
computer.
2-Tier Architecture

The 2-tier architecture is similar to a basic client-server model. The application at the client end directly
communicates with the database on the server side. APIs like ODBC and JDBC are used for this
interaction. The server side is responsible for providing query processing and transaction management
functionalities.

 On the client side, the user interfaces and application programs are run. The application on the
client side establishes a connection with the server side to communicate with the DBMS. For
Example: A Library Management System used in schools or small organizations is a classic example
of two-tier architecture.

 Client Layer (Tier 1): This is the user interface that library sta or users interact with. For example
they might use a desktop application to search for books, issue them, or check due dates.

 Database Layer (Tier 2): The database server stores all the library records such as book details,
user information and transaction logs.

 The client layer sends a request (like searching for a book) to the database layer which processes it
and sends back the result. This separation allows the client to focus on the user interface, while the
server handles data storage and retrieval.

Advantages of 2-Tier Architecture

 Easy to Access: 2-Tier Architecture makes easy access to the database, which makes fast retrieval.

 Scalable: We can scale the database easily, by adding clients or upgrading hardware.

 Low Cost: 2-Tier Architecture is cheaper than 3-Tier Architecture and Multi-Tier Architecture.

 Easy Deployment: 2-Tier Architecture is easier to deploy than 3-Tier Architecture.

 Simple: 2-Tier Architecture is easily understandable as well as simple because of only two
components.

Disadvantages of 2-Tier Architecture

 Limited Scalability: As the number of users increases, the system performance can slow down
because the server gets overloaded with too many requests.

 Security Issues: Clients connect directly to the database, which can make the system more
vulnerable to attacks or data leaks.

 Tight Coupling: The client and the server are closely linked. If the database changes, the client
application often needs to be updated too.

 Di icult Maintenance: Managing updates, fixing bugs, or adding features becomes harder when
the number of users or systems increases.

3-Tier Architecture

In 3-Tier Architecture, there is another layer between the client and the server. The client does not
directly communicate with the server. Instead, it interacts with an application server which further
communicates with the database system and then the query processing and transaction management
takes place. This intermediate layer acts as a medium for the exchange of partially processed data
between the server and the client. This type of architecture is used in the case of large web
applications.

Example: E-commerce Store

 User: You visit an online store, search for a product and add it to your cart.

 Processing: The system checks if the product is in stock, calculates the total price and applies any
discounts.

 Database: The product details, your cart and order history are stored in the database for future
reference.

Advantages of 3-Tier Architecture

 Enhanced scalability: Scalability is enhanced due to the distributed deployment of application

servers. Now, individual connections need not be made between the client and server.

 Data Integrity: 3-Tier Architecture maintains Data Integrity. Since there is a middle layer between
the client and the server, data corruption can be avoided/removed.

 Security: 3-Tier Architecture Improves Security. This type of model prevents direct interaction of the
client with the server thereby reducing access to unauthorized data.

Disadvantages of 3-Tier Architecture

 More Complex: 3-Tier Architecture is more complex in comparison to 2-Tier Architecture.

Communication Points are also doubled in 3-Tier Architecture.

 Di icult to Interact: It becomes di icult for this sort of interaction to take place due to the
presence of middle layers.

 Slower Response Time: Since the request passes through an extra layer (application server), it may
take more time to get a response compared to 2-Tier systems.

 Higher Cost: Setting up and maintaining three separate layers (client, server and database)
requires more hardware, software and skilled people. This makes it more expensive.

DATA INDEPENDENCE –

Data Independence is a fundamental concept in Database Management Systems (DBMS) that refers to
the ability to modify the schema at one level of the database without a ecting the schema at the next
higher level. This concept ensures that changes in how data is stored or structured internally do not
impact how users or applications access and interact with the data.

Why is Data Independence Important?

 Reduces Maintenance: Developers don't need to update applications every time the database
structure changes.

 Increases Flexibility: The database can be reorganized or optimized internally without a ecting
user queries.

 Supports Long-Term Growth: As business needs evolve, the database can be updated without
breaking existing systems.

To understand data independence, it’s essential to know how DBMS is organized:

1. Internal Level : Deals with physical storage (files, indexing, compression).

2. Conceptual Level : Describes structure like tables, fields and relationships.

3. View Level : Defines how users and applications see the data.

Data independence exists between these levels:

 Physical Data Independence: Change in the internal level without a ecting the logical level.

 Logical Data Independence: Change in the logical level without a ecting the view level.

Types of Data Independence

Data Independence is the ability to change the database schema at one level without a ecting the
schema at other levels. It helps in maintaining flexibility, reducing maintenance and ensuring that
applications continue to work despite internal changes in the database. There are two types of data
independence.

Logical Data Independence

Ability to change the logical structure (tables, columns, relationships) without a ecting external views
or application programs. Purpose of this to allow the database structure to evolve without impacting
user access or requiring changes in application code.

Why it matters:

 Helps modify the structure of the database as business needs evolve.

 Applications and user interfaces remain una ected.

Example:

 Adding a column like email in the employees table.

 Creating a new relationship between two tables.

 Merging two tables into a view for simplified access.

Benefits:

 Easier maintenance of application code.

 Allows smoother updates in growing systems.

 Helps support new business requirements without rewriting existing queries.

Physical Data Independence

The ability to change how data is physically stored without a ecting the logical schema or user-facing
applications. Purpose is to improve performance, storage e iciency, or hardware configurations
without changing how the data is structured logically.

Why it matters:

 Enables performance tuning and hardware changes

 Does not a ect how data is structured or queried

Example:

 Moving data files from the C: drive to the D: drive.

 Creating an index to speed up queries.

 Switching from HDD to SSD for better performance.

 Compressing data files to save space.

Benefits:

 Backend optimizations without a ecting users

 Reduces need for structural changes during storage upgrades

 Improves long-term maintainability

Di erence Between Physical and Logical Data Independence

Physical Data Independence Logical Data Independence

Focuses on how data is stored physically Focuses on structure and organization of data.

Deals with the internal schema Deals with the conceptual schema

Changes may require updates in application

Changes don’t a ect application programs
programs

It tells about the internal schema. It tells about the conceptual schema.

Easier to achieve More di icult to achieve

Used for performance and storage

Used for evolving database design
optimization

Example: Moving data files or adding indexes Example: Adding or removing a column in a table

DATA BASE LANGUAGES –

Database languages are used to read, store and update the data in the database. Specific languages
are used to perform various operations of the database.

Types of Database Languages

DDL(Data Definition Language) - Data Definition Language(DDL) is used for describing structures or
patterns and its relationship in a database. It is also used to define the database schema, tables, index,
Constraints, etc. It can also be used to store information like the number of tables, names, columns,
indexes, etc. The commands only a ect the database structure and not the data.

The commands used in DDL are:

 Create: It is used to create a database or table.

 Alter: It is used to make a change in the structure of a database.
 Drop: It is used to completely delete a table from the database
 Rename: It is used to rename a table.
 Truncate: It is used to delete the entities inside the table while holding the structure of the table.
 Comment: It is used to comment on the data dictionary.

DML(Data Manipulation Language) -DML is used to manipulate the data present in the table or
database. We can easily perform operations such as store, modify, update, and delete on the database.

The commands used in DML are:

 Select: It shows the record of the specific table. Also, it can be used with a WHERE clause to get
the particular record.
 Insert: It allows users to insert data into the database or tables.
 Update: It is used to update or modify the existing data in database tables.
 Delete: It is used to delete records from the database tables. Also, it can be used with a WHERE
clause to delete a particular row from the table.
 Merge: It allows the insert and update(UPSERT) operations.

DCL(Data Control Language) - DCL works to deal with SQL commands that are used to permit a user
to access, modify and work on a database. it is used to access stored data. It gives access, revokes
access, and changes the permission to the owner of the database as per the requirement.

The commands used in DCL are:

 Grant: It is used to give access to security privileges to a specific database user.

 Revoke: It is used to revoke the access from the user that is being granted by the grant
command.

TCL(Transaction Control Language) - It can be grouped into a logical transaction and is used to run the
changes made by the DML command in the database.

The commands used in TCL are:

 Commit: Transaction on the database is saved using Commit.

 Rollback: The database gets restored to the original since the last commit.

INTERFACE- An interface is a program that allows users to input queries into a database without writing
the code in the query language. An interface can be used to manipulate the database for adding,
deleting, updating, or viewing the data.

Types of Interface are-

Form based Interface -A form is displayed to each user by the form based interface. The user fills in the
details and submits the form to make a new entry into the database. It can also be done when the user
only fills in some details and the system will help by retrieving the rest of the details from the database.
The form based interface is built for the naive user(inexperienced user) which deals with a limited
number of operations. Many DBMS have specification language which helps the programmer define
such forms.

Example -Student entering his roll. no, branch in the form to get the grade card.

Menu based User Interface- In this interface, the user was provided with a list of options (called a
menu) through which the user forms a request. The user doesn't need to memorize the command and
syntax and the query is composed step by step by picking options from a menu. Pull down menu
interfaces are mostly used in web based user interfaces and are often used in browsing interfaces by
which the database content can be looked through.

Example - In a shopping website, categories are selected from the menu, brands are selected from the
menu of brands, and budget ranges are applied from the menu of budget range.

GUI(Graphical User Interface) - Users are provided a schema of diagrammatic form by which query
can be specified through manipulating the diagram. GUI utilizes both menu and form in several cases.
Schema Diagram's specific parts are selected using devices used by GUI.

Example - You liked a video on Instagram by tapping with your finger, and the color changes to red. The
visual graphic gets changed due to user action.

Natural Language Interface - A natural language interface contains its unique schema more like the
high level conceptual schema. It also has a directory of important words. It generates a query based on
the interpretation of important words in the input by the user and if the interpretation is successful,
then it displays the result to the user.

Example -A user googled the fastest car in India, and now the natural language interface will look for
the important words i. e. fastest, car, India, and show the result accordingly.

Speech Input and Output - The users query the interface with speech and get the answer in speech.
The input is detected using predefined words and conversions are done into speech to provide the
output. Nowadays, it has become the most common type of interface.

Example - OK Google, Siri on Apple, and Alexa is used in the form of speech.

Interface for DBA - DBA sta are provided commands that can only be used by them only to create an
account, grant account authorization, and change a schema, and storage structure reorganization.

COMPONENTS OF A DATABASE SYSTEM

Any DBMS based applications is made up of six key components that work together to handle data
e ectively.

1. Hardware

 Physical devices like servers, disks, input-output devices (keyboard, monitor, printer).

 Stores and processes data; interfaces between real-world inputs and digital systems.

 Examples: Personal computer hard disk, RAM, network devices used for DBMS operations.
2. Software

 Actual DBMS software like MySQL, Oracle, PostgreSQL.

 Includes the database engine, OS, network software, and application tools.

 Translates database access languages into operations.

3. Data

 Raw facts stored in structured or unstructured formats.

 Operational Data: Actual user data (e.g., name, age).

 Metadata: Data about data (e.g., storage time, size, data type).

 Core reason DBMS exists—to manage and store data e iciently.

4. Procedures

 Instructions and rules for using DBMS e ectively.

 Covers setup, login/logout, data validation, backup, access control, and report generation.

 Helps ensure consistent and secure use of the system.

5. Database Access Language

 Used to interact with the database (create, read, update, delete data).

 Examples: SQL, MyAccess, Oracle PL/SQL.

 DDL (Data Definition Language) – CREATE, ALTER, DROP

 DML (Data Manipulation Language) – INSERT, UPDATE, DELETE

6. People

 Users interacting with DBMS at di erent levels:

 Database Administrators (DBA) – Manage security, performance, user access.

 Developers – Build applications using the database.

 End Users – Use applications to access the database (e.g., students, employees).

DATA MODELS -

Data Model is the model that organizes elements of the data and tell how they relate to one-another
and with the properties of real-world entities. The basic purpose of the data model is to make sure that
the data stored in the data model is understood fully. Further, it has three types-

1. Physical Data Model,

2. Record-Based Data Model,

3. Object-Oriented Data Model

Physical Data Model is not used much nowadays. In this, we will study about the Record-Based Data
Model in detail.
Record-Based Data Model : When the database is organized in some fixed format of records of several
than the model is known as Record-Based Data Model. It has a fixed number of fields or attributes in
each record type and each field is usually of a fixed length. Further, it is classified into three types-

1. Hierarchical Data Model : In hierarchical type, the model data are represented by collection of
records. In this, relationships among the data are represented by links. In this model, tree data
structure is used. It was developed in 1960s by IBM, to manage large amount of data for
complex manufacturing projects. The basic logic structure of hierarchical data model is upside-
down "tree".

Advantages - Simplicity, Data Integrity, Data security, E iciency, Easy availability of expertise.

Disadvantages - Complexity, Inflexibility, Lack of Data Independence, Lack of querying

facility, Data Manipulation Language, Lack Of standards.

2. Network Data Model : In network type, the model data are represented by collection of records.
In this, relationships among the data are represented by links. Graph data structures are used in
this model. It permits a record to have more than one parent. For Example- Social Media sites
like Facebook, Instagram etc.

Advantages - Simplicity, Data Integrity, Data Independence, Database standards.

Disadvantages - System Complexity, Lack of structural Independence.

3. Relational Data Model : Relational Data Model uses tables to represent the data and the
relationship among these data. Each table has multiple columns and each column is identified
by a unique name. It is a low-level model.

Advantages - Structural Independence, Simplicity, Ease of designing, Implementation, Ad-Hoc

query capability.

Disadvantages - Hardware Overheads, Ease of design can result in bad design.