1. Keys, constraints and data types

CREATE TABLE dbo.Departments (
    DeptId    INT IDENTITY(1,1) CONSTRAINT PK_Departments PRIMARY KEY,
    DeptName  NVARCHAR(100) NOT NULL CONSTRAINT UQ_Departments_Name UNIQUE
);

CREATE TABLE dbo.Employees (
    EmpId      INT IDENTITY(1,1) PRIMARY KEY,             -- clustered by default
    FullName   NVARCHAR(150) NOT NULL,
    Email      VARCHAR(200) NOT NULL UNIQUE,
    DeptId     INT NULL REFERENCES dbo.Departments(DeptId),  -- foreign key
    ManagerId  INT NULL REFERENCES dbo.Employees(EmpId),     -- self-reference
    Salary     DECIMAL(12,2) NOT NULL CHECK (Salary > 0),
    HireDate   DATE NOT NULL DEFAULT (CAST(GETDATE() AS DATE)),
    IsActive   BIT NOT NULL DEFAULT 1,
    RowVer     ROWVERSION
);