Lesson 2: Database Design Basics

Database design is the process of planning and organizing the data in a way that is efficient, accurate, and easy to manage. Good database design ensures that data is stored logically, can be retrieved easily, and is consistent.

1. Components of Database Design

a. Tables

Tables are the core of a relational database. Each table holds data about a specific subject (e.g., employees, products, etc.). Tables are made up of columns and rows.

Example:

| EmployeeID | Name     | Department | Salary  |
|------------|----------|------------|---------|
| 1          | John Doe | HR         | 50000   |
| 2          | Jane Smith | IT       | 60000   |

b. Columns

Columns define the data types and attributes of the table. Each column has a name, and the data type specifies the kind of data it holds (e.g., numbers, text, dates).

Example Column Names:

• EmployeeID (integer)
• Name (string)
• Department (string)
• Salary (decimal)

c. Primary Key

A primary key is a unique identifier for each record in a table. It ensures that no two rows in the table have the same value in this column.

Example:

EmployeeID could be a primary key in an employee table because it uniquely identifies each employee.

d. Foreign Key

A foreign key is a column in one table that links to the primary key of another table. It establishes a relationship between two tables.

Example:

In an Orders table, a CustomerID could be a foreign key that links to the Customers table.

2. Types of Relationships Between Tables

a. One-to-One

A one-to-one relationship means each record in the first table corresponds to one and only one record in the second table.

Example: One person has one passport.

b. One-to-Many

A one-to-many relationship means one record in the first table can be linked to multiple records in the second table.

Example: A customer can place many orders.

c. Many-to-Many

A many-to-many relationship means records in one table can be linked to many records in another table, and vice versa.

Example: Students can enroll in multiple courses, and each course can have many students.

3. Normalization

Normalization is the process of organizing data to reduce redundancy and improve data integrity.

Steps of Normalization:

• 1st Normal Form (1NF): Eliminate duplicate columns; create unique rows.
• 2nd Normal Form (2NF): Remove partial dependency; ensure each attribute is fully dependent on the primary key.
• 3rd Normal Form (3NF): Remove transitive dependency; non-key attributes should depend only on the primary key.

4. Example: Creating Tables with Relationships

Let’s design two tables: Customers and Orders.

Step 1: Create the Customers Table:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100)
);

Step 2: Create the Orders Table:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

5. Best Practices for Database Design

• Use meaningful column names: Choose column names that clearly describe the data stored.
• Avoid storing calculated data: Instead, calculate values dynamically when needed.
• Plan for future growth: Ensure your design can scale as data grows.
• Use indexes on frequently queried columns: This improves query performance.

6. Example: Designing a Simple Database for a Library

Let’s create tables for Books, Authors, and Library Transactions.

Step 1: Create the Authors Table:

CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    Name VARCHAR(100),
    BirthYear INT
);

Step 2: Create the Books Table:

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(255),
    AuthorID INT,
    PublicationYear INT,
    Genre VARCHAR(50),
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

Step 3: Create the LibraryTransactions Table:

CREATE TABLE LibraryTransactions (
    TransactionID INT PRIMARY KEY,
    BookID INT,
    BorrowerID INT,
    BorrowDate DATE,
    ReturnDate DATE,
    FOREIGN KEY (BookID) REFERENCES Books(BookID)
);