Ex.No.

:1
DATABASE DEVELOPMENT LIFE CYCLE-CASE STUDY
Date:

AIM
To learn about the phases involved in Database Development Lifecycle

THE DATABASE LIFE CYCLE (DBLC)

The Database Life Cycle (DBLC) contains six phases, as shown in the following
Figure: database initial study, database design, implementation and loading, testing
and evaluation, operation, and maintenance and evolution.

1. The Database Initial Study:

In the Database initial study, the designer must examine the current system’s
operation within the company and determine how and why the current system
fails. The overall purpose of the database initial study is to:
 Analyze the company situation.
 Define problems and constraints.
 Define objectives.
 Define scope and boundaries.
2. Database Design:
The second phase focuses on the design of the database model that will support
company operations and objectives. This is arguably the most critical DBLC
phase: making sure that the final product meets user and system requirements.
 The process of database design is loosely related to the analysis and design of
a larger system. The data component is only one element of a larger
information system.

1
  The systems analysts or systems programmers are in charge of designing the
other system components. Their activities create the procedures that will help
transform the data within the database into useful information.
3. Implementation and Loading:
The output of the database design phase is a series of instructions detailing the
creation of tables, attributes, domains, views, indexes, security constraints, and
storage and performance guidelines.
4. Testing and Evaluation:
In the design phase, decisions were made to ensure integrity, security, performance,
and recoverability of the database. In testing and evaluation, the DBA tests and fine-
tunes the database to ensure that it performs as expected. This phase occurs in
conjunction with applications programming.
5. Operation
Once the database has passed the evaluation stage, it is considered to be operational.
At that point, the database, its management, its users, and its application programs
constitute a complete information system. The beginning of the operational phase
invariably starts the process of system evolution.
6. Maintenance and Evolution
The database administrator must be prepared to perform routine maintenance
activities within the database. Some of the required periodic maintenance activities
include:
 Preventive maintenance (backup).
 Corrective maintenance (recovery).
 Adaptive maintenance (enhancing performance, adding entities and
attributes, and so on).
 Assignment of access permissions and their maintenance for new and
old users.

RESULT
Thus various phases of Database Development cycle have been studied.

2
Ex.No.:2
DATABASE DESIGN USING ER MODELING-CASE STUDY
Date:

AIM

To create database using conceptual modeling (ER-EER) in SQL workbench.

ER-EER MODELING
 Entity Relationship Modeling (ER Modeling) is a graphical approach to database
design. It uses Entity/Relationship to represent real world objects.

 An Entity is a thing or object in real world that is distinguishable from surrounding

environment. For example each employee of an organization is a separate entity.

 Enhanced Entity Relationship (EER) Model is a high level data model which
provides extensions to original Entity Relationship (ER) model. EER Models
supports more details design. EER Modeling emerged as a solution for modeling
highly complex databases.

EER uses UML notation. UML is the acronym for Unified Modeling Language; it is
a general purpose modeling language used when designing object oriented systems.
Entities are represented as class diagrams. Relationships are represented as
associations between entities. The diagram shown below illustrates an ER diagram
using the UML notation.

It is deduced that the nature of the relationship between members and payments entities
is one-to-many. Now EER model is created using MySQL Workbench In the MySQL
workbench , Click - "+"Button

In this case study MyFlix Video Library is used to understand the concept of ER
diagrams. MyFlix is a business entity that rents out movies to its members. MyFlix has
been storing its records manually. The management now wants to move to a DBMS.

3
The steps to develop EER diagram for this database are

1. Identify the entities and determine the relationships that exist among them.
2. Each entity, attribute and relationship, should have appropriate names that
can be easilyunderstood by the non-technical people as well.
3. Relationships should not be connected directly to each other. Relationships
should connect entities.
4. Each attribute in a given entity should have a unique name.

 Entities in the "MyFlix" library

The entities to be included in our ER diagram are;

Members - this entity will hold member information.

Movies - this entity will hold information regarding movies
Categories - this entity will hold information that places movies into
different categories suchas "Drama", "Action", and "Epic" etc.
Movie Rentals - this entity will hold information that about movies
rented out to members.
Payments - this entity will hold information about the payments made by
members.

 Defining the relationships among entitiesMembers and movies

The following holds true regarding the interactions between the two entities.

A member can rent a more than movie in a given period.

A movie can be rented by more than one member in a given period.

From the above scenario, it is understood that the nature of the relationship is many-to-
many. Relational databases do not support many-to-many relationships. Hence a
junction entity is introduced. This is the role that the MovieRentals entity plays. It has
a one-to-many relationship with the members table and another one-to-many
relationship with movies table.
In the MySQL workbench, Double click on Add Diagram button to open the
workspace for ER diagrams.

4
Following window appears

The two objects to work with are.

The table object allows us to create entities and define the attributes
associated with the particular entity.
The relationship button allows us to define relationships between entities.

The members' entity will have the following attributes

 Membership number
 Full names
 Gender
 Date of birth
 Physical address
 Postal address

5
Create the members table

1. Drag the table object from the tools panel

2. Drop it in the workspace area. An entity named table 1 appears 3.Double click on it.
The properties window shown below appears

Next,
1. Change table 1 to Members
2. Edit the default idtable1 to membership_number
3. Click on the next line to add the next field
4. Do the same for all the attributes identified in members' entity.

The properties window is given below.

6
 Repeat the above steps for all the identified entities.

The diagram workspace should now look like the one shown below.

Create relationship between Members and Movie Rentals

1. Select the place relationship using existing columns too

2. Click on membership_number in the Members table
3. Click on reference_number in the MovieRentals table

7
Repeat above steps for other relationships. The ER diagram should now look like this -

Summary

 ER Diagrams play a very important role in the database designing

process. They serve as anon-technical communication tool for
technical and non-technical people.
 Entities represent real world things; they can be conceptual as a sales
order or physical such as a customer.
 All entities must be given unique names.
 ER models also allow the database designers to identify and define
the relations that exist among entities.

RESULT
Thus the database design using conceptual modeling (ER-EER) and map
it to relational database is performed successfully.

8
Ex.No.:3 A)
IMPLEMENT THE DATABASE USING DDL AND DML
COMMANDS IN POSTGRE SQL
Date:

AIM
To implement a database using DDL and DML commands.

POSTGRESQL
 PostrgeSQL is an advanced relational database system.
 It supports both relational (SQL) and non-relational (JSON) queries.
 PostgreSQL is free and open-source.

DDL COMMANDS
DDL is an abbreviation for Data Definition Language. It is concerned with
database schemas and descriptions of how data should be stored in the database. DDL
statements are auto-committed, meaning the changes are immediately made to the
database and cannot be rolled back.
The DDL commands in SQL are divided into following major categories:
 CREATE
 ALTER
 TRUNCATE
 DROP

 CREATE
The CREATE query is used to create a database or objects such as tables, views,
stored procedures, etc.
 CREATE DATABASE
The CREATE DATABASE statement is used to create a new SQL database.
Syntax
CREATE DATABASE databasename;
Example
CREATE DATABASE testDB;
 CREATE TABLE
The CREATE TABLE statement is used to create a table in database.
Syntax
CREATE TABLE tablename(attributename1 datatype1,attributename2
datatype2);
Example
create table student(rollno int,name char(10),dept char(10),marks int);
 ALTER
The ALTER command in SQL DDL is used to modify the structure of an already
existing table.
 ADDING A NEW COLUMN
The ALTER command is used to add a new column in an existing database.
Syntax
ALTER TABLE tablename ADD attibutename datatypename;
Example
alter table student add age int;

9
  MODIFYING AN EXISTING COLUMN
The ALTER command is used to modify the datatype and its allocated size of an
existing column.
Syntax
ALTER TABLE tablename ALTER COLUMN columnane datatype;
Example
alter table student alter column name type char(20);
 RENAME
RENAME is a DDL command which is used to change the name of the database
table.
Syntax
ALTER TABLE OldTableName RENAME TO NewTableName;
Example
alter table student rename to stud;

 TRUNCATE
The TRUNCATE command is used to remove all the records from a table.
Syntax
TRUNCATE TABLE tablename;
Example
truncate table student;
 DROP
The DROP command is used to delete an existing database or an object within a
database.
 DROP DATABASE
The DROP DATABASE statement is used to drop an existing SQL database.
Syntax
DROP DATABASE databasename;
Example
DROP DATABASE testDB;

 DROP TABLE
The DROP TABLE statement is used to delete a table from a database.
Syntax
DROP TABLE tablename;
Example
drop table student;

DML COMMANDS
The DML commands in Structured Query Language change the data present in the SQL
database. DML commands are used to access, store, modify, update and delete the
existing records from the database.
The DML commands in SQL are divided into following major categories:
 SELECT
 INSERT
 UPDATE
 DELETE

 SELECT
SELECT is the most important data manipulation command in Structured
Query Language. The SELECT command shows the records of the specified table.

10
 It also shows the particular record of a particular column by using the WHERE
clause.
Syntax
SELECT * FROM tablename;
Example
SELECT * FROM Student;
SELECT EmpId, EmpSalary FROM Employee;

 INSERT
The INSERT command is used to insert data in database tables.
Syntax
INSERT into TABLENAME values (attributename1,attributename2….);
Example
INSERT into STUDENT values (102,'lenin','ai',95); (or)
INSERT into STUDENT (rollno,name,dept,marks)values(107,'vinai','ece',99);

 UPDATE
The UPDATE command is used to update or modify the existing data in database
tables.
Syntax
UPDATE tablename SET columnname1= value1 WHERE condition;
Example
UPDATE Product SET ProductPrice = 80 WHERE ProductId = 'P102' ;

 DELETE
DELETE is a DML command which allows SQL users to remove single or
multiple existing records from the database tables.
Syntax
DELETE FROM tableName WHERE condition;
Example
DELETE FROM Product WHERE ProductId = 'P202' ;

QUERIES & OUTPUT

create table student(rollno int,name char(10),dept char(10),marks int);

insert into student values(101,'anu','cse',89);

insert into student values(102,'priya','ece',90);
insert into student values(103,'devi','cse',96);
insert into student (rollno,name,dept,marks)values(107,'vinai','ece',99);

select * from student;

11
alter table student alter column name type char(25);

select * from student;

alter table student add age int;

update student set age=19 where dept='cse';

update student set age=20 where dept='ece';
select * from student;

delete from student where marks=90;

alter table student rename marks to totalmarks;

alter table student rename to stud;

12
select * from student;

ERROR: relation "student" does not exist LINE 1: select * from student; ^ SQL state:
42P01 Character: 15
select * from stud;

truncate table stud;

select * from stud;

drop table stud;

ERROR: relation "stud" does not exist LINE 1: select * from stud; ^ SQL state: 42P01
Character: 15

RESULT
Thus the DDL and DML commands were implemented and the data was
retrieved from the database successfully.

13
Ex.No.:3 B)
IMPLEMENT THE DATABASE WITH CONSTRAINTS
Date:

AIM
To implement the database with constraints using PosgreSQL.

CONSTRAINTS
Constraints in DBMS (Database Management Systems) are rules or conditions that are
applied to the data within a database to ensure data integrity, consistency, and adherence to
business rules.
There are several types of constraints available in DBMS and they are:
 Domain constraints
 Entity Integrity constraints
 Referential Integrity constraints
 Key constraints

DOMAIN CONSTRAINTS
The domain refers to the allowed values (range of values) for a function. The domain
value of an attribute must be an atomic value .
Types of Domain Constraints
 Not Null
The column value cannot be empty (i.e. cannot contain a null value)
 Check
The CHECK constraint checks the condition that follows it, e.g. CHECK
(Age>21) ensures that each Age column value is greater than 21.

ENTITY INTEGRITY CONSTRAINTS

Entity Integrity Constraint is used to ensure that the primary key cannot be null and it
must contain unique values.
Types of Entity Integrity Constraints
 Primary Key Constraint
When this constraint is associated with the column of a table it will not allow NULL
values into the column and it will maintain unique values as part of the table.
 Unique Constraint
The column cannot contain duplicate values (i.e. all values in the column must be
different).
REFERENTIAL INTEGRITY CONSTRAINTS
Referential Integrity Constraint is defined as it ensures that there always exists a valid
relationship between two tables.

KEY CONSTRAINTS
Keys are the set of entities that are used to identify an entity within its entity set
uniquely. A primary key can only contain unique and not null values in the relational
database table.

14
QUERIES & OUTPUT

// NOT NULL & PRIMARY KEY CONSTRAINT

create table employee(empid int primary key,age int not null,ename char(10),salary int,city
char(10));
insert into employee values(101,25,'anki',21000,'chennai');
insert into employee values(102,24,'malar',15000,'salem');
insert into employee values(103,26,'sara',70000,'ooty');
insert into employee values(104,24,'danush',50000,'salem');
select * from employee;

insert into employee values(102,25,'danya',30000,'thrichy');

ERROR: duplicate key value violates unique constraint "employee_pkey" DETAIL: Key
(empid)=(102) already exists. SQL state: 23505

// UNIQUE CONSTRINT
alter table employee add unique(ename);
insert into employee values(107,24,'sara',85000,'Bangalore');
ERROR: duplicate key value violates unique constraint "employee_ename_key" DETAIL: Key
(ename)=(sara ) already exists. SQL state: 23505

//CHECK CONSTRAINT
alter table employee add check (age>25);

