A comprehensive relational database implementation for a retail store management system, featuring complete schema design, constraints, and PostgreSQL optimization.
This repository contains a complete database implementation for a multi-store retail management system. The database schema is designed to handle products, inventory, customers, orders, and shipments across multiple store locations, both physical and online.
The project includes:
- ✅ Full database schema with 7 normalized tables
- ✅ Complete referential integrity with foreign keys
- ✅ Business logic constraints (CHECK constraints)
- ✅ Detailed documentation and comments
- ✅ Entity-Relationship diagram (ERD)
- ✅ MariaDB alternative implementation
The database consists of the following tables:
| Table | Description |
|---|---|
| produtos | Product catalog with pricing and media information |
| lojas | Store information (physical and web locations) |
| clientes | Customer details and contact information |
| estoques | Inventory management per store |
| pedidos | Order tracking and status management |
| pedidos_itens | Individual items within orders |
| envios | Shipment tracking and delivery status |
- Data Integrity: Comprehensive foreign key relationships ensure referential integrity
- Business Rules: CHECK constraints enforce business logic:
- Product prices must be non-negative
- Stores must have either a web address or physical address
- Inventory quantities cannot be negative
- Email addresses must contain '@' symbol
- Predefined status values for orders and shipments
- Normalized Design: 3rd Normal Form (3NF) structure to minimize redundancy
- User Management: Includes database user creation with proper permissions
- Schema Organization: All tables organized under the
lojasschema
The complete ERD is available in the repository as:
pset1/cc1n_202306090_postgresql.architect(SQL Power Architect source)pset1/cc1n_202306090_postgresql.pdf(Visual diagram)
- PostgreSQL 12 or higher
- Linux environment (or WSL on Windows)
- UTF-8 locale support (
pt_BR.UTF-8)
- Clone the repository:
git clone https://github.com/kauanmodolo/PostgreSQL-Database.git
cd PostgreSQL-Database- Execute the SQL script:
psql -U postgres -f pset1/cc1n_202306090_postgresql.sqlThe script will automatically:
- Create the database user
- Create the
uvvdatabase - Set up the
lojasschema - Create all tables with constraints
- Establish foreign key relationships
The default configuration creates:
- Database:
uvv - Schema:
lojas - User:
kauan(modify as needed in the script) - Encoding: UTF-8
- Collation:
pt_BR.UTF-8
⚠️ Security Note: Remember to change the default password in the script before deploying to production.
PostgreSQL-Database/
├── pset1/
│ ├── cc1n_202306090_postgresql.sql # Main PostgreSQL script
│ ├── cc1n_202306090_postgresql.architect # ERD source file
│ ├── cc1n_202306090_postgresql.pdf # ERD visualization
│ └── mariaDB/ # MariaDB implementation
└── README.md
- PostgreSQL: Primary database management system
- SQL Power Architect: Database modeling and ERD design
- Linux: Development and deployment environment
- MariaDB: Alternative database implementation
This database schema is suitable for:
- E-commerce platforms with multiple stores
- Retail chain management systems
- Inventory tracking across locations
- Order fulfillment and shipment tracking
- Customer relationship management
Stores product information including pricing, images, and metadata with support for multimedia content.
Manages both physical and online store locations with geolocation support (latitude/longitude).
Customer profiles with contact information supporting up to three phone numbers.
Real-time inventory tracking per store location.
Order management with status tracking (OPEN, PAID, COMPLETE, CANCELLED, REFUNDED, SHIPPED).
Line items for each order with pricing and quantity information.
Delivery tracking with status updates (CREATED, SHIPPED, IN_TRANSIT, DELIVERED).