Experiment Title#: Explore databases

Aim/Objective:

The experiment aims to address the following objectives:

1. Understanding Database Fundamentals

2. Hands-on Database Creation
3. Executing SQL Queries
4. Exploring Real-world Applications
5. Introducing Data Security and Integrity

Pre-Requisites:

 Foundational Knowledge of Programming

 Mathematical and Logical Thinking Skills. Familiarity with logical operations, sets, and basic
algebra is beneficial for understanding query operations like joins, conditions, and groupings.
 Awareness of Data Formats like CSV, JSON, or Excel sheets
 Introduction to Relational Concepts includes tables, relationships, primary/foreign keys
 SQL Basics commands (SELECT, INSERT, UPDATE, DELETE)

Pre-Lab:

1. What is a database, and how does it differ from a spreadsheet?

A database is a structured system for storing, managing, and retrieving large amounts of data,
often used by multiple users simultaneously. Unlike spreadsheets, databases are designed to
handle relationships between data across multiple tables and provide advanced querying and
scaling capabilities.

2. Define the following terms: Primary Key, Foreign Key, and Index. Provide examples for each.

 Primary Key: A unique identifier for each record in a table, e.g., StudentID in a student
table.
 Foreign Key: A field in one table that links to the primary key of another table, e.g.,
CourseID in an enrollment table referencing a courses table.
 Index: A data structure that improves query performance, e.g., an index on LastName in
an employee’s table for faster searches.

3. What is SQL, and why is it important for working with databases?

SQL (Structured Query Language) is a standard programming language used to interact with
databases. It allows users to query, manipulate, and manage data efficiently, ensuring data
retrieval and modifications meet business needs.

4. Explain the difference between a SELECT statement and an INSERT statement in SQL.

 SELECT retrieves data from a database (e.g., SELECT * FROM employees;).

 INSERT adds new data to a table (e.g., INSERT INTO employees (name, age) VALUES
('John', 30) ;).
5. What are some real-world examples where databases are used? Why is organizing data
effectively important in these scenarios?

 Examples: Banking systems, e-commerce platforms, healthcare records, and social

media.
 Importance: Databases help organize data, ensure accuracy, enable efficient queries,
and support decision-making and scalability.

In-Lab:

1. Setting Up the Environment - Install and configure a database management system

(DBMS) such as MySQL, PostgreSQL, or SQLite.

2. Create a new database and connect to it using a database client or terminal.

3. Define and create tables for a sample use

4. Insert sample data into the tables using INSERT statements.

5. Write and execute queries to retrieve, modify and explore constraints

Procedure/Program:

1. Setting Up the Environment - Install and configure a database management system

(DBMS) such as MySQL, PostgreSQL, or SQLite.

Solution:
 Installing the DBMS
 Download from PostgreSQL official website.
 Install with components like pgAdmin (a GUI tool for managing the database).
 Set up a username and password for the superuser (default postgres).

2. Create a new database and connect to it using a database client or terminal.

Solution:
 CREATE DATABASE test_db;
 CREATE USER 'test_user' IDENTIFIED BY 'password';
 GRANT ALL PRIVILEGES ON test_db.* TO 'test_user';

3. Define and create tables for a sample use

Solution:

-- Create Departments table

