ARUNAC Name of the Student: _______________________________

HALA
Register Number :_______________________________
COLLEG
E OF Year &Branch :_______________________________
ENGINEE
RING Semester :_______________________________
FOR
WOMEN,
MANAVI
LAI

Subject Code & Name : CCS341 & DATA WAREHOUSING

Branch : AI & DS
Year/Semester : III / VI
DEPART
MENT OF Course Outcomes :
ARTIFICI On the successful completion of the course, the students will be able to
AL
INTELLI Cos Knowledge Course Outcomes
GENCE Level
AND CO1 K1 Design data warehouse architecture for various Problems
DATA
CO2 K3 Apply the OLAP Technology
SCIENCE
CO3 K3 Analyse the partitioning strategy
CO4 K3 Critically analyze the differentiation of various schema for given
problem
CO5 K4 Frame roles of process manager & system manager

CCS341 & DATA WAREHOUSING

(Regulations-2021)

1
 duce outstanding women graduate to compete with the technological challenges
with right attitude towards social empowerment.
Mission
 To equip necessary resources and to establish sufficient infrastructure for a
beneficial process of learning that paves the way for making ideal technocrats.
 To educate and make the students efficient with necessary skills and to make them
industry ready engineers.
 To establish high-level learning and research skills to confront technological
scenarios.
 To provide valuable resources for social empowerment and lifelong learning
process.

COLLEG DEPARTMENT VISION & MISSION STATEMENT

E VISION Vision

& To provide skill based technical education in the field of Computer Science with

MISSION evolving technologies and to produce employable individuals in the society.

STATEM Mission

ENT  To create an environment for student-centric learning and impart quality technical

Vision education for professionals.

To  To provide training in latest technological trends and advancements in the world of

inc computing technology.

uba  To empower the students with required skills to solve the complex technical

te problems.

val  To enhance the creativity in research and to develop the competency of the students

ue in technological field.

bas PROGRAM EDUCATIONAL OBJECTIVES (PEO’s)

ed  To direct the students in analytical, design and implementation skills for solving

tec computational problems.

hni  To train the students to become a software professional with social responsibilities

cal and ethical values.

edu  To enable the graduates be effective team member and infuse leadership qualities.

