DBMS PBL

On

Inventory Management System

Submitted by:- Submitted To:-

Avinash Tiwari Mr. Prince Gupta
2100290110041 Assistance Prof.
A-2 Batch, CSIT 5A CSIT Dept. KIET
 INDEX

1)Statement about the Problem

2)Objective

3)Scope

4)Methodology

5)ER Diagram

6)Relational Model

7)Queries and Output

8)References
 Statement about the Problem:-

This application is nowadays a basic use of any company, firm, shop or departmental store
because stock maintenance, stock forecasting are some things which are very essential these
days for earning great profits. In ancient times we need to maintain the complete inventory in
paper pen method. The ancient method is quite un-easy, uncomfortable and some times
inaccurate. For overcoming this problem we came with a solution of inventory management
system. From this system we can generate invoice for each and every purchase. In addition to
this we can have the employee details, customer details in this system. In short we can call
this as all in one system.
Goals of proposed system

1. Planned approach towards working: - The working in the organization will be well
planned and organized. The data will be stored properly in data stores, which will help in
retrieval of information as well as its storage.

2. Accuracy: - The level of accuracy in the proposed system will be higher. All operation
would be done correctly and it ensures that whatever information is coming from the center is
accurate.

3. Reliability: - The reliability of the proposed system will be high due to the above stated
reasons. The reason for the increased reliability of the system is that now there would be proper
storage of
information.

4. No Redundancy: - In the proposed system utmost care would be that no information is

repeated anywhere, in storage or otherwise. This would assure economic use of storage space
and consistency in the data stored.

5. Immediate retrieval of information: - The main objective of proposed system is to

provide for a quick and efficient retrieval of information.

6. Immediate storage of information: - In manual system there are many problems to

store the largest amount of information.

7. Easy to Operate: - The system should be easy to operate and should be such that it can
be developed within a short period of time and fit in the limited budget of the user.
 Objective:-

1. Inventory Tracking:
• Implement a system to track the quantity, location, and status of each product
in the inventory.
• Record product details, including name, description, category, cost, selling
price, and supplier information.
2. Order Management:
• Manage purchase orders and track incoming shipments.

• Update inventory levels based on received shipments.

3. Sales Tracking:
• Record sales transactions, including customer information, product sold,
quantity, and total sale amount.
• Update inventory levels based on sales.
4. User Authentication and Authorization:
• Implement a user authentication system to control access to the IMS.
• Assign roles (e.g., admin, manager, salesperson) with specific permissions.
5. Reporting and Analysis:
• Generate reports on inventory levels, sales, and product performance.
• Implement basic analytics to identify fast-moving and slow-moving products.
6. Alerts and Notifications:
• Set up alerts for low stock levels to avoid stockouts.
• Notify users about critical inventory updates, such as new stock arrivals or low
quantities.
7. Data Integrity and Validation:
• Ensure data integrity through proper validation mechanisms.
• Implement constraints to prevent data inconsistencies.

Scope:-
• Product Management:

• Add, update, and delete product information.

 •
Track product details such as name, description, category, cost, selling price,
and supplier information.

• Inventory Tracking:

• Monitor real-time inventory levels.

• Record product movements, including receipts, sales, and adjustments.

• Order Management:

• Create and manage purchase orders.

• Update inventory based on received shipments.

• Sales Tracking:

• Record sales transactions.

• Automatically update inventory levels after each sale.

• User Authentication and Authorization:

• Secure login mechanism for users.

• Assign roles (admin, manager, salesperson) with specific permissions.

• Reporting and Analysis:

• Generate reports on inventory levels, sales, and product performance.

• Include analytics for fast-moving and slow-moving products.

• Alerts and Notifications:

• Set up alerts for low stock levels.

• Notify users of critical inventory updates.

Methodology:-
1. Define Requirements:
• Conduct meetings with key stakeholders to understand their requirements.
• Identify user roles, system functionalities, and performance expectations.
• Create a detailed requirements document outlining both functional and
nonfunctional requirements.
 •
