BCE- BT205

Class Notes
Prepared By- Prof. Yogesh Verma

Unit -5
● Cloud Computing
○ Defination
○ Cloud Infrastructure
○ Cloud segments or service delivery models(Iaas, Paas, Saas)
○ Cloud Deployment models/types of cloud(Public, Private, Hybride,
Community)
○ Pros and Cons of Cloud Computing

● DBMS
○ Introduction
○ File Oriented Approach and Database Approach
○ Data Models
○ Architecture of Database System
○ Data Independence
○ Data Dictionary
 2

○ DBA
○ DDL & DML
○ Primary Key

Cloud Computing
Defination :-

Cloud computing is a technology that allows users to access and store data,
applications, and services over the internet instead of relying on local computers or
servers. It provides on-demand access to a shared pool of resources, such as servers,
storage, databases, networking, software, and more, which can be rapidly provisioned
and released with minimal management effort.

Types of Cloud Computing Services

1. Infrastructure as a Service (IaaS): Provides virtualized computing resources over

the internet. Examples: Amazon Web Services (AWS), Microsoft Azure, Google
Cloud Platform (GCP).
2. Platform as a Service (PaaS): Provides hardware and software tools over the
internet, typically needed for application development. Examples: Google App
Engine, Microsoft Azure App Service.
3. Software as a Service (SaaS): Delivers software applications over the internet, on
a subscription basis. Examples: Google Workspace, Microsoft Office 365,
 3

Salesforce.

Deployment Models

1. Public Cloud: Services are delivered over the public internet and shared across
multiple organizations.
2. Private Cloud: Services are maintained on a private network, dedicated to a
single organization.
3. Hybrid Cloud: A combination of public and private clouds, allowing data and
applications to be shared between them.
4. Community Cloud: Infrastructure shared by several organizations with common
concerns (e.g., security, compliance).

Benefits of Cloud Computing

● Cost Efficiency: Reduces the capital expense of buying hardware and software.
● Scalability: Easily scale resources up or down as needed.
● Accessibility: Access services and data from anywhere with an internet
connection.
 4

● Security: Advanced security features and compliance certifications.

● Disaster Recovery: Simplified disaster recovery and business continuity planning.

Cloud Infrastructure of Cloud computing

Cloud infrastructure refers to the collection of hardware, software, networks, storage,
and interfaces that combine to deliver computing as a service. This infrastructure
includes components required to support the computing requirements of cloud
computing, making it possible to offer scalable, on-demand resources and services over
the internet.

Key Components of Cloud Infrastructure

1. Compute Resources:
○ Virtual Machines (VMs): Simulated computer systems that run on
physical hardware and can be used to deploy applications and services.
○ Containers: Lightweight, portable units that bundle an application and its
dependencies, providing consistent environments across different
platforms.
○ Bare Metal Servers: Physical servers without virtualization, offering direct
access to hardware resources.
2. Storage:
○ Block Storage: Data storage in fixed-size blocks, often used for storing
data in applications such as databases.
○ Object Storage: Data storage as objects, suitable for unstructured data
like images, videos, and backups.
○ File Storage: Data storage in a hierarchical file system, commonly used for
traditional applications.
3. Networking:
○ Virtual Networks: Logical networks that allow resources to communicate
within the cloud environment.
 5

○ Load Balancers: Distribute incoming traffic across multiple servers to

ensure high availability and reliability.
○ VPN (Virtual Private Network): Secure connection between remote users
and the cloud environment.
4. Security:
○ Firewalls: Control incoming and outgoing network traffic based on
predetermined security rules.
○ Identity and Access Management (IAM): Manage user identities and their
access to resources.
○ Encryption: Protect data at rest and in transit by converting it into a secure
format.
5. Management and Monitoring:
○ Cloud Management Platforms: Tools for managing and orchestrating
cloud resources and services.
○ Monitoring Tools: Track performance, availability, and usage of cloud
resources.

Example: AWS Cloud Infrastructure

Amazon Web Services (AWS) is one of the leading cloud service providers, and its
infrastructure includes:

● Elastic Compute Cloud (EC2): Provides scalable virtual servers.

● Simple Storage Service (S3): Offers object storage for large-scale data.
● Elastic Block Store (EBS): Provides block storage volumes for use with EC2.
● Virtual Private Cloud (VPC): Enables the creation of isolated networks within the
AWS cloud.
● Identity and Access Management (IAM): Controls access to AWS resources.
● CloudWatch: Monitors AWS resources and applications.

