JoaoESmoreira
Data Base Project
The main objective of this project is to apply industry-standard practices in the design, implementation, and deployment of database-driven applications.
The project covers all key phases of the software development lifecycle, from initial planning and modeling to testing and delivery, with a strong emphasis on data management and system reliability.
The full repository and documentation can be found here: DB Project Repository.
Objectives
The project aims to:
- Demonstrate how a database application can be organized, planned, and executed.
- Develop both conceptual and physical data models to support and persist application data.
- Design, implement, test, and deploy a complete database system.
- Install, configure, manage, and tune a modern relational DBMS such as PostgreSQL.
- Apply client, and server, side programming using SQL and PL/pgSQL.
Dependencies
To run the project correctly, some technologies are required. Follow this simple documentation to help you set up everything properly.
Tools and Technologies
Tips for Dependencies
Before you do something, check these shell commands and verify what you need to install.
# check if already installed:
flask --version
sudo pip3 install flask
# check if it's correctly installed:
python3
>>> import flask
>>> # it's all right
>>> from werkzeug.security import generate_password_hash, check_password_hash
>>> # it's all right
Tools Installation
If you don't have some of the listed dependencies installed, here is how to do it.
- Python and Libraries
sudo apt install python3 python3-pip # Install python and pip sudo pip install flask sudo pip install werkzeug sudo apt install libpq-dev # In case you don't have this library sudo pip install psycopg2 pip install flask-jwt-extended - PostgreSQL
# Create the file repository configuration: sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' # Import the repository signing key: wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - # Update the package lists: sudo apt-get update # Install the latest version of PostgreSQL: sudo apt-get -y install postgresql - Postman
DataBase Setup
To set up the database, access PostgreSQL DBMS using `psql` or `pgadmin4`. We used the psql client with the following command:
# Use the default credentials:
# Username: postgres
# Password: postgres
psql -h localhost -p 5432 -U postgres
After access, create the database and connect to it:
CREATE DATABASE dbshop;
# List databases:
\l
# Connect to the created database:
\c dbshop
Then, execute the following commands to build your schema:
\i schema.sql # create tables
\i insert.sql # insert data
\i trigger.sql # create triggers
\i drop_tables.sql # drop all tables (optional)
Note: If you use pgAdmin4, see:
User Manual
Before starting → run the script `test.py` to encrypt the first users’ passwords. To run the project → `python3 main.py`
Users Login
Description: User authentication with username and password. A superadmin was created to register other admins and sellers.
URL: /api/login
Method: PUT
# Input
{
"username": "SuperAdmin",
"password": "SuperAdmin"
}
# Output
{
"status": 200,
"token": "TOKEN"
}
Users Registration
Description: Only admins can register other admins and sellers. Anyone can register as a buyer.
URL: /api/register
Method: POST
- Register admin/seller
{ "username": "Put a name", "nif": 0, "email": "Put an email", "adress": "Put an address", "password": "Put a pass", "token": "Token received in login", "user_type": "administrator/seller" } - Register buyer
{ "username": "Put a name", "nif": 0, "email": "Put an email", "adress": "Put an address", "password": "Put a pass" }
Product Creation
Description: Only sellers can create products.
URL: /api/product/add
Method: POST
{
"type": "Put a type",
"description": "Put a description",
"height": 0,
"weight": 0,
"colour": "Put a color",
"stock": 0,
"price": 0,
"token": "Token received in login"
}
Update Product Details
Description: Only sellers can update products.
URL: /api/product/{prod_id}
Method: PUT
{
"description": "new description",
"height": "new height",
"weight": "new weight",
"colour": "new colour",
"price": "new price",
"token": "Token received in login"
}
Obtain Product Details
Description: All users can see product details.
URL: /api/product/{product_id}
Method: GET
Make an Order
Description: Only buyers can make orders.
URL: /api/order
Method: PUT
{
"cart": [[Prod1, Quantity1], [Prod2, Quantity2], ...],
"token": "Token received in login"
}
Rate and Comment a Product
Description: Buyers can rate and comment on purchased products.
URL: /api/rating/{product_id}
Method: POST
{
"rating": 0,
"comment": "Put a comment",
"token": "Token received in login"
}
Questions and Comments (Forum)
Description: All users can ask or comment about products.
Sales Statistics
Description: Obtain sales statistics for the last 12 months.
URL: /api/statistics
Method: GET
# Output Example
{
"results": [
{
"month": "07-2021",
"orders": 1,
"total_value": "1622"
},
{
"month": "09-2021",
"orders": 2,
"total_value": "2144"
},
{
"month": "03-2022",
"orders": 1,
"total_value": "2784"
},
{
"month": "01-2022",
"orders": 1,
"total_value": "4800"
},
{
"month": "05-2022",
"orders": 5,
"total_value": "14717"
}
],
"status": 200
}
Footer
Copyright © 2025 Joao ES Moreira
The contents of this website are licensed under the Creative Commons Attribution-NoDerivatives 4.0 International License (CC-BY-ND 4.0).
The source code of this website is licensed under the MIT license, and available in GitHub repositor. User-submitted contributions to the site are welcome, as long as the contributor agrees to license their submission with the CC-BY-ND 4.0 license.