INSERT INTO Ref_Payment_Methods (payment_method_code,

payment_method_description)
VALUES ('CC', 'Credit Card');
SELECT * FROM Ref_Payment_Methods;
UPDATE Ref_Payment_Methods
SET payment_method_description = 'Credit Card - Updated'
WHERE payment_method_code = 'CC';
DELETE FROM Ref_Payment_Methods
WHERE payment_method_code = 'CC';
INSERT INTO Ref_Address_Types (address_type_code,
address_type_description)
VALUES ('BILL', 'Billing');
SELECT * FROM Ref_Address_Types;
UPDATE Ref_Address_Types
SET address_type_description = 'Billing Updated'
WHERE address_type_code = 'BILL';
DELETE FROM Ref_Address_Types
WHERE address_type_code = 'BILL';
INSERT INTO Ref_Product_Types (product_type_code,
product_type_description)
VALUES ('TOY', 'Toy');
SELECT * FROM Ref_Product_Types;
UPDATE Ref_Product_Types
SET product_type_description = 'Toy Updated'
WHERE product_type_code = 'TOY';
DELETE FROM Ref_Product_Types
WHERE product_type_code = 'TOY';
INSERT INTO Addresses (line_1, line_2, city, zip_postcode,
state_province_county, country, other_address_details)
VALUES ('123 Main St', 'Apt 4', 'New York', '10001', 'NY', 'USA',
'Residential address');
SELECT * FROM Addresses;
UPDATE Addresses
SET city = 'Los Angeles'
WHERE address_id = 1;
DELETE FROM Addresses
WHERE address_id = 1;
INSERT INTO Suppliers (supplier_code, supplier_name, supplier_address,
supplier_email, supplier_phone, other_supplier_details)
VALUES ('SUP01', 'ABC Supplies', '123 Supplier St', 'contact@[Link]',
'1234567890', 'Trusted supplier');
SELECT * FROM Suppliers;
UPDATE Suppliers
SET supplier_email = 'newemail@[Link]'
WHERE supplier_code = 'SUP01';
DELETE FROM Suppliers
WHERE supplier_code = 'SUP01';
INSERT INTO Customers (payment_method_code, customer_name,
customer_phone, customer_email, date_became_customer,
payment_details, other_customer_details)
VALUES ('CC', 'John Doe', '555-1234', 'johndoe@[Link]', '2023-01-
01', 'Card ending in 1234', 'Regular customer');
SELECT * FROM Customers;
UPDATE Customers
SET customer_name = 'John Doe Updated'
WHERE customer_id = 1;
DELETE FROM Customers
WHERE customer_id = 1;
INSERT INTO Customer_Addresses (customer_id, address_id,
address_type_code, date_from, date_to)
VALUES (1, 1, 'BILL', '2023-01-01', NULL);
SELECT * FROM Customer_Addresses;
UPDATE Customer_Addresses
SET date_to = '2023-12-31'
WHERE customer_id = 1 AND address_id = 1;
DELETE FROM Customer_Addresses
WHERE customer_id = 1 AND address_id = 1;
INSERT INTO Products (product_type_code, product_code,
product_name, product_price, product_description)
VALUES ('TOY', 'TOY001', 'Toy Car', 9.99, 'Small toy car');
SELECT * FROM Products;
UPDATE Products
SET product_price = 12.99
WHERE product_id = 1;
DELETE FROM Products
WHERE product_id = 1;
INSERT INTO Shopping_Basket (customer_id, basket_datetime,
total_cost, other_basket_details)
VALUES (1, '2024-10-25 10:00:00', 29.99, 'Customer bought toys');
SELECT * FROM Shopping_Basket;
UPDATE Shopping_Basket
SET total_cost = 35.99
WHERE customer_id = 1 AND basket_datetime = '2024-10-25 10:00:00';
DELETE FROM Shopping_Basket
WHERE customer_id = 1 AND basket_datetime = '2024-10-25 10:00:00';
INSERT INTO Basket_Items (customer_id, basket_datetime, product_id,
quantity, cost)
VALUES (1, '2024-10-25 10:00:00', 1, 2, 19.98);
SELECT * FROM Basket_Items;
UPDATE Basket_Items
SET quantity = 3, cost = 29.97
WHERE customer_id = 1 AND basket_datetime = '2024-10-25 10:00:00'
AND product_id = 1;
DELETE FROM Basket_Items
WHERE customer_id = 1 AND basket_datetime = '2024-10-25 10:00:00'
AND product_id = 1;
INSERT INTO Inventory_Locations (product_id, location_address_id,
 quantity_in_stock, reorder_level, reorder_quantity,
total_average_monthly_usage, other_inventory_details)
VALUES (1, 1, 100, 20, 50, 30, 'Main warehouse stock');
SELECT * FROM Inventory_Locations;
UPDATE Inventory_Locations
SET quantity_in_stock = 150
WHERE inventory_location_id = 1;
DELETE FROM Inventory_Locations
WHERE inventory_location_id = 1;
INSERT INTO Supplier_Locations (supplier_code, address_id, date_from,
date_to)
VALUES ('SUP01', 1, '2023-01-01', NULL);
SELECT * FROM Supplier_Locations;
UPDATE Supplier_Locations
SET date_to = '2023-12-31'
WHERE supplier_code = 'SUP01' AND address_id = 1;
DELETE FROM Supplier_Locations
WHERE supplier_code = 'SUP01' AND address_id = 1;

CREATE TABLE Ref_Payment_Methods (

payment_method_code CHAR(5) PRIMARY KEY,

payment_method_description VARCHAR(100)

);

CREATE TABLE Ref_Address_Types (

address_type_code CHAR(5) PRIMARY KEY,

address_type_description VARCHAR(100)

);

CREATE TABLE Ref_Product_Types (

product_type_code CHAR(5) PRIMARY KEY,

product_type_description VARCHAR(100)

);
CREATE TABLE Addresses (

address_id INT AUTO_INCREMENT PRIMARY KEY,

line_1 VARCHAR(100),

line_2 VARCHAR(100),

line_3 VARCHAR(100),

line_4 VARCHAR(100),

city VARCHAR(100),

zip_postcode VARCHAR(20),

state_province_county VARCHAR(100),

country VARCHAR(100),

other_address_details VARCHAR(255)

);

CREATE TABLE Suppliers (

supplier_code CHAR(5) PRIMARY KEY,

supplier_name VARCHAR(100),

supplier_address VARCHAR(255),

supplier_email VARCHAR(100),

supplier_phone VARCHAR(20),

other_supplier_details VARCHAR(255)

);

CREATE TABLE Customers (

customer_id INT AUTO_INCREMENT PRIMARY KEY,

payment_method_code CHAR(5),

customer_name VARCHAR(100),

customer_phone VARCHAR(20),

customer_email VARCHAR(100),

date_became_customer DATE,
 payment_details VARCHAR(100),

other_customer_details VARCHAR(255),

FOREIGN KEY (payment_method_code) REFERENCES

);

CREATE TABLE Customer_Addresses (

customer_id INT,

address_id INT,

address_type_code CHAR(5),

date_from DATE,

date_to DATE,

PRIMARY KEY (customer_id, address_id, address_type_code),

FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),

FOREIGN KEY (address_id) REFERENCES Addresses(address_id),

FOREIGN KEY (address_type_code) REFERENCES

);

