Doubtclip is an AI tutor that turns any homework or exam doubt into a step-by-step whiteboard video lesson with diagrams, formulas, and voice explanation.

Who is the founder of Doubtclip?

Vinay Malav is the founder of Doubtclip (Founder, Doubtclip, based in Kota, Rajasthan, India). More: https://www.doubtclip.com/about

Which subjects and exams does it support?

It supports Physics, Chemistry, Mathematics/Calculus and related STEM doubts for school boards, JEE, NEET, and global high-school / undergrad practice.

How do I get a video solution?

Upload a photo of the question or paste the text, sign in, and Doubtclip generates a whiteboard-style explanation video you can watch and share.

Is Doubtclip available worldwide?

Yes. Doubtclip is a web app for students globally. Lessons are available in English and Hinglish.

A relation R(A, B, C, D) has primary key (A, B). If the functional dependency B → C exists, what normal form does this relation violate?

Second Normal Form (2NF). In 2NF, no non-prime attribute (C) can be functionally dependent on a proper subset of the candidate key (B alone instead of full key AB). This is a partial dependency violating 2NF.

What is the fundamental difference between `DELETE` and `TRUNCATE` commands in SQL?

`DELETE` is a logged DML statement that removes rows one-by-one with WHERE filters; `TRUNCATE` is a DDL statement that deallocates data pages instantly. `DELETE` logs individual row deletions and supports WHERE clauses, while `TRUNCATE` is a DDL statement that deallocates entire table extents at the storage engine level.

Computer Science · College · BCA, BTech & MCA

SQL Masterclass: DDL/DML, Joins & Database Normalization (1NF–BCNF)

Master Structured Query Language (SQL) and relational design: DDL vs DML commands, Inner/Left/Right/Outer Joins, and Step-by-Step Normalization from 1NF to BCNF.

1SQL Command Categories (DDL, DML, DQL, DCL, TCL)

SQL Masterclass: DDL/DML, Joins & Database Normalization (1NF–BCNF): Subject, Target Audience, Topic Core — SQL Masterclass: DDL/DML, Joins & Database Normalization (1NF–BCNF)
Master Structured Query Language (SQL) and relational design: DDL vs DML commands, Inner/Left/Right/Outer Joins, and Step-by-Step Normalization from 1NF to BCNF.
1Subject — Computer Science
2Target Audience — BCA, BTech & MCA Students
3Topic Core — Queries, Join Types & 1NF, 2NF, 3NF, BCNF

SQL (Structured Query Language)is the standardized declarative language for managing relational databases. SQL statements are categorized into functional sub-languages according to their role in the database lifecycle.

SQL Sub-Languages Taxonomy

Category	Full Name	Primary Purpose	Core Commands
DDL	Data Definition Language	Defines, modifies, or drops table schemas and database structures	$\text{CREATE}\$ , $\text{ALTER}\$ , $\text{DROP}\$ , $\text{TRUNCATE}\$ , $\text{RENAME}\$
DML	Data Manipulation Language	Modifies data stored inside existing tables	$\text{INSERT}\$ , $\text{UPDATE}\$ , $\text{DELETE}\$
DQL	Data Query Language	Retrieves data from one or more tables without mutation	$\text{SELECT}\$ (with $\text{WHERE}\$ , $\text{GROUP BY}\$ , $\text{HAVING}\$ , $\text{ORDER BY}\$ )
DCL	Data Control Language	Manages user privileges and security roles	$\text{GRANT}\$ , $\text{REVOKE}\$
TCL	Transaction Control Language	Manages execution state of atomic transactions	$\text{COMMIT}\$ , $\text{ROLLBACK}\$ , $\text{SAVEPOINT}\$

DDL
Full Name
Data Definition Language
Primary Purpose
Defines, modifies, or drops table schemas and database structures
Core Commands
$\text{CREATE}\$ , $\text{ALTER}\$ , $\text{DROP}\$ , $\text{TRUNCATE}\$ , $\text{RENAME}\$
DML
Full Name
Data Manipulation Language
Primary Purpose
Modifies data stored inside existing tables
Core Commands
$\text{INSERT}\$ , $\text{UPDATE}\$ , $\text{DELETE}\$
DQL
Full Name
Data Query Language
Primary Purpose
Retrieves data from one or more tables without mutation
Core Commands
$\text{SELECT}\$ (with $\text{WHERE}\$ , $\text{GROUP BY}\$ , $\text{HAVING}\$ , $\text{ORDER BY}\$ )
DCL
Full Name
Data Control Language
Primary Purpose
Manages user privileges and security roles
Core Commands
$\text{GRANT}\$ , $\text{REVOKE}\$
TCL
Full Name
Transaction Control Language
Primary Purpose
Manages execution state of atomic transactions
Core Commands
$\text{COMMIT}\$ , $\text{ROLLBACK}\$ , $\text{SAVEPOINT}\$

sql· SQL DDL & DML Schema Creation and Aggregation Query

