Question

Metal Holdings Inc.

You have been assigned the task of creating a simple relational database using SQLite database by the owner of “Metal Holdings” who is in the business of selling industrial metals to customers (mostly wholesale suppliers) who buy from the company.

Your goal is to design and build a database that would be responsible for:

• Managing a product table containing all metals which at a minimum should provide information such as name, description and price of each metal sold by the company.

• Manage a list of customers who would be purchasing metals from the company. The owner is interested in name, location, contact as well as whether the customer is a retail or wholesale buyer.

• Manage and maintain a complete inventory of all orders shipped by the company to customers.
  
• The owner wants to maintain the following information about each order at a minimum, but not limited to:
  • Date of the OrderShipping information such as date and status.
  • Product purchased and Quantity ordered.
  • Customer Information.
  • Each customer can purchase 1-to-many products (metals) in a single order.

The following is a list of metals sold by the company (these below metals should be available in your uploaded SQLite database):

• Copper
• Aluminium
• Silver
• Steel
• Bronze
• Brass
• Lead
• Nickel
• Titanium
• Magnesium

Answer 1

SQLite Script:

CREATE TABLE Product (
        PRODUCT_ID integer PRIMARY KEY AUTOINCREMENT,
        NAME varchar,
        DESCRIPTION varchar,
        PRICE float
);

CREATE TABLE Customers (
        CUSTOMER_ID integer PRIMARY KEY AUTOINCREMENT,
        NAME varchar,
        LOCATION varchar,
        CONTACT varchar,
        BUYER_TYPE varchar
);

CREATE TABLE Orders (
        ORDER_ID integer PRIMARY KEY AUTOINCREMENT,
        ORDER_DATE date,
        ORDER_STATUS varchar,
        PRODUCT_ID integer,
        PRODUCT_NAME varchar,
        QUANTITY integer,
        CUSTOMER_ID integer,
        CUSTOMER_NAME varchar
);

INSERT INTO Product(NAME,DESCRIPTION,PRICE) VALUES("Copper","Copper_Descrition",3500);
INSERT INTO Product(NAME,DESCRIPTION,PRICE) VALUES("Aluminium","Aluminium_Description",3500);
INSERT INTO Product(NAME,DESCRIPTION,PRICE) VALUES("Silver","Silver_Description",3500);
INSERT INTO Product(NAME,DESCRIPTION,PRICE) VALUES("Steel","Steel_Description",3500);
INSERT INTO Product(NAME,DESCRIPTION,PRICE) VALUES("Bronze","Bronze_Description",3500);
INSERT INTO Product(NAME,DESCRIPTION,PRICE) VALUES("Brass","Brass_Description",3500);
INSERT INTO Product(NAME,DESCRIPTION,PRICE) VALUES("Lead","Lead_Description",3500);
INSERT INTO Product(NAME,DESCRIPTION,PRICE) VALUES("Nickel","Nickel_Description",3500);
INSERT INTO Product(NAME,DESCRIPTION,PRICE) VALUES("Titanium","Titanium_Description",3500);
INSERT INTO Product(NAME,DESCRIPTION,PRICE) VALUES("Magnesium","Magnesium_Description",3500);

OUTPUT:

I hope it helps.