COMSAT UNIVERSITY ISLAMABAD ATTOCK CAMPUS
DATABASE LAB PROJECT
NAME: MENAHIL NOOR- USMAN SADDIQUE
REGISTRATION NO: (SP24-BSE-035) (SP24-BSE-024)
SUBMITTED TO: DR MEHREEN WAHAB
DEPARTEMENT: SOFTWARE ENGINEERING
LAB PROJECT
DATE: 12 JUNE 2025
PROJECT: Inventory Management System
Project Title:
"Database Design and Implementation of an Inventory Management System".
Project Description:
The Inventory Management System is a database solution designed to efficiently
manage the flow of products, orders, and supplier activities within a business. This
system enables tracking of product availability, customer purchases, supplier
deliveries, and administrative reporting — all from a centralized platform.
Entities and Attributes:
Customer
Stores information about people or businesses who make purchase orders.
Attribute Name Description
CustomerID Unique ID for each customer (PrimaryKey)
CustomerName Full name of the customer
Phone Contact number
Email Email address
Address Residential or business address
Product
Represents items available in inventory.
Attribute Name Description
ProductID Unique ID for each product (PrimaryKey)
ProductName Name of the product
Price Selling price per unit
Category Product category (e.g., electronics)
QuantityInStock Current available quantity
PurchaseOrder
Captures orders placed by customers.
Attribute Name Description
PurchaseOrderID Unique ID for each order (PK)
CustomerID FK referencing Customer
OrderDate Date the order was placed
Product ID FK referencing Product
Total Amount Total Amount
Supplier
Tracks vendors who supply products to inventory.
Attribute Name Description
SupplierID Unique ID for each supplier (PrimaryKey)
SupplierName Name of the supplier
Phone Contact number
Email Email Address
Address Supplier's business address
SupplyOrder
Records deliveries received from suppliers.
Attribute Name Description
SupplyOrderID Unique ID for each supply order
(PrimaryKey)
SupplierID FK referencing Supplier
OrderDate Date the order was received
Total Cost Total Cost
Admin
Represents the users who manage the system and generate reports.
Attribute Name Description
AdminID Unique ID for each admin (PK)
AdminName Full name of the admin
Username Login username
Password Login password
Role Admin's role (e.g., Manager)
Report
Stores reports created by admins related to purchase/supply orders.
Attribute Name Description
ReportID Unique ID for each report (PK)
AdminID FK referencing Admin
ReportDate Date report was created
ReportType Type of report ('Purchase'/'Supply')
Description Summary or details of the report
Entity Relationship Diagram:
Relationships & Cardinality Constraints:
1. Customer — places — PurchaseOrder
Type: One-to-Many (1:N)
Explanation: One customer can place multiple purchase orders, but each
purchase order belongs to only one customer.
Customer (1) ——— (N) PurchaseOrder
2. PurchaseOrder — contains — Products
Type: Many-to-Many (M:N), implemented using a junction table
PurchaseOrderDetail
Explanation: One purchase order can include multiple products, and one
product can appear in multiple purchase orders.
PurchaseOrder (M) ——— (N) Product
3. Admin — manages — Product
Type: One-to-Many (1:N)
Explanation: Each product is entered or updated by one admin, but an
admin can manage multiple products.
AdminID is a foreign key in Product.
Admin (1) ——— (N) Product
4. Supplier — provides — SupplyOrder
Type: One-to-Many (1:N)
Explanation: One supplier can provide many supply orders, but each
supply order comes from only one supplier.
Supplier (1) ——— (N) SupplyOrder
5. SupplyOrder — includes — Products
Type: Many-to-Many (M:N), implemented using a junction table
SupplyOrderDetail
Explanation: One supply order can include multiple products, and each
product can be supplied in multiple supply orders.
SupplyOrder (M) ——— (N) Product
6. Admin — generates — Report
Type: One-to-Many (1:N)
Explanation: One admin can generate multiple reports, but each report
is generated by only one admin.
Admin (1) ——— (N) Report
From Entity To Entity Type Cardinality
Customer PurchaseOrder 1:N One-to-Many
PurchaseOrder Product 1:N One-to-Many
Supplier SupplyOrder 1:N One-to-Many
SupplyOrder Product 1:N One-to-Many
Admin Report 1:N One-to-Many
Transform ER Model to Relation:
Relations from ER Diagram
Based on entities: Customer, Product, PurchaseOrder, Supplier, SupplyOrder,
Admin, and Report.
Customer:
Customer(CustomerID, CustomerName, Phone, Email, Address)
Product
Product(ProductID, ProductName, Category, Price, QuantityInStock)
PurchaseOrder
PurchaseOrder(PurchaseOrderID, CustomerID, OrderDate, TotalAmount)
→ CustomerID is a foreign key referencing Customer(CustomerID)
Supplier
Supplier(SupplierID, SupplierName, Phone, Email, Address)
SupplyOrder
SupplyOrder(SupplyOrderID, SupplierID, OrderDate, TotalCost)
→ SupplierID is a foreign key referencing Supplier(SupplierID)
Admin
Admin(AdminID, AdminName, Username, Password, Role)
Report
Report(ReportID, AdminID, ReportDate, ReportType, Description)
→ AdminID is a foreign key referencing Admin(AdminID)
Normalization Process:
Unnormalized Form (UNF)
Example of a bad combined table:
InventoryData(CustomerName, ProductNames, Quantity, Prices,
SupplierName, PaymentMethod, ReportDetails)
Issues:
Repeating groups (ProductNames, Quantity)
Multivalued fields (Prices)
Redundancy in customer/supplier/report data
First Normal Form (1NF)
Customer(CustomerID, CustomerName, Phone, Email, Address)
Product(ProductID, ProductName, Category, Price)
Supplier(SupplierID, SupplierName)
PurchaseOrder(PurchaseOrderID, CustomerID, OrderDate)
SupplyOrder(SupplyOrderID, SupplierID, OrderDate)
Second Normal Form (2NF)
Report(ReportID, AdminID, AdminName, ReportType, ReportDate)
Primary Key: ReportID
Issue:
o AdminName depends on AdminID, not on ReportID
Fix:
o Move AdminName to Admin(AdminID, AdminName)
o Keep Report(ReportID, AdminID, ReportType, ReportDate)
Conclusion:
Not in 2NF → Now normalized into 2NF
Third Normal Form (3NF)
Fix:
Remove transitive dependencies
Example: If SupplierName is stored in both SupplyOrder and Supplier, that’s
redundant.
Supplier(SupplierID, SupplierName, Phone, Email, Address)
SupplyOrder(SupplyOrderID, SupplierID, OrderDate)
Don't store SupplierName again in SupplyOrder—just use the ID.
Also:
Report(ReportID, AdminID, ReportType, Description, ReportDate)
Admin(AdminID, AdminName, Username, Password, Role)
So all attributes depend only on the primary key, and not transitively.
Writing SQL (DDL) Queries:
CUSTOMER TABLE:
SUPPLIER TABLE:
Product Table:
Admin Table:
Purchase Order:
Supply Order:
Report:
Inserting data:
CUSTOMER TABLE:
INSERT INTO Customer VALUES (1, 'Ali Khan', '03123456789', 'ali@[Link]', 'Karachi');
INSERT INTO Customer VALUES (2, 'Sara Iqbal', '03214567890', 'sara@[Link]', 'Lahore');
INSERT INTO Customer VALUES (3, 'Bilal Ahmad', '03335678901', 'bilal@[Link]',
'Islamabad');
INSERT INTO Customer VALUES (4, 'Nida Farooq', '03001234567', '[Link]@[Link]',
'Rawalpindi');
INSERT INTO Customer VALUES (5, 'Usman Tariq', '03451112223', 'usman@[Link]',
'Peshawar');
INSERT INTO Customer VALUES (6, 'Hira Sajjad', '03111223344', 'hira@[Link]', 'Multan');
PRODUCT TABLE:
INSERT INTO Product VALUES (101, 'Wireless Mouse', 'Electronics', 1200.00, 150);
INSERT INTO Product VALUES (102, 'Mechanical Keyboard', 'Electronics', 3500.00, 100);
INSERT INTO Product VALUES (103, 'Laptop Stand', 'Accessories', 2200.00, 75);
INSERT INTO Product VALUES (104, 'USB-C Hub', 'Accessories', 1800.00, 90);
INSERT INTO Product VALUES (105, 'Webcam HD', 'Electronics', 3000.00, 60);
INSERT INTO Product VALUES (106, 'Bluetooth Speaker', 'Audio', 4500.00, 40);
INSERT INTO Product VALUES (107, 'LED Monitor 24"', 'Displays', 17000.00, 30);
SUPPLIER TABLE:
INSERT INTO Supplier VALUES (1, 'Tech Supply Co.', '03211234567', 'supply@[Link]',
'Lahore');
INSERT INTO Supplier VALUES (2, 'Office Solutions', '03456789012', 'office@[Link]',
'Faisalabad');
INSERT INTO Supplier VALUES (3, 'Pak IT Distributors', '03118889900', 'sales@[Link]',
'Quetta');
INSERT INTO Supplier VALUES (4, 'Galaxy Supplies', '03229997766',
'contact@[Link]', 'Sialkot');
ADMIN TABLE:
INSERT INTO Admin VALUES (1, 'Ayesha Ahmad', 'ayesha_admin', 'admin123', 'Manager');
INSERT INTO Admin VALUES (2, 'Zain Raza', 'zain_admin', 'admin456', 'Supervisor');
INSERT INTO Admin VALUES (3, 'Faizan Malik', 'faizan_admin', 'admin789', 'Data Analyst');
INSERT INTO Admin VALUES (4, 'Mehwish Noor', 'mehwish_admin', 'mehwish321',
'Inventory Manager');
PURCHASE ORDER TABLE:
INSERT INTO PurchaseOrder VALUES (201, 1, DATE '2024-06-01', 2400.00);
INSERT INTO PurchaseOrder VALUES (202, 2, DATE '2024-06-05', 3500.00);
INSERT INTO PurchaseOrder VALUES (203, 3, DATE '2024-06-08', 4000.00);
INSERT INTO PurchaseOrder VALUES (204, 4, DATE '2024-06-10', 7500.00);
INSERT INTO PurchaseOrder VALUES (205, 5, DATE '2024-06-11', 12000.00);
INSERT INTO PurchaseOrder VALUES (206, 6, DATE '2024-06-12', 2000.00);
SUPPLYORDER TABLE:
INSERT INTO SupplyOrder VALUES (301, 1, DATE '2024-05-25', 60000.00);
INSERT INTO SupplyOrder VALUES (302, 2, DATE '2024-06-02', 45000.00);
INSERT INTO SupplyOrder VALUES (303, 3, DATE '2024-06-03', 55000.00);
INSERT INTO SupplyOrder VALUES (304, 4, DATE '2024-06-04', 30000.00);
REPORT TABLE:
INSERT INTO Report VALUES (401, 1, DATE '2024-06-01', 'Purchase', 'Purchase order report
for June 1st');
INSERT INTO Report VALUES (402, 2, DATE '2024-06-05', 'Supply', 'Supply order from
Office Solutions on June 2nd');
INSERT INTO Report VALUES (403, 3, DATE '2024-06-10', 'Purchase', 'Customer purchase
log generated by Faizan');
INSERT INTO Report VALUES (404, 4, DATE '2024-06-12', 'Supply', 'June supply overview
generated by Mehwish');