-- DDL: Create Tables with Foreign Key Constraint
CREATE TABLE Departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE Employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) CHECK (salary > 0),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

-- DQL: Aggregate query with HAVING filter
SELECT d.dept_name, COUNT(e.emp_id) AS total_staff, AVG(e.salary) AS avg_sal
FROM Departments d
JOIN Employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name
HAVING AVG(e.salary) > 60000
ORDER BY avg_sal DESC;

Output:

+-------------+-------------+----------+
| dept_name   | total_staff | avg_sal  |
+-------------+-------------+----------+
| Engineering | 14          | 92500.00 |
| Product     | 6           | 78000.00 |
+-------------+-------------+----------+

2Relational Joins: Inner, Left, Right & Full Outer

AJOINclause combines rows from two or more tables based on a related common column between them. Relational algebra sets determine whether unmatched rows are retained or excluded.

SQL Join Types Matrix

Join Type	Relational Output Behavior	Unmatched Left Rows?	Unmatched Right Rows?
INNER JOIN	Returns rows only when there is a match in both tables	Excluded	Excluded
LEFT OUTER JOIN	Returns all rows from left table, plus matched rows from right (fills NULL for missing right)	Retained	Excluded (unless matched)
RIGHT OUTER JOIN	Returns all rows from right table, plus matched rows from left (fills NULL for missing left)	Excluded (unless matched)	Retained
FULL OUTER JOIN	Returns all rows when there is a match in either table (combines LEFT and RIGHT joins)	Retained (with NULLs)	Retained (with NULLs)
CROSS JOIN	Cartesian product: combines every row of Table A with every row of Table B (Size: m × n)	N/A	N/A

INNER JOIN
Relational Output Behavior
Returns rows only when there is a match in both tables
Unmatched Left Rows?
Excluded
Unmatched Right Rows?
Excluded
LEFT OUTER JOIN
Relational Output Behavior
Returns all rows from left table, plus matched rows from right (fills NULL for missing right)
Unmatched Left Rows?
Retained
Unmatched Right Rows?
Excluded (unless matched)
RIGHT OUTER JOIN
Relational Output Behavior
Returns all rows from right table, plus matched rows from left (fills NULL for missing left)
Unmatched Left Rows?
Excluded (unless matched)
Unmatched Right Rows?
Retained
FULL OUTER JOIN
Relational Output Behavior
Returns all rows when there is a match in either table (combines LEFT and RIGHT joins)
Unmatched Left Rows?
Retained (with NULLs)
Unmatched Right Rows?
Retained (with NULLs)
CROSS JOIN
Relational Output Behavior
Cartesian product: combines every row of Table A with every row of Table B (Size: m × n)
Unmatched Left Rows?
N/A
Unmatched Right Rows?
N/A

3Database Normalization: 1NF, 2NF, 3NF & BCNF

Definition

Database Normalization — The formal mathematical process of decomposing tables with redundancies into well-structured tables to eliminate update, insertion, and deletion anomalies.

Normal Form Progression Steps

1
Unnormalized (UNF)
Tables contain repeating groups or multi-valued attributes (e.g. Phone = "9876, 5432")
2
1st Normal Form (1NF)
All attributes contain strictly atomic (indivisible) scalar values; each record is unique
3
2nd Normal Form (2NF)
Must be in 1NF AND eliminate partial functional dependencies (all non-key attributes fully dependent on whole primary key)
4
3rd Normal Form (3NF)
Must be in 2NF AND eliminate transitive functional dependencies (no non-key attribute depends on another non-key attribute: X → Y → Z)
5
Boyce-Codd (BCNF)
Strict 3NF: For every functional dependency X → Y, the determinant X must strictly be a Super Key

Exam tip: In enterprise systems, databases are typically normalized up to3NF / BCNFfor OLTP (Online Transaction Processing) to guarantee transactional integrity, and intentionallydenormalized(star/snowflake schemas) for OLAP data warehouses to accelerate analytical reads.

Remember

SQL commands are segregated into DDL (structure), DML (records), DQL (retrieval), DCL (permissions), and TCL (transactions).
INNER JOIN extracts intersections; LEFT/RIGHT/FULL OUTER JOINs preserve records with missing relationships using NULL values.
1NF demands atomic values; 2NF removes partial key dependencies; 3NF removes transitive dependencies (non-key determining non-key).
BCNF is a stricter form of 3NF where every functional dependency determinant must be a super key.

Check yourself

Question 1
A relation R(A, B, C, D) has primary key (A, B). If the functional dependency B → C exists, what normal form does this relation violate?
Question 2
What is the fundamental difference between $\text{DELETE}\$ and $\text{TRUNCATE}\$ commands in SQL?

1SQL Command Categories (DDL, DML, DQL, DCL, TCL)

2Relational Joins: Inner, Left, Right & Full Outer

3Database Normalization: 1NF, 2NF, 3NF & BCNF

Remember

Check yourself

More Computer Science explained