2. System Design:
• Database Schema Design:
Develop an Entity-Relationship Diagram (ERD) to represent the database
schema.
• Normalize the database to eliminate redundancy and ensure data integrity.
• User Interface Design:
• Design the user interface considering user experience and ease of use.
• Create wireframes or mockups to visualize the interface.
3. Technology Selection:
• Choose DBMS:
• Select a suitable DBMS based on project requirements (e.g., MySQL,
PostgreSQL, MongoDB).
• Consider factors such as scalability, performance, and ease of integration.
• Select Programming Language:
• Choose a programming language compatible with the selected DBMS (e.g.,
Python, Java).
4. Development:
• Database Implementation:
• Create the database schema based on the finalized ERD.
• Write SQL scripts to create tables, relationships, and constraints.
• Backend Development:
• Implement the backend logic for handling business processes, transactions,
and database interactions.
• Frontend Development:
• Develop the user interface using the selected programming language and
framework.
• Implement data entry forms, reports, and dashboards.
5. Testing:
• Unit Testing:
• Test individual components of the system to ensure they work as intended.
• Verify database queries and transactions.
• Integration Testing:
• Test the interaction between different system components.
• Validate data flow between the frontend and backend.
• User Acceptance Testing (UAT):
 •
• Involve end-users in testing to ensure the system meets their expectations.
• Gather feedback for any necessary adjustments.
6. Deployment:
• Database Deployment:
Deploy the database schema to the production environment.
• Ensure data migration from any existing system.
• Application Deployment:
• Deploy the application to the production server.
• Configure security settings and ensure system stability.
7. Training and Documentation:
• User Training:
• Provide training sessions for end-users to familiarize them with the IMS.
• Address any questions or concerns.
• Documentation:
• Create user manuals and technical documentation for system maintenance.
• Document any customization or configuration instructions.

ER
Diagram:-
 Relational Model :-

Inter-relation between different relations:

 Queries and Output

CREATE TABLE :

SQL> create table brands(

2 bid number(5),

3 bname varchar(20)

4 );

Table created.

SQL> alter table brands 2 add primary key(bid); Table altered.

SQL> create table inv_user(

2 user_id varchar(20),

3 name varchar(20),

4 password varchar(20),

5 last_login timestamp,

6 user_type varchar(10)

7 );

Table created.

SQL> create table categories(

2 cid number(5),
 3 category_name varchar(20)

4 );

Table created.

SQL> alter table categories 2 add primary key(cid); Table altered.

SQL> alter table inv_user 2 add

primary key(user_id);

Table altered.

SQL> create table product(

2 pid number(5) primary key,

3 cid number(5) references categories(cid),

4 bid number(5) references brands(bid),

5 sid number(5),

6 pname varchar(20),

7 p_stock number(5),

8 price number(5),

9 added_date date);

Table created.

SQL> create table stores(

2 sid number(5),

3 sname varchar(20),

4 address varchar(20),
 5 mobno number(10)

6 );

Table created.

SQL> alter table stores

2 add primary key(sid);

Table altered.

SQL> alter table product

2 add foreign key(sid)references stores(sid);

Table altered.

SQL> create table provides(

2 bid number(5)references brands(bid),

3 sid number(5)references stores(sid),

4 discount number(5));

Table created.

SQL> create table customer_cart(

2 cust_id number(5) primary key,

3 name varchar(20),

4 mobno number(10)

5 );
Table created.

SQL> create table select_product(

 2 cust_id number(5) references customer_cart(cust_id),

3 pid number(5)references product(pid),

4 quantity number(4)

5 );

Table created.

SQL> create table transaction(

2 id number(5) primary key,

3 total_amount number(5),

4 paid number(5),

5 due number(5),

6 gst number(3),

7 discount number(5),

8 payment_method varchar(10),

9 cart_id number(5) references customer_cart(cust_id)

10 );

Table created.

SQL> create table invoice(

2 item_no number(5),

3 product_name varchar(20),

4 quantity number(5),

5 net_price number(5),
 6 transaction_id number(5)references transaction(id)

7 );

INSERTION:

INSERT INTO BRANDS:

SQL> insert into brands values(

2 '&bid'

3 ,

4 '&bname');
Enter value for bid: 1 old 2: '&bid' new 2: '1' Enter value

for bname: Apple old 4: '&bname') new 4:

'Apple')

1 row created.

1 row created.

SQL> insert into brands values(2,'Samsung');

1 row created.

SQL> insert into brands values(3,'Nike');

1 row created.

SQL> insert into brands values(4,'Fortune');

1 row created.
INSERT INTO INV_USER:

SQL> insert into inv_user values(

2 '&user_id',

3 '&name',

4 '&password', 5 '&last_login',
6 '&user_type');
Enter value for user_id: vidit@gmail.com old 2: '&user_id', new 2: 'vidit@gmail.com', Enter value for
name: vidit old 3: '&name', new 3: 'vidit',

