Modelleringsprojekt

CREATE TABLE Clothes (
    ClothingID INT NOT NULL,
    Brand VARCHAR(20) NOT NULL,
    Model VARCHAR(20) NOT NULL,
    Country VARCHAR(20) NOT NULL,
    ArticleNr INT NOT NULL,
    Weight INT NOT NULL, # Grams
    CSize VARCHAR(20) NOT NULL,
    SalesPrice INT NOT NULL,
    SupplierPrice INT NOT NULL,
    Color VARCHAR(20) NOT NULL,
    AmmountLeft INT NOT NULL,
    Category VARCHAR(20) NOT NULL,
    AmmountSold INT NOT NULL,

    PRIMARY KEY(ClothingID)
);

INSERT INTO Clothes VALUES(0, 'Ralph Lauren', 'Oxford Shirt', 'United States', 0, 300, 'L', 900, 500, 'White', 10, 'Shirts Men', 0);

SELECT * FROM Clothes;

CREATE TABLE Orders (
    OrderNr INT NOT NULL,
    PNr VARCHAR(20) NOT NULL, # Social security number
    TotalSum INT NOT NULL,
    DeliveryHomeAdress VARCHAR(30) NOT NULL,
    DeliveryPostAdress VARCHAR(20) NOT NULL,
    Country VARCHAR(20) NOT NULL,
    TotalWeight FLOAT NOT NULL,
    OrderDate DATE NOT NULL,
    OrderTime TIME NOT NULL,
    DiscountID INT NOT NULL,
    ClothingID INT NOT NULL,

    PRIMARY KEY(OrderNr, PNr)#,
    #FOREIGN KEY (OrderNr) REFERENCES Revenue(OrderNr),
    #FOREIGN KEY (Pnr) REFERENCES Customer(PNr),
    #FOREIGN KEY (DiscountID) REFERENCES Discount(DiscountID)
);

INSERT INTO Orders VALUES(0, '9603074833', 900, 'Gamla Infartsvägen 3b', '371 41 Karlskrona', 'Sweden', 300.0, NOW(), NOW(), 0, 0);

SELECT * FROM Orders;

CREATE TABLE Revenue (
    OrderNr INT NOT NULL,
    TotalSum INT NOT NULL,
    TransortationCost INT NOT NULL,
    SupplierPrice INT NOT NULL,
    LastReturnDate DATE NOT NULL,

    PRIMARY KEY(OrderNr)
);

INSERT INTO Revenue VALUES(0, 900, 50, 550, DATE_ADD(NOW(), INTERVAL 14 DAY));

SELECT * FROM Revenue;

CREATE TABLE Customer (
    PNr VARCHAR(20) NOT NULL,
    FName VARCHAR(20) NOT NULL,
    LName VARCHAR(20) NOT NULL,
    MobileNr VARCHAR(20) NOT NULL,
    EMail VARCHAR(30) NOT NULL,
    Gender VARCHAR(20) NOT NULL,
    HomeAdress VARCHAR(30) NOT NULL,
    PostAdress VARCHAR(20) NOT NULL,
    ShoppedSum FLOAT NOT NULL,

    CONSTRAINT PK
    PRIMARY KEY (PNr)
);

INSERT INTO Customer VALUES('9603074833', 'Anton', 'Karlsson', '0723535592', 'antonamkarlsson@hotmail.com', 'Male', 'Gamla Infartsvägen 3b', '371 41 Karlskrona', 900);

SELECT * FROM Customer;

CREATE TABLE Discount (
    DiscountID INT NOT NULL,
    CodeName VARCHAR(20) NOT NULL,
    AmountDiscount FLOAT NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL,

    PRIMARY KEY (DiscountID)
);

INSERT INTO Discount VALUES(0, 'Easter Deal', 0.9, "2019-04-18", "2019-04-22");

SELECT * FROM Discount;

CREATE TABLE BoughtClothes (
    OrderNr INT NOT NULL,
    ClothingID INT NOT NULL,

    PRIMARY KEY (OrderNr,ClothingID)#,
    #FOREIGN KEY (ClothingID) REFERENCES Clothes(ClothingID),
    #FOREIGN KEY (OrderNr) REFERENCES Orders(OrderNr)
);

INSERT INTO BoughtClothes VALUES(0, 0);

SELECT * FROM BoughtClothes;

#VIEWS
CREATE VIEW CheapClothes AS
    SELECT * FROM Clothes WHERE SalesPrice < 500;

SELECT * FROM CheapClothes;

CREATE VIEW BestSeller AS
    SELECT * FROM Clothes GROUP BY AmmountSold DESC LIMIT 10;

SELECT * FROM BestSeller;

#CustomerView
#BuyerView
#CorporateView ska göras

DROP TABLE BoughtClothes;
DROP TABLE Clothes;
DROP TABLE Orders;
DROP TABLE Revenue;
DROP TABLE Customer;
DROP TABLE Discount;