cati PROGRAM OUTCOMES (PO'S)

on 1. Engineering knowledge: Apply the knowledge of mathematics, science, engineering

and fundamentals, and an engineering specialization to the solution of complex engineering

pro problems.

2
2. for complex engineering problems and design system components or processes that meet
Problem the specified needs with appropriate consideration for the public health and safety, and the
analysis: cultural, societal, and environmental considerations.
Identify, 4. Conduct investigations of complex problems: Use research-based knowledge and
formulate, research methods including design of experiments, analysis and interpretation of data, and
review synthesis of the information to provide valid conclusions.
research 5. Modern tool usage:
literature, Create, select, and apply appropriate techniques, resources, and modern engineering and IT
and tools including prediction and modeling to complex engineering activities with an
analyze understanding of the limitations.
complex 6. The engineer and society: Apply reasoning informed by the contextual knowledge to
engineerin assess societal, health, safety, legal and cultural issues and the consequent responsibilities
g problems relevant to the professional engineering practice.
reaching 7. Environment and sustainability: Understand the impact of the professional engineering
substantiat solutions in societal and environmental contexts, and demonstrate the knowledge of, and
ed need for sustainable development.
conclusion 8. Ethics: Apply ethical principles and commit to professional ethics and responsibilities
s using and norms of the engineering practice.
first 9. Individual and team work: Function effectively as an individual, and as a member or
principles leader in diverse teams, and in multidisciplinary settings.
of 10. Communication: Communicate effectively on complex engineering activities with the
mathemati engineering community and with society at large, such as, being able to comprehend and
cs, natural write effective reports and design documentation, make effective presentations, and give
sciences, and receive clear instructions.
and 11. Project management and finance: Demonstrate knowledge and understanding of the
engineerin engineering and management principles and apply these to one’s own work, as a member
g sciences. and leader in a team, to manage projects and in multidisciplinary environments.
3. 12. Life-long learning: Recognize the need for, and have the preparation and ability to
Design/de engage in independent and life-long learning in the broadest context of technological
velopment change.
of PROGRAM SPECIFIC OUTCOMES PROGRAM (PSO’S)
solutions:  Able to solve problems in the broad area of programming concepts, appraise
Design environmental and social issues with ethics and manage different projects.
solutions

3
 Ap  Create solutions by adapting emerging technologies for real time applications of
ply industry.
the
acq
uir
ed
kno
wle
dge
to
des
ign
and
dev
elo
p
the
co
mp
ute
r
soft
war
e
LIST OF EXPERIMENTS WITH COs, POs & PSOs
and
har [Link] NAME OF EXPERIMENTS COs POs PSOs
dw Data exploration and integration with WEKA. CO1 1,2,3,4,5,6,9,10,11, 1,2,3
1
12
are.
Apply weka tool for data validation. CO1 1,2,3,4,5,6,9,10,11, 1,2,3
2
12
Plan the architecture for real time application. CO2 1,2,3,4,5,6,9,10,11, 1,2,3
3
12
Write the query for schema definition. CO4 1,2,3,4,5,6,9,10,11, 1,2,3
4
12
Design data ware house for real time CO3 1,2,3,4,5,6,9,10,11, 1,2,3
5
applications. 12
Analyse the dimensional Modelling. CO5 1,2,3,4,5,6,9,10,11, 1,2,3
6
12
4
 Case  Always sit on assigned computer.
7
 Enter laboratory in time and work quitely.
Case
8  Use the computer properly to keep it in good working condition.
Implementation  Wear id cards and lab coats before entering the laboratory.
9
 Report the problems identifies in the computer to the staff in charge.
 Shut down the computer properly before leaving the lab.
Implementation of Decision Tree Induction.
10
DON’Ts: of FP- Growth algorithm.
Implementation
11
 Do not eat in the lab.
 D not wander around the room and distract other students.
12 Classification of data using Bayesian approach.
 Do not remove anything from the computer laboratory without permission.
13 Implementation ofK-means algorithm.
Avoid stepping on electrical wires or any other computer cables
 Do not change the computer settings.
 Unauthorized access to any software is prohibited.
 Do not connect pen drive or any other storing devices.
 Do not download any software from unauthorized sites.
 Do not send any emails to unauthorized persons without permission.

INSTRUC
TIONS
TO
STUDEN
TS

DOs:
INDEX

Sl.
No. Date Topic Page No Signature

5
EX NO:1

AIM:

6
DATA EXPLORATION AND INTEGRATION WITH WEKA

To apply WEKA tool for data exploration and integration.

Installation steps of WEKA data mining toolkit.
1. Download the software as your requirements from the below given link.
[Link]
2. The Java is mandatory for installation of WEKA so if you have already Java on
your machine then download only WEKA else download the software with JVM.
3. Then open the file location and double click on the file.

4. Click Next

7
5. Click I Agree.

6. As your requirement do the necessary changes of settings and click Next. Full
and Associate files are the recommended settings.

8
7. Change to your desired installation location.

8. If you want a shortcut then check the box and click Install.

9. The Installation will start wait for a while it will finish within a minute.

9
10. After complete installation click on Next.

[Link]‘s all click on the Finish and take a shovel and start Mining.

1
0
This is the GUI you get when started. You have 4 options Explorer,
Experimenter, Knowledge Flow and Simple CLI.
The Graphical User Interface:
The Weka GUI Chooser (class Weka. Gui. GUI Chooser) provides a starting point
for launching Weka‘s main GUI applications and supporting tools. If one prefers a MDI
(multiple document interface) appearance, then this is provided by an alternative launcher
called Main. The GUI Chooser consists of four buttons, one for each of the four major Weka
applications, and four menus.
The buttons can be used to start the following applications:
 Explorer: An environment for exploring data with WEKA
 Experimenter: An environment for performing experiments and conducting statistical
tests between learning schemes.
 Knowledge Flow: This environment supports essentially the same functions as the
Explorer but with a drag-and-drop interface. One advantage is that it supports
incremental learning.
 Simple CLI: Provides a simple command-line interface that allows direct execution of
WEKA commands for operating systems that do not provide their own command line
interface.
1. Explorer
The Graphical user interface
1.1 Section Tabs
At the very top of the window, just below the title bar, is a row of tabs. When the Explorer is
first started only the first tab is active; the others are greyed out. This is because it is necessary

1
1
to open (and potentially pre-process) a data set before starting to explore the data. The tabs
are as follows:
1. Preprocess. Choose and modify the data being acted on.
2. Classify. Train & test learning schemes that classify or perform regression
3. Cluster. Learn clusters for the data.
4. Associate. Learn association rules for the data.
5. Select attributes. Select the most relevant attributes in the data.
6. Visualize. View an interactive 2D plot of the data.
2. Weka Experimenter:
The Weka Experiment Environment enables the user to create, run, modify, and
analyse experiments in a more convenient manner than is possible when processing the
schemes individually. For example, the user can create an experiment that runs several
schemes against a series of datasets and then analyse the results to determine if one of the
schemes is (statistically) better than the other schemes.

The Experiment Environment can be run from the command line using the Simple CLI.
The Experimenter comes in two flavours, either with a simple interface that provides most of
the functionality one needs for experiments, or with an interface with full access to the
Experimenter’s capabilities. You can choose between those two with the Experiment
Configuration Mode radio buttons:
 Simple
 Advanced

1
2
 Both setups allow you to setup standard experiments, that are run locally on a single
machine, or remote experiments, which are distributed between several hosts. The
distribution of experiments cut down the time the experiments will take until completion, but
on the other hand the setup takes more time. The next section covers the standard
experiments (both, simple and advanced), followed by the remote experiments and finally the
analysing of the results.
3. Knowledge Flow:
The Knowledge Flow provides an alternative to the Explorer as a graphical front end
to WEKA’s core algorithms. The Knowledge Flow presents a data-flow inspired interface to
WEKA. The user can select WEKA components from a palette, place them on a layout
canvas and connect them together in order to form a knowledge flow for processing and
analysing data. At present, all of WEKA’s classifiers, filters, clusters, associators, loaders and
savers are available in the Knowledge Flow along with some extra tools.

The Knowledge Flow can handle data either incrementally or in batches (the Explorer
handles batch data only).
4. Simple CLI:
The Simple CLI provides full access to all Weka classes, i.e., classifiers, filters, clusters,
1
3
etc., but without the hassle of the CLASSPATH (it facilitates the one, with which Weka was
started). It offers a simple Weka shell with separated command line and output.

Explore the available data sets in WEKA:

Perform data preprocessing tasks and demonstrate to categorical data and nominal data:
1. Open WEKA Tool.
2. Click on WEKA Explorer.
3. Click on open file button.
4. Choose WEKA folder in C drive.
5. Select and Click on data option button.

1
4
Sample Weka Data Sets
Below are some sample WEKA data sets, in arff format.
 [Link]
 [Link]
 [Link]
 [Link]
 [Link]
 [Link]
 [Link]
 [Link]
 [Link]
 [Link]
 [Link]
 [Link]
 [Link]
 [Link]
 [Link]
Steps for load the Weather data set.
1. Open WEKA Tool.
2. Click on WEKA Explorer.
3. Click on open file button.
4. Choose WEKA folder in C drive.
5. Select and Click on data option button.
6. Choose Weather. arff file and open the file.

1
5
List out the attribute names:
1. outlook
2. temperature
3. humidity
4. windy
5. play

10
 Steps to plot the histogram:
1. Open WEKA Tool.
2. Click on WEKA Explorer.
3. Click on Visualize button.
4. Click on right click button.
5. Select and Click on polyline option button.

To Visualize the data in various dimensions:

Click on Visualize All button in WEKA Explorer.

CONCLUSION:

Thus, the exploration and integration of data was done successfully with WEKA.
11
EX NO:2

AIM:

12
APPLY WEKA TOOL FOR DATA VALIDATION

To apply WEKA tool for data validation by splitting a data set into training, testing
and cross validating instances.
PROCEDURE:
1. Load a sample data set to be validated, [Link] from WEKA dataset(Open C:\
program files->WEKA 8.5->data->[Link]) and save 10 instances in the file and save
the file as [Link] in a desired location(D:\new folder).

2. Open WEKA Tool.

3. Click on WEKA Explorer.
4. In the Preprocessor tab, click on Open file, browse and load [Link] dataset
(located at D:\new folder\[Link]) with 10 instances.

13
 5. For data validation, first split 60% of the dataset into training set with 6 instances.
Click on Weka->select Filters->select Unsupervised ->select Instance->select
Resample, then click Filter pane.

6. From the Filter pane , choose invertSelection – False, noReplacement- True,

Sample size percentage-60, then click Ok->click Apply.

7. Sample dataset is filtered with 6 instances. Click on Save and save the file as [Link]
in a desired location.

14
 [Link] on Undo->click Resample from Filter pane, choose invertSelection – True,
noReplacement- True, Sample size percentage-60, then click Ok->click Apply.

9. 4 instances are created.

10. The 50% of remaining sample data set(4instances) is splitted into cross validated
data and test data.
11. Click Resample from Filter pane, select invertSelection – True, noReplacement-
True, Sample size percentage-50, then click Ok->click Apply.

15
12. 2 instances are created. Click on Save and save the file as [Link] in a desired location.

13. Click on Undo->click Resample from Filter pane, select, choose invertSelection –
False, noReplacement- True, Sample size percentage-50, then click Ok->click
Apply.

16
13. 2 instances are created. Click on Save and save the file as test. arff in a desired location.

14. To check the instances created in training, cross validating and testing data right click
and open the files in notepad, the files will be:
[Link]:

17
18
 [Link]:

[Link]:

CONCLUSION:

Thus, the data validation by splitting a data set into training, testing and cross
validating instances was done successfully with WEKA.

19
 EX NO 3:

PLAN THE ARCHITECTURE FOR REAL TIME APPLICATION

AIM:
To design an architecture for classifying, cross validating, and splitting dataset into
training and testing dataset and thereby computing the accuracy by applying decision tree
rule.
Steps to plan the architecture:

1. Define the problem

2. Data collection and preprocessing
3. Choose the appropriate Weka algorithms
4. Real-time data streaming
5. Model training and evaluation.
6. Integration and deployment
7. Monitoring and maintenance
PROCEDURE:

1. Open WEKA Tool.

2. Click on WEKA Explorer.
3. Click on Test option->Supplied Test set ->Set
4. Click on Open file

11
0
5. Load iris. arff data set from data set in WEKA,

6. Click on Classify and then Start option.

The output will be:

To improve the accuracy,

7. Click on Choose and select Decision table rule
8. Choose Cross Validation folds ->10 from Test options and then click on Start.

11
1
 The output will be:

To further improve the accuracy,

9. Choose Percentage split % 66 option from Test option and Click on Start.
The output will be:

CONCLUSION:

Thus, the architecture for classifying and testing a real time application (data set) was
designed successfully.
20
EX NO: 4

AIM:

21
WRITE THE QUERY FOR SCHEMA DEFINITION
To write the query for schema definition using PostgreSQL tool.
PROCEDURE:
1. Click Start- AllPrograms -PostgreSQL 16 - Open pgAdmin4.
2. Click this icon, enter name, host and password as postgre.
3. Double click PostgreSQL 16.
4. Right click databases (1) and choose Create and type database name as dwftp and
Save.

5. Double click dwftp and click schemas (1) - Right click and select Create and type
schema name as dw and Save.

22
6. Double click dw- right click Tables -select Query Tool and run the queries for creating
the tables:
(1) location
(2) phonerate
(3) timedim
(4) facts
CREATE TABLE [Link]
(
id_location integer NOT NULL,
city character VARCHAR (20),

provinence VARCHAR (20),

region VARCHAR (20),
PRIMARY KEY (id_location)

);
To insert the values into the tables right click the table(location) and type the values displayed
in Data Output pane.

23
CREATE TABLE
[Link]( ID_phoneRate INTEGER
NOT NULL, phoneRateType VARCHAR
(20), PRIMARY KEY(ID_phoneRate)
);

CREATE TABLE dw.

timedim( ID_time INTEGER NOT
NULL,
dateMonth INTEGER NOT NULL,
dateYear INTEGER NOT NULL,
PRIMARY KEY(ID_time)

);

24
25
CREATE TABLE [Link]
(

ID_time INTEGER NOT NULL,

ID_phoneRate INTEGER NOT NULL,
ID_location_Caller INTEGER NOT NULL,
ID_location_Receiver INTEGER NOT NULL,
Price FLOAT NOT NULL,

NumberOfCalls INTEGER NOT NULL,

PRIMARY KEY (ID_time, ID_phoneRate, ID_location_Caller,
ID_location_Receiver), FOREIGN KEY(ID_time) REFERENCES [Link]
(ID_time),

FOREIGN KEY(ID_phoneRate) REFERENCES [Link](ID_phoneRate),

FOREIGN KEY(ID_location_Caller) REFERENCES [Link] (ID_location),
FOREIGN KEY(ID_location_receiver) REFERENCES [Link] (ID_location)

);

26
 (1) To display sum of the prices, year and phone rate type(hint:use facts, timedim and
phonerate tables, group by phoneratetype and dateyear)

OUTPUT:

(2) To display month, year, Total number of calls, Total Income and Rank Income:
(hint: use facts and timedim tables, Rename Sum of Number of calls as Total number of
calls, Sum of Price as Total income, Rank(sum of Price) as Rank Income, group by
datemonth and dateyear )

27
28
 OUTPUT:

(3) To display month, total num of calls, Rank of num of calls where year=2003, grp by
datemonth.(hint: use facts and timedim tables, Rename Sum of Number of calls as
TotNumOfCalls, Rank(sum of NumberOfCalls) as RankNumOfCalls)
select dateMonth, sum (NumberOfCalls) as TotNumOfCalls,

rank () over (order by sum (NumberOfCalls) desc) as RankNumOfCalls

from [Link] F, [Link] Te

where F.ID_time = Te.ID_time and dateYear=2003

group by dateMonth;

OUTPUT:

CONCLUSION:

Thus, the query for schema definition using PostgreSQL tool was executed
29
successfully.

21
0
 EX NO: 5

DESIGN DATA WARE HOUSE FOR REAL TIME APPLICATION

AIM:
To design a data ware house for a real time application using PostgreSQL tool.
PROCEDURE:

1. Click Start- AllPrograms -PostgreSQL 16 - Open pgAdmin4.

2. Click this icon, enter name, host and password as postgre.
3. Double click PostgreSQL 16.
4. Right click databases (1) and choose Create and type database name as dwftp and
Save.

5. Double click dwftp and click schemas (1) - Right click and select Create and type
schema name as dw and Save.

21
1
6. Double click dw- right click Tables -select Table create table for Employee as emp1
with columns:
 Eno integer PRIMARY KEY
 Empname VARCHAR(20)
 Age integer
 Salary integer
 Job Char
 Deptno
integer and Save

7. To insert values into table right click the table emp1 select View/edit data -> All
rows and then add the number of rows, insert values by double clicking each
attribute and Save.

21
2
8. Right click on table emp1, select Query tool and perform the query operations:
(a) To list the records in the emp1 table orderby salary in descending order.
select * from dw.emp1 order by salary desc;
OUTPUT:

(b)Display only those employees whose deptno is 1.

select eno,empname,deptno from dw.emp1 where deptno=1;
21
3
CONCLUSION:

Thus, the data ware house for a real time application such as Employee details was
designed successfully.

30
EX NO: 6

ANALYSE THE DIMENSIONAL MODELING

AIM:

To analyse the dimensional modelling for applications and designing the star,
snowflake and fact constellation schemas.
PROCEDURE:

Multidimensional schema is defined using Data Mining Query Language (DMQL).

The two primitives, cube definition and dimension definition, can be used for defining the
data warehouses and data marts.
Star Schema
 Each dimension in a star schema is represented with only one-dimension table.
 This dimension table contains the set of attributes.
 The following diagram shows the sales data of a company with respect to the four
dimensions, namely time, item, branch, and location.
 There is a fact table at the centre. It contains the keys to each of four dimensions.
 The fact table also contains the attributes, namely dollars sold and units sold.

Snowflake Schema
 Some dimension tables in the Snowflake schema are normalized.
 The normalization splits up the data into additional tables.
31
  Unlike Star schema, the dimensions table in a snowflake schema is normalized.
For example, the item dimension table in star schema is normalized and split into
two dimension tables, namely item and supplier table.
 Now the item dimension table contains the attributes item_key, item_name, type,
brand, and supplier-key.
 The supplier key is linked to the supplier dimension table. The supplier dimension
table contains the attributes supplier_key and supplier_type.

Fact Constellation Schema

 A fact constellation has multiple fact tables. It is also known as galaxy schema.
 The following diagram shows two fact tables, namely sales and shipping.
 The sales fact table is same as that in the star schema.
 The shipping fact table has the five dimensions, namely item_key, time_key,
shipper_key, from_location, to_location.
 The shipping fact table also contains two measures, namely dollars sold and units sold.
 It is also possible to share dimension tables between fact tables. For example, time,
item, and location dimension tables are shared between the sales and shipping fact
table.

32
CONCLUSION:
Thus, the dimensional modelling was analysed and the star, snowflake and fact
constellation schemas was designed successfully.

33
EX NO: 7

AIM:

34
CASE STUDY USING OLAP
To study the different OLAP operations.
OLAP Operations:
Since OLAP servers are based on multidimensional view of data, we will discuss OLAP
operations in multidimensional data.
The different OLAP operations are:
1. Roll-up (Drill-up)
2. Drill-down
3. Slice and dice
4. Pivot (rotate)
1. Roll-up (Drill-up):
Roll-up performs aggregation on a data cube in any of the following ways:
 By climbing up a concept hierarchy for a dimension
 By dimension reduction
Roll-up is performed by climbing up a concept hierarchy for the dimension location.
Initially the concept hierarchy was "street < city < province < country". On rolling up,
the data is aggregated by ascending the location hierarchy from the level of city to the
level of country. The data is grouped into cities rather than countries. When roll-up is
performed, one or more dimensions from the data cube are removed.

35
2. Drill-down:
Drill-down is the reverse operation of roll-up. It is performed by either of these ways:
 By stepping down a concept hierarchy for a dimension.
 By introducing a new dimension.
Drill-down is performed by stepping down a concept hierarchy for the dimension time.
Initially the concept hierarchy was “day < month < quarter < year”. On drilling down, the
time dimension is descended from the level of quarter to the level of month. When drill-
down is performed, one or more dimensions from the data cube are added. It navigates the
data from less detailed data to highly detailed data.

3. Slice:
The slice operation selects one particular dimension from a given cube and provides a
new sub-cube.

36
 4. Dice:
Dice selects two or more dimensions from a given cube and provides a new sub-cube.

5. Pivot (rotate):
The pivot operation is also known as rotation. It rotates the data axes in view in order
to provide an alternative presentation of data.

CONCLUSION:
Thus, the case study of different OLAP operations was done successfully.

37
EX NO: 8

AIM:

38
CASE STUDY USING OLTP
To study the various aspects of OLTP operations.
OLTP:
Online Transaction Processing (OLTP) is a type of database system that is optimized for
high-speed data processing and rapid transaction execution in real-time. OLTP is an
operational system that supports transaction-oriented applications in a 3-tier architecture.
OLTP is basically focused on query processing, maintaining data integrity in multi-access
environments as well as effectiveness that is measured by the total number of transactions
per second.

OLTP Architecture:
An OLTP system uses a 3-tier architecture:
• The presentation layer: This layer is the front end or user interface where
transactions are generated.
• The logic layer: Also called the business logic or application layer, this layer
processes transaction data based on predefined rules.
• The data or data store layer: This is where each transaction and related data are
stored and indexed. It includes the database management system (DBMS) and
the database server.

Key characteristics of OLTP:

OLTP systems have four critical characteristics:
1. Fast query processing:

39
 OLTP systems are used for high-speed query processing. They handle transactions in
real-time, meaning that transactions are executed as soon as they are received, with little
or no delay.
2. High concurrency:
OLTP systems use algorithms that allow many concurrent users to perform
transactions simultaneously. Each transaction is executed independently of the others and
in the proper order.
3. ACID properties:
To ensure data integrity, consistency, and reliability, OLTP transactions comply with
the ACID (Atomicity, Consistency, Isolation, Durability) properties. These are:
 Atomicity: Transactions in OLTP systems are atomic, meaning they are
treated as a single, indivisible unit of work. If any part of a transaction fails,
the entire transaction is rolled back, so the database is left in its original state.
 Consistency: OLTP databases are designed to maintain data consistency,
despite failures or errors. Every transaction change table in predefined and
predictable ways, and the database will always be in a valid state.
 Isolation: Transactions in OLTP systems are isolated from each other. This
means that when multiple users read and write data simultaneously, they are
executed independently. It keeps the database in a consistent state.
 Durability: When a transaction is successfully executed in an OLTP database,
the changes to the data are permanent and will survive any subsequent failures
or errors, like system crashes or power outages.
4. Support for simple transactions:
OLTP systems support specific applications or business processes like order
processing, inventory management, or customer service. They are typically not used for
complex queries, data analysis, or reporting tasks.
OLTP use cases:
Online transaction processing systems are used in applications where the primary goal
is to manage and process many transactions in real time. Some standard use cases include:
E-commerce systems:
E-commerce applications use OLTP to manage customer orders, payments, and
inventory in real time. This allows them to provide exceptional customer service, boost

31
0
customer loyalty, and drive growth. For example, an OLTP database can help maintain up-to-
date and accurate inventory data, allowing e-commerce companies to fulfill orders promptly.
Banking and financial services:
Banks and other financial services use OLTP to process financial transactions in real-
time, manage customer data, and enable customers to make deposits, withdraw money,
transfer funds, and access other services quickly. OLTP solutions for financial transaction
systems, like online banking, must have secure and reliable data management practices,
multi-currency support, and custom reporting options. ATMs are the most common example
of an OLTP system used in the financial industry.

Reservation systems:
OLTP drives online reservation systems in the travel and hospitality industry. It is
used in applications that manage bookings, flights, payments, and related services. An online
transaction processing system in the travel and hospitality industry must integrate with
external systems, like airline reservation or car rental applications, and have multi-language
and multi- currency support. OLTP also enables efficient customer data management and
helps provide personalized recommendations that create seamless experiences for travellers.
Customer relationship management (CRM):
Customer relationship management (CRM) platforms use OLTP to manage customer
data, interactions, and transactions. An OLTP system can centralize customer data and record
interactions across multiple channels, like phone calls, emails, and chat messages. OLTP
helps CRM applications automate many sales and marketing processes, including lead
generation and campaign management, so companies can focus on nurturing customer
relationships and increasing sales.
Designing an effective OLTP system:
There are three key factors to consider when building an OLTP solution:
Best practices for schema design
A schema outlines how data is organized in a relational database. In OLTP, the database
schema is designed to process high data volumes. Here are some best practices when creating
an OLTP schema:
 Normalization: Normalization involves breaking down a large table into smaller, more
manageable tables to minimize data duplication. This ensures data consistency and
helps maintain data integrity.

31
1
  Choose appropriate indexes: Indexing is a method used to provide quick access to
database files. Indexed data can speed up query processing and improve performance.
This involves identifying the most frequently used queries and creating indexes on the
relevant columns.
 Use the correct keys: Keys are used to uniquely identify the rows in a table. They
establish relationships between different tables and ensure that there are no duplicate
rows in the database. There are three types of keys:
* Primary key
* Composite key
* Foreign key
 Monitor and optimize: Regularly monitor and optimize the schema to ensure it meets
performance metrics and can handle the required transaction volumes.
Scalability consideration:
Scalability determines the OLTP system’s ability to handle increasing transaction
volumes as the business grows. Factors that improve scalability are:
1. Sharding (horizontal scaling): Horizontal scaling involves adding more servers or
nodes to the database cluster to distribute the workload and improve performance.
2. Vertical scaling: Vertical scaling involves increasing the capacity of the hardware
or infrastructure that the database is running on. This can include adding more
memory, CPUs, or storage to the server.
3. Cloud-based solutions: Cloud-based solutions, such as Amazon RDS, Azure SQL
Database, and Google Cloud SQL, are scalable and highly available OLTP
databases that don’t need infrastructure maintenance.
Transaction and concurrency management:
Transaction and concurrency management are critical components of OLTP solutions.
They ensure database consistency and prevent conflicts between multiple users accessing and
modifying data. ACID compliance is the first consideration for transaction management in
OLTP. Here are three other crucial mechanisms:
 Locking: Locking is a technique that ensures that multiple transactions do not access
the same data simultaneously. Locks can be implemented at the database or table
level. They can be either shared or exclusive. Shared locks allow multiple transactions
to read the same data at the same time, while exclusive locks prevent other
transactions from accessing the data until the lock is released.

40
  Isolation level: An isolation level defines how transactions interact and how they see
changes made by other transactions. Most OLTP systems offer four different isolation levels
- Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
 Deadlock handling: Deadlocks occur when two or more transactions are waiting for each
other to release locked resources and cannot proceed. Deadlock prevention techniques, like
timeout mechanisms and prioritizing transactions, are used to detect and resolve deadlocks
before they occur.

CONCLUSION:
Thus, the case study of Online Transaction Processing was done successfully.

40
EX NO: 9
Decision Tree Induction

AIM: Generate a Decision Tree by using J48 algorithm.

DESCRIPTION:
Decision tree learning is one of the most widely used and practical methods for inductive
inference over supervised data. It represents a procedure for classifying categorical database on
their attributes. This representation of acquired knowledge in tree form is intuitive and easy to
assimilate by humans.
ILLUSTRATION:
Build a decision tree for the following data

AGE INC STU CREDIT_RATI BUYS_C

O D NG O
M ENT MPUTER
E
Youth High No Fair No

Youth High No Excellen No

t
Middle aged High No Fair Yes

Senior Mediu No Fair Yes

m
Senior Low Yes Fair Yes

Senior Low Yes Excellen No

t
Middle aged Mediu Yes Excellen Yes
m t

40
Youth Low No Fair No

Youth Mediu Yes Fair Yes

m
Senior Mediu Yes Fair Yes
m
Youth Mediu Yes Excellen Yes
m t
Middle aged Mediu No Excellen Yes
m t

Middle aged High Yes Fair Yes

Senior Mediu No Excellen No

m t

40
The entropy is a measure of the uncertainty associated with a random variable. As uncertainty
increases, so does entropy, values range from [0-1] to present the entropy of information

Entropy (D) =
Information gain is used as an attribute selection measure; pick the attribute having the highest
information gain, the gain is calculated by:
Gain (D, A) = Entropy (D) -
Where, D: A given data
partition A: Attribute
V: Suppose we were partition the tuples in D on some attribute A having v distinct values D is
split into v partition or subsets, (D1, D2….. Dj) , where Dj contains those tuples in D that have
outcome Aj of A.

Class P:
buys_computer=”yes” Class
N: buys_computer=”no”

Entropy (D) = -9/14log (9/14)-5/15log (5/14) =0.940

Compute the expected information requirement for each attribute start with the attribute age
Gain (age, D)

= Entropy (D) -
= Entropy ( D ) – 5/14Entropy(Syouth)-4/14Entropy(Smiddle-aged)-5/14Entropy(Ssenior)
= 0.940-0.694
=0.246

Similarly, for other

attributes, Gain (Income, D)
=0.029 Gain (Student, D ) =
0.151
Gain (credit_rating, D) = 0.048

Income Student Credit_rating Class

High No Fair No

High No Excellent No

Medium No Fair No

Low Yes Fair Yes

medium Yes excellent yes

Now, calculating information gain for subtable (age<=30)

I The attribute age has the highest information gain and therefore becomes the splitting
* attribute at the root node of the decision tree. Branches are grown for each outcome of

40
age. These tuples are shown partitioned accordingly.

40
Income=”high” S11=0, S12=2
I=0
Income=”medium” S21=1 S22=1
I (S21, S23) = 1
Income=”low” S31=1
S32=0 I=0
Entropy for income
E( income ) = (2/5)(0) + (2/5)(1) + (1/5)(0) = 0.4
Gain( income ) = 0.971 - 0.4 = 0.571

Similarly, Gain(student)=0.971
Gain(credit)=0.0208
Gain( student) is highest ,

A decision tree for the concept buys_computer, indicating whether a customer at All Electronics
is likely to purchase a computer. Each internal (non-leaf) node represents a test on an attribute.
Each leaf node represents a class ( either buys_computer=”yes” or buys_computer=”no”.

first create a csv file for the above problem,the csv file for the above problem will look like the
rows and columns in the above figure. This file is written in excel sheet.

40
Procedure for running the rules in weka:
Step 1:
Open weka explorer and open the file and then select all the item sets. The figure gives a better
understanding of how to do that.

40
Step2:
Now select the classify tab in the tool and click on start button and then we can see the result of
the problem as below

40
EX NO: 10
Classification of data using Bayesian approach

AIM: To apply naïve bayes classifier on a given data set.

Description:
In machine learning, Naïve Bayes classifiers are a family of simple probabilistic classifiers based
on applying Bayes‟ Theorem with strong (naïve) independence assumptions between the features

Example:
.
AGE INCOME STUDENT CREDIT_RATING BUYS_COMPUTER
<30 High No Fair No
<30 High No Excellent No
31-40 High No Fair Yes
>40 Mediu m No Fair Yes
>40 Low Yes Fair Yes
>40 Low Yes Excellent No
31-40 Mediu m Yes Excellent Yes
<=30
Low No Fair No
<=30 Mediu m Yes Fair Yes
>40 Mediu m Yes Fair Yes
<30 Mediu m Yes Excellent Yes
31-40 Mediu m No Excellent Yes
31-40 High Yes Fair Yes
>40 Mediu m No Excellent No

CLASS:
C1:buys_com
puter = ‘yes’
C2:buys_com
puter=’no’
DATA TO
BECLASSIFIED
:

X= (age<=30, income=Medium, Student=Yes, credit_rating=Fair)

40
P(C1): P(buys_computer=”yes”)= 9/14 =0.643
P (buys_computer=”no”) =5/14=0.357

40
Compute P(X/C1) and p(x/c2) weget:

1. P( age=”<=30” |buys_computer=”yes”)=2/9
2. P(age=”<=30”|buys_computer=”no”)=3/5
3. P(income=”medium”|buys_computer=”yes”)=4/9
4. P(income=”medium”|buys_computer=”no”)=2/5
5. P(student=”yes”|buys_computer=”yes”)=6/9
6. P(student=”yes” |buys_computer=”no”)=1/5=0.2
7. P(credit_rating=”fair ”|buys_computer=”yes”)=6/9
8. P(credit_rating=”fair” |buys_computer=”no”)=2/5

X=(age<=30, income=medium, student=yes,

credit_rating=fair) P(X/C1): P
(X/buys_computer=”yes”)=2/9*4/9*6/9*6/
9= 32/1134
P(X/C2):P(X/buys_computer=”no”)=3/5*2/5*1
/5*2/5=12/125

P(C1/X)=P(X/C1)*P(C1)

P(X/buys_computer=”yes”)*P(buys_computer=”yes”)=(32/1134)*(9/14)=0.019

P(C2/X)=p(x/c2)*p(c2)

P (X/buys_computer=”no”)*P(buys_computer=”no”)=(12/125)*(5/14)=0.007

Therefore, conclusion is that the given data belongs to C1 since P(C1/X)>P(C2/X)

Checking the result in the WEKA tool:

In order to check the result in the tool we need
to follow a procedure.

40
40
Step 1:
Create a csv file with the above table considered in the example. the arfffile
will look as shown below:

40
Step 2:
Now open weka explorer and then select all the attributes in the table.

40
Step 3:
Select the classifier tab in the tool and choose baye‟s folder and then naïve baye‟s classifier
to see the result as shown below.

Step3:

Check the main result which we got manually and the result in weka by right clicking on the

40
 result and visualizing the tree.

The visualized tree in weka is as shown below:

CONCLUSION:
The solution what we got manually and the weka both are same.

40