Concepts and Characteristics of DBMS

History and Evolution of Database

Database System Architecture

Relational Database Model

Entity Relationship Model

Types of Attributes and their Symbols

Connectivity of Relationships

Degree of a Relationship

Designing an ER Model

Conversion from ER Model to Relational Model

Conversion from ER Model to Relational Model_Composite Attributes

Conversion from ER Model to Relational Model - Derived and Multi-valued Attributes

Many to Many Relationship

Many to One Relationships

One to Many Relationships

One to One Relationships

Strong and Weak Entity Sets

Strong and Weak Entity Sets_2

Cardinality of a Relationship

Total and Partial Participation

Cardinality and Participation

Examples

Codd's Rules

Data Independence

Keys in DBMS

Functional Dependence

Inference Rules

Closure of Functional Dependencies

Minimal Cover

Fully Functional Dependency

Transitive Dependency

Multi Valued Dependency

Normalization Process

Computer Center Case Study

First Normal Form_1

Anomalies of the First Normal Form

First Normal Form_2

Second Normal Form

1Third Normal Form

1Boyce Codd Normal Form

1Fourth Normal Form

1Fifth Normal Form

Relational Query Languages

Basics of Relational Algebra

Select Operation

Projection Operation

Rename Operation

Union Operation

Intersection Operation

Set Difference Operation

Cartesian Product Operation

Joins

Relational Algebra Queries_1

Relational Algebra Queries_2

Introduction

DESCRIBE Command

SQL Statement Basics

Select Statements

Column Aliases

Quote Operator

DISTINCT and UNIQUE Operator

Concatenation Operator

Arithmetic Expressions

WHERE Clause

Comparison Operators

BETWEEN ... AND Operator

IN Operator

LIKE Operator

IS NULL Operator

Logical Operators

Rules of Precedence

ORDER BY Clause

ASC and DESC Operators

NULLS FIRST and NULLS LAST

ROWID and ROWNUM

Single Row Functions

Case Conversion Functions

Character Manipulation Functions

Numeric Functions

Nested Functions

Date Values and Formats

Date Format Models

Date Functions and Arithmetic Operators

Date Manipulation Functions

Number Format Models

Oracle Conversion Functions

Implicit Conversion Examples

TO_CHAR Function

NVL, NVL2, NULLIF and COALESCE Functions

Group Functions

AVG Function

COUNT Function

MAX Function

MIN Function

SUM Function

LISTAGG Function

Overview of Group Functions

GROUP BY Clause - 1

GROUP BY Clause - 2

HAVING Clause

Nested Group Functions

Join

Creating a Join

Natural Join

Join with USING Clause

Handling Ambiguous Column Names

INNER Join

Multiple Join Operations

Restricting Joins

SELF Join

Non-Equijoins

OUTER Joins

CROSS Join

Join Types

Subqueries

Single Row Subqueries

Multiple Row Subqueries

Multiple Column Subqueries

SET Operators

UNION and UNION ALL Operators

INTERSECT Operator

MINUS Operator

Matching Unmatched Queries

ORDER BY with SET Operators

Combining Multiple Queries with SET Operators

Data Definition Language

Database Object Naming Rules

CREATE TABLE Statement

ALTER TABLE Statement

DROP TABLE Command

TRUNCATE TABLE Statement

RENAME TABLE Statement

Data Manipulation Language

INSERT Statement

INSERT INTO SELECT Statement

UPDATE Statement

DELETE Statement

Constraints

NOT NULL Constraint

UNIQUE Constraint

Primary Key Constraint

Foreign Key Constraint

Check Constraint

Introduction

Procedure

Trigger

Functions

Disk Management

Pages and Blocks

Time to Access a Page

Heap Files

Index and Index Structure

Sorted Files

Storage Structure

Clustered Index

Unclustered Index

Problem on Cost of Range Search

Sparse Versus Dense Index

Two Level Index

Multi Level Index

Query Processing

Query Optimization

Transactions

Concurrent Execution of Transactions

Inconsistency Problem

States of a Transaction

ACID Properties

Schedules

Lost Update Problem

Dirty Read Problem

Unrepeatable Read Problem

Phantom Read Problem

Recoverable and Non-Recoverable Schedule

Cascadeless and Cascading Schedules

Recoverable and Cascadeless Schedules

Example Problem

Strict Schedule

Recoverable, Cascadeless and Strict Schedules

Important Point

Equivalence of Schedules

Conflicting Actions

Conflict Equivalence and Equivalence of Schedules

Conflict Serializability of a Schedule

Test for Conflict Serializability

Number of Conflict Serializable Schedules

Example Problems

View Equivalence and View Serializability

Problems on View Serializability

Additional Problems on View Serializability

Shared and Exclusive Locking Protocol

Two Phase Locking

Strict and Rigorous Phase Locking Protocols

Recovery Techniques

Introduction to NoSQL Databases

Types of NoSQL Databases

BASE Properties

CAP Theorem

Big Data

Introduction

Components of Data Warehouse

Data Warehouse Architecture

Online Analytical Processing

Online Transaction Processing

OLAP Versus OLTP