$\backslash$table$[[$Entity$,$Attributes,Relationships$],[,,1$ to NStudent project that incorporate database design using Entity$-$Relationship Diagrams $($ERD$)$ in

PostgreSQL, report creation using Jaspersoft Studio, and report deployment to JasperReports Server:

Retail Inventory Management:

$$ Database: Develop an ERD for products, suppliers, sales, and inventory levels.

Creating a conceptual diagram for a Retail Inventory Management system involves

illustrating the relationships between different entities: products, suppliers, sales, and

inventory levels. Here's a high$-$level conceptual overview of how these entities might

relate to each other:

$$ Products: Central to the system, products are the items being sold. This entity has a one$-$tomany relationship with sales $($each product can have many sales$)$ and a one$-$to$-$one relationship

with inventory levels $($each product has one inventory level$).$

$$ Suppliers: Suppliers provide products. There is typically a many$-$to$-$many relationship between

products and suppliers because a single supplier can provide multiple products, and a single

product can be supplied by multiple suppliers. This is often managed through a junction table

$($e$.$g$.,$ SupplierProducts$).$

$$ Sales: Sales record the transactions of products. Each sale relates to one product, indicating a

many$-$to$-$one relationship with products.

$$ Inventory Levels: This entity tracks the quantity of each product available. There's a one$-$to$-$one

relationship with products as each inventory record corresponds to a specific product.

Here's how you could represent these entities and their relationships in a conceptual

Entity$-$Relationship Diagram $($ERD$)$:

$1.$ Products Entity:

$$ Represented by a rectangle labeled "Products".

$$ Key attributes could include Product ID$,$ Name, Price, etc.

$2.$ Suppliers Entity:

$$ Represented by a rectangle labeled "Suppliers".

$$ Key attributes could include Supplier ID$,$ Name, Contact Info, etc.

$3.$ Sales Entity:

$$ Represented by a rectangle labeled "Sales".

$$ Key attributes could include Sale ID$,$ Date, Product ID $($foreign key$),$ Quantity Sold, etc.

$4.$ Inventory Levels Entity:

$$ Represented by a rectangle labeled "Inventory Levels".

$$ Key attributes could include Product ID $($foreign key$),$ Quantity on Hand, Reorder Level,

etc.

The relationships would be represented as follows:

$$ Products to Sales: A one$-$to$-$many relationship, because a single product can have multiple sales.

Represented by a line connecting "Products" to "Sales", with a $"1"$ on the product's side and an

$"$N$"$ on the sales' side.

$$ Products to Inventory Levels: A one$-$to$-$one relationship, as each product has one inventory

level record. Represented by a line connecting "Products" to "Inventory Levels" with a $"1"$ on

both sides.

$$ Products to Suppliers: A many$-$to$-$many relationship, usually requiring a junction table to

represent the multiple suppliers for a single product and vice versa. This could be represented

by a rectangle labeled "SupplierProducts" connected to both "Products" and "Suppliers" with

$"$N$"$ on both ends of the lines.

$$ Sales to Inventory Levels: An indirect relationship through the Products entity; when a sale is

made, it affects the inveCreate above structure in the dbdesigner, create script and run it in the pgadmin after

creating a database.

PostgreSQL INSERT scripts to populate data into the tables for Products, Suppliers,

Sales, and Inventory Levels for a Retail Inventory Management system.

$--$ Inserting data into the Products table

INSERT INTO Products $($ProductID$,$ Name, Price$)$ VALUES

$(1,$ 'Laptop', $1200\mathrm{.}00),$

$(2,$ 'Smartphone', $800\mathrm{.}00),$

$(3,$ 'Headphones', $150\mathrm{.}00),$

$(4,$ 'Charger', $25\mathrm{.}00),$

$(5,$ 'Monitor', $300\mathrm{.}00),$

$(6,$ 'Keyboard', $100\mathrm{.}00),$

$(7,$ 'Mouse', $50\mathrm{.}00),$

$(8,$ 'Webcam', $80\mathrm{.}00),$

$(9,$ 'Desk Lamp', $40\mathrm{.}00),$

$(10,$ 'Notebook', $5\mathrm{.}00)$;

$--$ Inserting data into the Suppliers table

INSERT INTO Suppliers $($SupplierID$,$ Name, ContactInfo$)$ VALUES

$(1,$ 'Tech Goods Inc.$\text{'},$ 'info@techgoods.com'$),$

$(2,$ 'Office Supplies Co$.\text{'},$ 'contact@officesuppliesco.com'$),$

$(3,$ 'Electronics Ltd$.\text{'},$ 'support@electronicsltd.com'$),$

$(4,$ 'Gadget Corp.$\text{'},$ 'sales@gadgetcorp.com'$),$

$(5,$ 'Computing Devices', 'hello@computingdevices.com'$)$;

$--$ Assuming a many$-$to$-$many relationship between Products and Suppliers, we'd have a

junction table.

$--$ For simplicity, we'll call this table SupplierProducts and insert data accordingly.

$--$ Inserting data into the SupplierProducts junction table

INSERT INTO SupplierProducts $($SupplierID$,$ ProductID$)$ VALUES

$(1,1),$

$(1,2),$

$(2,10),$

$(3,3),$

$(3,4),$

$(4,5),$

$(4,6),$

$(5,7),$

$(5,8),$

$(5,9)$;

$--$ Inserting data into the Sales table

$--$ Assuming each sale record is a line item of an invoice or receipt

INSERT INTO Sales $($SaleID$,$ Date, ProductID, QuantitySold$)$ VALUES

$(1,\text{'}2023-11-01\text{'},1,2),$

$(2,\text{'}2023-11-01\text{'},2,1),$

$(3,\text{'}2023-11-01\text{'},3,5),$

$(4,\text{'}2023-11-02\text{'},4,10),$

$(5,\text{'}2023-11-02\text{'},5,3),$

$(6,\text{'}2023-11-03\text{'},6,4),$

$(7,\text{'}2023-11-03\text{'},7,7),$

$(8,\text{'}2023-11-03\text{'},8,2),$

$(9,\text{'}2023-11-04\text{'},9,6),$

$(10,\text{'}2023-11-04\text{'},10,20)$;

$--$ Inserting data into the Inventory Levntory level of the product sold.