Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Need help with adding sample data in these tables in SQL. I need the whole thing to run all at once THIS IS THE SQL

Need help with adding sample data in these tables in SQL. I need the whole thing to run all at once 

THIS IS THE SQL CREATION SCRIPT THAT I MADE
-- Create MANUFACT Table
CREATE TABLE MANUFACT (
   ManuCode INT PRIMARY KEY,
   ManuName VARCHAR(50),
   LeadTime INT
);

-- Create PRODUCT Table
CREATE TABLE PRODUCT (
   ProdID INT PRIMARY KEY,
   ProdName VARCHAR(50),
   ProdDescr VARCHAR(50),
   UnitPrice DECIMAL(10, 2),
   QuantityOnHand INT
);

-- Create SUPPLIER Table
CREATE TABLE SUPPLIER (
   SupplierID INT PRIMARY KEY,
   SupplierName VARCHAR(50),
   ContactPerson VARCHAR(50),
   ContactNum VARCHAR(20),
   Email VARCHAR(50),
   ProductID INT,
   ManuCode INT
);

-- Create CUSTOMER Table
CREATE TABLE CUSTOMER (
   CusID INT PRIMARY KEY,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   Address VARCHAR(50),
   Email VARCHAR(50),
   ContactNumber VARCHAR(20),
   OrdNum INT,
   OrderedProdID INT
);

-- Create STOCK Table
CREATE TABLE STOCK (
   StockNum INT PRIMARY KEY,
   Description VARCHAR(50),
   UnitPrice DECIMAL(10, 2),
   UnitDescr VARCHAR(255),
   ManuCode INT
);

-- Create ORDERS Table
CREATE TABLE ORDERS (
   OrdNum INT PRIMARY KEY,
   OrdDate DATE,
   Backlog INT,
   PONum VARCHAR(50),
   PaidDate DATE,
   LeadTime INT,
   TotalAmount DECIMAL(10, 2),
   CusID INT
);

-- Create LOCATION Table
CREATE TABLE LOCATION (
   WarehouseLocation INT PRIMARY KEY,
   AisleNum INT,
   ShelfNum INT,
   BinNum INT,
   Quantity INT,
   ShipID INT
);

-- Create SHIPPINGINFORMATION Table
CREATE TABLE SHIPPINGINFORMATION (
   ShipID INT PRIMARY KEY,
   ShipDate DATE,
   ShipWeight DECIMAL(10, 2),
   ShipCharge DECIMAL(10, 2),
   Carrier VARCHAR(50),
   TrackingNum VARCHAR(50)
);

-- Create CATEGORY Table
CREATE TABLE CATEGORY (
   CatID INT PRIMARY KEY,
   CatName VARCHAR(50),
   OrdNum INT,
   WareHouseLocation INT
);

-- Create TRANSACTION Table
CREATE TABLE TRANSACTION (
   TransactionID INT PRIMARY KEY,
   TransactionDate DATE,
   Quantity INT,
   Email VARCHAR(50),
   ContactNumber VARCHAR(20),
   ShipID INT
);

-- Create ITEMS Table
CREATE TABLE ITEMS (
   ItemNum INT PRIMARY KEY,
   Quantity INT,
   TotalPrice DECIMAL(10, 2),
   ManuCode INT,
   StockNum INT,
   OrderNum INT
);

-- Create CATALOG Table
CREATE TABLE CATALOG (
   StockNum INT,
   ManuCode INT,
   CatNum INT PRIMARY KEY,
   CatPic VARCHAR(50)
);

-- Create CALLTYPE Table
CREATE TABLE CALLTYPE (
   CusID INT,
   OrdNum INT,
   CallDescr VARCHAR(50),
   UderID INT,
   CallCode INT PRIMARY KEY
);

-- Create EMPLOYEE Table
CREATE TABLE EMPLOYEE (
   EmployeeID INT PRIMARY KEY,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   Position VARCHAR(50),
   Email VARCHAR(50),
   ContactNumber VARCHAR(20),
   CusHandledByEmp INT
);

-- Add Foreign Key Constraints