ERROR: check constraint "employee_age_check" of relation "employee" is violated by some

row SQL state: 23514

//REFERNTIAL INTEGRITY
insert into person values(22445566,102);
insert into person values(12332145,103);
insert into person values(42536415,104);
select * from person;

insert into person values(24551242,107);

ERROR: insert or update on table "person" violates foreign key constraint
"person_empid_fkey" DETAIL: Key (empid)=(107) is not present in table "employee". SQL
state: 23503

15
delete from person where aadhar=12332145;

select * from person; select * from employee;

delete from employee where empid=102;

ERROR: update or delete on table "employee" violates foreign key constraint
"person_empid_fkey" on table "person" DETAIL: Key (empid)=(102) is still referenced from
table "person". SQL state: 23503

create table persn(aadhar int primary key,empid int, foreign key(empid) references
employee(empid)on delete cascade on update cascade);
insert into persn values(22445566,102);
insert into persn values(12332145,103);
insert into persn values(42536415,104);

select * from persn;

delete from employee where empid=102;

select * from employee; select * from persn;

update employee set empid=204 where empid=104;

select * from employee; select * from persn;

RESULT
Thus the types of constraints were studied and the commands were implemented in
database successfully.

16
Ex.No.:3 C)
IMPLEMENT THE DATABASE WITH VIEWS
Date:

AIM
To implement the concept of views in PostgreSQL.

VIEWS

Views allow the user to create a virtual table based on an SQL query referring to other tables
in the database. A view stores an SQL query that is executed whenever user refer to the view.
The view has primarily two purposes:
 Simplify the complex SQL queries.
 Provide restriction to users from accessing sensitive data.

CREATING VIEWS

Views can be created using CREATE VIEW statement. A View can be created from a single
table or multiple tables.
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;

CREATING VIEWS FROM MULTIPLE TABLES

A View can be created from a single table or multiple tables.
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name1, table_name2
WHERE condition;

INSERTING A ROW IN A VIEW

A row can be inserted into a view like inserting into a table.
Syntax:
INSERT into VIEW_NAME values(attributename1,attribute2…);

DELETING A ROW FROM A VIEW

The rows in a View can be deleted using delete statement.
Syntax:
DELETE FROM view_name WHERE condition;

DROPPING VIEWS
If the view is no longer needed it can be dropped using the DROP statement.
Syntax:
DROP VIEW view_name;

UPDATING VIEWS
The CREATE OR REPLACE VIEW statement is used to add or remove fields from a
view. There could be a situation to create a view or replace it if it already exists.

17
Syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT column1,column2,..
FROM table_name
WHERE condition;

OUTPUT
create table sdetails(regno int,name char(15),marks int,dept char(10));

insert into sdetails values(1001,'adharsh',89,'cse');

insert into sdetails values(1002,'bala',91,'ai');
insert into sdetails values(1003,'chithra',77,'ece');
insert into sdetails values(1004,'shiva',86,'ai');
select * from sdetails;

create view markview as select regno,marks from sdetails where marks>85;

Select * from markview;

insert into markview values(1005,90);

Select * from markview; select * from sdetails;

update sdetails set name='tina',dept='ai' where regno=1005;

select * from sdetails;

create view deptview as select dept,count(*) from sdetails group by dept;

Select * from deptview;

18
create table pers(regno int,age int,address char(20));

insert into pers values(1001,19,'chennai');

insert into pers values(1002,20,'madurai');
insert into pers values(1003,21,'chennai');
insert into pers values(1007,20,'salem');

select * from pers;

CREATE VIEW spview AS SELECT s.regno,p.address FROM sdetails s JOIN pers p ON

s.regno = p.regno;
(or)
CREATE VIEW spview AS SELECT sdetails.regno,pers.address FROM sdetails,pers where
sdetails.regno=pers.regno;

select * from spview;

CREATE or replace VIEW ageview AS SELECT

sdetails.regno,sdetails.name,pers.age,pers.address FROM sdetails,pers where
sdetails.regno=pers.regno and pers.age>=20;

RESULT
Thus the usage of views was implemented successfully.

19
Ex.No.:4 A)
IMPLEMENTATION OF SUB QUERIES
Date:

AIM
To implement various Sub queries in SQL.
SUB QUERY
In PostgreSQL, a subquery (also known as a nested query or inner query) is a query nested
inside another query. Subqueries are enclosed in parentheses and can be used in various parts
of a SQL statement, such as the SELECT, FROM, WHERE, and HAVING clauses.
The common types of subqueries in PostgreSQL are :

AS
 An alias is created with the AS keyword.
 SQL aliases are used to give a table, or a column in a table, a temporary name.
Syntax
SELECT column1, column2 FROM tablename AS aliasname;
Example
SELECT MAX(salary) AS maxsalary FROM employees
IN
The IN operator specify a list of possible values in the WHERE clause.
Syntax
WHERE IN (subquery)
Example
SELECT * FROM customers WHERE country IN ('Germany', 'France', 'UK');

ANY
 The subquery must return exactly one column.
 The ANY operator must be preceded by one of the following comparison
operator =, <=, >, <, > and <>.
 The ANY operator is used to compare a value to any value in a set of values.
Syntax
expression operator ANY (subquery)
Example
SELECT * FROM products WHERE price > ANY (SELECT price FROM
discount_products);

ALL
The ALL operator is used to compare a value to all values in a set of values.
Syntax
expression operator ALL (subquery)
Example
SELECT * FROM products WHERE price > ALL (SELECT price FROM
expensive_products);

20
EXISTS
The EXISTS operator is used to check the existence of rows in a subquery result.
Syntax

WHERE EXISTS (subquery)

Example
SELECT * FROM customers WHERE EXISTS (SELECT 1 FROM orders
WHERE orders.customer_id = customers.customer_id);
OUTPUT
create table product(pid int, price int);
insert into product values(1,200);
insert into product values(2,100);
insert into product values(3,300);
insert into product values(4,50);
select * from product;

pid price
1 200
2 100
3 300
4 50

create table discountprod(pid int,price int);

insert into discountprod values(1,180);
insert into discountprod values(2,80);
insert into discountprod values(4,25);
pid price
1 180
2 80
4 25
SELECT * FROM product WHERE price < All (SELECT price FROM discountprod);
No value
SELECT * FROM product WHERE price > All (SELECT price FROM discountprod);

pid price
1 200
3 300
SELECT * FROM product WHERE price < Any (SELECT price FROM discountprod);

pid price
2 100
4 50

21
SELECT * FROM product WHERE EXISTS (SELECT * FROM discountprod WHERE
product.pid = discountprod.pid);

pid price
1 200
2 100
4 50

SELECT * FROM product WHERE pid IN ( SELECT pid FROM discountprod WHERE
price = 80);

pid price
2 100

SELECT pid, (SELECT MAX(price) FROM product) AS maxprice FROM product;

pid maxprice
1 300
2 300
3 300
4 300

SELECT MAX(price) FROM product AS MAXPRICE;

max
300

RESULT
Thus the SQL sub query commands were implemented and the output was verified
successfully.

22
Ex.No.:4 B)
SELECT COMMANDS AND AGGREGATE FUNCTIONS IN SQL
Date:

AIM
To study and execute various select commands using SQL
TYPES OF SELECT COMMAND
GROUP BY

The SQL GROUP BY clause is used to arrange identical data into groups with the
help of some aggregate functions (COUNT, MAX, MIN, SUM, AVG) .
ORDERBY
The ORDER BY clause in SQL is used to sort the result set of a query based on one or more
columns. It can be used with both numerical and textual data, and sorted in ascending (asc)
or descending (desc) order.

HAVING

The HAVING clause in SQL is used in conjunction with the GROUP BY clause to filter the
results of a query based on aggregate conditions.
BETWEEN

The BETWEEN operator in SQL is used to filter the result set based on a specified range of
values. It is often used in the WHERE clause of a query.
SET OPERATORS
UNION

 The UNION operator in SQL is used to combine the result sets of two or more
SELECT statements into a single result set.
 It removes duplicate rows from the combined result set.
UNION ALL

 The UNION ALL operator in SQL is similar to the UNION operator but includes
all rows in the result set, including duplicate rows.
INTERSEC

 The INTERSECT operator in SQL is used to combine the result sets of two
SELECT statements and retrieve only the rows that are common to both result sets.

MINUS

 The MINUS operator in SQL is used to combine the result sets of two SELECT
statements.
 It performs set difference operation.

23
AGGREGATE FUNCTIONS

AVG

The AVG aggregate function in SQL is used to calculate the average value of a
numeric column in a result set.

COUNT

The COUNT aggregate function in SQL is used to count the number of rows in a
result set.
MAX

The MAX function is a useful aggregate function for obtaining the maximum value in
a set of data.
MIN
The MIN function is a useful aggregate function for obtaining the minimum value in
a set of data.
SUM

The SUM aggregate function in SQL is used to calculate the total sum of values in a
specified column.
OUTPUT
create table stud(rollno int,name char(20),dbms int,maths int,algorithm int,dept
char(10),address char(15));
insert into stud values(123,'priya',78,80,80,'cse','chennai');
insert into stud values(124,'uma',63,68,70,'cse','madurai');
insert into stud values(125,'ganesh',90,95,75,'ai','ooty');
insert into stud values(126,'varun',94,87,77,'ece','chennai');

select * from stud;

rollno name dbms maths algorithm dept address

123 priya 78 80 80 cse chennai
124 uma 63 68 70 cse madurai
125 ganesh 90 95 75 ai ooty
126 varun 94 87 77 ece chennai

select dept from student group by dept;

dept
cse
ai
ece

24
select dept from stud;

dept
cse
cse
ai
ece

select count(rollno),dept from stud group by dept;

count dept
2 cse
1 ai
1 ece
select name from stud order by (name)desc;

name
varun
uma
priya
ganesh

select name from stud where maths between 80 and 90;;

name
priya
varun

select count(dbms),dept from stud group by dept having count(dbms)>=2;

count dept
2 cse

create table person(rollno int,name char(20),dbms int,maths int,algorithm int,dept

char(10),address char(15));
insert into person values(125,'ganesh',90,95,75,'ai','ooty');
insert into person values(124,'uma',63,68,70,'cse','madurai');
insert into person values(131,'zara',56,61,55,'ece','madurai');
insert into person values(132,'jerin',52,85,61,'eee','salem');
select * from person;

rollno name dbms maths algorithm dept address

125 ganesh 90 95 75 ai ooty
124 uma 63 68 70 cse madurai
131 zara 56 61 55 ece madurai
132 jerin 52 85 61 eee salem

25
select * from stud union select * from person;

rollno name dbms maths algorithm dept address

126 varun 94 87 77 ece chennai
131 zara 56 61 55 ece madurai
125 ganesh 90 95 75 ai ooty
123 priya 78 80 80 cse chennai
132 jerin 52 85 61 eee salem
124 uma 63 68 70 cse madurai
select * from stud union all select * from person;

rollno name dbms maths algorithm dept address

123 priya 78 80 80 cse chennai
124 uma 63 68 70 cse madurai
125 ganesh 90 95 75 ai ooty
126 varun 94 87 77 ece chennai
125 ganesh 90 95 75 ai ooty
124 uma 63 68 70 cse madurai
131 zara 56 61 55 ece madurai
132 jerin 52 85 61 eee salem

select * from stud intersect select * from person;

rollno name dbms maths algorithm dept address

124 uma 63 68 70 cse madurai
125 ganesh 90 95 75 ai ooty
select * from stud except select * from person;

rollno name dbms maths algorithm dept address

126 varun 94 87 77 ece chennai
123 priya 78 80 80 cse chennai

select name from stud where name in (select name from person where dept='ai');

name
ganesh

RESULT
Thus various select command and aggregate functions are executed in SQL
successfully.

26
Ex.No.:5 A)
PROGRAMS USING PROCEDURES
Date:

AIM
To Querying/Managing the database using SQL Programming -Procedures and
Functions
PL/pgSQL
 PL/pgSQL is a procedural programming language for the PostgreSQL dbms system.
 PL/pgSQL allows you to extend the functionality of the PostgreSQL dbms server by
creating server objects with complex logic.
 PL/pgSQL was designed to :
 Create user-defined functions, stored procedures, and triggers.
 Extend standard SQL by adding control structures such as if, case,
and loop statements.
 Inherit all user-defined functions, operators, and types.

PROCEDURES
PostgreSQL 11 introduced stored procedures that support transactions.
The following illustrates the basic syntax of the create procedure statement:

create [or replace] procedure procedure_name(parameter_list)

language plpgsql
as $$
declare
-- variable declaration
begin
-- stored procedure body
end; $$

SAMPLE PROGRAM TO PRINT ‘ HAI’

DO $$
BEGIN
-- Use RAISE NOTICE to print the statement
RAISE NOTICE 'hai';
END $$;

Output:
NOTICE: hai
DO
Query returned successfully in 37 msec.

27
PL/PGSQL TO SWAP TWO NUMBERS

DO $$
DECLARE
num1 int;
num2 int;
temp int;
BEGIN
num1 := 1000;
num2 := 2000;
RAISE NOTICE 'Before swapping';
RAISE NOTICE 'num1=%, num2=%', num1, num2;
temp := num1;
num1 := num2;
num2 := temp;
RAISE NOTICE 'After swapping';
RAISE NOTICE 'num1=%, num2=%', num1, num2;
END $$;

