Database Systems & SQL: Basics, DBMS, Queries & Design

TAKEAWAYS:

• Understand DBMS as software that organizes, stores, and retrieves data efficiently.
• Learn SQL commands (SELECT, INSERT, UPDATE, DELETE) to manipulate databases.
• Know database design principles (tables, relationships, normalization).
• Compare file-based systems vs. DBMS using a structured table.
• Apply SQL queries to solve practical problems (e.g., filtering, sorting).
• Recognize advantages/disadvantages of DBMS in real-world scenarios.

1. What is a Database?

A database is an organized collection of related data stored in a structured way for easy access, management, and updates.

Types of Databases

mindmap
  root((Database Types))
    File-Based
      Simple, no structure
      Example: Excel sheets, text files
    Relational (Most Common)
      Uses tables (rows & columns)
      Example: MySQL, Oracle
    NoSQL (Non-Relational)
      Flexible, handles unstructured data
      Example: MongoDB, Firebase
    Object-Oriented
      Stores data as objects (like in OOP)
      Example: DB4O

Why use a database?

• Efficient storage (no duplicate data).
• Fast retrieval (queries run quickly).
• Security (user permissions, backups).
• Scalability (handles large data easily).

2. Database Management System (DBMS)

A DBMS is software that interacts with users, applications, and the database itself to capture and analyze data.

Key Features of DBMS

User/Application → DBMS → Database ↑ (Queries) ↑ (Stores/Retrieves)

• Data Definition Language (DDL): Creates/modifies database structure (e.g., CREATE TABLE).
• Data Manipulation Language (DML): Inserts, updates, deletes data (e.g., INSERT, UPDATE).
• Data Control Language (DCL): Manages access (e.g., GRANT, REVOKE).
• Query Language (SQL): Standard language for DBMS (e.g., SELECT, JOIN).

3. File-Based System vs. DBMS

Example:

• File-Based: Storing student records in separate Excel files (one per class).
• DBMS: Storing all records in a single Students table with relationships to Courses.

4. Relational Database Model (Most Common)

A relational database organizes data into tables (relations) linked by keys.

Key Concepts

• Table: A collection of records (rows) and fields (columns).
• Primary Key (PK): Unique identifier (e.g., Student_ID).
• Foreign Key (FK): Links tables (e.g., Course_ID in Enrollment table).
• Relationships:
  • One-to-One (1:1): One record in Table A → One record in Table B.
  • One-to-Many (1:M): One record in Table A → Many in Table B (e.g., one teacher → many students).
  • Many-to-Many (M:M): Requires a junction table (e.g., students and courses).

5. SQL (Structured Query Language)

SQL is the standard language for DBMS to:

• Create databases (CREATE DATABASE).
• Define tables (CREATE TABLE).
• Insert, update, delete data (INSERT, UPDATE, DELETE).
• Query data (SELECT).

Basic SQL Commands

-- Create a table
CREATE TABLE Students (
    Student_ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Email VARCHAR(50)
);

-- Insert data
INSERT INTO Students VALUES (1, 'Ramesh', 'ramesh@example.com');

-- Select data (with conditions)
SELECT Name, Email FROM Students WHERE Student_ID = 1;

-- Update data
UPDATE Students SET Email = 'newemail@example.com' WHERE Student_ID = 1;

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

Example: Filtering and Sorting

-- Find students with 'A' grade
SELECT Name FROM Students WHERE Grade = 'A';

-- Sort students by name (ascending)
SELECT * FROM Students ORDER BY Name ASC;

6. Database Design Principles

Good design avoids anomalies (errors in data) using normalization.

Normalization Steps

7. Advantages and Disadvantages of DBMS

8. Real-World Applications of DBMS

• E-commerce: Stores product details, user orders (e.g., Amazon).
• Banking: Manages accounts, transactions (e.g., SBI, Nabil Bank).
• Hospitals: Patient records, appointments (e.g., KOC Hospital).
• Social Media: User profiles, posts (e.g., Facebook, Twitter).

Exam Tip: How to Score Full Marks

1. Understand SQL Queries:

   • Practice writing SELECT, WHERE, JOIN, GROUP BY queries.
   • Example question:

     "Write an SQL query to find the total marks of all students in a class." Answer:

     SELECT Student_Name, SUM(Marks) AS Total_Marks
     FROM Student_Marks
     GROUP BY Student_Name;
     

2. Diagrams Are Key:

   • Draw ER diagrams (Entity-Relationship) for database design questions.
   • Example:

     "Design a database for a library system." Answer:

erDiagram
    Library ||--o{ Book : "contains"
    Book ||--|{ Author : "written by"
    Member ||--o{ Loan : "borrows"
    Loan }|--|| Book : "borrowed"
    Library {
        int library_id PK
        string name
        string location
    }
    Book {
        int book_id PK
        string title
        int library_id FK
    }
    Author {
        int author_id PK
        string name
    }
    Member {
        int member_id PK
        string name
        string contact
    }
    Loan {
        int loan_id PK
        int member_id FK
        int book_id FK
        date due_date
    }

3. Compare File-Based vs. DBMS:

   • Always highlight redundancy, speed, and security in comparisons.

4. Normalization Questions:

   • Identify anomalies in given tables and suggest fixes.
   • Example:

     "Convert this table into 3NF:" Before:

     Student_ID	Name	Course	Instructor
     1	Ramesh	Math	Mr. ABC
     After:

     • Students(Student_ID, Name)
     • Courses(Course_ID, Name)
     • Instructors(Instructor_ID, Name)
     • Enrollment(Student_ID, Course_ID, Instructor_ID)

5. SQL Practical Problems:

   • Expect questions like:

     "Write a query to find the second-highest salary from an Employees table." Answer:

     SELECT MAX(Salary) FROM Employees
     WHERE Salary < (SELECT MAX(Salary) FROM Employees);
     

Final Advice:

• Practice SQL on platforms like SQLFiddle or W3Schools.
• Memorize key commands (SELECT, JOIN, GROUP BY, HAVING).
• Draw diagrams for database design questions—they fetch extra marks!

End of Note (Word count: ~1,800)