ALTER TABLE Faculty 
ADD PRIMARY KEY (FacNo)

ALTER TABLE Faculty 
ADD UNIQUE ([Name]) 
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)

ALTER TABLE SGroup
ADD PRIMARY KEY (GrpNo)

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

ALTER TABLE SGroup
ADD CONSTRAINT Course_Legit CHECK ( Course >=1 AND Course <= 6)

ALTER TABLE SGroup
ADD CONSTRAINT Num_Legit CHECK( Num >0 AND Num <=700)

ALTER TABLE SGroup
ADD CONSTRAINT Quantity_Legit CHECK( Quantity >=1 AND Quantity <=50)

ALTER TABLE SGroup
ADD FOREIGN KEY (Curator) REFERENCES Teacher (TchNo) ON DELETE SET NULL

ALTER TABLE SGroup
ADD CONSTRAINT Rating_Def DEFAULT 0

ALTER TABLE SGroup
ADD CONSTRAINT Rating_Legit CHECK ( Rating BETWEEN 0 AND 100)

ALTER TABLE SGroup
ADD CONSTRAINT Unique_Group UNIQUE(DepNo,Num)

ALTER TABLE SGroup 
ADD CONSTRAINT Unique_Cur UNIQUE(DepNo,Curator)

ALTER TABLE [Subject]
ADD PRIMARY KEY (SbjNo)

ALTER TABLE [Subject]
ADD UNIQUE ([Name])  varchar 

ALTER TABLE Subject
ALTER COLUMN Name varchar NOT NULL

ALTER TABLE Room
ADD PRIMARY KEY (RomNo)

ALTER TABLE Room
ALTER COLUMN Num decimal(4,0) NOT NULL

ALTER TABLE Room
ADD CONSTRAINT Seats_Legit CHECK ( Seats BETWEEN 1 AND 300)

ALTER TABLE Room
ADD CONSTRAINT Floor_Legit CHECK( Floor BETWEEN 1 AND 16 )

ALTER TABLE Room
ALTER COLUMN Building char(5) NOT NULL

ALTER TABLE Room
ADD CONSTRAINT Building_Legit CHECK( Building = '1' OR Building ='2' OR Building ='3' OR Building = '4' OR Building = '5' OR Building = '6' OR Building = '7' OR Building = '8' OR Building ='9' OR Building = '10' )

ALTER TABLE Room
ADD CONSTRAINT Unique_Room UNIQUE(Num,Building)

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

ALTER TABLE Lecture
ADD FOREIGN KEY(GrpNo) REFERENCES SGroup(GrpNo) ON DELETE CASCADE

ALTER TABLE Lecture
ADD FOREIGN KEY(SbjNo) REFERENCES Subject(SbjNo) ON DELETE CASCADE

ALTER TABLE Lecture
ADD FOREIGN KEY(RomNo) REFERENCES Room(RomNo) ON DELETE SET NULL

ALTER TABLE Lecture
ADD CONSTRAINT Type_Lec CHECK([Type] = 'лекция' OR [Type] = 'лабораторная' OR [Type] = 'семинар' OR [Type] = 'практика' )

ALTER TABLE Lecture
ADD CONSTRAINT Day_What CHECK([Day] = 'пон' OR [Day] = 'втр' OR [Day] = 'срд' OR [Day] = 'чет' OR [Day] = 'пят' OR [Day] = 'суб' OR [Day] = 'вск')

ALTER TABLE Lecture
ADD CONSTRAINT Week_Type CHECK ([Week] = 1 OR [Week] = 2)

ALTER TABLE Lecture
ADD CONSTRAINT Lesson_Num CHECK (Lesson BETWEEN 1 AND 8)

ALTER TABLE Lecture
ALTER COLUMN [Type] varchar(25) NOT NULL

ALTER TABLE Lecture
ALTER COLUMN [Day] char(3) NOT NULL

ALTER TABLE Lecture
ALTER COLUMN [Week] decimal(1,0) NOT NULL

ALTER TABLE Lecture
ALTER COLUMN [Lesson] decimal(1,0) NOT NULL

ALTER TABLE Lecture
ADD CONSTRAINT First1 UNIQUE(GrpNo,[Day],[Week],Lesson)

ALTER TABLE Lecture
ADD CONSTRAINT Second2 UNIQUE(TchNo,[Day],[Week],Lesson)