Relational database system designed and implemented for a local bookstore in Ifrane, Morocco. Built as part of CSC 3326 — Database Systems (Fall 2024) in collaboration with a real client.
The system manages the full operational workflow of a bookstore — from product catalog and inventory to customer orders, notifications, and administrative actions. It was designed following a client requirements gathering process and implements a normalized relational schema with referential integrity.
Client: Librairie Ifrane, a local bookstore selling books and school supplies Team: Rita Jalal, Zaynab Aboulkacem, Tanishka Suwalka Supervisor: Dr. Lamiae Bouanane
bookstore-db/
├── sql/
│ ├── schema.sql ← CREATE TABLE statements with constraints
│ ├── seed_data.sql ← Sample data for all tables
│ └── queries.sql ← 10 analytical SQL queries
├── interface/
│ └── dashboard.html ← Web-based management interface
└── README.md
The database consists of 7 normalized tables:
| Table | Description |
|---|---|
| Users | Customers, staff, and admins with role-based access |
| Categories | Product categories for catalog organization |
| Products | Catalog items with stock and low-stock threshold tracking |
| Orders | Customer orders with status tracking |
| OrderDetails | Junction table linking orders to products (composite PK) |
| Notifications | Event-driven notifications sent to users |
| AdminActions | Audit log of all administrative operations |
- Composite primary key on
OrderDetails (Order_ID, Product_ID)to represent the many-to-many relationship between orders and products - CHECK constraints enforce valid values for
Order_Status,Role, andAccount_Status - Low_Stock_Threshold per product enables automated alerts when inventory falls below acceptable levels
- AdminActions table provides a full audit trail of staff/admin operations
- PostgreSQL 13+
- Clone the repository
git clone https://github.com/zaynab724/bookstore-db.git
cd bookstore-db- Create the database
psql -U postgres -c "CREATE DATABASE bookstore;"- Run the schema
psql -U postgres -d bookstore -f sql/schema.sql- Load sample data
psql -U postgres -d bookstore -f sql/seed_data.sql- Run sample queries
psql -U postgres -d bookstore -f sql/queries.sqlOpen interface/dashboard.html directly in any browser — no server required.
The queries.sql file includes 10 analytical queries:
- All products with their category
- All orders with customer name and status
- Order details — items per order
- Products below low stock threshold
- Total revenue per customer
- Most ordered products
- Orders summary by status
- Unread notifications per user
- Full order history for a specific customer
- Revenue by category
A browser-based management dashboard built with HTML, CSS, and JavaScript provides a visual interface over the database. It includes:
- Dashboard with key metrics and recent orders
- Product catalog with search and category filter
- Order management with status tracking
- Customer registry
- Database schema viewer
- Notifications panel
| Layer | Technology |
|---|---|
| Database | PostgreSQL |
| Schema Design | ER Modeling, Normalization |
| Query Language | SQL |
| Interface | HTML, CSS, JavaScript |
- Each order is linked to a verified user account
- Stock quantity is reduced upon successful order placement
- Orders follow a strict status flow: Pending → Processed → Shipped → Delivered
- Low stock alerts are triggered when inventory falls below the per-product threshold
- All admin and staff operations are logged in the AdminActions audit table
- Products are categorized for easier catalog management and reporting