Benefits of Cloud Infrastructure

● Scalability: Easily scale resources up or down based on demand.

● Cost Efficiency: Pay only for the resources you use, reducing capital expenditure.
● Reliability: High availability and redundancy to minimize downtime.
● Security: Advanced security features and compliance certifications.
 6

● Global Reach: Access resources from anywhere in the world through a global
network of data centers.

Cloud segments or service delivery models(Iaas, Paas,

Saas)
Cloud computing services are categorized into three main service delivery models, each
addressing different levels of abstraction and catering to various user needs. These
models are Infrastructure as a Service (IaaS), Platform as a Service (PaaS), and
Software as a Service (SaaS).

1. Infrastructure as a Service (IaaS)

IaaS provides virtualized computing resources over the internet. It offers fundamental
building blocks such as virtual machines, storage, and networks, enabling users to build
and manage their own computing environments.
 7

Key Features:

● Provides virtualized hardware resources (compute, storage, networking).

● Users have control over the operating systems, storage, and deployed
applications.
● Highly scalable and flexible.

Examples:

● Amazon Web Services (AWS) EC2: Offers resizable compute capacity in the
cloud.
● Microsoft Azure Virtual Machines: Provides scalable virtual machines on
demand.
● Google Compute Engine (GCE): Delivers virtual machines running in Google's
data centers.

2. Platform as a Service (PaaS)

PaaS provides a platform that allows developers to build, deploy, and manage
applications without worrying about the underlying infrastructure. It includes
development tools, databases, middleware, and more.

Key Features:

● Simplifies the development and deployment process.

● Includes development frameworks, databases, and middleware.
● Reduces the need for managing hardware and software infrastructure.

Examples:

● Google App Engine: A platform for building and hosting web applications in
Google-managed data centers.
● Microsoft Azure App Service: A platform for building and deploying web and
mobile apps.
● Heroku: A cloud platform that lets companies build, run, and operate applications
entirely in the cloud.

3. Software as a Service (SaaS)

 8

SaaS delivers software applications over the internet on a subscription basis. It provides
fully functional software to users, managed and maintained by the service provider.

Key Features:

● Accessible via a web browser or a thin client.

● Eliminates the need for users to install and run applications on their own
systems.
● Managed and updated by the service provider.

Examples:

● Google Workspace (formerly G Suite): A suite of productivity and collaboration

tools.
● Microsoft Office 365: A subscription service offering access to Office
applications and other productivity services.
● Salesforce: A customer relationship management (CRM) platform.

These service delivery models enable businesses to choose the level of control and
management they need, making cloud computing a versatile and powerful tool for a
wide range of applications.
 9

Cloud Deployment models/types of cloud(Public,

Private, Hybride, Community)
Cloud deployment models define the type of cloud environment a business operates in
and how they use cloud services. Here are the four primary cloud deployment models:

1. Public Cloud

Public Cloud services are delivered over the public internet and shared across multiple
organizations. This model is managed by third-party cloud service providers, and users
access these services on a pay-as-you-go basis.
 10

Key Characteristics:

● Cost-effective and economical for smaller organizations.

● High scalability and flexibility.
● No need to manage hardware and infrastructure.
● Offers various services like IaaS, PaaS, and SaaS.

Examples:

● Amazon Web Services (AWS)

● Microsoft Azure
● Google Cloud Platform (GCP)

2. Private Cloud

Private Cloud is a cloud environment dedicated to a single organization. It provides

greater control over resources and security compared to the public cloud. It can be
hosted on-premises or by a third-party provider.
 11

Key Characteristics:

● Enhanced security and privacy.

● Customizable to meet specific organizational needs.
● Higher costs compared to public cloud.
● Greater control over the infrastructure and data.

Examples:

● On-premises private cloud infrastructure.

 12

● VMware vSphere.
● Microsoft Azure Stack.

3. Hybrid Cloud

Hybrid Cloud is a combination of public and private clouds, allowing data and
applications to be shared between them. This model provides greater flexibility and
optimization of existing infrastructure, security, and compliance.

Key Characteristics:

● Flexibility to move workloads between private and public clouds.

● Optimizes existing infrastructure, security, and compliance.
● Cost-effective by using public cloud for non-sensitive operations.
● Enhanced scalability and resilience.

Examples:

● Microsoft Azure Hybrid Cloud

● Google Anthos
● AWS Outposts

4. Community Cloud
 13

Community Cloud is a cloud infrastructure shared by several organizations with

