Normalization and Data Integrity
1. Introduction to Normalization
Normalization is a systematic process of organizing data in a database to:
Minimize data redundancy (repetition of data),
Ensure data integrity, and
Improve data consistency and efficient querying.
Normalization is usually performed in stages called normal forms (NF). Each stage refines the structure
of the database to make it more efficient and maintainable.
2. Objectives of Normalization
Eliminate redundant data: Avoid storing the same data in multiple places.
Ensure data dependencies make sense: Data is logically stored.
Improve data integrity: Reduce the chances of anomalies (insertion, update, and deletion
anomalies).
Facilitate easier maintenance: With fewer duplicates, data is easier to maintain and update.
3. Data Redundancy and Anomalies
Data Redundancy
Occurs when the same piece of data exists in multiple places. Redundancy can lead to:
Wasted storage space
Inconsistent data updates
Integrity issues
Types of Anomalies
Insertion anomaly: You can’t add data because other data is missing.
Update anomaly: You must change data in multiple places when a single item changes.
Deletion anomaly: Deleting a record causes unintended loss of data.
4. Normal Forms
4.1 First Normal Form (1NF)
Definition: A table is in 1NF if:
All the values in a column are atomic (indivisible),
Each column contains only one value per record (no repeating groups or arrays).
Example:
StudentID Name Courses
101 Alice Math, English
Not in 1NF because "Courses" has multiple values.
After 1NF:
StudentID Name Course
101 Alice Math
101 Alice English
4.2 Second Normal Form (2NF)
Definition: A table is in 2NF if:
It is in 1NF,
All non-key attributes are fully dependent on the entire primary key.
Applies mainly to tables with composite keys (i.e., more than one attribute in the primary key).
Example:
StudentID CourseID StudentName CourseName
Here:
StudentName depends only on StudentID
CourseName depends only on CourseID
Not in 2NF because non-key attributes do not depend on the full key.
After 2NF:
Student Table:
StudentID StudentName
Course Table:
CourseID CourseName
Enrollment Table:
StudentID CourseID
4.3 Third Normal Form (3NF)
Definition: A table is in 3NF if:
It is in 2NF,
There is no transitive dependency (i.e., non-key attributes do not depend on other non-key
attributes).
Example:
EmployeeID Name Department DeptLocation
If Department → DeptLocation, and EmployeeID → Department, then EmployeeID →
DeptLocation is a transitive dependency.
After 3NF:
Employee Table:
| EmployeeID | Name | Department |
Department Table:
| Department | DeptLocation |
4.4 Boyce-Codd Normal Form (BCNF)
Definition: A stricter version of 3NF. A table is in BCNF if:
It is in 3NF,
Every determinant is a candidate key.
A determinant is any attribute on which some other attribute is fully functionally dependent.
Example:
StudentID Course Instructor
If:
Each Course is taught by only one Instructor,
A student can enroll in many courses,
Then Course → Instructor, but StudentID, Course is the primary key.
This violates BCNF because Course (a non-candidate key) determines Instructor.
After BCNF:
Course Table:
| Course | Instructor |
Enrollment Table:
| StudentID | Course |
5. Data Integrity
Definition:
Data integrity ensures that data is accurate, consistent, and reliable throughout its lifecycle in a
database.
Types of Data Integrity
Type Description
Entity Integrity Ensures each table has a unique primary key and that it is not null.
Referential Integrity Ensures that foreign keys correctly refer to primary keys in related tables.
Domain Integrity Enforces valid entries for a given column using data type, format, or constraints.
Type Description
User-Defined Integrity Custom rules set by users/business logic (e.g., age must be ≥ 18).
Enforcing Data Integrity
Use of primary keys (uniqueness and not null)
Use of foreign keys (to enforce relationships)
Use of constraints like:
o NOT NULL
o UNIQUE
o CHECK
o DEFAULT
Use of triggers and stored procedures for complex validations
6. Relationship Between Normalization and Data Integrity
Normalization Benefit How it Ensures Integrity
Removes redundancy Prevents inconsistent data from multiple storage points
Splits data logically Ensures dependencies are respected
Uses keys and constraints Enforces entity and referential integrity
Avoids anomalies Guarantees consistent insertion, update, deletion
7. Summary Table of Normal Forms
Normal Form Requirement
1NF Atomic columns, no repeating groups
2NF 1NF + Full functional dependency on primary key
3NF 2NF + No transitive dependencies
BCNF 3NF + Every determinant must be a candidate key
SQL Basics – Data Definition Language (DDL)
1. What is DDL?
Data Definition Language (DDL) refers to SQL commands that define the structure of a database,
including:
Creating, modifying, and deleting databases, tables, and other objects like indexes, views, and
schemas.
Key DDL Commands
Command Purpose
CREATE Create databases and tables
ALTER Modify existing tables (add, remove, modify columns)
DROP Delete databases or tables completely
2. Creating Databases and Tables
2.1 CREATE DATABASE
sql
CopyEdit
CREATE DATABASE SchoolDB;
This creates a new database named SchoolDB.
2.2 CREATE TABLE
sql
CopyEdit
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT,
Email VARCHAR(100)
);
Creates a table Students with specified columns and a primary key on StudentID.
3. ALTER TABLE
Used to modify a table's structure after it has been created.
Examples:
Add a new column:
sql
CopyEdit
ALTER TABLE Students ADD Gender VARCHAR(10);
Modify a column:
sql
CopyEdit
ALTER TABLE Students MODIFY Age SMALLINT;
Drop a column:
sql
CopyEdit
ALTER TABLE Students DROP COLUMN Email;
4. DROP TABLE / DROP DATABASE
Deletes a table or database permanently (all data is lost).
Examples:
Drop a table:
sql
CopyEdit
DROP TABLE Students;
Drop a database:
sql
CopyEdit
DROP DATABASE SchoolDB;
⚠️Caution: DROP is irreversible and deletes everything related to the object.
Practical Task for Week 5
Try writing and executing the following SQL statements:
Create a database called CompanyDB
Create a table called Employees with fields: EmpID, Name, Position, Salary
Add a column HireDate to Employees
Delete the column Salary
Drop the entire Employees table
Week 6: SQL – Data Manipulation Language (DML)
1. What is DML?
Data Manipulation Language (DML) is used to manipulate and retrieve data from existing tables.
Key DML Commands
Command Purpose
SELECT Retrieve data
INSERT Add new data
UPDATE Modify existing data
DELETE Remove data from a table
2. SELECT – Retrieving Data
Basic Syntax:
sql
CopyEdit
SELECT column1, column2 FROM table_name;
Example:
sql
CopyEdit
SELECT FirstName, LastName FROM Students;
Select All Columns:
sql
CopyEdit
SELECT * FROM Students;
With WHERE Clause:
sql
CopyEdit
SELECT * FROM Students WHERE Age > 18;
3. INSERT – Adding Data
Syntax:
sql
CopyEdit
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
Example:
sql
CopyEdit
INSERT INTO Students (StudentID, FirstName, LastName, Age)
VALUES (101, 'John', 'Doe', 20);
4. UPDATE – Modifying Data
Syntax:
sql
CopyEdit
UPDATE table_name SET column1 = value1 WHERE condition;
Example:
sql
CopyEdit
UPDATE Students SET Age = 21 WHERE StudentID = 101;
⚠️Always use WHERE to avoid updating all rows.
5. DELETE – Removing Data
Syntax:
sql
CopyEdit
DELETE FROM table_name WHERE condition;
Example:
sql
CopyEdit
DELETE FROM Students WHERE StudentID = 101;
⚠️Without a WHERE clause, all records will be deleted.
Practical Exercises for Week 6
1. Insert 3 records into a Courses table.
2. Update one course's title or description.
3. Retrieve all courses offered.
4. Delete one course from the table.
5. Select students above a certain age.
Tips and Best Practices
Always backup before using DELETE or DROP.
Use NOT NULL, UNIQUE, DEFAULT, and CHECK constraints for integrity.
Combine SELECT with ORDER BY, GROUP BY, JOIN for complex queries.
Use comments (-- comment) to document SQL scripts.
Advanced SQL Queries
1. JOIN Operations
JOINs are used to combine rows from two or more tables based on a related column between them
(usually foreign key relationships).
1.1 INNER JOIN
Returns records that have matching values in both tables.
sql
CopyEdit
SELECT A.Name, B.CourseName
FROM Students A
INNER JOIN Courses B ON A.CourseID = B.CourseID;
Only returns students who are enrolled in a course (matched by CourseID).
1.2 LEFT JOIN (LEFT OUTER JOIN)
Returns all records from the left table and the matched records from the right. If there is no match,
NULLs appear.
sql
CopyEdit
SELECT A.Name, B.CourseName
FROM Students A
LEFT JOIN Courses B ON A.CourseID = B.CourseID;
Shows all students, even if not enrolled in any course.
1.3 RIGHT JOIN (RIGHT OUTER JOIN)
Returns all records from the right table and the matched records from the left.
sql
CopyEdit
SELECT A.Name, B.CourseName
FROM Students A
RIGHT JOIN Courses B ON A.CourseID = B.CourseID;
Shows all courses, even if no students are enrolled.
1.4 FULL JOIN (FULL OUTER JOIN)
Returns all records when there is a match in either table. Non-matching rows get NULLs.
sql
CopyEdit
SELECT A.Name, B.CourseName
FROM Students A
FULL JOIN Courses B ON A.CourseID = B.CourseID;
Combines LEFT and RIGHT JOIN. Shows all students and courses, matched or not.
✅ Note: Not all RDBMS (like MySQL) support FULL JOIN directly. You may need to simulate it using
UNION.
2. Aggregate Functions
Aggregate functions perform calculations on multiple rows and return a single result.
Function Description Example
COUNT() Counts the number of rows SELECT COUNT(*) FROM Students;
SUM() Adds numeric values SELECT SUM(Salary) FROM Employees;
AVG() Calculates average value SELECT AVG(Age) FROM Students;
MAX() Finds the highest value SELECT MAX(Salary) FROM Employees;
MIN() Finds the lowest value SELECT MIN(Salary) FROM Employees;
With GROUP BY
Used to group rows that share the same values, allowing aggregate functions to be applied to each
group.
sql
CopyEdit
SELECT CourseID, COUNT(*) AS StudentCount
FROM Students
GROUP BY CourseID;
Counts students enrolled in each course.
With HAVING
Used to filter groups, like WHERE filters rows.
sql
CopyEdit
SELECT CourseID, COUNT(*) AS StudentCount
FROM Students
GROUP BY CourseID
HAVING COUNT(*) > 5;
Shows only courses with more than 5 students.
Week 8: Database Security and User Management
1. Authentication vs. Authorization
Term Meaning
Authentication Verifying a user’s identity (login credentials)
Authorization Determining what actions or resources a user is allowed to access
2. User Management in SQL
You can create, modify, and remove users, and control their access permissions.
2.1 Creating a User
sql
CopyEdit
CREATE USER 'john'@'localhost' IDENTIFIED BY 'password123';
Creates a user named john who can connect from the local machine.
2.2 Granting Privileges
sql
CopyEdit
GRANT SELECT, INSERT, UPDATE ON SchoolDB.* TO 'john'@'localhost';
Allows user john to select, insert, and update on all tables in SchoolDB.
ALL PRIVILEGES: Grant everything.
ON dbname.tablename: Grant on a specific table.
ON .: Grant on all databases and tables.
2.3 Revoking Privileges
sql
CopyEdit
REVOKE UPDATE ON SchoolDB.Students FROM 'john'@'localhost';
Removes UPDATE permission from user john.
2.4 Deleting a User
sql
CopyEdit
DROP USER 'john'@'localhost';
Deletes the user account from the system.
3. Role Management (in supported DBMSs like PostgreSQL,
Oracle, MySQL 8+)
Creating and Using Roles
sql
CopyEdit
CREATE ROLE staff;
GRANT SELECT, INSERT ON SchoolDB.* TO staff;
GRANT staff TO 'john'@'localhost';
Makes permission management easier across multiple users.
4. Best Practices for Database Security
Use strong passwords and password policies.
Grant minimum required privileges (Principle of Least Privilege).
Regularly audit user activity and permissions.
Use encryption (TLS/SSL) for data in transit.
Keep database software updated with latest security patches.
Avoid using root/admin for daily operations.
Sample Security Report Activity (for case study)
List of users with access and their privileges.
Tables or resources accessed by each user.
Identify excessive permissions and recommend revocations.
Suggest strong authentication and encryption measures.
