College of Engineering, Technology and Computational Science

Department of Computer Science and MIS

Online Shopping system
Data base document
By

1. Ermias Molla ………………………..UUR00192/17

2. Bitanya Samson……………………..UUR00765/17
3. Sara hailemariam……………………UUR00733/17
4. Meklit yonas………………………... UUR00695/17
5. Natnael Belay………………………..UUR00764/17

Instructor:

02/9/2025

1|Page
Contents

Acknowledgements................................................................................................
............. v

1.
Introduction........................................................................................... .............
............. 3
1.1 Core benefits of this system
include:............................................................................... .3

2.Statement of
problem........................ ...................................................................................
4

3. Database
Planning........................ ...................................................................................
.....4

3.1 Project
Overview .......................... .........................................................................
............. 4
3.2 Objectives............. .................................................................................
..............................

3.3Scope of the
Database..........................................................................................................
4

4. System
Definition..............................................................................................................
6

4.1 Stakeholder
Identification................................................................................................... 5

4.2 Data Collection

Techniques...........................................................................................
....6

4.3Functional
Requirements................................................................................................... 7
4.4Non-Functional
Requirements........................................................................................
........8
4.5Business Rules and
Constraints............................................................................................. 8
2|Page
4. Database
Design..................................................................................................................9
4.1 Entities.......................................................................................................... 9

4.2.1 Product
Entity........................................ .....................................................................
.. 9
4.2.2 Order
Entity .......................................... ........................................................... 10
4.2.3 Delivery
Entity............................................. ................................................................10
4.2.4 Category
Entity........................................ ..............................................................
........10
4.2.5 Review
Entity.................................... ...................................................................
..... 10
4.2.6 Staff
Entity......................................... ..............................................................
................ 11
4.2.7 Warehouse
Entity..................................................................................................
........... 11
5.1 E-R diagram......................................................................... .............................
………….12

Introduction
This project focuses on the development of a Relational Database Management System
(RDBMs) for a local online shopping management system based in Addis Ababa, Ethiopia. The
business operates exclusively within the city and deals only in self-manufactured products,
eliminating the need for external vendors or third-party suppliers.

3|Page
Designed for a business that produces, manages, and sells its own goods directly to consumers,
the system aims to streamline key operations such as product inventory tracking, customer order
processing, user account management, and delivery coordination across different areas within
Addis Ababa. Since all items are manufactured in-house, the database system is focused on
internal efficiency, simplicity, and real-time management rather than vendor integration or
external supply chains.
Core benefits of this system include:
 Centralized management of product data, customer details, order records, and delivery
information.
 Real-time updates to inventory and order statuses to ensure smooth business flow
and reduced delays.
 Efficient and secure handling of customer information and transaction histories.
 Geographically focused delivery coordination, specifically optimized for the districts
and neighborhoods of Addis Ababa.
 Structured reporting features to help track daily sales, customer activity, and
product performance.
The system is optimized for the unique needs of a city-based business, offering a lightweight,
cost-effective, and scalable solution that can grow as the business expands its operations within
Addis Ababa. This RDBMS provides a solid backbone for digitizing and automating key
business functions, allowing the company to enhance customer satisfaction and operational
reliability without overcomplicating its processes.

4|Page
Statement of problem
Limited Accessibility and Geographic Constraints: Traditional systems typically required
users to be physically present at a specific location to access data. This posed a significant
challenge for organizations with multiple branches, remote employees, or those needing to
collaborate with external partners across geographical boundaries.
Difficult and Time-Consuming Data Retrieval and Analysis: Searching through physical
records or disparate electronic files was often a laborious and inefficient process. Generating
comprehensive reports or performing complex data analysis required significant manual effort
and specialized skills.
Data Inconsistency and Integrity Issues: Maintaining data accuracy and consistency across
multiple disconnected systems was a major challenge. Redundant data entry increased the risk of
errors, and the lack of centralized control made it difficult to enforce data integrity rules.
Scalability and Storage Limitations: As organizations grew and generated more data,
traditional storage methods often became inadequate and expensive to scale. Physical storage
space was limited, and managing increasing volumes of electronic files presented logistical
challenges.

Database Planning
The planning phase establishes the groundwork for the successful development of a relational
database system for a local online shopping platform operating exclusively in Addis Ababa.
This phase is focused on clearly defining the purpose, identifying data needs, selecting
appropriate tools, and outlining the responsibilities of each stakeholder involved in the database
development process.

