CLASS WORK ASSIGNMENT NO.

:- 01

SESSIONAL WORK EXPERIMENT NO. :- 01

SUBMITTED ON :- 08/11/2024 MARKS OR GRADE OBTAINED :- _ _ _

NAME :- ANURAG PAL ROLL NO. :- 0873AL221007

CLASS :- 3rd Year/5th Sem DEPATMENT :- Artificial Intelligence & Machine Learning

SUBJECT :- DBMS LAB CODE NO. :-CS-502

Signature of Student Signature of Professor

Q.1 Explain ER Diagram with diagram.

=> An *Entity-Relationship Diagram (ERD)* is a visual tool used to represent the structure of a database. It shows how
different pieces of data (entities) are connected and related to each other.

### Main Components:

1. *Entities: These are objects or things in the system, like **Customer, **Product, or **Order*.

2. *Attributes: These describe the properties of an entity, like a **Customer's* *Name* or *Address*.

3. *Relationships: These show how entities are related. For example, a **Customer* places an *Order*.

4. *Primary Key: A unique identifier for each entity, like **CustomerID*.

### Types of Relationships:

1. *One-to-One (1:1): One entity is related to one other entity. Example: A **Person* has one *Passport*.

2. *One-to-Many (1:N): One entity is related to many others. Example: A **Department* has many *Employees*.

3. *Many-to-One (M:1): Many entities are related to one. Example: Many **Orders* can be placed by one *Customer*.

4. *Many-to-Many (M:N): Many entities are related to many others. Example: A **Student* can enroll in many
*Courses*.
ERDs help to design databases by clearly showing how data elements are connected, making it easier to organize and manage
data.

This image is a simple entity-relationship diagram (ERD). It illustrates:

*Entities* (rectangles) that represent objects or concepts in the database (e.g., a "Person" or "Product").

- *Attributes* (oval shapes connected to entities) that define specific properties or characteristics of an entity (e.g., a
person’s "Name" or "Age").

- *Relationships* (diamonds) that connect entities to show how they are related (e.g., "Works for" or "Buys").

This ERD visually organizes data by showing entities, their attributes, and the relationships between them.
Q.2 Study about RDBMS & Its Application, & installation of MySQL.

=> Relational Database Management System (RDBMS):-An RDBMS is a database management system that organizes data into
tables (also known as relations) with rows and columns. This structure allows for efficient data retrieval, storage, and
management using Structured Query Language (SQL).

Key Features of RDBMS:-

1. Tables and Schema: Data is stored in tables with a defined schema. Tables are structured with rows (records)
and columns (attributes).

2. Relationships: Data in different tables can be connected or related. These relationships are managed by using
foreign keys and primary keys.

3. Data Integrity: Maintains data accuracy and consistency through constraints like primary keys, foreign keys,
unique keys, etc.

4. ACID Compliance: RDBMS supports Atomicity, Consistency, Isolation, and Durability to ensure transaction
reliability.

5. SQL Support: SQL is used to query, update, delete, and manage data in the RDBMS.

Applications of RDBMS:- RDBMS systems are commonly used in various applications due to their reliable data management
capabilities:

1. Banking Systems: Store customer information, transaction records, and account details. 2. E-commerce: Manage product
catalogs, customer orders, inventory, and user profiles

3. Healthcare: Store patient records, appointment schedules, and treatment details.

4. Social Media: Handle user data, friend connections, messages, and activity logs.

5. Telecommunications: Manage customer data, billing information, and call records.

Installing MySQL:-MySQL is a popular open-source RDBMS that is widely used for web applications and data management.

Step 1: Download MySQL

1. Go to the official MySQL website: MySQL Downloads.

2. Choose the MySQL Community Server version compatible with your operating system (Windows, macOS,
Linux).

3. Download the installer file.

Step 2: Install MySQL

1. Open the downloaded installer file.

2. Follow the setup wizard:

1. Choose the Setup Type (Developer Default, Server Only, Full, or Custom).

2. Select the installation path.

3. Configure MySQL Server:

1. Choose the Server Configuration options (Standalone, InnoDB cluster, or Replica).

2. Set the Root Password and optionally add other users.

3. Select the Port (default is 3306).

 4. Configure MySQL as a Windows Service (on Windows) to allow MySQL to run in the background.

5. Finish the installation by clicking Execute to apply the configurations.

Step 3: Verify MySQL Installation

 Open a command prompt or terminal.

 Type the following command to start the MySQL command-line tool and log in
v mysql -u root -p

Ø Enter the root password you set during installation. If you see the MySQL prompt (mysql>), the installation was successful.

