University Resource Reservation System

Main Window (and also Login)

Project Overview

This desktop application, developed in Python using Tkinter, serves as a comprehensive resource reservation system for a university or similar institution. It enables users to browse available resources, make reservations, and allows administrators to manage resources, users, and reservation statuses. This project was developed as a final project for the "Databases" course, showcasing practical skills in SQL Server, database design, and GUI application development.

Key Features

• Resource Browse: Users can view a list of available resources.
• Resource Reservation: Ability to book a resource for a specific date and time period.
• User Management: (Admin-only) Add, modify, delete users, and manage their priority levels.
• Resource Management: (Admin-only) Add, modify, delete resources.
• Reservation/Requisition Management: (Admin-only) Approve, decline, or cancel reservations.
• Priority System: Different access and permission levels for users (e.g., students, professors, administrators).
• Penalty System: Automated penalty assignment and priority adjustments based on reservation adherence (managed by SQL triggers).
• Intuitive GUI: A user-friendly graphical interface built with Tkinter for easy interaction.

Technologies Used

• Python 3.12
• Tkinter: For building the graphical user interface.
• pyodbc: To connect to SQL Server.
• SQL Server: Relational Database Management System (RDBMS).
• tkcalendar: For date selection in the GUI.
• datetime: For handling dates and times.

Installation and Setup

Prerequisites

1. Python 3.12: Ensure Python is installed on your system. Download Python
2. SQL Server: Install SQL Server (e.g., SQL Server Express) and SQL Server Management Studio (SSMS). Download SQL Server Express Download SSMS
3. ODBC Driver 17 for SQL Server: This driver is crucial for Python to connect to SQL Server. Download ODBC Driver

Database Setup

1. Create Database: Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance. Execute the following SQL query to create a new database:

   CREATE DATABASE UniversityReservationSystem;

   Or, if you prefer, you can create it via the SSMS GUI: Right-click "Databases" -> "New Database..." and name it UniversityReservationSystem.

2. Execute SQL Scripts: Navigate to the db_scripts folder in this repository. The following scripts must be executed in SSMS, in the specified order, to set up the database schema and populate it with initial data:

   • TableCreation.sql: Creates all necessary tables and defines their relationships.
   • InfoInsertion.sql: Inserts initial data into the Priorities, States, Users, Resources, and some Reservations tables.
   • trg_CreateRequisition.sql: Creates a trigger trg_CreateRequisition which automatically inserts a new record into Requisitions table when a reservation's status changes to 'Satisfied'. It also defines a seq_RequisitionID sequence for unique requisition IDs.
   • trg_IncreasePriority.sql: Creates a trigger trg_IncreasePriority which increases a user's CurrentPriority if a requisition status for their reservation is updated to 'Closed' and the resource was returned on time.
   • trg_HandlePenalties.sql: Creates a trigger trg_HandlePenalties which automatically assigns penalties and potentially lowers a user's CurrentPriority based on reservation violations (e.g., forgotten reservations, not picked up, not returned on time).

   Execution Steps in SSMS:

   • Open each .sql file in SSMS.
   • Ensure you are connected to the UniversityReservationSystem database. You can do this by selecting UniversityReservationSystem from the dropdown menu in the toolbar, or by adding USE UniversityReservationSystem; at the top of each script (which is already present in TableCreation.sql and InfoInsertion.sql).
   • Execute the scripts in the order listed above.

3. Configure Database Connection: Open DatabaseConnector.py. The application is configured for Windows Authentication (Trusted_Connection=yes). Ensure that server_name and database_name match your SQL Server setup.

   # DatabaseConnector.py
   # ...
   class DatabaseConnector:
       def __init__(self):
           # ...
           self.server_name = "WIN-4JQC1EKGAGL\\SQLEXPRESS" # Change this to your SQL Server instance name
           self.database_name = "UniversityReservationSystem" # Change this to your database name
           # ...

Install Python Dependencies

1. Open a command prompt or terminal.
2. Navigate to the project's root directory.
3. Install the required Python libraries:

   pip install tkinter ttkwidgets tkcalendar pyodbc

Running the Application

1. Execute the main application file:

   python App.py

Administrator Panel

User's Personal Page

Reservation Making Form