Enter value for password: 1234 old 4: '&password', new 4: '1234',

Enter value for last_login: 31-oct-18 12:40 old 5: '&last_login', new 5: '31-oct-18 12:40', Enter value
for user_type: admin old 6: '&user_type') new 6: 'admin')

1 row created.

SQL> insert into inv_user values('harsh@gmail.com','Harsh Khanelwal','1111','30-oct18

10:20','Manager');

1 row created.

SQL> insert into inv_user values('prashant@gmail.com','Prashant','0011','29-oct-18

10:20','Accountant');

1 row created.

INSERT INTO CATEGORIES:

SQL> insert into categories values(

2 '&cid',

3 '&category_name'); Enter value for cid: 1 old 2: '&cid',

new 2: '1',

Enter value for category_name: Electroincs old 3: '&category_name') new 3: 'Electroincs')

1 row created.

SQL> insert into categories values(2,'Clothing');

1 row created.

SQL> insert into categories values(3,'Grocey');

1 row created.
INSERT INTO STORE

SQL> insert into stores values(

2 '&sid',

3 '&sname',

4 '&address',

5 '&mobno');
Enter value for sid: 1 old 2: '&sid', new
2: '1',

Enter value for sname: Ram kumar old 3: '&sname', new 3: 'Ram kumar', Enter value for address:

Katpadi vellore old 4: '&address', new 4: 'Katpadi vellore', Enter value for mobno: 9999999999

old 5: '&mobno') new 5: '9999999999')

1 row created.

SQL> insert into stores values(2,'Rakesh kumar','chennai',8888555541);

1 row created.
SQL> insert into stores values(3,'Suraj','Haryana',7777555541);

1 row created.

INSERT INTO PRODUCT:

SQL> insert into product values(

2 '&pid',

3 '&cid',

4 '&bid',

5 '&sid',

6 '&pname',

7 '&p_stock', 8 '&price',

9 '&added_date'); Enter value for pid: 1 old 2: '&pid', new 2: '1', Enter value for cid: 1 old 3:
'&cid', new 3: '1', Enter value for bid: 1 old 4: '&bid', new 4: '1', Enter value for sid: 1 old 5: '&sid',
new 5: '1',

Enter value for pname: IPHONE old 6: '&pname', new 6: 'IPHONE', Enter value for p_stock:

4 old 7: '&p_stock', new 7: '4', Enter value for price: 45000 old 8: '&price', new

8: '45000',
Enter value for added_date: 31-oct-18 old 9: '&added_date') new 9:

'31-oct-18')

1 row created.

SQL> insert into product values(2,1,1,1,'Airpods',3,19000,'27-oct-

18'); 1 row created.

SQL> insert into product values(3,1,1,1,'Smart Watch',3,19000,'27-oct-18');

 1 row created.

SQL> insert into product values(4,2,3,2,'Air Max',6,7000,'27-oct-18');

1 row created.

SQL> insert into product values(5,3,4,3,'REFINED OIL',6,750,'25-oct-18');

1 row created.

INSERT INTO PROVIDES:

SQL> insert into provides values(1,1,12);

1 row created.

SQL> insert into provides values(2,2,7);

1 row created.

SQL> insert into provides values(3,3,15);

1 row created.

SQL> insert into provides values(1,2,7);

1 row created.

SQL> insert into provides values(4,2,19);

1 row created.

SQL> insert into provides values(4,3,20);

 1 row created.

INSERT INTO CUSTOMER_CART:

SQL> insert into customer_cart values(

2 '&cust_id',

3 '&name',

4 '&mobno');
Enter value for cust_id: 1 old 2: '&cust_id', new 2: '1', Enter value for name: Ram old 3: '&name', new
3: 'Ram',

Enter value for mobno: 9876543210 old 4: '&mobno') new 4: '9876543210')

1 row created.

SQL> insert into customer_cart values(2,'Shyam',7777777777);

1 row created.

SQL> insert into customer_cart values(3,'Mohan',7777777775);

1 row created.

INSERT INTO SELECT_PRODUCT:

SQL> insert into select_product values(

2 '&cust_id',

3 '&pid',

4 '&quantity'); Enter value for cust_id: 1 old 2: '&cust_id', new 2: '1', Enter value for pid: 2 old 3:
'&pid', new 3: '2',

