Create tables for the following question - include table names and column names:

Question

Create tables for the following question - include table names and column names:

The National Merchandise Inc. (NMI) has asked you to design and implement a purchase order system. The NMI sells many office products, such as Pencil, Pen, and the like. Each product has a product ID and description. NMI constantly orders products from its suppliers, such as Staples, Office Max, etc. To simplify the matter, each time we place an order with a supplier, only one item is ordered. Not every supplier carries every product so we keep track authorized/available suppliers for each product. For instance, the authorized suppliers of Item p1 Pencil are s1 and s3; the authorized suppliers of Item p3 Magic Marker are s1 and s2. Notice that the same item may have different price with different suppliers. For instance, p1 Pencil is $2.50 if we order it from Staples, and $2.00 if we order from Office Depot. NMI maintains the price list in its database.

Explanation / Answer

Answer is as follows :

Create Tables Statements in SQL and sample data is as follows :

/* Table Product */

CREATE TABLE Product( Product_id varchar(20) PRIMARY KEY, Name text ,Description varchaar(20));

/* Table Supplier */

CREATE TABLE Supplier(Supplier_Id varchar(20) PRIMARY KEY, ProductSupplied Varchar(20), Description varchar(20), Price double(10) );

/* Table Order*/

CREATE TABLE Order(Product_Id varchar(20) FOREIGN KEY REFERENCES Product(Product_Id) ,

Supplier_Id varchar(20) FOREIGN KEY REFERENCES Supplier(Supplier_Id) );

if there is any query please ask in comments...

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.