You’re now ready to create databases, tables, and manage data using MySQL!
Q.3 Study about SQL Commands:-

> Create Table

> Insert Value in Table

> Drop Table

> Alter Table

> Truncate Table =>

> Create Table :-The CREATE TABLE command is used to define a new table and its columns.

> CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

Age INT,

Salary DECIMAL(10, 2)

);

> Insert Value in Table:-The INSERT INTO command adds new rows (records) into a table.

> INSERT INTO Employees (EmployeeID, FirstName, LastName, Age, Salary)

VALUES (1, 'John', 'Doe', 30, 50000.00);

> Drop Table :- The DROP TABLE command permanently deletes a table and all its data from the database.

>DROP TABLE Employees;

> Alter Table:- Add a new column:

ALTER TABLE Employees

ADD Address Varchar(255);

Modify an existing column:-

ALTER TABLE Employees

MODIFY COLUMN Salary numeric;

Delete a column:-

ALTER TABLE Employees

DROP COLUMN Address;

> Truncate Table:-

TRUNCATE TABLE Employees;

Q.4 – Use select command for following structure :-
1. Basic select command:-The SELECT statement in SQL is used to retrieve data from one or more tables in a database. It
allows you to specify which columns to retrieve and can be used with various clauses to filter, sort, and organize the result set.

Here’s the basic command syntax :-

2. Using Where clause:-To retrieve data from the Employee table using the WHERE clause, you can filter the rows based on
specific conditions. The WHERE clause allows you to specify criteria for the data you want to retrieve.

Here’s the basic command syntax :-

Q.5 Study about order by , group by, having by:-

=> ORDER BY Clause in SQL :- The ORDER BY clause is used to sort the result set of a SELECT query based on one or more
columns. You can sort the results in ascending (default) or descending order.

Here’s the basic command syntax :-

GROUP BY Clause in SQL :- The GROUP BY clause is used to group rows that have the same values in specified columns into
summary rows, like calculating totals, averages, or counts for each group. This is often used with aggregate functions like
COUNT(), SUM(), AVG(), MAX(), and MIN() to perform calculations on each group of data.

Here’s the basic command syntax :-

HAVING Clause in SQL :- The HAVING clause is used in SQL to filter records after grouping the data with the GROUP BY clause,
typically used with aggregate functions like COUNT(), SUM(), AVG(), etc. Here’s the basic command syntax :-

Q.6 Difference between different DCL commands:-

=> Here’s a comparison of the DCL (Data Control Language) commands GRANT and REVOKE:

Command Purpose Used To Example Effect

- The specified user/role

GRANT SELECT,

- Assign privileges like gains the granted INSERT ON

SELECT, INSERT, privileges on the given

Grants specific Employee TO John;

UPDATE, DELETE, object. privileges to a (This grants John the

GRANT etc., to users or roles. - Can be granted with user or role on a ability to SELECT

- Provide access to the WITH GRANT database object. and INSERT data specific tables, views, or
OPTION to allow the

into the Employee other objects. user to grant those

table.) privileges to others.

- The specified user/role

- Revoke privileges from
users or roles.

Removes specific

- Prevents users from privileges that

REVOKE performing certain

were previously actions (like selecting, granted.

inserting, etc.) on database objects.

Here’s the basic GRANT command syntax :- REVOKE INSERT

loses the revoked

ON Employee

privileges.

FROM John;

- If the user/role no

(This revokes John's longer has any ability to insert data privileges,
they cannot into the Employee

perform any operations table.) on the object.

Here’s the basic REVOKE command syntax :-
Q.7 Study and use join command:-

=> In SQL, the JOIN command is used to combine rows from two or more tables based on a related
column between them. There are several types of joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL
JOIN, and SELF JOIN.

Types of JOINs:-

1. INNER JOIN: Returns rows when there is a match in both tables.

2. LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matched
rows

from the right table. If there’s no match, NULL values will be returned for columns from the right
table.

3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and
matched rows from the left table. If no match exists, NULL values will be returned for
columns from the left table.

4. FULL JOIN (or FULL OUTER JOIN): Returns rows when there is a match in one of the
tables. If no match exists, NULL values will be returned for non-matching rows in either table.

5. SELF JOIN: Joins a table with itself, used when a table has hierarchical data or when
you need to compare rows within the same table.

Here’s the basic INNER JOIN command syntax :-

Here’s the basic LEFT JOIN command syntax :-

Here’s the basic RIGHT JOIN command syntax :-

Here’s the basic FULL JOIN command syntax :-

Here’s the basic SELF JOIN command syntax :-