EXPERIMENT 1

AIM: To study the basics of DBMS

THEORY:

1.) Database Management System (DBMS)- It is a program or group of programs that work in
conjunction with the operating system to create, process, store, retrieve, control and manage the
data. It acts as an interface between the application program and the data stored in the Database.

2.) Components of DBMS

A DBMS has three main components. These are Data Definition Language (DDL), Data Manipulation
Language and Query Facilities (DML/SQL) and software for controlled access of Database as shown
in Figure 1 and are defined as follows:

Fig 1: Components of DBMS

i.) Data Definition Language (DDL)

It allows the users to define the Database, specify the data types, data structures and the constraints
on the data to be stored in the Database.

ii.) Data Manipulation Language (DML) and Query Language

DML allows users to insert, update, delete and retrieve data from the Database. SQL provides
general query facility.

iii.) Software for Controlled Access of Database

This software provides the facility of controlled access of the Database by the users, concurrency
control to allow shared access of the Database and a recovery control system to restore the
Database in case of hardware or software failure.

3.) Relational Model in DBMS

E.F. Codd proposed the relational Model to model data in the form of relations or tables. After
designing the conceptual model of the Database using ER diagram, we need to convert the
conceptual model into a relational model which can be implemented using any RDBMS language like
Oracle SQL, MySQL, etc.

Important Terminologies in Relational Model

The relational model represents how data is stored in Relational Databases. A relational database
consists of a collection of tables, each of which is assigned a unique name. Consider a relation
STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE, and AGE shown in the table:

STUDENT

ROLL_NO NAME ADDRESS PHONE AGE

1 ADITYA GURUGRAM 9876543210 18
2 AKSHITA UTTAR PRADESH 9867543215 20
3 ANKIT DELHI 9871234565 19

i. Attribute- Attributes are the properties that define an entity. e.g. ROLL_NO, NAME,
ADDRESS, PHONE, AGE.
ii. Tuple- Each row in the relation is known as a tuple. The above relation contains 3 tuples,
one of which is shown as:

2 AKSHITA UTTAR PRADESH 9867543215 20

iii. Relation Schema- A relation schema defines the structure of the relation and represents
the name of the relation with its attributes. e.g.; STUDENT (ROLL_NO, NAME, ADDRESS,
PHONE, and AGE) is the relation schema for STUDENT. If a schema has more than 1
relation, it is called Relational Schema.
iv. Degree- The number of attributes in the relation is known as the degree of the relation.
The STUDENT relation defined above has degree 5.
v. Cardinality-The number of tuples in a relation is known as cardinality.
The STUDENT relation defined above has cardinality 3.

4.) Database Languages

• A DBMS has appropriate languages and interfaces to express database queries and updates.
• Database languages can be used to read, store and update the data in the Database.
1.) Data Definition Language (DDL)

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

Here are some tasks that come under DDL:

• Create: It is used to create objects in the Database.

• Alter: It is used to alter the structure of the Database.
• Drop: It is used to delete objects from the Database.
• Truncate: It is used to remove all records from a table.
• Rename: It is used to rename an object.
• Comment: It is used to comment on the data dictionary.

These commands are used to define and update the database schema that's why they come under
Data definition language.

2. Data Manipulation Language (DML)

DML stands for Data Manipulation Language. It is used for accessing and manipulating data in a
database. It handles user requests.

Here are some tasks that come under DML:

• Select: It is used to retrieve data from a database.

• Insert: It is used to insert data into a table.
• Update: It is used to update existing data within a table.
• Delete: It is used to delete all records from a table.

3. Data Control Language (DCL)

DCL stands for Data Control Language. It is used to retrieve the stored or saved data.

The DCL execution is transactional. It also has rollback parameters.

Here are some tasks that come under DCL:

• Grant: It is used to give user access privileges to a database.

• Revoke: It is used to take back permissions from the user.
There are the following operations which have the authorization of Revoke:

CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE and SELECT.

4. Transaction Control Language (TCL)

TCL is used to run the changes made by the DML statement. TCL can be grouped into a logical
transaction.

Here are some tasks that come under TCL:

• Commit: It is used to save the transaction on the Database.

• Rollback: It is used to restore the Database to original since the last Commit.
 EXPERIMENT-2

AIM: To study different Data Definition Language(DDL) commands:

1. CREATE
2. ALTER
3. DROP
4. TRUNCATE
5. RENAME

THEORY:

Structured Query Language (SQL): SQL, or Structured Query Language, is a domain-specific language
designed for managing and querying relational databases. It provides a standardized way to interact
with databases, making it an essential tool for anyone working with data. SQL commands perform
various database operations, such as creating tables, inserting data, querying information, and
controlling access and security. SQL commands can be categorized into different types, each serving
a specific purpose in the database management process.

Data Definition Language (DDL): DDL, which stands for Data Definition Language, is a subset of SQL
(Structured Query Language) commands used to define and modify the database structure. These
commands create, alter, and delete database objects like tables, indexes, and schemas. The primary
DDL commands in SQL include:

1.) CREATE: This command is used to create a new database object. For example, creating a
new table, a view, or a database.

Syntax:

CREATE TABLE table_name (column1 datatype, column2 datatype,…);

Example:

CREATE TABLE Persons (

PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255));
2.) ALTER: The ALTER TABLE statement adds, deletes, or modifies columns in an existing table.

The ALTER TABLE statement also adds and drops various constraints on an existing table.

i) To add a new column using Alter table command to the existing table.

Syntax:

