3.2.1. Introduction to Databases

Introduction to Databases for Database Technologies Beginner Level

3. Database Technologies: Organizing and Protecting Your Data

3. Database Technologies: Organizing and Protecting Your Data

Welcome to the world of databases! In this section, we’re going to learn how to design, build, and manage databases – the systems that hold all the important information for your applications. We want our databases to be:

Secure: Protecting your data from unauthorized access and breaches.
Efficient: Fast and responsive when we need to get data in and out.
Scalable: Able to grow and handle more and more data as your application becomes popular.

We’ll start from the very basics and work our way up to advanced techniques for handling massive amounts of data!

3.2. Beginner Level: Getting Started with Databases

At the beginner level, we’ll lay the foundation with Relational Database Management Systems (RDBMS). These are like the tried-and-true, reliable workhorses of the database world. We’ll focus on understanding the core ideas and getting hands-on with practical examples.

3.2.1. Introduction to Databases: The Basics of Storing Information

Fundamentals of RDBMS (MySQL, PostgreSQL) with a focus on data integrity and security:
- Detail: We’re starting with Relational Database Management Systems (RDBMS). Think of an RDBMS as a really organized system for storing information in tables.
  - Tables: Imagine spreadsheets, but much more powerful and structured. A table holds data about one type of thing. For example, you might have a Users table, a Products table, or an Orders table.
  - Rows and Columns: Inside each table, data is organized into rows (each row is a single record, like information about one specific user) and columns (each column is a piece of information about that record, like a user’s name, email, or password).
  - Schemas: A schema is like a blueprint for your entire database. It defines all the tables, columns, and relationships between them. It’s the overall structure of your database.
  - Keys: Keys are crucial for linking tables together and uniquely identifying rows.
    - Primary Key: Every table should have a primary key. This is a column (or set of columns) that uniquely identifies each row in the table. Like a student ID number – each student has a unique one. No two rows can have the same primary key value, and it can’t be empty.
    - Foreign Key: Foreign keys are used to create relationships between tables. A foreign key in one table points to the primary key in another table. For example, in an Orders table, you might have a user_id column as a foreign key that points to the id (primary key) column in the Users table. This links each order to the user who placed it.
  - Relational Database Principles: RDBMS are based on the idea of relationships between data. These relationships are defined using keys and constraints.
    - Relationships: Common types of relationships are:
      - One-to-One: One record in table A is related to at most one record in table B, and vice-versa. (Example: a User might have at most one UserProfile).
      - One-to-Many: One record in table A can be related to many records in table B, but each record in table B is related to at most one record in table A. (Example: One Customer can place many Orders).
      - Many-to-Many: Many records in table A can be related to many records in table B, and vice-versa. (Example: Many Products can be in many Orders - often requires a “join table” to represent the relationship).
    - Constraints: Rules you set on your data to make sure it’s valid and consistent.
      - NOT NULL: A column cannot be empty.
      - UNIQUE: Values in a column must be unique.
      - PRIMARY KEY: As explained above.
      - FOREIGN KEY: As explained above, enforces relationships between tables.
      - CHECK: Ensures values in a column meet a specific condition (e.g., age must be greater than 0).
  - ACID Properties (Data Integrity): RDBMS are designed to guarantee ACID properties to ensure data integrity, especially when many things are happening at once:
    - Atomicity: A transaction is treated as a single, indivisible unit of work. Either all changes in a transaction are completed successfully, or none are. If any part of a transaction fails, the entire transaction is rolled back, leaving the database in its original state. Think of it like an “all or nothing” deal.
    - Consistency: A transaction must take the database from one valid state to another valid state. It can’t violate any of the rules or constraints you’ve set up in your database (like primary keys, foreign keys, etc.).
    - Isolation: When multiple transactions are happening at the same time, they should be isolated from each other. One transaction should not see the intermediate changes made by another transaction that is still in progress. This prevents data corruption and ensures that concurrent transactions operate correctly.
    - Durability: Once a transaction is successfully committed (saved), the changes are permanent and will survive even system failures (like power outages, crashes). The data is written to persistent storage.
  - Security Fundamentals: Right from the start, we’ll think about security.
    - Database User Management: Creating user accounts and managing who has access to the database. You don’t want just anyone messing with your data!
    - Access Control (Permissions): Controlling what each user is allowed to do in the database. Some users might only be able to read data, while others can write, update, or delete data. We use the principle of least privilege – give users only the minimum permissions they need to do their job.
  - Popular RDBMS: MySQL and PostgreSQL: We’ll be using popular, open-source RDBMS like MySQL and PostgreSQL. They are both powerful, reliable, and widely used in the industry. They are excellent choices for learning database fundamentals.
- Why it’s important: RDBMS are the foundation of data management for so many applications. Understanding how they work, their core principles, and ensuring data integrity and basic security is absolutely essential for any back-end developer. MySQL and PostgreSQL are industry standards and fantastic starting points for learning RDBMS concepts.
- Learning Method:
  - Lectures: We’ll have lectures to explain the concepts of RDBMS, database terminology, relational principles, ACID properties, and basic security.
  - Database System Demonstrations: We’ll show you how to set up and use MySQL or PostgreSQL, demonstrating the concepts in action.
  - Hands-on Tutorials: You’ll get step-by-step tutorials to guide you through setting up a database, creating tables, and interacting with it using tools.
  - Quizzes: Short quizzes to check your understanding of the fundamental database concepts we cover.
Basic SQL queries (CRUD operations, joins):
- Detail: Now that we understand what a database is, we need to learn how to talk to it! We use SQL (Structured Query Language) to interact with relational databases. SQL is the standard language for managing and manipulating data in RDBMS.
  - SQL - The Language of Databases: SQL is like a special language designed just for databases. It lets you tell the database what you want to do.
  - CRUD Operations - The Four Basic Actions: Almost everything you do with data in a database falls into these four categories, known as CRUD:
    - C - Create (INSERT): Adding new data to the database. In SQL, we use the INSERT statement to add new rows to a table.
      - Example: INSERT INTO Users (name, email) VALUES ('Alice', 'alice@example.com'); (Adds a new user to the Users table).
    - R - Read (SELECT): Getting data out of the database. We use the SELECT statement to retrieve data from tables. This is how you ask questions of your database.
      - Example: SELECT name, email FROM Users WHERE id = 1; (Gets the name and email of the user with id 1 from the Users table).
    - U - Update (UPDATE): Changing existing data in the database. We use the UPDATE statement to modify rows in a table.
      - Example: UPDATE Users SET email = 'new_email@example.com' WHERE id = 1; (Changes the email of the user with id 1 in the Users table).
    - D - Delete (DELETE): Removing data from the database. We use the DELETE statement to remove rows from a table.
      - Example: DELETE FROM Users WHERE id = 1; (Deletes the user with id 1 from the Users table).
  - JOINs - Combining Data from Multiple Tables: Often, the information you need is spread across multiple tables in a relational database. JOINs are SQL operations that allow you to combine data from two or more related tables into a single result set.
    - INNER JOIN: Returns rows only when there is a match in both tables based on a related column (join condition). It’s like finding the intersection of data between tables.
      - Example: If you have a Users table and an Orders table, and you want to get a list of orders with the name of the user who placed each order, you’d use INNER JOIN.
    - LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the “left” table and the matching rows from the “right” table. If there’s no match in the “right” table, you still get the row from the “left” table, but with NULL values for the columns from the “right” table. Think of it as “give me everything from the left table, and if there’s a match in the right table, include that too.”
      - Example: Get a list of all users and their orders (even users who haven’t placed any orders yet).
    - RIGHT JOIN (or RIGHT OUTER JOIN): Similar to LEFT JOIN, but returns all rows from the “right” table and matching rows from the “left” table. Less commonly used than LEFT JOIN.
    - FULL OUTER JOIN: Returns all rows when there is a match in one of the tables. You get all rows from both tables, and NULL values where there isn’t a match. Supported by some databases, but not all (e.g., MySQL doesn’t directly support FULL OUTER JOIN, but you can simulate it).
- Why it’s important: SQL is the essential language for working with relational databases. Mastering basic SQL, especially CRUD operations and JOINs, is absolutely fundamental for any back-end developer. You’ll use SQL constantly to get data in and out of your database, manipulate it, and build the logic of your applications.
- Learning Method:
  - Interactive SQL Tutorials: We’ll use interactive online platforms where you can type in SQL queries and see the results instantly.
  - Hands-on Exercises: You’ll get lots of practice writing SQL queries for various data manipulation tasks. We’ll give you scenarios and you’ll have to write the SQL to solve them.
  - SQL Query Challenges: We’ll give you more complex SQL problems to solve, to test your understanding and problem-solving skills with SQL.
  - Database Query Optimization Basics Introduction: We’ll start to touch on the idea of writing efficient SQL queries and introduce basic optimization concepts to get you thinking about performance early on.
Practical Exercises: Simple database design and querying projects:
- Detail: Theory is great, but the best way to learn databases is by doing! You’ll get hands-on experience with simple projects to solidify your understanding of database design and SQL.
  - Project Examples: We’ll work on projects like:
    - Simple Library System Database: Design a database to keep track of books, members, and borrowing records in a library. Tables might be: Books, Members, Loans.
    - Blog Database: Design a database for a simple blog platform. Tables might be: Posts, Authors, Categories, Comments.
    - Small E-commerce Platform Database: Design a database for a very basic online store. Tables might be: Products, Categories, Customers, Orders, OrderItems.
  - Database Design: For each project, you’ll go through the process of designing the database schema:
    1. Identify Entities: What are the main “things” we need to store information about? (e.g., Books, Members, Posts, Products). These become your tables.
    2. Identify Attributes: What information do we need to store about each entity? (e.g., for a Book: title, author, ISBN, publication_year). These become your columns in the tables.
    3. Define Relationships: How are these entities related to each other? (e.g., a Member can borrow many Books, a Post is written by one Author). Figure out the relationships (one-to-many, many-to-many) and how to represent them using foreign keys.
    4. Choose Data Types: Decide what type of data each column will hold (e.g., TEXT, INTEGER, DATE, BOOLEAN).
    5. Apply Constraints: Decide on constraints to ensure data integrity (e.g., primary keys, not null constraints, unique constraints).
  - SQL Querying Practice: After designing your database, you’ll practice writing SQL queries to:
    - Create Tables: Use CREATE TABLE statements to build your database schema in MySQL or PostgreSQL.
    - Insert Data: Use INSERT statements to populate your tables with sample data.
    - Retrieve Data: Use SELECT statements with WHERE clauses and JOINs to answer specific questions and get information out of your database.
    - Update Data: Use UPDATE statements to modify existing data.
    - Delete Data: Use DELETE statements to remove data when needed.
- Why it’s important: Hands-on practice is key to learning databases and SQL. These projects will solidify your theoretical knowledge and give you practical experience in building and querying real databases. You’ll learn to think like a database designer and data manager.
- Learning Method:
  - Project-based Learning: We’ll learn through doing! You’ll work on these database projects from start to finish.
  - Guided Database Design Exercises: We’ll guide you through the database design process, step by step.
  - SQL Coding Labs: Dedicated lab sessions where you’ll focus on writing SQL code to solve specific project requirements.
  - Code Reviews of Database Designs and Queries: We’ll review your database designs and SQL queries to provide feedback and help you improve.
  - Peer Feedback: You’ll also get to see and provide feedback on your classmates’ database designs and queries – learning from each other is a great way to grow!

That’s the Beginner Level Introduction to Databases! You’ve learned about RDBMS fundamentals, basic SQL, and you’re ready to start building your own simple databases and querying them!

GPT Prompts for Further Learning

Understanding Database Indexing:
- “Explain the concept of database indexing and how it improves query performance.”
- “What are the different types of indexes in databases, and when should each be used?”
Advanced SQL Queries:
- “How do you write a complex SQL query involving multiple joins and subqueries?”
- “What are window functions in SQL, and how can they be used to perform advanced data analysis?”
NoSQL Databases:
- “Compare and contrast NoSQL databases with relational databases. What are the use cases for each?”
- “Explain the different types of NoSQL databases (document, key-value, column-family, graph) and provide examples of each.”
Database Normalization:
- “What is database normalization, and why is it important? Explain the different normal forms.”
- “Provide examples of how to normalize a database schema from 1NF to 3NF.”
Database Transactions and Concurrency:
- “Explain the concept of database transactions and the importance of ACID properties.”
- “How do databases handle concurrency and ensure data consistency in a multi-user environment?”

National Digital Capacity Library - Sri Lanka

3.2.1. Introduction to Databases

3. Database Technologies: Organizing and Protecting Your Data

3.2. Beginner Level: Getting Started with Databases

3.2.1. Introduction to Databases: The Basics of Storing Information

GPT Prompts for Further Learning

Future Reading Links