🗃️ DBMS Basics

Introduction to Database Management Systems

A Database Management System (DBMS) is specialized software designed to create, maintain, and use databases. Understanding DBMS fundamentals is essential for effective data management and manipulation in modern information systems.

🧩 Understanding Databases and DBMS

📊 What is a Database?

• An organized collection of structured data
• Designed to store, retrieve, and manage information efficiently
• Typically organized into tables with rows and columns
• Supports relationships between different data elements
• Examples: Student records, inventory systems, customer information

🔧 What is a DBMS?

• Software that manages databases
• Provides an interface between users/applications and the data
• Handles data storage, retrieval, update, and administration
• Ensures data security, integrity, and consistency
• Examples: Microsoft Access, MySQL, Oracle, SQL Server

📋 Key Features of DBMS

• Data Definition: Tools to create and modify database structure
• Data Manipulation: Methods to insert, update, retrieve, and delete data
• Data Security: Controls to protect data from unauthorized access
• Data Integrity: Rules to ensure data accuracy and consistency
• Data Recovery: Mechanisms to restore data after failures
• Concurrency Control: Management of simultaneous data access

📑 Database Models

📊 Relational Database Model

• Most common and widely used model
• Data organized in tables (relations) with rows and columns
• Relationships established through keys
• Uses SQL (Structured Query Language) for data manipulation
• Examples: MySQL, Microsoft SQL Server, Oracle

Example table structure:

STUDENTS
ID  | Name        | Age | Class
----|-------------|-----|------
001 | Chan Tai Man| 16  | 5A
002 | Wong Mei Mei| 15  | 5B
003 | Lee Siu Ming| 16  | 5A

📁 Hierarchical Database Model

• Data organized in a tree-like structure
• Parent-child relationships between records
• One parent can have multiple children, but each child has only one parent
• Efficient for one-to-many relationships
• Example: IBM's Information Management System (IMS)

🕸️ Network Database Model

• Extension of the hierarchical model
• Allows many-to-many relationships
• Child records can have multiple parent records
• More flexible than hierarchical but more complex
• Example: Integrated Data Store (IDS)

📊 Object-Oriented Database Model

• Stores data as objects (similar to object-oriented programming)
• Encapsulates data and methods together
• Supports complex data types and relationships
• Good for multimedia and complex applications
• Examples: MongoDB, ObjectDB

🧱 Database Components

📋 Tables

• Primary structure for storing data
• Organized into rows (records) and columns (fields)
• Each table typically represents a single entity type
• Example: STUDENTS, COURSES, ENROLLMENTS

Example table creation:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DateOfBirth DATE,
    ClassID INT
);

🔑 Keys

• Primary Key: Uniquely identifies each record in a table
• Foreign Key: Refers to a primary key in another table
• Composite Key: Multiple columns that together form a unique identifier
• Candidate Key: Column(s) that could be used as a primary key

Example key definition:

-- Primary Key
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100)
);

-- Foreign Key
CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

🔄 Relationships

• One-to-One: One record in table A relates to exactly one record in table B
• One-to-Many: One record in table A relates to multiple records in table B
• Many-to-Many: Multiple records in table A relate to multiple records in table B

Example relationship diagram:

STUDENTS (1) ---- (*) ENROLLMENTS (*) ---- (1) COURSES

📝 Fields and Data Types

• Text/String: Names, descriptions, addresses
• Numeric: Integers, decimals, currency
• Date/Time: Dates, times, timestamps
• Boolean: True/false values
• Binary: Images, documents, files
• Memo/CLOB: Large text fields

Example field definitions:

CREATE TABLE Products (
    ProductID INT,
    ProductName VARCHAR(100),
    UnitPrice DECIMAL(10,2),
    InStock BOOLEAN,
    DateAdded DATE,
    Description TEXT
);

🛠️ Basic DBMS Operations

📝 Creating Databases and Tables

• Defining database schema
• Specifying tables, fields, and relationships
• Setting constraints and rules
• Establishing indexes for performance

Example database creation:

-- Create database
CREATE DATABASE SchoolSystem;

-- Use the database
USE SchoolSystem;

-- Create table
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Grade INT
);

📊 Data Manipulation

• INSERT: Add new records
• SELECT: Retrieve data
• UPDATE: Modify existing records
• DELETE: Remove records

Example operations:

-- Insert data
INSERT INTO Students (StudentID, FirstName, LastName, Grade) 
VALUES (1, 'Tai Man', 'Chan', 11);

-- Select data
SELECT FirstName, LastName FROM Students WHERE Grade = 11;

-- Update data
UPDATE Students SET Grade = 12 WHERE StudentID = 1;

-- Delete data
DELETE FROM Students WHERE StudentID = 1;

🔍 Querying Data

• Retrieving specific information
• Filtering with WHERE clauses
• Sorting with ORDER BY
• Grouping with GROUP BY
• Joining multiple tables

Example queries:

-- Basic query with filter
SELECT * FROM Students WHERE Grade > 10;

-- Sorting results
SELECT * FROM Students ORDER BY LastName ASC;

-- Joining tables
SELECT s.FirstName, s.LastName, c.ClassName
FROM Students s
JOIN Classes c ON s.ClassID = c.ClassID;

📊 Data Views

• Virtual tables based on query results
• Simplify complex queries
• Restrict access to certain data
• Present data in a specific format

Example view creation:

CREATE VIEW StudentGrades AS
SELECT s.FirstName, s.LastName, c.CourseName, e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

🔒 Database Security and Administration

👤 User Management

• Creating user accounts
• Assigning permissions
• Role-based access control
• Authentication mechanisms

Example user management:

-- Create user
CREATE USER 'teacher'@'localhost' IDENTIFIED BY 'password';

-- Grant permissions
GRANT SELECT ON SchoolSystem.Students TO 'teacher'@'localhost';

🛡️ Data Security

• Access controls
• Encryption
• Audit trails
• Backup and recovery procedures

🔄 Database Maintenance

• Backup and restore operations
• Performance optimization
• Index management
• Database integrity checks

💼 Practical Applications

🏫 School Information Systems

• Student records management
• Course enrollment tracking
• Grade recording and reporting
• Attendance monitoring

📦 Inventory Management

• Product tracking
• Stock level monitoring
• Order processing
• Supplier management

👥 Customer Relationship Management

• Customer information storage
• Purchase history tracking
• Communication logs
• Service request management

💰 Financial Systems

• Transaction recording
• Account management
• Financial reporting
• Budget tracking

Understanding DBMS basics provides the foundation for creating efficient, secure, and reliable database applications. These concepts apply across different database systems and are essential for anyone working with structured data.