Unit-2 Introduction to Data

Management and Data Models

● Data VS Information
○ What is Data?
○ What is Information?
○ Difference between Data and Information
● Database
● Database Management System:
○ Introduction of the database and the DBMS
○ Tasks of DBMS
○ Components of DBMS
○ Characteristics of DBMS
○ Advantages and Disadvantages of DBMS
● Types of Database
○ Centralized Database
○ Distributed Database
 ○ Relational Database
○ NoSQL Database
○ Cloud Database
○ Object-oriented Database
○ Hierarchical Database
○ Network Database
○ Personal Database
○ Operational Database
○ Enterprise Database
● Database Architecture/Environment
○ Major parts of DBMS Architecture
○ Type of DBMS Architecture
■ 1-tier Architecture
■ 2-tier Architecture
■ 3-tier Architecture
○ Types of DBMS Languages
● Functions of DBMS
● Data Model Basic Building Block
○ Data Entities
○ Data types
○ Keys
○ Indexes
○ Constraints
● Relational Model in DBMS
○ The E-R Model
○ The Object-Oriented Model
○ Newer Data Models :- Object/Relational and XML.
● The Relational Database Model
○ Tables and its characteristics
○ Operations on Table
● Concepts of functional dependency
● Integrity Constraints
● SQL Relational Set Operation
○ Union
○ Union All
○ Intersect
○ Minus
● The Data Dictionary and The System Catalog
● Relationship within the Relational Database
○ The 1:M Relationship, The 1:1 Relationship
○ The M:N Relationship,
○ The concept of data redundancy
Data V/S Information
 Data V/S Information
1) What is Data ?
▪ The term Data is defined as a raw and unstructured fact that needs to be processed
to make it meaningful,Data can be simple and unstructured at the same time until it
is structured.
▪ Usually data contains facts, numbers, symbols, image, observation, perceptions,
characters,etc.
▪ To derive meaning,data is always interpreted by a machine or [Link],it is
meaningful.
▪ Data comprises of statements,characters and numbers in a raw form.
▪ Examples of Data:the number of visitors to a website by country,for the past 100
years,the history of temperature readings around the globe is the data.
2) What is Information?

▪ The term Information is defined as a set of data that are processed according to the
given requirements in a meaningful [Link] make the information useful and
meaningful,it must be processed,presented and structured in a given context.
▪ Information is processed from data and process context,purpose and [Link]
also includes raw data manipulations.
▪ Information gives meaning and increases the accuracy of the [Link] helps to
guarantee undesirability and reduces confusion.
▪ Thus,when the data is transformed into information,there is no unwanted and unless
detail contained in the information.
▪ Example of Information : Merit List,Receipts,Reports,Report Cards,etc.
Difference between Data and Information

Parameters Data Information

meaning Data is based on the observations Information is more reliable as

and records,and is stored in compared to the [Link] enables
computer and remembered by an the researcher to conduct a
individual. proper study.

Format Data comes in the form of Information is in the form of ideas

letters,numbers,or a character and inferences.
set.

Represented We can represent the data in the We can represent the information
form of a graph,data tree,tabular in the form of languages,thoughts
data,etc. on the basis of the given data.

Meaningful Data has no particular [Link] Information carries the meaning

data is no meaningful. assigned by the data
interpretation.
Parameters Data Information

Interrelation Facts which is gathered. Data which is processed.

Knowledge level Data is low level knowledge. Information is the second level of
knowledge.

Contains Data contains unprocessed raw Information contains set of data

factors. that is processed in meaningful
way

Dependency The data depends on the sources Information depends on data.

to be collected.

Significance Data alone does not have any Information by itself is

significance. significance.

Usefulness The data which is gathered by the Information is used and valuable
researcher can or cannot be because it is easily accessible for
useful. the researcher to use.

Description Quantitative or Qualitative Information is a set of data that

variables that are used to carries news and meaning.
generate ideas or conclusions.
Database
 What is Database ?
▪ The database is a collection of interrelated data which is used to
retrieve,insert and delete the data [Link] is also used to organize
the data in the form of a table,schema,and reports,etc.

▪ For example : The college Database organizes the data about the
admin,staff,students and faculty etc.

