05. Online Store Database

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

CREATE TABLE Customers(
CustomerID INT PRIMARY KEY ,
[Name] VARCHAR(50) NOT NULL,
BirthDay DATE NOT NULL,
CityID INT FOREIGN KEY REFERENCES Cities(CityID)
)

CREATE TABLE Orders(
OrderID INT PRIMARY KEY ,
CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID)
)

CREATE TABLE ItemTypes(
ItemTypeID INT PRIMARY KEY ,
[Name] VARCHAR(50)
)

CREATE TABLE Items(
ItemID INT PRIMARY KEY ,
[Name] VARCHAR(50) NOT NULL,
ItemTypeID INT FOREIGN KEY REFERENCES ItemTypes(ItemTypeId)
)

/*
CREATE TABLE OrderItems(
OrderID INT FOREIGN KEY REFERENCES Orders(OrderID),
ItemID INT FOREIGN KEY REFERENCES Items(ItemID),
PRIMARY KEY (OrderID, ItemID)
)

*/

CREATE TABLE OrderItems(
OrderID INT FOREIGN KEY  REFERENCES Orders(OrderID) NOT NULL,
ItemID INT FOREIGN KEY REFERENCES Items(ItemID) NOT NULL,
PRIMARY KEY (OrderId, ItemID)
)