Funeral Home Case Management DB

Design a SQL Server database and Python CLI for funeral case management.

Introduction

30 Second Summary

When a family loses someone, the funeral home handling their arrangements is quietly coordinating dozens of details. Contact information, service preferences, vendor schedules, legal paperwork, and financial records all need to be tracked from the very first phone call through final disposition.

In this project, you will build a local SQL Server database and Python command-line application that manages funeral home cases. You will design a normalized schema, write stored procedures, and connect everything through a CLI tool that handles data entry, record lookup, and pre-need to at-need conversion.

What You'll Build

A production-ready Microsoft SQL Server database running inside a Docker container, paired with a Python CLI tool for full case lifecycle management.

By the end of this project, you'll have:

  • A SQL Server database with 8+ interconnected tables tracking funerals from first contact through final disposition.
  • A Python CLI tool that inserts records, queries cases by status, converts pre-need arrangements to at-need, and logs case notes.
  • A T-SQL stored procedure that transitions case status, records the date of death, and preserves original planning data.
  • Secret Mission: a services catalog with pricing that generates an itemized cost estimate during pre-need to at-need conversion.

Are there any prerequisites?

None. This project includes full setup instructions for Docker Desktop, SQL Server, Python, and all required database drivers. No prior database experience is assumed.

Set Up Docker and SQL Server

SQL Server does not run natively on macOS. The official approach for local development on Mac is running SQL Server inside a Docker container.

In this step, you will install Docker Desktop, pull the SQL Server 2022 container image, start it with a persistent volume, and connect to it using VS Code with the MSSQL extension.

In this step, get ready to:
  • Install Docker Desktop and pull the SQL Server 2022 image.
  • Run the SQL Server container with a persistent data volume.
  • Connect to SQL Server from VS Code with the MSSQL extension.
Install Docker Desktop and pull the SQL Server 2022 image

Docker Desktop provides a graphical interface and the docker CLI for running containers on your Mac.

  • Download Docker Desktop from the official Docker website.
  • Choose the correct installer for your Mac (Apple Silicon or Intel).
  • Double-click the downloaded Docker.dmg file, then drag the Docker icon to your Applications folder.
  • Open Docker Desktop by pressing Cmd+Space to open Spotlight, typing Docker, and pressing Enter.
  • Accept the Docker Subscription Service Agreement when prompted.
  • Wait for Docker Desktop to finish starting. You will see the Docker whale icon in your menu bar when it is ready.

Apple Silicon Macs and Rosetta

If you are on an Apple Silicon Mac (M1, M2, M3, or M4), Docker Desktop may prompt you to install Rosetta for running x86 containers. Accept the prompt if it appears.

You can also install Rosetta manually by running:

softwareupdate --install-rosetta

Make sure Rosetta emulation is enabled in Docker Desktop under Settings > General.

  • Verify Docker is installed by running this command in your terminal:
docker --version

✔️ I see a version number

Docker is installed and ready. Continue to the next instruction.

ⓧ Command not found

Docker Desktop is not installed or not in your PATH.

  • Go to https://www.docker.com/products/docker-desktop/ and download the installer for your Mac.
  • Double-click Docker.dmg and drag Docker to Applications.
  • Launch Docker Desktop from Applications and wait for it to finish starting.
  • Close and reopen your terminal, then run docker --version again.
  • Pull the SQL Server 2022 container image by running this command:
docker pull mcr.microsoft.com/mssql/server:2022-latest

What does this command do?

This downloads the official Microsoft SQL Server 2022 image from the Microsoft Container Registry. The image contains a full SQL Server Developer Edition, which is free for non-production use.

  • Wait for the download to complete. It may take a few minutes depending on your internet speed.

Seeing a platform warning?

On Apple Silicon Macs, you may see a warning about the image being built for a different platform. This is expected. The SQL Server image is x86-based and runs through Rosetta emulation.

If the pull fails entirely, make sure Docker Desktop is running and that Rosetta is enabled in Settings > General.

help me debug a Docker pull failure for the SQL Server image on my Mac

Run the SQL Server container with a persistent data volume

A Docker volume ensures your database files survive container restarts. Without one, all data is lost when the container stops.

  • Start the SQL Server container by running this command:
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=YourStrong@Passw0rd" -p 1433:1433 --name sql2022 --hostname sql2022 -v sqldata:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2022-latest

What does this command do?

  • -e "ACCEPT_EULA=Y" accepts the SQL Server license agreement.
  • -e "MSSQL_SA_PASSWORD=YourStrong@Passw0rd" sets the system administrator password for SQL Server.
  • -p 1433:1433 maps port 1433 on your Mac to port 1433 inside the container. This is SQL Server's default port.
  • --name sql2022 gives the container a friendly name you can reference later.
  • -v sqldata:/var/opt/mssql attaches a named volume so your database files persist between container restarts.
  • -d runs the container in the background.
  • Verify the container is running by running:
docker ps
  • You should see a row showing sql2022 with status Up and port 0.0.0.0:1433->1433/tcp.

Container not showing as Up?

If the container shows Exited status, check the logs for errors:

docker logs sql2022

Common causes include: password not meeting complexity requirements (must have uppercase, lowercase, digits, and special characters), or not enough memory allocated to Docker (SQL Server needs at least 2 GB).

help me fix my SQL Server Docker container that exits immediately

  • Wait about 10-15 seconds for SQL Server to finish starting inside the container, then check the logs:
docker logs sql2022
  • You should see a message indicating SQL Server is ready for client connections. This confirms your database engine is running.
Install VS Code with the MSSQL extension and connect to SQL Server

You need a tool to write and run SQL queries against your database. Microsoft's recommended tool is Visual Studio Code with the MSSQL extension. Azure Data Studio was retired in February 2026, and the MSSQL extension for VS Code is its replacement.

  • If you do not have VS Code installed, download it from https://code.visualstudio.com and install it.
  • Open VS Code by pressing Cmd+Space (macOS) or the Windows key (Windows), typing Visual Studio Code, and pressing Enter.
  • Open the Extensions view by pressing Cmd+Shift+X (macOS) or Ctrl+Shift+X (Windows).
  • In the search bar, type mssql.
  • Find SQL Server (mssql) in the results and click Install.
  • Wait for the extension to finish installing. You will see the MSSQL icon appear in the Activity Bar on the left side of VS Code.

What is the MSSQL extension?

The MSSQL extension lets you connect to SQL Server databases, browse objects, write T-SQL queries with IntelliSense, and view results directly in VS Code. It supports macOS, Windows, and Linux.

Now connect VS Code to your running SQL Server container.

  • Click the MSSQL icon in the Activity Bar on the left side of VS Code to open the Connections view.
  • Click Add Connection (the plus icon).
  • Fill in the connection profile fields as follows:
  • Server name: localhost
  • Authentication Type: SQL Login
  • User name: sa
  • Password: YourStrong@Passw0rd
  • Trust Server Certificate: check this field.
  • Save Password: Yes.
  • Encrypt: Mandatory.
  • Press Enter to create the connection profile and connect.

If the connection succeeds, you will see the server listed in the Connections view with a green dot. You can expand it to browse the system databases.

  • Verify the connection works by opening a new SQL file. Press Cmd+N (macOS) or Ctrl+N (Windows), then set the language to SQL by pressing Cmd+K M and selecting SQL.
  • Type the following query and run it with Cmd+Shift+E (macOS) or Ctrl+Shift+E (Windows):
SELECT @@VERSION;
  • You should see a result showing the SQL Server 2022 version string. This confirms your database is running and VS Code can communicate with it.

Connection refused or timed out?

Make sure your Docker container is still running by checking docker ps in your terminal.

If the container has stopped, restart it with:

docker start sql2022

Also verify you entered localhost (not localhost,1433) as the server name. Port 1433 is the default and does not need to be specified separately.

help me connect VS Code MSSQL extension to my local SQL Server Docker container

Your database environment is ready. Next up, you will install Python and the database drivers needed to connect your code to SQL Server.

Install Python and Database Drivers

Your SQL Server container is running and you have confirmed connectivity through Azure Data Studio. Now you need Python to talk to that database programmatically.

In this step you will set up Python, install the ODBC driver that bridges Python to SQL Server, and verify the full connection chain works end to end.

In this step, get ready to:
  • Verify Python 3.10+ is installed and create a virtual environment.
  • Install the ODBC Driver 18 for SQL Server and pyodbc.
  • Test the Python-to-SQL-Server connection.
Verify Python 3.10+ is installed and create a virtual environment

This project requires Python 3.10 or higher for full compatibility with pyodbc and the SQL Server ODBC driver.

  • Check your Python version by running this command in your terminal:
python3 --version

✔️ I see version 3.10 or higher

You see output like Python 3.10.x or higher. You are good to continue.

ⓧ I see an older version

macOS ships with a system Python that is often older than 3.10. Even after installing a newer version, python3 may still resolve to the older system binary.

  • Install Python 3.12 via Homebrew by running:
brew install python@3.12
  • After the install completes, close and reopen your terminal so the new PATH takes effect.
  • Run python3 --version again to confirm the version is 3.10 or higher.

Why Homebrew for Python?

Homebrew installs a recent Python to /opt/homebrew/bin/python3 on Apple Silicon Macs. It automatically places this ahead of the system Python in your PATH, so python3 resolves to the newer version after a shell restart.

ⓧ Command not found

Python 3 is not installed on your machine.

  • Install Python via Homebrew by running:
brew install python@3.12
  • After the install completes, close and reopen your terminal.
  • Run python3 --version to confirm it outputs Python 3.10 or higher.

Now create a project directory on your Desktop and set up a virtual environment inside it.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir funeral-home-db
cd funeral-home-db
  • Confirm the folder exists by running:
ls

You should see the funeral-home-db folder listed in the output.

  • Create a Python virtual environment by running:
python3 -m venv venv
  • Activate the virtual environment by running:
source venv/bin/activate

Your terminal prompt now shows (venv) at the beginning, confirming the virtual environment is active.

What is a virtual environment?

A virtual environment is an isolated Python installation inside your project folder. Packages you install here stay local to this project and do not interfere with your system Python or other projects.

  • Confirm the virtual environment is active by running:
which python3

You should see a path that includes funeral-home-db/venv/bin/python3 rather than a system path.

Don't see (venv) in your prompt?

Make sure you ran source venv/bin/activate from inside the funeral-home-db folder. If you opened a new terminal tab, you need to activate again.

Help me troubleshoot my virtual environment activation.

Install the ODBC Driver 18 for SQL Server and pyodbc

Python needs two pieces to talk to SQL Server: the Microsoft ODBC Driver (which handles the network protocol) and pyodbc (which gives Python a standard interface to that driver).

  • Install the ODBC Driver 18 for SQL Server by running these commands:
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_ACCEPT_EULA=Y brew install msodbcsql18 mssql-tools18

What does this install?

The msodbcsql18 package is Microsoft's ODBC Driver 18 for SQL Server. It also automatically installs unixODBC as a dependency, which is the driver manager that pyodbc requires on macOS.

The mssql-tools18 package includes sqlcmd, a command-line SQL client that is useful for quick queries outside Python.

  • If you are on an Apple Silicon Mac (M1, M2, M3, or M4), create the symlinks that let the ODBC driver find its configuration files:
sudo ln -s /opt/homebrew/etc/odbcinst.ini /etc/odbcinst.ini
sudo ln -s /opt/homebrew/etc/odbc.ini /etc/odbc.ini

Why are these symlinks needed?

On Apple Silicon, Homebrew installs to /opt/homebrew/ instead of /usr/local/. The ODBC driver manager looks for its config files in /etc/ by default, so the symlinks bridge the two locations.

  • Now install pyodbc inside your virtual environment by running:
pip install pyodbc
  • Verify pyodbc installed correctly by running:
python3 -c "import pyodbc; print('pyodbc version:', pyodbc.version)"

You should see output showing the pyodbc version number with no errors.

Seeing an error when importing pyodbc?

Make sure your virtual environment is still active (you see (venv) in your prompt). If you opened a new terminal tab, run source venv/bin/activate again from inside funeral-home-db before retrying the install.

Help me fix this pyodbc import error.

Test the connection from Python to SQL Server

With the driver and library installed, you can now verify that Python can reach the SQL Server container you started in Step 1.

  • Create a test script by running this command:
touch test_connection.py
  • Open test_connection.py in your code editor and paste the following code:
import pyodbc

# Connection string for local SQL Server Docker container
connection_string = (
    "Driver={ODBC Driver 18 for SQL Server};"
    "Server=tcp:localhost,1433;"
    "Database=master;"
    "UID=sa;"
    "PWD=YourStrong@Passw0rd;"
    "TrustServerCertificate=yes;"
)

# Attempt to connect and run a simple query
conn = pyodbc.connect(connection_string)
cursor = conn.cursor()
cursor.execute("SELECT @@VERSION")
row = cursor.fetchone()
print("Connected successfully!")
print(row[0])
conn.close()

What does this code do?

The connection string tells pyodbc which driver to use, where to find the server, and what credentials to present. It connects to the master database (which always exists on SQL Server) as a quick connectivity check.

The TrustServerCertificate=yes parameter skips SSL certificate validation, which is required for local Docker containers that use a self-signed certificate. SELECT @@VERSION returns the SQL Server version string to prove the connection works.

  • Save test_connection.py and run it:
python3 test_connection.py

You should see Connected successfully! followed by the SQL Server 2022 version string.

Connection refused or login failed?

Check that your SQL Server container is still running by opening Docker Desktop and looking for sql2022 in the container list. If it is stopped, start it again.

Make sure the password in the connection string matches the one you set when creating the container: YourStrong@Passw0rd. Also verify the symlinks are in place if you are on Apple Silicon (the sudo ln -s commands from the previous substep).

Help me debug this connection error.

Your Python environment is fully wired to SQL Server. Next up, you will design and create the database schema that powers the funeral home case management system.

Design and Create the Core Schema

Your SQL Server is running and you can connect to it from both Azure Data Studio and Python. Now it is time to design the database itself.

A funeral home tracks many interconnected pieces of information: decedent details, next-of-kin contacts, service preferences, staff assignments, and locations. In this step, you will build a normalized schema that separates lookup data (staff, locations, service types) from case-specific data (cases, decedents, contacts), connected by foreign keys.

In this step, get ready to:
  • Create the FuneralHomeDB database and three lookup tables for reference data.
  • Create the core tables (Cases, Decedents, Contacts) with constraints and foreign key relationships.
  • Insert seed data and verify the full schema is working.
Create the database and lookup tables

Lookup tables store reference data that rarely changes, like staff members, service types, and locations. Core tables reference these lookups through foreign keys, so the lookup tables must exist first.

  • In Azure Data Studio, create a new file by clicking File in the top menu bar, then New File.
  • Save the file as schema.sql inside your funeral-home-db project folder.
  • Add the database creation and lookup table definitions by pasting this code into schema.sql:
-- Funeral Home Case Management Database Schema
-- Run this script in Azure Data Studio or sqlcmd connected to your local SQL Server

CREATE DATABASE FuneralHomeDB;
GO

USE FuneralHomeDB;
GO

-- Lookup table: Staff members
CREATE TABLE Staff (
    StaffID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Role NVARCHAR(50) NOT NULL,
    Phone NVARCHAR(20),
    Email NVARCHAR(100),
    IsActive BIT NOT NULL DEFAULT 1
);
GO

-- Lookup table: Locations (chapels, cemeteries, etc.)
CREATE TABLE Locations (
    LocationID INT IDENTITY(1,1) PRIMARY KEY,
    LocationName NVARCHAR(100) NOT NULL,
    LocationType NVARCHAR(50) NOT NULL,
    Address NVARCHAR(200),
    City NVARCHAR(50),
    State NVARCHAR(2),
    ZipCode NVARCHAR(10),
    Phone NVARCHAR(20)
);
GO

-- Lookup table: Service types
CREATE TABLE ServiceTypes (
    ServiceTypeID INT IDENTITY(1,1) PRIMARY KEY,
    ServiceName NVARCHAR(100) NOT NULL,
    Category NVARCHAR(50) NOT NULL,
    Description NVARCHAR(500)
);
GO

What does this code do?

CREATE DATABASE FuneralHomeDB creates a brand new database on your SQL Server instance. GO is a batch separator that tells SQL Server to execute everything above it before moving on.

Each lookup table uses INT IDENTITY(1,1) for its primary key, which auto-increments starting at 1. NVARCHAR columns store Unicode text, which supports international characters in names and addresses.

The Staff table tracks employees by role (Funeral Director, Embalmer, etc.). Locations stores chapels, cemeteries, and crematories. ServiceTypes catalogs the kinds of services offered (visitation, cremation, burial).

  • Save schema.sql.

Why lookup tables first?

The Cases table you will create next has a foreign key pointing to Staff. SQL Server requires the referenced table to exist before you can create the constraint. Building lookup tables first guarantees all references will resolve.

Add the core tables with constraints and foreign keys

The core tables hold the actual case data. Each case links to a decedent and one or more contacts, and these relationships are enforced by foreign keys so orphaned records can never exist.

  • In schema.sql, add the Cases table below the ServiceTypes table by pasting this code:
-- Core table: Cases (the central record for each funeral)
CREATE TABLE Cases (
    CaseID INT IDENTITY(1,1) PRIMARY KEY,
    CaseNumber NVARCHAR(20) NOT NULL UNIQUE,
    CaseStatus NVARCHAR(20) NOT NULL DEFAULT 'PreNeed',
    CaseType NVARCHAR(20) NOT NULL,
    CreatedDate DATETIME2 NOT NULL DEFAULT GETDATE(),
    ConvertedDate DATETIME2 NULL,
    AssignedStaffID INT NULL,
    Notes NVARCHAR(MAX),
    CONSTRAINT CHK_CaseStatus CHECK (CaseStatus IN ('PreNeed', 'AtNeed', 'Completed', 'Cancelled')),
    CONSTRAINT CHK_CaseType CHECK (CaseType IN ('Burial', 'Cremation', 'Memorial', 'Other')),
    CONSTRAINT FK_Cases_Staff FOREIGN KEY (AssignedStaffID) REFERENCES Staff(StaffID)
);
GO

What do these constraints do?

CHK_CaseStatus restricts the CaseStatus column to exactly four valid values: PreNeed, AtNeed, Completed, or Cancelled. This means no code can accidentally write an invalid status to the database.

FK_Cases_Staff is a foreign key that ensures AssignedStaffID always points to a real row in the Staff table. If someone tries to assign a case to a non-existent staff member, the database rejects the insert.

  • Below the Cases table, add the Decedents and Contacts tables by pasting this code:
-- Core table: Decedents
CREATE TABLE Decedents (
    DecedentID INT IDENTITY(1,1) PRIMARY KEY,
    CaseID INT NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    MiddleName NVARCHAR(50),
    LastName NVARCHAR(50) NOT NULL,
    DateOfBirth DATE NULL,
    DateOfDeath DATE NULL,
    PlaceOfDeath NVARCHAR(200),
    SSN NVARCHAR(11),
    Gender NVARCHAR(20),
    MaritalStatus NVARCHAR(20),
    Occupation NVARCHAR(100),
    Veteran BIT DEFAULT 0,
    CONSTRAINT FK_Decedents_Cases FOREIGN KEY (CaseID) REFERENCES Cases(CaseID)
);
GO

