Scribd
Upload
31 views5 pages

Database Design for Inventory System

The document outlines a project for ISTE 230 focused on designing an inventory management system for a local electronics store. It includes a business scenario, core requirements, entity identification, E-R diagram, relational schema, SQL implementation, and sample queries. The project aims to efficiently manage products, sales, customers, and employees while ensuring proper data normalization and relational database design.

Uploaded by

James Maina
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
31 views5 pages

Database Design for Inventory System

The document outlines a project for ISTE 230 focused on designing an inventory management system for a local electronics store. It includes a business scenario, core requirements, entity identification, E-R diagram, relational schema, SQL implementation, and sample queries. The project aims to efficiently manage products, sales, customers, and employees while ensuring proper data normalization and relational database design.

Uploaded by

James Maina
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

ISTE 230 – Introduction to Database and Data Modeling

Spring 2025

Project :
Submission:
ISTE 230 Database Project
Phase I: Conceptual and Logical Design (15 Points)
1. Business Scenario (5 Points)

Scenario Description (3 Points)

The aim of this project is to design an inventory management system for a local store. The store
sells various electronics devices and accessories not limited to mobile phones, laptops and home
appliance. Database will keep track o products, sales, customers, and employees. The main goal
is to ensure efficient inventory tracking, sales processing, and customer management.

Core Business/User Requirements (2 Points)

The system should allow users to:

1. Add new products to inventory with details like name, brand, category, price, and stock
level.
2. Process customer sales, tracking each purchase with details like date, items bought,
quantity, and total cost.
3. Manage customer information, storing contact details and purchase history.
4. Generate sales reports to review revenue and track best-selling products.

2. Identifying Entities and Relationships (2 Points)

Entities and Attributes (1 Point)

 Product (ProductID [PK], Name, Brand, Category, Price, StockLevel)


 Customer (CustomerID [PK], Name, Phone, Email, Address)
 Sale (SaleID [PK], CustomerID [FK], SaleDate, TotalAmount)
 SaleItem (SaleItemID [PK], SaleID [FK], ProductID [FK], Quantity, SubTotal)
 Employee (EmployeeID [PK], Name, Role, Salary, Contact)

Relationships (1 Point)

 A Customer can make multiple Sales (1:M relationship).


 A Sale consists of multiple SaleItems (1:M relationship).
 A SaleItem is associated with a single Product (M:1 relationship).
 An Employee can handle multiple Sales (1:M relationship).
3. E-R Diagram (3 Points)

4. Transposing E-R Diagram into Relations (5 Points)

Relational Schema

Product(ProductID, Name, Brand, Category, Price, StockLevel)


Customer(CustomerID, Name, Phone, Email, Address)
Sale(SaleID, CustomerID, EmployeeID, SaleDate, TotalAmount)
SaleItem(SaleItemID, SaleID, ProductID, Quantity, SubTotal)
Employee(EmployeeID, Name, Role, Salary, Contact)

Normalization

 First Normal Form (1NF): No repeating groups or multivalued attributes.


 Second Normal Form (2NF): Partial dependencies removed.
 Third Normal Form (3NF): Transitive dependencies removed.
Example: SaleItem table before normalization:

(SaleID, ProductID, ProductName, Price, Quantity, SubTotal)

After normalization:

SaleItem(SaleItemID, SaleID, ProductID, Quantity, SubTotal)


Product(ProductID, Name, Brand, Category, Price, StockLevel)

Phase II: Physical Design (10 Points)


1. SQL Implementation in MariaDB (5 Points)

Create Tables

CREATE TABLE Product (


ProductID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100) NOT NULL,
Brand VARCHAR(50) NOT NULL,
Category VARCHAR(50),
Price DECIMAL(10,2) NOT NULL,
StockLevel INT NOT NULL
);

CREATE TABLE Customer (


CustomerID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100) NOT NULL,
Phone VARCHAR(15) UNIQUE,
Email VARCHAR(100) UNIQUE,
Address TEXT
);

CREATE TABLE Employee (


EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100) NOT NULL,
Role VARCHAR(50),
Salary DECIMAL(10,2),
Contact VARCHAR(15) UNIQUE
);