▪ Using the database,you can easily retrieve,insert and delete the

information.
Database Management
System
Database Management System[DBMS]
• Database management system is a software which is used to manage
database. For example: MySQL, Oracle, etc are a very popular
commercial database which is used in different applications.
• 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.
• It provides protection and security to the database. In the case of
multiple users, it also maintains data consistency.
DBMS allows users the following tasks:
The following are common functions that a DBMS performs:
• Administration tasks:
A DBMS supports many typical database administration tasks, including change
management, performance monitoring and tuning, security, and backup and recovery.
Most database management systems are also responsible for automated rollbacks and
restarts as well as logging and auditing of activity in databases and the applications that
access them.
• Storage:
A DBMS provides efficient data storage and retrieval by ensuring that data is stored in
tables, rows and columns.
• Concurrency control:
In environments where multiple users access and modify the database
simultaneously, a DBMS guarantees controlled transaction execution to prevent data
corruption or inconsistency
• Centralized view:
A DBMS provides a centralized view of data that multiple users can access from
multiple locations in a controlled manner. A DBMS can limit what data end users see
and how they view the data, providing many views of a single database schema. End
users and software programs are free from having to understand where the data is
physically located or on what type of storage medium it resides because the DBMS
handles all requests.
• Data manipulation:
A DBMS ensures data integrity and consistency by letting users insert, update, delete
and modify data inside a database.
• Data independence:
A DBMS offers both logical and physical data independence to protect users and
applications from having to know where data is stored or from being concerned about
changes to the physical structure of data. As long as programs use the application
programming interface (API) for the database that the DBMS provides, developers
won’t have to modify programs just because changes have been made to the database.
• Backup and recovery:
A DBMS facilitates backup and recovery options.
by creating backup copies so that data can be restored to a consistent state. This
protects against data loss due to hardware failures, software errors and other
unforeseen events. In a relational database management system (RDBMS) – the
most widely used type of DBMS – the API is structured query language (SQL), a
standard programming language for defining, protecting and accessing data.
Components of a DBMS:

A DBMS is a sophisticated piece of system software consisting of multiple integrated

components that deliver a consistent, managed environment for creating, accessing and
modifying data in databases. These components include the following:

• Storage engine:

This basic element of a DBMS is used to store data. The DBMS must interface with a file
system at the operating system (OS) level to store data. It can use additional components
to store data or interface with the actual data at the file system level.

• Metadata catalogue:

Sometimes called a system catalogue or database dictionary, a metadata catalogue

functions as a repository for all database objects that have been created. When databases
and other objects are created, the DBMS automatically registers information about them in
the metadata catalogue.
The DBMS uses this catalogue to verify user requests for data, and users can query the
catalogue for information about the database structures that exist in the DBMS. The
metadata catalogue can include information about database objects, schemas, programs,
security, performance, communication and other environmental details about the
databases it manages.

• Database access language:

The DBMS must also provide an API to access the data, typically in the form of a database
access language that can be used to modify data but also create database objects and
secure or authorize access to the data. SQL is an example of a database access language
and encompasses several sets of commands, including control language for authorizing
data access, data definition language.
defining database structures and data manipulation language for reading and
modifying data.
• Optimization engine:
A DBMS can also provide an optimization engine that’s used to parse database access
language requests and turn them into actionable commands for accessing and
modifying data.
• Query processor:
After a query is optimized, the DBMS must provide a way to run the query and return
results.
• Lock manager:
This crucial component of the DBMS manages concurrent access to the same data.
Locks are required to ensure multiple users aren’t trying to modify the same data
simultaneously.
• Log manager:
The DBMS records all changes made to data managed by the DBMS. The record of
changes is known as the log, and the log manager component of the DBMS is used
to ensure that log records are made efficiently and accurately. The DBMS uses the
log manager during shutdown and startup to ensure data integrity, and it interfaces
with database utilities to create backups and run recoveries.
• Data utilities:
A DBMS also provides a set of utilities for managing and controlling database
activities. Examples of database utilities include reorganization, RUNSTATS, backup
and copy, recover, integrity check, load data, unload data and repair database.
• Reporting and monitoring tools:
Most DBMSes are integrated with reporting and monitoring tools to offer enhanced
functionality for managing and analysing data. Reporting tools generate reports,
whereas monitoring tools track various database metrics, such as resource
consumption and user activity.
Characteristics of DBMS
1. Data Abstraction:
Provides different levels of data abstraction (physical, logical, view level).
2. Data Independence:
Changes in the schema at one level do not affect the schema at the next higher level.
3. Data Security and Integrity:
Controls unauthorized access and ensures data correctness and consistency.
4. Efficient Data Access:
Uses advanced techniques for efficient storage and retrieval of data.
5. Transaction Support:
Manages transactions to ensure ACID (Atomicity, Consistency, Isolation,
Durability) properties.
6. Multi-user Access:
Supports concurrent access by multiple users.
7. Backup and Recovery:
Provides mechanisms for automatic backup and recovery from failures.
Advantages of DBMS
• Controls database redundancy:
It can control data redundancy because it stores all the data in one single database
file and that recorded data is placed in the database.
• Data sharing:
In DBMS, the authorized users of an organization can share the data among multiple
users.
• Easily Maintenance:
It can be easily maintainable due to the centralized nature of the database system.
• Reduce time:
It reduces development time and maintenance need.
• Backup:
It provides backup and recovery subsystems which create automatic backup of data
from hardware and software failures and restores the data if required.
• Multiple user interface:
It provides different types of user interfaces like graphical user interfaces and
application program interfaces.
Disadvantages of DBMS

• Cost of Hardware and Software:

It requires a high speed of data processor and large memory size to run DBMS
software.

• Size:
It occupies a large space of disks and large memory to run them efficiently.

Complexity: Database system creates additional complexity and requirements.

• Higher impact of failure: Failure is highly impacted the database because in most
of the organization, all the data stored in a single database and if the database is
damaged due to electric failure or database corruption then the data may be lost
forever.
The Importance of Databases

▪ Databases are essential tools in the digital world. They are organized collections of
data that facilitate the storage, retrieval, management, and manipulation of
information.
▪ At their core, databases are designed to hold data in a structured format, allowing
users and applications to efficiently access and update the information as needed.
▪ The importance of databases extends across nearly all fields but is particularly critical
in data science. Data science projects often involve analyzing large volumes of data
to derive insights, make predictions, or inform decision-making.
▪ Without databases, managing this data—especially as it grows in size and
complexity—would be cumbersome and error-prone. Databases provide a systematic
way to store data and ensure its integrity, security, and accessibility.
▪ Consider, for example, a retail company that tracks sales, customer interactions,
inventory, and supplier information. A database serves as the backbone of the
company’s operations, enabling them to analyze trends, forecast demand, optimize
inventory levels, and enhance customer experiences.
▪ Without a database, the company would struggle to handle the vast amounts of
data generated daily, let alone use this data to make informed business decisions.
▪ Different types of databases exist because different applications have different
data requirements and complexities. Databases are developed to improve
performance, functionality, and handle specific use-cases.
▪ The need for various database types arises due to differences in data
structures, access patterns, scalability needs, and consistency
requirements.
▪ Traditional business applications usually use structured data organized in tables with
predefined schemas. For this reason, Relational Databases (RDBMS) are commonly
used.
▪ With the growth of big data, social networks, and real-time analytics, relational
databases showed limitations in handling unstructured data, horizontal scaling, and
highly connected data.
▪ To solve these problems, NoSQL databases were introduced. They provide flexibility,
scalability, and better performance for data that does not fit the rigid structure of
relational databases.
▪ The rise of IoT and time-sensitive applications created the need for Time-Series
Databases, which are optimized to store and manage time-based data efficiently.
▪ Cloud Databases have become popular because they store data on remote servers,
providing better scalability, accessibility, and availability.
▪ In addition, Vector Databases are used in machine learning applications to efficiently
store and query high-dimensional vector data.
Types of Database
Types of Database

There are different types of databases designed to store and manage different kinds of
data. Some common types include Centralized Database, Distributed Database,
NoSQL Database, Cloud Database, Relational Database, Network Database,
Graph Database, and Hierarchical Database.