Output:
NOTICE: Before swapping
NOTICE: num1=1000, num2=2000
NOTICE: After swapping
NOTICE: num1=2000, num2=1000
DO

SAMPLE PROGRAM USING PROCEDURES

CREATE OR REPLACE PROCEDURE proc()
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Hello from my stored procedure!';
END;
$$;

Call proc();

OUTPUT:
NOTICE: Hello from my stored procedure! CALL Query returned
successfully in 41 msec.

28
PROGRAM USING PROCEDURES TO PERFORM TRANSACTION

create table accounts ( id int, name varchar(100) not null, balance int not null, primary
key(id));
insert into accounts values(1,'Raju', 5000);
insert into accounts values(2,'Nikhil', 10000);

select * from accounts;

id name balance
1 Raju 5000
2 Nikhil 10000

create or replace procedure transfer(sender int, receiver int, amount int)

language plpgsql
as $$
begin
-- subtracting the amount from the sender's account
update accounts
set balance = balance - amount
where id = sender;

-- adding the amount to the receiver's account

update accounts
set balance = balance + amount
where id = receiver;
commit;
end;$$
call transfer(1,2,1000);
select * from accounts;

id name balance
1 Raju 4000
2 Nikhil 11000

RESULT:
Thus the concept of procedures and functions are executed successfully.

29
Ex.No.:5 B)
PROGRAMS USING FUNCTIONS
Date:

AIM
To execute program using functions in postgreSQL.

FUNCTIONS
PostgreSQL uses the CREATE FUNCTION statement to develop user-defined functions.

Syntax:
CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype
LANGUAGE plpgsql
AS $variable_name$
DECLARE
declaration;
[...] -- variable declaration
BEGIN
< function_body >
[...] -- logic
RETURN { variable_name | value }
END;
$$

Example:

CREATE FUNCTION inc(val integer) RETURNS integer AS $$

BEGIN
RETURN val + 1;
END; $$
LANGUAGE PLPGSQL;

select inc(20);

OUTPUT:

inc
21

PROGRAM USING FUNCTIONS

create table car ( id int, price int);

insert into car values(1,50000);

insert into car values(3,82000);

30
insert into car values(2,70000);
select * from car;

Create function get_car_Price(Price_from int, Price_to int)

returns int
language plpgsql
as
$$
Declare
Car_count integer;
Begin
select count(*)
into Car_count
from Car
where price between Price_from and Price_to;
return Car_count;
End;
$$;
Select get_car_Price(25000,75000);

OUTPUT:

PL/PGSQL FUNCTION TO FIND SQUARE OF A NUMBER

CREATE OR REPLACE FUNCTION square(n int)
RETURNS int AS $$
BEGIN
RETURN n * n;
END;
$$ LANGUAGE plpgsql;

//call the function

select square(5);

OUTPUT:

31
PL/PGSQL FUNCTION TO CALCULATE THE FACTORIAL OF A NUMBER:

CREATE OR REPLACE FUNCTION factorial(n int)

RETURNS int AS $$
DECLARE
result int = 1;
i int;
BEGIN
IF n < 0 THEN
RAISE EXCEPTION 'Invalid input. Factorial is not defined for negative numbers.';
END IF;
FOR i IN 1..n LOOP
result := result * i;
END LOOP;
RETURN result;
END;
$$ LANGUAGE plpgsql;

// call the function

SELECT factorial(5);

OUTPUT:

PL/PGSQL FUNCTION TO LARGEST AMONG THREE NUMBERS

CREATE OR REPLACE FUNCTION great(a int, b int, c int)

RETURNS int AS $$
BEGIN
IF a >= b AND a >= c THEN
RETURN a;
ELSIF b >= a AND b >= c THEN
RETURN b;
ELSE
RETURN c;
END IF;
END;
$$ LANGUAGE plpgsql;

SELECT great(5, 20, 15);

OUTPUT:

32
PL/PGSQL FUNCTION TO ACCEPT A NUMBER A PRINT THE SUM OF ITS DIGIT

CREATE OR REPLACE FUNCTION sum_of_digits(n int)

RETURNS int AS $$
DECLARE
sum int = 0;
BEGIN
WHILE n > 0 LOOP
sum := sum + n % 10;
n := n / 10;
END LOOP;

RETURN sum;
END;
$$ LANGUAGE plpgsql;
// call the function

SELECT sum_of_digits(12345);
OUTPUT:

PL/PGSQL FUNCTION TO DISPLAY A NUMBER IN REVERSE ORDER

CREATE OR REPLACE FUNCTION reverse_number(n int)
RETURNS int AS $$
DECLARE
rev int = 0;
BEGIN
WHILE n > 0 LOOP
rev := rev * 10 + n % 10;
n := n / 10;
END LOOP;

RETURN rev;
END;
$$ LANGUAGE plpgsql;

SELECT reverse_number(123);

OUTPUT:

RESULT
Thus the usage of functions was studied and the programs were executed successfully.

33
Ex.No.:6
CONSTRAINTS AND SECURITY USING TRIGGERS
Date:

AIM
To querying the database using SQL Programming – Constraints and security using
Trigger.

TRIGGERS
A trigger is a stored procedure in a database that automatically invokes whenever a special
event in the database occurs. For example, a trigger can be invoked when a row is inserted
into a specified table or when specific table columns are updated.

Syntax:
create trigger [trigger_name]
[before | after]
{insert | update | delete}
on [table_name]
[for each row]
[trigger_body]

Explanation of Syntax
1. Create trigger [trigger_name]: Creates or replaces an existing trigger with the
trigger_name.
2. [before | after]: This specifies when the trigger will be executed.
3. {insert | update | delete}: This specifies the DML operation.
4. On [table_name]: This specifies the name of the table associated with the trigger.
5. [for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for
each affected row.
6. [trigger_body]: This provides the operation to be performed as the trigger is fired

PROGRAM

CREATE TABLE employee(ID int,first_name varchar(40) not null,last_name varchar(40)

not null,primary key(ID));
INSERT into employee VALUES(101,'John','Doe');
INSERT into employee VALUES(102,'Lily','Bush');

select * from employee;

CREATE TABLE employee_audit(ID int,employee_id int not null,last_name

varchar(40)not null,changed_on timestamp(6) not null);

34
SELECT * FROM employee_audit;

CREATE OR REPLACE FUNCTION log_last_name_changes()

returns trigger
language PLPGSQL
as
$$
BEGIN
if NEW.last_name<>OLD.last_name then INSERT into
employee_audit(employee_id,last_name,changed_on)values(OLD.ID,OLD.last_name,NOW(
));
END if;
return new;
END;
$$

CREATE trigger last_name_changes

before update
on employee
for each row
execute procedure log_last_name_changes();

SELECT * FROM employee;

UPDATE employee set last_name='Mathew' WHERE ID=102;

RESULT
Thus the trigger was invoked and executed successfully.

35
Ex.No.:7
DATABASE DESIGN USING NORMALIZATION
Date:

AIM:
To create a database with Normal forms.

NORMALZATION
 Normalization is a database design technique which organizes tables in a
manner that reduces redundancy and dependency of data.
 It divides larger tables to smaller tables and links them using relationships.

The inventor of the relational model Edgar Codd proposed the theory of normalization
with the introduction of First Normal Form, and he continued to extend theory with
Second and Third Normal Form. Later he joined with Raymond F. Boyce to develop
the theory of Boyce-Codd Normal Form.
Theory of Data Normalization in SQL is still being developed further. For example,
there are discussions even on 6thNormal Form. However, in most practical
applications, normalization achieves its best in 3rd Normal Form. The evolution of
Normalization theories is illustrated below-

Database Normalization Examples -

Assume a video library maintains a database of movies rented out. Without any
normalization, all information is stored in one table as shown below.

Table 1

Here Movies Rented column has multiple values.Database Normal Forms

Now let's move into 1st Normal Forms

1NF (First Normal Form) Rules

 Each table cell should contain a single value.
 Each record needs to be unique.
The above table in 1NF-

36
1NF Example

Table 1: In 1NF Form

What is a KEY?
A KEY is a value used to identify a record in a table uniquely. A KEY could be a
single column orcombination of multiple columns

Note: Columns in a table that are NOT used to identify a record uniquely are called

non-key columns. What is a Primary Key?

A primary is a single column value used to identify a

database recorduniquely.

It has following attributes:

 A primary key cannot be NULL

 A primary key value must be unique
 The primary key values cannot be changed
 The primary key must be given a value when a new
record is inserted.

What is Composite Key?

A composite key is a primary key composed of multiple columns used to identify a
record uniquely
In our database, we have two people with the same name Robert Phil, but they
live in different places.

Hence, both Full Name and Address a r e r e q u ir e d to identify a record uniquely.

That is a compositekey.

37
 Let's move into second normal form 2NF

2NF (Second Normal Form) Rules

 Rule 1- Be in 1NF
 Rule 2- Single Column Primary Key

It is clear that we can't move forward to make our simple database in 2nd
Normalization form unlesswe partition the table above.

Table 1

Table 2

We have divided our 1NF table into two tables viz. Table 1 and Table2. Table 1
contains member information. Table 2 contains information on movies rented.

We have introduced a new column called Membership_id which is the primary

key for table 1. Records can be uniquely identified in Table 1 using membership id.
Database - Foreign Key

In Table 2, Membership_ID is the Foreign Key

Foreign Key references the primary key of

another Table! Ithelps connect your Tables
 A foreign key can have a different
name from it s p r i ma r y k e y.
 It ensures rows in one table have
corresponding rows in another table.
 Unlike the Primary key, they do not
have to be unique.
 Foreign keys can be null even though
primary keys cannot

38
 Why do we need a foreign key?
Suppose an idiot inserts a record in Table B such as
We will only be able to insert values into your foreign key that exist in the unique
key in the
parenttable. This helps in referential integrity.

The above problem can be overcome by declaring membership id from Table2 as

foreign key ofmembership id from Table1

Now, if somebody tries to insert a value in the membership id field that does not
exist in the parenttable, an error will be shown!

What are transitive functional dependencies?

A transitive functional dependency is when changing a non-key column, might cause
any of the othernon-key columns to change
Consider the table 1. Changing the non-key column Full Name may change Salutation.

39
 Let's move into 3NF
3NF (Third Normal Form) Rules
 Rule 1- Be in 2NF
 Rule 2- Has no transitive functional dependencies

To move our 2NF table into 3NF, we again need to again divide our table.

3NF Example

Table 1

Table 2

Table 3

We have again divided our tables and created a new table which stores Salutations.
There are no transitive functional dependencies, and hence our table is in 3NF

In Table 3 Salutation ID is primary key, and in Table 1 Salutation ID is foreign to

primary key inTable 3

Now our little example is at a level that cannot further be decomposed to attain higher
forms of normalization. In fact, it is already in higher normalization forms. Separate
efforts for moving into next levels of normalizing data are normally needed in

40
 complex databases. However, we will be discussing next levels of normalizations in
brief in the following.

Boyce-Codd Normal Form (BCNF)

Even when a database is in 3rd Normal Form, still there would be anomalies resulted if
it has more than one Candidate Key.
BCNF is also referred as 3.5 Normal Form.

4NF (Fourth Normal Form) Rules

If no database table instance contains two or more, independent and multivalued data
describing therelevant entity, then it is in 4th Normal Form.

5NF (Fifth Normal Form) Rules

A table is in 5th Normal Form only if it is in 4NF and it cannot be decomposed into any
number of smaller tables without loss of data.

RESULT
Thus the normalization concept was studied and the database was created using
normalization.

41
Ex.No.:8 DEVELOPING A DATABASE APPLICATIONS USING
Date: VISUALSTUDIO

AIM
To develop a database application for students using VisualStudio
PROCEDURE
1. In the start menu go to all apps and select Microsoft office->Ms Access
2. Select new document .create a table with name mydata . Choose design view and
create three fields name, address and age.
3. Enter the data for the created fields by double clicking the table mydata. Save the
document as mydata and close the document.

4. In the start menu go to Microsoft Visual studio 2022.click create a new project->WPF
framework(.Netframework)->next->create a new project.
5. Select and pin toolbox and datasource at the leftside of the window.From the toolbox
select 3 labels and 3 textboxes, place it in the form and change the label name from
its properties.

6. Add six buttons in the form and change its name.

7. Choose the datasource ->database->dataset->set a connection to Microsoft access

database file.
8. Select the saved database file name and click ok in the dialog box.

42
 9. Choose the database objects and click finish.

10. Place the datagridview in the form and from choose data source->mydata
11. Select each textbox and from its properties choose data bindings->tag->select
appropriate field.
12. Write the code for each buttons.
13. Run the project by pressing CTRL+F5.

VISUAL BASIC CODE

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

MydataBindingSource.MoveNext()
End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

MydataBindingSource.MovePrevious()
End Sub

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click

MydataBindingSource.AddNew()
End Sub

Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click

MydataBindingSource.EndEdit()
MydataTableAdapter.Update(MydataDataSet.mydata)
MsgBox("saved")
End Sub

Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click

MydataBindingSource.RemoveCurrent()
End Sub

Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click

Me.Close()
End Sub

43
OUTPUT

RESULT
Thus the application to develop student Management System using VisualStudio is
successfully implemented.

44
Ex.No.:9 DATABASE DESIGN USING EER-TO-ODB MAPPING / UML CLASS
Date: DIAGRAMS

AIM
To design a database using EER-to-ODB mapping and create UML class diagrams for
the object-oriented database model.

ALGORITHM
1. Develop EER or UML diagrams representing system entities, attributes, and
relationships.
2. Extract entities and attributes for tables, considering each entity as a table and
attributes as columns.
3. Translate relationships into foreign keys in corresponding tables, considering
cardinalities and participation constraints.
4. Decide on a mapping strategy for inheritance (table-per-class or table-per-hierarchy)
for generalization/specialization.
5. Map aggregations to foreign keys or separate tables.
6. Assign appropriate data types to attributes based on requirements and the DBMS.
7. Apply normalization to eliminate redundancy and anomalies.
8. Establish primary keys, foreign keys, unique constraints, and other integrity
constraints.
9. Create a SQL script to implement the database schema, relationships, and constraints.
10. Execute the SQL script to create the database, test and optimize the schema for
performance and functionality, and document the structure

DETAILS OF THE CLASSES

1. Library management systemAttributes:
Usertype
Username
PasswordOperations:
Login()
Register()
Logout()
2.User Attributes:
Name
Id Operations: Verify()
CheckAccount()
get_book_info()
3. staff Attributes:
Dept

45
4. Student Attributes:
Class
5. AccountAttributes:
no_borrowed_books no_reserved_books no_returned_books
no_lost_books fine_amountOperations: Calculate_fine()
6. Book Attributes:
Title
Author
ISBN
publication Operations: Show_duedt()
Reservation_status()
Feedback()
Book_request()
Renew_info()
7. librarianAttributes:
Name
Id
Password
SearchString Operations: Verify_librarian()
Search()
8. Library database
Attributes:
List_of_books Operations:
Add()
Delete()
Update()
Display()
Search()

46
UML DIAGRAM

RESULT

Thus the implementation on database design using eer-to-odb mapping / uml class
diagrams is performed successfully.

47
Ex.No.:10 A)
PROGRAM USING UDTs AND INHERITANCE
Date:

AIM:
To implement the object features of SQL using UDTs and Inheritance.
ALGORITHM:
1. Create a type called as addresstype to hold street and city.
2. Create a table employ consisting of emp_id,name,salary and address.
3. Create a table manager which inherits employ table and insert values into this table.
4. Create a table developer which inherits employ and insert values into this table.
5. Display the above three tables, now the base table is inherited by the child tables and the
result will be shown.

PROGRAM

// Create the base UDT representing an addresstype

CREATE TYPE addresstype AS (street VARCHAR, city VARCHAR );
// Create a table for employee using the addresstype UDT
CREATE TABLE employ(emp_id SERIAL PRIMARY KEY,name VARCHAR,
salary DECIMAL, address addresstype);
// Create a table for manager using the inheritance from employ table

CREATE TABLE manager (bonus DECIMAL) INHERITS (employ);

drop table developer;
// Create a table for developer using the inheritance from employ table
CREATE TABLE developer ( programming_language VARCHAR) INHERITS (employ);
// Insert some data into the tables
INSERT INTO manager (name, salary, address,bonus) VALUES ('Alice', 80000, ROW('123
Main St','chennai'),5000 );
INSERT INTO developer (name, salary, address,programming_language) VALUES ('Bob',
70000,ROW('11 West St','ooty'), 'Python' );
INSERT INTO developer (name, salary, address,programming_language) VALUES ('Bala',
65000,ROW('car street','madurai'), 'JAVA' );
// Query data from the tables

select * from manager;

emp_id name salary address bonus

1 Alice 80000 ("123 Main St",chennai) 5000

48
select * from developer;

emp_id name salary address programming_language

2 Bob 70000 ("11 West St",ooty) Python
3 Bala 65000 ("car street",madurai) JAVA

select * from employ;

emp_id name salary address

1 Alice 80000 ("123 Main St",chennai)
2 Bob 70000 ("11 West St",ooty)
3 Bala 65000 ("car street",madurai)

RESULT
Thus the object features of SQL like UDT and inheritance was implemented
successfully.

49
Ex.No.:10 B)
PROGRAM USING UDTs AND SUBTYPES
Date:

AIM
To implement the object features of SQL like UDTs and Subtypes.
SUBTYPE IN POSTGRESQL
In PostgreSQL, subtypes are implemented using the CREATE TYPE statement. Subtypes
allow to create a new data type that is based on an existing data type and inherits its
properties.
ALGORITHM

1. Create a type called as person_name to hold first_name and last_name.

2. Create a type called as employee_name to hold employee_id and full_name.Full_name
inherits the properties of person_name.
3. Create a table employees which holds emp_id and name. The datatype for name attribute is
employee_name, which is inherited.
4. Insert rows into the table employees.
5. Display the table, employees.
PROGRAM
-- Create the base type
CREATE TYPE person_name AS (first_name VARCHAR,last_name VARCHAR);
-- Create the subtype (employee_name) based on the base type (person_name)
CREATE TYPE employee_name AS (salutation varchar,full_name person_name);

-- Create a table using the subtype

CREATE TABLE employees (emp_id SERIAL PRIMARY KEY, name employee_name);
-- Insert some data
INSERT INTO employees VALUES (1, ROW('Mrs', ROW('Uma', 'Rani')));
INSERT INTO employees VALUES (2, ROW('Mr', ROW('Hanu', 'Saran')));
INSERT INTO employees VALUES (3, ROW('Ms', ROW('Riya', 'Gaur')));
-- Query the data
SELECT * FROM employees;

RESULT
Thus the subtype concept under object features of SQL was implemented
successfully.

50
Ex.No.:10 C)
OBJECT FEATURES OF SQL-METHOD DEFINITION
Date:

AIM
To calculate the length of books title using method definition.

ALGORITHM

1. Create a table named books with columns book_id, title, author, and publication_year.
2. Define a method named calculate_title_length that takes a book title as an argument
and returns the length of the title using the LENGTH function.
3. Insert some sample data into the books table.
4. Use the calculate_title_length method in a query to find the title length for each book.

PROGRAM
-- Create a table
CREATE TABLE books (book_id SERIAL PRIMARY KEY, title VARCHAR, author
VARCHAR, publication_year INT);
-- Create a method to calculate the number of characters in the title of a book
CREATE OR REPLACE FUNCTION calculate_title_length(book_title VARCHAR)
RETURNS INT AS $$
BEGIN
RETURN LENGTH(book_title);
END;
$$ LANGUAGE plpgsql;

-- Insert some data into the table

INSERT INTO books (title, author, publication_year) VALUES
('The Catcher in the Rye', 'J.D. Salinger', 1951),
('To Kill a Mockingbird', 'Harper Lee', 1960),
('1984', 'George Orwell', 1949);

-- Use the method to calculate the title length for a specific book

SELECT title, calculate_title_length(title) AS title_length FROM books;

RESULT
Thus the length of given books title was calculated using method definition and
displayed successfully.

51
Ex.No.:11 QUERYING THE OBJECT-RELATIONAL DATABASE USING
Date: OBJET QUERY LANGUAGE

AIM
To query the object relational database using object query language.
OBJECT-RELATIONAL DATABASE

 Object-relational databases aim to bridge the gap between relational databases and
object-oriented databases, incorporating concepts from both paradigms.
 They allow the users to define custom data types, store complex data structures, and
use functions to manipulate the data, providing more flexibility than traditional
relational databases.

Popular Object-Relational Database Management Systems include:

 Oracle Database: Oracle supports object-relational features and has a long history of
providing support for complex data types.

 PostgreSQL: PostgreSQL is an open-source database that supports a wide range of

data types, including arrays and user-defined types.

 IBM Db2: Db2 is known for its support for complex data types and object-relational
features.
ALGORITHM & QUERIES

1. Table Creation:
 The Authors table is created with columns AuthorID (primary key),
AuthorName (name of the author), and Books (an array of book titles).
CREATE TABLE Authors ( AuthorID SERIAL PRIMARY KEY, AuthorName
VARCHAR(100), Books VARCHAR(200)[ ]);

2. Data Insertion:
 Sample data is inserted into the Authors table. Authors are associated with an
array of books they have written.
INSERT INTO Authors (AuthorName, Books) VALUES
('Jenith joel', ARRAY['Book1', 'Book2']),
('Reena dev', ARRAY['Book3', 'Book4', 'Book5']),
('Alice mary', ARRAY['Book6']);

3. Query to Retrieve Authors and the Number of Books:

 The first SELECT statement retrieves AuthorID, AuthorName, and the
number of books written by each author using the CARDINALITY function
to get the array length.

52
 SELECT a.AuthorID, a.AuthorName, CARDINALITY(Books) AS NumberofBooks
FROM Authors a;

4. Query to Retrieve All Authors and Their Books:

 The second SELECT statement retrieves all columns from the Authors table,
showing the authors and the arrays of books they have written.

SELECT aut.AuthorID, aut.AuthorName, aut.Books FROM Authors aut;

5. Query to Retrieve Authors with More Than 1 Book:

 The third SELECT statement filters the authors who have written more than
one book, using the WHERE clause with the CARDINALITY function.
SELECT aut.AuthorID, aut.AuthorName, aut.Books FROM Authors aut WHERE
CARDINALITY(Books) > 1;

RESULT
Thus the usage of arrays, creation of user-defined types and the inclusion of functions
which are features of object-relational databases are implemented and executed.

53
EX.No. 1 IMPLEMENT BASIC SEARCH STRATEGIES 8-PUZZLE PROBLEM
DATE:

AIM

To implement basic search strategies – 8-Puzzle Problem.

ALGORITHM