-- Core table: Contacts (next of kin, informants, etc.)
CREATE TABLE Contacts (
    ContactID INT IDENTITY(1,1) PRIMARY KEY,
    CaseID INT NOT NULL,
    Relationship NVARCHAR(50) NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Phone NVARCHAR(20),
    Email NVARCHAR(100),
    Address NVARCHAR(200),
    City NVARCHAR(50),
    State NVARCHAR(2),
    ZipCode NVARCHAR(10),
    IsPrimaryContact BIT NOT NULL DEFAULT 0,
    CONSTRAINT FK_Contacts_Cases FOREIGN KEY (CaseID) REFERENCES Cases(CaseID)
);
GO

How do these tables connect?

Both Decedents and Contacts have a CaseID column with a foreign key back to Cases. This enforces referential integrity: you cannot create a decedent or contact record without an existing case to attach it to.

DateOfDeath in the Decedents table is NULL by default. For pre-need cases (funerals planned in advance), the person is still alive, so this field stays empty until the case converts to at-need.

  • Save schema.sql.
Insert seed data and run the schema

Lookup tables need starter data so the application has services, locations, and staff to work with from day one.

  • At the bottom of schema.sql, add the seed data INSERT statements:
INSERT INTO ServiceTypes (ServiceName, Category, Description) VALUES
('Visitation', 'Ceremony', 'Viewing period for family and friends'),
('Funeral Service', 'Ceremony', 'Formal funeral ceremony'),
('Graveside Service', 'Ceremony', 'Service conducted at the burial site'),
('Cremation', 'Disposition', 'Cremation of remains'),
('Burial', 'Disposition', 'Interment of casket or urn'),
('Embalming', 'Preparation', 'Preservation of remains'),
('Transportation', 'Logistics', 'Transfer of remains'),
('Death Certificate', 'Documentation', 'Filing and obtaining death certificates');
GO

INSERT INTO Locations (LocationName, LocationType, Address, City, State, ZipCode) VALUES
('Main Chapel', 'Chapel', '100 Memorial Drive', 'Springfield', 'IL', '62701'),
('Oak Hill Cemetery', 'Cemetery', '250 Oak Hill Road', 'Springfield', 'IL', '62702'),
('City Crematorium', 'Crematory', '500 Industrial Blvd', 'Springfield', 'IL', '62703');
GO

INSERT INTO Staff (FirstName, LastName, Role, Phone, Email) VALUES
('James', 'Morrison', 'Funeral Director', '555-0101', 'jmorrison@funeralhome.com'),
('Sarah', 'Chen', 'Embalmer', '555-0102', 'schen@funeralhome.com'),
('Michael', 'Davis', 'Arrangements Counselor', '555-0103', 'mdavis@funeralhome.com');
GO

What does the seed data provide?

Eight service types cover the standard funeral home offerings, categorized into Ceremony, Disposition, Preparation, Logistics, and Documentation. Three locations represent the physical spaces involved in services. Three staff members fill the core roles you will assign to cases later.

  • Save schema.sql.
  • Switch to Azure Data Studio and right-click your localhost server connection in the Servers sidebar.
  • Select New Query.
  • Copy the entire contents of schema.sql and paste it into the query editor.
  • Click Run (the green play button at the top of the editor) to execute the full script.

What should I see?

You should see multiple "Commands completed successfully" messages in the results pane at the bottom. Each GO batch produces its own success message. The INSERT statements will each report rows affected (8, 3, and 3 respectively).

  • Verify the tables were created by running this query in a new query tab:
-- Verify the schema was created successfully
USE FuneralHomeDB;
GO

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME;
GO

You should see six tables listed: Cases, Contacts, Decedents, Locations, ServiceTypes, and Staff.

Seeing an error about the database already existing?

If you ran the script more than once, the CREATE DATABASE FuneralHomeDB statement will fail because the database already exists. To start fresh, run DROP DATABASE FuneralHomeDB; first, then re-run the full script.

If you see a foreign key error, check that the Staff table creation appears before the Cases table in your file. help me fix a SQL Server schema creation error

✔️ Awesome, I've got everything!

Great. Double check you have saved schema.sql in your funeral-home-db project folder.

ⓧ I'd like to double check the full code

Here is the complete schema.sql file at this point:

-- Funeral Home Case Management Database Schema
-- Run this script in Azure Data Studio or sqlcmd connected to your local SQL Server

CREATE DATABASE FuneralHomeDB;
GO

USE FuneralHomeDB;
GO

-- Lookup table: Staff members
CREATE TABLE Staff (
    StaffID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Role NVARCHAR(50) NOT NULL,
    Phone NVARCHAR(20),
    Email NVARCHAR(100),
    IsActive BIT NOT NULL DEFAULT 1
);
GO

-- Lookup table: Locations (chapels, cemeteries, etc.)
CREATE TABLE Locations (
    LocationID INT IDENTITY(1,1) PRIMARY KEY,
    LocationName NVARCHAR(100) NOT NULL,
    LocationType NVARCHAR(50) NOT NULL,
    Address NVARCHAR(200),
    City NVARCHAR(50),
    State NVARCHAR(2),
    ZipCode NVARCHAR(10),
    Phone NVARCHAR(20)
);
GO

-- Lookup table: Service types
CREATE TABLE ServiceTypes (
    ServiceTypeID INT IDENTITY(1,1) PRIMARY KEY,
    ServiceName NVARCHAR(100) NOT NULL,
    Category NVARCHAR(50) NOT NULL,
    Description NVARCHAR(500)
);
GO

-- Core table: Cases (the central record for each funeral)
CREATE TABLE Cases (
    CaseID INT IDENTITY(1,1) PRIMARY KEY,
    CaseNumber NVARCHAR(20) NOT NULL UNIQUE,
    CaseStatus NVARCHAR(20) NOT NULL DEFAULT 'PreNeed',
    CaseType NVARCHAR(20) NOT NULL,
    CreatedDate DATETIME2 NOT NULL DEFAULT GETDATE(),
    ConvertedDate DATETIME2 NULL,
    AssignedStaffID INT NULL,
    Notes NVARCHAR(MAX),
    CONSTRAINT CHK_CaseStatus CHECK (CaseStatus IN ('PreNeed', 'AtNeed', 'Completed', 'Cancelled')),
    CONSTRAINT CHK_CaseType CHECK (CaseType IN ('Burial', 'Cremation', 'Memorial', 'Other')),
    CONSTRAINT FK_Cases_Staff FOREIGN KEY (AssignedStaffID) REFERENCES Staff(StaffID)
);
GO

-- Core table: Decedents
CREATE TABLE Decedents (
    DecedentID INT IDENTITY(1,1) PRIMARY KEY,
    CaseID INT NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    MiddleName NVARCHAR(50),
    LastName NVARCHAR(50) NOT NULL,
    DateOfBirth DATE NULL,
    DateOfDeath DATE NULL,
    PlaceOfDeath NVARCHAR(200),
    SSN NVARCHAR(11),
    Gender NVARCHAR(20),
    MaritalStatus NVARCHAR(20),
    Occupation NVARCHAR(100),
    Veteran BIT DEFAULT 0,
    CONSTRAINT FK_Decedents_Cases FOREIGN KEY (CaseID) REFERENCES Cases(CaseID)
);
GO

-- Core table: Contacts (next of kin, informants, etc.)
CREATE TABLE Contacts (
    ContactID INT IDENTITY(1,1) PRIMARY KEY,
    CaseID INT NOT NULL,
    Relationship NVARCHAR(50) NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Phone NVARCHAR(20),
    Email NVARCHAR(100),
    Address NVARCHAR(200),
    City NVARCHAR(50),
    State NVARCHAR(2),
    ZipCode NVARCHAR(10),
    IsPrimaryContact BIT NOT NULL DEFAULT 0,
    CONSTRAINT FK_Contacts_Cases FOREIGN KEY (CaseID) REFERENCES Cases(CaseID)
);
GO

INSERT INTO ServiceTypes (ServiceName, Category, Description) VALUES
('Visitation', 'Ceremony', 'Viewing period for family and friends'),
('Funeral Service', 'Ceremony', 'Formal funeral ceremony'),
('Graveside Service', 'Ceremony', 'Service conducted at the burial site'),
('Cremation', 'Disposition', 'Cremation of remains'),
('Burial', 'Disposition', 'Interment of casket or urn'),
('Embalming', 'Preparation', 'Preservation of remains'),
('Transportation', 'Logistics', 'Transfer of remains'),
('Death Certificate', 'Documentation', 'Filing and obtaining death certificates');
GO

INSERT INTO Locations (LocationName, LocationType, Address, City, State, ZipCode) VALUES
('Main Chapel', 'Chapel', '100 Memorial Drive', 'Springfield', 'IL', '62701'),
('Oak Hill Cemetery', 'Cemetery', '250 Oak Hill Road', 'Springfield', 'IL', '62702'),
('City Crematorium', 'Crematory', '500 Industrial Blvd', 'Springfield', 'IL', '62703');
GO

INSERT INTO Staff (FirstName, LastName, Role, Phone, Email) VALUES
('James', 'Morrison', 'Funeral Director', '555-0101', 'jmorrison@funeralhome.com'),
('Sarah', 'Chen', 'Embalmer', '555-0102', 'schen@funeralhome.com'),
('Michael', 'Davis', 'Arrangements Counselor', '555-0103', 'mdavis@funeralhome.com');
GO

Your database schema is in place with six interconnected tables and seed data ready to go. Next up, you will add the workflow tables and a stored procedure that converts pre-need records to at-need status.

Add Pre-Need and At-Need Workflow Tables

Your core tables store the people and places involved in a funeral case. Now you need to capture what actually happens during the case: which services the family selects and what events occur along the way.

In this step, you will create workflow tables that link cases to their chosen services and track timeline events. You will also write a stored procedure that converts a pre-planned case into an active one when the time comes.