Enter value for quantity: 2 old 4: '&quantity') new 4: '2')

1 row created.

SQL> insert into select_product values(1,3,1);

1 row created.

SQL> insert into select_product values(2,3,3);

1 row created.

SQL> insert into select_product values(3,2,1);

1 row created.

INSERT INTO TRANSACTIONS:

SQL> insert into transaction values(

2 '&id',

3 '&total_amount',

4 '&paid',

5 '&due',

6 '&gst',

7 '&discount',

8 '&payment_method',

9 '&cart_id'); Enter value for id: 1 old 2: '&id', new 2: '1',

Enter value for total_amount: 57000 old 3: '&total_amount', new 3: '25000', Enter value for paid: 2000
old 4: '&paid', new 4: '20000', Enter value for due: 5000 old 5: '&due', new 5: '5000', Enter value for
gst: 350 old 6: '&gst', new 6: '350', Enter value for discount: 350 old 7: '&discount', new 7:
'350',
Enter value for payment_method: card old 8: '&payment_method', new 8: 'card', Enter value for
cart_id: 1 old 9: '&cart_id') new 9: '1')
1 row created.

insert into transaction values(2,57000,57000,0,570,570,'cash',2);

SQL> insert into transaction values(3,19000,17000,2000,190,190,'cash',3);

1 row created. SQL> insert into transaction values(3,19000,17000,2000,190,190,'cash',3);

1 row created.

PL/SQL

Functions:

SQL> declare

2 due1 number(7);

3 cart_id1 number(7);

4 function get_cart(c_id number)return number is

5 begin

6 return (c_id);

7 end;

8 begin

9 cart_id1:=get_cart('&c_id');

10 select due into due1 from transaction where cart_id=cart_id1;

11 dbms_output.put_line(due1);

12 end;

13 /
Enter value for c_id:

1 old 9: cart_id1:=get_cart('&c_id'); new 9: cart_id1:=get_cart('1'); 5000

PL/SQL procedure successfully completed.

Cursors:

SQL> DECLARE

2 p_id product.pid%type;

3 p_name product.pname%type;

4 p_stock product.p_stock%type;

5 cursor p_product is

6 select pid,pname ,p_stock from product;

7 begin

8 open p_product;

9 loop

10 fetch p_product into p_id,p_name,p_stock;

11 exit when p_product%notfound;

12 dbms_output.put_line(p_id||' '||p_name||' '||p_stock);

13 end loop;

14 close p_product;

15 end;

16 /

1 IPHONE 4

2 Airpods 3

3 Smart Watch 3

4 Air Max 6
 5 REFINED OIL 6

PL/SQL procedure successfully completed.

Procedure:

SQL> DECLARE

2 a number; 3 b number;

4 PROCEDURE check_stock(x IN number) IS

5 BEGIN

6 IF x < 2 THEN

7 dbms_output.put_line('Stock is Less');

8 ELSE

9 dbms_output.put_line('Enough

Stock'); 10 END IF;

11 END;

12 BEGIN

13 b:='&b';

14 select p_stock into a from product where pid=b;

15 check_stock(a); 16 END;

17 /

Enter value for b: 2 old 13: b:='&b'; new 13: b:='2'; Enough Stock

PL/SQL procedure successfully completed.

 References
1. Levin, Roger. "Inventory management." Journal of the American Dental Association 135, no. 6
(June 2004): 786–87. http://dx.doi.org/10.14219/jada.archive.2004.0278.

2. Sánchez-Lozano, Gloria, and Esther Chávez-Miranda. "“Tratamiento de capacity management

inventory management en manuales de revenue management”." International Journal of World
of Tourism 2, no. 4 (2015): 9–16. http://dx.doi.org/10.12795/ijwt.2015.i04.02.

3. Van Belle, D. "INVENTORY MANAGEMENT©." Acta Horticulturae, no. 1085 (June 2015): 333–34
. http://dx.doi.org/10.17660/actahortic.2015.1085.61.

4. Agrahari, Amit, and Saket Jhunjhunwala. "Inventory Management Process." Journa of Cases on
Information Technology 14, no. 1 (January 2012): 1–1.
http://dx.doi.org/10.4018/jcit.2012010101.

5. Caron, Franco. "Integrated Inventory Management." International Journal of Project Management

1, no. 8 (November 2001): 485–86. http://dx.doi.org/10.1016/s02637863(00)00049-1.