1. The code starts by creating a Solution class and then defining the method solve.
2. The function takes in a board as an argument, which is a list of tuples representing the
positions on the board.
3. It iterates through each position in the list and creates a dictionary with that position's value
set to 0.
4. Then it iterates through all possible moves for that position and returns their number of
occurrences in dict.
5. After this, it loops over all nodes on the board until it finds one where there are no more
moves left to make (i.e., when len(current_nodes) == 0).
6. This node will be returned as -1 if found or else its index will be stored into pos_0 so that we
can find out what move was made at that point later on.
7. The next step is finding out what move was made at every node by looping over all possible
moves for each node using self's find_next function, which takes in a single node as an
argument and returns any other nodes connected to it via path-finding algorithms like DFS or
BFS (see below).
8. For example, if pos_0 = 1 then self would call: moves = { 0: [1], 1:
9. The code will print the number of paths in a solution.

PROGRAM

class Solution:
def solve(self, board):
state_dict = {} # Better name than 'dict' (which shadows the built-in dict type)
flatten = []

# Flatten the board into a 1D list and convert to tuple for immutability
for i in range(len(board)):
flatten += board[i]
flatten = tuple(flatten)

# Initialize the dictionary with the starting state

state_dict[flatten] = 0

# Check if the board is already in the goal state

if flatten == (0, 1, 2, 3, 4, 5, 6, 7, 8):
return 0

# Start the BFS search

 return self.get_paths(state_dict)

def get_paths(self, state_dict):

cnt = 0

while True:
# Get all the nodes at the current depth level (cnt)
current_nodes = [x for x in state_dict if state_dict[x] == cnt]

# If no nodes are left to process, we return -1 (no solution)

if len(current_nodes) == 0:
return -1

# Explore all possible moves from the current nodes

for node in current_nodes:
next_moves = self.find_next(node)

for move in next_moves:

if move not in state_dict:
# Mark this state as visited and assign the new depth level
state_dict[move] = cnt + 1

# If the move is the goal state, return the depth

if move == (0, 1, 2, 3, 4, 5, 6, 7, 8):
return cnt + 1

# Move to the next depth level

cnt += 1

def find_next(self, node):

# Define the possible moves for each index (where 0 represents the blank tile)
moves = {
0: [1, 3],
1: [0, 2, 4],
2: [1, 5],
3: [0, 4, 6],
4: [1, 3, 5, 7],
5: [2, 4, 8],
6: [3, 7],
7: [4, 6, 8],
8: [5, 7]
}

results = []
pos_0 = node.index(0) # Find the position of the blank (0)

# Generate possible next moves by swapping the blank with its valid neighbors
for move in moves[pos_0]:
new_node = list(node)
new_node[move], new_node[pos_0] = new_node[pos_0], new_node[move]
results.append(tuple(new_node)) # Convert back to tuple for immutability

return results
 # Example usage:
ob = Solution()
matrix = [
[3, 1, 2],
[4, 7, 5],
[6, 8, 0]
]

# Output the number of moves required to solve the puzzle

print("NO OF MOVES==", ob.solve(matrix)

OUTPUT:

NO OF MOVES== 4

RESULT:

Thus the program to implement 8 puzzles search strategy is implemented and executed successfully
EX.No. 1.b Implement basic search strategies – 8-Queens Problem
DATE:

AIM

To implement basic search strategies – 8-Queens Problem.

ALGORITHM

1. The code starts by asking the user to enter a number.

2. It then creates an NxN matrix with all elements set to 0.
3. The code then defines two functions: attack and N_queens.
4. The function attack checks vertically and horizontally, while the function N_queens checks diagonally.
5. If either of these functions return true, it means that there is a queen in that position on the board.
6. The code is a function that will check if there are enough queens on the chessboard.
7. The code starts by defining a function, N_queens (n), which will return true if there are enough queens
and False otherwise.
8. The variable n is used to define how many queens need to be placed on the board for it to be considered
complete.

PROGRAM

def print_board(board):
"""Helper function to print the board."""
for row in board:
print(" ".join("Q" if col == 1 else "." for col in row))
print()

def attack(board, row, col, N):

"""Check if placing a queen at (row, col) is safe."""
# Check vertical and horizontal
for k in range(N):
if board[row][k] == 1 or board[k][col] == 1:
return True

# Check diagonals
for k in range(N):
for l in range(N):
if (k + l == row + col) or (k - l == row - col):
if board[k][l] == 1:
return True
return False

def N_queens(board, n, N):

"""Try to place n queens on the board."""
if n == 0: # All queens are placed
return True
 for i in range(N):
for j in range(N):
if not attack(board, i, j, N) and board[i][j] != 1:
board[i][j] = 1 # Place queen

# Recur to place the rest of the queens

if N_queens(board, n - 1, N):
return True

# Backtrack
board[i][j] = 0

return False

# Main function to set up the N-Queens problem

def main():
print("Enter the number of queens:")
N = int(input())

# Create a NxN chessboard initialized with 0s

board = [[0] * N for _ in range(N)]

if N_queens(board, N, N):
print_board(board)
else:
print("No solution exists.")

# Execute the main function

if name__ == " main ":
main()
OUTPUT:
Enter the number of
queens 8
[1, 0, 0, 0, 0, 0, 0, 0]
[0, 0, 0, 0, 1, 0, 0, 0]
[0, 0, 0, 0, 0, 0, 0, 1]
[0, 0, 0, 0, 0, 1, 0, 0]
[0, 0, 1, 0, 0, 0, 0, 0]
[0, 0, 0, 0, 0, 0, 1, 0]
[0, 1, 0, 0, 0, 0, 0, 0]
[0, 0, 0, 1, 0, 0, 0, 0]

RESULT

Thus the program to implement 8 queens search strategy is implemented and executed successfully.
EX.No.3 Implement basic search strategies – Crypt arithmetic

DATE:

AIM
To implement basic search strategies – Crypt arithmetic.

ALGORITHM

1. Initialization:The code initializes a variable to keep track of letter assignments

and sets up a loop to assign integer values to each character in the given words.
2. Validation: It checks if any of the first or last letters of the words are assigned
a value of zero, indicating an invalid assignment which leads to an early termination
of the function.
3. Backtracking: The algorithm uses a recursive approach to try all possible
assignments of digits to letters, ensuring that no letter is zero if it is the starting letter
of a word.
4. Solution Check:It verifies if the current assignments satisfy the equation
`word1 + word2 = result` and collects all valid solutions.
5. Output:Finally, the code prints the solutions to the puzzle or indicates if no
valid solution is found, displaying a message like "0 Solutions”.
PROGRAM

def find_value(word, assigned):

"""Converts the letters of a word into the corresponding number using the assigned dictionary."""
num = 0
for char in word:
num = num * 10 + assigned[char]
return num

def is_valid_assignment(word1, word2, result, assigned):

"""Checks if the current assignment is valid."""
# First letter of any word cannot be zero
if assigned[word1[0]] == 0 or assigned[word2[0]] == 0 or assigned[result[0]] == 0:
return False
return True

def _solve(word1, word2, result, letters, assigned, solutions):

"""Recursively tries to solve the puzzle using backtracking."""
if not letters:
# All letters have been assigned
if is_valid_assignment(word1, word2, result, assigned):
num1 = find_value(word1, assigned)
num2 = find_value(word2, assigned)
num_result = find_value(result, assigned)
if num1 + num2 == num_result:
# Solution found
solutions.append((f'{num1} + {num2} = {num_result}', assigned.copy()))
 return

# Get the next letter to assign

cur_letter = letters.pop()
for num in range(10):
if num not in assigned.values(): # Avoid reusing digits
assigned[cur_letter] = num
_solve(word1, word2, result, letters, assigned, solutions)
assigned.pop(cur_letter) # Backtrack
letters.append(cur_letter) # Restore the letter to the list

def solve(word1, word2, result):

"""Main function to solve the cryptarithmetic puzzle."""
# Extract all unique letters from word1, word2, and result
letters = sorted(set(word1) | set(word2) | set(result))

# If there are more than 10 unique letters, it's not possible to assign digits
if len(letters) > 10:
print('0 Solutions!')
return

solutions = []
_solve(word1, word2, result, letters, {}, solutions)

if solutions:
print('\nSolutions:')
for soln in solutions:
print(f'{soln[0]}\t{soln[1]}')
else:
print('0 Solutions!')

if __name__ == '__main__':
print('CRYPTARITHMETIC PUZZLE SOLVER')
print('WORD1 + WORD2 = RESULT')
word1 = input('Enter WORD1: ').upper()
word2 = input('Enter WORD2: ').upper()
result = input('Enter RESULT: ').upper()

# Ensure all inputs are valid alphabets

if not word1.isalpha() or not word2.isalpha() or not result.isalpha():
raise ValueError('Inputs should only consist of alphabets.')

solve(word1, word2, result)

 OUTPUT

CRYPTARITHMETIC PUZZLE SOLVER

WORD1 + WORD2 = RESULT
Enter WORD1: SEND
Enter WORD2: MORE
Enter RESULT: MONEY
Solutions:
9567 + 1085 = 10652 {'Y': 2, 'S': 9, 'R': 8, 'O': 0, 'N': 6, 'M': 1, 'E':
5, 'D': 7}

RESULT

Thus the program to implement crypt arithmetic search strategy is implemented and executed successfully
EX.No. 2 Implement A* Algorithm
DATE:

AIM

To Implement A* Algorithm.

ALGORITHM

1. Class Initialization: The `A_Star_Solver` class is initialized with a starting

point, a goal, and a priority queue, setting up the state with values and parent
references.
2. Solve Method: The `Solve` method processes nodes from a priority queue,
exploring paths and checking if the current node matches the goal or if it’s the last
node.
3. Distance Calculation: Each state calculates its distance from the goal using
the `GetDistance` method to guide the search for the optimal path.
4. Node Exploration: The `Analyze` method adds new nodes to the queue, avoids
revisiting nodes, and generates children states using `CreateChildren` to further
explore potential paths.
5. Path Finding: The algorithm continues visiting nodes and their children until it
either finds the goal or exhausts all possibilities, eventually tracing back to the start
if no solution is found.

PROGRAM

from queue import PriorityQueue

# Base Class
class State(object):
def __init (self, value, parent, start=0, goal=0):
self.children = []
self.parent = parent
self.value = value
self.dist = 0
if parent:
self.start = parent.start
self.goal = parent.goal
self.path = parent.path[:] # Copy the parent's path
self.path.append(value) # Add the current value to the path
else:
self.path = [value]
self.start = start
self.goal = goal

def GetDistance(self):
pass
 def CreateChildren(self):
pass

# Subclass for handling strings

class State_String(State):
def __init (self, value, parent, start=0, goal=0):
super(State_String, self). init__(value, parent, start, goal)
self.dist = self.GetDistance() # Calculate distance based on current state

def GetDistance(self):
if self.value == self.goal:
return 0
dist = 0
for i in range(len(self.goal)):
letter = self.goal[i]
if letter in self.value:
dist += abs(i - self.value.index(letter))
else:
dist += len(self.goal) # If letter is not found, add maximum possible distance
return dist

def CreateChildren(self):
if not self.children:
for i in range(len(self.goal) - 1):
val = self.value
# Swap adjacent letters
val = val[:i] + val[i+1] + val[i] + val[i+2:]
child = State_String(val, self) # Create new child state
self.children.append(child)

# A* Solver Class
class A_Star_Solver:
def __init (self, start, goal):
self.path = []
self.visitedQueue = []
self.priorityQueue = PriorityQueue()
self.start = start
self.goal = goal

def Solve(self):
startState = State_String(self.start, None, self.start, self.goal)
count = 0
self.priorityQueue.put((0, count, startState)) # Start state with priority 0

while not self.path and self.priorityQueue.qsize():

closestChild = self.priorityQueue.get()[2]
closestChild.CreateChildren() # Generate possible child states
self.visitedQueue.append(closestChild.value)

for child in closestChild.children:

if child.value not in self.visitedQueue:
count += 1
if child.dist == 0: # If the child distance is 0, we have found the goal
 self.path = child.path
break
self.priorityQueue.put((child.dist, count, child))

if not self.path:
print("Goal is not possible: " + self.goal)
else:
print("Goal found! Path to solution:")
for i in range(len(self.path)):
print("{0}) {1}".format(i, self.path[i]))

# Main block to test the solver

if name__ == " main ":
start1 = "secure"
goal1 = "rescue"
print("Starting A* Solver")
a = A_Star_Solver(start1, goal1)
a.Solve()
 OUTPUT

Starting....
0)secure
1)secrue
2)sercue
3)srecue
4)rsecue
5)rescue

RESULT

Thus the program to implement A* algorithm is implemented and executed successful

EX.No. 3 Implement Mini-Max algorithm for game playing (Alpha-Beta pruning)
DATE:

AIM

To Implement Mini-max algorithm for game playing.

ALGORITHM

1. The code first creates an array of five values.

2. These values represent the possible outcomes of a mini-max calculation.
3. Next, the code calls the minimax () function to find the optimal value for the current player.
4. This function takes three arguments: depth, nodeIndex, and maximizingPlayer.
5. The first two arguments specify how deep into the tree the minimax calculation should occur, and nodeIndex
specifies which child node in that depth should be used as the starting point for this calculation.
6. The third argument is a Boolean flag that tells minimax () whether or notmaximizingPlayer should be used during
this calculation.
7. The code then sets up some variables to track which value in values represents best for each of MIN and MAX.
8. These variables are alpha and beta.
9. Alpha tracks which value in values is currently being optimized (MIN or MAX), while beta tracks which value
is currently being minimized (MIN or MAX).
10. The code then loops through all of values' children nodes, calling minimax () on each one with different
combinations of True and False as its arguments.
11. It keeps track of these results using best and val variables respectively.
12. If any of these calculations results in a better overall value than either MIN or MAX, then that new best value
becomes the new minimum
13. The code will return the optimal value for the current player, which is 5
PROGRAM

MAX, MIN = 1000, -1000

# Returns optimal value for the current player (Initially called for root and maximizing player)
def minimax(depth, nodeIndex, maximizingPlayer, values, alpha, beta):
# Terminating condition. i.e, leaf node is reached
if depth == 3:
return values[nodeIndex]

if maximizingPlayer:
best = MIN
# Recur for left and right children (Maximizing player)
for i in range(2):
 val = minimax(depth + 1, nodeIndex * 2 + i, False, values, alpha, beta)
best = max(best, val)
alpha = max(alpha, best)

# Alpha-Beta Pruning
if beta <= alpha:
break

return best
else:
best = MAX
# Recur for left and right children (Minimizing player)
for i in range(2):
val = minimax(depth + 1, nodeIndex * 2 + i, True, values, alpha, beta)
best = min(best, val)
beta = min(beta, best)

# Alpha-Beta Pruning
if beta <= alpha:
break

return best

# Driver Code
if name__ == " main ":
values = [3, 5, 6, 9, 1, 2, 0, -1] # Leaf node values
print("The optimal value is:", minimax(0, 0, True, values, MIN, MAX))
OUTPUT
The optimal value is : 5

RESULT

Thus the program to implement Minimax algorithm for game playing is implemented and executed
successfully
EX.No.4 Solve constraint satisfaction problems
DATE:

AIM

To solve constraint satisfaction problems.

ALGORITHM

1. The code starts by defining some variables.

2. The first variable, assignment, is a list of strings.
3. The second variable, VARIABLES, is a list of five strings.
4. Next, the code defines some constraints.
5. The first constraint is that the two lists have the same length (len(assignment) == len(VARIABLES)).
6. The next constraint is that each string in VARIABLES must be in one of the three domains (Monday, Tuesday,
and Wednesday).
7. Finally, the last constraint is that each string in DOMAIN must be associated with at least one variable in
VARIABLES.
8. The code then starts to search for an assignment that meets all the constraints.
9. First it checks if assignment is complete (if len (assignment) == len (VARIABLES)) and then it selects a
variable from VARIABLES based on its domain and value.
10. If there are no more variables to select, then the code returns none.
11. Otherwise it backtracks through the assignment looking for a consistent combination of values for each
selected variable.
12. If successful, it assigns those values to corresponding variables in result and returns this as solution.
13. The code first looks for a variable not yet assigned in the VARIABLES list.
14. If no variable is found, it returns none.
15. If an assignment is found, the code checks to see if the variables are consistent with each other.
16. If they are not, the code backtracks and tries again with a different variable.
17. If all variables are consistent with each other, then the code assigns the value of day to var3 and returns False.
PROGRAM
VARIABLES = ["csc", "maths", "phy", "che", "tam", "eng", "bio"]
DOMAIN = ["Monday", "Tuesday", "Wednesday"]
CONSTRAINTS = [
("csc", "maths"),
("csc", "phy"),
("maths", "phy"),
("maths", "che"),
("maths", "tam"),
("phy", "tam"),
("phy", "eng"),
("che", "eng"),
("tam", "eng"),
("tam", "bio"),
("eng", "bio")
]

def backtrack(assignment):
# Check if assignment is complete
if len(assignment) == len(VARIABLES):
return assignment

