· 6 years ago · Oct 17, 2019, 06:32 AM
1/* ---------------------------------------------------- */
2/* Generated by Enterprise Architect Version 14.0 */
3/* Created On : 10-říj-2019 8:23:19 */
4/* DBMS : SQL Server 2012 */
5/* ---------------------------------------------------- */
6
7/* Drop Foreign Key Constraints */
8
9IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_Garantuje_Ucitel]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
10ALTER TABLE [Predmet] DROP CONSTRAINT [FK_Garantuje_Ucitel]
11GO
12
13IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_Studuje_Obor]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
14ALTER TABLE [Student] DROP CONSTRAINT [FK_Studuje_Obor]
15GO
16
17IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_Student_Zapis]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
18ALTER TABLE [Zapis] DROP CONSTRAINT [FK_Student_Zapis]
19GO
20
21IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_Predmet_Zapis]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
22ALTER TABLE [Zapis] DROP CONSTRAINT [FK_Predmet_Zapis]
23GO
24
25/* Drop Tables */
26
27IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[Obor]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
28DROP TABLE [Obor]
29GO
30
31IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[Predmet]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
32DROP TABLE [Predmet]
33GO
34
35IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[Student]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
36DROP TABLE [Student]
37GO
38
39IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[Ucitel]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
40DROP TABLE [Ucitel]
41GO
42
43IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[Zapis]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
44DROP TABLE [Zapis]
45GO
46
47/* Create Tables */
48
49CREATE TABLE [Obor]
50(
51 [OborID] int NOT NULL,
52 [Nazev] varchar(50) NULL
53)
54GO
55
56CREATE TABLE [Predmet]
57(
58 [PredmetID] int NOT NULL,
59 [Nazev] varchar(50) NULL,
60 [Mistnost] varchar(6) NULL,
61 [UcitelID] int NULL
62)
63GO
64
65CREATE TABLE [Student]
66(
67 [StudentID] int NOT NULL,
68 [Jmeno] varchar(50) NULL,
69 [Prijmeni] varchar(50) NULL,
70 [Vek] int NULL,
71 [Rocnik] int NULL,
72 [OborID] int NULL
73)
74GO
75
76CREATE TABLE [Ucitel]
77(
78 [UcitelID] int NOT NULL,
79 [Jmeno] varchar(50) NULL,
80 [Prijmeni] varchar(50) NULL,
81 [Titul] varchar(20) NULL
82)
83GO
84
85CREATE TABLE [Zapis]
86(
87 [Hodnoceni] int NULL,
88 [StudentID] int NOT NULL,
89 [PredmetID] int NOT NULL
90)
91GO
92
93/* Create Primary Keys, Indexes, Uniques, Checks */
94
95ALTER TABLE [Obor]
96 ADD CONSTRAINT [PK_Obor]
97 PRIMARY KEY CLUSTERED ([OborID] ASC)
98GO
99
100ALTER TABLE [Predmet]
101 ADD CONSTRAINT [PK_Predmet]
102 PRIMARY KEY CLUSTERED ([PredmetID] ASC)
103GO
104
105ALTER TABLE [Student]
106 ADD CONSTRAINT [PK_Student]
107 PRIMARY KEY CLUSTERED ([StudentID] ASC)
108GO
109
110ALTER TABLE [Ucitel]
111 ADD CONSTRAINT [PK_Ucitel]
112 PRIMARY KEY CLUSTERED ([UcitelID] ASC)
113GO
114
115ALTER TABLE [Zapis]
116 ADD CONSTRAINT [PK_Zapis]
117 PRIMARY KEY CLUSTERED ([StudentID] ASC,[PredmetID] ASC)
118GO
119
120/* Create Foreign Key Constraints */
121
122ALTER TABLE [Predmet] ADD CONSTRAINT [FK_Garantuje_Ucitel]
123 FOREIGN KEY ([UcitelID]) REFERENCES [Ucitel] ([UcitelID]) ON DELETE No Action ON UPDATE No Action
124GO
125
126ALTER TABLE [Student] ADD CONSTRAINT [FK_Studuje_Obor]
127 FOREIGN KEY ([OborID]) REFERENCES [Obor] ([OborID]) ON DELETE No Action ON UPDATE No Action
128GO
129
130ALTER TABLE [Zapis] ADD CONSTRAINT [FK_Student_Zapis]
131 FOREIGN KEY ([StudentID]) REFERENCES [Student] ([StudentID]) ON DELETE No Action ON UPDATE No Action
132GO
133
134ALTER TABLE [Zapis] ADD CONSTRAINT [FK_Predmet_Zapis]
135 FOREIGN KEY ([PredmetID]) REFERENCES [Predmet] ([PredmetID]) ON DELETE No Action ON UPDATE No Action
136GO