AC51049 - Database Systems Development

Coursework 1 - Cover Sheet

TEAM NUMBER: 7

COMPANY NAME: AdventureFits

COMPANY SLOGAN: "Equipping Your Next Great Adventure"

TEAM MEMBERS: Oluwafemi Olaoluwa Awodiya (2539388)

Eunice Imeh John (2531234)
Maftahul Islam Drobo (2548364)
Abdulfatai Olalere Lawal (2546213)
Wissal Hakkou (2535216)
Suzana Hridi (2596655)
Boumote Digitemie (2546779)
Oyedotun Oyewole (2531196)
Guankun Ren 2543345

 Company description / Specification report

Word count (900-1000) …..… Number of pages …….

 E-R diagram Number of pages …….

 SQL CREATE statements Number of pages …….

--------

Total pages …….

 We confirm that the team members have read and understood the
University policy on Academic Misconduct

You will also need to complete a personal peer review at

peerreview.computing.dundee.ac.uk
(standard Computing login required)
 AdventureFits
"Equipping Your Next Great Adventure"

Introduction
AdventureFits is a fast-growing SME specializing in outdoor adventure gear and experiences that
originated as a small business but has now expanded its reach across the United Kingdom. Our
comprehensive range of outdoor products encompasses hiking and camping gear, cycling accessories,
making AdventureFits the go-to destination for adventure enthusiasts of all ages and backgrounds. With
our company's remarkable growth, we have had the challenge of efficiently managing various aspects of
our operations, from sales and inventory to supplier relationships and customer engagement. In
response to these challenges, we are ushering in a new era of efficiency and effectiveness by introducing
a robust database system.

This report serves as an exploration of the database design decisions we have made for AdventureFits.
Our design choices have been deeply influenced by the unique structure and operations of our company.
We place a strong emphasis on catering to the diverse needs of various user categories, ensuring that
our database aligns perfectly with our operational requirements. These key user categories encompass
managers, shop-floor staff, office personnel, suppliers, and our valued customers. Each of these
categories plays a pivotal role in the context of AdventureFits' dynamic and thriving business activities.

Company Description and Structure

AdventureFits operates with a decentralized structure, comprising multiple physical branches and a
central office. The key components of our company include:

Physical Branches: AdventureFits proudly maintains a network of 30 strategically located branches

across the UK with dedicated staff members responsible for assisting customers, managing inventory,
and overseeing daily operations. These branches serve as our retail outlets, offering customers a hands-
on shopping experience.

3 strategic Warehouses: At the core of AdventureFits' operations stands our 3 warehouse facilities that
service all the requests of the sales branches. This facility receives parts and raw materials from various
suppliers, and distributes them to all sales branches based on Request Orders.

Product Range: AdventureFits boasts an extensive product range encompassing hiking and camping gear
and cycling accessories. We take pride in featuring six distinct drone models, each tailored for specific
outdoor pursuits.

Expansion Plans: AdventureFits is poised for ambitious expansion into the European market. This
endeavor necessitates the optimization and scaling of our operations.
Database Implementation Objectives
At AdventureFits, we acknowledge the need to modernize our record-keeping practices and harmonize
various facets of our business through a unified database system. Our foremost database
implementation objectives are as follows:

Streamlined Operations: Our database will serve as a centralized hub, enabling the efficient
management of all business-related data. It will provide comprehensive oversight of our company's
operations, including inventory management, sales tracking, and invoicing.

User-Friendly Interface: The system will incorporate a user-friendly interface accessible to employees
across all branches. It will be thoughtfully designed to cater to the unique requirements of different user
categories.

Enhanced Customer Engagement: In our pursuit of customer-centricity, AdventureFits aims to develop a

customer-facing app or website. This digital platform will empower customers to explore our diverse
product offerings, make purchases, and access valuable information about our adventure holidays.

User Roles and Categories

 Our sales branch managers will access real-time data to track inventory, monitor sales, analyze
