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

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
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)