Database Design

Introduction

Welcome to the world of database design! Databases are essential for storing, managing, and retrieving data efficiently. This section will cover the fundamentals of database design, including key concepts, normalization, and practical examples.

Concept

What is a Database?

A database is an organized collection of structured information that can be easily accessed, managed, and updated. Databases are crucial for applications that require persistent storage and efficient data retrieval.

Key Components of a Database

1. Data: The actual information stored in the database.
2. Schema: The blueprint or structure of the database, defining how data is organized.
3. Tables: Collections of related data items (records) with columns (fields).
4. Records: Individual entries within a table.
5. Fields: Specific attributes of a record.

Types of Databases

• Relational Databases: Use tables to store and manage data, with relationships between tables defined by keys.
• NoSQL Databases: Store unstructured or semi-structured data and are highly scalable.

Examples

Let's dive into some practical examples to understand database design better.

Example 1: Designing a Simple Database for a Library

Imagine you're designing a database for a library. The library has books, authors, and members. Here’s how you might structure the database:

Tables

1. Books

   • book_id (Primary Key)
   • title
   • author_id (Foreign Key)

2. Authors

   • author_id (Primary Key)
   • name

3. Members

   • member_id (Primary Key)
   • name
   • email

SQL Schema

CREATE TABLE Authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE Books (
    book_id INT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

CREATE TABLE Members (
    member_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

Example 2: Normalization

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. Let's normalize a simple example.

Unnormalized Table

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100),
    professor_name VARCHAR(100)
);

This table has redundancy because the same professor’s name is repeated for multiple students in the same course.

Normalized Tables

CREATE TABLE Professors (
    professor_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    professor_id INT,
    FOREIGN KEY (professor_id) REFERENCES Professors(professor_id)
);

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    course_id INT,
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);