In this step, get ready to:
  • Create the ServiceSelections table linking cases to chosen services and preferences.
  • Create the CaseNotes table for tracking timeline events from first call through disposition.
  • Write a stored procedure that transitions a case from pre-need to at-need status.
Create the ServiceSelections table

When a family plans a funeral, they choose specific services like a visitation, a ceremony type, or transportation. The ServiceSelections table records each choice and connects it back to the case, the service type, and optionally a location.

  • In schema.sql, find the Contacts table's GO statement at the bottom of your existing tables.
  • Add the following ServiceSelections table below it:
-- Workflow table: Service selections for a case
CREATE TABLE ServiceSelections (
    SelectionID INT IDENTITY(1,1) PRIMARY KEY,
    CaseID INT NOT NULL,
    ServiceTypeID INT NOT NULL,
    LocationID INT NULL,
    ScheduledDate DATETIME2 NULL,
    Preferences NVARCHAR(500),
    CONSTRAINT FK_Selections_Cases FOREIGN KEY (CaseID) REFERENCES Cases(CaseID),
    CONSTRAINT FK_Selections_ServiceTypes FOREIGN KEY (ServiceTypeID) REFERENCES ServiceTypes(ServiceTypeID),
    CONSTRAINT FK_Selections_Locations FOREIGN KEY (LocationID) REFERENCES Locations(LocationID)
);
GO

What does this table do?

  • CaseID links each selection back to its parent case via a foreign key.
  • ServiceTypeID references the ServiceTypes lookup table so you always pick from a known list of services.
  • LocationID is nullable because not every service needs a specific location at planning time.
  • Preferences stores free-text notes like "white roses on the casket" or "no open casket."
  • Highlight the CREATE TABLE ServiceSelections block in Azure Data Studio and click Run (or press Cmd+Shift+E on macOS).
  • You should see "Commands completed successfully" in the messages panel.

Seeing an 'Invalid object name' error?

Make sure you ran the core tables from the previous step first. The foreign keys reference Cases, ServiceTypes, and Locations which must already exist.

Also confirm your query window is connected to FuneralHomeDB by checking the database dropdown in Azure Data Studio's toolbar.

Help me troubleshoot my ServiceSelections table creation error.

Create the CaseNotes table

A funeral case generates many events over its lifecycle: the first call from a family, arrangement meetings, service scheduling, and status changes. The CaseNotes table captures this timeline so nothing falls through the cracks.

  • In schema.sql, add the following CaseNotes table below the ServiceSelections block you just added:
-- Workflow table: Case notes / timeline events
CREATE TABLE CaseNotes (
    NoteID INT IDENTITY(1,1) PRIMARY KEY,
    CaseID INT NOT NULL,
    NoteDate DATETIME2 NOT NULL DEFAULT GETDATE(),
    NoteType NVARCHAR(50) NOT NULL,
    NoteText NVARCHAR(MAX) NOT NULL,
    CreatedByStaffID INT NULL,
    CONSTRAINT FK_Notes_Cases FOREIGN KEY (CaseID) REFERENCES Cases(CaseID),
    CONSTRAINT FK_Notes_Staff FOREIGN KEY (CreatedByStaffID) REFERENCES Staff(StaffID)
);
GO

What does this table do?

  • NoteDate defaults to the current timestamp so every event is automatically logged with its time.
  • NoteType categorizes entries like "First Call", "Arrangement", "Status Change", or "General".
  • CreatedByStaffID is nullable because some notes are system-generated (like the conversion note you will automate next).
  • Highlight the CREATE TABLE CaseNotes block and click Run.
  • You should see "Commands completed successfully" in the messages panel.

Getting a foreign key error?

Check that the Staff table exists by expanding Tables in the object explorer sidebar. If you only see Cases, Decedents, and Contacts, you need to run the lookup table statements from the previous step first.

Help me fix my CaseNotes table foreign key error.

Write the ConvertPreNeedToAtNeed stored procedure

The core workflow in funeral home software is converting a pre-planned case into an active one at the time of death. A stored procedure bundles multiple updates into a single, reusable database operation that either succeeds completely or rolls back entirely.

Why a stored procedure instead of multiple queries?

Converting a case touches three tables: Cases (status change), Decedents (date of death), and CaseNotes (audit trail). If one update succeeds but another fails, you would have a half-converted case.

Wrapping everything in a transaction inside a stored procedure guarantees all-or-nothing execution. The Python CLI can call it with a single statement instead of managing three separate queries.

  • In schema.sql, add the following stored procedure below the CaseNotes table:
-- Stored Procedure: Convert a Pre-Need case to At-Need
CREATE PROCEDURE ConvertPreNeedToAtNeed
    @CaseID INT,
    @DateOfDeath DATE,
    @PlaceOfDeath NVARCHAR(200) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM Cases WHERE CaseID = @CaseID AND CaseStatus = 'PreNeed')
    BEGIN
        RAISERROR('Case not found or is not in PreNeed status.', 16, 1);
        RETURN;
    END

    BEGIN TRANSACTION;

    UPDATE Cases
    SET CaseStatus = 'AtNeed',
        ConvertedDate = GETDATE()
    WHERE CaseID = @CaseID;

    UPDATE Decedents
    SET DateOfDeath = @DateOfDeath,
        PlaceOfDeath = COALESCE(@PlaceOfDeath, PlaceOfDeath)
    WHERE CaseID = @CaseID;

    INSERT INTO CaseNotes (CaseID, NoteType, NoteText)
    VALUES (@CaseID, 'Status Change', 'Case converted from Pre-Need to At-Need. Date of death: ' + CONVERT(NVARCHAR, @DateOfDeath, 101));

    COMMIT TRANSACTION;
END;
GO

What does this procedure do?

  • The IF NOT EXISTS guard rejects the call if the case is already converted or does not exist.
  • BEGIN TRANSACTION ensures all three operations succeed together or none of them persist.
  • The Cases update flips the status to AtNeed and stamps the conversion date.
  • The Decedents update records the date and place of death. COALESCE preserves any existing place of death if the caller does not provide one.
  • The CaseNotes insert creates an automatic audit trail entry for the status change.
  • Highlight the entire CREATE PROCEDURE block (from CREATE PROCEDURE down to the final GO) and click Run.
  • You should see "Commands completed successfully" in the messages panel.
  • Verify the procedure exists by running this query:
SELECT name FROM sys.procedures WHERE name = 'ConvertPreNeedToAtNeed';

You should see one row returned with the name ConvertPreNeedToAtNeed.

Seeing 'Incorrect syntax near BEGIN'?

Make sure you selected the entire procedure including the GO statement at the end. If you stop the selection before GO, SQL Server tries to parse the procedure definition as a regular batch and fails.

Also check that you did not accidentally include the CaseNotes CREATE TABLE statement in your selection. Only the procedure block should be highlighted.

Help me fix my stored procedure syntax error.

  • Save schema.sql with all the new code included.

✔️ Awesome, I've got everything!

Great. Double check you have saved schema.sql with both new tables and the stored procedure.

ⓧ I'd like to double check the full code

-- Funeral Home Case Management Database Schema
-- Run this script in Azure Data Studio or sqlcmd connected to your local SQL Server

CREATE DATABASE FuneralHomeDB;
GO

USE FuneralHomeDB;
GO

-- Lookup table: Staff members
CREATE TABLE Staff (
    StaffID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Role NVARCHAR(50) NOT NULL,
    Phone NVARCHAR(20),
    Email NVARCHAR(100),
    IsActive BIT NOT NULL DEFAULT 1
);
GO

-- Lookup table: Locations (chapels, cemeteries, etc.)
CREATE TABLE Locations (
    LocationID INT IDENTITY(1,1) PRIMARY KEY,
    LocationName NVARCHAR(100) NOT NULL,
    LocationType NVARCHAR(50) NOT NULL,
    Address NVARCHAR(200),
    City NVARCHAR(50),
    State NVARCHAR(2),
    ZipCode NVARCHAR(10),
    Phone NVARCHAR(20)
);
GO

-- Lookup table: Service types
CREATE TABLE ServiceTypes (
    ServiceTypeID INT IDENTITY(1,1) PRIMARY KEY,
    ServiceName NVARCHAR(100) NOT NULL,
    Category NVARCHAR(50) NOT NULL,
    Description NVARCHAR(500)
);
GO

-- Core table: Cases (the central record for each funeral)
CREATE TABLE Cases (
    CaseID INT IDENTITY(1,1) PRIMARY KEY,
    CaseNumber NVARCHAR(20) NOT NULL UNIQUE,
    CaseStatus NVARCHAR(20) NOT NULL DEFAULT 'PreNeed',
    CaseType NVARCHAR(20) NOT NULL,
    CreatedDate DATETIME2 NOT NULL DEFAULT GETDATE(),
    ConvertedDate DATETIME2 NULL,
    AssignedStaffID INT NULL,
    Notes NVARCHAR(MAX),
    CONSTRAINT CHK_CaseStatus CHECK (CaseStatus IN ('PreNeed', 'AtNeed', 'Completed', 'Cancelled')),
    CONSTRAINT CHK_CaseType CHECK (CaseType IN ('Burial', 'Cremation', 'Memorial', 'Other')),
    CONSTRAINT FK_Cases_Staff FOREIGN KEY (AssignedStaffID) REFERENCES Staff(StaffID)
);
GO

-- Core table: Decedents
CREATE TABLE Decedents (
    DecedentID INT IDENTITY(1,1) PRIMARY KEY,
    CaseID INT NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    MiddleName NVARCHAR(50),
    LastName NVARCHAR(50) NOT NULL,
    DateOfBirth DATE NULL,
    DateOfDeath DATE NULL,
    PlaceOfDeath NVARCHAR(200),
    SSN NVARCHAR(11),
    Gender NVARCHAR(20),
    MaritalStatus NVARCHAR(20),
    Occupation NVARCHAR(100),
    Veteran BIT DEFAULT 0,
    CONSTRAINT FK_Decedents_Cases FOREIGN KEY (CaseID) REFERENCES Cases(CaseID)
);
GO