var = select_unassigned_variable(assignment) # Choose the next unassigned variable

for value in DOMAIN:
if consistent(var, value, assignment):
assignment[var] = value
result = backtrack(assignment)
if result is not None:
return result
# Undo the assignment (Backtrack)
del assignment[var]
return None

def select_unassigned_variable(assignment):
# Select the next unassigned variable (can be optimized with MRV)
for var in VARIABLES:
if var not in assignment:
return var
return None

def consistent(var, value, assignment):

# Check if the assignment of 'value' to 'var' is consistent with the constraints
for var1, var2 in CONSTRAINTS:
if var == var1 or var == var2:
# Check if either of the two variables is already assigned and has a conflict
other_var = var2 if var == var1 else var1
 if other_var in assignment and assignment[other_var] == value:
return False
return True

# Initialize the assignment dictionary and start the backtracking search

assignment = {}
solution = backtrack(assignment)

print("Solution:")
print(solution)

OUTPUT
{'csc': 'Monday', 'maths': 'Tuesday', 'phy': 'Tuesday', 'che': 'Monday', 'tam': 'MoMonday', 'eng': 'Wednesday',
'bio': 'Tuesday'}

RESULT

Thus the program to solve constraint satisfaction problem is implemented and executed successfully.
EX.No. 5 Propositional Model Checking Algorithms
DATE:

AIM

To Implement Propositional Model checking Algorithm.

ALGORITHM

1. Class Literal, it has attributes name and sign to denote whether the literal is positive or negative in use.
2. The neg function returns a new literal with the same name but the opposite sign of its parent literal.
3. The repr function returns the string of the literal name,( or the string with a negative sign) each time the
instance of the literal is called.
4. The CNFConvert function converts the KiB from a list of sets to a list of list for easier computing
5. The VariableSet function finds all the used literals in the KB, and in order to assist with running the DPLL.
6. The Negativeofx function is for holding the negative form of the literal, for use in the DPLL algorithm
7. The pickX function picks a literal from the variable set and works with it as a node in the tree.
8. Now define the functions splitfalseliterals() and splitTrueLiteral().
9. Create the function dpll() that performs the dpll algorithm recursively.
10. Finally call the function to execute the code.

PROGRAM

import re

class Literal:
def __init (self, name, sign=True):
self.name = str(name)
self.sign = sign

def __neg (self):

return Literal(self.name, not self.sign)

def __str (self):

return str(self.name)

def __repr (self):

return f'{str(self.name) if self.sign else "-" + str(self.name)}'

def CNFconvert(KB):
# Converts the KB from a list of sets to a list of lists for easier computing
storage = []
for i in KB:
i = list(i)
i = [str(j) if isinstance(j, str) else j for j in i] # Ensure literals are properly handled
storage.append(i)
return storage
def VariableSet(KB):
# Finds all the used literals in the KB
KB = CNFconvert(KB)
storage = []
for obj in KB:
for item in obj:
if item[0] == '-' and item[1:] not in storage:
storage.append(str(item[1:]))
elif item not in storage and item[0] != '-':
storage.append(str(item))
return storage

def Negativeofx(x):
# Holds the negative form of the literal
return str(x[1:]) if x[0] == '-' else '-' + str(x)

def pickX(literals, varList):

# Picks a literal from the variable set
for x in varList:
if x not in literals:
return x
return None

def splitFalseLiterals(cnf, x):

holder = []
for item in cnf:
if x in item:
item.remove(x)
holder.append(item)
return holder

def splitTrueLiteral(cnf, x):

holder = []
for item in cnf:
if x not in item:
holder.append(item)
return holder

def unitResolution(clauses):
literalholder = {}
i=0
while i < len(clauses):
newClauses = []
clause = clauses[i]
if len(clause) == 1:
literal = str(clause[0])
pattern = re.match("-", literal)
 if pattern:
nx = literal[1:]
literalholder[nx] = False
else:
nx = "-" + literal
literalholder[literal] = True
for item in clauses:
if item != clauses[i]:
if nx in item:
item.remove(nx)
newClauses.append(item)
i=0
clauses = newClauses
return literalholder, clauses

def dpll(clauses, varList):

literals, cnf = unitResolution(clauses)
if cnf == []:
return literals
elif [] in cnf:
return "notsatisfiable"
else:
while True:
x = pickX(literals, varList)
if x is None:
break
nx = Negativeofx(x)
ncnf = splitTrueLiteral(cnf, x)
ncnf = splitFalseLiterals(ncnf, nx)
if ncnf == cnf:
varList.remove(x)
else:
break
case1 = dpll(ncnf, varList)
if case1 != "notsatisfiable":
copy = case1.copy()
copy.update(literals)
copy.update({x: True})
return copy
case2 = dpll(ncnf, varList)
if case2 != "notsatisfiable":
copy = case2.copy()
copy.update(literals)
copy.update({x: False})
return copy
else:
return "notsatisfiable"

def DPLL(KB):
KB = CNFconvert(KB)
varList = VariableSet(KB)
 result = dpll(KB, varList)
if result == 'notsatisfiable':
return [False, {}]
else:
for i in varList:
if i in result and result[i] == True:
result[i] = 'true'
elif i in result and result[i] == False:
result[i] = 'false'
else:
result[i] = 'free'
return [True, result]

# Example usage
A = Literal('A')
B = Literal('B')
C = Literal('C')
D = Literal('D')
KB = [{A, B}, {A, -C}, {-A, B, D}]
print(DPLL(KB))

OUTPUT

[True, {'B': 'true', 'A': True, 'C': 'free', 'D': 'free'}]

RESULT

Thus the program to implement Propositional Model checking Algorithm is implemented and executed
successfully.
 EX.No. 6 Implement Forward Chaining Algorithm

DATE:

AIM

To Implement Forward Chaining Algorithm.

ALGORITHM

1. The code starts by declaring a variable called "x".

2. This is the number of items in the database.
3. The code then declares two variables, "database" and "knowbase".
4. The first one is an array that stores all the words in the database.
5. The second one is an array that stores all the colors of objects in our world.
6. Next, it prints out a message saying *-----Forward--Chaining ---- * to let us know what's going on.
7. Then it displays some text with instructions for how to use this program before displaying any output from it
(i.e., telling us what we're supposed to do).
8. It also tells us which input option will be valid if you enter 1 or 2 as your input value (in this case, frog0 and
green1).
9. If you enter 3 or 4 instead, then they'll tell you about canary1 and yellow3 respectively.
10. Else it prints invalid knowledge database

PROGRAM

database = ["Croaks", "Eat Flies", "Shrimps", "Sings"]

knowbase = ["Frog", "Canary", "Green", "Yellow"]
def display():
print("\n X is \n1..Croaks \n2.Eat Flies \n3.shrimps \n4.Sings ", end='')
print("\n Select One ", end='')

def main():
print("*-----Forward--Chaining ---- *", end='')
display()
x = int(input())
print(" \n", end='')
if x == 1 or x == 2:
print(" Chance Of Frog ", end='')
elif x == 3 or x == 4:
print(" Chance of Canary ", end='')
else:
print("\n-------In Valid Option Select ---------", end='')
if x >= 1 and x <= 4:
print("\n X is ", end='')
 print(database[x-1], end='')
print("\n Color Is 1.Green 2.Yellow", end='')
print("\n Select Option ", end='')
k = int(input())
if k == 1 and (x == 1 or x == 2): # frog0 and green1
print(" yes it is ", end='')
print(knowbase[0], end='')
print(" And Color Is ", end='')
print(knowbase[2], end='')
elif k == 2 and (x == 3 or x == 4): # canary1 and yellow3
print(" yes it is ", end='')
print(knowbase[1], end='')
print(" And Color Is ", end='')
print(knowbase[3], end='')
else:
print("\n---InValid Knowledge Database", end='')

if name == " main ":

main()

OUTPUT

*-----Forward--Chaining ---- *
X is
1.Croaks
2.Eat Flies
3.shrimps
4.Sings
Select One 1
Chance Of Frog
X is Croaks
Color Is
1.Green
2.Yellow
Select Option 1
yes it is Frog And Color Is Green

RESULT

Thus the program to implement Forward Chaining Algorithm is implemented and executed successful
EX.No. 7 Implement backward Chaining Algorithm
DATE:

AIM

To implement backward chaining algorithm

ALGORITHM

1. The code starts with a function called display () which prints out the text "X is 1.frog 2.canary"
2. And then asks for input from the user, asking them to select one of two options: Chance of eating flies or
Chance of shrimping.
3. If they choose either option, it will print out what that option would be in green or yellow color respectively.
4. The next line is x = int (input ()) which takes the value entered by the user and assigns it to variable x.
5. The if statement checks whether x equals 1 or 2
6. So if they enter 1 as their answer, it will print out "Chance of eating flies"
7. Otherwise it will print "Chance of shrimping".

PROGRAM

# Databases and knowledge base

database = ["Croaks", "Eat Flies", "Shrimps", "Sings"]
knowbase = ["Frog", "Canary", "Green", "Yellow"]

def display():
"""Displays options for the user to choose from."""
print("\nX is:")
print("1. Croaks")
print("2. Eat Flies")
print("3. Shrimps")
print("4. Sings")
print("\nSelect One (1-4):", end=' ')

def main():
"""Main function to drive the forward chaining logic."""
print("*-----Forward-Chaining---- *")
display()

try:
x = int(input()) # User selects an option from the database

# Check if the user selected a valid option

if x < 1 or x > 4:
print("\n-------Invalid Option Selected ------- ")
return

# Print the chosen option from the database

print("\nX is", database[x - 1])

# Now ask for the color (Green or Yellow)

 print("\nColor Is:")
print("1. Green")
print("2. Yellow")
print("\nSelect Option (1-2):", end=' ')
k = int(input()) # User selects a color option

# Forward chaining logic

if k == 1: # Green color
if x == 1 or x == 2: # Frog
print("Yes, it is", knowbase[0], "and Color Is", knowbase[2]) # Frog and Green
else:
print("\n---Invalid Knowledge Database---") # Invalid selection for Green
elif k == 2: # Yellow color
if x == 3 or x == 4: # Canary
print("Yes, it is", knowbase[1], "and Color Is", knowbase[3]) # Canary and Yellow
else:
print("\n---Invalid Knowledge Database---") # Invalid selection for Yellow
else:
print("\n---Invalid Option Selected for Color---") # If user selects an invalid color option

except ValueError:
print("\n---Invalid Input. Please enter a number (1-4 for X and 1-2 for Color).---")

if name__ == " main ":

main()
OUTPUT

*-----Backward--Chaining ---- *
X is
1.frog
2.canary
Select One 1
Chance Of eating flies
X is Frog
1.green
2.yellow
1
yes it is in Green colour and will Croak

RESULT

Thus the program to implement backward chaining algorithm is implemented and executed successfully.
EX.No. 8 Implement Naïve Bayes Models

DATE:

AIM

To implement Naïve Bayes Models.

ALGORITHM

1. The code starts by loading the iris dataset.

2. The data is then split into a training and test set of equal size.
3. Next, a Gaussian Naive Bayes classifier is trained using the training set.
4. Then predictions are made on the test set with accuracy scores calculated for each prediction.
5. Finally, a confusion matrix is created to show how well each prediction was classified as correct or incorrect
6. The code is used to train a Gaussian Naive Bayes classifier and then use it to make predictions.
7. The code prints the model's predictions, as well as the test set's output for comparison.

PROGRAM

# Import necessary libraries

from sklearn import datasets
from sklearn.metrics import confusion_matrix, accuracy_score
from sklearn.model_selection import train_test_split
from sklearn.naive_bayes import GaussianNB

# Load the iris dataset

iris = datasets.load_iris()
X = iris.data
Y = iris.target

# Split the dataset into training and testing sets (1/3 for testing)
X_train, X_test, Y_train, Y_test = train_test_split(X, Y, test_size=0.33)

# Training a Gaussian Naive Bayes classifier

model = GaussianNB()
model.fit(X_train, Y_train)

# Make predictions on the test set

model_predictions = model.predict(X_test)

# Print model predictions

print("\nPredicted Values:\n", model_predictions)

# Print actual test values

print("\nActual Values:\n", Y_test)

# Calculate accuracy of the model

accuracy_score_value = accuracy_score(Y_test, model_predictions)
 print("\nAccuracy Score is:", accuracy_score_value)

# Generate a confusion matrix

cm = confusion_matrix(Y_test, model_predictions)
print("\nConfusion Matrix:\n", cm)

OUTPUT

Predicted Values:
[1 0 2 2 2 0 1 0 1 1 2 2 0 2 2 1 1 2 1 0 2 2 0 2 1 0 1 2 1 2 2 0 0 2 1 0 1
2 1 2 2 1 2 2 1 1 2 2 0 1]

Actual Values:
[1 0 2 2 2 0 1 0 1 1 2 2 0 2 2 1 1 2 1 0 2 2 0 2 2 0 1 1 1 1 2 0 0 2 1 0 1
2 1 2 2 1 2 2 2 1 2 2 0 1]

Accuracy Score is: 0.92

Confusion Matrix:
[[11 0 0]
[ 0 15 2]
[ 0 2 20]]

RESULT

Thus the program to implement Naïve Bayes Model is implemented and executed successf
EX.No. 9 Implement Bayesian Networks and perform inferences

DATE:

AIM:
To Implement Bayesian Networks and perform inferences.

ALGORITHM:

1. The code starts by importing the datasets module.

2. This is used to load the iris dataset from sklearn, which is then passed into a variable called data.
3. The target and data_tensor are then created with torch's numpy library.
4. The code continues by creating an instance of a sequential model that has three layers:
bnn.BayesLinear(prior_mu=0, prior_sigma=0.1, in_features=4), nn.ReLU(), and bnn.BayesLinear(prior_mu=0,
prior_sigma=0.1, in_features=100).
5. The cross entropy loss function is then created as well as an instance of it being assigned to the cross entropy
loss variable named crossEntropyLoss().
6. The code will create a model that takes in 100 features and outputs 3.
7. The code starts by importing the necessary libraries.
8. Then it creates a model that will be used to predict the target variable, which is the number of people who are
likely to buy a product in one month.
9. The code then calculates how much money it would cost for this prediction and compares it with what's
actually spent on advertising.
10. The code starts by creating an optimizer object that uses Adam as its learning algorithm and has a parameter
of 0.01 for its learning rate.
11. It then loops through 3000 steps, each step taking about 1 second, where models are created from data_tensor
using torch's max function and predicted is calculated using torch's max function on models' data tensor (which is
just another way of saying "the most recent value").
12. Cross entropy loss is calculated between predictions and targets using cross_entropy_loss
13. Finally, kloss , which stands for Kullback-Leibler divergence, is calculated with klloss .
14. This loss measures how different two probability distributions are from each other; in this case we're
measuring how different our predictions are from their actual values (i.e., whether they were correct).
15. Total cost is then calculated by adding cross entropy plus klweight*kl .
16. Finally, optim
17. The code is used to predict the output of a model given input data.
18. The code starts by initializing the variables that will be needed for later calculations.