(1) Centralized Database

A Centralized Database stores all data in a single central location or server. Users
from different locations can access the data through applications with proper
authentication.
Example: A central library database used by all libraries in a university.
 Imagine a college management system with campuses in different
[Link] database server is in delhi.
Location Role

Delhi Main Server(Stores all data)

Ahmedabad Client Computer

Mumbai Client Computer

All the data stored in delhi only.

Data Stored Location

Student Data Delhi Server

Faculty Data Delhi Server

Exam Data Delhi Server

 ● Now User in Mumbai wants faculty data.

● A user in Mumbai sends a query.

SELECT * FROM FACULTY;

● The request goes through the network to the delhi server.

● The Delhi server processes the query.
● The result is sent back to the Mumbai computer.

● Here only one database server exists

● All users connect to the same server.
Advantages of Centralized Database

● Reduced risk in data management – Changes or manipulation will not easily affect the
main data.

● Data consistency – Since data is stored in one central place, it remains consistent.

● Better data quality – Organizations can maintain proper data standards.

● Low cost – Managing data is cheaper because fewer vendors and systems are required.

Disadvantages of Centralized Database

● The database size becomes very large, which increases the response time for retrieving
data.

● It is difficult to update and maintain such a large database system.

● If the central server fails, the entire data may be lost, which can cause huge loss.
(2) Distributed Database
A Distributed Database is a database system where data is stored at multiple locations
or servers instead of a single central location. These databases are connected through
communication links, allowing users to access data easily from different places.
Examples: Apache Cassandra, HBase, Ignite.

Types of Distributed Database

1. Homogeneous Distributed Database (Homogeneous DDB)
● All database systems run on the same operating system.
● They use the same application software and hardware.
● The database structure is similar across all locations.

2. Heterogeneous Distributed Database (Heterogeneous DDB)

● Database systems run on different operating systems.
● They use different applications and hardware devices.
● Database structures may be different at different locations.
Imagine a college management system with campuses in different cities.

Server Location Data Stored

Ahmedabad Server Student Records
Delhi Server Faculty Records
Mumbai Server Exam Records

Each server:
● Stores part of the database
● Processes local queries
● Communicates with other servers
Now suppose you are in Mumbai and want to access Faculty Data.
What Happens Step-by-Step
1. User Sends Query
SELECT * FROM FACULTY;
The request goes to the local server in Mumbai.
2. Query Processor Checks Location
The Mumbai server checks where the faculty table is stored.
It finds that Faculty data is stored on the Delhi server.
3. Request Sent to Remote Server
The Mumbai server sends the request through the network to the Delhi server.
4. Delhi Server Processes Query
The Delhi server executes the query and retrieves the faculty records.
5. Data Returned
The Delhi server sends the result back to the Mumbai server.
6. Result Displayed
The Mumbai server shows the faculty data to the user.
Advantages of Distributed Database
● Modular development is possible because the system can be expanded by adding new computers to
the distributed system.

● Failure of one server does not affect the entire database, so data availability is higher.
(3) Relational Database
A Relational Database is based on the relational data model, where data is stored in the form of tables.
Each table consists of rows (tuples) and columns (attributes).
Relational databases use SQL (Structured Query Language) to store, manipulate, and manage data.
The relational database model was invented by E. F. Codd in 1970.
Each table contains a key that uniquely identifies the data.
Examples
● MySQL
● Oracle
● Microsoft SQL Server
● PostgreSQL
● MariaDB
Properties of Relational Database (ACID Properties)

A means Atomicity

Atomicity ensures that a transaction is completed fully or not executed at all.

It follows the “all or nothing” rule. If any part fails, the transaction is aborted.

C means Consistency

Consistency ensures that data remains correct before and after a transaction.
For example, in a bank account transaction, the total balance should remain correct after the
operation.

I means Isolation: There can be concurrent users for accessing data at the same time from the
database. Thus, isolation between the data should be isolated. For example, when multiple transactions
occur at the same time, the transaction affects should not be visible to the other transactions in the
database.

D means Durability: It ensures that once it completes the operation and commits the data, data
changes should remain permanent.
 ACID Properties