-- Core table: Contacts (next of kin, informants, etc.)
CREATE TABLE Contacts (
    ContactID INT IDENTITY(1,1) PRIMARY KEY,
    CaseID INT NOT NULL,
    Relationship NVARCHAR(50) NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Phone NVARCHAR(20),
    Email NVARCHAR(100),
    Address NVARCHAR(200),
    City NVARCHAR(50),
    State NVARCHAR(2),
    ZipCode NVARCHAR(10),
    IsPrimaryContact BIT NOT NULL DEFAULT 0,
    CONSTRAINT FK_Contacts_Cases FOREIGN KEY (CaseID) REFERENCES Cases(CaseID)
);
GO

-- Workflow table: Service selections for a case
CREATE TABLE ServiceSelections (
    SelectionID INT IDENTITY(1,1) PRIMARY KEY,
    CaseID INT NOT NULL,
    ServiceTypeID INT NOT NULL,
    LocationID INT NULL,
    ScheduledDate DATETIME2 NULL,
    Preferences NVARCHAR(500),
    CONSTRAINT FK_Selections_Cases FOREIGN KEY (CaseID) REFERENCES Cases(CaseID),
    CONSTRAINT FK_Selections_ServiceTypes FOREIGN KEY (ServiceTypeID) REFERENCES ServiceTypes(ServiceTypeID),
    CONSTRAINT FK_Selections_Locations FOREIGN KEY (LocationID) REFERENCES Locations(LocationID)
);
GO

-- Workflow table: Case notes / timeline events
CREATE TABLE CaseNotes (
    NoteID INT IDENTITY(1,1) PRIMARY KEY,
    CaseID INT NOT NULL,
    NoteDate DATETIME2 NOT NULL DEFAULT GETDATE(),
    NoteType NVARCHAR(50) NOT NULL,
    NoteText NVARCHAR(MAX) NOT NULL,
    CreatedByStaffID INT NULL,
    CONSTRAINT FK_Notes_Cases FOREIGN KEY (CaseID) REFERENCES Cases(CaseID),
    CONSTRAINT FK_Notes_Staff FOREIGN KEY (CreatedByStaffID) REFERENCES Staff(StaffID)
);
GO

-- Stored Procedure: Convert a Pre-Need case to At-Need
CREATE PROCEDURE ConvertPreNeedToAtNeed
    @CaseID INT,
    @DateOfDeath DATE,
    @PlaceOfDeath NVARCHAR(200) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM Cases WHERE CaseID = @CaseID AND CaseStatus = 'PreNeed')
    BEGIN
        RAISERROR('Case not found or is not in PreNeed status.', 16, 1);
        RETURN;
    END

    BEGIN TRANSACTION;

    UPDATE Cases
    SET CaseStatus = 'AtNeed',
        ConvertedDate = GETDATE()
    WHERE CaseID = @CaseID;

    UPDATE Decedents
    SET DateOfDeath = @DateOfDeath,
        PlaceOfDeath = COALESCE(@PlaceOfDeath, PlaceOfDeath)
    WHERE CaseID = @CaseID;

    INSERT INTO CaseNotes (CaseID, NoteType, NoteText)
    VALUES (@CaseID, 'Status Change', 'Case converted from Pre-Need to At-Need. Date of death: ' + CONVERT(NVARCHAR, @DateOfDeath, 101));

    COMMIT TRANSACTION;
END;
GO

INSERT INTO ServiceTypes (ServiceName, Category, Description) VALUES
('Visitation', 'Ceremony', 'Viewing period for family and friends'),
('Funeral Service', 'Ceremony', 'Formal funeral ceremony'),
('Graveside Service', 'Ceremony', 'Service conducted at the burial site'),
('Cremation', 'Disposition', 'Cremation of remains'),
('Burial', 'Disposition', 'Interment of casket or urn'),
('Embalming', 'Preparation', 'Preservation of remains'),
('Transportation', 'Logistics', 'Transfer of remains'),
('Death Certificate', 'Documentation', 'Filing and obtaining death certificates');
GO

INSERT INTO Locations (LocationName, LocationType, Address, City, State, ZipCode) VALUES
('Main Chapel', 'Chapel', '100 Memorial Drive', 'Springfield', 'IL', '62701'),
('Oak Hill Cemetery', 'Cemetery', '250 Oak Hill Road', 'Springfield', 'IL', '62702'),
('City Crematorium', 'Crematory', '500 Industrial Blvd', 'Springfield', 'IL', '62703');
GO

INSERT INTO Staff (FirstName, LastName, Role, Phone, Email) VALUES
('James', 'Morrison', 'Funeral Director', '555-0101', 'jmorrison@funeralhome.com'),
('Sarah', 'Chen', 'Embalmer', '555-0102', 'schen@funeralhome.com'),
('Michael', 'Davis', 'Arrangements Counselor', '555-0103', 'mdavis@funeralhome.com');
GO

Your workflow layer is complete. The database now knows how to track service selections, log timeline events, and convert pre-need records into active cases. Next up, you will build the Python CLI that talks to this database and lets you manage cases from the command line.

Build the Python CLI Data Entry Tool

Your database schema is built and seeded with lookup data. Now you need a way to interact with it without writing raw SQL every time.

In this step, you will create a Python command-line application that connects to your SQL Server container and provides menu-driven functions for creating cases, converting records, and querying data.

In this step, get ready to:
  • Create the database connection module with pyodbc.
  • Build a function to insert a new pre-need case with decedent and contact info.
  • Build functions to query, convert, and annotate cases.
Create the connection module and case number helper

Every function in the CLI needs a database connection. You will create a dedicated get_connection() function that builds the pyodbc connection string once, so the rest of the code stays clean.

  • Create a new file called funeral_cli.py in your project folder by running:
touch funeral_cli.py
  • Open funeral_cli.py in your editor and paste the following code:
"""\nFuneral Home Case Management CLI\nConnects to a local SQL Server instance and provides\ndata entry and case management functions.\n"""\n\nimport pyodbc\nimport sys\nfrom datetime import date\n\n\ndef get_connection():\n    """Connect to the local SQL Server Docker container."""\n    connection_string = (\n        "Driver={ODBC Driver 18 for SQL Server};"\n        "Server=tcp:localhost,1433;"\n        "Database=FuneralHomeDB;"\n        "UID=sa;"\n        "PWD=YourStrong@Passw0rd;"\n        "TrustServerCertificate=yes;"\n    )\n    return pyodbc.connect(connection_string)\n\n\ndef generate_case_number(conn):\n    """Generate the next case number in format YYYY-NNNN."""\n    cursor = conn.cursor()\n    year = date.today().year\n    prefix = f"{year}-"\n    cursor.execute(\n        "SELECT MAX(CaseNumber) FROM Cases WHERE CaseNumber LIKE ?",\n        (prefix + "%",)\n    )\n    row = cursor.fetchone()\n    if row[0] is None:\n        return f"{year}-0001"\n    last_num = int(row[0].split("-")[1])\n    return f"{year}-{last_num + 1:04d}"

What does this code do?

The get_connection() function builds an ODBC connection string targeting your local Docker container on port 1433. It uses the sa account and the password you set when you launched the container.

The TrustServerCertificate=yes parameter skips SSL certificate validation, which is required for local development containers that use self-signed certificates.

The generate_case_number() function queries the highest existing case number for the current year and increments it. If no cases exist yet, it starts at 0001.

  • Save funeral_cli.py.
  • Verify the connection works by running this quick test:
python -c "from funeral_cli import get_connection; conn = get_connection(); print('Connected!'); conn.close()"

You should see Connected! printed to the terminal.

Seeing a connection error?

  • Make sure your SQL Server Docker container is running. Check with docker ps and look for sql2022 in the list.
  • If you see a "Can't open lib" error, your ODBC Driver 18 may not be installed correctly. Re-run the Homebrew install command from Step 2.
  • Make sure your virtual environment is activated. The pyodbc package is installed inside the venv, not globally.

Help me debug my pyodbc connection to SQL Server in Docker

Build the pre-need case creation function

The core workflow of a funeral home starts with a pre-need record. This function walks the user through entering case type, decedent details, and a primary contact, then inserts everything into the database in one transaction.

  • Add the following function below generate_case_number() in funeral_cli.py:
def create_preneed_case(conn):\n    """Walk through creating a new pre-need case."""\n    print("\n--- Create New Pre-Need Case ---")\n    cursor = conn.cursor()\n\n    # Prompt for case type and validate input\n    case_type = input("Case type (Burial/Cremation/Memorial/Other): ").strip()\n    if case_type not in ("Burial", "Cremation", "Memorial", "Other"):\n        print("Invalid case type.")\n        return\n\n    case_number = generate_case_number(conn)\n\n    # Insert the case record\n    cursor.execute(\n        "INSERT INTO Cases (CaseNumber, CaseStatus, CaseType) "\n        "VALUES (?, 'PreNeed', ?)",\n        (case_number, case_type)\n    )\n    cursor.execute("SELECT SCOPE_IDENTITY()")\n    case_id = int(cursor.fetchone()[0])\n\n    # Collect decedent information\n    print("\n-- Decedent Information --")\n    first_name = input("First name: ").strip()\n    last_name = input("Last name: ").strip()\n    dob = input("Date of birth (YYYY-MM-DD or leave blank): ").strip() or None\n\n    cursor.execute(\n        "INSERT INTO Decedents (CaseID, FirstName, LastName, DateOfBirth) "\n        "VALUES (?, ?, ?, ?)",\n        (case_id, first_name, last_name, dob)\n    )\n\n    # Collect primary contact (next of kin)\n    print("\n-- Primary Contact (Next of Kin) --")\n    contact_first = input("Contact first name: ").strip()\n    contact_last = input("Contact last name: ").strip()\n    contact_rel = input("Relationship to decedent: ").strip()\n    contact_phone = input("Phone number: ").strip()\n\n    cursor.execute(\n        "INSERT INTO Contacts (CaseID, Relationship, FirstName, LastName, Phone, IsPrimaryContact) "\n        "VALUES (?, ?, ?, ?, ?, 1)",\n        (case_id, contact_rel, contact_first, contact_last, contact_phone)\n    )\n\n    conn.commit()\n    print(f"\nPre-Need case created successfully! Case Number: {case_number}")

