f

Experiment No.: 1
AIM: Introduction to Database Management System (DBMS)
INTRODUCTION: A database management system is a software tool that enables users to
manage a database easily. It allows users to access and interact with the underlying data in the
database. These actions can range from simply querying data to defining database schemas that
fundamentally affect the database structure. A database is usually controlled by a database
management system (DBMS).
Here are some examples of DBMS include:
Windows registry, Microsoft Access, XML, SQL, MySQL, Oracle, etc.

ADVANTAGES OF DBMS:
1.Data Organization: A DBMS provides a systematic approach to organizing data in a
structured way, which makes it easier to retrieve and manage data efficiently.
2.Data Consistency and accuracy: DBMS ensures that data is consistent and accurate by
enforcing data integrity constraints and preventing data duplication. This helps to eliminate
data discrepancies and errors that can occur when data is stored and manage manually .
3.Data Sharing : A DBMS provides a platform for sharing data across multiple applications
and users, which can increase productivity and collabration.
4.Data Integration: Due to the database management system we have access to well-managed
and synchronized forms of data thud it makes data handeling very easy and gives an integrated
view of how a particular organization is working.
5.Data security: The more accessible and usable the database, the more it is prone to security
issues. As the number of users increases, the data transferring or data sharing rate also
increases thus increasing the rate of data security. It is widely used in the corporate world
where companies invest large amounts of money, time and effort to ensure data is secured and
used properly.
6.Data Scalability and flexibity: DBMS is highly scalable and can easily accommodate
changes in data volumes and user requriments. It provides flexibity in data storage, retriveal,
and manipulation, allowing users to easily modify the structure and content of the database as
needed.
f

7.Concurrency and maintained atomicity: That means, if some operation is performed on

one particular table of the database the changes must be reflected for the entire database. The
DBMS allows concurrent access to multiple users by using the synchronization technique.

DISADVANTAGES OF DBMS:
1.Damaged part: If one part of database is corrupted or damaged, then entire database
may get affected.
2.Complex design: Database management is complex, difficult and time consuming.
3.Compatibility: DBMS software may not be compatible with other software systems or
platforms, making it difficult to integrate with other applications.
4.Data isolation: Because data are scattered in various files, and files may be in different
formats, writing new application programs to retrieve the appropriate data is difficult.
5.Increased cost: DBMS is the high cost. The cost can be of many types, such as
hardware or software costs, data storage costs, etc.

Experiment No: 2

AIM: To install MySQL application on device.

INTRODUCTION: MySQL is an open-source relational database management system

(RDBMS) developed by Oracle Corporation that’s used to store, retrieve, manipulate, and
process data in the form of tables. MySQL is based on structured qyery language (SQL)
and is considered the world’s most popular open source database.
f

PROTOCOL:

search MySQL download on google Go to downloads section.

f

Click on MySQL community downloads.

Click on MySQL installer for windows.

Dwnload the appropriate version of Click on ‘Execute’ to install the above

MySQL on the system. Three.
f

After execution, click on ‘Next’. After product configuration, click‘ Next’.

Under ‘accounts and roles’ set a valid Select the appropriate options and click.
‘MySQL Root Password’ and click ‘next’.
f

Give appropriate ‘Server file permissions’ Apply and execute all the configuration
and click ‘next’.

After execution click ‘Finish’. Once the installation is complete click

‘Finish’ and add the path in ‘system
environment’.
f

In command
prompt,use syntax: mysql –version
to check the version of MySQL and then use
syntax: mysql-u root -p to root MySQL workbench
through.
RESULT: Thus, the installation and setup of MySQL application on the system is
successfully completed.
f

EXPERIMENT NO : 3

AIM: To create a database on command prompt and MySQL Workbench.

INTRODUCTION : MySQL is a relational database management system. The

Database structure is organized into physical files optimized for
Speed. The logical data model, with objects such as data tables,
Views, rows, and columns, offers a flexible programming
Environment.