common concerns, such as security, compliance, or jurisdiction. It can be managed by
the organizations or a third-party provider.

Key Characteristics:

● Shared infrastructure for organizations with similar requirements.

● Cost-effective compared to private cloud.
● Collaborative management and governance.
● Meets specific regulatory and compliance requirements.

Examples:

● Government organizations sharing a common infrastructure.

● Healthcare organizations sharing a cloud to meet HIPAA compliance.
● Educational institutions sharing resources and research data.
 14

Pros and Cons of Cloud Computing

Pros of Cloud Computing

1. Cost Efficiency:
○ Reduces capital expenditure as there's no need to invest in physical
hardware.
 15

○ Pay-as-you-go model allows businesses to only pay for the resources they
use.
○ Reduces operational and maintenance costs.
2. Scalability:
○ Easily scale resources up or down based on demand.
○ Accommodates growth without the need for significant infrastructure
investment.
3. Accessibility and Flexibility:
○ Access data and applications from anywhere with an internet connection.
○ Supports remote work and collaboration.
4. Disaster Recovery:
○ Simplifies backup and recovery processes.
○ Ensures business continuity in case of hardware failure or natural
disasters.
5. Performance and Reliability:
○ Offers high performance and availability through advanced infrastructure
and global data centers.
○ Provides redundancy and load balancing to minimize downtime.
6. Automatic Updates:
○ Cloud service providers handle software and hardware updates.
○ Ensures access to the latest features and security enhancements.

Cons of Cloud Computing

1. Security and Privacy:

○ Potential vulnerabilities to data breaches and cyberattacks.
○ Concerns over data privacy and regulatory compliance.
2. Dependency on Internet:
○ Requires a stable internet connection to access services.
○ Potential downtime or service interruptions if internet connectivity is lost.
3. Limited Control:
○ Less control over the infrastructure and data as it is managed by
third-party providers.
○ Potential issues with data sovereignty and compliance with local
regulations.
 16

4. Cost Overruns:
○ Uncontrolled usage can lead to unexpectedly high costs.
○ Requires careful monitoring and management of resource usage.
5. Vendor Lock-In:
○ Difficulty in migrating services and data from one provider to another.
○ Dependency on a single provider's platform and services.
6. Performance Variability:
○ Shared resources can lead to performance fluctuations.
○ Network latency and bandwidth limitations can affect performance.

DBMS- DATABASE MANAGEMENT SYSTEM

A Database Management System (DBMS) is software designed to store, manage, and

facilitate access to databases. It serves as an intermediary between users or
applications and the database itself, ensuring that data is consistently organized and
 17

easily accessible.

Key Functions of a DBMS

● Data Storage, Retrieval, and Update: Handles the storage, querying, updating,
and deletion of data.
● User Management: Manages user permissions and ensures data security by
controlling access to the database.
● Data Integrity: Ensures data accuracy and consistency through constraints and
rules.
● Transaction Management: Manages transactions to ensure data integrity in
multi-user environments.
● Backup and Recovery: Provides mechanisms for data backup and recovery in
case of data loss or corruption.
 18

Types of DBMS

1. Relational DBMS (RDBMS): Stores data in tables (relations) and uses SQL for
database interaction. Examples: MySQL, PostgreSQL, Oracle Database, SQL
Server.
2. NoSQL DBMS: Designed for unstructured data and large-scale data storage.
Examples: MongoDB, Cassandra, Redis.
3. Object-oriented DBMS (OODBMS): Stores data in the form of objects, similar to
object-oriented programming. Examples: ObjectDB, db4o.
4. Hierarchical DBMS: Organizes data in a tree-like structure. Example: IBM
Information Management System (IMS).
5. Network DBMS: Uses a graph structure to represent relationships among data.
Example: Integrated Data Store (IDS).

File Oriented Approach and Database Approach

The File-Oriented Approach and Database Approach are two methods for storing,
managing, and retrieving data in computer systems. Here's a detailed comparison:

1. File-Oriented Approach

The File-Oriented Approach organizes data into files and stores them on storage
devices. Each application has its own set of files for processing data.

Features:

● Data Redundancy: Duplication of data across multiple files.

● Data Dependence: Changes in data format or structure affect the application.
● Limited Security: Files are usually accessible to anyone with access to the
system.
● Lack of Scalability: Managing a large number of files becomes complex.
● No Centralized Control: Each application manages its own files independently.

Advantages:
 19

● Simple and easy to implement.

● Suitable for small systems or applications with minimal data.

Disadvantages:

● Data inconsistency due to redundancy.

● Difficult to manage relationships between data.
● Limited data sharing capabilities.
● Inefficient for large-scale systems.

2. Database Approach

The Database Approach uses a Database Management System (DBMS) to store and
manage data in a centralized and structured manner.

Features:

● Data Independence: Data structure changes do not affect applications.

● Reduced Redundancy: Data is stored in a single location, minimizing duplication.
● Enhanced Security: Controlled access through user permissions.
● Centralized Control: Data is managed by the DBMS.
● Data Integrity: Ensures consistency and accuracy of data.
● Data Sharing: Multiple users and applications can access data simultaneously.

Advantages:

● Better data management and organization.

● Scalability for large-scale systems.
● Efficient data retrieval using queries.
● Easier to maintain and update.

Disadvantages:

● Higher initial setup cost.

● Requires specialized knowledge to manage and maintain.
● May have performance overhead for small systems.
 20

Difference Between File System and DBMS

Basics File System DBMS

The file system is a way of

arranging the files in a DBMS is software for managing the
Structure
storage medium within a database.
computer.

Redundant data can be

Data Redundancy In DBMS there is no redundant data.
present in a file system.

It doesn’t provide an
Inbuilt mechanism for It provides in house tools for backup and
Backup and Recovery
backup and recovery of recovery of data even if it is lost.
data if it is lost.

There is no efficient query

Efficient query processing is there in
Query processing processing in the file
DBMS.
system.
 21

There is less data

There is more data consistency because
Consistency consistency in the file
of the process of normalization .
system.

It is less complex as It has more complexity in handling as

Complexity
compared to DBMS. compared to the file system.

File systems provide less

DBMS has more security mechanisms
Security Constraints security in comparison to
as compared to file systems.
DBMS.

It is less expensive than It has a comparatively higher cost than

Cost
DBMS. a file system.

In DBMS data independence exists,

mainly of two types:

There is no data
Data Independence
independence. 1) Logical Data Independence .

2)Physical Data Independence.

 22

Only one user can access

User Access Multiple users can access data at a time.
data at a time.

The users are not required The user has to write procedures for
Meaning
to write procedures. managing databases

Data is distributed in
Due to centralized nature data sharing
Sharing many files. So, it is not
is easy
easy to share data.

It give details of storage

Data Abstraction It hides the internal details of Database
and representation of data

Integrity Constraints are Integrity constraints are easy to

Integrity Constraints
difficult to implement implement

To access data in a file ,

user requires attributes
Attribute s No such attributes are required.
such as file name, file
location.

Example Cobol , C++ Oracle , SQL Server

 23

Data Models
A data model in a Database Management System (DBMS) is a conceptual framework
for organizing and structuring data. It defines how data is stored, accessed, and
manipulated within a database. A data model provides a blueprint for designing and
implementing a database, outlining the relationships among data elements and the
rules that govern them.

Types of Relational Models

1. Conceptual Data Model
2. Representational Data Model
3. Physical Data Model

1. Conceptual Data Model

The conceptual data model describes the database at a very high level and is
useful to understand the needs or requirements of the database. It is this
model that is used in the requirement-gathering process i.e. before the
Database Designers start making a particular database. One such popular
model is the entity/relationship model (ER model). The E/R model specializes
in entities, relationships, and even attributes that are used by database
designers.
 24

Entity-Relationship Model( ER Model): It is a high-level data model which is

used to define the data and the relationships between them. It is basically a
conceptual design of any database which is easy to design the view of data.

Components of ER Model:

1. Entity: An entity is referred to as a real-world object. It can be a

name, place, object, class, etc. These are represented by a rectangle
in an ER Diagram.
2. Attributes: An attribute can be defined as the description of the
entity. These are represented by Ellipse in an ER Diagram. It can be
Age, Roll Number, or Marks for a Student.
3. Relationship: Relationships are used to define relations among
different entities. Diamonds and Rhombus are used to show
Relationships.

Characteristics of a conceptual data model

● Offers Organization-wide coverage of the business concepts.

● This type of Data Models are designed and developed for a business
audience.
● The conceptual model is developed independently of hardware
specifications like data storage capacity, location or software
specifications like DBMS vendor and technology. The focus is to
represent data as a user will see it in the “real world.”
 25

2. Representational Data Model

This type of data model is used to represent only the logical part of the
database and does not represent the physical structure of the database. The
representational data model allows us to focus primarily on the design part
of the database. A popular representational model is a Relational model. The
relational Model consists of Relational Algebra and Relational Calculus. In
the Relational Model, we basically use tables to represent our data and the
relationships between them. It is a theoretical concept whose practical
implementation is done in the Physical Data Model.