What does this code do?

  • Validates the case type against the four allowed values defined in your CHK_CaseType constraint.
  • Uses parameterized queries (? placeholders) to prevent SQL injection. The values are passed as a tuple, never concatenated into the query string.
  • Retrieves the auto-generated CaseID using SCOPE_IDENTITY() so subsequent inserts can reference the same case.
  • Calls conn.commit() at the end to save all three inserts as a single logical unit.
  • Save funeral_cli.py.

You won't see a change yet. The next substep adds the remaining functions and the main menu that lets you test everything.

Build the query, conversion, and menu functions

With case creation in place, you need functions to look up cases, convert pre-need records to at-need, add timeline notes, and a menu to tie it all together.

  • Add the following functions below create_preneed_case() in funeral_cli.py:
def convert_to_at_need(conn):\n    """Convert a pre-need case to at-need status."""\n    print("\n--- Convert Pre-Need to At-Need ---")\n    cursor = conn.cursor()\n\n    case_number = input("Enter case number to convert: ").strip()\n\n    # Look up the case and verify status\n    cursor.execute(\n        "SELECT CaseID, CaseStatus FROM Cases WHERE CaseNumber = ?",\n        (case_number,)\n    )\n    row = cursor.fetchone()\n    if row is None:\n        print("Case not found.")\n        return\n    if row.CaseStatus != "PreNeed":\n        print(f"Case is already in '{row.CaseStatus}' status. Cannot convert.")\n        return\n\n    case_id = row.CaseID\n    date_of_death = input("Date of death (YYYY-MM-DD): ").strip()\n    place_of_death = input("Place of death (or leave blank): ").strip() or None\n\n    # Call the stored procedure\n    cursor.execute(\n        "EXEC ConvertPreNeedToAtNeed @CaseID=?, @DateOfDeath=?, @PlaceOfDeath=?",\n        (case_id, date_of_death, place_of_death)\n    )\n    conn.commit()\n    print(f"Case {case_number} converted to At-Need successfully.")\n\n\ndef list_cases(conn):\n    """List all cases with their current status."""\n    print("\n--- All Cases ---")\n    cursor = conn.cursor()\n    cursor.execute(\n        "SELECT c.CaseNumber, c.CaseStatus, c.CaseType, c.CreatedDate, "\n        "d.FirstName, d.LastName "\n        "FROM Cases c "\n        "JOIN Decedents d ON c.CaseID = d.CaseID "\n        "ORDER BY c.CreatedDate DESC"\n    )\n    rows = cursor.fetchall()\n    if not rows:\n        print("No cases found.")\n        return\n\n    print(f"{'Case #':<12} {'Status':<12} {'Type':<12} {'Decedent':<25} {'Created':<12}")\n    print("-" * 73)\n    for row in rows:\n        decedent_name = f"{row.FirstName} {row.LastName}"\n        created = row.CreatedDate.strftime("%Y-%m-%d")\n        print(f"{row.CaseNumber:<12} {row.CaseStatus:<12} {row.CaseType:<12} {decedent_name:<25} {created:<12}")

What's happening here?

  • The convert_to_at_need() function calls your ConvertPreNeedToAtNeed stored procedure. The procedure handles updating the case status, recording the date of death, and adding a case note in one transaction.
  • The list_cases() function joins Cases and Decedents to show a formatted table. Python f-string formatting (:<12) creates aligned columns.
  • Now add the detail view, case notes, and main menu below list_cases():
def view_case_detail(conn):\n    """View full details of a specific case."""\n    print("\n--- Case Detail ---")\n    cursor = conn.cursor()\n\n    case_number = input("Enter case number: ").strip()\n\n    # Fetch case header\n    cursor.execute(\n        "SELECT c.CaseID, c.CaseNumber, c.CaseStatus, c.CaseType, "\n        "c.CreatedDate, c.ConvertedDate "\n        "FROM Cases c WHERE c.CaseNumber = ?",\n        (case_number,)\n    )\n    case = cursor.fetchone()\n    if case is None:\n        print("Case not found.")\n        return\n\n    print(f"\nCase: {case.CaseNumber} | Status: {case.CaseStatus} | Type: {case.CaseType}")\n    print(f"Created: {case.CreatedDate.strftime('%Y-%m-%d')}", end="")\n    if case.ConvertedDate:\n        print(f" | Converted: {case.ConvertedDate.strftime('%Y-%m-%d')}")\n    else:\n        print()\n\n    # Fetch decedent info\n    cursor.execute(\n        "SELECT FirstName, MiddleName, LastName, DateOfBirth, DateOfDeath "\n        "FROM Decedents WHERE CaseID = ?",\n        (case.CaseID,)\n    )\n    dec = cursor.fetchone()\n    if dec:\n        name = f"{dec.FirstName} {dec.MiddleName or ''} {dec.LastName}".replace("  ", " ")\n        print(f"\nDecedent: {name}")\n        if dec.DateOfBirth:\n            print(f"  Born: {dec.DateOfBirth}")\n        if dec.DateOfDeath:\n            print(f"  Died: {dec.DateOfDeath}")\n\n    # Fetch contacts\n    cursor.execute(\n        "SELECT FirstName, LastName, Relationship, Phone, IsPrimaryContact "\n        "FROM Contacts WHERE CaseID = ? ORDER BY IsPrimaryContact DESC",\n        (case.CaseID,)\n    )\n    contacts = cursor.fetchall()\n    if contacts:\n        print("\nContacts:")\n        for c in contacts:\n            primary = " (PRIMARY)" if c.IsPrimaryContact else ""\n            print(f"  {c.FirstName} {c.LastName} - {c.Relationship}{primary} | {c.Phone or 'N/A'}")\n\n    # Fetch case notes\n    cursor.execute(\n        "SELECT NoteDate, NoteType, NoteText "\n        "FROM CaseNotes WHERE CaseID = ? ORDER BY NoteDate",\n        (case.CaseID,)\n    )\n    notes = cursor.fetchall()\n    if notes:\n        print("\nCase Notes:")\n        for n in notes:\n            print(f"  [{n.NoteDate.strftime('%Y-%m-%d %H:%M')}] {n.NoteType}: {n.NoteText}")\n\n\ndef add_case_note(conn):\n    """Add a note to an existing case."""\n    print("\n--- Add Case Note ---")\n    cursor = conn.cursor()\n\n    case_number = input("Enter case number: ").strip()\n    cursor.execute("SELECT CaseID FROM Cases WHERE CaseNumber = ?", (case_number,))\n    row = cursor.fetchone()\n    if row is None:\n        print("Case not found.")\n        return\n\n    note_type = input("Note type (First Call/Arrangement/Service/Status Change/General): ").strip()\n    note_text = input("Note text: ").strip()\n\n    cursor.execute(\n        "INSERT INTO CaseNotes (CaseID, NoteType, NoteText) VALUES (?, ?, ?)",\n        (row.CaseID, note_type, note_text)\n    )\n    conn.commit()\n    print("Note added successfully.")\n\n\ndef main():\n    """Main menu loop."""\n    try:\n        conn = get_connection()\n    except pyodbc.Error as e:\n        print(f"Failed to connect to database: {e}")\n        sys.exit(1)\n\n    print("=" * 50)\n    print("  Funeral Home Case Management System")\n    print("=" * 50)\n\n    while True:\n        print("\n[1] Create Pre-Need Case")\n        print("[2] Convert Pre-Need to At-Need")\n        print("[3] List All Cases")\n        print("[4] View Case Detail")\n        print("[5] Add Case Note")\n        print("[6] Exit")\n\n        choice = input("\nSelect an option: ").strip()\n\n        if choice == "1":\n            create_preneed_case(conn)\n        elif choice == "2":\n            convert_to_at_need(conn)\n        elif choice == "3":\n            list_cases(conn)\n        elif choice == "4":\n            view_case_detail(conn)\n        elif choice == "5":\n            add_case_note(conn)\n        elif choice == "6":\n            print("Goodbye.")\n            conn.close()\n            break\n        else:\n            print("Invalid option. Please try again.")\n\n\nif __name__ == "__main__":\n    main()

What does this code do?

  • The view_case_detail() function pulls everything related to a case (header, decedent, contacts, notes) across four separate queries and formats it into a readable summary.
  • The add_case_note() function lets you append timeline events to any case. This is how funeral directors track progress from first call through service completion.
  • The main() function opens one connection at startup and reuses it for all menu actions. The if __name__ == "__main__" guard ensures the menu only runs when you execute the file directly.
  • Save funeral_cli.py.
  • Run the CLI by executing:
python funeral_cli.py

You should see the banner and a numbered menu with six options.

Script crashes on launch?

  • If you see IndentationError or SyntaxError, check that your code uses consistent indentation (4 spaces, no tabs).
  • If you see ModuleNotFoundError: No module named 'pyodbc', make sure your virtual environment is activated before running the script.
  • If the connection fails, confirm your Docker container is still running with docker ps.

Help me fix an error when running my funeral_cli.py Python script

✔️ Awesome, I've got everything!

Great. Double check you have saved funeral_cli.py and that the menu appears when you run the script.

ⓧ I'd like to double check the full code