PROTOCOL :
SYNTAX FUNCTION
mysql --version To retrieve the version of the
MySQL client utility, which
may differ from the version of
the MySQL server that it
connects to.
f

mysql -u root -p To connect to a MySQL

server as the root user with a
password prompt.
create database 1.
name;  To create a new database
within the MySQL server.
The database_name is the
name you assign to this new
database.

show databases; To display a list of all the

databases available on the
MySQL server instance.
use To set the specified database as
database_name; the active database for the
current session.
create table To create a table with required
name(col1 no. of attributes
datatype, col2
datatype, col3
datatype);
desc table_name; To display the structure of a
table, including the column
names, data types, and other
f

relevant information.
Insert into table- To add new records (rows) to
_name(col1, col2, a table. It specifies the table
col3) values(10, name, the columns to be
‘abx’, ‘xyz’); filled, and the corresponding
values for those columns. .
create table Creating a table in SQL with
name(col1 datatype primary and unique keys is
primary key, col2 an essential part of database
datatype, col3 design, as it helps maintain
datatype unique data integrity and enforce
key); rules on the data being
stored.
drop table name; To delete a particular table
drop database To delete a particular database
name;
select * from table- To view the table
_name;
create table Creating a table with primary
name2(COL1 keys, unique keys, and
datatype primary foreign keys is essential for
key, COL2 establishing relationships
datatype, COL3 between tables in a relational
datatype unique database. This structure
key, foreign key helps maintain data integrity
f

(COL1) references and enforce referential

name(col1)); integrity.
set foreign- To temporarily disable
_key_checks=0; foreign key constraints.
select * from To view the two tables in merged
table_name, form
table_name2;
select * from To retrieve data from a table
table_name where based on specified criteria.
col1=10; The WHERE clause is used to
filter the results based on a
condition.

EXERCISE 1 : To create a database on mysql workbench.

f

EXERCISE 2: To delete a column, table and database on mysql workbench.

To delete a table

To delete a database in mysql workbench

f

To update data in a table of a database on mysql workbench

f

To alter data in a table of a database

RESULT: Database is created on mysql workbench by using various types of Syntax.

f

EXPERIMENT NO: 4

AIM: To use SELECT command to sort and filter data and use of GROUP BY

INTRODUCTION: Select keyword is used to filter and sort data from Relations.
This helps to display the data user wants to see. We can also
Use select to view all data from the table.

4.1 : To use select * from the

4.2: To use select collegeID, city from college where collegeID=2

f

4.3: To use select count(first_name) as total_student from college;

f

4.4: To use select * from student order by name desc;

4.5: To use select * from college where collegeID in (2,3);

f

4.6: To use
Select first_name from college where first_name like ‘%a’;
Select first_name from college where first_name like ‘a%h’;
Select first_name from college where first_name like ‘%t;

RESULT:

Experiment No : 05
f

AIM : To study JOINS a derived operation in relational algebra

INRTODUCTION : Relational algebra is a theory that uses algebraic structures for modeling
data, and defining queries on it with well fonded semantics.

Joins are a derived operation in relational algebra that combine rows from
two or more tables based on a related column between them. They allow
retrieving data from multiple interconnected tables simultaneously.

1.INNER JOIN: Only include values who has matching values in relation 1 & 2 on specified
condition.

2.LEFT JOIN: Returns all rows from relation 1 & matching rows from relation 2.
f

3.RIGHT JOIN: Returns all rows from relation 2 & matching rows from relation.
f

4.FULL JOIN: This join returns all rows when there is a match in either table. If no match,
NULL is returned.

RESULT: The application of JOINS on relations in command prompt is done successfully.

f

EXPERIMENT NO : 07

AIM : To study INDEXING(create, show delete, update)

INTRODUCTION :