The advantage of using a Representational data model is to provide a

foundation to form the base for the Physical model

3. Physical Data Model

The physical Data Model is used to practically implement Relational Data

Model. Ultimately, all data in a database is stored physically on a secondary
storage device such as discs and tapes. This is stored in the form of files,
records, and certain other data structures. It has all the information on the
format in which the files are present and the structure of the databases, the
presence of external data structures, and their relation to each other. Here,
we basically save tables in memory so they can be accessed efficiently. In
order to come up with a good physical model, we have to work on the
relational model in a better way.

Characteristics of a physical data model:

 26

● The physical data model describes data need for a single project or
application though it maybe integrated with other physical data
models based on project scope.
● Data Model contains relationships between tables that which
addresses cardinality and nullability of the relationships.
● Developed for a specific version of a DBMS, location, data storage or
technology to be used in the project.
● Columns should have exact datatypes, lengths assigned and default
values.
● Primary and Foreign keys, views, indexes, access profiles, and
authorizations, etc. are defined

Some Other Data Models

1. Hierarchical Model

The hierarchical Model is one of the oldest models in the data model which
was developed by IBM, in the 1950s. In a hierarchical model, data are viewed
as a collection of tables, or we can say segments that form a hierarchical
relation. In this, the data is organized into a tree-like structure where each
record consists of one parent record and many children. Even if the segments
are connected as a chain-like structure by logical associations, then the
instant structure can be a fan structure with multiple branches. We call the
illogical associations as directional associations.

2. Network Model

The Network Model was formalized by the Database Task group in the
1960s. This model is the generalization of the hierarchical model. This model
 27

can consist of multiple parent segments and these segments are grouped as
levels but there exists a logical association between the segments belonging
to any level. Mostly, there exists a many-to-many logical association
between any of the two segments.

3. Object-Oriented Data Model

In the Object-Oriented Data Model, data and their relationships are contained
in a single structure which is referred to as an object in this data model. In
this, real-world problems are represented as objects with different attributes.
All objects have multiple relationships between them. Basically, it is a
combination of Object Oriented programming and a Relational Database
Model.

Architecture of Database System

The architecture of a database system refers to the design and structure of the system,
defining how data is stored, managed, and accessed. There are various architectures
used in database systems, but the most common ones are single-tier, two-tier, and
three-tier architectures.

Types of DBMS Architecture

 28

Database architecture can be seen as a single tier or multi-tier. But logically,

database architecture is of two types like: 2-tier architecture and 3-tier
architecture.

1-Tier Architecture
○ In this architecture, the database is directly available to the user. It
means the user can directly sit on the DBMS and uses it.
○ Any changes done here will directly be done on the database itself. It
doesn't provide a handy tool for end users.
○ The 1-Tier architecture is used for development of the local application,
where programmers can directly communicate with the database for
the quick response.

2-Tier Architecture

○ The 2-Tier architecture is the same as basic client-server. In the two-tier

architecture, applications on the client end can directly communicate
 29

with the database at the server side. For this interaction, API's like:
ODBC, JDBC are used.
○ The user interfaces and application programs are run on the client-side.
○ The server side is responsible to provide the functionalities like: query
processing and transaction management.
○ To communicate with the DBMS, client-side applications establishes a
connection with the server side.

3-Tier Architecture
○ The 3-Tier architecture contains another layer between the client and
server. In this architecture, client can't directly communicate with the
server.
○ The application on the client-end interacts with an application server
which further communicates with the database system.
○ End user has no idea about the existence of the database beyond the
application server. The database also has no idea about any other user
beyond the application.
○ The 3-Tier architecture is used in case of large web application.
 30

Data Independence
○ Data independence can be explained using the three-schema
architecture.
○ Data independence refers characteristic of being able to modify the
schema at one level of the database system without altering the
schema at the next higher level.

1. Logical Data Independence

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

○ Logical data independence occurs at the user interface level.

2. Physical Data Independence

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

Data Dictionary
A Data Dictionary, also known as a system catalog, is a crucial component of a
Database Management System (DBMS). It contains metadata, which is data about data,
and provides information about the structure, organization, and characteristics of the
data stored in the database. The data dictionary acts as a centralized repository of
information that helps in managing and utilizing the database effectively.

Key Components of a Data Dictionary