-- SUPPLIER
ALTER TABLE SUPPLIER ADD CONSTRAINT FK_SUPPLIER_PRODUCT
   FOREIGN KEY (ProductID) REFERENCES PRODUCT(ProdID);
ALTER TABLE SUPPLIER ADD CONSTRAINT FK_SUPPLIER_MANUFACT
   FOREIGN KEY (ManuCode) REFERENCES MANUFACT(ManuCode);

-- CUSTOMER
ALTER TABLE CUSTOMER ADD CONSTRAINT FK_CUSTOMER_ORDERS
   FOREIGN KEY (OrdNum) REFERENCES ORDERS(OrdNum);
ALTER TABLE CUSTOMER ADD CONSTRAINT FK_CUSTOMER_PRODUCT
   FOREIGN KEY (OrderedProdID) REFERENCES PRODUCT(ProdID);

-- STOCK
ALTER TABLE STOCK ADD CONSTRAINT FK_STOCK_MANUFACT
   FOREIGN KEY (ManuCode) REFERENCES MANUFACT(ManuCode);

-- ORDERS
ALTER TABLE ORDERS ADD CONSTRAINT FK_ORDERS_CUSTOMER
   FOREIGN KEY (CusID) REFERENCES CUSTOMER(CusID);

-- LOCATION
ALTER TABLE LOCATION ADD CONSTRAINT FK_LOCATION_SHIPPINGINFO
   FOREIGN KEY (ShipID) REFERENCES SHIPPINGINFORMATION(ShipID);

-- CATEGORY
ALTER TABLE CATEGORY ADD CONSTRAINT FK_CATEGORY_ORDERS
   FOREIGN KEY (OrdNum) REFERENCES ORDERS(OrdNum);
ALTER TABLE CATEGORY ADD CONSTRAINT FK_CATEGORY_LOCATION
   FOREIGN KEY (WareHouseLocation) REFERENCES LOCATION(WarehouseLocation);

-- TRANSACTION
ALTER TABLE TRANSACTION ADD CONSTRAINT FK_TRANSACTION_SHIPPINGINFO
   FOREIGN KEY (ShipID) REFERENCES SHIPPINGINFORMATION(ShipID);

-- ITEMS
ALTER TABLE ITEMS ADD CONSTRAINT FK_ITEMS_MANUFACT
   FOREIGN KEY (ManuCode) REFERENCES MANUFACT(ManuCode);
ALTER TABLE ITEMS ADD CONSTRAINT FK_ITEMS_STOCK
   FOREIGN KEY (StockNum) REFERENCES STOCK(StockNum);
ALTER TABLE ITEMS ADD CONSTRAINT FK_ITEMS_ORDERS
   FOREIGN KEY (OrderNum) REFERENCES ORDERS(OrdNum);

-- CATALOG
ALTER TABLE CATALOG ADD CONSTRAINT FK_CATALOG_STOCK
   FOREIGN KEY (StockNum) REFERENCES STOCK(StockNum);
ALTER TABLE CATALOG ADD CONSTRAINT FK_CATALOG_MANUFACT
   FOREIGN KEY (ManuCode) REFERENCES MANUFACT(ManuCode);

-- CALLTYPE
ALTER TABLE CALLTYPE ADD CONSTRAINT FK_CALLTYPE_CUSTOMER
   FOREIGN KEY (CusID) REFERENCES CUSTOMER(CusID);
ALTER TABLE CALLTYPE ADD CONSTRAINT FK_CALLTYPE_ORDERS
   FOREIGN KEY (OrdNum) REFERENCES ORDERS(OrdNum);

-- EMPLOYEE
ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_EMPLOYEE_CUSTOMER
   FOREIGN KEY (CusHandledByEmp) REFERENCES CUSTOMER(CusID);

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Concepts of Database Management

Authors: Philip J. Pratt, Mary Z. Last

8th edition

1285427106, 978-1285427102

More Books

Students also viewed these Databases questions

Question

Describe the purpose of the SELECT command in relational algebra.

Answered: 1 week ago