[Bakary] - DB MSSQL Exam - 2017-02-19 - Section 1. DDL - CT

-- Section 1. DDL - CREATE 7 TABLES
--1
CREATE TABLE Countries
(Id INT IDENTITY,
Name NVARCHAR(50) UNIQUE NOT NULL,
CONSTRAINT PK_CountryID PRIMARY KEY (Id)
)

--2
CREATE TABLE Customers
(Id INT IDENTITY,
FirstName NVARCHAR(25),
LastName NVARCHAR(25),
Gender CHAR(1) CHECK (Gender='M' OR Gender='F'),
Age INT,
PhoneNumber CHAR(10) CHECK (LEN(PhoneNumber) = 10),
CountryId INT,
CONSTRAINT PK_CustomerID PRIMARY KEY (Id),
CONSTRAINT FK_CCountriesID FOREIGN KEY (CountryId) REFERENCES Countries (Id)
)

--3 ---------------
CREATE TABLE Products
(Id INT IDENTITY,
Name NVARCHAR(25) UNIQUE,
Description NVARCHAR(250),
Recipe NVARCHAR(MAX),
Price MONEY CHECK (Price >= 0),
CONSTRAINT PK_ProductID PRIMARY KEY (Id)
)

--4
CREATE TABLE Feedbacks
(Id INT IDENTITY,
Description NVARCHAR(255),
Rate DECIMAL(10,2) CHECK (Rate>=0 AND Rate<=10),
ProductId INT,
CustomerId INT,
CONSTRAINT PK_FeedbackID PRIMARY KEY (Id),
CONSTRAINT FK_FProductsIDs FOREIGN KEY (ProductId) REFERENCES Products(Id),
CONSTRAINT FK_FCustomerId FOREIGN KEY (CustomerId) REFERENCES Customers(Id)
)

--5
CREATE TABLE Distributors
(Id INT IDENTITY,
Name NVARCHAR(25) UNIQUE,
AddressText NVARCHAR(30),
Summary NVARCHAR(200),
CountryId INT,
CONSTRAINT PK_DistributorID PRIMARY KEY (Id),
CONSTRAINT FK_DCountriesIDs FOREIGN KEY (CountryId) REFERENCES Countries(Id)
)

--6
CREATE TABLE Ingredients
(Id INT IDENTITY,
Name NVARCHAR(30),
Description NVARCHAR(200),
OriginCountryId INT,
DistributorId INT,
CONSTRAINT PK_IngredientID PRIMARY KEY (Id),
CONSTRAINT FK_ICountriesIDs FOREIGN KEY (OriginCountryId) REFERENCES Countries(Id),
CONSTRAINT FK_IDistributorsIDs FOREIGN KEY (DistributorId) REFERENCES Distributors(Id)
)

--7
CREATE TABLE ProductsIngredients
(ProductId INT,
IngredientId INT,
CONSTRAINT PK_ProductsIngredientsID PRIMARY KEY (ProductId, IngredientId),
CONSTRAINT FK_PIProductsIDs FOREIGN KEY (ProductId) REFERENCES Products(Id),
CONSTRAINT FK_PIIngredientsIDs FOREIGN KEY (IngredientId) REFERENCES Ingredients(Id)
)