Zoo Management System

Final Project - Databases course.
Developed by Gonçalo Silva and Gonçalo Simões.

Overview

This project is a Zoo Management System designed to help manage information about animals, habitats, employees, visitors, and veterinary records.
The application was developed in C# (Windows Forms) and uses a Microsoft SQL Server relational database, consisting of 44 tables, 57 stored procedures, 11 user-defined functions, 3 triggers, and 1 index.
It includes 6 interactive forms that provide staff with user-friendly interfaces.
The main goal is to provide zoo staff — such as zookeepers, veterinarians, and administrators — with a centralized tool to manage daily operations and data efficiently.

Key Features

Employee Management

• Register, update, and remove employees
• Support for multiple roles (veterinarian, zookeeper, maintenance, customer service)
• Search with filtering and pagination
• Rehire previously deleted employees
• Automatic statistics (total employees, average and total salary) via UDFs

Visitor and Ticket Management

• Ticket registration and sales
• Filtering by name, tax ID (NIF), category, and date
• Export tickets to CSV
• Real-time validation (name, NIF, date, price, etc.)

Areas and Habitats

• Manage general areas and animal habitats
• Add, update, and delete areas
• Automatic calculation of occupancy rates
• Triggers prevent deletion of habitats containing animals

Feeding Schedules

• Manage feeding times and assign zookeepers to each
• Add, remove, and update schedules through stored procedures
• Synchronized views between zookeepers and feeding schedules

Animal Management

• Register and update animal data (species, diet, classification, conservation status)
• Link animals to their habitats and feeding schedules
• Filtering and pagination for quick data access

Veterinary Records and Login System

• Secure login system for veterinarians (hashed passwords + salt)
• Manage veterinary records, diagnoses, and treatments
• Create, modify, and delete veterinary accounts

Database Structure

• 44 tables (29 used in the final version of the application)
• 57 stored procedures
• 11 user-defined functions (UDFs)
• 3 triggers
• 1 index

Below are the main diagrams illustrating the database structure:

Entity-Relationship Diagram (ERD)

Relational Schema

The project demonstrates some database design concepts such as normalization, entity specialization, and referential integrity.

Project Structure

final-project/
├── ddl/                              # DDL scripts defining the database schema
│   └── ddl.sql
|
├── demo/                             # Demo video
|   └── demo.mp4
│
├── diagramas/                        # Database and system diagrams
│   ├── der.png                       # Entity-relationship diagram
│   └── esquema_relacional.png        # Relational schema diagram
│
├── documents/                        # Project documentation
│   ├── apresentacao.pdf              # Presentation slides
│   ├── proposta.pdf                  # Initial project proposal
│   └── relatorio.pdf                 # Final project report
│
├── ProjetoBD/                        # Main C# Windows Forms application
│   └── ProjetoBD/
│       ├── DAL/                      # Data Access Layer (database interactions)
│       ├── Forms/                    # Application forms (UI)
│       ├── Models/                   # Data models and entities
│       ├── Services/                 # Business logic and helper services
│       ├── Utils/                    # Utility classes (e.g., password hashing)
│       └── Program.cs                # Entry point of the application
│
└── scripts/                          # SQL scripts for database creation and management
    ├── ddl.sql                       # Schema creation (tables, relationships)
    ├── everything.sql                # Master script executing all others
    ├── indexes.sql                   # Index definitions
    ├── inserts.sql                   # Example data inserts
    ├── stored_procedures.sql         # Stored procedures (SPs)
    ├── triggers.sql                  # Triggers
    ├── udfs.sql                      # User-defined functions
    └── views.sql                     # View definitions

Documentation

The repository includes all project documentation:

• Initial Proposal (proposta.pdf) – outlines the project goals, requirements, and planned design.
• Presentation Slides (apresentacao.pdf) – used for the course presentation, including an overview of features and architecture.
• Final Report (relatorio.pdf) – detailed report of the final implementation, including screenshots, database diagrams, and explanations of key features.

These documents provide context and complement the demo video, giving a complete view of the project's development process.

User Interface and Demo

Screenshots

Screenshots of the main application forms and interactions can be found in the Final Report.

Demo Video

A short video (45 MB) demonstrating the application's main interactions is included in the demo folder.

The demo video does not show pagination or the secure login system, as these features were added after the presentation. All features are fully implemented and visible in the screenshots included in the Final Report. The video was part of the presentation.

How to Run

1. Clone the repository.

git clone [email protected]:goncalooliveirasilva/db-course-project.git

2. Restore the SQL Server database using the provided scripts. You can use the all-in-one script.
3. Open the project in Visual Studio.
4. Update the database connection string in this file in the method GetConnection().
5. Run the application.

Project Outcome

This project was developed as part of the Databases course 2025 at the University of Aveiro.
Final grade: 20/20.