Skip to main content

Course Progress

Loading...

📝 Homework: Create a Database Schema

Due: Before next session
Module 3: Session 1

Assignment Objectives

  • Apply database design concepts learned in Session 1
  • Create a complete database schema from scratch
  • Practice using MySQL commands and phpMyAdmin
  • Think through real-world data relationships
  • Document your database design decisions

🎯 Your Assignment

Design and implement a database schema for aTask Management Application

📋
The Scenario
You've been hired to create the database for "TaskMaster Pro" - a simple task management application where users can create projects, add tasks, assign them to team members, and track progress.

📋 Requirements

Minimum Database Requirements

1. Users Table

  • Store user information (username, email, password)
  • Track when users joined
  • User roles (admin, member)

2. Projects Table

  • Project name and description
  • Project owner (link to users)
  • Start and end dates
  • Project status (planning, active, completed)

3. Tasks Table

  • Task title and description
  • Belongs to a project
  • Assigned to a user
  • Priority (low, medium, high)
  • Status (todo, in_progress, done)
  • Due date

4. Additional Tables (You Decide!)

  • Comments on tasks?
  • Categories or tags?
  • Team memberships?
  • File attachments?

🎨 Example Schema Design

graph TB Users[users] Projects[projects] Tasks[tasks] Comments[comments] Users -->|creates| Projects Projects -->|contains| Tasks Users -->|assigned to| Tasks Tasks -->|has| Comments Users -->|writes| Comments Users -.->|id| U1[PK: id
username
email
password
role
created_at] Projects -.->|id| P1[PK: id
FK: owner_id
name
description
status
start_date
end_date] Tasks -.->|id| T1[PK: id
FK: project_id
FK: assigned_to
title
description
priority
status
due_date] style Users fill:#3b82f6,color:white style Projects fill:#10b981,color:white style Tasks fill:#f59e0b,color:white style Comments fill:#ef4444,color:white

📝 Starter Template

Use This as Your Starting Point

-- Create the database
CREATE DATABASE taskmaster_pro;
USE taskmaster_pro;

-- Create users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'member') DEFAULT 'member',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create projects table
CREATE TABLE projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    status ENUM('planning', 'active', 'completed') DEFAULT 'planning',
    start_date DATE,
    end_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (owner_id) REFERENCES users(id)
);

-- TODO: Add your tasks table here
-- Think about: What fields do tasks need?
-- How do they relate to projects and users?

-- TODO: Add any additional tables you think are needed
-- Consider: Comments? Categories? Team members?

✅ Deliverables

📦
Submission Format
Create a folder namedlastname_firstname_taskmastercontaining:
  • schema.sql- Your database structure
  • sample_data.sql- INSERT statements
  • screenshot.png- phpMyAdmin view
  • design_notes.txt- Your explanations

💡 Tips and Hints

Design Tips

  • 🔑Primary Keys:Every table needs one!
  • 🔗Foreign Keys:Use them to link tables
  • 📝Naming:Use lowercase with underscores (user_id, not UserId)
  • ⚡Indexes:Add them to columns you'll search frequently
  • 🎯Data Types:Choose appropriate sizes (VARCHAR(50) vs VARCHAR(255))

Common Relationships to Consider

  • One user can own many projects
  • One project can have many tasks
  • One task is assigned to one user
  • One task can have many comments
  • Many users can be members of many projects (many-to-many)

🏆 Bonus Challenges

⭐
Extra Credit Opportunities
  • Implement a many-to-many relationship (project_members table)
  • Add file attachments table for tasks
  • Create a notifications table
  • Add task dependencies (tasks that depend on other tasks)
  • Implement soft deletes (deleted_at column instead of removing)

📚 Testing Your Schema

-- Test your relationships work correctly

-- 1. Insert test users
INSERT INTO users (username, email, password, role) VALUES
('john_manager', 'john@taskmaster.com', 'hashed_pass', 'admin'),
('jane_dev', 'jane@taskmaster.com', 'hashed_pass', 'member');

-- 2. Create a project
INSERT INTO projects (owner_id, name, description, status) VALUES
(1, 'Website Redesign', 'Complete overhaul of company website', 'active');

-- 3. Add tasks
-- Your INSERT statements here

-- 4. Test queries
-- Get all tasks for a project
SELECT * FROM tasks WHERE project_id = 1;

-- Get all projects for a user
SELECT * FROM projects WHERE owner_id = 1;

-- Get tasks assigned to a specific user
-- Your query here

Self-Assessment Checklist

✅
Before Submitting
Check that you have:
  • ☐ Created the database in MySQL
  • ☐ All tables have primary keys
  • ☐ Foreign keys are properly set up
  • ☐ Used appropriate data types
  • ☐ Added NOT NULL where needed
  • ☐ Included sample data
  • ☐ Tested all relationships
  • ☐ Documented design decisions
  • ☐ Taken phpMyAdmin screenshot
  • ☐ SQL runs without errors

Helpful Resources