CREATE TABLE Sale (


SaleID INT PRIMARY KEY AUTO_INCREMENT,
CustomerID INT,
EmployeeID INT,
SaleDate DATE NOT NULL,
TotalAmount DECIMAL(10,2) NOT NULL,
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
);
CREATE TABLE SaleItem (
SaleItemID INT PRIMARY KEY AUTO_INCREMENT,
SaleID INT,
ProductID INT,
Quantity INT NOT NULL,
SubTotals DECIMAL(10,2) NOT NULL,
FOREIGN KEY (SaleID) REFERENCES Sale(SaleID),
FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

2. Relational Algebra Expressions (2 Points)

Retrieve all customer who made a purchase

Select CustomerID,Name from customer union select customerID,Name from customer where
customerID IN (SELECT FROM SALE)

Retrieve all products sold in a specific category along with their sale details

Select [Link],[Link],[Link],[Link] FROM Product p JOIN SaleItem s ON [Link] =


[Link] WHERE [Link] = ‘Electronics’;

3. SQL Queries (3 Points)

1. Retrieve customer purchase history

SELECT [Link], [Link], [Link], [Link]


FROM Customer
JOIN Sale ON [Link] = [Link]
WHERE [Link] = 1001;

2. Find products that are out of stock

SELECT * FROM Product WHERE StockLevel = 0;

3. Update stock after a sale

UPDATE Product
SET StockLevel = StockLevel - (SELECT Quantity FROM SaleItem WHERE
[Link] = [Link])
WHERE ProductID = 1;

Common questions

Powered by AI

Without adequate indexing, tables with high transaction volumes like Sale and SaleItem may suffer from slow query performance, as the database system has to perform full table scans instead of using indices to quickly locate data. This can lead to inefficiencies in transaction processing, higher system resource usage, and delays in generating reports or executing real-time queries, which are critical for decision-making in an inventory management system [hypothetical based on Source 1 and Source 2 context].

The project ensures normalization up to 3NF by eliminating repeating groups or multivalued attributes for 1NF, removing partial dependencies for 2NF, and eliminating transitive dependencies for 3NF. For example, the SaleItem table initially included fields like ProductName and Quantity together, which after normalization resulted in the separation into distinct tables: SaleItem and Product, reducing redundancy and dependency issues .

The SQL command to create the Sale table involves defining SaleID as the primary key and establishing foreign key constraints to reference CustomerID in the Customer table and EmployeeID in the Employee table. The command looks like: CREATE TABLE Sale ( SaleID INT PRIMARY KEY AUTO_INCREMENT, CustomerID INT, EmployeeID INT, SaleDate DATE NOT NULL, TotalAmount DECIMAL(10,2) NOT NULL, FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID), FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID) ).

Foreign key constraints in the database ensure that relationships between tables, such as between Sale and Customer, or SaleItem and Sale, are respected, maintaining the integrity of references across tables. They prevent operations that could leave orphaned records or break data links, thereby safeguarding the logical interconnections essential for accurate data representation and retrieval in the system designed in the ISTE 230 project .

The database structure facilitates efficient retrieval of a customer's purchase history by leveraging normalized tables with proper indexing, foreign key relationships between Customer and Sale tables, and SQL joins to collate related records efficiently. Queries such as joining the Customer table with the Sale table on CustomerID allow direct retrieval of purchase details like SaleDate and TotalAmount, streamlined by the structured schema .

Relational algebra provides a set of operations that allow for precise and systematic query formulations, enabling the combination and transformation of data across multiple tables. This fosters efficient data retrieval strategies, such as ensuring only relevant data is queried and combined, which enhances performance and clarity. It is especially useful in queries that require complex joins and data interactions, such as retrieving all products sold in a specific category along with sale details .

The system's update function deducts sold quantities from the current stock levels in the Product table. The SQL command for this involves calculating the reduced stock by subtracting quantities from related rows in the SaleItem table, thus updating the inventory record accurately and ensuring that stock levels reflect real-time sales figures. The command used is: UPDATE Product SET StockLevel = StockLevel - (SELECT Quantity FROM SaleItem WHERE Product.ProductID = SaleItem.ProductID).

The main data entities in the inventory management system are Product, Customer, Sale, SaleItem, and Employee. Their primary relationships include: a Customer can make multiple Sales (1:M relationship), a Sale consists of multiple SaleItems (1:M relationship), a SaleItem is associated with a single Product (M:1 relationship), and an Employee can handle multiple Sales (1:M relationship).

The project addresses data integrity by implementing foreign key constraints that enforce referential integrity across related tables, linking sales data to specific customers and employees. It eliminates redundancy through normalization up to 3NF, where partial and transitive dependencies are removed, thereby minimizing data duplication and ensuring that each piece of information is stored in one place .

Ensuring attributes like price, stock level, and category are correctly defined in the Product table is crucial for maintaining data accuracy and operational functionality. Using constraints such as NOT NULL ensures that these critical attributes are always provided, and using appropriate data types (e.g., DECIMAL for price, INT for stock level) prevents data type errors. Additionally, defining these attributes properly helps in efficient data retrieval, accurate reporting, and smooth integration with other system modules .

You might also like