Create database University2 use University2 Create table FACULTY FacPK

  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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
Create database University2
use University2
Create table FACULTY (
FacPK int PRIMARY KEY,
[Name] varchar(50) UNIQUE NOT NULL,
DeanFK int,
Building char(2)
CHECK(Building BETWEEN 1 AND 10),
Fund decimal(9,2)
CHECK (Fund > 100000.00)
)
/*FOREIGN KEY (DeanFK) REFERENCES TEACHER(TchPK) ON DELETE SET NULL,*/
Create table DEPARTMENT (
DepPK int PRIMARY KEY,
FacFK int,
[Name] varchar(50) UNIQUE NOT NULL,
HeadFK int,
Building char(2)
CHECK(Building BETWEEN 1 AND 10),
Fund decimal(8,2)
CHECK (Fund > 20000.00 AND FUND < 100000.00)
)
Create table TEACHER (
TchPK int PRIMARY KEY,
DepFK int,
[Name] varchar(50) UNIQUE NOT NULL,
Post varchar(15)
CHECK(Post = 'ассистент' OR Post = 'преподаватель' OR Post = 'доцент' OR Post = 'профессор'),
Tel char(7),
Hiredate date NOT NULL
Check(Hiredate > '01.01.1950'),
Salary decimal(6,2) NOT NULL
Check(Salary > 1000),
Commission decimal(6,2) default 0
Check (Commission >= 0),
ChiefFK int
)
Create table SGROUP (
GrpPK int PRIMARY KEY,
DepFK int,
Course decimal(1)
Check(Course >= 1 AND Course <= 6 ),
Num decimal(3)
CHECK(Num BETWEEN 0 AND 700),
Quantity decimal(2)
CHECK(Quantity BETWEEN 1 AND 50),
Curator int,
Rating decimal(3) default 0
CHECK(Rating BETWEEN 0 AND 100),
)
Create table [SUBJECT] (
SbjPK int PRIMARY KEY,
[Name] varchar(50) UNIQUE NOT NULL
)
Create table ROOM (
RomPK int PRIMARY KEY,
Num decimal(4) NOT NULL,
Seats decimal(3)
Check(Seats BETWEEN 1 AND 300),
[Floor] decimal(2)
Check([Floor] BETWEEN 1 AND 16),
Building char(5) NOT NULL
CHECK(Building BETWEEN 1 AND 10),
)
Create table LECTURE (
TchFK int,
GrpFK int,
SbjFK int,
RomFK int,
[Type] varchar(15) NOT NULL
Check([Type] = 'лекция' OR [Type] = 'лабораторная' OR [Type] = 'семинар' OR [Type] = 'практика' ),
[Day] char(3) NOT NULL
Check([Day] = 'пон' OR [Day] = 'втр' OR [Day] = 'срд' OR [Day] = 'чет' OR [Day] = 'пят' OR [Day] = 'суб' OR [Day] = 'вск'),
[Week] decimal(1) NOT NULL
Check([Week] = 1 OR [Week] = 2),
Lesson decimal(1) NOT NULL
Check(Lesson BETWEEN 1 AND 8)
FOREIGN KEY (TchFK) REFERENCES TEACHER(TchPK) ON DELETE SET NULL,
FOREIGN KEY (GrpFK) REFERENCES SGROUP(GrpPK) ON DELETE CASCADE,
FOREIGN KEY (SbjFK) REFERENCES [SUBJECT](SbjPK) ON DELETE CASCADE ,
FOREIGN KEY (RomFK) REFERENCES ROOM(RomPK) ON DELETE SET NULL
)
/*Добавление ограничений в FACULTY*/
ALTER TABLE FACULTY
ADD CONSTRAINT fk_faculty_dep
FOREIGN KEY (DeanFK)
REFERENCES Teacher(TchPK);
/*Добавление ограничений в DEPARTMENT*/
ALTER TABLE DEPARTMENT
ADD CONSTRAINT fk_dep_fac
FOREIGN KEY (FacFK)
REFERENCES FACULTY(FacPK);
ALTER TABLE DEPARTMENT
ADD CONSTRAINT fk_dep_teach
FOREIGN KEY (HeadFK )
REFERENCES TEACHER(TchPK);
ALTER TABLE DEPARTMENT
ADD CONSTRAINT Name_FK_Uniq UNIQUE (FacFK, [Name]);
/*Добавление ограничений в TEACHER*/
ALTER TABLE TEACHER
ADD CONSTRAINT fk_teach_dep
FOREIGN KEY (DepFK)
REFERENCES DEPARTMENT(DepPK);
ALTER TABLE TEACHER
ADD CONSTRAINT fk_teach_teach
FOREIGN KEY (ChiefFK )
REFERENCES TEACHER(TchPK );
/*Добавление ограничений в SGROUP*/
ALTER TABLE SGROUP
ADD CONSTRAINT fk_skgr_dep
FOREIGN KEY (DepFK)
REFERENCES DEPARTMENT(DepPK);
ALTER TABLE SGROUP
ADD CONSTRAINT fk_skgr_teach
FOREIGN KEY (Curator)
REFERENCES TEACHER(TchPK);
ALTER TABLE SGROUP
ADD CONSTRAINT Dep_FK_Num_Uniq UNIQUE (DepFK, Num);
ALTER TABLE SGROUP
ADD CONSTRAINT Dep_FK_Curator_Uniq UNIQUE (DepFK, Curator);
/*Добавление ограничений в ROOM*/
ALTER TABLE ROOM
ADD CONSTRAINT Num_Building_Uniq UNIQUE (Num, Building);
/*Добавление ограничений в LECTURE */
ALTER TABLE LECTURE
ADD CONSTRAINT GrpFK_Day_Week_Lesson_Uniq UNIQUE (GrpFK, [Day], [Week], Lesson);
ALTER TABLE LECTURE
ADD CONSTRAINT TchFK_Day_Week_Lesson_Uniq UNIQUE (TchFK, [Day], [Week], Lesson);