Skip to content

DATABASE.md

Latest commit

 

History

History
167 lines (109 loc) · 5.54 KB

DATABASE.md

File metadata and controls

167 lines (109 loc) · 5.54 KB

🐘 Linux-CLI-Trainer Database

Table of Contents

Description

This directory contains the PostgreSQL database implementation for the Linux CLI Trainer application.

The database is responsible for storing the Linux command line exercises. The application uses this data to generate interactive practice sessions where users solve command line tasks and receive immediate feedback.

The database is designed to support flexible exercise categorization (topics and difficulty levels). It is used by an Express (Node.js) backend through a REST API and provides persistent storage for the React frontend application.

Set up

Refer to the PostgreSQL documentation for instructions on how to:

  • install PostgreSQL
  • start the database server
  • create a new database

Once you have created a database, run the initialization script from the repository root directory:

psql -d <database> -f database/init.sql

This will:

  • Create the database schema
  • Insert seed data
  • Execute all scripts in the correct order

Database Design

The database is intentionally kept relatively simple due to the scope and time constraints of the course project.

The main purpose of the database is to store exercises for the Linux CLI Trainer application. Each exercise contains:

  • a topic
  • a difficulty level
  • a question
  • a correct answer
  • a hint

The schema currently consists of two tables:

  • topic
  • exercise

topic

The topic table stores exercise categories such as navigation or file operations.

Each topic:

  • has a unique numeric primary key
  • has a unique name
  • can be referenced by multiple exercises

Keeping topics in a separate table makes the design more flexible, because new topics can be added later without changing the structure of the exercise table.

The current design uses a one-to-many relationship between topics and exercises, which keeps the data model simple and efficient for the current use case.

exercise

The exercise table stores the actual training tasks.

Each exercise:

  • belongs to exactly one topic
  • has a difficulty level
  • contains a question
  • contains one correct answer
  • contains one hint

The difficulty column is restricted with a CHECK constraint so that only the predefined values beginner, intermediate, and guru are allowed.

The relationship between exercise and topic is enforced with a foreign key constraint. A topic cannot be removed if exercises still reference it, which helps preserve referential integrity.

Normalization

The schema follows basic normalization principles while keeping the design small and practical.

First Normal Form (1NF)

All columns contain atomic values. There are no repeating groups or multi-valued attributes in the current design.

Second Normal Form (2NF)

Each table uses a single-column primary key:

  • topic.id
  • exercise.id

Because the primary keys are not composite, all non-key attributes depend fully on the whole primary key.

For example, in the exercise table, all attributes depend on exercise.id:

  • topic_id
  • difficulty
  • question
  • correct_answer
  • hint

Third Normal Form (3NF)

The schema does not contain clear transitive dependencies.

The topic entity has been separated into its own table, which reduces duplication and makes the structure more maintainable.

Although the question, correct_answer, and hint are logically related, they are treated as direct properties of an exercise rather than separate dependent entities.

Fourth Normal Form (4NF)

The schema does not include multi-valued dependencies that would require further decomposition.

Each exercise currently has only one topic, one correct answer, and one hint. If multiple independent values (such as multiple correct answers or multiple topics) were introduced, additional tables would be required to maintain 4NF.

Summary

exercise                        topic
+-----------------------+       +-------------+
| id               (pk) |   +---> id     (pk) |
| topic_id         (fk) ----+   | name        |
| difficulty            |       +-------------+
| question              |
| correct_answer        |
| hint                  |
+-----------------------+

The relationship between topic and exercise is one-to-many. One topic can be linked to multiple exercises, but each exercise belongs to exactly one topic.

Design Decisions and Trade-offs

The database was kept intentionally simple so that more time could be spent on the application itself.

For example, the current schema assumes that each exercise has:

  • One topic
  • One correct answer
  • One hint

In a more advanced version, the database could be extended to support:

  • Multiple correct answers for a single exercise
  • Multiple hints
  • More detailed difficulty modeling
  • Many-to-many relationships between exercises and topics

These features were left out to keep the implementation straightforward and suitable for the course project.