Learn RDBMS, NoSQL, SQL vs NoSQL & Database Design - Textnotes

Learn RDBMS, NoSQL, SQL vs NoSQL & Database Design

Learn the fundamentals of databases in this beginner-friendly guide. Understand relational vs non-relational databases, SQL vs NoSQL, database terminology (tables, rows, columns, schema, primary & foreign keys), database design basics, and practical tasks like installing MySQL, SQL Server, PostgreSQL, or Oracle and creating your first database and table.

1. What is a Database?

A database is a structured collection of data that allows you to store, manage, and retrieve information efficiently.

Key Points:

  1. Databases are essential for applications, websites, and data analytics.
  2. They ensure data integrity, consistency, and security.
  3. Databases can store structured, semi-structured, or unstructured data.

2. Types of Databases

2.1 Relational Databases (RDBMS)

  1. Data is stored in tables with rows and columns.
  2. Tables can have relationships through primary and foreign keys.
  3. Examples: MySQL, SQL Server, PostgreSQL, Oracle

Example Table: Employees

IDNameDepartmentSalary
1AliceHR50000
2BobIT60000

2.2 Non-Relational Databases (NoSQL)

  1. Data can be document-based, key-value, graph, or columnar.
  2. Flexible schema; good for big data and real-time applications.
  3. Examples: MongoDB, CouchDB, Cassandra

3. SQL vs NoSQL

FeatureSQL (Relational)NoSQL (Non-relational)
SchemaFixed schemaDynamic schema
Query LanguageSQLAPI/Query language varies
ScalingVertical scalingHorizontal scaling
Use CaseTransactional apps, bankingBig data, analytics, real-time apps

4. Database Terminology

TermDescription
TableCollection of related data (rows & columns)
Row / RecordA single entry in a table
Column / FieldAttribute or data type in a table
SchemaStructure of the database (tables, columns, relationships)
Primary KeyUnique identifier for each row
Foreign KeyLinks a row in one table to a row in another table

5. Database Design Basics

  1. Plan tables based on entities and relationships.
  2. Normalize data to avoid redundancy.
  3. Identify primary and foreign keys to establish relationships.
  4. Use ER diagrams (Entity-Relationship diagrams) for visual design.

6. Practical Tasks

6.1 Install a Database Server

  1. MySQL: https://dev.mysql.com/downloads/
  2. SQL Server: https://www.microsoft.com/en-us/sql-server
  3. PostgreSQL: https://www.postgresql.org/download/
  4. Oracle Database: https://www.oracle.com/database/

6.2 Create Your First Database


-- MySQL / SQL Server / PostgreSQL
CREATE DATABASE CompanyDB;

6.3 Create Your First Table


CREATE TABLE Employees (
ID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10,2)
);

6.4 Understand Schema and Relationships

  1. Each table has a schema defining its columns and data types.
  2. Relationships are defined using primary and foreign keys:

CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50)
);

ALTER TABLE Employees
ADD CONSTRAINT FK_Dept
FOREIGN KEY (Department) REFERENCES Departments(DeptName);

7. Practice Exercises

  1. Install a database server of your choice (MySQL / SQL Server / PostgreSQL / Oracle).
  2. Create a database called CompanyDB.
  3. Create a table Employees with ID, Name, Department, Salary.
  4. Create another table Departments with DeptID, DeptName.
  5. Establish a foreign key relationship between Employees and Departments.
  6. Insert sample data and explore the table structure.