"""\nFuneral Home Case Management CLI\nConnects to a local SQL Server instance and provides\ndata entry and case management functions.\n"""\n\nimport pyodbc\nimport sys\nfrom datetime import date\n\n\ndef get_connection():\n    """Connect to the local SQL Server Docker container."""\n    connection_string = (\n        "Driver={ODBC Driver 18 for SQL Server};"\n        "Server=tcp:localhost,1433;"\n        "Database=FuneralHomeDB;"\n        "UID=sa;"\n        "PWD=YourStrong@Passw0rd;"\n        "TrustServerCertificate=yes;"\n    )\n    return pyodbc.connect(connection_string)\n\n\ndef generate_case_number(conn):\n    """Generate the next case number in format YYYY-NNNN."""\n    cursor = conn.cursor()\n    year = date.today().year\n    prefix = f"{year}-"\n    cursor.execute(\n        "SELECT MAX(CaseNumber) FROM Cases WHERE CaseNumber LIKE ?",\n        (prefix + "%",)\n    )\n    row = cursor.fetchone()\n    if row[0] is None:\n        return f"{year}-0001"\n    last_num = int(row[0].split("-")[1])\n    return f"{year}-{last_num + 1:04d}"\n\n\ndef create_preneed_case(conn):\n    """Walk through creating a new pre-need case."""\n    print("\n--- Create New Pre-Need Case ---")\n    cursor = conn.cursor()\n\n    case_type = input("Case type (Burial/Cremation/Memorial/Other): ").strip()\n    if case_type not in ("Burial", "Cremation", "Memorial", "Other"):\n        print("Invalid case type.")\n        return\n\n    case_number = generate_case_number(conn)\n\n    cursor.execute(\n        "INSERT INTO Cases (CaseNumber, CaseStatus, CaseType) "\n        "VALUES (?, 'PreNeed', ?)",\n        (case_number, case_type)\n    )\n    cursor.execute("SELECT SCOPE_IDENTITY()")\n    case_id = int(cursor.fetchone()[0])\n\n    print("\n-- Decedent Information --")\n    first_name = input("First name: ").strip()\n    last_name = input("Last name: ").strip()\n    dob = input("Date of birth (YYYY-MM-DD or leave blank): ").strip() or None\n\n    cursor.execute(\n        "INSERT INTO Decedents (CaseID, FirstName, LastName, DateOfBirth) "\n        "VALUES (?, ?, ?, ?)",\n        (case_id, first_name, last_name, dob)\n    )\n\n    print("\n-- Primary Contact (Next of Kin) --")\n    contact_first = input("Contact first name: ").strip()\n    contact_last = input("Contact last name: ").strip()\n    contact_rel = input("Relationship to decedent: ").strip()\n    contact_phone = input("Phone number: ").strip()\n\n    cursor.execute(\n        "INSERT INTO Contacts (CaseID, Relationship, FirstName, LastName, Phone, IsPrimaryContact) "\n        "VALUES (?, ?, ?, ?, ?, 1)",\n        (case_id, contact_rel, contact_first, contact_last, contact_phone)\n    )\n\n    conn.commit()\n    print(f"\nPre-Need case created successfully! Case Number: {case_number}")\n\n\ndef convert_to_at_need(conn):\n    """Convert a pre-need case to at-need status."""\n    print("\n--- Convert Pre-Need to At-Need ---")\n    cursor = conn.cursor()\n\n    case_number = input("Enter case number to convert: ").strip()\n\n    cursor.execute(\n        "SELECT CaseID, CaseStatus FROM Cases WHERE CaseNumber = ?",\n        (case_number,)\n    )\n    row = cursor.fetchone()\n    if row is None:\n        print("Case not found.")\n        return\n    if row.CaseStatus != "PreNeed":\n        print(f"Case is already in '{row.CaseStatus}' status. Cannot convert.")\n        return\n\n    case_id = row.CaseID\n    date_of_death = input("Date of death (YYYY-MM-DD): ").strip()\n    place_of_death = input("Place of death (or leave blank): ").strip() or None\n\n    cursor.execute(\n        "EXEC ConvertPreNeedToAtNeed @CaseID=?, @DateOfDeath=?, @PlaceOfDeath=?",\n        (case_id, date_of_death, place_of_death)\n    )\n    conn.commit()\n    print(f"Case {case_number} converted to At-Need successfully.")\n\n\ndef list_cases(conn):\n    """List all cases with their current status."""\n    print("\n--- All Cases ---")\n    cursor = conn.cursor()\n    cursor.execute(\n        "SELECT c.CaseNumber, c.CaseStatus, c.CaseType, c.CreatedDate, "\n        "d.FirstName, d.LastName "\n        "FROM Cases c "\n        "JOIN Decedents d ON c.CaseID = d.CaseID "\n        "ORDER BY c.CreatedDate DESC"\n    )\n    rows = cursor.fetchall()\n    if not rows:\n        print("No cases found.")\n        return\n\n    print(f"{'Case #':<12} {'Status':<12} {'Type':<12} {'Decedent':<25} {'Created':<12}")\n    print("-" * 73)\n    for row in rows:\n        decedent_name = f"{row.FirstName} {row.LastName}"\n        created = row.CreatedDate.strftime("%Y-%m-%d")\n        print(f"{row.CaseNumber:<12} {row.CaseStatus:<12} {row.CaseType:<12} {decedent_name:<25} {created:<12}")\n\n\ndef view_case_detail(conn):\n    """View full details of a specific case."""\n    print("\n--- Case Detail ---")\n    cursor = conn.cursor()\n\n    case_number = input("Enter case number: ").strip()\n\n    cursor.execute(\n        "SELECT c.CaseID, c.CaseNumber, c.CaseStatus, c.CaseType, "\n        "c.CreatedDate, c.ConvertedDate "\n        "FROM Cases c WHERE c.CaseNumber = ?",\n        (case_number,)\n    )\n    case = cursor.fetchone()\n    if case is None:\n        print("Case not found.")\n        return\n\n    print(f"\nCase: {case.CaseNumber} | Status: {case.CaseStatus} | Type: {case.CaseType}")\n    print(f"Created: {case.CreatedDate.strftime('%Y-%m-%d')}", end="")\n    if case.ConvertedDate:\n        print(f" | Converted: {case.ConvertedDate.strftime('%Y-%m-%d')}")\n    else:\n        print()\n\n    cursor.execute(\n        "SELECT FirstName, MiddleName, LastName, DateOfBirth, DateOfDeath "\n        "FROM Decedents WHERE CaseID = ?",\n        (case.CaseID,)\n    )\n    dec = cursor.fetchone()\n    if dec:\n        name = f"{dec.FirstName} {dec.MiddleName or ''} {dec.LastName}".replace("  ", " ")\n        print(f"\nDecedent: {name}")\n        if dec.DateOfBirth:\n            print(f"  Born: {dec.DateOfBirth}")\n        if dec.DateOfDeath:\n            print(f"  Died: {dec.DateOfDeath}")\n\n    cursor.execute(\n        "SELECT FirstName, LastName, Relationship, Phone, IsPrimaryContact "\n        "FROM Contacts WHERE CaseID = ? ORDER BY IsPrimaryContact DESC",\n        (case.CaseID,)\n    )\n    contacts = cursor.fetchall()\n    if contacts:\n        print("\nContacts:")\n        for c in contacts:\n            primary = " (PRIMARY)" if c.IsPrimaryContact else ""\n            print(f"  {c.FirstName} {c.LastName} - {c.Relationship}{primary} | {c.Phone or 'N/A'}")\n\n    cursor.execute(\n        "SELECT NoteDate, NoteType, NoteText "\n        "FROM CaseNotes WHERE CaseID = ? ORDER BY NoteDate",\n        (case.CaseID,)\n    )\n    notes = cursor.fetchall()\n    if notes:\n        print("\nCase Notes:")\n        for n in notes:\n            print(f"  [{n.NoteDate.strftime('%Y-%m-%d %H:%M')}] {n.NoteType}: {n.NoteText}")\n\n\ndef add_case_note(conn):\n    """Add a note to an existing case."""\n    print("\n--- Add Case Note ---")\n    cursor = conn.cursor()\n\n    case_number = input("Enter case number: ").strip()\n    cursor.execute("SELECT CaseID FROM Cases WHERE CaseNumber = ?", (case_number,))\n    row = cursor.fetchone()\n    if row is None:\n        print("Case not found.")\n        return\n\n    note_type = input("Note type (First Call/Arrangement/Service/Status Change/General): ").strip()\n    note_text = input("Note text: ").strip()\n\n    cursor.execute(\n        "INSERT INTO CaseNotes (CaseID, NoteType, NoteText) VALUES (?, ?, ?)",\n        (row.CaseID, note_type, note_text)\n    )\n    conn.commit()\n    print("Note added successfully.")\n\n\ndef main():\n    """Main menu loop."""\n    try:\n        conn = get_connection()\n    except pyodbc.Error as e:\n        print(f"Failed to connect to database: {e}")\n        sys.exit(1)\n\n    print("=" * 50)\n    print("  Funeral Home Case Management System")\n    print("=" * 50)\n\n    while True:\n        print("\n[1] Create Pre-Need Case")\n        print("[2] Convert Pre-Need to At-Need")\n        print("[3] List All Cases")\n        print("[4] View Case Detail")\n        print("[5] Add Case Note")\n        print("[6] Exit")\n\n        choice = input("\nSelect an option: ").strip()\n\n        if choice == "1":\n            create_preneed_case(conn)\n        elif choice == "2":\n            convert_to_at_need(conn)\n        elif choice == "3":\n            list_cases(conn)\n        elif choice == "4":\n            view_case_detail(conn)\n        elif choice == "5":\n            add_case_note(conn)\n        elif choice == "6":\n            print("Goodbye.")\n            conn.close()\n            break\n        else:\n            print("Invalid option. Please try again.")\n\n\nif __name__ == "__main__":\n    main()