Property Meaning Example

Atomicity All or nothing Both debit & credit happen

Consistency Valid state Balance remains correct

Isolation No interference Multiple users safe

Durability Permanent Data saved after commit

(4) NoSQL Database

NoSQL/Not Only SQL is a type of database that is used for storing a wide range of
data sets. It is not a relational database as it stores data not only in tabular form but in
several different ways. It came into existence when demand for building modern
applications increased. Thus, NoSQL provides a wide variety of database
technologies in response to the demands.

Examples:
• Apache Cassandra
• MongoDB
• Azure Cosmos DB
• Amazon DynamoDB
• CouchDB

We further divide a NoSQL database into the following four types:

Key-value storage: It is the simplest type of database storage where it stores every
single item as a key (or attribute name) holding its value, together.
Document-oriented Database: A type of database used to store data in JSON-like
document. It helps developers in storing data by using the same document-model
format as used in the application code.

Graph Databases: It is used for storing vast amounts of data in a graph structure. Most
commonly, social networking websites use the graph database.

Wide-column stores: It is similar to the data represented in relational databases. Here,

data is stored in large columns together, instead of storing in rows.

Advantages of NoSQL Database

• It enables good productivity in the application development as it is not required to store

data in a structured format.
• It is a better option for managing and handling large data sets.
• It provides high scalability.
• Users can quickly access data from the database through key-value.
(5) Cloud Database

A type of database where data is stored in a virtual environment and executes over the cloud computing
platform.

It provides users with various cloud computing services (SaaS, PaaS, IaaS, etc.) for accessing the
database. There are numerous cloud platforms, but the best options are:

▪ Amazon Web Services (AWS)

▪ Microsoft Azure
▪ Kamatera
▪ PhoenixNAP
▪ ScienceSoft
▪ Google Cloud SQL, etc.
(6) Object-oriented Databases

The type of database that uses the object-based data model approach for storing data
in the database system.

The data is represented and stored as objects which are similar to the objects used in
the object-oriented programming language.

Examples:

● ObjectDB
● GemStone/S
● Versant
● Objectivity/DB

(7) Hierarchical Databases

It is the type of database that stores data in the form of parent-child relationship nodes.
Here, it organizes data in a tree-like structure.
Examples:
• IBM IMS
• RDM Mobile
• Windows Registry

(8) Network Databases

It is the database that typically follows the network data model. Here, the representation
of data is in the form of nodes connected via links between them. Unlike the hierarchical
database, it allows each record to have multiple children and parent nodes to form a
generalized graph structure.

Examples:
• Oracle CODASYL
• IDMS
(9) Personal Database

Collecting and storing data on the user's system defines a Personal Database.
This database is basically designed for a single user.

Advantage of Personal Database

• It is simple and easy to handle.

• It occupies less storage space as it is small in size.

(10) Operational Database

The type of database which creates and updates the database in real-time. It is
basically designed for executing and handling the daily data operations in several
businesses. For example, an organization uses operational databases for
managing per day transactions.
(11) Enterprise Database

Large organizations or enterprises use this database for managing a

huge amount of data. It helps organizations to increase and improve
efficiency. Such a database allows simultaneous access to users.

Advantages of Enterprise Database:

• Multi processes are supportable over the Enterprise database

DBMS Architecture
DBMS Architecture / Environment

DBMS Architecture 5 major parts:

1. Hardware:
The physical infrastructure, including servers, storage devices (disks, SSDs), memory (RAM), and input/output
devices (computers, networks) that support the database system.

2. Software:
The actual DBMS program (like MySQL, Oracle) that manages the database, plus operating systems, network
software, and application tools.

3. Data:
The raw facts stored, including operational data (customer names, balances) and metadata (data definitions,
schemas).

4. Procedures:
The rules, policies, and instructions for designing, using, and maintaining the database (e.g., backup, security,
login/logout, data validation).

5. Users (People):
The individuals interacting with the system, such as Database Administrators (DBAs), application developers, and
Types of DBMS Architecture

DBMS Architecture includes:

• 1-tier Architecture
• 2-tier Architecture
• 3-tier Architecture

1-Tier Architecture (Database Architecture)

