Untitled

CREATE TABLE [Menu](
	MenuItemID INT NOT NULL PRIMARY KEY,
	Name VARCHAR(50) NOT NULL,
	Description XML,
	Price DECIMAL(10, 2)
);

CREATE TABLE [Customer](
	CustomerID INT NOT NULL PRIMARY KEY,
	Name VARCHAR(50) NOT NULL,
	Email VARCHAR(30) NOT NULL,
	Phone VARCHAR(15) NOT NULL
);

CREATE TABLE [Employee](
	EmployeeID INT NOT NULL PRIMARY KEY,
	Name VARCHAR(50) NOT NULL,
	Position VARCHAR(30) NOT NULL,
	Salary VARCHAR(15) NOT NULL
);

CREATE TABLE [Table](
	TableID INT NOT NULL PRIMARY KEY,
	TableNumber INT NOT NULL,
	Capacity INT NOT NULL
);

CREATE TABLE [Shift](
	ShiftID INT NOT NULL PRIMARY KEY,
	StartTime DATETIME NOT NULL,
	EndTime DATETIME NOT NULL
);

CREATE TABLE [Reservation](
	ReservationID INT NOT NULL PRIMARY KEY,
	ReservationDateTime DATETIME NOT NULL,
	CustomerID INT NOT NULL FOREIGN KEY REFERENCES [Customer](CustomerID),
	TabelID INT NOT NULL FOREIGN KEY REFERENCES [Table](TableID)
);

CREATE TABLE [Inventory](
	IngredientID INT NOT NULL PRIMARY KEY,
	Name VARCHAR(50) NOT NULL,
	StockQuantity INT NOT NULL,
	UnitPrice DECIMAL(10, 2),
	MenuItemID INT NOT NULL FOREIGN KEY REFERENCES [Menu](MenuItemID)
);

CREATE TABLE [Order](
	OrderID INT NOT NULL PRIMARY KEY,
	OrderDateTime DATETIME NOT NULL,
	NoOfItems VARCHAR NOT NULL,
	CustomerID INT NOT NULL FOREIGN KEY REFERENCES [Customer](CustomerID),
	EmployeeID INT NOT NULL FOREIGN KEY REFERENCES [Employee](EmployeeID),
	IngredientID INT NOT NULL FOREIGN KEY REFERENCES [Inventory](IngredientID)
);

CREATE TABLE [Bill](
	BillNo INT NOT NULL PRIMARY KEY,
	TotalAmount DECIMAL(10, 2) NOT NULL,
	OrderID INT NOT NULL FOREIGN KEY REFERENCES [Order](OrderId),
	CustomerID INT NOT NULL FOREIGN KEY REFERENCES [Customer](CustomerID),
	EmployeeID INT NOT NULL FOREIGN KEY REFERENCES [Employee](EmployeeID)
);

CREATE TABLE [EmployeeShift](
	EmployeeShiftID INT NOT NULL PRIMARY KEY,
	Date DATETIME NOT NULL,
	ShiftID INT NOT NULL FOREIGN KEY REFERENCES [Shift](ShiftID),
	EmployeeID INT NOT NULL FOREIGN KEY REFERENCES [Employee](EmployeeID)
);