Database Design and Schema Creation

Day 2: Database Design and Schema Creation

Date: January 20, 2025

Understanding Database Design

Database design is the foundation of any application that handles data. A well-designed schema ensures efficient data storage, retrieval, and management. This process involves organizing data into tables, defining relationships, and ensuring consistency.

Key Concepts

1. Tables, Rows, and Columns

• Table: Represents an entity, such as users or products.
• Rows: Represent individual records (e.g., a single user).
• Columns: Represent the attributes of the entity (e.g., name, email).

2. Primary Keys and Foreign Keys

• Primary Key: A unique identifier for each row in a table (e.g., id in a users table).
• Foreign Key: A field in one table that references the primary key in another table, establishing a relationship.

Normalization

Normalization is the process of organizing data to minimize redundancy and improve integrity. The key levels of normalization are:

1. First Normal Form (1NF)

• Ensure that each column contains atomic (indivisible) values.
• Each row must be unique.

2. Second Normal Form (2NF)

• Meet all requirements of 1NF.
• Remove partial dependencies by ensuring that all non-key attributes depend entirely on the primary key.

3. Third Normal Form (3NF)

• Meet all requirements of 2NF.
• Remove transitive dependencies (non-key attributes should not depend on other non-key attributes).

Practice Activity: Designing a Schema

Let’s design a schema for an e-commerce application. This application will include:

• Users (customers).
• Products.
• Orders.

Schema Design

1. Users Table

2. Products Table

3. Orders Table

Defining Relationships

1. One-to-One:
   • A user may have one profile, and a profile belongs to one user.
2. One-to-Many:
   • A user can place multiple orders, but an order belongs to one user.
3. Many-to-Many:
   • Products can appear in multiple orders, and orders can include multiple products. This requires a junction table (e.g., order_items) to manage the many-to-many relationship.

Hands-On Practice

Using SQLite, you can implement the above schema:

Step 1: Create Tables

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL
);

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    stock INTEGER NOT NULL
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id),
    FOREIGN KEY (product_id) REFERENCES products (id)
);

Step 2: Insert Sample Data

-- Insert sample users
INSERT INTO users (name, email, password) VALUES 
('Arjun', 'arjun@example.com', 'password123'),
('Omkar', 'omkar@example.com', 'securepass456');

-- Insert sample products
INSERT INTO products (name, price, stock) VALUES 
('Laptop', 1200.50, 10),
('Mouse', 25.75, 100);

-- Insert sample orders
INSERT INTO orders (user_id, product_id, quantity) VALUES 
(1, 1, 1),
(2, 2, 2);

Step 3: Query the Data

Retrieve orders with user and product details:

SELECT 
    orders.id AS order_id,
    users.name AS customer,
    products.name AS product,
    orders.quantity,
    orders.order_date
FROM orders
JOIN users ON orders.user_id = users.id
JOIN products ON orders.product_id = products.id;

Real-World Example

An e-commerce app like Amazon relies heavily on databases. By understanding schema design, you can replicate how such systems store and manage users, products, and orders efficiently.

Common Interview Questions

1. What are primary and foreign keys? Explain their role.
2. What is normalization? Why is it important?
3. How would you design a schema for a social media application?

Outcome for the Day

By the end of Day 2, you should:

• Understand how to design a database schema.
• Create tables with proper relationships.
• Apply normalization to optimize your design.

Tomorrow, we’ll explore SQL Queries for Data Manipulation and Retrieval to interact with the schema effectively.

Let me know if you need further clarification! 😊

Author Of article : arjun Read full article