· 6 years ago · Jan 12, 2020, 02:12 AM
1if not exists (select 1 from sys.schemas where name='ao') exec('create schema [ao]');
2go
3if object_id ('PK_Vreme_id_vreme', 'PK') is not null
4 alter table ao.Vreme
5 drop constraint PK_Vreme_id_vreme;
6go
7
8if object_id ('PK_Kurs_Facts_id_kursa', 'PK') is not null
9 alter table ao.Kurs_Facts
10 drop constraint PK_Kurs_Facts_id_kursa;
11go
12if object_id ('PK_Otvaranja_id_otvaranja', 'PK') is not null
13 alter table ao.Otvaranja
14 drop constraint PK_Otvaranja_id_otvaranja;
15go
16if object_id ('PK_Materijali_id_materijala', 'PK') is not null
17 alter table ao.Materijali
18 drop constraint PK_Materijali_id_materijala;
19go
20if object_id ('PK_Zadaci_id_zadatka', 'PK') is not null
21 alter table ao.Zadaci
22 drop constraint PK_Zadaci_id_zadatka;
23go
24if object_id ('PK_Prijava_id_prijava', 'PK') is not null
25 alter table ao.Prijava
26 drop constraint PK_Prijava_id_prijava;
27go
28
29
30
31if object_id ('FK_Kurs_Facts_Otvaranja', 'FK') is not null
32 alter table ao.Kurs_Facts
33 drop constraint FK_Kurs_Facts_Otvaranja;
34go
35if object_id ('FK_Kurs_Facts_Materijali', 'FK') is not null
36 alter table ao.Kurs_Facts
37 drop constraint FK_Kurs_Facts_Materijali;
38go
39if object_id ('FK_Kurs_Facts_Zadaci', 'FK') is not null
40 alter table ao.Kurs_Facts
41 drop constraint FK_Kurs_Facts_Zadaci;
42go
43if object_id ('FK_Kurs_Facts_Student', 'FK') is not null
44 alter table ao.Kurs_Facts
45 drop constraint FK_Kurs_Facts_Student;
46go
47if object_id ('FK_Kurs_Facts_Prijava', 'FK') is not null
48 alter table ao.Kurs_Facts
49 drop constraint FK_Kurs_Facts_Prijava;
50go
51if object_id ('FK_Kurs_Facts_Vreme', 'FK') is not null
52 alter table ao.Kurs_Facts
53 drop constraint FK_Kurs_Facts_Vreme;
54go
55
56if object_id('ao.Kurs_Facts', 'U') is not null
57begin
58 drop table ao.Kurs_Facts;
59end
60go
61
62if object_id('ao.Vreme', 'U') is not null
63drop table ao.Vreme;
64go
65
66if object_id('ao.Otvaranja', 'U') is not null
67begin
68 drop table ao.Otvaranja;
69end
70go
71
72
73if object_id('ao.Materijali', 'U') is not null
74begin
75 drop table ao.Materijali;
76end
77go
78
79
80if object_id('ao.Zadaci', 'U') is not null
81begin
82 drop table ao.Zadaci;
83end
84go
85if object_id('ao.Prijava', 'U') is not null
86begin
87 drop table ao.Prijava;
88end
89go
90
91
92if object_id ('ao.Vreme_sequence', 'SO') is not null
93 drop sequence ao.Vreme_sequence;
94go
95
96
97if object_id ('ao.Kurs_sequence', 'SO') is not null
98 drop sequence ao.Kurs_sequence;
99go
100
101
102if object_id('ao.Otvaranja', 'U') is null
103begin
104 create table ao.Otvaranja (
105 id_otvaranja int,
106 broj_klikova int,
107 constraint PK_Otvaranja_id_otvaranja primary key (id_otvaranja)
108 )
109end
110go
111if object_id('ao.Materijali', 'U') is null
112begin
113 create table ao.Materijali (
114 id_materijala int ,
115 tip varchar(45),
116 sedmica_od int,
117 sedmica_do int,
118 constraint PK_Materijali_id_materijala primary key (id_materijala)
119 )
120end
121go
122if object_id('ao.Zadaci', 'U') is null
123begin
124 create table ao.Zadaci (
125 id_zadatka int,
126 tip varchar(45),
127 datum int,
128 vrednovanje int,
129 constraint PK_Zadaci_id_zadatka primary key (id_zadatka)
130 )
131end
132go
133if object_id('ao.Student', 'U') is not null
134begin
135 alter table ao.Student
136 drop constraint PK_Student_id_student;
137 drop table ao.Student;
138end
139if object_id('ao.Student', 'U') is null
140begin
141 create table ao.Student (
142 id_student int,
143 pokusaja int,
144 imd_indeks varchar(16),
145 region varchar(45),
146 konacni_rezultat varchar(45),
147 constraint PK_Student_id_student primary key (id_student)
148 )
149end
150go
151if object_id('ao.Prijava', 'U') is null
152begin
153 create table ao.Prijava (
154 id_prijave int,
155 datum_prijavljivanja int,
156 datum_odjavljivanja int,
157 constraint PK_Prijava_id_prijava primary key (id_prijave)
158 )
159end
160go
161create sequence ao.Vreme_sequence as int
162start with 1
163minvalue 1
164increment by 1
165cycle
166go
167create table ao.Vreme(
168id_vreme int default (next value for ao.Vreme_sequence),
169datum date,
170danUNedelji varchar(20),
171constraint PK_Vreme_id_vreme primary key (id_vreme)
172)
173
174go
175 DECLARE @start DATE = '2016-01-01'
176 WHILE @start < GETDATE()
177 BEGIN
178 INSERT INTO ao.Vreme(datum, danUNedelji)
179 VALUES(@start,
180 DATENAME(dw,@start));
181
182 SET @start = DATEADD(dd,1,@start)
183 END
184go
185create sequence ao.Kurs_sequence as int
186start with 1
187minvalue 1
188increment by 1
189cycle
190go
191if object_id('ao.Kurs_Facts', 'U') is null
192begin
193 create table ao.Kurs_Facts (
194 id_kursa int default (next value for ao.Kurs_sequence),
195 sifra_modula varchar(45),
196 sifra_prezentacije varchar(45),
197 id_otvaranja int,
198 id_materijala int,
199 id_zadatka int,
200 id_student int,
201 id_prijave int,
202 id_vreme int,
203 trajanje date,
204 constraint PK_Kurs_Facts_id_kursa primary key (id_kursa),
205 constraint FK_Kurs_Facts_Otvaranja foreign key(id_otvaranja) references ao.Otvaranja(id_otvaranja),
206 constraint FK_Kurs_Facts_Materijali foreign key(id_materijala) references ao.Materijali(id_materijala),
207 constraint FK_Kurs_Facts_Zadaci foreign key(id_zadatka) references ao.Zadaci(id_zadatka),
208 constraint FK_Kurs_Facts_Student foreign key(id_student) references ao.Student(id_student),
209 constraint FK_Kurs_Facts_Prijava foreign key(id_prijave) references ao.Prijava(id_prijave),
210 constraint FK_Kurs_Facts_Vreme foreign key(id_vreme) references ao.Vreme(id_vreme),
211 )
212end