CREATE TABLE CUSTOMER(
    CID varchar(10) PRIMARY KEY,
    isCompany BIT,
    "name" varchar(30) NOT NULL,
    surname varchar(30) NOT NULL,
    country varchar(30),
    City varchar(30),
    Street varchar(30),
    BNR int,
    postalcode CHAR(5),
    email varchar(30),
    phone varchar(15),
    );


CREATE TABLE EMPLOYEE(
    EID varchar(10) PRIMARY KEY,
    "name" varchar(30) NOT NULL,
    surname varchar(30) NOT NULL,
    hireDate date,
    position varchar(30 )NOT NULL,
    salary float,
    bankacc numeric(26),
    phone char(10),
    email varchar(50)
    );

CREATE TABLE "ORDER"(
    OrderID varchar(10) PRIMARY KEY,
    "date" DATETIME,
    totalprice float,
    CID varchar(10),
    FOREIGN KEY (CID) REFERENCES CUSTOMER,
    EID varchar(10),
    FOREIGN KEY (EID) REFERENCES EMPLOYEE,
    );

CREATE TABLE SHIPMENT(
    trackingnumber varchar(30) PRIMARY KEY,
    shipmentdate datetime,
    deliverydate datetime,
    CID varchar(10),
    FOREIGN KEY (CID) REFERENCES CUSTOMER,
    EID varchar(10),
    FOREIGN KEY (EID) REFERENCES EMPLOYEE,
    );
CREATE TABLE CARPART(
    PartSerialID varchar(30) PRIMARY KEY,
    "OEM" varchar(30) UNIQUE,
    "name" varchar(30),
    "brandname" varchar(30),
    carmanufacturer varchar(30),
    model varchar(30),
    "version" varchar(30),
    "year" int,
    engineversion varchar(30),
    gastype varchar(30),
    price float ,
    );

 CREATE TABLE PARTSTORAGE(
    partid varchar(30) PRIMARY KEY,
    place varchar(5),
    quantity int,
    sellprice float NOT NULL,
    PartSerialID varchar(30),
    FOREIGN KEY (PartSerialID) REFERENCES CARPART,
    );

CREATE TABLE OrderDetails(
    QuantityOrdered int,
    QuantitySent int,
    UnitPrice float NOT NULL,
    OrderID varchar(10),
    partid varchar(30),
    FOREIGN KEY (OrderID) REFERENCES "ORDER",
    FOREIGN KEY (partid) REFERENCES "PARTSTORAGE",
    );
CREATE TABLE SupplierOrder(
    SupOrderID varchar(30) PRIMARY KEY,
    "date" DATETIME,
    totalprice float NOT NULL,
    EID varchar(10),
    CID varchar(10),
    FOREIGN KEY (EID) REFERENCES EMPLOYEE,
    FOREIGN KEY (CID) REFERENCES CUSTOMER,

    );

CREATE TABLE SupOrderDetails(
    quantitysent int,
    quantityordered int,
    price float,
    SupOrderID varchar(30),
    FOREIGN KEY (SUpOrderid) REFERENCES SupplierOrder,
    PartSerialID varchar(30),
    FOREIGN KEY(Partserialid) REFERENCES CarPArt,

    );

CREATE TABLE DELIVERY(
    deliveryID varchar(30) PRIMARY KEY,
    shipmentdate date,
    deliverydate date,
    SupoOrderID varchar(30),
    FOREIGN KEY (SupoOrderID) REFERENCES SupplierOrder,
    );

CREATE TABLE DELIVERYDETAILS(
    quantity int,
    PartSerialNumber varchar(30),
    FOREIGN KEY (PartSerialNumber) REFERENCES CARPART,
    deliveryid varchar(30),
    FOREIGN KEY (deliveryid) REFERENCES DELIVERY,
    );

CREATE TABLE SUPPLIER(
    SUpplierid varchar(30) PRIMARY KEY,
    "name" varchar(30),
    "country" varchar(30),
    city varchar(30),
    street varchar(30),
    buildingnumber int,
    phone varchar(30),
    email varchar(30),
    bankacc varchar(30),
    );