SUPERMARKET INVENTORY

MANAGEMENT

Technical report
 Contents

Technical report.............................................................................................................. 0
Content............................................................................................................................ 1
INTRODUCTION............................................................................................................... 2
Conceptual Data Model (CDM) and Logical Data Model (LDM).....................................3
1. Conceptual Data Model (CDM)....................................................................................... 3
Relationships and Cardinalities................................................................................... 4
Relationship Interpretation......................................................................................... 4
2. Logical Data Model (LDM)..............................................................................................5
Database schema.............................................................................................................6
1. Database creation...........................................................................................................6
2. Tables creation............................................................................................................... 6
Table documentation...................................................................................................... 9
1. Category Table................................................................................................................ 9
a. Fields................................................................................................................... 9
b. Constraints.......................................................................................................... 9
2. Supplier Table.................................................................................................................9
a. Fields................................................................................................................... 9
b. Constraints........................................................................................................ 10
3. Product Table................................................................................................................10
a. Fields................................................................................................................. 10
b. Constraints........................................................................................................ 10
4. Order Table................................................................................................................... 11
a. Fields................................................................................................................. 11
b. Constraints........................................................................................................ 11

1
 INTRODUCTION

In today's fast-paced, data-driven business landscape, the efficiency and

accuracy of information management have become
paramount.Supermarkets,as hubs of consumer activity,no exceptions.A
well-structured database is no longer a luxury aim and a necessity for these
retail giants to thrive. This report delves into the intricacies of constructing a
robust database for a supermarket, emphasizing the pivotal role it plays in
streamlining operations, decision-making and ultimately driving business
success.
This technical report will explore the foundational steps involved in database
creation, from conceptualizing the entity-relationship model to executing
complex SQL queries. By understanding the intricacies of this process,we
love to illuminate the transformative power of a well-designed database in
revolutionizing supermarket management.

2
 Conceptual Data Model (CDM) and Logical
Data Model (LDM)

1. Conceptual Data Model (CDM)

This image shows an Entity-Relationship Diagram (ERD) that illustrates

the relationships between different entities and their cardinalities in a
database. Here is a clear explanation of the relationships and their
cardinalities:

3
Entities and Attributes

● Category: Represents different product categories.

○ Attributes: Category ID (primary key), Category Name
● Product: Represents individual products.
○ Attributes: Product ID (primary key), Name, Description, Price,
Stock
● Supplier: Represents suppliers of products.
○ Attributes: Supplier ID (primary key), Name, Contact, Address,
Telephone
● Orders: Represents customer orders.
○ Attributes: Order ID (primary key), Quantity, Order Date

Relationships and Cardinalities

● Category - Product (1:n): One category can have many products, but a
product can only belong to one category. This is a one-to-many
relationship.
● Product - Supplier (1:n): One product can be supplied by one supplier,
but a supplier can supply many products. This is also a one-to-many
relationship.
● Product - Orders (1:n): One product can be part of many orders, but
an order can contain many products. This is another one-to-many
relationship.

Relationship Interpretation

● Category and Product: Each product is classified under a single

category. For example, "Electronics" could be a category, and
"Smartphone" and "Laptop" would be products within that category.

4
 ● Product and Supplier: one supplier can provide several products.
● Product and Orders: A product can appear in many orders as
customers purchase it multiple times.

These relationships and cardinalities help in structuring the data to

reflect the real-world interactions between categories, products, suppliers,
and orders in a database management system.

2. Logical Data Model (LDM)

A logical data model represents the structure of the data elements and
the relationships between them without focusing on how they will be
physically implemented in the database. It includes entities, attributes, and
relationships, often depicted in an Entity-Relationship Diagram (ERD). Based
on the previous schema, here's a logical data model:

Product ( Product_ID, Name, Description, Price, Stock, # Supplier_ID,

#Category_ID )

Supplier ( Supplier_ID, Name, Contact, Address, Telephone )

Orders ( Order_ID, Quantity, Order_date, #Product_ID )

Category ( Category_ID, Category_name )

5
 Database schema

SQL scripts for database and table creation

1. Database creation
CREATE DATABASE `Supermarche`;

2. Tables creation

● Product

CREATE TABLE Products (

product_id INT PRIMARY KEY AUTO_INCREMENT,

`name` VARCHAR(255) NOT NULL,

`description` TEXT,

price DECIMAL(10,2) NOT NULL,

stock VARCHAR(255) NOT NULL DEFAULT 0,

foreign key (category_id) references Categories (category_id),

foreign key (supplier_id) references Suppliers (supplier_id),

supplier_id INT NOT NULL,

category_id int not null );

6
 ● Supplier

CREATE TABLE Suppliers (

supplier_id INT PRIMARY KEY AUTO_INCREMENT,

`name` VARCHAR(255) NOT NULL,

contact VARCHAR(255),

address TEXT,

telephone VARCHAR(20)

);

● Orders

CREATE TABLE Orders (

order_id INT PRIMARY KEY AUTO_INCREMENT,

quantity INT NOT NULL,

order_date DATE NOT NULL,

product_id INT NOT NULL,

foreign key (product_id) references Products (product_id)

);

7
 ● Category

CREATE TABLE Categories (

category_id INT PRIMARY KEY AUTO_INCREMENT,

`name` VARCHAR(255) NOT NULL

);

8
 Table documentation

Description of fields and constraints for each table.

1. Category Table

a. Fields
- category_id: Integer, Primary Key, Auto Increment, Unique
identifier for each category.
- name: VARCHAR(255), Not Null, The name of the category.

b. Constraints
- PRIMARY KEY (category_id): Ensures that each category has a
unique identifier.

2. Supplier Table

a. Fields
- supplier_id: Integer, Primary Key, Auto Increment, Unique
identifier for each supplier.
- name: VARCHAR(255), Not Null, The name of the supplier.
- contact: VARCHAR(255), Contact information for the supplier.
- address: TEXT, Address of the supplier.
- telephone: VARCHAR(20), Telephone number of the supplier.

9
 b. Constraints

- PRIMARY KEY (supplier_id): Ensures that each supplier has a unique

identifier.

3. Product Table
a. Fields
- product_id: Integer, Primary Key, Auto Increment, Unique
identifier for each product.
- name: VARCHAR(255), Not Null, The name of the product.
- description: TEXT, Description of the product.
- price: DECIMAL(10,2), Not Null, Price of the product.
- stock: INT, Not Null, Default 0, Quantity of the product in stock.
- supplier_id: Integer, Not Null, Foreign Key referencing
`Suppliers.supplier_id`.
- category_id: Integer, Not Null, Foreign Key referencing
`Categories.category_id`.

b. Constraints
- PRIMARY KEY (product_id): Ensures that each product has a
unique identifier.
- FOREIGN KEY (category_id) REFERENCES Categories
(category_id)`: Ensures that each product is associated with a
valid category.
- FOREIGN KEY (supplier_id) REFERENCES Suppliers (supplier_id)`:
Ensures that each product is associated with a valid supplier.

10
 4. Order Table
a. Fields
- order_id: Integer, Primary Key, Auto Increment, Unique identifier
for each order.
- quantity: Integer, Not Null, Quantity of the product ordered.
- order_date: DATE, Not Null, Date of the order.
- product_id: Integer, Not Null, Foreign Key referencing
`Products.product_id`.

b. Constraints
- PRIMARY KEY (order_id): Ensures that each order has a unique
identifier.
- FOREIGN KEY (product_id) REFERENCES Products (product_id):
Ensures that each order is associated with a valid product.

This documentation provides a clear description of the fields and

constraints for each table, ensuring that the data integrity and relationships
between entities are maintained.

11