INDEXING: Indexing is a technique used to speed up the retrieval of records from the
database. It creates an auxiliary data structure that helps the database system to efficiently
locate the records without having to scan the entire table. It acts like a pointer to the data,
allowing for faster search, retrieval, and updating.

PROTOCOL:-

1. To create an index on a column in a relation

Create index index_name on table_name(column_name);
2. To show indexes on a table
Show indexes on table_name;

3. To alter a table and create index

Alter table table_name add index index_name(column_name);
4. To drop an index on a column in a relation
Drop index index_name on table_name;
f

RESULT: Indexing is studied and created successfully.

f

Experiment No: 8

Aim: To create and work with views.

Introduction; Views are virtual tables that are derived from one or more base tables. Unlike
physical tables, views do not store data themselves but present data from the base tables
through a query. Views can be used to simplify complex queries, enhance security, and present
specific data to users without giving access to the underlying base tables.

Protocol:

1.To create a view on a relation

Create view view_name as select column_name from table_name;
2.To show view on a relation
Select * from information_schema.views;
3.To drop a view on a relation
Drop view view_name on table_name;
f

RESULT: Viewing on a relation is done successfully.

f

Experiment No.: 9

AIM: To study Transaction.

INTRODUCTION: In a database management system (DBMS), transactions are a

fundamental concept that ensures the integrity and consistency of the data. Transactions are a
sequence of one or more database operations (e.g., insert, update, delete) that are treated as a
single, indivisible unit.

Here are some important notes on transactions in DBMS:

1. ACID Properties: Transactions in DBMS must adhere to the ACID properties, which stand
for Atomicity, Consistency, Isolation, and Durability. These properties ensure the reliability of
database operations.
- Atomicity: A transaction is atomic, meaning it is treated as a single, indivisible unit. Either
all the operations in a transaction are executed successfully, or none of them are. There are no
partial results.
- Consistency: A transaction brings the database from one consistent state to another. It ensures
that the data adheres to all integrity constraints, such as referential integrity and domain
constraints.
- Isolation: Each transaction should be isolated from other concurrent transactions. This means
that the execution of one transaction should not interfere with or be visible to other transactions
until it's completed.
- Durability: Once a transaction is committed, its changes should be permanent and survive
system failures. Durability guarantees that the changes made by committed transactions will
not be lost.

2. Transaction States:
- Active: The initial state when a transaction is actively performing operations.
f

- Partially Committed: After all operations have been successfully executed, but before the
transaction is fully committed.
- Committed: The transaction is successfully completed, and its changes are permanent.
- Aborted: The transaction is terminated due to an error, and any changes made by the
transaction are rolled back.

3. Transaction Management:
- BEGIN TRANSACTION: Initiates a new transaction.
- COMMIT: Marks the transaction as complete, making all its changes permanent.
- ROLLBACK: Aborts the transaction and undoes all its changes.
- SAVEPOINT: Allows creating a point within a transaction to which you can later roll back.

4. Concurrency Control: DBMS uses concurrency control mechanisms, such as locks and
timestamps, to manage multiple concurrent transactions and ensure their isolation and
consistency
5. Transaction Isolation Levels:
- Read Uncommitted: The lowest level where transactions can read uncommitted changes
from other transactions.
- Read Committed: Transactions can only read committed changes made by other
transactions.
- Repeatable Read: Ensures that, within a transaction, the same query will return the same
results, even if other transactions are modifying data.
- Serializable: Highest level of isolation, ensuring that transactions appear to be executed
one at a time.

6. Deadlock: A deadlock occurs when two or more transactions are waiting for each other to
release resources. DBMSs have mechanisms to detect and resolve deadlocks.

7. Logging: Transactions and their changes are often logged to ensure durability. This log can
be used for recovery in case of system failures.
f

8. Two-Phase Commit (2PC): A protocol used for distributed transactions, ensuring that all
participants agree to commit or abort the transaction.

9. Savepoints: Savepoints allow you to set intermediate points within a transaction. If an error
occurs, you can roll back to a savepoint instead of rolling back the entire transaction.