36
 19. Next, it calculates the cross entropy loss between target and predicted values.
20. Then, it calculates the cost function which is then minimized using Adam optimizer.
21. Finally, it prints out the predicted value and total cost after every iteration of optimization process.
22. The code starts by defining a function called draw_graph that takes in a predicted value.
23. The code then creates two subplots on the same figure, one for each of the predictions.
24. The first subplot is created with fig_1 and has an index of 1, which means it's the second plot in this figure.
25. This plot will have two axes: x-axis and y-axis.
26. The x-axis represents time, while the y-axis represents accuracy percentage (0% to 100%).
27. The second subplot is created with fig_2 and has an index of 2, which means it's the third plot in this figure.
28. This plot will also have two axes: x-axis and y-axis but they represent different values than those on fig_1 .
29. The code is a function that takes in an input of predicted and returns the accuracy, cross entropy, and KL
values.
30. The first line of code calculates the size of the tensor using target_tensor.size(0) which will be equal to 1
because it is a one-dimensional tensor.
31. Next, we have the function draw_graph which draws a graph with two subplots; one for accuracy and one for
cross entropy.
32. The last line prints out some statistics on how accurate this prediction was.

PROGRAM

# Install torchbnn if not already installed

!pip install torchbnn

# Install PyTorch if not installed

!pip install torch torchvision torchaudio
!pip install matplotlib

import numpy as np
import torch
import torch.nn as nn
import torch.optim as optim
import torchbnn as bnn
import matplotlib.pyplot as plt
from sklearn import datasets

# Load the Iris dataset

37
dataset = datasets.load_iris()
data = dataset.data
target = dataset.target

# Convert to tensors
data_tensor = torch.from_numpy(data).float()
target_tensor = torch.from_numpy(target).long()

# Define the Bayesian Neural Network model

model = nn.Sequential(
bnn.BayesLinear(prior_mu=0, prior_sigma=0.1, in_features=4, out_features=100),
nn.ReLU(),
bnn.BayesLinear(prior_mu=0, prior_sigma=0.1, in_features=100, out_features=3)
)

# Loss functions
cross_entropy_loss = nn.CrossEntropyLoss()
klloss = bnn.BKLLoss(reduction='mean', last_layer_only=False)

# Hyperparameters
klweight = 0.01
optimizer = optim.Adam(model.parameters(), lr=0.01)

# Training the model

for step in range(3000):
model.train() # Set the model to training mode

# Forward pass
models = model(data_tensor)

# Compute the cross-entropy loss

cross_entropy = cross_entropy_loss(models, target_tensor)

# Compute the Kullback-Leibler divergence loss

kl = klloss(model)

38
 # Total loss
total_cost = cross_entropy + klweight * kl

# Zero the gradients, perform the backward pass, and update the weights
optimizer.zero_grad()
total_cost.backward()
optimizer.step()

# Print the accuracy and loss every 300 steps

if step % 300 == 0:
# Get predictions
_, predicted = torch.max(models.data, 1)

# Calculate accuracy
final = target_tensor.size(0)
correct = (predicted == target_tensor).sum().item()
accuracy = 100 * float(correct) / final

print(f'- Accuracy: {accuracy:.2f}%')

print(f'- CE : {cross_entropy.item():.2f}, KL : {kl.item():.2f}')

# Evaluate the model and plot the results

def draw_graph(predicted):
fig = plt.figure(figsize=(16, 8))

# Real data
fig_1 = fig.add_subplot(1, 2, 1)
z1_plot = fig_1.scatter(data[:, 0], data[:, 1], c=target, marker='v')
plt.colorbar(z1_plot, ax=fig_1)
fig_1.set_title("REAL")

# Predicted data
fig_2 = fig.add_subplot(1, 2, 2)
z2_plot = fig_2.scatter(data[:, 0], data[:, 1], c=predicted, marker='v')
plt.colorbar(z2_plot, ax=fig_2)
fig_2.set_title("PREDICT")
39
 plt.show()

# Test the model

model.eval() # Set the model to evaluation mode
with torch.no_grad():
models = model(data_tensor)
_, predicted = torch.max(models.data, 1)

# Plot the results

draw_graph(predicted)

OUTPUT:

- Accuracy: 33.33%
- CE : 2.31, KL : 2.91
- Accuracy: 95.33%
- CE : 0.14, KL : 3.19
- Accuracy: 95.33%
- CE : 0.10, KL : 3.74
- Accuracy: 98.00%
- CE : 0.06, KL : 3.79
- Accuracy: 94.00%
- CE : 0.16, KL : 3.86
- Accuracy: 98.00%
- CE : 0.06, KL : 3.76
- Accuracy: 98.00%
- CE : 0.06, KL : 3.62
- Accuracy: 98.67%
- CE : 0.05, KL : 3.47
- Accuracy: 98.67%
- CE : 0.06, KL : 3.34
- Accuracy: 98.67%
- CE : 0.06, KL : 3.24

40
RESULT

Thus, the program to implement Bayesian Networks and perform inferences is implemented and executed
successfully.

41
 VARUVN VADIVELAN INSTITUTE OF TECHNOLOGY
DHARMAPURI – 636701

DEPARTMENT OF ARTIFICIAL INTELLIGENCE ANDDATA

SCIENCE

AD3301

DATA EXPLORATION AND VISUALIZATION LABORATORY

PRACTICAL EXERCISES:

1. Install the data Analysis and Visualization tool: R/ Python /Tableau Public/ Power BI.
2. Perform exploratory data analysis (EDA) on with datasets like email data set. Export all your emails as a
dataset, import them inside a pandas data frame, visualize them and get different insights from the data.
3. Working with Numpy arrays, Pandas data frames , Basic plots using Matplotlib.
4. Explore various variable and row filters in R for cleaning data. Apply various plot features in R on sample
data sets and visualize.
5. Perform Time Series Analysis and apply the various visualization techniques.
6. Perform Data Analysis and representation on a Map using various Map data sets with Mouse Rollover
effect, user interaction, etc..
7. Build cartographic visualization for multiple datasets involving various countries of the world;
states and districts in India etc.
8. Perform EDA on Wine Quality Data Set.
9. Use a case study on a data set and apply the various EDA and visualization techniques and present an
analysis report.
 LIST OF EXPERIMENTS
S.NO EXPERIMENS PAGE NO MARKS SIGNATURE

9
EX NO: 1
DATE: INSTALLING DATA ANALYSIS AND VISUALIZATION TOOL

AIM:
To write a steps to install data Analysis and Visualization tool: R/ Python /Tableau Public/ Power BI.

PROCEDURE:
R:
 R is a programming language and software environment specifically designed for statistical
computing and graphics.
Windows:
 Download R from the official website: https://cran.r-project.org/mirrors.html
 Run the installer and follow the installation instructions.
macOS:
 Download R for macOS from the official website: https://cran.r-project.org/mirrors.html
 Open the downloaded file and follow the installation instructions.
Linux:
 You can typically install R using your distribution's package manager. For example, on Ubuntu, you
can use the following command:
csharp
Copy code
sudo apt-get install r-base
Python:
 Python is a versatile programming language widely used for data analysis. You can install Python
and data analysis libraries using a package manager like conda or pip.
Windows:
 Download Python from the official website: https://www.python.org/downloads/windows/
 Run the installer, and make sure to check the "Add Python to PATH" option during installation.
 You can install data analysis libraries like NumPy, pandas, and matplotlib using pip.
macOS:
 macOS typically comes with Python pre-installed. You can install additional packages using pip or
set up a virtual environment using Ana
 conda.
Linux:
 Python is often pre-installed on Linux. Use your distribution's package manager to install Python if
it's not already installed. You can also use conda or pip to manage Python packages.
Tableau Public:
 Tableau Public is a free version of Tableau for creating and sharing interactive data visualizations.
 Go to the Tableau Public website: https://public.tableau.com/s/gallery
 Download and install Tableau Public by following the instructions on the website.
Power BI:
 Power BI is a business analytics service by Microsoft for creating interactive reports and dashboards.
 Go to the Power BI website: https://powerbi.microsoft.com/en-us/downloads/
 Download and install Power BI Desktop, which is the tool for creating reports and dashboards.
 Please note that the installation steps may change over time, so it's a good idea to check the official
websites for the most up-to-date instructions and download links. Additionally, system requirements
may vary, so make sure your computer meets the necessary specifications for these tools.
Ex no: 2
Date: Exploratory Data Analysis (EDA) on with Datasets

Aim:
To Perform exploratory data analysis (EDA) on with datasets like email data set.
Procedure:
Exploratory Data Analysis (EDA) on email datasets involves importing the data, cleaning it, visualizing
it, and extracting insights. Here's a step-by-step guide on how to perform EDA on an email dataset using
Python and Pandas
1. Import Necessary Libraries:
Import the required Python libraries for data analysis and visualization.
2. Load Email Data:
Assuming you have a folder containing email files (e.g., .eml files), you can use the email library to
parse and extract the email contents.
3. Data Cleaning:
Depending on your dataset, you may need to clean and preprocess the data. Common
cleaning steps include handling missing values, converting dates to datetime format, and removing
duplicates.
4. Data Exploration:
Now, you can start exploring the dataset using various techniques. Here are some common EDA
tasks:
Basic Statistics:
Get summary statistics of the dataset.
Distribution of Dates:
Visualize the distribution of email dates.
5. Word Cloud for Subject or Message:
Create a word cloud to visualize common words in email subjects or messages.
6. Top Senders and Recipients:
Find the top email senders and recipients.
Depending on your dataset, you can explore further, analyze sentiment, perform network analysis, or
any other relevant analysis to gain insights from your email data.
Program:
# Import necessary libraries
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
# Load the dataset
df = pd.read_csv('D:\ARCHANA\dxv\LAB\DXV\Emaildataset.csv')
# Display basic information about the dataset
print(df.info())
# Display the first few rows of the dataset
print(df.head())
# Descriptive statistics
print(df.describe())
# Check for missing values
print(df.isnull().sum())
# Visualize the distribution of numerical variables
sns.pairplot(df)
plt.show()
# Visualize the distribution of categorical variables
sns.countplot(x='label', data=df)
plt.show()
# Correlation matrix for numerical variables
correlation_matrix = df.corr()
sns.heatmap(correlation_matrix, annot=True, cmap='coolwarm')
plt.show()
# Word cloud for text data (if you have a column with text data)
from wordcloud import WordCloud
text_data = ' '.join(df['text_column'])
wordcloud = WordCloud(width=800, height=400, random_state=21,
max_font_size=110).generate(text_data)
plt.figure(figsize=(10, 7))
plt.imshow(wordcloud, interpolation="bilinear")
plt.axis('off')
plt.show()