performance, and make strategic decisions. The sales branch Manager Raises Request Order,
receives shipment of Order to the Inbound Inventory, and transfer them to the outbound for
sales

 Our shop-floor staff will arrange the physical stocks which serials are in the outbound inventory
for sales. Initiate sales transactions by processing online, telephone and physical sales.

 The Warehouse processing team receives the requested Orders from each sales branch,
processes the shipment of orders, and monitors the physical delivery of each shipment to the
destination Sales Branch. After successful physical delivery update the Outbound Stock in the
delivery Warehouse.

 The procurement department which is under the inventory management team, raises and
processes PO based on low inventory levels and monitors deliveries of suppliers to their
destination warehouses. After successful delivery update the Inbound Inventory in the
destination Warehouse.

Fitness for Purpose in Design

In our design approach, "fitness for purpose" stands as a core principle, guaranteeing that the database
aligns precisely with AdventureFits' specific needs and objectives. To achieve this, our design ensures
scalability, accommodating the company's growth, including expansion into the European market. It can
effortlessly handle increased branches, products, and customers while maintaining optimal performance.
We prioritize data integrity for accurate reporting and decision-making. The database will implement
data validation rules, constraints, and integrity, ensuring the consistency and quality of our data. We will
design a user-friendly and intuitive interface to enable employees with varying technical expertise to
navigate the system effortlessly. This approach ensures accessibility and usability for all team members.
Robust security measures, including access control and role-based access, will be put in place to
safeguard sensitive data. Employees only access information relevant to their roles, enhancing data
protection across the organization.

Entity Relationship (E-R) Model

We have developed an Entity Relationship model that outlines how we can enhance Adventurefit’s
inventory system based on the existing inventory management system. We have identified the following
entities and attributes:

Entities Attributes
Customer ID, First Name, Last Name, Address, Phone
1 Customers Number, Email
Transaction Number, Customer ID, Product ID, Product
Description, Units, Price per unit, Total Price, VAT, Price
2 Sales Inc VAT, Discount, Amount Paid
Branch ID, Name, Branch Location, Manager Name,
Product ID, Quantity Available, Shipment ID, Inbound,
3 Sales Branches Outbound, Serial number
Staff ID, First Name, Last Name, Address, Phone Number,
4 Employees Email, Designation, Location, Employment Start Date
Username, Role ID, Role Name, Role Description, Branch
5 Staff Access ID, Location ID, Password
Product ID, Product Description, Product Quantity, Supplier
6 Product Master Table ID, Category ID, Product Master Tablecol
Product Name, Product ID, Product Description, Product
7 Product Request Table Quantity, Category ID
Request Order No, Product ID, Staff ID, Transaction Date,
Requested Branch ID, Warehouse Location ID, Date of
8 Request Order Order, Request Sale
Supplier ID, Purchase Order No., Supplier Name, Supplier
9 Suppliers Address, Supplier Phone Number, Supplier Email Address
Serial Number, Product Name, Product Description,
Purchase Order Number, Suppliers GRD No., Transaction
Number, Warehouse Location ID, Inbound, Outbound,
Transaction Code, Request Order Number, Username,
Destination Shop Branch ID, Shipment ID, Transaction
10 Inventory Management Date, Outbound
E-R Model Description
Our Entity Relationship (ER) model consists of 10 entities with various attributes. When a customer
places an order or a sale occurs in one of our sales branches, the details of this sale transaction are
stored in the Sales entity at that specific branch. Each sales transaction is associated with a unique
Transaction Number and Sales ID. Sales can either be on-site or, in the case of online or telephone sales,
can include the option of selecting a pickup location.

