Lab 2:
Introduction Relational Algebra, SQL, and Normalization
Objectives
Lab Objectives
By the end of this lab, students will be able to:
Understand the basics of relational databases and their importance.
Learn the concepts of relational algebra and how they form the
foundation of SQL.
Write and execute SQL queries for data definition and
manipulation.
Apply normalization techniques (1NF, 2NF) to organize data and
remove redundancy.
Instructions
This is individual Lab work/task.
Complete this lab work within lab timing.
Discussion with peers is not allowed.
You can consult any book, notes & Internet.
Copy paste from Internet will give you negative marks.
Lab work is divided into small tasks, complete all tasks sequentially.
Show solution of each lab task to your Lab Instructor.
Paste your solution (i.e. code) in given space under each task.
CS 351 –Advance DBMS
1. Introduction to Relational Databases
A database is an organized collection of data that can be easily accessed, managed, and updated.
A Relational Database Management System (RDBMS) stores data in tables (relations). Each
table consists of rows (tuples) and columns (attributes).
Key concepts:
Relation (Table): A set of tuples with the same attributes.
Tuple (Row): A single record in a table.
Attribute (Column): A data field in the table.
Primary Key: A unique identifier for each tuple.
Foreign Key: An attribute that references the primary key of another table.
Examples of RDBMS: MySQL, Oracle, SQL Server, PostgreSQL.
2. Relational Algebra
Relational Algebra is the theoretical foundation of SQL. It provides a set of operations to
manipulate and query data stored in relations.
Fundamental operations:
Select (σ): Extracts rows that satisfy a condition.
Project (π): Extracts specific columns.
Union (∪): Combines tuples from two relations.
Set Difference (−): Tuples in one relation but not in another.
Cartesian Product (×): Combines every tuple of one relation with every tuple of
another.
Rename (ρ): Renames attributes or relations.
For example:
σ Age > 20 (Students) → Select students older than 20.
π Name, Dept (Students) → Project only Name and Dept attributes.
3. Structured Query Language (SQL)
SQL is the practical language for managing relational databases. It is based on relational algebra.
Types of SQL commands:
2
CS 351 –Advance DBMS
DDL (Data Definition Language): CREATE, ALTER, DROP
DML (Data Manipulation Language): INSERT, UPDATE, DELETE
DQL (Data Query Language): SELECT
Example:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Dept VARCHAR(20)
);
INSERT INTO Students VALUES (1, 'Ali', 20, 'CS');
INSERT INTO Students VALUES (2, 'Ayesha', 21, 'IT');
SELECT Name, Dept FROM Students WHERE Age > 20;
4. Normalization
Normalization is the process of organizing data in a database to minimize redundancy and
improve consistency.
1NF (First Normal Form):
o Each table cell must contain a single value.
o Each record must be unique.
2NF (Second Normal Form):
o Must be in 1NF.
o No partial dependency (non-key attributes should depend on the whole primary
key).
Example:
Unnormalized table (UNF):
StudentID Name Subjects
1 Ali Math, Physics
2 Sara CS, Math
1NF: Split multi-valued attributes into separate rows.
3
CS 351 –Advance DBMS
StudentID Name Subject
1 Ali Math
1 Ali Physics
2 Sara CS
2 Sara Math
2NF: If a composite key exists, ensure attributes depend on the whole key.
Lab Tasks
Task 1: Create and Populate Tables
Create the following tables using SQL:
1. Students(StudentID, Name, Age, DeptID)
2. Departments(DeptID, DeptName)
Insert at least 5 sample records into each table.
Task 2: SQL Queries (Relational Algebra in Practice)
Write SQL queries to:
1. Select all students from the CS department.
2. Project only the names and ages of students.
3. Find students older than 20.
4. Perform a join to display each student’s name with their department name.
Task 3: Normalization Practice
Given the following unnormalized table:
OrderID CustomerName Items
1 Ali Book, Pen
2 Ayesha Notebook
1. Convert this table into 1NF (remove multivalued attributes).
2. Write the SQL statements to create the normalized tables.
3. Insert the corresponding data into your tables.