1. Project Overview
The system will manage product manufacturing and direct-to-customer delivery without external
vendors. It will support customer registrations, product listings, order processing, delivery
assignments, payments, and customer reviews.

2. Objectives
 To build a structured and scalable relational database system
 To ensure all internal operations (products, orders, delivery, and reviews) are accurately
recorded
 To provide both administrative and customer-facing data functionalities
 To enable future integration with basic reporting or web-based interfaces

3. Scope of the Database

The database will store and manage the following entities:

Customer – personal and login information

5|Page
Product – manufactured goods ready for sale
Category – grouping of products
Order – details of customer purchases
Staff – includes both administrators and delivery personnel
Warehouse – physical stock information
Payment – transaction tracking
Delivery – assignment and status of order fulfillment
Review – customer feedback on delivered products
4. Stakeholders
Admins – Oversee staff accounts, manages warehouse, product inventory, and order approvals
Delivery Staff – Handle deliveries and update delivery statuses
Customers – End-users who place orders and submit reviews
Developers/Database Designers – Responsible for planning, development, and deployment
5. Risk Considerations
Data Loss Prevention: Regular backups will be planned, especially during transitions between Access
and MySQL.

Power and Connectivity: Given local conditions, the database design will support offline operations
in MS Access when needed, with synchronization to MySQL where possible.

Scalability: While the system is city-based, it will be structured to allow future expansion by
normalizing data and using foreign key constraints.

6. Timeline and Team Roles

Planning Duration: Estimated 1
week Design & ER Modeling: 1–2
weeks
Implementation (MS Access → MySQL): 2–3 weeks
Testing and Feedback: 1 week
Deployment and Handover: Final 1 week
Role Responsibility
Database Designer Schema creation, ERD modeling
Developer Form and query implementation
Admin Stakeholder Data source verification and testing
QA/Tester Validating logic, data integrity, and output

6|Page
 Phase % of Amount Description
Total (Br)
1. Planning 10% 35,000 Br Requirements gathering, feasibility, scope
definition, tool selection
2. Database Design 20% 70,000 Br ERD modeling, normalization, schema drafting,
choosing data types, keys
3. Implementation 30% 105,000 Br Actual development in MS Access & MySQL,
setting up forms, queries, scripts
4. Testing & QA 20% 70,000 Br Data validation, integrity checks, query testing,
user feedback loop
5. Deployment & 10% 35,000 Br Setting up the final environment, admin training,
Training data migration
6. Maintenance 10% 35,000 Br Support after deployment, fixing bugs, small
feature adjustments
Total 100% 350,000 Br

System Definition
System Title: Local Online Shopping Management System (LOSMS) – Addis Ababa
System Type: Relational Database Management System (RDBMS)
Development Tools:
Database Platforms: Microsoft Access (for prototyping/local use), MySQL (for scalable deployment)
Supporting Tools: dbdiagram.io (for ERD), MySQL Workbench, MS Access Forms, Visual Basic
(optional for UI scripting)

System Purpose: The LOSMS is designed to manage the end-to-end operations of a locally-based
online shopping platform in Addis Ababa, Ethiopia. The system, customer orders, delivery
assignments, payment tracking, and post-sale reviews without relying on third-party vendors.

Core Objectives:
 Maintain accurate and up-to-date records of customers, products, orders, payments, and delivery
statuses
 Enable administrators to manage staff roles (admin and delivery personnel) and warehouse stock
 Allow customers to place orders, view product categories, and submit reviews
 Provide reports and search functionalities to support decision-making and operations
 Ensure data consistency, integrity, and scalability for potential future system upgrades

System Users:
Administrator: Manages system users, product entries, orders, delivery staff, and warehouse data

7|Page
Delivery Staff: Views assigned deliveries and updates delivery status
Customers: Browse products, place orders, make payments, and submit reviews
Requirement Gathering: Requirement gathering is a critical phase in the Database Development Life
Cycle (DDLC) that focuses on identifying, analyzing, and documenting all necessary data and user
requirements for the system. For this project, the requirement gathering process focuses on building a
relational database to support a local online shopping management system in Addis Ababa, which
manufactures its own products and does not rely on external vendors.

