Branch: MCA (DATA SCIENCE) Semester: 1
Student Name: CHAHAT SHRIVASTAVA UID: 25MCD10050
Subject Name: PL/SQL LAB Subject Code: 25CAP-602
Section/Group: MCD 1A Date of Performance: 20-08-2025
Experiment No. :1.1
1. Aim/Overview of the practical:
To understand and implement Horizontal Fragmentation in databases and verify its correctness through predicates,
reconstruction, and validation checks. Understand, analyze and apply common SQL statements including DDL,
DML and DCL statements to perform different operations.
2. Introduction/Objective to Horizontal fragmentation:
Horizontal Fragmentation is a database design technique used in Distributed Database Systems
(DDBMS).In this approach, a relation (table) is divided into subsets of tuples (rows), based on certain
conditions (predicates). Each fragment contains a subset of rows but keeps all the attributes (columns) of the
original relation.
Objectives of Horizontal Fragmentation
1. Data Locality:
Store data closer to the site where it is most frequently accessed, reducing data transfer costs and response
time.
2. Improved Performance:
Queries that are executed locally on smaller fragments run faster than on a large centralized relation.
3. Parallelism:
Different fragments can be processed simultaneously at different sites, improving system throughput.
4. Security and Privacy:
Sensitive data can be stored only at specific locations, restricting unnecessary access.
5. Reliability and Availability:
If one site fails, only the fragment stored there is affected, not the entire relation.
6. Reduced Communication Cost:
Only relevant fragments are accessed and transmitted across the network, avoiding unnecessary data
movement.
1
3. Code for experiment/Practical:
Step 1: Create Database
CREATE DATABASE Mysql1;
USE Mysq1;
Step 2: Create Main Table
CREATE TABLE Emp(
Eno VARCHAR(5),
Ename VARCHAR(50),
Title VARCHAR(50));
Step 3: Insert Sample Data
INSERT INTO Emp VALUES ('E1', '[Link]', '[Link]'),
('E2', '[Link]', '[Link]'),
('E3', '[Link]', '[Link]'),
('E4', '[Link]', 'PROGRAMMER'),
('E5', '[Link]', '[Link]'),
('E6', '[Link]', '[Link]'),
('E7', '[Link]', '[Link]'),
2
('E8', '[Link]', '[Link]');
Select * from Emp;
Step 4: Define Predicates P1:
TITLE <= 'Programmer' P2:
TITLE > 'Programmer'
Step 5: Verify Correctness SELECT
COUNT(*) FROM Emp1 UNION ALL
SELECT COUNT(*) FROM Emp2;
SELECT * FROM EMP1
INTERSECT
SELECT * FROM EMP2;
SELECT * FROM EMP1
UNION
SELECT * FROM EMP2;
Step 6: Correct Predicates (Refined)
CREATE TABLE EMP_ELECT AS
SELECT * FROM EMP WHERE TITLE ='[Link]';
CREATE TABLE EMP_MECH AS
SELECT * FROM EMP WHERE TITLE ='[Link]';
CREATE TABLE EMP_PROG AS
3
SELECT * FROM EMP WHERE TITLE ='PROGRAMMER';
CREATE TABLE EMP_SYST AS
SELECT * FROM EMP WHERE TITLE ='[Link]';
SELECT COUNT(*) FROM EMP_ELECT
UNION ALL
SELECT COUNT(*) FROM EMP_MECH
UNION ALL
SELECT COUNT(*) FROM EMP_PROG
UNION ALL
SELECT COUNT(*) FROM EMP_SYST;
SELECT * FROM EMP_ELECT
UNION
SELECT * FROM EMP_MECH
UNION
SELECT * FROM EMP_PROG
UNION
SELECT * FROM EMP_SYST
ORDER BY ENO;
CREATE VIEW EMP_ELECT1 AS SELECT * FROM EMP_ELECT;
CREATE VIEW EMP_MECH1 AS SELECT * FROM EMP_MECH;
CREATE VIEW EMP_PROGRAMMER1 AS SELECT * FROM EMP_PROG;
CREATE VIEW EMP_SYSANAL1 AS SELECT * FROM EMP_SYST;
SELECT * FROM EMP_ELECT1;
SELECT * FROM EMP_MECH1;
SELECT * FROM EMP_PROGRAMMER1;
SELECT * FROM EMP_SYSANAL1;
4
4. Result/Output/Writing Summary:
5
Learning outcomes (What I have learnt):
Understanding Data Distribution:
Learners will understand how large relations can be divided into smaller row-based fragments and distributed
across multiple sites.
Application of Predicates:
Gain knowledge of how selection predicates (conditions) are applied to partition a table into horizontal
fragments.
Improved Query Performance Awareness:
Recognize how dividing relations into smaller fragments leads to faster query execution due to reduced data
size and localized access.
Concept of Data Locality:
Learn how horizontal fragmentation ensures that data is stored closer to the site where it is used most
frequently, reducing communication overhead.
Parallel Processing Skills:
Understand how queries can be processed in parallel across different fragments, improving system throughput.