CREATE TABLE Departments (
DepartmentID INT AUTO_INCREMENT PRIMARY KEY,
DepartmentName VARCHAR(100) NOT NULL
);
-- Create Employees table
CREATE TABLE Employees (
EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
DepartmentID INT,
HireDate DATE,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

4. Insert sample data into the tables using INSERT statements.

Solution:

-- Insert data into Departments table

INSERT INTO Departments (DepartmentName)

VALUES

('Human Resources'),

('IT'),

('Finance'),

('Marketing');

-- Insert data into Employees table

INSERT INTO Employees (FirstName, LastName, DepartmentID, HireDate)

VALUES

('John', 'Doe', 1, '2023-06-15'), -- Employee in Human Resources

('Jane', 'Smith', 2, '2022-09-10'), -- Employee in IT

('Alice', 'Brown', 3, '2021-03-25'), -- Employee in Finance

('Mark', 'Johnson', 4, '2020-11-05'); -- Employee in Marketing

5. Write and execute queries to retrieve, modify and explore constraints

Solution:

SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName,

Departments.DepartmentName, Employees.HireDate
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

UPDATE Employees
SET DepartmentID = 3
WHERE FirstName = 'Jane' AND LastName = 'Smith';

ALTER TABLE Employees

ADD COLUMN Salary DECIMAL(10, 2);

Check Constraints:
SELECT conname AS ConstraintName, contype AS ConstraintType
FROM pg_constraint
WHERE conrelid = 'employees'::regclass;
ALTER TABLE Employees
ADD CONSTRAINT CheckSalary CHECK (Salary > 30000);

Sample VIVA-VOCE Questions (In-Lab):

1. What is the purpose of a primary key in a database, and can a table have more than one primary
key?

 Purpose: A primary key uniquely identifies each record in a table and ensures that no
duplicate or null values exist for the key column(s).
 Multiple Primary Keys: A table cannot have more than one primary key, but it can
have a composite primary key, which combines multiple columns into a single primary
key.
2. Write an SQL query to retrieve the data using where command with a condition.

SELECT * FROM Employees

WHERE DepartmentID = 2 AND HireDate > '2022-01-01';

3. What is the difference between DELETE and TRUNCATE commands in SQL?

DELETE:

 Removes specific rows based on a condition (WHERE clause).

 Triggers are activated.

 Can roll back changes using transactions.

TRUNCATE:

 Removes all rows from a table, resetting auto-increment counters.

 No triggers are activated.

 Typically, faster and cannot roll back changes (depending on DBMS).

4. How does the JOIN operation work, and can you explain the difference between INNER JOIN and
LEFT JOIN?

 JOIN Operation: Combines rows from two or more tables based on related columns
(usually through foreign keys).
 INNER JOIN: Retrieves only the matching records from both tables.
 LEFT JOIN: Retrieves all records from the left table and matching records from the right
table, filling unmatched rows with NULL.

5. Why is it important to enforce constraints like NOT NULL and UNIQUE in a database schema?

 NOT NULL: Ensures essential data is not left blank, improving data integrity.
 UNIQUE: Prevents duplicate entries, ensuring data consistency for attributes like email
or username.
Constraints enforce data reliability and prevent errors or inconsistencies in relational
databases.

Post-Lab:

1. Advanced Query Practice such as Writing subqueries, Using window functions , joins
2. Schema Optimization Exercise includes analyze and normalize their database schema to at least
the third normal form (3NF) and document the changes made.
3. Error Debugging: Fix a query where a foreign key constraint violation occurs.
4. Real-world Application Report

 Procedure/Program:

1. Advanced Query Practice such as Writing subqueries, Using window functions , joins
Writing Subqueries:
SELECT DepartmentName
FROM Departments
WHERE DepartmentID IN (
SELECT DepartmentID
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(EmployeeID) > 2
);
Using Window Functions:
SELECT DepartmentID, FirstName, LastName, Salary,
SUM(Salary) OVER (PARTITION BY DepartmentID ORDER BY HireDate) AS CumulativeSalary
FROM Employees;
Joins:
Inner Join:
Retrieve employee details along with their department names:

SELECT e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName

FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

Left Join:
Show all employees, even those without a department:

SELECT e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName

FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

Self-Join:
Find employees who share the same department:

SELECT e1.FirstName AS Employee1, e2.FirstName AS Employee2, e1.DepartmentID

FROM Employees e1 INNER JOIN Employees e2 ON e1.DepartmentID = e2.DepartmentID AND
e1.EmployeeID < e2.EmployeeID;

2. Schema Optimization Exercise includes analyze and normalize their database schema to at least
the third normal form (3NF) and document the changes made.
Step 1: First Normal Form (1NF)
1NF requires all attributes to have atomic (indivisible) values.

Both Departments and Employees tables already follow 1NF.

Step 2: Second Normal Form (2NF)
2NF removes partial dependencies, meaning all non-key attributes must depend entirely on the
primary key.

The existing schema satisfies 2NF because there is no partial dependency in the current tables.
Step 3: Third Normal Form (3NF)
3NF removes transitive dependencies, ensuring that non-key attributes are dependent only on
the primary key.

Remove the transitive dependency of DepartmentName from the Employees table.

Create a normalized schema where DepartmentName resides only in the Departments table.
3. Error Debugging: Fix a query where a foreign key constraint violation occurs.
Understanding Foreign Key Constraint Violations
A foreign key (FK) constraint ensures that the value in a child table's column corresponds to a
valid value in the referenced column of the parent table. A violation occurs when:
1. You try to insert or update a value in the child table that doesn't exist in the parent table.
2. You delete a row from the parent table that has dependent rows in the child table.
4. Real-world Application Report

Key Tables in the Database

1. Customers Table

o Stores personal details of customers.

CREATE TABLE Customers (

CustomerID INT PRIMARY KEY,

FirstName VARCHAR(100),

LastName VARCHAR(100),

Email VARCHAR(100),

Phone VARCHAR(15),

Address VARCHAR(255)

);

2. Products Table

o Stores product details.

CREATE TABLE Products (

ProductID INT PRIMARY KEY,

ProductName VARCHAR(100),

Price DECIMAL(10, 2),

StockQuantity INT

);

3. Sales Table

o Tracks individual transactions made by customers.

CREATE TABLE Sales (

SaleID INT PRIMARY KEY,

CustomerID INT,

ProductID INT,

Quantity INT,

SaleDate DATETIME,

TotalAmount DECIMAL(10, 2),

FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),

FOREIGN KEY (ProductID) REFERENCES Products(ProductID)

);

