# Data Modelling Project: Vocational School Database

# Project Summary

This work demonstrates **end-to-end data modelling**, from a school’s business requirements to a running database environment. I designed and implemented a **3rd Normal Form Relational Database** for a vocational school according to their business requirements. **The db is containerized in Docker and queried in PostgreSQL.**

# Project Repository

[Click here for Github Project repository](https://github.com/Akina-Aoki/data_modelling_lab_aira_franco)

# Learning Repository for Data Modelling

[Click here](https://github.com/Akina-Aoki/data_modelling_aira_franco_de25.git) for Data Modelling Learning Log Documentation.  
A brief blog series documenting my hands-on learning of OLTP (Online Transaction Processing) with practical Docker containerization and database design with 3rd Normal Form Validation.

# Project Video

Brief explanation of the project.  
(Video used for course submission. This is not the full explanation of the process).

[![Data Modelling Video](https://img.youtube.com/vi/L8Wg-WHX-T8/0.jpg align="left")](https://www.youtube.com/watch?v=L8Wg-WHX-T8)

---

# Tech Stacks used

* PostgreSQL, Docker, PostgreSQL (DDL, DML, constraints, triggers), Git for version control
    

---

# Task

## Business requirements from the school (client) for their database

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1770380790664/716cb896-a6b5-4ee6-8a3e-8b176e2e3a81.png align="center")

YrkesCo manages is a vocational school that needs their own database for administration tasks. They require that the database system must support:

* **Students** are able to enroll in **one program (2 years) or courses (standalone course)**. Not allowed to enroll at both at the same time.
    
* **Programs** consist of multiple courses and are approved in rounds → each approval creates a **Class**.
    
* **Courses** can be program-based or standalone.
    
* **Teaching** is delivered by **internal staff as instructors or consultants**, governed by active contracts.
    
* **Program Managers** are staff members managing **exactly a maximum of three classes**.
    
* **Contracts** are time-bound, role-locked, campus-specific, and support planned employment.
    
* **Sensitive data** is segregated (privacy by design).
    
* **Campuses** are extensible beyond Stockholm and Gothenburg.
    

---

## Implementation

Design the Conceptual Model → Logical Model → Physical Model

### **Conceptual Model**

This model replaces current Excel-based system with a structured solution that tracks **students, staff, consultants, programs, courses, and campuses** across YrkesCo's operations.

* ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1770380910982/adaebf35-7cc6-4d0a-aa50-e394e4a30919.png align="center")
    
    **People**
    
    * **Students** – Individuals enrolled at the school
        
    * **Staff** – Internal employees (program managers, administrators)
        
    * **Consultants** – External instructors hired to teach specific courses
        
    * **Private Details** – Secure storage for sensitive information (personal IDs, emails, addresses)
        
    
    **Education Delivery**
    
    * **Programs** – Two-year education offerings (e.g., Data Engineering)
        
    * **Courses** – Individual units of education (can be part of a program or standalone)
        
    * **Classes** – Approved delivery instances of programs (e.g., "DE24", "DE25") assigned to specific campuses
        
    
    **Contracts & Locations**
    
    * **Staff Contracts** – Employment agreements defining roles, salaries, and campus assignments
        
    * **Consultant Contracts** – Service agreements for external instructors
        
    * **Campus** – Physical school locations where classes are delivered
        
    * **Consultant Company** – Organizations that provide consultant services
        
    
    **Tracking Relationships**
    
    * **Enrollment** – Connects students to their classes
        
    * **Consultant Teach** – Tracks which consultants teach which courses
        
    * **Program Manager Management** – Assigns staff members as managers for specific programs
        
    
    **Key Business Rules Built Into the Model**
    
    ✅ **One active contract per person** – Staff and consultants can only hold one role at a time  
    ✅ **Privacy protection** – Sensitive data is isolated and access-controlled  
    ✅ **Flexible enrollment** – Students can enroll in a full program OR a standalone course (but not both simultaneously)  
    ✅ **Campus assignment** – Every class happens at exactly one campus  
    ✅ **Role clarity** – Contracts define whether someone is staff or a consultant—roles can't change mid-contract
    

### **Logical Model**

The logical model ensures that **the data is structured correctly before implementation**. It eliminates redundancy, enforces business rules through database constraints, and creates a solid foundation for the physical database (PostgreSQL).

* ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1770380978294/925267bd-8fa7-4207-9303-a4b4aa5905f1.png align="center")
    

**Key Business Rules Enforced by the Logical Model**

| **Rule** | **How It's Enforced** |
| --- | --- |
| One active contract per person | Foreign key from `Staff_Contract` → `Staff` (1:1 relationship) |
| Students enroll in one program OR one standalone course | Enforced by constraints in `Enrollment` table |
| Roles can't change without ending contract | Contracts are immutable—role is tied to `Staff_Contract` or `Consultant_Contract` |
| Sensitive data is isolated | `Private_Details` is a separate table, referenced only via FK |
| Program managers can manage max 3 classes | Constraint in `Program_Manager_Management` |

**Many-to-Many Relationships Are Resolved**

When two entities have a **many-to-many** relationship (e.g., consultants can teach many courses, courses can have many consultants), create a **bridge table**:

* **Consultant\_Teach** → Connects consultants to courses
    
* **Enrollment** → Connects students to classes
    
* **Program\_Manager\_Management** → Connects staff to classes they manage
    

### Physical Model

**Data Definition Language (DDL): The Blueprint for the Database**

Exact link to the sql scripts: [data\_modelling\_lab\_aira\_franco/yh\_labb/sql/init at main · Akina-Aoki/data\_modelling\_lab\_aira\_franco](https://github.com/Akina-Aoki/data_modelling_lab_aira_franco/tree/main/yh_labb/sql/init)

This is the **schema**, the blueprint of how the database is structured. This is where I implement the physical model from the logical model. **Create tables, constraints, indexes, functions and triggers are found here.** 👉 **This SQL file is what makes those business rules impossible to break.**

```python
# Sample
CREATE TABLE IF NOT EXISTS program (
  program_id VARCHAR(10) PRIMARY KEY,
  program_name VARCHAR(50) NOT NULL,
  program_credits INTEGER NOT NULL CHECK (program_credits > 0),
  program_description TEXT
);
```

## Docker

### Idempotency and Repoducability set in docker-compose

In docker-compose.yml, this set up enforces idempotency. I can run \`docker compose up\` multiple times and start the system many times without breaking anything.

This works because PostgreSQL saves its data in a Docker volume. Once the database is created the first time, PostgreSQL sees that the data already exists and **does not run the setup scripts again**.

Exact link to the docker-compose yml: [data\_modelling\_lab\_aira\_franco/yh\_labb/docker-compose.yml at main · Akina-Aoki/data\_modelling\_lab\_aira\_franco](https://github.com/Akina-Aoki/data_modelling_lab_aira_franco/blob/main/yh_labb/docker-compose.yml)

```python
# docker-compose.yml    
volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./sql/init:/docker-entrypoint-initdb.d:ro
      - ./sql:/sql:ro
```

### Populate the Database with \`INSERT INTO\`

This query in the \`02\_seed.sql\` inserts program records but safely skips them if they already exist, allowing the script to be run multiple times without creating duplicates.

```sql
/* SAMPLE -------------------------
   2) Programs
   ------------------------- */
INSERT INTO program (program_id, program_name, program_credits, program_description) VALUES
  ('DE', 'Data Engineering', 400, 'Two-year vocational program focused on data engineering fundamentals.'),
  ('UX', 'UX Design', 400, 'Two-year vocational program focused on UX design methods and practice.')
ON CONFLICT (program_id) DO NOTHING;
```

### Spin up a Docker Container

The whole documentation available in this exact link:  
[data\_modelling\_lab\_aira\_franco/yh\_labb/documentation/06\_docker\_](https://github.com/Akina-Aoki/data_modelling_lab_aira_franco/blob/main/yh_labb/documentation/06_docker_implementation.md)[implementation.md](http://implementation.md) [at main · Akina-Aoki/data\_modelling\_lab\_aira\_franco](https://github.com/Akina-Aoki/data_modelling_lab_aira_franco/blob/main/yh_labb/documentation/06_docker_implementation.md)

At this point, I spin up PostgreSQL inside a Docker container. This gives me a reproducible database environment where the schema and data are initialized automatically. Using Docker makes it easy to reset or rerun the database while keeping the setup consistent across runs.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1770383427136/9a120376-0d63-4f69-8108-1b7cb311e732.png align="center")

---

# Database Results

**Sample 1 of the database showing the Course and Program name in a specific campus, and which consultant is teaching. Also shows the business requirement fulfilled that a standalone course, not tied to a program, can exist.**

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1770383764112/ec2d0536-beb4-4352-825f-c0303b2487f7.png align="center")

**Sample 2 of the database showing that the school’s requirement is fulfilled that a program manager can, at a maximum handle 3 classes at a time. This shows the constraints being triggered.**

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1770383912175/c0006229-4b06-408a-9fae-bc848f32526d.png align="center")

**Sample 3 of the results showing the database of the students and their personal information being accessed. Another is the database of the contract information of the school employees.**

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1770384042466/51c2debd-339a-4d5a-b224-53be164c32b2.png align="center")

## Scalable Database Architecture

The database is built to scale alongside the school, supporting future expansion such as new programs, additional campuses, and new student cohorts without requiring structural changes.

**Sample 4 of INSERTS of a new class in the database**

Exact link for this query: [data\_modelling\_lab\_aira\_franco/yh\_labb/sql/04\_insert\_new\_cohort.sql at main · Akina-Aoki/data\_modelling\_lab\_aira\_franco](https://github.com/Akina-Aoki/data_modelling_lab_aira_franco/blob/main/yh_labb/sql/04_insert_new_cohort.sql)

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1770384371011/1b060449-ddac-4667-9b8f-e1fb5a1bc8b7.png align="center")

---

# Conclusion

Building this end-to-end database solution for a school’s database taught me how data modeling decisions ripple through every part of a system, from normalization and constraint enforcement to deployment with Docker. I successfully translated business requirements into a scalable, auditable PostgreSQL schema that can support real operations. This project solidified my confidence in designing 3rd normal form databases that are not only technically sound but also built to last.