OUT PUT:
Data columns (total 4 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Unnamed: 0 5171 non-null int64
1 label 5171 non-null object
2 text 5171 non-null object
3 label_num 5171 non-null int64
dtypes: int64(2), object(2)
memory usage: 161.7+ KB
None
Unnamed: 0 label text label_num
0 605 ham Subject: enron methanol ; meter # : 988291\r\n... 0
1 2349 ham Subject: hpl nom for january 9 , 2001\r\n( see... 0
2 3624 ham Subject: neon retreat\r\nho ho ho , we ' re ar... 0
3 4685 spam Subject: photoshop , windows , office . cheap ... 1
4 2030 ham Subject: re : indian springs\r\nthis deal is t... 0
Unnamed: 0 label_num
count 5171.000000 5171.000000
mean 2585.000000 0.289886
std 1492.883452 0.453753
min 0.000000 0.000000
25% 1292.500000 0.000000
50% 2585.000000 0.000000
75% 3877.500000 1.000000
max 5170.000000 1.000000
Unnamed: 0 0
label 0
text 0
label_num 0
dtype: int64
Result:
The above Performing exploratory data analysis (EDA) on with datasets like email data set has been
performed successfully.
Ex no: 03
Date: Working with Numpy arrays, Pandas data frames , Basic plots using Matplotlib

Aim:
Write the steps for Working with Numpy arrays, Pandas data frames , Basic plots using Matplotlib
Procedure:
1. NumPy:
NumPy is a fundamental library for numerical computing in Python. It provides support for multi-
dimensional arrays and various mathematical functions. To get started, you'll first need to install NumPy if
you haven't already (you can use pip):

pip install numpy

Once NumPy is installed, you can use it as follows:

import numpy as np
# Creating NumPy arrays
arr = np.array([1, 2, 3, 4, 5])
print(arr)
# Basic operations
mean = np.mean(arr)
sum = np.sum(arr)
# Mathematical functions
square_root = np.sqrt(arr)
exponential = np.exp(arr)
# Indexing and slicing
first_element = arr[0]
sub_array = arr[1:4]
# Array operations
combined_array = np.concatenate([arr, sub_array])
OUTPUT:
2. Pandas:
Pandas is a powerful library for data manipulation and analysis.
You can install Pandas using pip:
pip install pandas
Here's how to work with Pandas DataFrames:
import pandas as pd

# Creating a DataFrame from a dictionary

data = {
'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Emily'],
'Age': [25, 30, 35, 28, 22],
'City': ['New York', 'Los Angeles', 'Chicago', 'Houston', 'Miami']
}

df = pd.DataFrame(data)
# Display the entire DataFrame
print("DataFrame:")
print(df)
# Accessing specific columns
print("\nAccessing 'Name' column:")
print(df['Name'])
# Adding a new column
df['Salary'] = [50000, 60000, 75000, 48000, 55000]
# Filtering data
print("\nPeople older than 30:")
print(df[df['Age'] > 30])
# Sorting by a column
print("\nSorting by 'Age' in descending order:")
print(df.sort_values(by='Age', ascending=False))
# Aggregating data
print("\nAverage age:")
print(df['Age'].mean())
# Grouping and aggregation
grouped_data = df.groupby('City')['Salary'].mean()
print("\nAverage salary by city:")
print(grouped_data)
# Applying a function to a column
df['Age_Squared'] = df['Age'].apply(lambda x: x ** 2)
# Removing a column
df = df.drop(columns=['Age_Squared'])
# Saving the DataFrame to a CSV file
df.to_csv('output.csv', index=False)
# Reading a CSV file into a DataFrame
new_df = pd.read_csv('output.csv')
print("\nDataFrame from CSV file:")
print(new_df)
OUTPUT:
 3. Matplotlib:

Matplotlib is a popular library for creating static, animated, or interactive plots and graphs.
Install Matplotlib using pip:
pip install matplotlib
Here's a simple example of creating a basic plot:
import matplotlib.pyplot as plt
# Sample data
x = np.linspace(0, 10, 100)
y = np.sin(x)
# Create a line plot
plt.figure(figsize=(8, 6))
plt.plot(x, y, label='Sine Wave')
plt.title('Sine Wave Plot')
plt.xlabel('X-axis')
plt.ylabel('Y-axis')
plt.legend()
plt.grid(True)
plt.show()
OUTPUT:
RESULT:
Thus the above working with numpy, pandas, matplotlib has been completed successfully.
Ex no:4
Date: Exploring various variable and row filters in R for cleaning data
Aim:
Exploring various variable and row filters in R for cleaning data.
PROCEDURE:
Data Preparation and Cleaning
First, let's create a sample dataset and then explore various variable and row filters to clean the data

# Create a sample dataset

set.seed(123)
data <- data.frame(
ID = 1:10,
Age = sample(18:60, 10, replace = TRUE),
Gender = sample(c("Male", "Female"), 10, replace = TRUE),
Score = sample(1:100, 10)
)
# Print the sample data
print(data)
OUTPUT:

Variable Filters
1. Filtering by a Specific Value:
To filter rows based on a specific value in a variable (e.g., only show rows where Age is greater than
30):
filtered_data <- data[data$Age > 30, ]

2. Filtering by Multiple Conditions:

 You can filter rows based on multiple conditions using the & (AND) or | (OR) operators (e.g., show
rows where Age is greater than 30 and Gender is "Male"):
filtered_data <- data[data$Age > 30 & data$Gender == "Male", ]
Row Filters
1. Removing Duplicate Rows:
To remove duplicate rows based on certain columns (e.g., remove duplicates based on 'ID'):
cleaned_data <- unique(data[, c("ID", "Age", "Gender")])
2. Removing Rows with Missing Values:
To remove rows with missing values (NA):
cleaned_data <- na.omit(data)
Data Visualization
1. Apply various plot features using the ggplot2 package to visualize the cleaned data.
# Load the ggplot2 package
library(ggplot2)
# Create a scatterplot of Age vs. Score with points colored by Gender
ggplot(data = cleaned_data, aes(x = Age, y = Score, color = Gender)) +
geom_point() +
labs(title = "Scatterplot of Age vs. Score",
x = "Age",
y = "Score")
# Create a histogram of Age
ggplot(data = cleaned_data, aes(x = Age)) +
geom_histogram(binwidth = 5, fill = "blue", alpha = 0.5) +
labs(title = "Histogram of Age",
x = "Age",
y = "Frequency")
# Create a bar chart of Gender distribution
ggplot(data = cleaned_data, aes(x = Gender)) +
geom_bar(fill = "green", alpha = 0.7) +
labs(title = "Gender Distribution",
x = "Gender",
y = "Count")

RESULT:
Thus the above Exploring various variable and row filters in R for cleaning data.
EXNO: 5 PERFORM EDA ON WINE QUALITY DATA SET.
DATE
AIM:
To write a program to Perform EDA on Wine Quality Data Set.
PROGRAM:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
# Load the dataset
data = pd.read_csv("pathname")
# Display the first few rows of the dataset
print(data.head())
# Get information about the dataset
print(data.info())
# Summary statistics
print(data.describe())
# Distribution of wine quality
sns.countplot(data['quality'])
plt.title(" Wine Quality data set")
plt.show()
# Box plots for selected features by wine quality
features = ['alcohol', 'volatile acidity', 'citric acid', 'residual sugar']
for feature in features:
plt.figure(figsize=(8, 6))
sns.boxplot(x='quality', y=feature, data=data)
plt.title(f'{feature} by Wine Quality')
plt.show()
# Pair plot of selected features
sns.pairplot(data, vars=['alcohol', 'volatile acidity', 'citric acid', 'residual sugar'],
hue='quality', diag_kind='kde')
plt.suptitle("Pair Plot of Selected Features")
plt.show()
# Correlation heatmap
corr_matrix = data.corr()
plt.figure(figsize=(10, 8))
sns.heatmap(corr_matrix, annot=True, cmap="coolwarm", fmt=".2f")
plt.title("Correlation Heatmap")
plt.show()
# Histograms of selected features
features = ['alcohol', 'volatile acidity', 'citric acid', 'residual sugar']
for feature in features:
plt.figure(figsize=(6, 4))
sns.histplot(data[feature], kde=True, bins=20)
plt.title(f"Distribution of {feature}")
plt.show()

OUTPUT:
RESULT:
Thus the above program to to Perform EDA on Wine Quality Data Set.
 EX NO:6
DATE: TIME SERIES ANALYSIS USING VARIOUS VISULAIZATION
TECHNIQUES
AIM:
To perform time series analysis and apply the various visualization techniques.

DOWNLOADING DATASET:
Step 1: Open google and type the following path in the address bar and download a dataset.
http://github.com/jbrownlee/Datasets.
Step 2: write the following code to get the details.
from pandas import read_csv
from matplotlib import pyplot
series=read_csv(‘pathname')
print(series.head())
series.plot()
pyplot.show()

OUTPUT:
Step 3: To get the time series line plot:
series.plot(style='-.')
pyplot.show()

Step 4:
To create a Histogram:
series.hist()
pyplot.show()
Step 5:
To create density plot:
series.plot(kind='kde')
pyplot.show()

Result:
Thus the above time analysis has been checked with Various visualization techniques.
 EX NO: 7
DATE: DATA ANALYSIS AND REPRESENTATION ON A MAP

AIM:
Write a program to perform data analysis and representation on a map using various map data sets
with mouse rollover effect, user interaction.
PROCEDURE:
STEP 1:
 Make sure to install the necessary libraries.
pip install geopandas folium bokeh
PROGRAM:
from bokeh.io import show
from bokeh.models import ColumnDataSource, HoverTool
from bokeh.plotting import figure
from bokeh.layouts import column
import pandas as pd
import folium
# Load your data
data = pd.read_csv('D:\ARCHANA\dxv\LAB\DXV\geographic.csv')
# Create a Bokeh figure
p = figure(width=800, height=400, tools='pan,wheel_zoom,reset')
# Create a ColumnDataSource to hold data
source = ColumnDataSource(data)
# Add circle markers to the figure
p.circle(x='Longitude', y='Latitude', size=10, source=source, color='orange')
# Create a hover tool for mouse rollover effect
hover = HoverTool()
hover.tooltips = [("Info", "@Info"), ("Latitude", "@Latitude"), ("Longitude",
"@Longitude")]
p.add_tools(hover)
# Display the Bokeh plot
layout = column(p)
show(layout)
# Create a map centered at a specific location
m = folium.Map(location=[latitude, longitude], zoom_start=10)
# Add markers for your data points
for index, row in data.iterrows():
folium.Marker(
location=[row['Latitude'], row['Longitude']],
popup=row['Info'], # Display additional info on mouse click
).add_to(m)
# Save the map to an HTML file
m.save('map.html')
OUPUT:

RESULT:
Data analysis and representation on a map using various map data sets with mouse rollover effect,
user interaction has been completed successfully.
EX NO: 8
DATE: BUILDING CARTOGRAPHIC VISUALIZATION

AIM:
Build cartographic visualization for multiple datasets involving various countries of the world;
states and districts in India etc
PROCEDURE:
STEP 1:
Collect Datasets
Gather the datasets containing geographical information for countries, states, or districts. Make sure these
datasets include the necessary attributes for mapping (e.g., country/state/district names, codes, and
relevant data).
STEP 2:
Install Required Libraries:
pip install geopandas matplotlib
STEP 3:
Load Geographic Data:
Use Geopandas to load the geographic data for countries, states, or districts. Make sure to match the
geographical data with your datasets based on the common attributes.
STEP 4:
Merge Datasets:
Merge your datasets with the geographic data based on common attributes. This step is crucial for linking
your data to the corresponding geographic regions.
STEP 5:
Create Cartographic Visualizations:
Use Matplotlib to create cartographic visualizations. You can create separate plots for different datasets
or overlay them on a single map.
STEP 6:
Customize and Enhance:
Customize your visualizations based on your needs. You can add legends, labels, titles, and other
elements to enhance the interpretability of your maps.
STEP 7:
Save and Share:
Save your visualizations as image files or interactive plots if needed. You can then share these
visualizations with others.
PROGRAM:
import pandas as pd
import geopandas as gpd
import shapely
# needs 'descartes'
import matplotlib.pyplot as plt
df = pd.DataFrame({'city': ['Berlin', 'Paris', 'Munich'],
'latitude': [52.518611111111, 48.856666666667, 48.137222222222],
'longitude': [13.408333333333, 2.3516666666667, 11.575555555556]})
gdf = gpd.GeoDataFrame(df.drop(['latitude', 'longitude'], axis=1),
crs={'init': 'epsg:4326'},
geometry=[shapely.geometry.Point(xy)
for xy in zip(df.longitude, df.latitude)])
print(gdf)
world = gpd.read_file(gpd.datasets.get_path('naturalearth_lowres'))
base = world.plot(color='white', edgecolor='black')
gdf.plot(ax=base, marker='o', color='red', markersize=5)
plt.show()

OUTPUT:
city geometry
0 Berlin POINT (13.40833 52.51861)
1 Paris POINT (2.35167 48.85667)
2 Munich POINT (11.57556 48.13722)
RESULT:
Build cartographic visualization for multiple datasets involving various countries of the world;
has been visualized successfully.
EX NO :9
DATE: VISUALIZING VARIOUS EDA TECHNIQUES AS CASE STUDY FOR
IRIS DATASET
AIM:
Use a case study on a data set and apply the various EDA and visualization techniques and
present an analysis report.
PROCEDURE:
Import Libraries:
Start by importing the necessary libraries and loading the dataset.
Descriptive Statistics:
Compute and display descriptive statistics.
python
Check for Missing Values:
Verify if there are any missing values in the dataset.
Visualize Data Distributions:
Visualize the distribution of numerical variables.
python
Correlation Heatmap:
Examine the correlation between numerical variables.
Boxplots for Categorical Variables:
Use boxplots to visualize the distribution of features by species.
Violin Plots:
Combine box plots with kernel density estimation for better visualization.
Correlation between Features:
Visualize pair-wise feature correlations.
Conclusion and Summary:
Summarize key findings and insights from the analysis.
This case study provides a comprehensive analysis of the Iris dataset, including data exploration,
descriptive statistics, visualization of data distributions, correlation analysis, and feature-specific
visualizations.