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)
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)