OnlineStore Database

CREATE TABLE Orders
(
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL
)

CREATE TABLE Customers
(
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Birthday DATE NOT NULL,
    CityID INT NOT NULL
)

CREATE TABLE Cities
(
    CityID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
)

ALTER TABLE Customers
ADD CONSTRAINT FK_Customer_City
FOREIGN KEY (CityID)
REFERENCES Cities(CityID)

ALTER TABLE Orders
ADD CONSTRAINT FK_Order_Customer
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)

CREATE TABLE Items
(
    ItemID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    ItemTypeID INT NOT NULL
)

CREATE TABLE ItemTypes
(
    ItemTypeID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
)

CREATE TABLE OrderItems
(
    OrderID INT NOT NULL,
    ItemID INT NOT NULL
)

ALTER TABLE Items
ADD CONSTRAINT FK_Item_ItemType
FOREIGN KEY (ItemTypeID)
REFERENCES ItemTypes(ItemTypeID)

ALTER TABLE OrderItems
ADD CONSTRAINT PK_OrderItems
PRIMARY KEY (OrderID, ItemID)

ALTER TABLE OrderItems
ADD CONSTRAINT FK_OrderItem_Order
FOREIGN KEY (OrderID)
REFERENCES Orders(OrderId)

ALTER TABLE OrderItems
ADD CONSTRAINT FK_OrderItem_Item
FOREIGN KEY (ItemID)
REFERENCES Items(ItemID)