Database_ipynb_2_

• 3 min read

Databases

Intro to Databases

  • A database is a structured collection of data that can be easily accessed, managed, and updated. Databases are used in almost every application today, from websites to banking systems, and even scientific research.

Why Do We Need Databases?

Before databases, data was stored in files, leading to:

  • Redundancy: The same data was stored multiple times.
  • Inconsistency: Different versions of data in different files.
  • Data Isolation: Hard to retrieve and manipulate related data.
  • Security Issues: Difficult to restrict access to sensitive data.

Databases solve these problems by organizing data in a structured way, allowing for efficient storage, retrieval, and security.

Image

Types of Databases

Databases can be categorized based on their structure and functionality:

1. Relational Databases (RDBMS)

  • Data is stored in tables (rows and columns).
  • Uses Structured Query Language (SQL).
  • Examples: MySQL, PostgreSQL, Oracle Database.

2. NoSQL Databases

  • Designed for unstructured and semi-structured data.
  • Good for big data and real-time applications.
  • Examples: MongoDB, Cassandra, Redis.

3. Hierarchical Databases

  • Data is stored in a tree-like structure (parent-child relationship).
  • Example: IBM Information Management System (IMS).

4. Network Databases

  • More flexible than hierarchical databases, allowing many-to-many relationships.
  • Example: Integrated Data Store (IDS).

5. Object-Oriented Databases

  • Data is stored as objects (similar to object-oriented programming).
  • Example: db4o, ObjectDB.

Database Management System (DBMS)

A DBMS is software that interacts with databases to perform operations like creating, reading, updating, and deleting data (CRUD operations).

Features of DBMS:

  • Data Abstraction: Hides complex storage details from users.
  • Data Independence: Changes in structure do not affect applications.
  • Data Security: Provides authentication and authorization.
  • Concurrency Control: Allows multiple users to access data simultaneously.
  • Backup and Recovery: Ensures data safety.

SQL: The Language of Databases

Image

SQL (Structured Query Language) is used to interact with relational databases.

Basic SQL Commands:

  • DDL (Data Definition Language): Defines database schema.
    • CREATE TABLE students (id INT, name VARCHAR(50));
    • ALTER TABLE students ADD age INT;
    • DROP TABLE students;
  • DML (Data Manipulation Language): Modifies data.
    • INSERT INTO students VALUES (1, 'John Doe');
    • UPDATE students SET age = 20 WHERE id = 1;
    • DELETE FROM students WHERE id = 1;
  • DQL (Data Query Language): Retrieves data.
    • SELECT * FROM students;
    • SELECT name FROM students WHERE age > 18;
  • DCL (Data Control Language): Controls user access.
    • GRANT SELECT ON students TO user1;
    • REVOKE SELECT ON students FROM user1;

ACID Properties of a Database

For a database transaction to be reliable, it must follow ACID properties:

  • Atomicity: A transaction is either fully completed or not executed at all.
  • Consistency: Ensures that the database remains in a valid state.
  • Isolation: Transactions occur independently.
  • Durability: Once a transaction is committed, it remains stored permanently.

Popcorn Hack 1: Identify the Type of Database

Match the following database types to their best use cases:

  1. A banking system requiring strict data integrity.
  2. A social media platform with large-scale unstructured data.
  3. A library catalog storing books in a tree-like format.
  4. A gaming leaderboard storing real-time scores and player stats.

Options:
A) Relational Database
B) NoSQL Database
C) Hierarchical Database
D) Object-Oriented Database

Popcorn Hack 2: Write SQL Queries

Given the following table named Employees:

EmpID Name Age Department
101 Alice 30 HR
102 Bob 25 IT
103 Carol 28 Finance

Write SQL queries to:

  1. Retrieve all employees working in the IT department.
  2. Add a new employee: EmpID = 104, Name = Dave, Age = 26, Department = IT.
  3. Delete the record of the employee named Carol.

Homework Question

You are designing a small database for a university that stores student and course information.

Requirements:

  1. Each student has an ID, Name, Age, and Major.
  2. Each course has a CourseID, CourseName, and Credits.
  3. Students enroll in courses, creating a many-to-many relationship.

Task:

  • Design the schema for the database (list the necessary tables and their attributes).
  • Write SQL queries for the following:
    1. Create the Students and Courses tables.
    2. Enroll a student with ID 1001 into the course with ID CS101.
    3. Retrieve the list of all students enrolled in CS101.