Police Station Database Website

An optimized fullstack website for a police station to store criminal record information and police officer information.

Overview

This project was created to learn about databases and how to connect code to a phpMyAdmin localhost database server. I utilized XAMPP to host the server to create and use database information on phpmyAdmin. Databases, tables, and information were created using SQL statements as attached in the source. The fullstack website was created for two views: a normal viewer and a police officer with more viewer privileges. There is a registration system for the two different views which displays different pages and viewership of private and public data based on the user. The code pulls information using SQL statements to pull from the database.

Team

Demo's

Regular User Demo

Officer/Higher Power user Demo

Add function not completely shown since it is straightfoward and time consuming to do. Hopefully it is understood how it works!

phpMyAdmin Database Tables & SQL Functions/Procedure "Tree"

Tech Stack & Features & Development

Features:

• Registration & Login: User logs in as a viewer or an officer, controlled using sessions and using info stored on the Database table for logins.
• Logout: Logs the user out and security added to prevent roundabouts to go back into the session in the frontend and backend.

Regular User

• Home: Displays Crime and Criminal Information such as ID, Type, Dates, Criminal Name, Criminal Status, etc.
• Search: Search by entering a character. Can filter whether user is searching for a Criminal or a Probation Officer.

Officer User

• Home: Displays the same information seen on the regular user view.
• Update: Uses SQL procedures in the database such as updateClassification, updateDateCH, updateStatus, etc to edit crime information.
• Delete: Uses SQL procedure deleteCrime to delete the crime after clicking a confirmation pop up.
• Search: Search for a criminal, probation officer, (NEW) Search for a Crime Officer's Badge Number which is something only officers can do. The backend utilizes an SQL Function searchCrimeOfficer to retrieve this.
• Add Crime & Criminal: Enter the crime information and criminal information like submitting a form of information in the database system. Uses SQL procedures addCrime and addCriminal.

Backend:

• Python & Flask-Python (micro web framework written in Python)
• Pandas (open source software library)
• XAMPP (Apache & MySQL Ports) & phpMyAdmin
• Structured query language (SQL): SELECT, UPDATE, DELETE, INSERT INTO, CREATE TABLE, ALTER DATABASE, ETC Statements created and used to develop police station tables and information database.

Frontend:

• HTML
• CSS
• JavaScript