r7D8QPWN

1DROP SCHEMA Test;
2CREATE SCHEMA Test;
3
4-- Create a new table called '[KURS]' in schema '[Vezba]'
5-- Drop the table if it already exists
6IF OBJECT_ID('[Vezba].[KURS]', 'U') IS NOT NULL
7DROP TABLE [Vezba].[KURS]
8GO
9-- Create the table in the specified schema
10CREATE TABLE [Vezba].[KURS]
11(
12    [kursID] NUMERIC NOT NULL,
13    -- Primary Key column
14    [opis] NVARCHAR(50),
15    [cena] NUMERIC,
16    [preduslov] NUMERIC,
17    CONSTRAINT PK_Kurs_kursID PRIMARY KEY(kursID),
18    CONSTRAINT FK_Kurs_Kurs FOREIGN KEY(preduslov)
19        REFERENCES Vezba.KURS(kursID)
20);
21GO
22
23
24-- Create a new table called '[INSTRUKTOR]' in schema '[Vezba]'
25-- Drop the table if it already exists
26IF OBJECT_ID('[Vezba].[INSTRUKTOR]', 'U') IS NOT NULL
27DROP TABLE [Vezba].[INSTRUKTOR]
28GO
29-- Create the table in the specified schema
30CREATE TABLE [Vezba].[INSTRUKTOR]
31(
32    [instrID] NUMERIC NOT NULL,
33    -- Primary Key column
34    [ime] NVARCHAR(25) NOT NULL,
35    [prz] NVARCHAR(25) NOT NULL,
36    [adresa] VARCHAR(50),
37    [PTT] VARCHAR(5),
38    [telefon] VARCHAR(5),
39    [pol] char(1)
40        CONSTRAINT PK_Instruktor_instrID PRIMARY KEY(instrID),
41    CONSTRAINT CHK_Lenght_PTT CHECK(LEN(PTT)=5),
42    CONSTRAINT CHK_Instruktor_pol CHECK(pol in ('M','Z'))
43);
44GO
45
46-- Create a new table called '[TIPOCENE]' in schema '[Vezba]'
47-- Drop the table if it already exists
48IF OBJECT_ID('[Vezba].[TIPOCENE]', 'U') IS NOT NULL
49DROP TABLE [Vezba].[TIPOCENE]
50GO
51-- Create the table in the specified schema
52CREATE TABLE [Vezba].[TIPOCENE]
53(
54    [tipoID] CHAR(2) NOT NULL,
55    -- Primary Key column
56    [opis] NVARCHAR(50) NOT NULL,
57    CONSTRAINT PK_Tipocene_tipoID PRIMARY KEY(tipoID),
58    CONSTRAINT UQ_Tipocene_opis UNIQUE(opis)
59);
60GO
61
62-- Create a new table called '[STUDENT]' in schema '[Vezba]'
63-- Drop the table if it already exists
64IF OBJECT_ID('[Vezba].[STUDENT]', 'U') IS NOT NULL
65DROP TABLE [Vezba].[STUDENT]
66GO
67-- Create the table in the specified schema
68CREATE TABLE [Vezba].[STUDENT]
69(
70    [studentID] NUMERIC NOT NULL,
71    -- Primary Key column
72    [ime] NVARCHAR(25) NOT NULL,
73    [prz] NVARCHAR(25) NOT NULL,
74    [adresa] NVARCHAR(50),
75    [PTT] NVARCHAR(5),
76    [telefon] NVARCHAR(20),
77    [datumprijave] DATE DEFAULT(GetDate()),
78    CONSTRAINT PK_Student_studentID PRIMARY KEY(studentID),
79    CONSTRAINT CHK_Student_PTT CHECK(LEN(PTT)=5)
80);
81GO
82
83-- Create a new table called '[SEKCIJA]' in schema '[Vezba]'
84-- Drop the table if it already exists
85IF OBJECT_ID('[Vezba].[SEKCIJA]', 'U') IS NOT NULL
86DROP TABLE [Vezba].[SEKCIJA]
87GO
88-- Create the table in the specified schema
89CREATE TABLE [Vezba].[SEKCIJA]
90(
91    [sekcijaID] NUMERIC,
92    -- Primary Key column
93    [kursID] NUMERIC NOT NULL,
94    [sekcijaBr] NUMERIC NOT NULL,
95    [datumpocetka] DATE,
96    [lokacija] NVARCHAR(50),
97    [instrID] NUMERIC NOT NULL,
98    [kapacitet] NUMERIC,
99    CONSTRAINT PK_Sekcija_sekcijaID PRIMARY KEY (sekcijaID),
100    CONSTRAINT UQ_Sekcija_sekcijaBR UNIQUE(sekcijaID),
101    CONSTRAINT CHK_Sekcija_kapacitet CHECK(kapacitet>5),
102    CONSTRAiNT FK_Sekcija_Kurs FOREIGN KEY(kursID)
103        REFERENCES Vezba.KURS(kursID),
104    CONSTRAINT FK_Sekcija_Instruktor FOREIGN KEY(instrID)
105        REFERENCES Vezba.INSTRUKTOR(instrID)
106);
107GO
108
109-- Create a new table called '[UPISANI]' in schema '[Vezba]'
110-- Drop the table if it already exists
111IF OBJECT_ID('[Vezba].[UPISANI]', 'U') IS NOT NULL
112DROP TABLE [Vezba].[UPISANI]
113GO
114-- Create the table in the specified schema
115CREATE TABLE [Vezba].[UPISANI]
116(
117    [studentID] NUMERIC NOT NULL,
118    -- Primary Key column
119    [sekcijaID] NUMERIC NOT NULL,
120    [datumupisa] DATE NOT NULL DEFAULT(GetDate()),
121    [konacnaocena] NUMERIC,
122    CONSTRAINT PK_studentID_sekcijaID PRIMARY KEY(studentID,sekcijaID),
123    CONSTRAINT FK_UPISANI_STUDENT FOREIGN KEY(studentID)
124        REFERENCES Vezba.STUDENT(studentID),
125    CONSTRAINT FK_UPISANI_SEKCIJA FOREIGN KEY(sekcijaID)
126        REFERENCES Vezba.SEKCIJA(sekcijaID)
127);
128GO
129
130-- Create a new table called '[ELEMENTIOCENE]' in schema '[Vezba]'
131-- Drop the table if it already exists
132IF OBJECT_ID('[Vezba].[ELEMENTIOCENE]', 'U') IS NOT NULL
133DROP TABLE [Vezba].[ELEMENTIOCENE]
134GO
135-- Create the table in the specified schema
136CREATE TABLE [Vezba].[ELEMENTIOCENE]
137(
138    [sekcijaID] NUMERIC NOT NULL,
139    -- Primary Key column
140    [tipoID] CHAR(2) NOT NULL,
141    [brposekciji] NUMERIC NOT NULL,
142    [tezina] NUMERIC NOT NULL,
143    [odbacinajmanju] CHAR(1) NOT NULL,
144    CONSTRAINT PK_sekcijaID_tipoID PRIMARY KEY(sekcijaID,tipoID),
145    CONSTRAINT CHK_Elementocene_odbacinajmanju CHECK(odbacinajmanju in (0,1)),
146    CONSTRAINT FK_ELEMENTOCENE_SEKCIJA FOREIGN KEY(sekcijaID)
147        REFERENCES Vezba.SEKCIJA(sekcijaID),
148    CONSTRAINT FK_ELEMENTOCENE_TIPOCENE FOREIGN KEY(tipoID)
149        REFERENCES Vezba.TIPOCENE(tipoID)
150);
151GO
152
153-- Create a new table called '[OCENA]' in schema '[Vezba]'
154-- Drop the table if it already exists
155IF OBJECT_ID('[Vezba].[OCENA]', 'U') IS NOT NULL
156DROP TABLE [Vezba].[OCENA]
157GO
158-- Create the table in the specified schema
159CREATE TABLE [Vezba].[OCENA]
160(
161    [studentID] NUMERIC NOT NULL,
162    -- Primary Key column
163    [sekcijaID] NUMERIC NOT NULL,
164    [tipoID] CHAR(2) NOT NULL,
165    [rBrtip] NUMERIC NOT NULL,
166    [bodovi] NUMERIC NOT NULL,
167    [datumpolaganja] DATE NOT NULL DEFAULT(GetDate()),
168    [komentar] NVARCHAR(20),
169    CONSTRAINT PK_studentID_sekcijaID_tipoID_rBrtip PRIMARY KEY(studentID,sekcijaID,tipoID,rBrtip),
170    CONSTRAINT FK_OCENA_UPISANI FOREIGN KEY (studentID,sekcijaID)
171        REFERENCES Vezba.UPISANI(studentID,sekcijaID),
172    CONSTRAINT FK_OCENA_ELEMENTIOCENE FOREIGN KEY(sekcijaID,tipoID)
173        REFERENCES Vezba.ELEMENTIOCENE(sekcijaID,tipoID)
174);
175GO
176IF OBJECT_ID('Vezba.kursID','SO') IS NOT NULL
177    DROP SEQUENCE Vezba.kursID;
178
179CREATE SEQUENCE Vezba.kursID
180    as numeric
181    start with 0
182    increment by 1
183;
184
185ALTER TABLE Vezba.KURS
186    ADD CONSTRAINT DFT_KURSID DEFAULT (next value for Vezba.kursID) for kursID;
187
188IF OBJECT_ID('Vezba.instrID','SO') IS NOT NULL
189    DROP SEQUENCE Vezba.instrID;
190
191CREATE SEQUENCE Vezba.instrID
192    as numeric
193    start with 0
194    increment by 1
195;
196
197ALTER TABLE Vezba.INSTRUKTOR
198    ADD CONSTRAINT DFT_INSTRID DEFAULT(next value for Vezba.instrID) for instrID;
199
200IF OBJECT_ID('Vezba.studentID','SO') IS NOT NULL
201    DROP SEQUENCE Vezba.studentID;
202
203CREATE SEQUENCE Vezba.studentID
204    as numeric
205    start with 0
206    increment by 1
207;
208
209ALTER TABLE Vezba.STUDENT
210    ADD CONSTRAINT DFT_STUDENTID DEFAULT(next value for Vezba.StudentID) for studentID;
211
212IF OBJECT_ID('Vezba.sekcijaIDr','SO') IS NOT NULL
213    DROP SEQUENCE Vezba.sekcijaID;
214
215CREATE SEQUENCE Vezba.sekcijaID
216    as numeric
217    start with 0
218    increment by 1
219;
220
221ALTER TABLE Vezba.SEKCIJA
222    ADD CONSTRAINT DFT_SEKCIJAID DEFAULT(next value for Vezba.sekcijaID) for sekcijaID;
223
224
225-- Create a new table called '[Kurs]' in schema '[Test]'
226-- Drop the table if it already exists
227IF OBJECT_ID('[Test].[Kurs]', 'U') IS NOT NULL
228DROP TABLE [Test].[Kurs]
229GO
230-- Create the table in the specified schema
231CREATE TABLE [Test].[Kurs]
232(
233    [Id] INT NOT NULL PRIMARY KEY, -- Primary Key column
234    [ColumnName2] NVARCHAR(50) NOT NULL,
235    [ColumnName3] NVARCHAR(50) NOT NULL
236    -- Specify more columns here
237);
238GO