· 9 years ago · Jan 09, 2017, 10:52 PM
1USE blagamzrobnowatabele
2GO
3/****** Object: Table [dbo].[Dealer] Script Date: 09.01.2017 22:50:26 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8SET ANSI_PADDING ON
9GO
10CREATE TABLE [dbo].[Dealer](
11 [id_Dealer] [int] NOT NULL IDENTITY(1,1),
12 [Nazwa] [varchar](50) NOT NULL,
13 [Siedziba] [varchar](50) NOT NULL,
14 [Panstwo] [varchar](50) NOT NULL,
15 CONSTRAINT [id_Dealer] PRIMARY KEY CLUSTERED
16(
17 [id_Dealer] ASC
18)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
19) ON [PRIMARY]
20
21GO
22SET ANSI_PADDING OFF
23GO
24/****** Object: Table [dbo].[Franczyza] Script Date: 09.01.2017 22:50:26 ******/
25SET ANSI_NULLS ON
26GO
27SET QUOTED_IDENTIFIER ON
28GO
29CREATE TABLE [dbo].[Franczyza](
30 [id_Franczyza] [int] NOT NULL IDENTITY(1,1),
31 [id_Marka_F] [int] NOT NULL,
32 [id_Dealer_F] [int] NOT NULL,
33 CONSTRAINT [id_Franczyza] PRIMARY KEY CLUSTERED
34(
35 [id_Franczyza] ASC,
36 [id_Marka_F] ASC,
37 [id_Dealer_F] ASC
38)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
39) ON [PRIMARY]
40
41GO
42/****** Object: Table [dbo].[Klient] Script Date: 09.01.2017 22:50:26 ******/
43SET ANSI_NULLS ON
44GO
45SET QUOTED_IDENTIFIER ON
46GO
47SET ANSI_PADDING ON
48GO
49CREATE TABLE [dbo].[Klient](
50 [id_Klient] [int] NOT NULL IDENTITY(1,1),
51 [Imie] [varchar](50) NOT NULL,
52 [Nazwisko] [varchar](50) NOT NULL,
53 [Plec] [varchar](50) NOT NULL,
54 CONSTRAINT [id_Klient] PRIMARY KEY CLUSTERED
55(
56 [id_Klient] ASC
57)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
58) ON [PRIMARY]
59
60GO
61SET ANSI_PADDING OFF
62GO
63/****** Object: Table [dbo].[Koncerny] Script Date: 09.01.2017 22:50:26 ******/
64SET ANSI_NULLS ON
65GO
66SET QUOTED_IDENTIFIER ON
67GO
68SET ANSI_PADDING ON
69GO
70CREATE TABLE [dbo].[Koncerny](
71 [id_Koncern] [int] NOT NULL IDENTITY(1,1),
72 [Nazwa] [varchar](50) NOT NULL,
73 [Panstwo] [varchar](50) NOT NULL,
74 [Rok_zalozenia] [int] NOT NULL,
75 CONSTRAINT [id_Koncern] PRIMARY KEY CLUSTERED
76(
77 [id_Koncern] ASC
78)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
79) ON [PRIMARY]
80
81GO
82SET ANSI_PADDING OFF
83GO
84/****** Object: Table [dbo].[Marka] Script Date: 09.01.2017 22:50:26 ******/
85SET ANSI_NULLS ON
86GO
87SET QUOTED_IDENTIFIER ON
88GO
89SET ANSI_PADDING ON
90GO
91CREATE TABLE [dbo].[Marka](
92 [id_Marka] [int] NOT NULL IDENTITY(1,1),
93 [Nazwa] [varchar](50) NOT NULL,
94 [Rok_zalozenia] [int] NOT NULL,
95 [id_Koncern_M] [int] NOT NULL,
96 CONSTRAINT [id_Marka] PRIMARY KEY CLUSTERED
97(
98 [id_Marka] ASC
99)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
100) ON [PRIMARY]
101
102GO
103SET ANSI_PADDING OFF
104GO
105/****** Object: Table [dbo].[Model] Script Date: 09.01.2017 22:50:26 ******/
106SET ANSI_NULLS ON
107GO
108SET QUOTED_IDENTIFIER ON
109GO
110SET ANSI_PADDING ON
111GO
112CREATE TABLE [dbo].[Model](
113 [id_Model] [int] NOT NULL IDENTITY(1,1),
114 [Nazwa] [varchar](50) NOT NULL,
115 [Od_kiedy] [int] NOT NULL,
116 [Rynek] [varchar](50) NOT NULL,
117 [Do_kiedy] [int] NOT NULL,
118 [Cena_podstawowa] [int] NOT NULL,
119 [id_Marka_MO] [int] NOT NULL,
120 CONSTRAINT [id_Model] PRIMARY KEY CLUSTERED
121(
122 [id_Model] ASC
123)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
124) ON [PRIMARY]
125
126GO
127SET ANSI_PADDING OFF
128GO
129/****** Object: Table [dbo].[Serwis] Script Date: 09.01.2017 22:50:26 ******/
130SET ANSI_NULLS ON
131GO
132SET QUOTED_IDENTIFIER ON
133GO
134SET ANSI_PADDING ON
135GO
136CREATE TABLE [dbo].[Serwis](
137 [id_Serwis] [int] NOT NULL IDENTITY(1,1),
138 [Nazwa] [varchar](50) NOT NULL,
139 [Specjalizacja] [varchar](50) NOT NULL,
140 [id_Marka_M] [int] NOT NULL,
141 CONSTRAINT [id_Serwis] PRIMARY KEY CLUSTERED
142(
143 [id_Serwis] ASC
144)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
145) ON [PRIMARY]
146
147GO
148SET ANSI_PADDING OFF
149GO
150/****** Object: Table [dbo].[Zakup] Script Date: 09.01.2017 22:50:26 ******/
151SET ANSI_NULLS ON
152GO
153SET QUOTED_IDENTIFIER ON
154GO
155SET ANSI_PADDING ON
156GO
157CREATE TABLE [dbo].[Zakup](
158 [id_Model_Z] [int] NOT NULL IDENTITY(1,1),
159 [id_Klient_Z] [int] NOT NULL,
160 [Stan] [varchar](50) NOT NULL,
161 CONSTRAINT [id_Zakup] PRIMARY KEY CLUSTERED
162(
163 [id_Model_Z] ASC,
164 [id_Klient_Z] ASC
165)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
166) ON [PRIMARY]
167
168GO
169SET ANSI_PADDING OFF
170GO
171ALTER TABLE [dbo].[Franczyza] WITH CHECK ADD CONSTRAINT [Dealer_Franczyza_fk] FOREIGN KEY([id_Dealer_F])
172REFERENCES [dbo].[Dealer] ([id_Dealer])
173GO
174ALTER TABLE [dbo].[Franczyza] CHECK CONSTRAINT [Dealer_Franczyza_fk]
175GO
176ALTER TABLE [dbo].[Franczyza] WITH CHECK ADD CONSTRAINT [Marka_Franczyza_fk] FOREIGN KEY([id_Marka_F])
177REFERENCES [dbo].[Marka] ([id_Marka])
178GO
179ALTER TABLE [dbo].[Franczyza] CHECK CONSTRAINT [Marka_Franczyza_fk]
180GO
181ALTER TABLE [dbo].[Marka] WITH CHECK ADD CONSTRAINT [Koncerny_Marka_fk] FOREIGN KEY([id_Koncern_M])
182REFERENCES [dbo].[Koncerny] ([id_Koncern])
183GO
184ALTER TABLE [dbo].[Marka] CHECK CONSTRAINT [Koncerny_Marka_fk]
185GO
186ALTER TABLE [dbo].[Model] WITH CHECK ADD CONSTRAINT [Marka_Model_fk] FOREIGN KEY([id_Marka_MO])
187REFERENCES [dbo].[Marka] ([id_Marka])
188GO
189ALTER TABLE [dbo].[Model] CHECK CONSTRAINT [Marka_Model_fk]
190GO
191ALTER TABLE [dbo].[Serwis] WITH CHECK ADD CONSTRAINT [Marka_Serwis_fk] FOREIGN KEY([id_Marka_M])
192REFERENCES [dbo].[Marka] ([id_Marka])
193GO
194ALTER TABLE [dbo].[Serwis] CHECK CONSTRAINT [Marka_Serwis_fk]
195GO
196ALTER TABLE [dbo].[Zakup] WITH CHECK ADD CONSTRAINT [Klient_Zakup_fk] FOREIGN KEY([id_Klient_Z])
197REFERENCES [dbo].[Klient] ([id_Klient])
198GO
199ALTER TABLE [dbo].[Zakup] CHECK CONSTRAINT [Klient_Zakup_fk]
200GO
201ALTER TABLE [dbo].[Zakup] WITH CHECK ADD CONSTRAINT [Model_Zakup_fk] FOREIGN KEY([id_Model_Z])
202REFERENCES [dbo].[Model] ([id_Model])
203GO
204ALTER TABLE [dbo].[Zakup] CHECK CONSTRAINT [Model_Zakup_fk]
205GO
206/****** Object: StoredProcedure [dbo].[DodajKoncern] Script Date: 09.01.2017 22:50:26 ******/
207SET ANSI_NULLS ON
208GO
209SET QUOTED_IDENTIFIER ON
210GO
211CREATE PROCEDURE [dbo].[DodajKoncern] @id_Koncernp int, @Nazwap varchar(100), @Panstwop varchar(100), @Rok_zalozeniap int AS
212BEGIN
213IF EXISTS (SELECT 1 FROM Koncerny WHERE @Nazwap = Nazwa)
214 BEGIN
215 RAISERROR('Koncern już istnieje!!',1,2)
216 END
217ELSE
218BEGIN
219 INSERT INTO Koncerny (id_Koncern, Nazwa, Panstwo, Rok_zalozenia)
220 SELECT @id_Koncernp, @Nazwap, @Panstwop, @Rok_zalozeniap
221 PRINT 'Nowy koncern dodany!'
222END
223END
224GO
225/****** Object: StoredProcedure [dbo].[PodwyzkaCeny] Script Date: 09.01.2017 22:50:26 ******/
226SET ANSI_NULLS ON
227GO
228SET QUOTED_IDENTIFIER ON
229GO
230CREATE PROCEDURE [dbo].[PodwyzkaCeny] @id_Modelp int, @oIle int AS
231DECLARE @Cena int
232BEGIN
233 SELECT @Cena = cena_podstawowa + @oIle
234 FROM Model
235 WHERE @id_Modelp = id_Model
236 UPDATE Model SET cena_podstawowa = @Cena
237 WHERE id_Model = @id_Modelp
238END
239GO