i need to write a mysql script to build a database that look

i need to write a mysql script to build a database that looks like this and i dont know what to do

Category (categoryID, categoryName)

Product (productID, productName, productDescription, currentUnitPrice, inStock, season, categoryID)

Customer (customerID, firstName, lastName, streetNo, street, suburb, postcode, state, emailAddress)

CustomerPhoneNo (customerID, phoneNumber)

Orders (orderID, customerID, orderDateTime, paymentStatus, streetNo, street, suburb, postcode, state)

OrderContents (orderID, productID, productUnitPrice, productQuantity)

Salesperson (salespersonID, firstName, lastName, phoneNumber, emailAddress, supervisorID)

Partner (partnerID, companyName, streetNo, street, suburb, postcode, state, firstName, lastName, phoneNumber, emailAddress, managerID)

Purchase (purchaseID, partnerID, salespersonID, productID, purchaseDateTime, paymentStatus, streetNo, street, suburb, postcode, state, productUnitPrice, productQuantity, discount)

Foreign Keys •

Product (categoryID) is dependent on Category (categoryID)

• CustomerPhoneNumber (customerID) is dependent on Customer (customerID)

• Order (customerID) is dependent on Customer (customerID)

• OrderContents (productID) is dependent on Product (productID)

• OrderContents (orderID) is dependent on Order (orderID)

• Salesperson (supervisorID) is dependent on Salesperson (salespersonID)

• Partner (managerID) is dependent on Salesperson (salespersonID)

• Purchase (partnerID) is dependent on Partner (partnerID)

• Purchase (salespersonID) is dependent on Salesperson (salespersonID)

• Purchase (productID) is dependent on Product (productID)

Other Constraints

• It is assumed that all primary keys must be unique

• The quantity of a product in an order or purchase must be at least 1.

• The attribute paymentStatus, which exists in two tables, can only have the value of paid and unpaid

• The attribute inStock, which exists in the product table, can only have the value of yes or no

Solution

We need to go ahead step by step to achieve the desired database:

1. Create the database using \'create database\' query (let\'\'s name the database as ORDER_MANAGEMENT):

CREATE DATABASE ORDER_MANAGEMENT;

2. Then create the specified tables with \'create table\' command. We will assign various datatypes to the mentioned columns as per the requirement. For eg, ID variables for all the tables should be integers, hence use INT datatype; text containing columns should use character datatypes, hence use VARCHAR; and so on...). We will also include the primary key, foreign key and check constraints to meet the database requirement in table creation step only. The final queries would be as follows:

CREATE TABLE Categories
(
    categoryID    INT,
    categoryName VARCHAR(100),
    PRIMARY KEY (categoryID) -- We will specify all the ID columns as primary key like this
);

CREATE TABLE Product
(
    productID           INT,
    productName         VARCHAR(100),
    productDescription VARCHAR(200),
    currentUnitPrice    FLOAT,
    inStock             VARCHAR(5),
    season              VARCHAR(50),
    categoryID          INT,
    PRIMARY KEY (productID),
    FOREIGN KEY (categoryID) REFERENCES Categories(categoryID), -- We will specify all the foreign key constraints on the table like this
    CHECK (inStock IN (\'yes\',\'no\')) -- We will specify all the other value checks for columns like this. Multiple comnstraints are allowed on a table.
);

CREATE TABLE Customer
(
    customerID   INT,
    firstName    VARCHAR(50),
    lastName     VARCHAR(50),
    streetNo     VARCHAR(10),
    street       VARCHAR(100),
    suburb       VARCHAR (100),
    postcode     VARCHAR(10),
    state        VARCHAR(50),
    emailAddress VARCHAR(200),
    PRIMARY KEY (customerID)
);

CREATE TABLE CustomerPhoneNo
(
    customerID INT,
    phoneNumber VARCHAR(15),
    PRIMARY KEY (customerID,phoneNumber), -- A primary key may be a combination of multiple columns like this.
    FOREIGN KEY (customerID) REFERENCES Customer(customerID)
);

CREATE TABLE Orders
(
    orderID       INT,
    customerID    INT,
    orderDateTime DATE,
    paymentStatus VARCHAR(10),
    streetNo      VARCHAR(10),
    street        VARCHAR(100),
    suburb        VARCHAR (100),
    postcode      VARCHAR(10),
    state         VARCHAR(50),
    PRIMARY KEY (orderID),
    FOREIGN KEY (customerID) REFERENCES Customer(customerID),
    CHECK (paymentStatus IN (\'paid\',\'unpaid\'))
);

CREATE TABLE OrderContents
(
    orderID   INT,
    productID INT,
    productUnitPrice FLOAT,
    productQuantity INT,
    PRIMARY KEY (orderID,productID),
    FOREIGN KEY (orderID) REFERENCES Orders(orderID),
    FOREIGN KEY (productID) REFERENCES Product(productID),
    CHECK (productQuantity > 0)
);

CREATE TABLE Salesperson
(
    salespersonID INT,
    firstName     VARCHAR(50),
    lastName      VARCHAR(50),
    phoneNumber   VARCHAR(15),
    emailAddress VARCHAR(200),
    supervisorID INT,
    PRIMARY KEY (salespersonID),
    FOREIGN KEY (supervisorID) REFERENCES Salesperson(salespersonID)
);

CREATE TABLE Partner
(
    partnerID    INT,
    companyName VARCHAR(200),
    streetNo     VARCHAR(10),
    street       VARCHAR(100),
    suburb       VARCHAR (100),
    postcode     VARCHAR(10),
    state        VARCHAR(50),
    firstName    VARCHAR(50),
    lastName     VARCHAR(50),
    phoneNumber VARCHAR(15),
    emailAddress VARCHAR(200),
    managerID      INT,
    PRIMARY KEY (partnerID),
    FOREIGN KEY (managerID) REFERENCES Salesperson(salespersonID)
);

CREATE TABLE Purchase
(
    purchaseID       INT,
    partnerID        INT,
    salespersonID    INT,
    productID        INT,
    purchaseDateTime DATE,
    paymentStatus    VARCHAR(10),
    street           VARCHAR(100),
    suburb           VARCHAR (100),
    postcode         VARCHAR(10),
    state            VARCHAR(50),
    productUnitPrice FLOAT,
    productQuantity INT,
    discount FLOAT,
    PRIMARY KEY (purchaseID),
    FOREIGN KEY (partnerID) REFERENCES Partner(partnerID),
    FOREIGN KEY (salespersonID) REFERENCES Salesperson(salespersonID),
    FOREIGN KEY (productID) REFERENCES Product(productID),
    CHECK (productQuantity > 0 AND paymentStatus IN (\'paid\',\'unpaid\'))
);