The sales branch's top priority is to maintain adequate stock levels, ensuring products are always
available based on predefined minimum reorder levels. If a product's availability falls below the reorder
threshold, the sales manager initiates a request order through the Product Request table. Each request
order is assigned a unique alphanumeric transaction ID incorporating the transaction code i.e. a Request
Order, Branch ID, Staff ID, Transaction Date, and a serialized number format. For example, a request
order raised at the Dundee branch might have an ID like RQ/DD-1/EVN-MG-225/RQ-001.
The Product Request table serves as an entity that itemizes all the products currently offered by
AdventureFit. These request orders are then forwarded to the Inventory Management table for
validation and further processing. The Inventory Management table plays a central role in our model,
featuring one-to-many relationships with 5 different entities. Its primary interaction is with the Product
Master table, where product availability is verified, and the appropriate supplier is selected using the
Supplier ID. Subsequently, a purchase order is generated for the selected supplier, destined for one of
AdventureFit's strategic warehouse locations. Each Purchase Order is assigned a unique transaction ID.

The Supplier's table is another entity that tracks all Purchase Order (PO) transactions initiated by the
Inventory Management Team and is currently in the processing stage with the supplier. This table
contains essential information, including the destination warehouse, the goods' delivery number, lead
time to delivery, quantity to be supplied, and the amount to be paid upon delivery.

Upon the supplier's delivery of products to the designated warehouse location, the Inventory
Management table is updated with the new supplies added to the Inbound Stock. Future request orders
from sales branches will be fulfilled using the Outbound Stock, generating a unique Shipment ID for each
shipment recorded in the database. This Shipment ID is used for reconciliation purposes at each sales
branch after the physical delivery of the goods. For example, in processing the Dundee Request, the
shipment ID for the Dundee Branch will be SP/DD-2/IAN-INV-120/SP-001. Which comprises the
transaction Code, the warehouse location fulfilling the request, the Staff ID in the warehouse, and a
unique transaction number.