Definition:
In 1-Tier Architecture, the user interface, application logic, and database all are on the same system
(computer). The user directly interacts with the database without any server or network.

Key Points

● The database is directly available to the user.

● The user works directly on the DBMS.
● All changes happen directly in the database.
● There is no middle layer or server.
● Everything runs on a single machine.
Example

A common example is Microsoft Excel.

In Excel:

● User interface
● Data storage
● Calculations / logic

All happen on the same computer.

Where 1-Tier Architecture is Used

● Local application development

● Personal applications
● Small projects
● Standalone systems without network
Advantages of 1-Tier Architecture

1. Simple Architecture

● Very easy to set up

● Only one computer is required.

2. Cost-Effective

● No need for extra hardware or servers.

3. Easy to Implement

● Quick to develop and deploy.

● Commonly used for small projects.
Disadvantages of 1-Tier Architecture
1. Limited to Single User
● Only one user can use the system at a time.
● Not suitable for multiple users or teamwork.

2. Poor Security
● All data and application are on one computer.
● If someone gets access to that system, they can easily access the data.

3. No Centralized Control
● Data is stored locally on one device.
● It becomes difficult to manage or backup data across different systems.

4. Hard to Share Data

● Since everything is on one computer, sharing data with other users is difficult.
2-Tier Architecture

Definition:
2-Tier Architecture is a client-server architecture where the client application
communicates directly with the database server.

How it Works

● The client side contains the user interface and application program.
● The server side contains the database and DBMS.
● The client connects to the server using APIs like ODBC or JDBC.

Layers in 2-Tier Architecture

1. Client Layer (Tier 1)

● Contains the user interface and application program.

● Users interact with the system here.
Example:

● Searching books
● Issuing books
● Checking due dates in a Library Management System.

2. Database Server Layer (Tier 2)

● Contains the database server.

● Responsible for:

○ Query processing
○ Transaction management
○ Data storage
2-Tier Architecture is a system where the client (application) directly
communicates with the database server.

It has two layers (tiers):

1. Client Layer
2. Database Server Layer
Explanation of the Diagram

Your diagram shows this flow:

User → Application → Database System

Client Side

● User – The person using the system.

● Application – The software interface (like a library app, banking
software, etc.).

The user interacts with the application.

Server Side

● Database System – Stores all the data.

● Runs on the server.
How it Works (Step-by-Step)
1. User interacts with the Application
Example: Searching for a book in a library system.

2. Application sends request to Database Server

3. Database processes the request

4. Result is sent back to the Application

5. Application shows result to the User

So the communication is:

User → Application → Database → Application → User
Example of 2-Tier Architecture

Example: Library Management System

● User searches book in application.

● Application sends query to database.
● Database returns book information.

Advantages (Shown in the Page)

1. Easy to Access

Direct connection between client and database makes data retrieval faster.

2. Scalable

We can increase performance by:

● Adding more clients

● Upgrading server hardware.
3. Low Cost

Cheaper than 3-Tier or Multi-Tier Architecture because fewer layers are involved.

Simple Real-Life Example

Think of it like this:

Customer (User)
Application (Client)
Database (Server)

Only two main layers, so it is called 2-Tier Architecture.

Disadvantages of 2-Tier Architecture
1) Limited Scalability
When the number of users increases, the database server receives too many
requests and the system becomes slow.
2) Security Issues
In 2-tier architecture, clients connect directly to the database, which can make the
system more vulnerable to attacks or data leaks.
3) Tight Coupling
The client and server are closely connected.
If the database structure changes, the client application also needs
modification.
4) Difficult Maintenance
When users increase, it becomes harder to manage updates, fix bugs, or add
new features.
3-Tier Architecture
Definition
3-Tier Architecture adds one more layer between the client and the database
server.
So instead of two layers, there are three layers.
Layers in 3-Tier Architecture
1) Presentation Layer (Client)
● User interface
● Example: Web browser or application

2) Application Layer (Application Server)

● Processes the logic and requests

3) Data Layer (Database Server)