1. Stakeholder Identification
The requirement gathering process began with identifying key stakeholders who will interact with or
influence the system. Their inputs provide essential insights into what the system must support.
Stakeholder Role & Responsibility
Administrators Oversee product management, orders, delivery staff, and warehouse inventory
Delivery Staff Responsible for handling deliveries and updating delivery status
Customers End-users who register, browse products, place orders, and provide reviews
Database Developer Responsible for implementing the technical design based on requirements
Business Owner Guides business logic, goals, and resource priorities

2. Data Collection Techniques

To gather accurate and meaningful requirements for the database system, the following techniques were
applied:

Interviews with admins and staff to understand daily operations, especially around order processing,
inventory updates, and delivery assignments.

Observation of the manual workflows such as how products are entered and tracked, how orders are
placed and fulfilled, and how staff communicate assignments.

Surveys and Feedback collected from test customers to determine expectations for browsing products,
viewing categories, placing orders, and submitting reviews.

Review of existing paper-based logs, such as inventory sheets, payment receipts, and delivery logs.

3. Functional Requirements
These are the core actions and services the database system must support:

A. Customer Module
 Register new customers with personal information
 Allow customers to log in and update their details
 View available products by category
 Place orders and view past orders

8|Page
B. Product & Category Module
 Add, update, and delete product records
 Assign products to appropriate categories
 Maintain information on price, description, and quantity

C. Order Module
 Create orders associated with customers and products
 Track order status (e.g., pending, confirmed, out for delivery)
 Associate orders with delivery and payment information

D. Staff Module
 Register and manage delivery staff and admin users
 Assign delivery tasks to staff
 Allow staff to log in and update delivery status

E. Warehouse Module
 Maintain product quantities
 Update stock after successful order confirmation
 Track product storage location if needed

F. Payment Module

 Record payments made by customers

 Link payment to specific orders
 Support payment status (e.g., paid, pending)

G. Delivery Module

 Assign deliveries to staff

 Track delivery date, time, and current status
 Log customer receipt of the product

H. Review Module

 Allow customers to rate and comment on products

 Link reviews to customers and specific products
 View review history

I. Reporting Module

 Generate reports for total sales, orders per day/week, and top-reviewed products
 View delivery performance by staff
 Export data summaries for business decisions

4. Non-Functional Requirements
These requirements define the quality and operational standards the database system must adhere to.

9|Page
 Requirement Description
Type
Performance Query response time should be less than 2 seconds in local network use
Reliability System must be operable 95% of the time with scheduled weekly backups
Security Role-based access control for admin and delivery staff
Usability Simple, form-based interfaces for non-technical users via MS Access
Scalability Database must support future addition of new products, categories, and users
Data Integrity All foreign key relationships enforced; validation on all input forms
Maintainability System must be easy to update with minimal effort using MySQL or Access
tools

5. Business Rules and Constraints

 One customer can place multiple orders.
 Each order can contain multiple products.
 Every product belongs to exactly one category.
 Only administrators can add or modify products and categories.
 Only assigned delivery staff can update the status of deliveries.
 A review can only be submitted by customers who completed a purchase.
 Product stock in the warehouse must reduce after order confirmation.

Database Design

Entities: Customer, Product, Category, Order, Staff, Warehouse, Payment, Delivery, Review

Customer entity
1NF: c_id,C_Fname,C_Mname,C_Lname, C_Country, C_City, C_Subcity, C_Woreda, C_Streetno,
C_hno, , C_Cellphone1, C_Cellphone2, C_Email, C_Password, C_Gender, C_Age, C_Uname,
Product_Id, Review_Id P_Method, P_Date, D_Adress, Pro_Name,Pro_Price, D_Adress,
D_Method, O_Date.
2NF: c_id, C_Fname,C_Mname,C_Lname, C_Country, C_City, C_Subcity, C_Woreda, C_hno, ,
C_Cellphone1, C_Cellphone2, C_Email, C_Password, C_Gender, C_Age, C_Uname, Product_Id,
Review_Id, Pro_Name,Pro_Price.
3NF: : c_id, C_Fname,C_Mname,C_Lname, C_Subcity, C_Woreda, C_hNo, C_Cellphone1,
C_Cellphone2, C_Email, C_Password, C_Gender, C_Age, C_Uname, , Product_Id, Review_Id.

Product Entity

