Emergency Station Database

A small relational database that simulates data collected and processed by an emergency medical station.
The project includes:

• SQL Server schema design (teams, ambulances, patients, drugs, procedures, interventions)
• Python script to automatically populate the database with realistic fake data using Faker
• Jupyter notebook showing how to connect, query, and work with the data from Python

---

Overview

The goal of this project was to:

• design a relational schema for an emergency station,
• populate it with synthetic but realistic data,
• access and manipulate the data from Python for further analysis or reporting.

This is a good example of connecting SQL database design with Python data tooling.

---

Schema Design

Database: Emergency_Station

Main tables:

• Teams – emergency teams

  • team_ID (PK), Name, LastName

• Ambulances – ambulances assigned to teams

  • ambulance_ID (PK), team_ID (FK → Teams), registration_date, last_technical_checkup

• Drugs – drug inventory

  • DrugID (PK, identity), drugs_name, Quantity

• Procedures – medical procedures

  • ProcedureID (PK, identity), procedure_, team_ID (FK → Teams)

• Patients – patients handled by the station

  • patient_ID (PK), Name, LastName, team_ID (FK → Teams),
    DrugID (FK → Drugs), Telephone_number, date_of_birth

• Interventions – individual interventions

  • interventions_ID (PK), team_ID (FK → Teams),
    ambulance_ID (FK → Ambulances),
    patient_ID (FK → Patients),
    DrugID (FK → Drugs),
    ProcedureID (FK → Procedures)

The schema models how teams, ambulances, patients, drugs and procedures are linked in real emergency workflows.

---

Tech Stack

• Database: Microsoft SQL Server
• SQL: T-SQL (DDL for schema, FK constraints, identity columns)
• Python: pyodbc, faker, random
• Notebooks: Jupyter (Emergency_Station.ipynb)

---

Files

• SQL_cript.sql – creates the Emergency_Station database and all tables + ALTER statements
• generating_data.py – connects to SQL Server and populates all tables with fake data
• Emergency_Station.ipynb – notebook showing connection, basic operations and data access from Python
• README.md – project description

---

How to Set It Up

1. Create the database and tables

   • Open SQL_cript.sql in SQL Server Management Studio (SSMS)
   • Execute the script to create the Emergency_Station database and all tables

2. Configure Python connection

   sqlServerName = 'SERVER_NAME'  
   databaseName = 'Emergency_Station'
   trusted_connection = 'yes'

3. Generate fake data

   python generating_data.py

The script will:

• insert ~1000 teams, patients, interventions, etc.

• insert predefined lists of drugs and medical procedures

• create realistic relationships between teams, ambulances, patients, drugs and procedures

4. Explore in Jupyter

• Open Emergency_Station.ipynb and run the cells to:
  • connect to SQL Server with pyodbc
  • run example queries
  • inspect generated data

Data Generation Logic (Python)

generating_data.py uses Faker and Python logic to:

• create teams with unique IDs and random names
• assign ambulances to teams with registration and technical checkup dates
• insert a predefined list of drugs and procedures
• create patients with:
  • realistic IDs,
  • names, phone numbers and dates of birth (18–80 years old),
  • links to teams and prescribed drugs
• create interventions that join together:
  • team, ambulance, patient, drug and procedure All inserts go through parametrized queries using pyodbc.

What I Practised in This Project

• Designing a relational schema with multiple foreign keys and realistic relationships
• Writing DDL scripts (CREATE TABLE, ALTER TABLE) in T-SQL
• Connecting Python to SQL Server using pyodbc
• Generating synthetic but structured data with Faker
• Preparing a database that can later be used for:
  • analytics (SQL queries, BI tools),
  • ML experiments on structured medical/emergency data,
  • demos of data governance / data quality checks

Possible Next Steps

• Add views, stored procedures or triggers for common reporting needs
• Build Power BI / Python reports on top of this data
• Add basic data quality checks or anomaly detection
• Use the dataset as a base for ML models (e.g. predicting intervention types or resource usage)