10. Transaction Rollback: Transactions can be rolled back due to errors, violations of
integrity constraints, or user-initiated rollbacks. Rollbacks should leave the database in a
consistent state.

CONCLUSION: Thus, transactions are a critical concept in DBMS, and they are essential for
maintaining data integrity and ensuring that database operations are executed reliably and
consistently.
f

Experiment No.: 8

AIM: To study Database Recovery and Backup.

INTRODUCTION: Recovery and backup are crucial aspects of data management and system
maintenance, particularly in the context of databases and computer systems. Here are some
important notes on recovery and backup:

Backup: Backup is the process of creating copies of data and storing them in a safe and
separate location from the original data. The purpose is to ensure data preservation and
recovery in case of data loss, corruption, or system failures.

1. Types of Backups:
- Full Backup: A complete copy of all data and files.
- Incremental Backup: Only backs up data that has changed since the last backup (either
full or incremental).
- Differential Backup: Backs up data that has changed since the last full backup.
- Snapshot Backup: A point-in-time copy of the entire system or volume, useful for virtual
environments.

2. Backup Strategies:
- Grandfather-Father-Son (GFS): A strategy involving daily, weekly, and monthly backups.
- 3-2-1 Rule: Keep three copies of data (original and two backups), on two different types of
media, with one copy offsite.
- Cloud Backup: Storing backups in the cloud for added redundancy and accessibility.

3. Data Retention Policy: Defines how long backups are retained and when they are
automatically purged.
f

4. Importance: Backups are essential for disaster recovery, ensuring data availability in cases
of hardware failures, data corruption, accidental deletions, or natural disasters.

Recovery: Recovery is the process of restoring data and system operations to a usable state
after a failure or data loss event. It involves the restoration of data from backups and the repair
of the system.

1. Types of Recovery:
- Point-in-Time Recovery: Restoring data to a specific point in time, often used in database
systems.
- Rollback: Reverting changes made by a transaction to a previous state.
- Forward Recovery: Applying changes and updates from a log or transaction history to
reach a desired state.
- Disaster Recovery: A comprehensive plan to recover systems and data after a major
disaster.
2. Recovery Points:
- Last Known Good Configuration: Restoring to the most recent known good state.
- Backup Image: Restoring to a specific backup point.
- Transaction Log: Restoring to a specific point in time based on transaction logs.

3. Redundancy and High Availability: Implementing redundancy and high availability

solutions, such as failover clusters, can reduce recovery time in the event of system failures.

4. Testing and Validation: Regularly test and validate your recovery process to ensure that
backups are usable and recovery procedures are effective.

5. Business Continuity Planning (BCP): Develop a comprehensive business continuity plan

that includes recovery strategies for both data and business operations.
f

7. Legal and Compliance Considerations: Ensure that your backup and recovery procedures
comply with legal and regulatory requirements, especially for data privacy and retention.

CONCLUSION: In summary, backup and recovery are critical components of data

management and system administration. They are essential for safeguarding data and ensuring
that organizations can quickly recover from data loss or system failures. A well-designed and
tested backup and recovery strategy is a fundamental part of a robust IT infrastructure.

Experiment No.: 11
f

AIM: Data Validation and Constraints: Implementation constraints (Ex. NOT NULL,
UNIQUE, CHECK) on the database schema to enforce data integrity and validation rules.
INTRODUCTION: The keys are the part of DBMS which maintain integrity of data. In
DBMS there are various types of keys are used on database schema to enforce data integrity.
1. Primary key- It is unique identifier for each record in a table. It has following features:
A. Not null i.e., it cannot have null value.
B. Uniqueness- each value will be unique enabling to identify each row.
C. Efficient Data Retrieval- As it allows to identify each row uniquely, it makes easy to retrieve
data.
2. Composite key- It is a combination of 2 or more columns which allows to uniquely identify
each record.