1. Tables: Information about the database tables, including table names, column
names, data types, and constraints.
2. Indexes: Details about the indexes created on tables, including index names,
columns involved, and index types.
3. Views: Information about the views created in the database, including view
definitions and underlying tables.
4. Constraints: Data about constraints applied to tables, such as primary keys,
foreign keys, unique constraints, and check constraints.
5. Users and Roles: Details about database users, their roles, and their access
permissions.
6. Stored Procedures and Functions: Information about stored procedures and
functions, including their definitions and parameters.
7. Triggers: Details about triggers created in the database, including trigger events
and associated actions.

Benefits of a Data Dictionary

● Improved Data Management: Provides a centralized repository of metadata,

making it easier to manage and organize the database.
● Data Consistency: Ensures consistency and standardization of data across the
database.
● Enhanced Security: Helps in managing access permissions and security policies.
● Better Documentation: Acts as documentation for the database, aiding
developers and administrators in understanding the database structure.
 33

● Efficient Query Optimization: Assists the DBMS in optimizing queries by

providing information about indexes and relationships.

Role in Database Management

● Database Design: Helps in the design and modification of database schema.

● Query Processing: Assists in query parsing, optimization, and execution by
providing metadata.
● Security Management: Manages user access and permissions based on roles
and privileges defined in the data dictionary.
● Maintenance: Facilitates database maintenance tasks such as backup, recovery,
and performance tuning.

D.B.A.
A Database Administrator (DBA) is a crucial role in the management and maintenance
of database systems. DBAs are responsible for ensuring the availability, performance,
and security of databases, and they play a key role in the design, implementation, and
administration of database environments.

Key Responsibilities of a DBA

1. Database Installation and Configuration:

○ Setting up and configuring database software.
○ Ensuring that the database environment is properly installed and
configured according to organizational requirements.
2. Database Maintenance:
○ Performing regular backups and recovery operations to prevent data loss.
○ Applying updates and patches to keep the database software up-to-date.

Skills Required for a DBA

● Technical Expertise: Proficiency in database management systems (DBMS) such

as Oracle, SQL Server, MySQL, PostgreSQL, etc.
 34

● Problem-Solving Skills: Ability to diagnose and resolve complex database

issues.
● Attention to Detail: Ensuring data integrity and accuracy.

DDL & DML

DDL & DML commands

In a Database Management System (DBMS), DDL (Data Definition Language) and DML
(Data Manipulation Language) are two types of SQL (Structured Query Language)
commands used for different purposes.

Data Definition Language (DDL)

DDL commands are used to define and manage the structure of the database objects
such as tables, indexes, and schemas. These commands include:

1. CREATE: Creates a new database object, such as a table, index, or view.

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

Department VARCHAR(50),

Salary DECIMAL(10, 2)

);
 35

2.ALTER: Modifies the structure of an existing database object.

ALTER TABLE Employees ADD DateOfBirth DATE;

3.DROP: Deletes an existing database object.

DROP TABLE Employees;

4.TRUNCATE: Removes all rows from a table, but keeps the table structure for future
use.

TRUNCATE TABLE Employees;

5.RENAME: Renames a database object.

RENAME TABLE Employees TO Staff;

Data Manipulation Language (DML)

DML commands are used to manipulate and manage the data within the database
objects. These commands include:

1.INSERT: Adds new rows of data to a table.

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)

VALUES (1, 'John', 'Doe', 'Engineering', 60000.00);

2.UPDATE: Modifies existing data within a table.

 36

UPDATE Employees SET Salary = 65000.00 WHERE EmployeeID = 1;

3.DELETE: Removes rows of data from a table.

DELETE FROM Employees WHERE EmployeeID = 1;

SELECT: Retrieves data from one or more tables.

1. SELECT * FROM Employees WHERE Department = 'Engineering';

2. SELECT * FROM Employees ;

Primary Key

In SQL, a primary key is a column or a set of columns in a table that uniquely identifies
each row in that table. The primary key must contain unique values, and it cannot
contain NULL values. Each table can have only one primary key, which ensures the
uniqueness and integrity of the data.

Key Characteristics of a Primary Key

● Uniqueness: Each value in the primary key column(s) must be unique.

● Non-Nullable: Primary key columns cannot contain NULL values.
● Single or Composite: A primary key can consist of a single column or a
combination of multiple columns (composite key).

Example of Creating a Primary Key

Here’s how you can create a primary key in a table:

Single Column Primary Key

 37

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

Department VARCHAR(50),

Salary DECIMAL(10, 2)

);