Your cart is empty.
Keep shopping
Teach on Udemy
Turn what you know into an opportunity and reach millions around the world.
Learn MoreComplete Database & SQL Masterclass | RDBMS, Practical SQL
Rating: 4.4 out of 54.4(7,339 ratings)
22,162 students
Complete Database & SQL Masterclass | RDBMS, Practical SQL
Master SQL, DB Design, ER Modeling, Normalization, Transactions, Indexing, Views, Stored Procedures and Data Warehousing
Created byAtchyut Kumar, Edufulness EFN
Last updated 7/2026
English
English [Auto],French [Auto],
What you'll learn
- Describe the fundamental elements of relational database management systems.
- Explain the basic concepts of relational data model, entity-relationship model, relational database design, relational algebra and SQL.
- Design ER-models to represent simple database application scenarios
- Improve the database design by normalization.
- Concurrency control( from scratch to advance).
- Database indexing ( B and B+ Trees).
- Able to create complex SQL queries using multiple tables.
- In-depth knowledge over database design or database engineer.
- Learn core concepts of database design.
- Capable to answer any level of competitive exams like GATE, ISRO, PSU's or any other.
- Write Efficient SQL Queries – Use DDL, DML, subqueries, joins, views, and indexing for performance optimization.
- Work with Microsoft Fabric SQL – Understand schemas, stored procedures, CTEs, temp tables, and string aggregate functions
Explore related topics
Course content
23 sections • 211 lectures • 24h 24m total length
- Types of keys.2:44
Summary – Types of Keys in DBMS
Keys are a set of one or more attributes used to uniquely identify each row (tuple) in a relation (table).
Keys also help establish relationships between tables.
There are five primary types of keys commonly used in DBMS:
Candidate Key
Primary Key
Alternate Key
Super Key
Foreign Key
Candidate Key, Primary Key, Alternate Key, and Super Key are closely related concepts and will be studied individually.
Foreign Key is used to create and maintain relationships between tables.
Another commonly used key is the Surrogate Key.
Surrogate Key is a system-generated key that is created when no suitable Candidate Key is available.
The meaning and usage of each key type become clearer after understanding them individually, so they are typically covered in separate lessons.
Key Takeaway
Keys uniquely identify records and help establish relationships between tables. Understanding the different types of keys is fundamental to learning relational databases and SQL.
- Candidate Key6:07
Candidate Key – Summary
A Candidate Key is the minimum set of attribute(s) that can uniquely identify each row (tuple) in a table.
A Candidate Key must satisfy two conditions:
Uniqueness – Every value (or combination of values) must be unique; duplicate values are not allowed.
Not NULL – Every record must have a value; NULL values are not allowed.
The word "minimum" means that no unnecessary attribute is included. If any attribute is removed, it should no longer uniquely identify the record.
A table can have one or more Candidate Keys.
Example
Consider the following Employee table:
Emp_NoEmp_NameAadhaar_NoDriving_License_No101Rahul1234-5678-9012DL12345102Priya2345-6789-0123DL23456103Amit3456-7890-1234DL34567
Possible Candidate Keys:
Emp_No ✅ (Unique and NOT NULL)
Aadhaar_No ✅ (Unique and NOT NULL)
Driving_License_No ✅ (Unique and NOT NULL)
Not Candidate Keys:
Emp_Name ❌ (Different employees can have the same name.)
Any attribute containing duplicate or NULL values ❌
Prime and Non-Prime Attributes
Prime Attributes: Attributes that are part of any Candidate Key.
Example: Emp_No, Aadhaar_No, Driving_License_No
Non-Prime Attributes: Attributes that are not part of any Candidate Key.
Example: Emp_Name
Key Takeaway
A Candidate Key is the smallest possible attribute (or combination of attributes) that uniquely identifies every record in a table. It must be unique, cannot contain NULL values, and a table may have multiple Candidate Keys.
- Primary Key2:40
Primary Key – Summary
A Primary Key is the Candidate Key selected to uniquely identify each record in a table.
Since a Primary Key is chosen from the Candidate Keys, it also satisfies the Candidate Key properties:
Unique – No duplicate values are allowed.
NOT NULL – Every row must have a value.
A table can have multiple Candidate Keys, but it can have only one Primary Key.
If a table has only one Candidate Key, it automatically becomes the Primary Key.
If there are multiple Candidate Keys, the database designer selects one as the Primary Key based on convenience, simplicity, and business requirements.
The chosen Primary Key should be:
Stable (rarely changes)
Easy to use and reference
Compact and meaningful for database operations
Example
Consider the following Employee table:
Emp_NoEmp_NameAadhaar_NoDriving_License_No101Rahul1234-5678-9012DL12345102Priya2345-6789-0123DL23456103Amit3456-7890-1234DL34567
Candidate Keys:
Emp_No ✅
Aadhaar_No ✅
Driving_License_No ✅
Among these, we choose Emp_No as the Primary Key because it is:
Short and easy to reference
Stable
Convenient for database operations
Relationship Between Candidate Key and Primary Key
Every Primary Key is a Candidate Key.
Not every Candidate Key becomes the Primary Key.
Key Takeaway
A Primary Key is the single Candidate Key chosen to uniquely identify every record in a table. It must be unique, cannot contain NULL values, and every table can have only one Primary Key.
- Alternate Key1:17
Alternate Key – Summary
An Alternate Key is a Candidate Key that is not selected as the Primary Key.
A table can have multiple Candidate Keys, but only one of them is chosen as the Primary Key — the remaining Candidate Keys automatically become Alternate Keys.
Example: In an Employee table with Candidate Keys – Emp_Number, Aadhaar_Number, Driving_License:
If Emp_Number is selected as the Primary Key,
then Aadhaar_Number and Driving_License become Alternate Keys.
Simple relationship: Alternate Keys = Candidate Keys − Primary Key.
Since Alternate Keys are Candidate Keys, they also satisfy the properties of Uniqueness and NOT NULL.
Alternate Keys can still be used to uniquely identify records and serve different practical purposes in the database.
Key Takeaway: Any Candidate Key other than the selected Primary Key is called an Alternate Key.
- Super Key4:09
Super Key – Summary
A Super Key is any set of one or more attributes whose values can uniquely identify each row (tuple) in a table.
If a set of attributes generates uniqueness for every record, that set is a Super Key — it does not need to be minimal.
Forming Super Keys: adding zero or more attributes to a Candidate Key always produces a Super Key.
Example: If Emp_Number is a Candidate Key, then Emp_Number, (Emp_Number, Ename), (Emp_Number, Dept_No), etc. are all Super Keys.
Important relationship:
Every Candidate Key is a Super Key (a Candidate Key is simply a minimal Super Key).
But every Super Key is NOT a Candidate Key, because a Super Key may contain unnecessary (extra) attributes.
Overall picture: Candidate Keys ⊂ Super Keys; one Candidate Key is chosen as the Primary Key and the remaining ones become Alternate Keys.
Key Takeaway: Any attribute set that guarantees uniqueness is a Super Key; the minimal ones among them are the Candidate Keys.
- Foreign Key6:07
Foreign Key – Summary
A Foreign Key is a column (or set of columns) in one table that refers to the Primary Key of another table (or, in some cases, of the same table).
Intuition: just like a person outside their native place is a "foreigner", the values of a Foreign Key column are not native to that table — their origin (source) is another table.
Example:
Employee table: Emp_No (Primary Key), Ename, Manager_ID, Dept_No.
Department table: Dept_No (Primary Key), Dept_Name, Location.
Dept_No in the Employee table is a Foreign Key, because its values come from the Primary Key of the Department table.
Parent and Child tables:
Parent table – the source of the values (here, Department).
Child table – the table using those values as a Foreign Key (here, Employee).
A Foreign Key can also refer to a Candidate Key of another table — it need not always be the Primary Key.
Self-referencing Foreign Key: a Foreign Key can refer to the Primary Key of the same table — e.g., Manager_ID in the Employee table refers to Emp_No of the same table (a manager is also an employee).
Foreign Keys are used to establish relationships between tables and maintain referential integrity.
Key Takeaway: A Foreign Key binds one table to another by referring to the Primary (or Candidate) Key of the parent table.
- Test 1 : Keys
- Section Summary : Keys1:02
- Introduction.4:37
ER Model : Introduction – Summary
The Entity-Relationship (ER) Model describes the structure of a database with the help of diagrams, known as ER Diagrams.
It represents the logical structure of a database — a design blueprint, just like the plan of a house prepared before construction.
Main components of the ER Model:
Entities – Strong (regular) and Weak entities.
Attributes – Key, Simple, Composite, Single-valued, Multi-valued, Stored, and Derived attributes.
Relationships – One:One (1:1), One:Many (1:M), Many:One (M:1), Many:Many (M:M).
We also study Partial Participation and Total Participation of entities in relationships.
Finally, using all these concepts, we learn to construct ER diagrams (blueprints) for real-world database requirements.
Key Takeaway: The ER Model is the blueprint of a database, built from entities, attributes, and relationships.
- Entity : Strong and Weak Entity.6:39
Entity : Strong and Weak Entity – Summary
An Entity is an object or component of data — in simple terms, usually a table (a set of records with columns). Examples: Student, College, Employee.
An entity is represented by a rectangle in an ER diagram; each row of the table is an entity instance.
Strong Entity:
Has a key attribute (Primary/Candidate Key) that uniquely identifies each instance — e.g., Roll_Number in Student.
Represented by a single rectangle.
Weak Entity:
Cannot be uniquely identified by its own attributes — it has no Primary Key of its own.
It relies on a relationship with another (strong) entity for identification.
Represented by a double rectangle.
Example: Bank Account — account numbers alone may repeat across banks (ICICI 1,2,3… and HDFC 1,2,3…), so the account entity depends on the Bank entity.
Key Takeaway: An entity with its own key is strong; an entity without one, depending on another entity, is weak.
- Attribute : Key attribute, Simple and Composite Attributes.6:16
Attribute : Key, Simple and Composite Attributes – Summary
An Attribute describes a property of an entity — simply a column of the table (Name, Salary, Age…). Rows are tuples; columns are attributes.
Attributes are represented by an oval (bubble) connected to the entity in an ER diagram.
Key Attribute:
Uniquely identifies each entity instance from the entity set — essentially the Primary Key (e.g., Roll_Number in Student; Name/Age/Address can have duplicates).
Shown with its name underlined in the ER diagram.
Simple Attribute: cannot be divided into smaller components — e.g., Age.
Composite Attribute: a combination of other attributes — it can be divided further:
Address → State, City, Pincode.
Name → First Name, Middle Name, Last Name.
Key Takeaway: Attributes are columns; the key attribute is underlined, simple attributes are indivisible, and composite attributes can be split further.
- Attribute : Single and Multi-valued Attributes & Stored and Derived Attributes.4:23
Attribute : Single/Multi-valued & Stored/Derived – Summary
Single-valued Attribute: takes only one value per entity instance — e.g., Age of a student.
Multi-valued Attribute: can take more than one value — e.g., a student may have two phone numbers or two email IDs. Represented by a double oval.
Stored Attribute: a value stored permanently that does not change — e.g., Date of Birth.
Derived Attribute: calculated from other attributes — e.g., Age = Current Year − Date of Birth. Represented by a dashed (dotted) oval.
A single Student entity diagram can show all types together: Roll_No (key, underlined), Address (composite), Mobile_No (multi-valued), DOB (stored), Age (derived).
Key Takeaway: Single vs multi-valued is about how many values; stored vs derived is about where the value comes from.
- Relationship : Introduction, Weak Relationship.3:32
Relationship : Introduction & Weak Relationship – Summary
A Relationship is an association between entities — e.g., Employee works for Organization.
Represented by a diamond symbol connecting the entities in the ER diagram.
A relationship can connect two or more entities (more than two is covered under degree of relationship).
Strong Relationship: between two strong entities — drawn with a single diamond.
Weak Relationship:
When one of the participating entities is a weak entity, the relationship is weak.
Drawn with a double diamond — e.g., Bank ↔ Bank Account (Bank Account is a weak entity).
Key Takeaway: Relationships (diamonds) associate entities; if a weak entity participates, it becomes a weak relationship (double diamond).
- Relationship : Degree - Unary, Binary, Ternary and n-ary Relationships.3:34
Relationship Degree : Unary, Binary, Ternary, n-ary – Summary
Degree of a relationship = number of entities participating in that relationship.
Unary (degree 1): only one entity involved — e.g., an Employee is the manager of another Employee (Manager_ID refers to Emp_No in the same table).
Binary (degree 2): two entities involved — e.g., Employee works for Department. Most common in practice.
Ternary (degree 3): three entities involved in one relationship.
n-ary (degree n): n entities participate in the relationship.
Key Takeaway: Count the entities connected to a relationship — that count is its degree.
- Relationship:Cardinality-One:One(1:1),One:Many(1:M),Many:One(M:1),Many:Many(M:M)7:54
Relationship Cardinality : 1:1, 1:M, M:1, M:M – Summary
Cardinality describes the numerical relationship between instances of two entities — how records of one entity connect to records of another.
One : One (1:1): a single instance relates to a single instance — e.g., one Person has one Passport, and a passport belongs to only one person.
One : Many (1:M): one instance relates to many — e.g., one Customer places many Orders.
Many : One (M:1): the reverse direction — e.g., many Students study in one College. (Order → Customer is M:1, while Customer → Order is 1:M — direction matters.)
Many : Many (M:M): many instances relate to many — e.g., many Students enroll in many Courses.
Key Takeaway: Cardinality = how many instances participate on each side: 1:1, 1:M, M:1, or M:M, and it depends on the direction you read the relationship.
- Relationship : Partial and Total Participation & Cardinality Ratio.8:33
Partial & Total Participation, Cardinality Ratio – Summary
For every entity in a relationship we calculate minimum and maximum participation — the least and most number of times an entity instance participates.
Minimum cardinality = 0 → some instances do not participate → Partial Participation (single line to the relationship).
Minimum cardinality ≥ 1 → every instance participates at least once → Total Participation (double line to the relationship).
Maximum cardinality is the highest number of times any instance participates — it decides the cardinality ratio side (1 or Many).
Example: if instances of Entity A participate 1–2 times (min 1, max 2) and Entity B has some non-participating instances with max 3 (min 0, max 3), then A has total participation and B has partial participation.
Key Takeaway: Min = 0 → partial; min ≥ 1 → total. Max participation values give the cardinality ratio (1:1, 1:M, M:M).
- Example 1,2 : Min,Max Cardinality, Partial & Total Participation, Relationship.8:21
Example 1, 2 : Min/Max Cardinality & Participation – Summary
Example 1 – Reading a given diagram (Project–Employee, M:M):
Project side (1, 10): every project must have at least 1 employee → minimum = 1 → Total Participation; a project can have at most 10 employees.
Employee side (0, 4): an employee need not participate in any project → minimum = 0 → Partial Participation; an employee can work on at most 4 projects.
Both directions allow "many" (Employee→Project 1:M and Project→Employee 1:M) → overall Many : Many (M:M) relationship.
Example 2 – Constructing the diagram from statements:
"Each project must have minimum 2, maximum 15 employees" → project side is total participation (minimum ≥ 1); maximum = 15.
"Every employee must be part of exactly one project" → employee side (1, 1) → total participation.
Project → Employee is 1 : M (a project has many employees); Employee → Project is 1 : 1 → overall the relationship is One : Many.
Key Takeaway: Read (min, max) pairs carefully — min = 0 → partial, min ≥ 1 → total participation; the max values on each side decide whether the relationship is 1:1, 1:M, or M:M.
- Test 2: ER Model
- Section Summary : ER Model0:59
- Functional Dependency : Introduction, Examples13:24
Functional Dependency : Introduction – Summary
A Functional Dependency (FD) is a relationship among the attributes of a relation — the foundation concept for Normalization.
Written as X → Y: attribute set X (determinant) derives attribute set Y (dependent).
Meaning: whenever the same value of X appears, it must derive the same value of Y — in every row of the relation.
Example: if for every occurrence of x1 the Y value is always y1 (and similarly for x2, x3 …), then X → Y is a valid FD.
Invalid FD: if even one value of X derives two different Y values (x1 → y1 in one row but x1 → y2 in another), then X → Y does not hold.
Key Takeaway: X → Y holds only when equal X values always give equal Y values; X is the determinant, Y is the dependent.
- Types of Functional Dependencies : Trivial and Non-Trivial FD's.3:41
Trivial and Non-Trivial FDs – Summary
Functional Dependencies are of two types: Trivial and Non-Trivial.
Trivial FD (A → B): when B is a subset of A (or equal to A).
Examples: AB → A, ABC → BC, (Emp_ID, Emp_Name) → Emp_ID.
Always valid automatically — they give no new information.
Non-Trivial FD (A → B): when B is NOT a subset of A (A ∩ B is empty, or B has attributes outside A).
Examples: A → B, AB → C, Emp_ID → Emp_Name.
These are the useful FDs for analysis and normalization.
Key Takeaway: If the right side is contained in the left side, the FD is trivial; otherwise it is non-trivial (the useful kind).
- FD : Example.6:19
FD : Example (Counting Functional Dependencies) – Summary
Question: for a relation with two attributes (A, B), how many Functional Dependencies can be formed?
Possible left/right sides from {A, B}: A, B, AB (and the empty set) — each side can be any subset of the attributes.
Listing all combinations (A→A, A→B, A→AB, B→A, B→B, AB→A, AB→B, AB→AB …) gives 2ⁿ × 2ⁿ total combinations for n attributes — for n = 2, that is 16 possible FDs.
Among them, dependencies like A→A, B→B, AB→A, AB→AB are trivial (right side ⊆ left side).
The useful (non-trivial) ones here are essentially A → B and B → A.
Key Takeaway: With n attributes, 2ⁿ × 2ⁿ FD combinations exist (trivial + non-trivial, valid + invalid); only the non-trivial ones matter in practice.
- Inference Rules7:42
Inference Rules (Armstrong's Axioms) – Summary
Inference rules are assertions applied to a given set of FDs to derive new valid FDs.
1. Reflexivity: if Y ⊆ X, then X → Y (e.g., ABC → AB) — gives trivial FDs.
2. Augmentation: if X → Y, then XZ → YZ for any attribute set Z (e.g., A → B gives AC → BC).
3. Transitivity: if X → Y and Y → Z, then X → Z.
4. Union: if X → Y and X → Z, then X → YZ.
5. Decomposition: if X → YZ, then X → Y and X → Z (reverse of union).
These rules are used again and again — in attribute closure, finding keys, checking equivalence of FD sets, and minimal covers.
Key Takeaway: Reflexivity, Augmentation and Transitivity (with Union and Decomposition) let you derive every valid FD from a given FD set.
- Attribute Closure : Example 27:17
Attribute Closure : Example 2 – Summary
Attribute Closure X⁺ = the set of all attributes that X can derive using the given FDs.
Procedure:
Start the set with X itself (every attribute set derives itself).
Scan each FD: if the FD's left side is already inside the set, add its right-side attributes to the set.
Repeat the scan again and again — earlier-skipped FDs may now apply.
Stopping rule: when iteration i and iteration i+1 produce the same set (no new attribute added), the closure is complete.
Worked in this lecture: closures like (AB)⁺, (AE)⁺, (DC)⁺, (BE)⁺ — each built step by step with the given FD set.
Key Takeaway: X⁺ = start with X, keep adding right sides of FDs whose left sides are inside the set, stop when the set stops growing.
- Attribute Closure : Example 38:11
Attribute Closure : Example 3 (Counting all FDs) – Summary
Goal: find the total number of FDs (trivial + non-trivial) possible from a relation with a given FD set.
Method: take every subset of attributes (all combinations: A, B, C, AB, AC, BC, ABC …) and compute its closure.
For each subset X: X can functionally determine any subset of its closure X⁺ — that gives 2^|X⁺| FDs from X.
Example: if A⁺ = {A, B, C} then A gives 2³ = 8 FDs; if C⁺ = {C} then C gives 2¹ = 2 FDs.
Total = sum of 2^|closure| over all attribute subsets.
Key Takeaway: Compute the closure of every attribute combination; each contributes 2^(closure size) FDs; add them up for the total.
- Attribute Closure : Example 44:22
Attribute Closure : Example 4 (Counting all FDs) – Summary
Another practice problem: given two FDs, find the total number of possible FDs from the relation.
Compute the closure of every attribute subset:
A⁺ = all attributes (A derives everything) → contributes 2³ = 8.
B⁺ = {B} and C⁺ = {C} — B and C appear only on the right side of the FDs, so they derive nothing extra → each contributes 2¹ = 2.
Two-attribute sets containing A (AB, AC) derive everything → 2³ = 8 each; BC⁺ = {B, C} → 2² = 4; ABC⁺ = all → 2³ = 8.
Shortcut: once one member of a set derives everything, the whole set's closure is everything — combine (union) individual closures.
Adding all contributions gives the total (here 41 FDs), which includes trivial and non-trivial ones.
Key Takeaway: Total FDs = Σ 2^(closure size) across all subsets; attributes appearing only on right sides have singleton closures.
- Applications of Attribute Closure3:45
Applications of Attribute Closure – Summary
Attribute closure is the working tool for four important applications, all central to normalization:
1. Finding additional FDs — derive new meaningful FDs from the given set (beyond what is explicitly listed).
2. Finding keys of a relation — identify all possible Candidate Keys (and hence Super Keys and the Primary Key): if X⁺ = all attributes, X is a super key; if minimal, a candidate key.
3. Checking equivalence of FD sets — when two different FD sets exist for the same relation, closures decide whether they are equal, or one covers the other.
4. Finding the irreducible (minimal / canonical) set of FDs — eliminate duplicate and redundant FDs so that no further reduction is possible.
Key Takeaway: Closure is used to find extra FDs, keys, FD-set equivalence, and minimal covers — the four pillars of FD analysis.
- Application 1 : Finding additional FD's3:07
Derive additional functional dependencies from given functional dependencies by computing closures and applying decomposition rules to test validity on a relation with attributes A, B, C, and D.
- Application 2 : Finding keys in a relation - Example 1 & 2.8:27
Compute closures to identify candidate keys; a set that determines all attributes is a superkey, and removing any attribute reveals a minimal candidate key.
- Application 2 : Finding keys in a relation - Example 3 & 4.3:49
Learn how to find candidate keys in a relation with four attributes using given functional dependencies, by identifying attributes not present in any FD and exploring superkeys.
- Application 2 : Finding keys in a relation - Example 5 & 6.2:57
Apply the candidate key search by testing attribute subsets; observe that each individual attribute can be a candidate key, and the pair b and c also qualifies.
- Application 2 : Finding keys in a relation - Example 7 & 8.2:24
Identify candidate keys in a relation by evaluating attribute subsets and computing their closures to determine all attributes. Reveal how specific subsets yield a single candidate key.
- Application 2 : Finding keys in a relation - Example 9 & 10.5:12
Identify candidate keys in two examples by analyzing how attributes determine all attributes, noting prime attributes and functional dependencies, and showing multiple candidate keys.
- Application 2 : Finding keys in a relation - Example 11 & 12.5:31
The lecture demonstrates how to identify candidate keys for a relation by testing attribute combinations. It shows four candidate keys, including acb, pcb, and pcd.
- Application 2 : Finding keys in a relation - Example 13 & 14.4:37
This example shows how to identify candidate keys in a relation, revealing three keys such as a c d and b c d, with e dependent on c and d.
- Find candidate keys.
- Application 3 : Equivalence of Functional Dependencies - Example 17:32
Evaluate the equivalence of functional dependencies by comparing closures, verify mutual implication, and prefer the fd set with fewer dependencies for implementation.
- Application 3 : Equivalence of Functional Dependencies - Example 23:32
Explore the equivalence of two sets of functional dependencies in a relation, using example 2 from application 3, and determine how to verify when two dependency sets are identical.
- Application 3 : Equivalence of Functional Dependencies - Example 32:21
This lecture presents example 3 on the equivalence of functional dependencies, showing how to derive attributes like B and C from a given set and why they are not equivalent.
- Application 3 : Equivalence of Functional Dependencies - Example 44:30
Explore the equivalence of functional dependencies by analyzing attribute closures and covers in a given relation, determining which dependencies hold and when one side is sufficient to derive others.
- Application 4 : Irreducible set and Algorithm.6:53
Present an algorithm for irreducible sets and the minimal cover of functional dependencies, detailing decomposition, essential attribute tests, and closure computation.
- Application 4 : Irreducible set - Example 17:32
Explains deriving the canonical form of a relation through stepwise decomposition, checking essential attributes, analyzing functional dependencies, and obtaining minimal sets by considering closures and subsets.
- Application 4 : Irreducible set - Example 2.7:39
Identify essential and nonessential attributes in the irreducible set example from the WXYZ relation, iteratively eliminate nonessentials, compute closures to test generation, and derive the final canonical form.
- Partial Functional Dependency and Full Functional Dependency.10:21
This lecture explains partial functional dependency, where a proper subset of X determines a non-key attribute, and full functional dependency, where an attribute depends on the whole candidate key.
- Anomalies : Update, Deletion and Insertion Anomaly.13:07
Explore update, deletion, and insertion anomalies in unnormalized tables, highlighting redundancy and data inconsistencies, and see how normalization splits tables to enforce consistent relationships between employees and departments.
- Functional Dependency - Summary1:12
- Normalization : Introduction and types.6:29
Explore normalization as a data organization process that reduces redundancy and anomalies by applying 1NF, 2NF, 3NF, and BCNF, guided by functional dependencies and atomic values.
- First Normal Form (1NF).5:16
Define first normal form by ensuring all table cells hold atomic, single-valued attributes, removing multi-valued and composite attributes, and restructuring data like multiple phone numbers into separate records.
- Second Normal Form ( 2NF).11:40
Learn second normal form by ensuring every non-key attribute fully depends on the primary key, removing partial dependencies, and decomposing tables into patient and drug relations to reduce redundancy.
- 2NF : Examples - 1, 2, 3, 4 and NOTE.11:31
Practice solving 2NF examples by identifying candidate keys and exploring functional dependencies, including partial and full dependencies, to decompose relations into second normal form.
- Third Normal Form ( 3NF).10:22
Apply third normal form by eliminating partial dependencies and reducing redundancy. This minimizes insertion and deletion anomalies by dividing data into related tables with appropriate keys.
- 3NF : Examples - 1, 2.7:03
Examine third normal form through several examples, identify primary keys and non-key attributes, resolve partial dependencies, and decompose a relation into smaller tables to achieve 3nf.
- 3NF : Example - 3.10:36
Decompose a complex relation to third normal form by identifying keys, analyzing partial and full functional dependencies, and creating separate tables for dependencies, keys, and non-key attributes.
- 3NF : Example - 4.3:57
Analyze a third normal form example by identifying candidate keys and prime attributes, and verify second and third normal forms via functional and partial dependencies.
- Boyce Codd Normal Form ( BCNF).7:36
Learn BCNF, a stricter form than 3NF where every determinant is a superkey. Note how functional dependencies and candidate keys shape relations and reduce redundancy.
- Normalization - Summary1:20
- SELECT statement structure and evaluation order.9:56
Master the select statement structure and evaluation order, retrieving data from tables using from and where, then applying group by, having, and order by, plus distinct and set operators.
- SELECT clause Syntax and examples.11:18
Master select statements to retrieve data from the EMV table, choose specific columns or use *, control display order, and handle nulls, salaries, and commissions.
- DISTINCT keyword in SELECT clause.5:28
Apply the distinct keyword in the select clause to remove duplicates and return only unique rows or unique column combinations.
- WHERE clause : Introduction.5:39
Explore the where clause introduction by filtering records with conditions, selecting specific columns or all columns, and using operators like equals to extract department 10 results.
- WHERE clause : Operators.7:55
Explore how to use the where clause operators in SQL queries, including greater than, less than, equals, and not equal, with practical examples for salaries and job categories.
- WHERE clause : Logical Operators - AND, OR, NOT.11:46
Learn how to use the where clause with the logical operators and, or, and not to filter records using multiple conditions, including negation and optimization that may skip remaining conditions.
- WHERE clause : AND, OR, NOT - Together.7:23
Explore how to use the where clause with and, or, and not to filter employees by salesmen status, salary and department, including negations and combinations.
- ORDER BY clause : ASC, DESC7:48
Explore the order by clause to sort query results in ascending or descending order. Understand how multiple columns provide tie-breaker and default ascending behavior.
- LIKE and Wildcard Operators.11:54
Explore how the like operator and wildcards % and _ filter string patterns in where clauses, including starts with, ends with, contains, and specific position patterns.
- NULL values and IS NULL & IS NOT NULL.6:49
Understand null values in sql tables, learn how to test for nulls with is null and is not null, and filter records using is null in the where clause.
- Aggregate Functions : SUM(), AVG(), MIN(), MAX().12:26
Aggregate functions take a set of values and return a single result, using sum, avg, min, max, and distinct, often with group by and having, working on numbers and text.
- Aggregate Functions : COUNT().4:22
Explore aggregate functions with count: use count(*), count(column), and count(distinct column) to count records, handle nulls, and remove duplicates in SQL queries.
- GROUP BY clause.9:44
Explore how the group by clause works with select statements to group rows by department numbers and apply aggregate functions such as count, sum, average, min, and max.
- GROUP BY clause : Multiple columns6:00
Explore how the group by clause handles multiple columns, forming distinct groups by department number and job, and apply aggregates such as count, min, max, and average on each group.
- HAVING clause and rules.5:01
The having clause filters grouped results created by group by, using a condition on each group, with examples like departments having more than four employees.
- Set Manipulation Operators : Rules.5:35
Apply set manipulation operators to relations by enforcing equal attribute counts, compatible corresponding domains, and automatic duplicate elimination for union, intersection, and minus.
- Set Manipulation Operators : UNION, INTERSECTION, MINUS.9:39
Explore union, intersection, and minus set operators in relational queries, learn the compatibility rules, and see how duplicates are handled across depositor and borrower data.
- Joins : Introduction, Cross Join.7:02
Understand how joins merge data from multiple tables using a common field, and how a Cartesian product or cross join yields row pairs before conditions create inner and outer joins.
- Joins : Theta Join / Conditional Join.7:02
Discover how joins in relational databases combine data from two or more tables, focusing on theta (conditional) joins and the role of cartesian products and filtering conditions.
- Joins : Equi Join.3:42
Explore equi joins in relational databases by joining student and course tables on the equality condition to retrieve rule numbers and associated student names.
- Joins : Natural Join.9:53
Learn how natural join automatically matches common attributes and merges two relations without explicit conditions. This operation requires at least one common attribute with matching domain to produce the result.
- Joins : Self Join8:39
Explore self joins that connect a table to itself to reveal manager relationships, using an employee table and proper join conditions.
- Outer Join : Introduction.1:48
Explore outer join concepts, including left outer join, right outer join, and full outer join, and learn how they return both matching and nonmatching records.
- Joins : Left Outer Join5:59
Understand how the left outer join returns all rows from the left table and matching rows from the right table. When no match exists, the right side shows nulls.
- Joins : Right Outer Join3:53
Explore the right outer join, focusing on the right side table data, applying a join condition, and returning all right table rows in the result.
- Joins : Full Outer Join4:26
learn how a full outer join combines left and right tables to return all records, including unmatched ones from both sides, showing matching and non-matching rows.
- Query : 1,2,3.9:57
Practice sql queries to list clerks using select *, filter by department number, and order by department ascending and job descending; also display distinct job titles in descending order.
- Query : 4.4:23
This lecture demonstrates computing daily and annual salaries from monthly pay, selecting employee id, name, daily salary, and annual salary, and ordering results by annual salary in ascending order.
- Query : 5.5:23
Write an SQL query to display employee number, name, salary, and computed experience by subtracting date of joining from today, filter by manager 7698, and sort by experience.
- Query : 6, 7.5:23
write select queries to display all columns from a table, filter by job (analyst or clerk), and order results by name in descending order, while understanding query operators.
- Query : 8.4:23
Explore querying with the between operator to filter employees hired in 1993, selecting all columns, and using year extraction from hire dates as an alternative approach.
- Query : 9.4:09
Learn to query employees joined in August 1998 using between on a date range, selecting all columns, and extracting month and year for flexible filtering.
- Query : 10.11:18
Learn to write select statements to retrieve data from EMV table, using star to select all columns or specific columns, and grasp primary and foreign keys, nulls, and department relations.
- Query : 11.1, 11.2, 11.3, 11.4.4:55
Explore sql pattern matching with wildcard operators, including underscore for single-character matches, to find five-character names and build queries for names that start or end with specific patterns.
- Relational Algebra : Introduction.2:47
Explore relational algebra and calculus as the theoretical foundation for representing database queries, and learn core operators—projection, selection, union, cartesian product, joins, rename, and division.
- RA : Selection and Projection Operators.12:29
Explore selection and projection operators in relational algebra. Use sigma to filter rows and projection to display specific attributes from a relation, with examples on employees and salaries.
- RA : Set Manipulation Operators(Union, Intersection, Difference)4:10
Master set manipulation operators in database management systems: union, intersection, and difference; ensure matching attribute counts and compatible domains, and note that duplicates are eliminated by default.
- RA : Set Manipulation Operators - UNION6:42
Explore the union operator by combining two relations in relational algebra, eliminate duplicates, ensure compatible attributes, and produce a unified customer name set from depositor and borrower examples.
- RA : Set Manipulation Operators - INTERSECTION.3:23
Explore the intersection operator in relational algebra by retrieving customer names present in both depositor and loans relations, using two selects followed by intersection to find common customers.
- RA : Set Manipulation Operators - SET DIFFERENCE.7:39
this lecture explains the set difference operator in relational algebra, showing how to derive records in one relation but not in another using minus.
- RA : Cartesian Product.9:58
Explore the cartesian product, or cross product, in relational databases. See how it combines all rows from two relations, then apply a selection and a projection to obtain customer names.
- RA : Rename Operator.4:46
Use the rename operator to assign a new name to a relation or to its attributes, creating aliases for expressions.
- RA : Join introduction and Types.2:22
Explore joins in relational algebra, from Cartesian product and conditional selection to inner and outer joins, combining data across two or more relations.
- RA : Conditional Join / Theta Join8:40
Explore conditional joins, or theta joins, by matching student and course tables on a specific condition using comparison operators, producing related results without a Cartesian product.
- RA : Equijoin1:54
Explore equijoin concepts using equality conditions to join student and course data by matching roll numbers and IDs, and see how the equality operator forms the join condition.
- RA : Natural Join.5:08
Explore how natural join automatically matches records using all common attributes between two relations. Ensure common attributes with matching names and compatible types enable automatic equality conditions across them.
- RA : Outer Join3:24
Learn the outer join concept, comparing inner and outer joins, and examine left, right, and full outer joins, showing how matching and nonmatching rows appear in the output.
- RA : Left Outer Join5:57
Explore left outer join by comparing left and right tables, returning all left records and matching right records, with nulls for non-matches.
- RA : Right Outer Join3:08
Illustrate the right outer join in relational algebra by returning all right-table records, matching with the left where possible, and showing non-matching left rows as null.
- RA : Full Outer Join4:03
Full outer join combines all records from the left and right relations, returning matched rows and placing nulls for non-matching ones to include unmatched left or right records.
- RA : Division Operator.10:54
Examine the division operator in relational algebra, its proper-subset requirement, and how it yields attributes a minus b, illustrated by faculty teaching all courses.
- RA : Division Operator - Example 1, 2.5:29
Demonstrate the division operator in relational algebra through two examples, linking publisher and category and showing customers with a loan across all branches.
- Transaction : Introduction5:09
Explore the concept of a transaction as a single logical unit of work, illustrated by transferring funds, and learn how serial versus concurrent execution affects data consistency and conflict resolution.
- Schedule : Serial and Concurrent Schedules - Definitions.11:54
Define schedules as transaction operations; contrast serial schedules, where one transaction completes before the next, with concurrent schedules, where transactions progress simultaneously while preserving consistency and ACID properties.
- Problem : Number of Searial and Concurrent Schedules.5:55
The lecture counts two-transaction concurrent schedules with read and write operations, showing six possible schedules and contrasting them with serial schedules.
- 1. Last update problem : Problems due to concurrency control.4:23
Analyze how concurrent schedules cause the last update problem and inconsistent data, illustrating a right trade conflict and its impact on final values in a transaction.
- 2. Dirty Read Problem : Problems due to concurrency control.4:21
Explore the dirty read problem in concurrency control, showing how reading uncommitted data from one transaction can yield inconsistent results if another aborts or rolls back.
- 3. Unrepeatable Read Problem : Problems due to concurrency control.1:31
Explore the unrepeatable read problem in concurrency control, showing how a value read twice can differ after another transaction writes between reads, and why this issue must be addressed in concurrency schedules.
- Problem5:08
Identify read-write conflicts, unrepeatable reads, and reading uncommitted data across three schedules to illustrate the problems.
- Non-recoverable Schedules.3:17
Understand non-recoverable schedules by analyzing a two-transaction scenario where a read precedes a commit, preventing rollback and risking permanent data inconsistency.
- Recoverable Schedules.2:36
Explore how recoverable schedules enable recovering original data by managing commit timing and potential rollbacks, as transactions read and write uncommitted data before final commits.
- Cascading Schedules.3:21
Cascading rollbacks occur when one transaction failure causes subsequent transactions to abort due to uncommitted reads and delayed commits, highlighting why such schedules are not recommended.
- Cascadeless Schedules.2:57
Contrast cascading rollbacks with cascadeless recoverable schedules to illustrate how a failure can trigger others to rollback, while cascadeless schedules prevent propagation by preserving committed data.
- Strict Schedules1:47
Describe strict schedules in databases, where a read or write on a data item proceeds only after the transaction commits or aborts, ensuring access to committed or original values.
- Relation among Strict, Cascadeless and Recoverable Schedules.2:54
Explore the relation among strict, cascadeless, and recoverable schedules in concurrency control. Strict schedules are cascadeless and recoverable, while non strict or non recoverable and cascading schedules pose issues.
- Problem : 1, 2.6:01
Practice identifying transaction properties such as dirty reads, uncommitted data, cascade versus cascadeless schedules, and recoverable versus not recoverable and strict versus non-strict schedules by tracing commit and abort.
- Problem : 3.4:46
Examine transaction concepts such as read and write operations, dirty reads from uncommitted data, recoverability, and cascading rollback to understand how commit and abort affect schedules.
- Problem : 4.3:57
Analyze transaction schedules to determine recoverability and cascading effects, comparing commit orders and rollbacks, and identifying non-strict schedules in database transaction problems.
- Problem : 5, 6.2:21
Examine problems 5 and 6, focusing on recoverable reads, commit operations, and cascading versus non-cascading schedules.
- Problem : 7, 8.2:59
Analyze transaction schedules by examining read and write operations, committed and uncommitted data, and determine recoverable, cascading, and strict schedules in read committed data.
- Classification5:02
Identify serial and non-serial schedules, noting serials keep consistency but waste the processor and input/output. Favor recoverable, cascade-less non-serial schedules; cascading ones may cause broader rollbacks and are less preferred.
- Serializability : Introduction14:42
Define serializability in transactions and concurrency control, contrast serial and non-serial schedules, and show how a non-serial schedule can be equivalent to a serial schedule for consistency.
- Serializability : Number of serial and non-serial schedules.5:55
Explore how serial schedules count equals three factorial, yielding six possibilities, and learn a formula for non-serial schedules, with an example yielding 56 non-zero schedules.
- Serializability : Types2:43
Explore the types of serializability, focusing on conflict serializability and view serializability, and learn to identify consistent schedules without enumerating all possibilities.
- Conflict Serializable Schedules : Introduction19:04
Define conflict serializable schedules as those that can become serial schedules by swapping non-conflicting operations. Explore how data items, read and write operations, and two transactions determine conflict.
- Conflict Serializable Schedule : Example 2, 3.7:12
Examine conflict serializable schedules through examples 2 and 3, analyzing read and write operations on data items x and y across two transactions to determine serializability.
- Introduction4:25
Explore file organization and file structure, learning how data blocks, labels, and records enable fast retrieval within a database, using blocking factor concepts and storage strategies.
- Storage Strategies : Spanned and Unspanned Strategies.5:09
Analyze spanned and unspanned storage strategies for records. Spanned uses multiple blocks for variable-length data; unspanned keeps one block per record for fixed-length data, trading space for speed.
- Records organization in a file.4:07
Explore records organization in a file, comparing unordered and ordered file organization. Learn how inserts and searches trade off, with linear versus binary search and impact on efficiency.
- Sparse Index and Dense Index.5:00
A sparse index stores index records for only a subset of data items and points to blocks, while a dense index creates a record for every item for faster lookups.
- Indexing : Introduction.6:27
Indexing enables fast retrieval of database records using a two-column index (primary/candidate key and block pointer); it covers single-level and multilevel indexes, including primary, clustered, and secondary types.
- Indexing : Primary Indexing.5:25
Explain primary indexing for fixed-length records with a primary key and block pointer; create a block anchor and an index entry per block, yielding log B + 1 block accesses.
- Indexing : Primary Indexing - Numerical Problem.14:15
Explain primary indexing with 30,000 records, compute data and index blocks, and show how indexing cuts searches from 3,000 data blocks to about six index lookups plus one data fetch.
- Indexing : Cluster Index.2:30
Create a cluster index by grouping identical values into clusters and assigning one index record per distinct value, stored across data pages and index pages per table.
- Indexing : Secondary Indexing.5:19
Explore secondary indexing, created on non-primary keys to speed data access, with dense index properties and index blocks storing pointers for each record.
- Indexing : Secondary Indexing - Example.4:58
Explore how secondary indexing handles 30,000 records, calculates index records per block, and estimates total index blocks, contrasting with primary indexing in terms of blocks accessed.
- B-Tree : Introduction.5:20
Explore the B-tree introduction as a height-balanced, multiway index structure for multilevel indexing, generalizing binary search trees, with leaf nodes at the same level and internal nodes holding keys.
- B-Tree : Node structure.6:30
Examine the B-tree node structure, defining order as the maximum number of children and detailing how keys, block pointers, and data pointers map to physical memory addresses.
- B-Tree : Order of Node.4:25
Derive the B-tree node order by computing how many block pointers, keys, and data pointers fit within the block size, considering memory for block pointers, keys, and data pointers.
- B-Tree : Numerical problem on Order of node.11:59
Determine the B-tree order for a 512-byte block with 9-byte keys, 6-byte block pointers, and 7-byte record pointers, then estimate index entries across three levels.
- B-Tree : Tree creation with example.16:27
Explore how B-tree insertion works with a practical, step-by-step example, including full nodes, splitting, and promoting the middle key to a higher level to maintain balance.
- B-Tree : Deletion operation with example.19:48
Explore the B-tree deletion process through case-based handling, including deleting from leaves, borrowing from left or right siblings to maintain minimum keys, and merging with the parent.
- B+ Tree : Introduction - Structure, Order of Leaf and Internal Node.12:59
Explore the B-plus tree introduction and node structures. See how leaf nodes hold keys and memory addresses with a single block pointer, while internal nodes manage keys and multiple pointers.
- B+ Tree : Leaf node and Internal node Split.5:16
Learn how a B+ tree splits leaf and internal nodes during insertions, moving the middle element to the apex and dividing keys into left and right sides.
- B+ Tree : Insertion Operation - Tree Creation.12:31
Explore how a B+ tree handles insertion, including leaf and internal node splits, middle-element promotion to higher levels, and pointer updates.
- B+ - Tree : Numerical problem on Order of node.16:00
Determine the B+ tree order for leaves and internal nodes from block size, key size, and pointers, then compute maximum entries across three levels, illustrating capacity changes.
Requirements
- No Prerequisites.
Description
"Why should I buy this course instead of the hundreds of SQL courses available?"
Here's a stronger version.
Complete SQL & Database Masterclass | DBMS, RDBMS & Database Design
Master SQL and Database Engineering with Confidence
Databases are the backbone of every modern application. Whether you're building web applications, designing enterprise systems, preparing for interviews, or pursuing a career in software or data engineering, a strong understanding of SQL and database concepts is essential.
This course is designed to take you from complete beginner to advanced through a structured, practical, and industry-focused learning path.
Instead of memorizing isolated topics, you'll understand how databases actually work, why they are designed the way they are, and how SQL is used to solve real-world problems.
Every concept is explained step by step using practical examples, visual illustrations, and hands-on SQL demonstrations.
What You'll Learn
Database Fundamentals
Relational Database Concepts
ER Modeling
Keys and Relationships
Functional Dependencies
Relational Algebra
Database Design Principles
Normalization (1NF to BCNF)
SQL from Beginner to Advanced
DDL, DML and DCL
SELECT Queries
Filtering and Sorting
Joins
Group By & Having
Aggregate Functions
Subqueries
Views
Indexes
Stored Procedures
Common Table Expressions (CTEs)
Temporary Tables
Window Functions
Performance Optimization
Transactions & Concurrency
ACID Properties
Locking Mechanisms
Deadlocks
Recovery Techniques
Concurrency Control
Database Storage & Performance
File Organization
Indexing Strategies
B-Trees
B+ Trees
Multi-Level Indexing
Query Optimization Concepts
Data Warehousing
Fact Tables
Dimension Tables
Slowly Changing Dimensions (SCD Type 1–6)
Practical SQL
Learn SQL using a modern SQL environment with practical examples covering:
Constraints
Operators
System Functions
Window Functions
Common Table Expressions
Temporary Tables
Views
Stored Procedures
Performance-Oriented SQL Techniques
Why This Course Is Different
This isn't just another SQL course that teaches syntax.
You'll understand the concepts behind every SQL statement and learn why database systems behave the way they do.
Throughout the course you'll work with practical examples that build strong problem-solving skills rather than simply memorizing commands.
The curriculum has been carefully designed to connect database theory with practical SQL, helping you develop a complete understanding of database engineering.
Who Should Take This Course?
Students learning Database Management Systems (DBMS)
Beginners starting SQL from scratch
Software Developers
Backend Developers
Data Engineers
Data Analysts
Computer Science Students
Interview Preparation (SQL & DBMS)
Anyone wanting a strong foundation in relational databases
What You'll Get
25+ Hours of HD Video Lessons
Step-by-Step Practical Demonstrations
Downloadable Resources
Assignments
Lifetime Access
Certificate of Completion
Regular Course Updates
Instructor Support
By the End of This Course
You'll be able to:
Design relational databases confidently.
Write efficient SQL queries for real-world scenarios.
Understand how enterprise database systems are designed.
Improve SQL query performance using indexing techniques.
Work with advanced SQL features such as CTEs, Window Functions, Views, and Stored Procedures.
Understand transactions, concurrency control, and recovery mechanisms.
Build a strong foundation for careers in Software Development, Database Engineering, Data Engineering, and Analytics.
Enroll Today
Whether you're preparing for interviews, strengthening your database fundamentals, or looking to master SQL for your career, this course provides the knowledge and practical skills needed to become confident in database engineering.
Join thousands of learners and start mastering SQL and Database Engineering today!
Who this course is for:
- Academic Students
- Competitive Exams Aspirants
- GATE CS/IT
- Willing to Learn
- Database Engineer
- Database Designer