ALTER TABLE Faculty ADD PRIMARY KEY FacNo ALTER TABLE Faculty ADD UNIQ

ALTER TABLE Faculty 
ADD PRIMARY KEY (FacNo)

ALTER TABLE Faculty 
ADD UNIQUE [Name] varchar 

ALTER TABLE Faculty
ADD FOREIGN KEY (DeanNo) REFERENCES Teacher(TchNo) ON DELETE SET NULL

ALTER TABLE Faculty
ADD CONSTRAINT Building_Legit CHECK (Building >=1 AND Building <= 10)

ALTER TABLE Faculty
ADD CONSTRAINT Fund_Suff CHECK ( Fund > 10000.00) 

ALTER TABLE Department 
ADD PRIMARY KEY (DepNo)

ALTER TABLE Department
ADD FOREIGN KEY (FacNo) REFERENCES Faculty(FacNo)

ALTER TABLE Department
ALTER COLUMN [Name] varchar NOT NULL

ALTER TABLE Department
ADD FOREIGN KEY (Head) REFERENCES Teacher(TchNo) ON DELETE SET NULL

ALTER TABLE Department
ADD CONSTRAINT Building_Legit CHECK (Building >=1 AND Building <= 10)

ALTER TABLE Department
ADD CONSTRAINT Fund_Suff CHECK ( Fund >=20000.00 AND Fund <=10000.00)

ALTER TABLE Department
ADD CONSTRAINT UC_Dep UNIQUE(FacNo,[Name])

ALTER TABLE Teacher
ADD PRIMARY KEY (TchNo)

ALTER TABLE Teacher
ADD FOREIGN KEY (DepNo) REFERENCES Department(DepNo) ON DELETE SET NULL

ALTER TABLE Teacher
ALTER COLUMN [Name] varchar NOT NULL

ALTER TABLE Teacher
ADD  ChiefNo int FOREIGN KEY REFERENCES Teacher(TchNO) ON DELETE SET NULL

ALTER TABLE Teacher
ADD CONSTRAINT CK_Hiredate_Legit CHECK(Hiredate > '19500101') 

ALTER TABLE Teacher
ALTER COLUMN Hiredate date NOT NULL

ALTER TABLE Teacher
ADD CONSTRAINT Salary_Legit CHECK ( Salary > 1000)

ALTER TABLE Teacher
ALTER COLUMN Salary decimal(6,2) NOT NULL

ALTER TABLE Teacher
ADD CONSTRAINT Commission_Def DEFAULT 0 

ALTER TABLE Teacher
ADD CONSTRAINT Commission_Legit CHECK ( Comission >= 0)

ALTER TABLE Teacher
ADD CONSTRAINT Salary_Legit CHECK (Commission + Salary > 1000 AND Commission + Salary < 3000)

ALTER TABLE Teacher
ADD CONSTRAINT Teacher_Post CHECK( Post = 'ассистент' OR Post = 'преподаватель' OR Post = 'доцент' OR Post = 'профессор')

ALTER TABLE Teacher
ADD CONSTRAINT Salary2X CHECK(Commission >= Salary * 0.5)

ALTER TABLE Teacher 
ADD CONSTRAINT ChiefNo_TchNo_Not_Equal CHECK ( ChiefNo != TchNo)