1NF:p_id (PK), p_name, p_desc, p_barcode, p_mfg_date, p_exp_date, p_price, cat_id, txn_id,
ship_date, deliv_addr, pay_method, pay_date, deliv_fee, cat_desc
2NF:Product: p_id (PK), p_name, p_desc, p_barcode, p_mfg_date, p_exp_date, p_price,
Category: cat_id (PK), cat_name, cat_desc
3NF:Product: p_id (PK), p_name, p_desc, p_barcode, p_mfg_date, p_exp_date, p_price, cat_id c

10 | P a g e
Order Entity
1NF:o_id, p_id, c_id, o_date, o_status, o_ship_date, p_desc, p_barcode, p_mfg_date, pay_id,
pay_method, pay_date, amt_paid, deliv_id, deliv_addr, deliv_method, deliv_fee
2NF:Order: o_id (PK), c_id, o_date, o_status, o_ship_date, Order_Product: o_id (PK, FK), p_id
(PK, FK), qty
Payment: pay_id (PK), o_id (FK), pay_method, pay_date, amt_paid
Delivery: deliv_id (PK), o_id (FK), deliv_addr, deliv_method, deliv_fee
3NF: Order: o_id (PK), c_id (FK), o_date, o_status, o_ship_date
Order_Product: o_id (PK, FK), p_id (PK, FK), qty
Payment: pay_id (PK), o_id (FK), pay_method, pay_date, amt_paid, pay_verify_status
Delivery: deliv_id (PK), o_id (FK), deliv_addr, deliv_method, deliv_fee, deliv_status, s_id
(FK) Payment Entity
1NF: pay_id, pay_method, pay_date, txn_id, amt_paid, p_id, p_name, p_desc, o_status,
o_ship_date
2NF: Payment: pay_id (PK), p_id (FK), p_name, p_desc, p_barcode), pay_method, pay_date,
txn_id, amt_paid, pay_verify_status
3NF: pay_id (PK, pay_method, pay_date, txn_id, amt_paid, pay_verify_status, p_id (FK
Delivery Entity
1NF: deliv_id, deliv_addr, deliv_method, deliv_fee, deliv_status, p_name, p_desc, p_barcode,
p_mfg_date, o_date, o_status, o_ship_date
2NF: Delivery: deliv_id (PK), p_id (FK), deliv_addr, deliv_method, deliv_fee, deliv_status,
pay_date, txn_id, amt_paid
3NF: Delivery: deliv_id (PK), pay_id (FK), deliv_addr, deliv_method, deliv_fee, deliv_status,
Category Entity
1NF:cat_id, cat_name, cat_desc, p_id, feedback, rating, rev_date, s_name, s_role
2NF:Category: cat_id (PK), cat_name, cat_desc, s_id, s_addr, s_role, s_phone, s_email
3NF: Category: cat_id (PK), cat_name, cat_desc, s_id
Review Entity
1NF:rev_id, feedback, rating, rev_date, deliv_addr, deliv_method, deliv_fee, p_name, p_desc,
p_barcode, pay_date, amt_paid, o_status
2NF:Review: rev_id (PK), o_id (FK), p_id (FK), pay_id (FK), feedback, rating, rev_date

11 | P a g e
3NF:Review: rev_id (PK), o_id (FK), pay_id (FK), feedback, rating, rev_date
Staff Entity
1NF: s_id, s_fname,s_mname, s_lnme, s_subcity,s_woreda, s_hno, s_role, s_phone, s_email,
s_pwd, s_gender, s_age, emp_status
2NF: Staff: s_id (PK), s_name, s_addr, s_phone, s_email, s_pwd, s_gender, s_age, emp_status,
s_role deliv_id , deliv_addr, deliv_method,
3NF:Staff: s_id (PK), s_name, s_addr, s_phone, s_email, s_pwd, s_gender, s_dob, emp_status,
deliv_id

Warehouse Entity
1NF: wh_id, wh_subcity,wh_woreda, wh_hno, wh_name, wh_status, p_id, stock_qty, s_id,
p_name, p_desc, p_barcode, p_mfg_date
2NF: wh_id (PK), wh_subcity,wh_woreda, wh_hno, wh_name, wh_status, s_id, , s_phone,
s_email, s_pwd, s_gender ,stock_qty
3NF: wh_id (PK), wh_subcity,wh_woreda, wh_hno, wh_name, wh_status, s_id, stock_qty

12 | P a g e
13 | P a g e
14 | P a g e