Your CLI is ready to accept data. Next up, you will run a complete end-to-end test by entering a pre-need case and converting it to at-need.

Test the Full Workflow

Your database schema is deployed and your Python CLI is ready to go. Now it is time to prove that everything works together by running through the entire case lifecycle.

In this step, you will enter a pre-need record, add a service selection, convert the record to at-need, and query the full case history. This exercises every table, every foreign key relationship, and the stored procedure you built.

In this step, get ready to:
  • Enter a complete pre-need record through the CLI with decedent details, next-of-kin, and service selections.
  • Convert that pre-need record to at-need and verify the status change, date of death capture, and data preservation.
  • Query and display the complete case history including all linked records.
Enter a complete pre-need record

A pre-need record captures all arrangements a family makes in advance. Your CLI walks through each piece of information: case type, decedent details, and primary contact.

  • Launch the CLI by running this command in your terminal (make sure your virtual environment is active):
python funeral_cli.py

You should see the main menu with six numbered options.

  • Type 1 and press Enter to select Create Pre-Need Case.
  • Enter Burial for the case type.
  • Enter Margaret for the decedent first name.
  • Enter Thompson for the decedent last name.
  • Enter 1942-03-15 for the date of birth.
  • Enter Robert for the contact first name.
  • Enter Thompson for the contact last name.
  • Enter Son for the relationship.
  • Enter 555-0150 for the phone number.

You should see a confirmation message like Pre-Need case created successfully! Case Number: 2026-0001. Note down the case number for the next substeps.

What happened in the database?

That single menu option inserted rows into three tables: Cases (with status PreNeed), Decedents (linked via CaseID), and Contacts (with IsPrimaryContact=1).

The foreign keys you defined in schema.sql ensure that every decedent and contact row points to a valid case. If you tried to insert a decedent with a non-existent CaseID, SQL Server would reject it.

Now add a service selection to make this a truly complete pre-need record. The CLI does not include service selection in the base version, so you will add one directly via SQL.

  • Switch back to Azure Data Studio (or your MSSQL editor from earlier).
  • Open a new query window connected to FuneralHomeDB.
  • Insert a service selection for the case you just created by running this query:
-- Link the case to a Visitation service at the Main Chapel
INSERT INTO ServiceSelections (CaseID, ServiceTypeID, LocationID, Preferences)
VALUES (1, 1, 1, 'Family prefers evening hours for visitation');

What does this query do?

  • CaseID = 1 links to the case you just created.
  • ServiceTypeID = 1 references the "Visitation" entry in the ServiceTypes seed data.
  • LocationID = 1 references "Main Chapel" in the Locations seed data.
  • The Preferences column stores free-text notes about the family's wishes.
  • Verify the insert succeeded by running:
SELECT s.SelectionID, s.CaseID, st.ServiceName, l.LocationName, s.Preferences
FROM ServiceSelections s
JOIN ServiceTypes st ON s.ServiceTypeID = st.ServiceTypeID
JOIN Locations l ON s.LocationID = l.LocationID
WHERE s.CaseID = 1;

You should see one row showing Visitation at Main Chapel with the preferences you entered.

Seeing a foreign key violation error?

If the INSERT fails with a foreign key constraint error, the CaseID might not be 1. Run SELECT CaseID, CaseNumber FROM Cases to find your case's actual ID, then update the INSERT statement to use that value.

Help me fix a foreign key violation on my ServiceSelections insert.

Convert the pre-need record to at-need

When a pre-planned arrangement becomes active (at the time of death), the case status must transition from PreNeed to AtNeed. Your stored procedure handles this in a single transaction.

  • Return to your terminal where the CLI is still running.
  • Type 2 and press Enter to select Convert Pre-Need to At-Need.
  • Enter the case number from the previous substep (e.g. 2026-0001).
  • Enter 2026-05-28 for the date of death.
  • Enter Springfield Memorial Hospital for the place of death.

You should see the confirmation: Case 2026-0001 converted to At-Need successfully.

What did the stored procedure do?

The ConvertPreNeedToAtNeed stored procedure executed three operations inside a single transaction:

  • Updated Cases.CaseStatus to AtNeed and set ConvertedDate to the current timestamp.
  • Updated the Decedents row with DateOfDeath and PlaceOfDeath.
  • Inserted a CaseNotes row recording the status change with the date of death.

If any of those operations failed, the transaction would roll back and no data would be changed. This guarantees data consistency.

  • Verify the status change by typing 3 to select List All Cases.

You should see your case listed with a status of AtNeed instead of PreNeed.

Seeing "Case not found" or "Cannot convert"?

Double-check you typed the case number exactly as it was printed when you created it. Case numbers use the format YYYY-NNNN (e.g. 2026-0001). If the case already shows AtNeed status, you may have already converted it.

Help me troubleshoot my pre-need to at-need conversion.

Query and display the complete case history

The true test of a relational database is whether linked records come together into a coherent story. The case detail view queries across multiple tables to reconstruct the full picture.

  • In the CLI menu, type 4 and press Enter to select View Case Detail.
  • Enter your case number (e.g. 2026-0001).

You should see a detailed output with all of the following sections:

  • Case header showing status AtNeed, type Burial, and a Converted date.
  • Decedent information with both date of birth and date of death filled in.
  • Contacts section showing Robert Thompson as the primary contact.
  • Case Notes section with a "Status Change" entry that the stored procedure created automatically.

Why is this output important?

The view_case_detail function runs four separate queries (Cases, Decedents, Contacts, CaseNotes) and assembles the results into one view. This proves your foreign key relationships are working correctly.

The "Status Change" note was never entered manually. The ConvertPreNeedToAtNeed stored procedure inserted it as part of the conversion transaction. This is how real database systems maintain audit trails automatically.

  • Add a manual case note to complete the lifecycle. Type 5 to select Add Case Note.
  • Enter your case number.
  • Enter Arrangement for the note type.
  • Enter Family confirmed visitation for Thursday evening at Main Chapel for the note text.

You should see "Note added successfully." printed to the terminal.

  • Run View Case Detail one more time (option 4) to confirm the new note appears alongside the stored procedure's automatic note.
  • Type 6 to exit the CLI when you are finished.

Case Notes section is empty?

If the case detail shows no notes, the conversion may not have succeeded. Run option 3 (List All Cases) and check the status column. If it still reads PreNeed, the stored procedure did not execute. Re-run option 2 and ensure you enter the date in YYYY-MM-DD format.

Help me figure out why my case notes are not showing up.

You have proven the full lifecycle works end to end. A case enters the system as a pre-need plan, gains linked records for the decedent, contacts, and services, then converts to at-need status with full data preservation. Next up, you can extend this system with a services catalog and automated cost estimation in the Secret Mission.

Secret mission

Add a Services Catalog with Cost Estimation

Your database tracks cases from first call through disposition, but families also need to know what everything costs. In this mission, you will add a full services catalog with pricing and generate an itemized cost estimate automatically when converting a pre-need record to at-need.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. The SQL Server Docker container uses memory and CPU while running, but there are no ongoing financial costs since everything runs locally.

Resources you used:

  • SQL Server 2022 Docker container (sql2022).
  • Docker volume for persistent database storage (sqldata).
  • Project files (schema.sql, funeral_cli.py, schema_secret_mission.sql, funeral_cli_secret.py).
  • Python virtual environment inside the project folder.
  • ODBC Driver 18 for SQL Server (installed via Homebrew).
  • Docker Desktop application.

Keep everything running

No action needed. Choose this if you are still actively building on this project or plan to add more features soon.

  • Your sql2022 container stays running and accessible on port 1433.
  • Your project files, virtual environment, ODBC Driver 18, and Docker Desktop remain on your machine.
  • You can reconnect anytime by running python funeral_cli.py from your project folder.

Pause - I'll come back to this later

Shut down the Docker container to free up memory and CPU. Your database data is preserved in the Docker volume and will be there when you restart.

  • Stop the SQL Server container by running:
docker stop sql2022
  • When you are ready to resume, start it again by running:
docker start sql2022
  • Your project files and virtual environment remain untouched.
  • You can also quit Docker Desktop from the menu bar to save additional system resources.

Delete - I don't want to use this again

Remove all project resources and start fresh. This is irreversible for any data stored in the database.

  • Stop and remove the Docker container by running:
docker stop sql2022
docker rm sql2022
  • Remove the Docker volume that holds your database data by running:
docker volume rm sqldata
  • Remove the SQL Server Docker image to reclaim disk space by running:
docker rmi mcr.microsoft.com/mssql/server:2022-latest
  • Delete your project folder from the Desktop. Navigate to your Desktop and remove the folder containing your project files by running:
cd ~/Desktop
rm -rf funeral-home-db

Which folder to delete?

The folder name should match whatever you named your project directory when you created it. Look for the folder on your Desktop that contains schema.sql and funeral_cli.py.

  • Uninstall the ODBC Driver 18 and SQL Server tools via Homebrew by running:
brew uninstall msodbcsql18 mssql-tools18
  • Uninstall Docker Desktop by opening the application, clicking the Troubleshoot icon (bug icon) in the top-right corner of the Dashboard, and selecting Uninstall.
  • Confirm the uninstall when prompted.

Nice Work!

Nice Work!

You just built a professional funeral home case management database from the ground up.

You've learned how to:

  • Designed and implemented a normalized SQL Server schema with 8+ interconnected tables, foreign keys, and referential integrity constraints.
  • Built a Python CLI tool that connects to SQL Server via ODBC and performs full CRUD operations including case creation, querying, and status updates.
  • Implemented a pre-need to at-need conversion workflow using a T-SQL stored procedure that transitions case status, records the date of death, and preserves all original planning data.
  • Secret Mission: Created a services catalog with pricing and built automated itemized cost estimation during the pre-need to at-need conversion.

Ready to quiz yourself?