· 9 years ago · Apr 27, 2017, 05:46 PM
1USE [master]
2GO
3IF db_id('TPBD2e1147782') is not null DROP DATABASE [TPBD2e1147782]
4GO
5
6CREATE DATABASE [TPBD2e1147782]
7 CONTAINMENT = NONE
8 ON PRIMARY
9( NAME = N'TPBD2e1147782', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\TPBD2e1147782.mdf' , SIZE = 5120KB , FILEGROWTH = 10%)
10 LOG ON
11( NAME = N'TPBD2e1147782_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\TPBD2e1147782_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
12GO
13ALTER DATABASE [TPBD2e1147782] SET ANSI_NULL_DEFAULT OFF
14GO
15ALTER DATABASE [TPBD2e1147782] SET ANSI_NULLS OFF
16GO
17ALTER DATABASE [TPBD2e1147782] SET ANSI_PADDING OFF
18GO
19ALTER DATABASE [TPBD2e1147782] SET ANSI_WARNINGS OFF
20GO
21ALTER DATABASE [TPBD2e1147782] SET ARITHABORT OFF
22GO
23ALTER DATABASE [TPBD2e1147782] SET AUTO_CLOSE OFF
24GO
25ALTER DATABASE [TPBD2e1147782] SET AUTO_SHRINK OFF
26GO
27ALTER DATABASE [TPBD2e1147782] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
28GO
29ALTER DATABASE [TPBD2e1147782] SET AUTO_UPDATE_STATISTICS ON
30GO
31ALTER DATABASE [TPBD2e1147782] SET CURSOR_CLOSE_ON_COMMIT OFF
32GO
33ALTER DATABASE [TPBD2e1147782] SET CURSOR_DEFAULT GLOBAL
34GO
35ALTER DATABASE [TPBD2e1147782] SET CONCAT_NULL_YIELDS_NULL OFF
36GO
37ALTER DATABASE [TPBD2e1147782] SET NUMERIC_ROUNDABORT OFF
38GO
39ALTER DATABASE [TPBD2e1147782] SET QUOTED_IDENTIFIER OFF
40GO
41ALTER DATABASE [TPBD2e1147782] SET RECURSIVE_TRIGGERS OFF
42GO
43ALTER DATABASE [TPBD2e1147782] SET DISABLE_BROKER
44GO
45ALTER DATABASE [TPBD2e1147782] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
46GO
47ALTER DATABASE [TPBD2e1147782] SET DATE_CORRELATION_OPTIMIZATION OFF
48GO
49ALTER DATABASE [TPBD2e1147782] SET PARAMETERIZATION SIMPLE
50GO
51ALTER DATABASE [TPBD2e1147782] SET READ_COMMITTED_SNAPSHOT OFF
52GO
53ALTER DATABASE [TPBD2e1147782] SET READ_WRITE
54GO
55ALTER DATABASE [TPBD2e1147782] SET RECOVERY SIMPLE
56GO
57ALTER DATABASE [TPBD2e1147782] SET MULTI_USER
58GO
59ALTER DATABASE [TPBD2e1147782] SET PAGE_VERIFY CHECKSUM
60GO
61ALTER DATABASE [TPBD2e1147782] SET TARGET_RECOVERY_TIME = 0 SECONDS
62GO
63ALTER DATABASE [TPBD2e1147782] SET DELAYED_DURABILITY = DISABLED
64GO
65USE [TPBD2e1147782]
66GO
67IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [TPBD2e1147782] MODIFY FILEGROUP [PRIMARY] DEFAULT
68GO
69
70
71IF OBJECT_ID('dbo.Inventaire') IS NOT NULL DROP TABLE dbo.Inventaire;
72GO
73IF OBJECT_ID('dbo.Character_Class') IS NOT NULL DROP TABLE dbo.Character_Class;
74GO
75IF OBJECT_ID('dbo.Class') IS NOT NULL DROP TABLE dbo.Class;
76GO
77IF OBJECT_ID('dbo.Character') IS NOT NULL DROP TABLE dbo.Character;
78GO
79IF OBJECT_ID('dbo.EmailAddress') IS NOT NULL DROP TABLE dbo.EmailAddress;
80GO
81IF OBJECT_ID('dbo.Users') IS NOT NULL DROP TABLE dbo.Users;
82GO
83
84USE [TPBD2e1147782]
85GO
86
87CREATE TABLE dbo.Users (
88UserID INT NOT NULL IDENTITY(1, 1)
89 CONSTRAINT PK_Users_UserID PRIMARY KEY,
90Name nvarchar(255) NOT NULL,
91Billing_Address nvarchar(255) NOT NULL,
92Country nvarchar(255) NOT NULL
93)
94GO
95
96CREATE TABLE dbo.EmailAddress (
97EmailAddressID INT NOT NULL IDENTITY(1, 1),
98UserID INT NOT NULL
99 CONSTRAINT FK_EmailAddress_UserID FOREIGN KEY (UserID)
100 REFERENCES dbo.Users (UserID)
101 ON DELETE CASCADE
102 ON UPDATE CASCADE,
103EmailAddress nvarchar(255) NOT NULL,
104 CONSTRAINT PK_EmailAddress PRIMARY KEY (UserID, EmailAddressID)
105)
106GO
107
108CREATE TABLE dbo.Character (
109CharacterID INT NOT NULL IDENTITY(1, 1),
110UserID INT NOT NULL
111 CONSTRAINT FK_Character_UserID FOREIGN KEY (UserID)
112 REFERENCES dbo.Users (UserID)
113 ON DELETE CASCADE
114 ON UPDATE CASCADE,
115CharacterName nvarchar(255) NOT NULL,
116CharacterLevel integer NOT NULL,
117CharacterRace nvarchar(255) NOT NULL
118 CONSTRAINT PK_Character PRIMARY KEY (CharacterID)
119)
120GO
121
122CREATE TABLE dbo.Class (
123ClassID INT NOT NULL IDENTITY(1, 1)
124 CONSTRAINT PK_Class_ClassID PRIMARY KEY,
125Name nvarchar(255) NOT NULL,
126)
127GO
128
129CREATE TABLE dbo.Character_Class (
130CharacterCharacterID INT NOT NULL
131 CONSTRAINT FK_Character_Class_CharacterCharacterID FOREIGN KEY (CharacterCharacterID)
132 REFERENCES dbo.Character (CharacterID)
133 ON DELETE CASCADE
134 ON UPDATE CASCADE,
135ClassClassID INT NOT NULL
136 CONSTRAINT FK_Character_Class_ClassClassID FOREIGN KEY (ClassClassID)
137 REFERENCES dbo.Class (ClassID)
138 ON DELETE CASCADE
139 ON UPDATE CASCADE,
140 CONSTRAINT PK_Character_Class PRIMARY KEY (CharacterCharacterID, ClassClassID)
141)
142GO
143
144CREATE TABLE dbo.Inventaire (
145ItemID INT NOT NULL IDENTITY(1, 1),
146CharacterCharacterID INT NOT NULL
147 CONSTRAINT FK_Inventory_CharacterCharacterID FOREIGN KEY (CharacterCharacterID)
148 REFERENCES dbo.Character (CharacterID)
149 ON DELETE CASCADE
150 ON UPDATE CASCADE,
151ItemName nvarchar(255) NOT NULL,
152ItemValue integer NOT NULL,
153 CONSTRAINT PK_Inventory PRIMARY KEY (CharacterCharacterID, ItemID)
154)
155GO
156
157-----------------------
158-- PARTIE INSERTION
159-----------------------
160
161DELETE FROM dbo.Users
162WHERE 1 = 1
163GO
164
165DELETE FROM dbo.Character
166WHERE 1 = 1
167GO
168
169DELETE FROM dbo.Class
170WHERE 1 = 1
171GO
172
173DELETE FROM dbo.EmailAddress
174WHERE 1 = 1
175GO
176
177DELETE FROM dbo.Character_Class
178WHERE 1 = 1
179GO
180
181DELETE FROM dbo.Inventaire
182WHERE 1 = 1
183GO
184
185-- Déclaration des variables
186DECLARE @ID_BenDes as INT;
187DECLARE @ID_PhiGui as INT;
188DECLARE @ID_ÉtiBou as INT;
189DECLARE @ID_JosBoi as INT;
190DECLARE @ID_MikTur as INT;
191DECLARE @ID_DonTru as INT;
192
193DECLARE @ID_Goth as INT;
194DECLARE @ID_Arag as INT;
195DECLARE @ID_Giml as INT;
196DECLARE @ID_Necr as INT;
197DECLARE @ID_Jova as INT;
198DECLARE @ID_Kash as INT;
199
200INSERT INTO dbo.Users (Name, Billing_Address, Country) VALUES
201('Benoit Desrosiers', '54 Rue des Abeilles', 'Canada'),
202('Philippe Guilmette', '185 Rue du Slav', 'Russie'),
203('Étienne Boutet', '23 Rue de la Baguette', 'Niger'),
204('Joseph Boisvert', '99 Boulevard Allard', 'Pakistan'),
205('Mike Turcotte', '550 Broadway Avenue', 'États-Unis'),
206('Donald Trump', '67 Wall Street', 'États-Unis')
207
208SET @ID_BenDes = (SELECT UserID FROM dbo.Users WHERE Name = 'Benoit Desrosiers')
209SET @ID_PhiGui = (SELECT UserID FROM dbo.Users WHERE Name = 'Philippe Guilmette')
210SET @ID_ÉtiBou = (SELECT UserID FROM dbo.Users WHERE Name = 'Étienne Boutet')
211SET @ID_JosBoi = (SELECT UserID FROM dbo.Users WHERE Name = 'Joseph Boisvert')
212SET @ID_MikTur = (SELECT UserID FROM dbo.Users WHERE Name = 'Mike Turcotte')
213SET @ID_DonTru = (SELECT UserID FROM dbo.Users WHERE Name = 'Donald Trump')
214
215INSERT INTO dbo.Character (UserID, CharacterName, CharacterLevel, CharacterRace) VALUES
216(@ID_BenDes,'Gothtarte', 60, 'Dark Elf'),
217(@ID_PhiGui, 'Aragorn' , 52, 'Human'),
218(@ID_ÉtiBou, 'Gimli', 30, 'Dwarf'),
219(@ID_JosBoi, 'Necro', 22, 'High Elf'),
220(@ID_MikTur, 'Jova' , 5, 'Human'),
221(@ID_DonTru, 'Khashir', 17, 'Khajit')
222
223SET @ID_Goth = (SELECT CharacterID from dbo.Character WHERE CharacterName = 'Gothtarte')
224SET @ID_Arag = (SELECT CharacterID from dbo.Character WHERE CharacterName = 'Aragorn')
225SET @ID_Giml = (SELECT CharacterID from dbo.Character WHERE CharacterName = 'Gimli')
226SET @ID_Necr = (SELECT CharacterID from dbo.Character WHERE CharacterName = 'Necro')
227SET @ID_Jova = (SELECT CharacterID from dbo.Character WHERE CharacterName = 'Jova')
228SET @ID_Kash = (SELECT CharacterID from dbo.Character WHERE CharacterName = 'Kashir')
229
230INSERT INTO dbo.Class (Name) VALUES
231('Warrior'),
232('Mage'),
233('Necromancer'),
234('Archer'),
235('Thief'),
236('Paladin'),
237('Monk'),
238('Cleric')
239
240INSERT INTO dbo.EmailAddress (UserID, EmailAddress) VALUES
241(@ID_BenDes, 'ben1@hotmail.com'),
242(@ID_BenDes, 'ben2@gmail.com'),
243(@ID_PhiGui, 'phil1@hotmail.com'),
244(@ID_PhiGui, 'phil2@gmail.com'),
245(@ID_ÉtiBou, 'et1@hotmail.com'),
246(@ID_ÉtiBou, 'et2@gmail.com'),
247(@ID_JosBoi, 'jos1@hotmail.com'),
248(@ID_JosBoi, 'jos2@gmail.com'),
249(@ID_MikTur, 'mike1@hotmail.com'),
250(@ID_MikTur, 'mike2@gmail.com'),
251(@ID_DonTru, 'donald1@hotmail.com')
252
253INSERT INTO dbo.Inventaire (CharacterCharacterID, ItemName, ItemValue) VALUES
254(@ID_Goth, 'Spade', 5),
255(@ID_Goth, 'Iron Sword', 50),
256(@ID_Arag, 'Lobster', 15),
257(@ID_Arag, 'Mithril Mace', 75),
258(@ID_Giml, 'Rune Helm', 250),
259(@ID_Giml, 'Magic Staff', 100),
260(@ID_Necr, 'Spade', 5),
261(@ID_Necr, 'Bow', 20),
262(@ID_Jova, 'Magic Staff', 100),
263(@ID_Kash, 'Steel Sword', 80)
264
265DECLARE @ID_Warrior as INT;
266DECLARE @ID_Mage as INT;
267DECLARE @ID_Necromancer as INT;
268DECLARE @ID_Archer as INT;
269DECLARE @ID_Thief as INT;
270DECLARE @ID_Paladin as INT;
271DECLARE @ID_Monk as INT;
272DECLARE @ID_Cleric as INT;
273
274SET @ID_Warrior = (SELECT ClassID from dbo.Class WHERE Name = 'Warrior')
275SET @ID_Mage = (SELECT ClassID from dbo.Class WHERE Name = 'Mage')
276SET @ID_Necromancer = (SELECT ClassID from dbo.Class WHERE Name = 'Necromancer')
277SET @ID_Archer = (SELECT ClassID from dbo.Class WHERE Name = 'Archer')
278SET @ID_Thief = (SELECT ClassID from dbo.Class WHERE Name = 'Thief')
279SET @ID_Paladin = (SELECT ClassID from dbo.Class WHERE Name = 'Paladin')
280SET @ID_Monk = (SELECT ClassID from dbo.Class WHERE Name = 'Monk')
281SET @ID_Cleric = (SELECT ClassID from dbo.Class WHERE Name = 'Cleric')
282
283INSERT INTO dbo.Character_Class (CharacterCharacterID, ClassClassID) VALUES
284(@ID_Goth, @ID_Paladin),
285(@ID_Goth, @ID_Mage),
286(@ID_Arag, @ID_Mage),
287(@ID_Arag, @ID_Thief),
288(@ID_Giml, @ID_Warrior),
289(@ID_Giml, @ID_Necromancer),
290(@ID_Necr, @ID_Monk),
291(@ID_Jova, @ID_Mage),
292(@ID_Jova, @ID_Cleric),
293(@ID_Kash, @ID_Archer)
294GO