Additionally, we have a Staff entity table that lists all AdventureFit staff. This table maintains a one-to-
one relationship with the Staff Access table, configured with appropriate views. Staff roles defined in the
Staff Access table grant varying levels of access to the Inventory Management table, the Sales Branch
and other information based on their specific roles within the organization. This ensures controlled and
secure access to sensitive information across sales branches and teams.
SQL CREATE STATEMENTS
-- MySQL Script generated by MySQL Workbench
-- Thu Oct 19 02:03:47 2023
-- Model: New Model Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE,
SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZE
RO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`Staff`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Staff` (
`Staff ID` INT NOT NULL,
`First Name` VARCHAR(45) NULL,
`Last Name` VARCHAR(45) NULL,
`Address` VARCHAR(45) NULL,
 `Phone Number` VARCHAR(45) NULL,
`Email` VARCHAR(45) NULL,
`Designation` VARCHAR(45) NULL,
`Location` VARCHAR(45) NULL,
`Employmemt Start Date` VARCHAR(45) NULL,
PRIMARY KEY (`Staff ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Suppliers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Suppliers` (
`Supplier ID` INT NOT NULL,
`PO Number` VARCHAR(45) NULL,
`Supplier Name` VARCHAR(45) NULL,
`Supplier Address` VARCHAR(45) NULL,
`Supplier Phone Number` VARCHAR(45) NULL,
`Supplier Email Address` VARCHAR(45) NULL,
PRIMARY KEY (`Supplier ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Inventory Management`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Inventory Management` (
`Serial Number` VARCHAR(45) NOT NULL,
`Product Name` INT NULL,
`Product Description` VARCHAR(45) NULL,
`Available Quatity` VARCHAR(45) NULL,
`PO number` VARCHAR(45) NULL,
 `Suppliers GRD No` VARCHAR(45) NULL,
`Transaction Number` VARCHAR(45) NULL,
`Inbound` VARCHAR(45) NULL,
`Transaction Code` VARCHAR(45) NULL,
`Reqest Order Number` VARCHAR(45) NULL,
`Destination Shop Branch ID` VARCHAR(45) NULL,
`ShipmentID` VARCHAR(45) NULL,
`Transaction Date` VARCHAR(45) NULL,
`Username` VARCHAR(45) NULL,
`Outbound` VARCHAR(45) NULL,
`Location ID` VARCHAR(45) NULL,
PRIMARY KEY (`Serial Number`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Request Order`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Request Order` (
`Request ID` INT NOT NULL,
`Product ID` VARCHAR(45) NULL,
`Product Name` VARCHAR(45) NULL,
`Product Description` VARCHAR(45) NULL,
`Requested Product Quantity` VARCHAR(45) NULL,
`Staff ID` VARCHAR(45) NULL,
`Transaction Code` VARCHAR(45) NULL,
`Transaction Date` VARCHAR(45) NULL,
`Request Order No` VARCHAR(45) NULL,
`Inventory Management_Serial Number` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Request ID`, `Inventory Management_Serial Number`),
INDEX `fk_Request Order_Inventory Management1_idx` (`Inventory
Management_Serial Number` ASC) VISIBLE,
 CONSTRAINT `fk_Request Order_Inventory Management1`
FOREIGN KEY (`Inventory Management_Serial Number`)
REFERENCES `mydb`.`Inventory Management` (`Serial Number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Sales Branches`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Sales Branches` (
`Branch ID` INT NOT NULL,
`Name` VARCHAR(45) NULL,
`Branch Location` VARCHAR(45) NULL,
`Manager Name` VARCHAR(45) NULL,
`Product ID` VARCHAR(45) NULL,
`Available Quantity` VARCHAR(45) NULL,
`Shipment ID` VARCHAR(45) NULL,
`Inbound` VARCHAR(45) NULL,
`Serial Number` VARCHAR(45) NULL,
`Outbound` INT NULL,
`Inventory Management_Serial Number` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Branch ID`, `Inventory Management_Serial Number`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Sales`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Sales` (
`Customer ID` INT NOT NULL,
 `Transaction Number` VARCHAR(45) NULL,
`Product ID` VARCHAR(45) NULL,
`Product Description` VARCHAR(45) NULL,
`Units` VARCHAR(45) NULL,
`Price Per Unit` VARCHAR(45) NULL,
`Total Price` VARCHAR(45) NULL,
`VAT` VARCHAR(45) NULL,
`Price Inc VAT` VARCHAR(45) NULL,
`Discounts` VARCHAR(45) NULL,
`Amount Paid` VARCHAR(45) NULL,
`Sales Branches_Branch ID` INT NOT NULL,
PRIMARY KEY (`Customer ID`, `Sales Branches_Branch ID`),
INDEX `fk_Sales_Sales Branches1_idx` (`Sales Branches_Branch ID`
ASC) VISIBLE,
CONSTRAINT `fk_Sales_Sales Branches1`
FOREIGN KEY (`Sales Branches_Branch ID`)
REFERENCES `mydb`.`Sales Branches` (`Branch ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Staff Acess`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Staff Acess` (
`Username` VARCHAR(45) NOT NULL,
`Role ID` INT NOT NULL,
`Role Name` VARCHAR(45) NULL,
`Role Description` VARCHAR(45) NULL,
`Branch ID` VARCHAR(45) NULL,
`Location ID` VARCHAR(45) NULL,
 `Password` VARCHAR(45) NULL,
`Staff_Staff ID` INT NOT NULL,
`Sales Branches_Branch ID` INT NOT NULL,
`Sales Branches_Inventory Management_Serial Number` VARCHAR(45) NOT
NULL,
`Inventory Management_Serial Number` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Username`, `Staff_Staff ID`, `Sales Branches_Branch
ID`, `Sales Branches_Inventory Management_Serial Number`, `Inventory
Management_Serial Number`),
INDEX `fk_Staff Acess_Staff1_idx` (`Staff_Staff ID` ASC) VISIBLE,
INDEX `fk_Staff Acess_Sales Branches1_idx` (`Sales Branches_Branch
ID` ASC, `Sales Branches_Inventory Management_Serial Number` ASC)
VISIBLE,
INDEX `fk_Staff Acess_Inventory Management1_idx` (`Inventory
Management_Serial Number` ASC) VISIBLE,
CONSTRAINT `fk_Staff Acess_Staff1`
FOREIGN KEY (`Staff_Staff ID`)
REFERENCES `mydb`.`Staff` (`Staff ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Staff Acess_Sales Branches1`
FOREIGN KEY (`Sales Branches_Branch ID` , `Sales
Branches_Inventory Management_Serial Number`)
REFERENCES `mydb`.`Sales Branches` (`Branch ID` , `Inventory
Management_Serial Number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Staff Acess_Inventory Management1`
FOREIGN KEY (`Inventory Management_Serial Number`)
REFERENCES `mydb`.`Inventory Management` (`Serial Number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Products Request Table`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Products Request Table` (
`Product Name` INT NULL,
`Product ID` VARCHAR(45) NOT NULL,
`Product Description` VARCHAR(45) NULL,
`Product Quatity` VARCHAR(45) NULL,
`Category ID` VARCHAR(45) NULL,
`Request Order_Request ID` INT NOT NULL,
PRIMARY KEY (`Product ID`, `Request Order_Request ID`),
INDEX `fk_Products_Request Order1_idx` (`Request Order_Request ID`
ASC) VISIBLE,
CONSTRAINT `fk_Products_Request Order1`
FOREIGN KEY (`Request Order_Request ID`)
REFERENCES `mydb`.`Request Order` (`Request ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Customers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Customers` (
`Customer ID` INT NOT NULL,
`First Name` VARCHAR(45) NULL,
`Last Name` VARCHAR(45) NULL,
`Adress` VARCHAR(45) NULL,
`Phone Number` VARCHAR(45) NULL,
`Email` VARCHAR(45) NULL,
PRIMARY KEY (`Customer ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Customers_has_Sales`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Customers_has_Sales` (
`Customers_Customer ID` INT NOT NULL,
`Sales_Customer ID` INT NOT NULL,
PRIMARY KEY (`Customers_Customer ID`, `Sales_Customer ID`),
INDEX `fk_Customers_has_Sales_Sales1_idx` (`Sales_Customer ID` ASC)
VISIBLE,
INDEX `fk_Customers_has_Sales_Customers1_idx` (`Customers_Customer
ID` ASC) VISIBLE,
CONSTRAINT `fk_Customers_has_Sales_Customers1`
FOREIGN KEY (`Customers_Customer ID`)
REFERENCES `mydb`.`Customers` (`Customer ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Customers_has_Sales_Sales1`
FOREIGN KEY (`Sales_Customer ID`)
REFERENCES `mydb`.`Sales` (`Customer ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Product Master Table`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Product Master Table` (
`Product ID` INT NOT NULL,
 `Product Description` VARCHAR(45) NULL,
`Product Quantity` VARCHAR(45) NULL,
`Supplier ID` VARCHAR(45) NULL,
`Category ID` VARCHAR(45) NULL,
`Product Master Tablecol` VARCHAR(45) NULL,
PRIMARY KEY (`Product ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Product Master Table_has_Stock Management`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Product Master Table_has_Stock
Management` (
`Product Master Table_Product ID` INT NOT NULL,
`Stock Management_Serial Number` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Product Master Table_Product ID`, `Stock
Management_Serial Number`),
INDEX `fk_Product Master Table_has_Stock Management_Stock
Manageme_idx` (`Stock Management_Serial Number` ASC) VISIBLE,
INDEX `fk_Product Master Table_has_Stock Management_Product
Master_idx` (`Product Master Table_Product ID` ASC) VISIBLE,
CONSTRAINT `fk_Product Master Table_has_Stock Management_Product
Master T1`
FOREIGN KEY (`Product Master Table_Product ID`)
REFERENCES `mydb`.`Product Master Table` (`Product ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Product Master Table_has_Stock Management_Stock
Management1`
FOREIGN KEY (`Stock Management_Serial Number`)
REFERENCES `mydb`.`Inventory Management` (`Serial Number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Inventory Management_has_Suppliers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Inventory
Management_has_Suppliers` (
`Inventory Management_Serial Number` VARCHAR(45) NOT NULL,
`Suppliers_Supplier ID` INT NOT NULL,
PRIMARY KEY (`Inventory Management_Serial Number`,
`Suppliers_Supplier ID`),
INDEX `fk_Inventory Management_has_Suppliers_Suppliers1_idx`
(`Suppliers_Supplier ID` ASC) VISIBLE,
INDEX `fk_Inventory Management_has_Suppliers_Inventory
Managemen_idx` (`Inventory Management_Serial Number` ASC) VISIBLE,
CONSTRAINT `fk_Inventory Management_has_Suppliers_Inventory
Management1`
FOREIGN KEY (`Inventory Management_Serial Number`)
REFERENCES `mydb`.`Inventory Management` (`Serial Number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Inventory Management_has_Suppliers_Suppliers1`
FOREIGN KEY (`Suppliers_Supplier ID`)
REFERENCES `mydb`.`Suppliers` (`Supplier ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Sales Branches_has_Inventory Management`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Sales Branches_has_Inventory
Management` (
`Sales Branches_Branch ID` INT NOT NULL,
`Sales Branches_Inventory Management_Serial Number` VARCHAR(45) NOT
NULL,
`Inventory Management_Serial Number` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Sales Branches_Branch ID`, `Sales Branches_Inventory
Management_Serial Number`, `Inventory Management_Serial Number`),
INDEX `fk_Sales Branches_has_Inventory Management_Inventory
Mana_idx` (`Inventory Management_Serial Number` ASC) VISIBLE,
INDEX `fk_Sales Branches_has_Inventory Management_Sales
Branches1_idx` (`Sales Branches_Branch ID` ASC, `Sales
Branches_Inventory Management_Serial Number` ASC) VISIBLE,
CONSTRAINT `fk_Sales Branches_has_Inventory Management_Sales
Branches1`
FOREIGN KEY (`Sales Branches_Branch ID` , `Sales
Branches_Inventory Management_Serial Number`)
REFERENCES `mydb`.`Sales Branches` (`Branch ID` , `Inventory
Management_Serial Number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Sales Branches_has_Inventory Management_Inventory
Manage1`
FOREIGN KEY (`Inventory Management_Serial Number`)
REFERENCES `mydb`.`Inventory Management` (`Serial Number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Products Request Table_has_Sales Branches`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Products Request Table_has_Sales
Branches` (
`Products Request Table_Product ID` VARCHAR(45) NOT NULL,
 `Products Request Table_Request Order_Request ID` INT NOT NULL,
`Sales Branches_Branch ID` INT NOT NULL,
`Sales Branches_Inventory Management_Serial Number` VARCHAR(45) NOT
NULL,
PRIMARY KEY (`Products Request Table_Product ID`, `Products Request
Table_Request Order_Request ID`, `Sales Branches_Branch ID`, `Sales
Branches_Inventory Management_Serial Number`),
INDEX `fk_Products Request Table_has_Sales Branches_Sales
Branches_idx` (`Sales Branches_Branch ID` ASC, `Sales
Branches_Inventory Management_Serial Number` ASC) VISIBLE,
INDEX `fk_Products Request Table_has_Sales Branches_Products
Reque_idx` (`Products Request Table_Product ID` ASC, `Products Request
Table_Request Order_Request ID` ASC) VISIBLE,
CONSTRAINT `fk_Products Request Table_has_Sales Branches_Products
Request1`
FOREIGN KEY (`Products Request Table_Product ID` , `Products
Request Table_Request Order_Request ID`)
REFERENCES `mydb`.`Products Request Table` (`Product ID` ,
`Request Order_Request ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Products Request Table_has_Sales Branches_Sales
Branches1`
FOREIGN KEY (`Sales Branches_Branch ID` , `Sales
Branches_Inventory Management_Serial Number`)
REFERENCES `mydb`.`Sales Branches` (`Branch ID` , `Inventory
Management_Serial Number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;