● Stores and manages data
How it Works
Flow of communication:
User → Client Application → Application Server → Database → Application Server →
Client → User
The client does not communicate directly with the database.
Example: E-Commerce Store
User
You visit an online store, search for a product, and add it to your cart.
Processing
The system:
● Checks product availability
● Calculates total price
● Applies discounts
Database
The system stores:
● Product details
● Shopping cart information
Advantages of 3-Tier Architecture

1) Enhanced Scalability

Performance improves because application servers can be

distributed, allowing the system to support more users.

2) Data Integrity

The middle layer controls data processing, reducing chances of data

corruption.

3) Better Security

Clients cannot directly access the database, which prevents

unauthorized access.
Disadvantages of 3-Tier Architecture
1) More Complex
3-Tier architecture is more complex than 2-Tier architecture because it has three
layers.
Communication between layers also becomes more complicated.
2) Difficult Interaction
Because of the middle layer (application server), interaction between the client and
database becomes more complicated.
3) Slower Response Time
The request must pass through the application server before reaching the database,
so sometimes the response may take longer than in a 2-tier system.
4) Higher Cost
It requires:
● More hardware
● More software
● Skilled technical staff
So it is more expensive to build and maintain.
Database Languages
A Database Management System (DBMS) uses different languages to define, manipulate,
control, and retrieve data.

There are five main types.

Types of DBMS Languages

DDL – Data Definition Language

Purpose: Defines and modifies the database structure.

▪ DDL (Data Definition Language) is used to define and manage database structure
(tables, schema, etc.).
▪ It deals with structure, not data.

Commands

● CREATE – create table/database TRUNCATE– delete data but column remains

● ALTER – modify table as it is
● DROP – delete table RENAME- rename table
1) CREATE command
▪ Used to create database objects like tables, databases, indexes.

▪ Syntax(Create Database)
CREATE DATABASE database_name;

▪ Example:-
CREATE DATABASE college;
It will create database.

▪ Syntax(Create Table)
CREATE TABLE table_name (column_name datatype,column_name datatype);
▪ Example:-
CREATE TABLE student (sid INT,sname VARCHAR(20),semail INT,sge INT);
Creates a new table.
sid sname semail sage
2) ALTER command
Used to modify existing table structure.
1) Add new Column
Syntax :- ALTER TABLE table_name ADD column_name data_type;
Example:- ALTER TABLE student ADD scontact_no INT;
This will add new column into student table.
sid sname semail sage scontact_no

2) Rename old Column

Syntax :- ALTER TABLE table_name CHANGE oldcolumn_name newcolumn_name
data_type;
Example:- ALTER TABLE student CHANGE sname name VARCHAR(20);
This query will rename ‘sname’ column to ‘name’.

sid name semail sage scontact_no

3) Drop column
Syntax :- ALTER TABLE table_name DROP COLUMN column_name;
Example:- ALTER TABLE student DROP COLUMN sage;
This query will drop/delete sage column from student table.

sid name semail scontact_no

4) Change Data type of column

Syntax :- ALTER TABLE table_name MODIFY column_name datatype;
Example:- ALTER TABLE student MODIFY semail VARCHAR(30);
This query will change data type of semail column.
3) DROP command
Used to delete entire table or database permanently.
▪ Drop any table
Syntax :- DROP TABLE table_name;
Example :- DROP TABLE student;
Table + data both deleted permanently ❌
▪ Drop any database
Syntax :- DROP DATABASE database_name;
Example :- DROP DATABASE college;
This will delete whole college database
4) TRUNCATE command
Used to remove all records from table quickly.
Syntax :- TRUNCATE TABLE table_name;
Example :- TRUNCATE TABLE student;
▪ Only data deleted, structure remains
▪ Faster than DELETE

5) RENAME command
Used to change table name.
Syntax :- RENAME TABLE old_table_name TO new_table_name;
Example :- RENAME TABLE student TO college_student;
DML – Data Manipulation Language

Purpose: Used to insert, update, and delete records.

Command

● INSERT
● UPDATE
● DELETE

1) INSERT command
We created student table above which is looke like this:

sid name semail scontact_no

Now to insert data in this four column insert query is used.

Method-1 Without column Name
Syntax :- INSERT INTO table_name VALUES(value1,value2,value3,value4);
Example 1 :- INSERT INTO student VALUES(1,’mahi’,’mahi@[Link]’,9878675434);

