[go: up one dir, main page]
Scribd
Upload
23 views9 pages

DBS Lab

The document describes an inventory management system including entities like products, locations, warehouses, inventory, providers, orders, deliveries, and transfers. It outlines attributes for each entity and relationships between entities like one-to-many and one-to-one. A database diagram shows entities as tables connected by relationships. The system would help organizations reduce costs by providing up-to-date inventory levels and ensuring prompt deliveries.

Uploaded by

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

DBS Lab

The document describes an inventory management system including entities like products, locations, warehouses, inventory, providers, orders, deliveries, and transfers. It outlines attributes for each entity and relationships between entities like one-to-many and one-to-one. A database diagram shows entities as tables connected by relationships. The system would help organizations reduce costs by providing up-to-date inventory levels and ensuring prompt deliveries.

Uploaded by

badarfatima43
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 9

University of Engineering and Technology Lahore (Narowal

Campus)

Department of Computer science

Submitted by: MUBEEN ISHTIAQ


Submitted To: MAM RABIA ZAFAR AND SIR
MUZAMMIL
➢ PROJECT DESCRIPTION :
The most important feature of any inventory management system is to provide up-to-date
Information about inventory levels. This allows organizations to reduce costs (by minimizing
Overstocking) while maintaining customer satisfaction by ensuring prompt deliveries and
Reducing out of stock situations.There is a brief description of entities involved, their attributes
and relationship between these Entities.

Entities:
An inventory system should have following entities:

❖ Product:
Products are the starting point for designing our system. Attributes for

‘Product’ entity are given as:

• Product ID: This is the unique id number for each product. We will use it as
Primary key in our database.

• Product Code: It is also unique code for each product inside an organization.
Its datatype is VARCHAR.

• Product Name: We use a VARCHAR datatype for it.


• Product Description: It can store additional description for a product. It

Also uses VARCHAR datatype.

• Product Category: Each product has category. It also uses VARCHAR


Datatype.

❖ Location:
An organization may have its branches in different locations. Attributes for

‘location’ are given as:

• Location ID: We use it as a primary key in Location table.


• Location Address: It uses VARCHAR datatype to store the address.
❖ Warehouse:
The organization have its warehouses at different locations.
• Warehouse ID: It will use as a Primary Key in our database.
• Warehouse Name: It use VARCHAR datatype.
❖ Inventory:
We cannot define direct Many-to-Many relationship between two entities i.e.

Product and Warehouse. As a product may be present in different warehouse and a

Warehouse can contain different products. So, we use inventory entity to build Many-

To-Many relationship between these two entities. The attributes for it are given as:

• Inventory ID: It will use as Primary Key.


• Quantity Available: It will store the quantity of each product store in each

Warehouse. Its datatype will be INT.

• Minimum Stock Level: The minimum number of units required to ensure no


Shortages occur at this warehouse.

• Maximum Stock Level: The maximum number of units desired in stock, i.e.
To avoid overstocking.

• Record Point: This must be between Minimum and Maximum stock level to
Minimize the cost and avoid getting under the minimum stock level.

❖ Provider:
The organization will purchase its good from other organizations or

Providers.

• Provider ID: It will use as Primary Key.


• Provider Name: It can be name of organization or person.
• Provider Address: It uses VARCHAR datatype to store the address of

Provider.

❖ Order:
Order indicates the goods that organization purchase from provider.

• Order ID: It will use as Primary Key in this relation.


• Order Date: It will provide date of order. Its datatype will be DATE.
❖ Order Detail:
It provides additional details about order.

• Order Detail ID: It will use as Primary Key in this relation.


• Quantity: It indicates the quantity of ordered goods or products.
• Expected Date: It gives us expected date of receiving order.
• Actual Date: It gives us actual date of receiving order
❖ Customer:
This entity contains the customers that purchases goods or products from

This organization.

• Customer ID: It will use as Primary Key.


• Customer Name: It can be name of buyer.
• Customer Address: It uses VARCHAR datatype to store the address of

Customer.

• Customer Email: It stores the Email of a customer.


❖ Delivery:
Delivery indicates the products that customer purchase from organization.

• Delivery ID: It Warehouse use as Primary Key in this relation.


• Sale Date: It will provide date in which organization sale its product. Its

Datatype will be DATE.

❖ Delivery Detail:
It provides additional details about delivery or sale.

• Delivery Detail ID: It will use as Primary Key in this relation.


• Quantity: It indicates the quantity of sold goods or products.
• Expected Date: This is the date when the products should arrive at the

Customer’s address.

• Actual Date: This is the date when the products were delivered.
❖ Transfer:
Products can be transferred from one warehouse to another warehouse within
Organization. For this purpose, we use ‘Transfer’ entity.

• Transfer ID: It will use as Primary Key in this relation.


• Transfer Quantity: It indicates how much of a specific Product has been

Transferred from one warehouse to another.

• Expected Date: This is the date when the products should arrive at target
Location.

• Actual Date: This is the date when the products were arrived at the target.
➢ Relationships
• Order to ProviderWarehouseA provider can have multiple orders. It is Many-to-One
relationship.
• Order to Order Detail
One order can have only one order-detail. Therefore, it is One-to-One

Relationship.

• Customer to Delivery
A customer can buy multiple products. One delivery can have only one

Customer. There exist One-to-Many relationship.

• Delivery to Delivery Detail


One delivery can have only one delivery-detail. Therefore, it is One-to-One

Relationship.

• Location to Warehouse
Organization can have multiple warehouses at same location, but one

Warehouse cannot have multiple locations. There is One-to-Many relationship.

• Product to Order Detail


This is a One-to-Many relationship.

• Product to Delivery Detail


This is also One-to-Many relationship.

• Warehouse to Order Detail


One warehouse can order many products. There is One-to-Many

Relationship.

• Warehouse to Delivery Detail


One warehouse can have multiple sales. There is One-to-Many

Relationship.

• Product to Inventory
One product can have multiple inventories at multiple warehouses. There

Is One-to-Many relationship between these entities.

• Warehouse to inventory
One warehouse can have multiple inventories. It is One-to-Many

Relationship.

• Product to Transfer
One product can only be transfer from one warehouse to other at a time.

Therefore, it has One-to-Many relationship.

• Warehouse to Transfer
One warehouse can transfer multiple products. There exist One-to-Many

Relationship between them.


➢ DATABASE DIAGRAM :
ERD:

You might also like