ALTER TABLE table_name ADD column_name datatype;

Example: Alter Table Persons add PIN varchar(10);

ii) To drop a column using Alter Table command from the existing table.

Syntax:

ALTER TABLE table_name DROP COLUMN column_name;

Example: Alter table Persons drop column PIN;

3.) RENAME: It renames the table or a column in the table.

 Syntax:
i.) Rename the table

ALTER TABLE table_name RENAME TO new_table_name;

Example: Alter table Persons Rename to Persons_info;

ii.) Rename the column:

ALTER TABLE table_name RENAME COLUMN oldcolumn_name to

newcolumn_name;

Example: ALTER TABLE Persons_info RENAME COLUMN PersonID to ID;

4.) TRUNCATE: The TRUNCATE TABLE command deletes the data inside a table but not the table
itself.
Syntax:

TRUNCATE TABLE table_name;

 Example: Truncate Table Persons_info;

5.) DROP: The DROP TABLE command deletes a table in the Database.

Syntax:

DROP TABLE table_name;

Example: Drop table Persons_info;

CONCLUSION: Different Data Definition Language(DDL) commands have been successfully studied.
 EXPERIMENT-3

AIM: Apply different Integrity Constraints to the tables:

• PRIMARY KEY
• FOREIGN KEY
• NOT NULL
• DEFAULT
• UNIQUE
• CHECK

THEORY:

SQL Constraints:

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and
reliability of the data in the table. The action is aborted if there is any violation between the
constraint and the data action.

Constraints can be column level or table level. Column-level constraints apply to a column, and
table-level constraints apply to the whole table.

The following constraints are commonly used in SQL:

NOT NULL - Ensures that a column cannot have a NULL value

UNIQUE - Ensures that all values in a column are different

PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

FOREIGN KEY - Prevents actions that would destroy links between tables

CHECK - Ensures that the values in a column satisfy a specific condition

DEFAULT - Sets a default value for a column if no value is specified.

IMPLEMENTATION:

Create a table Employee with the following Attributes and Constraints:

In the Employee table:

1. EmployeeID is the primary key.

2. Name cannot be null (NOT NULL constraint).
3. Age has a check constraint to ensure it's greater than or equal to 18.
4. City has a default value of 'Delhi.'
5. Name is marked as UNIQUE, ensuring each employee has a unique name.
Syntax:

CREATE TABLE Employee (

EmployeeID INT PRIMARY KEY,

Name VARCHAR(255) NOT NULL,

Age INT CHECK (Age >= 18),

City VARCHAR(255) DEFAULT 'Delhi',

UNIQUE (Name));

Create another table Project with the Foreign Key:

In the "Project" table:

1. ProjectID is the primary key.

2. ProjectName cannot be null.
3. EmployeeID is a foreign key referencing the EmployeeID column in the "Employee" table.
4. Department can be null.

Syntax:

CREATE TABLE Project (

ProjectID INT PRIMARY KEY,

ProjectName VARCHAR(25) NOT NULL,

EmployeeID INT,

Department VARCHAR(25),

FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID));

VIOLATING CONSTRAINTS:

1.) VIOLATING UNIQUE CONSTRAINT-Inserting a duplicate Name into Employee:

2.) VIOLATING CHECK CONSTRAINT-Inserting a value less than 18 into Age in Employee:

3.) VIOLATING NOT NULL CONSTRAINT-Inserting a null value into ProjectName in Project:

4.) VIOLATING REFERENTIAL INTEGRITY CONSTRAINT-Inserting a non-existent EmployeeID into

Project:

CONCLUSION: Different SQL Constraints have been successfully studied.

 EXPERIMENT-4

AIM: To study various Data Manipulation Language(DML) commands:

• INSERT
• SELECT
• UPDATE
• DELETE

THEORY:

DML stands for Data Manipulation Language. It is a subset of SQL (Structured Query Language)
that allows users to manipulate data stored in a database. DML commands are primarily used to
retrieve, insert, update, and delete data in database tables.

Here's an explanation of each DML command:

1.) SELECT:

Used to retrieve data from one or more tables.

Syntax: SELECT column1, column2, ... FROM table_name WHERE condition;

2.) INSERT:

Used to add new rows of data into a table.

Syntax: INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

3.) UPDATE:

Used to modify existing data in a table.

Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

4.) DELETE:

Used to remove one or more rows from a table.

Syntax: DELETE FROM table_name WHERE condition;

IMPLEMENTATION:

Create table Products to perform DML OPERATIONS on it:

CREATE TABLE Products (

ProductID INT PRIMARY KEY,

ProductName VARCHAR(100),

Category VARCHAR(100),

Price DECIMAL(10, 2),

StockQuantity INT);
1.) INSERT OPERATION:

Insert values into the table Product:

2.) SELECT OPERATION:

i.) Select all the records from the relation:

Select * from Products

ii.) Select products with a price greater than 15500:

SELECT * FROM Products WHERE Price > 15500;
 iii.) Select products in the 'Electronics' category

SELECT * FROM Products WHERE Category = 'Electronics';

3.) UPDATE OPERATION:

i.) Update the price of all products by increasing it by 100

UPDATE Products SET Price = Price +100;

ii.) Update the stock quantity of a specific product

UPDATE Products SET StockQuantity = 25 WHERE ProductID = 2;

4.) DELETE OPERATION:

i.) Delete from Products where stock quantity is less than 15.

DELETE FROM Products WHERE StockQuantity < 15;

CONCLUSION: DML commands have been successfully studied.