CREATE TABLE Products (

product_id INT AUTO_INCREMENT PRIMARY KEY,

product_type_code CHAR(5),

product_code VARCHAR(50),

product_name VARCHAR(100),

product_price DECIMAL(10, 2),

product_description VARCHAR(255),

FOREIGN KEY (product_type_code) REFERENCES

);
CREATE TABLE Shopping_Basket (

customer_id INT,

basket_datetime DATETIME,

total_cost DECIMAL(10, 2),

other_basket_details VARCHAR(255),

PRIMARY KEY (customer_id, basket_datetime),

FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)

);

CREATE TABLE Basket_Items (

customer_id INT,

basket_datetime DATETIME,

product_id INT,

quantity INT,

cost DECIMAL(10, 2),

PRIMARY KEY (customer_id, basket_datetime, product_id),

FOREIGN KEY (customer_id, basket_datetime) REFERENCES

FOREIGN KEY (product_id) REFERENCES Products(product_id)

);

CREATE TABLE Inventory_Locations (

inventory_location_id INT AUTO_INCREMENT PRIMARY KEY,

product_id INT,

location_address_id INT,

quantity_in_stock INT,

reorder_level INT,

reorder_quantity INT,

total_average_monthly_usage INT,
 other_inventory_details VARCHAR(255),

FOREIGN KEY (product_id) REFERENCES Products(product_id),

FOREIGN KEY (location_address_id) REFERENCES Addresses(address_id)

);

CREATE TABLE Supplier_Locations (

supplier_code CHAR(5),

address_id INT,

date_from DATE,

date_to DATE,

PRIMARY KEY (supplier_code, address_id),

FOREIGN KEY (supplier_code) REFERENCES Suppliers(supplier_code),

FOREIGN KEY (address_id) REFERENCES Addresses(address_id)

);

CREATE TABLE Product_Suppliers (

product_id INT,

supplier_code CHAR(5),

value_supplied_to_date DECIMAL(10, 2),

total_quantity_supplied_to_date INT,

first_item_supplied_date DATE,

last_item_supplied_date DATE,

delivery_lead_time INT,

standard_price DECIMAL(10, 2),

percentage_discount DECIMAL(5, 2),

minimum_order_quantity INT,

maximum_order_quantity INT,

other_item_suppliers_details VARCHAR(255),

PRIMARY KEY (product_id, supplier_code),

FOREIGN KEY (supplier_code) REFERENCES Suppliers(supplier_code)

);