Skip to content

Database

Jump to bottom
uswah-batool edited this page Apr 13, 2025 · 3 revisions

Important information for Deadline 2

‼️  This chapter should be completed by Deadline 2 (see course information at Lovelace)

📑  Chapter summary In this section students must design and implement the database structure (mainly the data model).

In this section you must implement:

  • The database table structure.
  • The data models (ORM)
  • Data models access methods (if needed)
  • Populating the database using the models you have created
In this section you should aim for a high quality small implementation instead of implementing a lot of features containing bugs and lack of proper documentation.

SECTION GOALS:

  1. Understand database basics
  2. Understand how to use ORM to create database schema and populate a database
  3. Setup and configure database
  4. Implement database backend
✔️     Chapter evaluation (max 5 points) You can get a maximum of 5 points after completing this section. More detailed evaluation is provided in the evaluation sheet in Lovelace.

Database design and implementation

Database design

📑  Content that must be included in the section Describe your database. The documentation must include:
  • A name and a short description of each database model. Describe in one or two sentences what the model represents.
  • An enumeration of the attributes (columns) of each model. Each attribute must include:
    • Its type and restrictions (values that can take)
    • A short description of the attribute whenever the name is not explicit enough. E.g. If you are describing the users of a "forum", it is not necessary to explain the attributes "name", "surname" or "address"
      • because their meanings are obvious.
    • Characteristics of this attribute (e.g. if it is unique, if it contains default values)
  • Connection with other models (primary keys and foreign keys)
  • Other keys
You can use the table skeleton provided below

For this section you can use a visual tool to generate a diagram. Be sure that the digram contains all the information provided in the models. Some tools you can use include: https://dbdesigner.net/, https://www.lucidchart.com/pages/tour/ER_diagram_tool, https://dbdiffo.com/

Database Design

Database Models

User Model

Represents an individual user in the system.

Name Type Restrictions Description Characteristics Links
user_id UUID Primary Key Unique user identifier Auto-generated
username String(50) Unique, Not Null User's display name
email String(120) Unique, Not Null User's contact email
password_hash String(128) Not Null Hashed password
role Enum 'member' or 'admin' User's permission level Default: 'member'
created_at DateTime Account creation timestamp Auto-set on creation
last_login DateTime Last login timestamp

Team Model

Represents a group of users working together.

Name Type Restrictions Description Characteristics Links
team_id UUID Primary Key Unique team identifier Auto-generated
name String(100) Not Null Team display name
description Text Team purpose description
created_at DateTime Team creation timestamp Auto-set on creation
lead_id UUID Team leader reference Foreign Key (User.user_id) ON DELETE: SET NULL

Project Model

Represents a collection of tasks with a common goal.

Name Type Restrictions Description Characteristics Links
project_id UUID Primary Key Unique project identifier Auto-generated
title String(200) Not Null Project title
description Text Detailed project description
status Enum 'planning', 'active', 'completed' Current project phase Default: 'planning'
deadline DateTime Project completion deadline
team_id UUID Not Null Owning team reference Foreign Key (Team.team_id) ON DELETE: CASCADE
category_id UUID Project category reference Foreign Key (Category.category_id) ON DELETE: SET NULL

Task Model

Represents an individual work unit within a project.

Name Type Restrictions Description Characteristics Links
task_id UUID Primary Key Unique task identifier Auto-generated
title String(150) Not Null Task title
description Text Detailed task description
status Enum 'pending', 'active', 'completed' Task progress state Default: 'pending'
priority Integer 1-5 Urgency level (1=low, 5=critical) Default: 3
deadline DateTime Task completion deadline
project_id UUID Not Null Parent project reference Foreign Key (Project.project_id) ON DELETE: CASCADE
assignee_id UUID Assigned user reference Foreign Key (User.user_id) ON DELETE: SET NULL

Category Model

Represents task/project classification groups.

Name Type Restrictions Description Characteristics Links
category_id UUID Primary Key Unique category identifier Auto-generated
name String(50) Unique, Not Null Category name
color String(7) Hex color code for UI display Default: #64748b

Team Membership Model (Junction Table)

Manages many-to-many relationship between Users and Teams.

Name Type Restrictions Description Characteristics Links
membership_id UUID Primary Key Unique membership identifier Auto-generated
user_id UUID Not Null Member user reference Foreign Key (User.user_id) ON DELETE: CASCADE
team_id UUID Not Null Team reference Foreign Key (Team.team_id) ON DELETE: CASCADE
role Enum 'member' or 'admin' Team-specific role Default: 'member'

✏️ Do not forget to include a diagram presenting the relations

erDiagram
    USER {
        uuid user_id PK
        string username "Unique, Not Null"
        string email "Unique, Not Null"
        string password_hash "Not Null"
        string role "Default: 'member'"
        datetime created_at "Auto-set on creation"
        datetime last_login
    }
    TEAM {
        uuid team_id PK
        string name "Not Null"
        string description
        datetime created_at "Auto-set on creation"
        uuid lead_id FK
    }
    PROJECT {
        uuid project_id PK
        string title "Not Null"
        string description
        string status "Default: 'planning'"
        datetime deadline
        uuid team_id FK
        uuid category_id FK
    }
    TASK {
        uuid task_id PK
        string title "Not Null"
        string description
        string status "Default: 'pending'"
        int priority "1-5, Default: 3"
        datetime deadline
        uuid project_id FK
        uuid assignee_id FK
    }
    CATEGORY {
        uuid category_id PK
        string name "Unique, Not Null"
        string color "Default: #64748b"
    }
    TEAM_MEMBERSHIP {
        uuid membership_id PK
        uuid user_id FK
        uuid team_id FK
        string role "Default: 'member'"
    }

    USER ||--o{ TEAM : "leads"
    TEAM ||--o{ PROJECT : "owns"
    PROJECT ||--o{ TASK : "contains"
    CATEGORY ||--o{ PROJECT : "categorizes"
    USER ||--o{ TASK : "assigned to"
    USER ||--o{ TEAM_MEMBERSHIP : "joins"
    TEAM ||--o{ TEAM_MEMBERSHIP : "has members"
Loading

Database implementation

💻     TODO: SOFTWARE TO DELIVER IN THIS SECTION The code repository must contain:
  1. The ORM models and functions
  2. A .sql dump (or similar data structure) of a database or the .db file (if you are using SQlite). The provided document must contain enough information to replicate your database. You must provide a populated database in order to test your models.
  3. The scripts used to generate your database (if any)
  4. A README.md file containing:
    • All dependencies (external libraries) and how to install them
    • Define database (MySQL, SQLite, MariaDB, MongoDB...) and version utilized
    • Instructions how to setup the database framework and external libraries you might have used, or a link where it is clearly explained.
    • Instructions on how to setup and populate the database.
  5. If you are using python a `requirements.txt` with the dependencies

✏️ You do not need to write anything in this section, just complete the implementation.

Use of AI

📝 If you have use AI during this deliverable, explain what tool/s you have used and how.

Resources allocation

Task Student Estimated time
Database Design Muhammad Hassan Sohail & Uswah Batool 6 hours
Database Implementation Mathéo Morin & Syed Abdullah Hassan 6 hours

Report index

Clone this wiki locally