Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE [master]
- GO
- IF db_id('TPBD2e1147782') is not null DROP DATABASE [TPBD2e1147782]
- GO
- CREATE DATABASE [TPBD2e1147782]
- CONTAINMENT = NONE
- ON PRIMARY
- ( NAME = N'TPBD2e1147782', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\TPBD2e1147782.mdf' , SIZE = 5120KB , FILEGROWTH = 10%)
- LOG ON
- ( NAME = N'TPBD2e1147782_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\TPBD2e1147782_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
- GO
- ALTER DATABASE [TPBD2e1147782] SET ANSI_NULL_DEFAULT OFF
- GO
- ALTER DATABASE [TPBD2e1147782] SET ANSI_NULLS OFF
- GO
- ALTER DATABASE [TPBD2e1147782] SET ANSI_PADDING OFF
- GO
- ALTER DATABASE [TPBD2e1147782] SET ANSI_WARNINGS OFF
- GO
- ALTER DATABASE [TPBD2e1147782] SET ARITHABORT OFF
- GO
- ALTER DATABASE [TPBD2e1147782] SET AUTO_CLOSE OFF
- GO
- ALTER DATABASE [TPBD2e1147782] SET AUTO_SHRINK OFF
- GO
- ALTER DATABASE [TPBD2e1147782] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
- GO
- ALTER DATABASE [TPBD2e1147782] SET AUTO_UPDATE_STATISTICS ON
- GO
- ALTER DATABASE [TPBD2e1147782] SET CURSOR_CLOSE_ON_COMMIT OFF
- GO
- ALTER DATABASE [TPBD2e1147782] SET CURSOR_DEFAULT GLOBAL
- GO
- ALTER DATABASE [TPBD2e1147782] SET CONCAT_NULL_YIELDS_NULL OFF
- GO
- ALTER DATABASE [TPBD2e1147782] SET NUMERIC_ROUNDABORT OFF
- GO
- ALTER DATABASE [TPBD2e1147782] SET QUOTED_IDENTIFIER OFF
- GO
- ALTER DATABASE [TPBD2e1147782] SET RECURSIVE_TRIGGERS OFF
- GO
- ALTER DATABASE [TPBD2e1147782] SET DISABLE_BROKER
- GO
- ALTER DATABASE [TPBD2e1147782] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
- GO
- ALTER DATABASE [TPBD2e1147782] SET DATE_CORRELATION_OPTIMIZATION OFF
- GO
- ALTER DATABASE [TPBD2e1147782] SET PARAMETERIZATION SIMPLE
- GO
- ALTER DATABASE [TPBD2e1147782] SET READ_COMMITTED_SNAPSHOT OFF
- GO
- ALTER DATABASE [TPBD2e1147782] SET READ_WRITE
- GO
- ALTER DATABASE [TPBD2e1147782] SET RECOVERY SIMPLE
- GO
- ALTER DATABASE [TPBD2e1147782] SET MULTI_USER
- GO
- ALTER DATABASE [TPBD2e1147782] SET PAGE_VERIFY CHECKSUM
- GO
- ALTER DATABASE [TPBD2e1147782] SET TARGET_RECOVERY_TIME = 0 SECONDS
- GO
- ALTER DATABASE [TPBD2e1147782] SET DELAYED_DURABILITY = DISABLED
- GO
- USE [TPBD2e1147782]
- GO
- IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [TPBD2e1147782] MODIFY FILEGROUP [PRIMARY] DEFAULT
- GO
- IF OBJECT_ID('dbo.Inventaire') IS NOT NULL DROP TABLE dbo.Inventaire;
- GO
- IF OBJECT_ID('dbo.Character_Class') IS NOT NULL DROP TABLE dbo.Character_Class;
- GO
- IF OBJECT_ID('dbo.Class') IS NOT NULL DROP TABLE dbo.Class;
- GO
- IF OBJECT_ID('dbo.Character') IS NOT NULL DROP TABLE dbo.Character;
- GO
- IF OBJECT_ID('dbo.EmailAddress') IS NOT NULL DROP TABLE dbo.EmailAddress;
- GO
- IF OBJECT_ID('dbo.Users') IS NOT NULL DROP TABLE dbo.Users;
- GO
- USE [TPBD2e1147782]
- GO
- CREATE TABLE dbo.Users (
- UserID INT NOT NULL IDENTITY(1, 1)
- CONSTRAINT PK_Users_UserID PRIMARY KEY,
- Name nvarchar(255) NOT NULL,
- Billing_Address nvarchar(255) NOT NULL,
- Country nvarchar(255) NOT NULL
- )
- GO
- CREATE TABLE dbo.EmailAddress (
- EmailAddressID INT NOT NULL IDENTITY(1, 1),
- UserID INT NOT NULL
- CONSTRAINT FK_EmailAddress_UserID FOREIGN KEY (UserID)
- REFERENCES dbo.Users (UserID)
- ON DELETE CASCADE
- ON UPDATE CASCADE,
- EmailAddress nvarchar(255) NOT NULL,
- CONSTRAINT PK_EmailAddress PRIMARY KEY (UserID, EmailAddressID)
- )
- GO
- CREATE TABLE dbo.Character (
- CharacterID INT NOT NULL IDENTITY(1, 1),
- UserID INT NOT NULL
- CONSTRAINT FK_Character_UserID FOREIGN KEY (UserID)
- REFERENCES dbo.Users (UserID)
- ON DELETE CASCADE
- ON UPDATE CASCADE,
- CharacterName nvarchar(255) NOT NULL,
- CharacterLevel integer NOT NULL,
- CharacterRace nvarchar(255) NOT NULL
- CONSTRAINT PK_Character PRIMARY KEY (CharacterID)
- )
- GO
- CREATE TABLE dbo.Class (
- ClassID INT NOT NULL IDENTITY(1, 1)
- CONSTRAINT PK_Class_ClassID PRIMARY KEY,
- Name nvarchar(255) NOT NULL,
- )
- GO
- CREATE TABLE dbo.Character_Class (
- CharacterCharacterID INT NOT NULL
- CONSTRAINT FK_Character_Class_CharacterCharacterID FOREIGN KEY (CharacterCharacterID)
- REFERENCES dbo.Character (CharacterID)
- ON DELETE CASCADE
- ON UPDATE CASCADE,
- ClassClassID INT NOT NULL
- CONSTRAINT FK_Character_Class_ClassClassID FOREIGN KEY (ClassClassID)
- REFERENCES dbo.Class (ClassID)
- ON DELETE CASCADE
- ON UPDATE CASCADE,
- CONSTRAINT PK_Character_Class PRIMARY KEY (CharacterCharacterID, ClassClassID)
- )
- GO
- CREATE TABLE dbo.Inventaire (
- ItemID INT NOT NULL IDENTITY(1, 1),
- CharacterCharacterID INT NOT NULL
- CONSTRAINT FK_Inventory_CharacterCharacterID FOREIGN KEY (CharacterCharacterID)
- REFERENCES dbo.Character (CharacterID)
- ON DELETE CASCADE
- ON UPDATE CASCADE,
- ItemName nvarchar(255) NOT NULL,
- ItemValue integer NOT NULL,
- CONSTRAINT PK_Inventory PRIMARY KEY (CharacterCharacterID, ItemID)
- )
- GO
- -----------------------
- -- PARTIE INSERTION
- -----------------------
- DELETE FROM dbo.Users
- WHERE 1 = 1
- GO
- DELETE FROM dbo.Character
- WHERE 1 = 1
- GO
- DELETE FROM dbo.Class
- WHERE 1 = 1
- GO
- DELETE FROM dbo.EmailAddress
- WHERE 1 = 1
- GO
- DELETE FROM dbo.Character_Class
- WHERE 1 = 1
- GO
- DELETE FROM dbo.Inventaire
- WHERE 1 = 1
- GO
- -- Déclaration des variables
- DECLARE @ID_BenDes as INT;
- DECLARE @ID_PhiGui as INT;
- DECLARE @ID_ÉtiBou as INT;
- DECLARE @ID_JosBoi as INT;
- DECLARE @ID_MikTur as INT;
- DECLARE @ID_DonTru as INT;
- DECLARE @ID_Goth as INT;
- DECLARE @ID_Arag as INT;
- DECLARE @ID_Giml as INT;
- DECLARE @ID_Necr as INT;
- DECLARE @ID_Jova as INT;
- DECLARE @ID_Kash as INT;
- INSERT INTO dbo.Users (Name, Billing_Address, Country) VALUES
- ('Benoit Desrosiers', '54 Rue des Abeilles', 'Canada'),
- ('Philippe Guilmette', '185 Rue du Slav', 'Russie'),
- ('Étienne Boutet', '23 Rue de la Baguette', 'Niger'),
- ('Joseph Boisvert', '99 Boulevard Allard', 'Pakistan'),
- ('Mike Turcotte', '550 Broadway Avenue', 'États-Unis'),
- ('Donald Trump', '67 Wall Street', 'États-Unis')
- SET @ID_BenDes = (SELECT UserID FROM dbo.Users WHERE Name = 'Benoit Desrosiers')
- SET @ID_PhiGui = (SELECT UserID FROM dbo.Users WHERE Name = 'Philippe Guilmette')
- SET @ID_ÉtiBou = (SELECT UserID FROM dbo.Users WHERE Name = 'Étienne Boutet')
- SET @ID_JosBoi = (SELECT UserID FROM dbo.Users WHERE Name = 'Joseph Boisvert')
- SET @ID_MikTur = (SELECT UserID FROM dbo.Users WHERE Name = 'Mike Turcotte')
- SET @ID_DonTru = (SELECT UserID FROM dbo.Users WHERE Name = 'Donald Trump')
- INSERT INTO dbo.Character (UserID, CharacterName, CharacterLevel, CharacterRace) VALUES
- (@ID_BenDes,'Gothtarte', 60, 'Dark Elf'),
- (@ID_PhiGui, 'Aragorn' , 52, 'Human'),
- (@ID_ÉtiBou, 'Gimli', 30, 'Dwarf'),
- (@ID_JosBoi, 'Necro', 22, 'High Elf'),
- (@ID_MikTur, 'Jova' , 5, 'Human'),
- (@ID_DonTru, 'Khashir', 17, 'Khajit')
- SET @ID_Goth = (SELECT CharacterID from dbo.Character WHERE CharacterName = 'Gothtarte')
- SET @ID_Arag = (SELECT CharacterID from dbo.Character WHERE CharacterName = 'Aragorn')
- SET @ID_Giml = (SELECT CharacterID from dbo.Character WHERE CharacterName = 'Gimli')
- SET @ID_Necr = (SELECT CharacterID from dbo.Character WHERE CharacterName = 'Necro')
- SET @ID_Jova = (SELECT CharacterID from dbo.Character WHERE CharacterName = 'Jova')
- SET @ID_Kash = (SELECT CharacterID from dbo.Character WHERE CharacterName = 'Kashir')
- INSERT INTO dbo.Class (Name) VALUES
- ('Warrior'),
- ('Mage'),
- ('Necromancer'),
- ('Archer'),
- ('Thief'),
- ('Paladin'),
- ('Monk'),
- ('Cleric')
- INSERT INTO dbo.EmailAddress (UserID, EmailAddress) VALUES
- (@ID_BenDes, 'ben1@hotmail.com'),
- (@ID_BenDes, 'ben2@gmail.com'),
- (@ID_PhiGui, 'phil1@hotmail.com'),
- (@ID_PhiGui, 'phil2@gmail.com'),
- (@ID_ÉtiBou, 'et1@hotmail.com'),
- (@ID_ÉtiBou, 'et2@gmail.com'),
- (@ID_JosBoi, 'jos1@hotmail.com'),
- (@ID_JosBoi, 'jos2@gmail.com'),
- (@ID_MikTur, 'mike1@hotmail.com'),
- (@ID_MikTur, 'mike2@gmail.com'),
- (@ID_DonTru, 'donald1@hotmail.com')
- INSERT INTO dbo.Inventaire (CharacterCharacterID, ItemName, ItemValue) VALUES
- (@ID_Goth, 'Spade', 5),
- (@ID_Goth, 'Iron Sword', 50),
- (@ID_Arag, 'Lobster', 15),
- (@ID_Arag, 'Mithril Mace', 75),
- (@ID_Giml, 'Rune Helm', 250),
- (@ID_Giml, 'Magic Staff', 100),
- (@ID_Necr, 'Spade', 5),
- (@ID_Necr, 'Bow', 20),
- (@ID_Jova, 'Magic Staff', 100),
- (@ID_Kash, 'Steel Sword', 80)
- DECLARE @ID_Warrior as INT;
- DECLARE @ID_Mage as INT;
- DECLARE @ID_Necromancer as INT;
- DECLARE @ID_Archer as INT;
- DECLARE @ID_Thief as INT;
- DECLARE @ID_Paladin as INT;
- DECLARE @ID_Monk as INT;
- DECLARE @ID_Cleric as INT;
- SET @ID_Warrior = (SELECT ClassID from dbo.Class WHERE Name = 'Warrior')
- SET @ID_Mage = (SELECT ClassID from dbo.Class WHERE Name = 'Mage')
- SET @ID_Necromancer = (SELECT ClassID from dbo.Class WHERE Name = 'Necromancer')
- SET @ID_Archer = (SELECT ClassID from dbo.Class WHERE Name = 'Archer')
- SET @ID_Thief = (SELECT ClassID from dbo.Class WHERE Name = 'Thief')
- SET @ID_Paladin = (SELECT ClassID from dbo.Class WHERE Name = 'Paladin')
- SET @ID_Monk = (SELECT ClassID from dbo.Class WHERE Name = 'Monk')
- SET @ID_Cleric = (SELECT ClassID from dbo.Class WHERE Name = 'Cleric')
- INSERT INTO dbo.Character_Class (CharacterCharacterID, ClassClassID) VALUES
- (@ID_Goth, @ID_Paladin),
- (@ID_Goth, @ID_Mage),
- (@ID_Arag, @ID_Mage),
- (@ID_Arag, @ID_Thief),
- (@ID_Giml, @ID_Warrior),
- (@ID_Giml, @ID_Necromancer),
- (@ID_Necr, @ID_Monk),
- (@ID_Jova, @ID_Mage),
- (@ID_Jova, @ID_Cleric),
- (@ID_Kash, @ID_Archer)
- GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement