Databases & Tables 🗄️

The Foundation of Every Data-Driven Application

Beginner Friendly 20 min read Lesson 2 of 13

Welcome to the World of Databases! 🎯

In this lesson, you'll learn about Databases and Tables - the foundation of every data-driven application. Think of a database as a digital filing cabinet and tables as the drawers!

By the end of this lesson, you'll be able to:

✅ Understand what databases and tables are
✅ Create your first database
✅ Create tables with proper columns
✅ Understand primary keys and constraints
✅ Connect to a database from C#
✅ Run simple SQL queries from C#

What is a Database?

In Simple Words: A database is a digital filing cabinet where you store and organize your data. It's like a giant Excel spreadsheet on steroids!

📁 Real-World Analogy

Think of a database like a filing cabinet:

🏢 The Database = The entire filing cabinet
📂 Tables = The drawers (Students, Courses, Teachers)
📄 Rows = The files in each drawer (each student)
🏷️ Columns = The information on each file (Name, Age, Email)

Result: Organized, searchable data!

💻 In Code

Creating a database:

-- Create your first database
CREATE DATABASE SchoolDB;

-- Switch to it
USE SchoolDB;

-- Now you can create tables!

📌 Command: CREATE DATABASE + Name

📌 Tip: Always use USE to switch to your database

😂 Fun Joke: Why did the database break up with the spreadsheet? Because it couldn't handle the relationship! (Get it? Relationships? Tables? ...I'll stop 😅)

What is a Table?

In Simple Words: A table is where you store your data. It has columns (types of data) and rows (the actual data).

👀 What a Table Looks Like

StudentId	FirstName	LastName	Email
1	John	Doe	john@email.com
2	Jane	Smith	jane@email.com
3	Ali	Hassan	ali@email.com

📌 Columns: StudentId, FirstName, LastName, Email

📌 Rows: Each student is one row

💻 Creating a Table

-- Create a Students table
CREATE TABLE Students (
    StudentId INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) NOT NULL UNIQUE
);

📌 Understanding the code:

IDENTITY(1,1) = Auto-number (1,2,3...)
PRIMARY KEY = Unique ID for each row
NOT NULL = Must fill this column
UNIQUE = No duplicate emails

💡 Pro Tip: Always have a PRIMARY KEY in every table. It's like each person having a unique ID card!

Primary Keys and Constraints

What are Constraints? They're rules that keep your data clean and correct!

✅ Types of Constraints

PRIMARY KEY - Unique ID for each row
NOT NULL - Column cannot be empty
UNIQUE - No duplicate values
DEFAULT - Set a default value
CHECK - Validate the data
FOREIGN KEY - Link to another table

💡 Example: CHECK (Age >= 18) means you can't add a student under 18!

💻 Constraints in Action

-- Create with ALL constraints
CREATE TABLE Courses (
    CourseId INT IDENTITY(1,1) PRIMARY KEY,
    Title NVARCHAR(100) NOT NULL,
    Credits INT NOT NULL CHECK (Credits BETWEEN 1 AND 6),
    CreatedDate DATE DEFAULT GETDATE()
);

📌 This ensures:

✅ Every course has a unique ID
✅ Title is always filled in
✅ Credits are between 1-6
✅ CreatedDate is automatically today

😂 Joke: Why did the NOT NULL constraint break up with the column? Because it couldn't handle the empty space! (I'll show myself out 😅)

Connect to Database from C#

Why This Matters: This is how your C# apps talk to the database!

📝 Connection String

The magic string that connects C# to SQL Server:

// SQL Server Connection String
"Server=localhost;" +
"Database=SchoolDB;" +
"Trusted_Connection=True;" +
"TrustServerCertificate=True;"

📌 Parts:

Server = Where your SQL Server is
Database = Which database to use
Trusted_Connection = Use Windows login

🎯 Complete C# Example

// Full working C# example
using System;
using System.Data;
using Microsoft.Data.SqlClient;

class Program
{
    static void Main()
    {
        // 1️⃣ Connection string
        string connString = "Server=localhost;" +
                           "Database=SchoolDB;" +
                           "Trusted_Connection=True;";
        
        // 2️⃣ Connect and query
        using (var conn = new SqlConnection(connString))
        {
            conn.Open();
            Console.WriteLine("✅ Connected to database!");
            
            // 3️⃣ Query data
            string query = "SELECT * FROM Students";
            using (var cmd = new SqlCommand(query, conn))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"Name: {reader["FirstName"]}");
                    }
                }
            }
        }
    }
}

✅ This connects and reads data from your database!

💡 Pro Tip: Always use using statements for database connections. They automatically close the connection, even if there's an error!

Let's Practice! 🎮

Your Challenge: Create a complete database and table, then connect from C#!

Your Mission:

Create a Database
- Name it "LibraryDB"
- Use SSMS or SQL query
Create a Table
- Table name: Books
- Columns: BookId (PK), Title, Author, Year
- Add constraints: NOT NULL, CHECK (Year > 1900)
Insert Some Data
- Add 3-4 books
- Use INSERT INTO command
Connect from C#
- Write C# code to read the data
- Display the books in the console

💡 What You'll Learn:

✅ How to create a database
✅ How to design a table
✅ How to add constraints
✅ How to insert data
✅ How to read data from C#
✅ Build a complete data system!

Result: A complete database with a C# connection!

-- ===== SQL SCRIPT - LIBRARY DATABASE =====

-- 1️⃣ Create Database
CREATE DATABASE LibraryDB;
GO

-- 2️⃣ Switch to it
USE LibraryDB;
GO

-- 3️⃣ Create Books Table
CREATE TABLE Books (
    BookId INT IDENTITY(1,1) PRIMARY KEY,
    Title NVARCHAR(200) NOT NULL,
    Author NVARCHAR(100) NOT NULL,
    Year INT NOT NULL CHECK (Year > 1900)
);
GO

-- 4️⃣ Insert Sample Books
INSERT INTO Books (Title, Author, Year)
VALUES 
    ('C# Programming', 'John Doe', 2020),
    ('Clean Code', 'Robert Martin', 2008),
    ('Design Patterns', 'Erich Gamma', 1994);
GO

-- 5️⃣ View the data
SELECT * FROM Books;
GO

-- ===== C# CODE - READ FROM DATABASE =====

// Complete C# program to read the Library database
using System;
using System.Data;
using Microsoft.Data.SqlClient;

class LibraryApp
{
    static void Main()
    {
        // Connection string
        string connString = "Server=localhost;" +
                           "Database=LibraryDB;" +
                           "Trusted_Connection=True;" +
                           "TrustServerCertificate=True;";
        
        using (var conn = new SqlConnection(connString))
        {
            conn.Open();
            Console.WriteLine("📚 LIBRARY BOOKS\n");
            
            string query = "SELECT * FROM Books ORDER BY Year";
            using (var cmd = new SqlCommand(query, conn))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"📖 {reader["Title"]}");
                        Console.WriteLine($"   Author: {reader["Author"]}");
                        Console.WriteLine($"   Year: {reader["Year"]}");
                        Console.WriteLine();
                    }
                }
            }
        }
    }
}

✅ Complete Solution! You've built a complete database and connected from C#!

🎉 What You Learned Today!

Database

Digital filing cabinet

Tables

Store your data

Primary Key

Unique identifier

Constraints

Keep data clean

C# Connection

Talk to database

Real Example

Library System 📚

Ready!

For more SQL!

🎯 You're now ready to build database-driven applications!

Previous Lesson Next Lesson

Test Your Knowledge - Take Quiz