sid name semail scontact_no

1 mahi mahi@[Link] 9878675434

Example 2 :- INSERT INTO student VALUES(2,’sima’,’sima@[Link]’);

Note :- Whenever you Insert record using method-1 you have to write value for all column for example in student
table we have four columns like sid,name,semail,scontact_no so user have to insert four values.
Example 2 is not valid because user insert record for only 3 columns sid,name and scontact_no but in student
table there is 4 columns sid,name,semail,scontact_no.
Method-2 With column name
Syntax :- INSERT INTO table_name(column1,column2,column3,column4) VALUES(value1,value2,value3,value4);
Example :- INSERT INTO student(sid,name,semail,scontact_no) VALUES(2,’sima’,’sima@[Link]’,9878675645);
sid name semail scontact_no

1 mahi mahi@[Link] 9878675434

2 sima sima@[Link] 9878675645

▪ If you want to skip column name in this method then you can skip.
Example:- INSERT INTO student(sid,name,semail) VALUES(3,’jiya’,’jiya@[Link]’);
Here user skip scontact_no which is [Link] SQL automatically add NULL into scontact_no
column.

sid name semail scontact_no

1 mahi mahi@[Link] 9878675434

2 sima sima@[Link] 9878675645

3 jiya jiya@[Link] NULL

2) UPDATE command
Used to modify existing data.
Update Command without condition:-
Syntax :- UPDATE table_name SET column = value;
Example :- UPDATE student SET sname=’mahi’;
Whenever Update command is used without condition it will update all data means here in
above query if student table have three record then all sname becomes ‘mahi’.

sid name semail scontact_no

1 mahi mahi@[Link] 9878675434

2 mahi sima@[Link] 9878675645

3 mahi jiya@[Link] NULL

Update Command with condition

Syntax :- UPDATE table_name SET column = value WHERE condition;
Example :- UPDATE student SET sname=’jiya’ WHERE sid=3;
sid name semail scontact_no

1 mahi mahi@[Link] 9878675434

2 mahi sima@[Link] 9878675645

3 jiya jiya@[Link] NULL

●Here only name becomes jiya where sid=[Link] all name becomes jiya because we mention
condition.
3) DELETE

Used to remove specific records from a table.

Syntax :DELETE FROM table_name;

Example:DELETE FROM student;
This query delete all data from table.

Syntax : DELETE FROM table_name WHERE condition;

Example: DELETE FROM student WHERE sid=3;
This query will delete only one student data whose sid is 3.
DQL – Data Query Language

Purpose: Used to retrieve data from the database.

Command

● SELECT

1) Select All column All Row : - * means all columns

Syntax :- SELECT * FROM table_name;

Example :- Fetch All Record From Student Table.

SELECT * FROM student;

Output:-
sid name semail scontact_no

1 mahi mahi@[Link] 9878675434

2 sima sima@[Link] 9878675645

3 jiya jiya@[Link] NULL

2) Select All column Particular Row : -
Syntax :- SELECT * FROM table_name WHERE condition;
Example :- Show the Record of Jiya.
SELECT * FROM student WHERE sid=3;
Output :-
sid name semail scontact_no

3 jiya jiya@[Link] NULL

3) Select Particular Column but All row: -

Syntax :- SELECT column_name FROM table_name;
Example :- Show student name from student table.
SELECT name FROM student; name

Output :- mahi

sima

jiya
4) Select Particular Column and Particular Row
Syntax :
SELECT column_name FROM table_name WHERE condition;
Example :-Show student name whose id = 2;

Output :-
name

sima
DCL – Data Control Language

Purpose: Controls user access and permissions.

Examples

● GRANT-It is used to grant permission to user.

● REVOKE-It is used to take permission back from the user.

Example:
GRANT SELECT ON Student TO user1;
TCL – Transaction Control Language

Purpose: Manages database transactions.

Examples

● COMMIT - It is used to save changes into database.

● ROLLBACK - It is used to perform undo on database.

● SAVEPOINT - It is used to make savepoint for backup.

Example:
COMMIT;