Use Case and Application

Customer Data Management:

 Customers can make purchases via the store’s website or physical location.

 By maintaining a Customers table, businesses can store detailed profiles, track purchasing
behavior, and execute targeted marketing strategies.

 Using an INDEX on Email helps speed up searches related to customer lookup.

Product Inventory:

 The Products table stores critical product details, including stock quantity.

 When a sale is made, the Sales table records the transaction details, and the inventory is
updated by reducing the quantity in the Products table.

Example Query (Update product stock after a sale):

UPDATE Products

SET StockQuantity = StockQuantity - 1

WHERE ProductID = 101; -- For example, ProductID 101 is sold

Sales Tracking and Reporting:

 The Sales table stores the total amount for each sale, which is used to generate business reports
such as:

o Total sales for a day/week/month.

o Most popular products sold.

o Total revenue generated by each customer.

Example Query (Calculate total sales for the month):

SELECT SUM(TotalAmount) AS TotalSales

FROM Sales

WHERE SaleDate BETWEEN '2024-12-01' AND '2024-12-31';

Customer Insights and Targeted Marketing:

 Retail businesses use the customer data and sales history stored in the database to personalize
promotions, generate coupons, and send newsletters to customers.

 For example, if a customer frequently buys specific products, the company might send them
promotions or discounts related to those products.

Example Query (Find loyal customers who spend more than $100):

SELECT CustomerID, SUM(TotalAmount) AS TotalSpent

FROM Sales

GROUP BY CustomerID

HAVING SUM(TotalAmount) > 100;

Challenges in Database Management

1. Data Redundancy: Managing large-scale data can result in duplication and inconsistencies.
Regular maintenance and normalization help avoid these issues.

2. Data Security: Storing sensitive customer data (such as emails and phone numbers) requires
implementing proper security measures like encryption and access control to prevent
unauthorized access.

3. Scalability: As businesses grow, the volume of transactions increases. Database systems need to
scale efficiently and effectively to handle millions of records.
(For Evaluator’s use only)

Evaluator Remark (if Any):

Marks Secured: out of 50

Signature of the Evaluator with Date

Evaluator MUST ask Viva-voice prior to signing and posting marks for each experiment