

Create Schema zmac;
use zmac;
CREATE  TABLE CUSTOMER(
	CustomerID	int		NOT NULL,
	FirstName	varchar(40)		NOT NULL,
	LastName	varchar(40)		NOT NULL,
    City		varchar(40),
	Country		varchar(40),
    Phone       varchar(30),
    
	CONSTRAINT 			CUSTOMER_PK 	PRIMARY KEY(CustomerID)
	);
    
    CREATE  TABLE ORDERS(
	OrderID	int		NOT NULL,
	OrderDate varchar(30),
    CustomerID int,
    TotalAmount decimal(12,2),
    OrderNumber varchar(10),
    
	CONSTRAINT 			ORDER_PK 	PRIMARY KEY(OrderID),
    CONSTRAINT 			CUST_ORDER_FK	FOREIGN KEY(CustomerID)
							REFERENCES Customer(CustomerID)
							ON UPDATE CASCADE
		);
              CREATE  TABLE SUPPLIER(
	SupplierID	int		NOT NULL,
	CompanyName varchar(50),
    ContactName varchar(50),
    City varchar(40),
    Country varchar(40),
    Phone varchar(30),
    Fax varchar(30),
    CONSTRAINT 			SUPPLIER_PK 	PRIMARY KEY(SupplierID)
		);
                 CREATE  TABLE PRODUCT(
	ProductID	int		NOT NULL,
	ProductName varchar(50),
    SupplierID int,
    UnitPrice decimal(12,2),
    Package varchar(30),
    CONSTRAINT 			PRODUCT_PK 	PRIMARY KEY(ProductID),
       CONSTRAINT 			PRODUCT_SUPPLIER_FK	FOREIGN KEY(SupplierID)
							REFERENCES SUPPLIER(SupplierID)
							ON UPDATE CASCADE
		);
    
       CREATE  TABLE ORDERITEM(
	OrderItemID	int		NOT NULL,
	OrderID	int,
    ProductID	int,
    UnitPrice decimal(12,2),
	Quantity int,
	CONSTRAINT 			ORDERITEM_PK 	PRIMARY KEY(OrderItemID),
    CONSTRAINT 			ORDERITEM_ORDER_FK	FOREIGN KEY(OrderID)
							REFERENCES ORDERS(OrderID)
							ON UPDATE CASCADE,
        CONSTRAINT 			ORDERITEM_PRODUCT_FK	FOREIGN KEY(ProductID)
							REFERENCES PRODUCT(ProductID)
							ON UPDATE CASCADE                     
		);
        

        
      
    