----- MY QUESTIONS ARE IN THE BELOW ----- ----- MY QUESTIONS ARE IN THE BELOW ----- ----- MY QUESTIONS ARE IN THE BELOW -----

/* * Write an SP at the start of a game that will randomly deal 3 cards to the players. It can be assumed that all the cards in the card table are used in the game. one card in one game. It can only be one player.

* During the game, write an SP, with which the player can take a new card from the card pile into his hand.

* Write a Trigger that will check the number of passive cards in his hand for the player who receives a new card. A player can have a maximum of 5 passive cards in a game.

* Write an SP to be run at the end of each round. This procedure will execute the spEarnIncome procedure for all players in the game. Also, if the game has reached the 20th round will end the game. In this case, the winner of the game is the player with the most money. However, the player cannot level up. */

CREATING DATABASE **********/ ***** EIF DB_ID('cardgame_DB) IS NOT NULL BEGIN ALTER DATABASE cardgame_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE USE master DROP DATABASE cardgame_DB END GO ECREATE DATABASE cardgame_DB ON PRIMARY ( NAME = 'cardgame_DB', FILENAME = 'C:\database\cardgame_DB.mdf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) LOG ON (NAME = 'cardgame_DB_log', FILENAME = 'c:\database\cardgame_DB_log.ldf", SIZE = 2MB, MAXSIZE 50MB, FILEGROWTH = 1MB) GO CREATING TABLE EUSE cardgame_DB * This table contains information about the cards to be used in the game. Each of your cards: * First Name * Resources required to activate it in the game * When active, the income information will be provided to the player in each round is available */ E CREATE TABLE tblCard ID INT PRIMARY KEY IDENTITY (1,1), FirstName VARCHAR(20) NOT NULL, Fee INT NOT NULL, -- The fee to be paid to activate the card Tree INT NOT NULL, -- The amount of trees to be paid to activate the card Stone INT NOT NULL, -- amount of stones to be paid to activate the card GainFee INT NOT NULL, -- After the card is activated, the money it will earn in each round GainTree INT NOT NULL, -- After the card is activated, the tree it will earn in each round GainStone INT NOT NULL, -- After the card is activated, the stone it will win in each turn GO -- This table contains information about the players registered in the system. FCREATE TABLE tblPlayer ID INT PRIMARY KEY IDENTITY (1,1), FirstName VARCHAR(30) NOT NULL, SecondName VARCHAR(30) NOT NULL, e_mail VARCHAR(50) UNIQUE NOT NULL, Grade INT NOT NULL DEFAULT O GO This table contains information about the players registered in the system. E CREATE TABLE tblGame ID INT PRIMARY KEY IDENTITY (1,1), GameOwner INT CONSTRAINT fkGameOwner FOREIGN KEY REFERENCES tblPlayer(ID), Winner INT CONSTRAINT fkGameWinner FOREIGN KEY REFERENCES tblPlayer(ID) DEFAULT NULL, DateStarter DATETIME NOT NULL, DateFinish DATETIME DEFAULT NULL, TotalRound TINYINT NOT NULL DEFAULT O, Go E CREATE TABLE tblPlayers ID INT PRIMARY KEY IDENTITY (1,1), GameID INT CONSTRAINT fkGameID FOREIGN KEY REFERENCES tblGame (ID) DEFAULT NULL, PlayerID INT CONSTRAINT fkPlayerID FOREIGN KEY REFERENCES tblPlayer(ID) DEFAULT NULL, OrderNo TINYINT NOT NULL CONSTRAINT chkPlayerSira CHECK OrderNo BETWEEN 1 AND 6), Fee INT NOT NULL DEFAULT 10, Tree INT NOT NULL DEFAULT 5, Stone INT NOT NULL DEFAULT 3, CONSTRAINT ungGameID_PlayerID UNIQUE GameID, PlayerID), CONSTRAINT unqGameOrder UNIQUE (GameID, OrderNo) Go In this table, there is information about the cards that the players have in each game. E CREATE TABLE tblPlayer Rounds ID INT PRIMARY KEY IDENTITY (1,1), CardID INT CONSTRAINT FkCardID_tblPlayer Rounds FOREIGN KEY REFERENCES tblCard(ID) DEFAULT NULL, GameID INT CONSTRAINT fkGameID_tblPlayer Rounds FOREIGN KEY REFERENCES tblGame (ID) DEFAULT NULL, PlayerID INT CONSTRAINT fkPlayerID_tblPlayer Rounds FOREIGN KEY REFERENCES tblPlayer(ID) DEFAULT NULL, Situation TINYINT NOT NULL DEFAULT O, CONSTRAINT unqoE UNIQUE (CardID, GameID, PlayerID) GO E CREATE FUNCTION fncNumberOfGameshon (@PlayerID INT) RETURNS INT AS BEGIN RETURN (SELECT ISNULL(COUNT(*), 0) FROM tblGame O WHERE O.Winner = @PlayerID) END GO E CREATE VIEW VwPlayerScores AS SELECT O. FirstName + + 0. SecondName AS Player, ISNULL(COUNT (Oyn.ID), 0) AS GameNumber, dbo.fncNumberOfGamesWon (O.ID AS WinNumber, ISNULL(MAX(Fee), O) AS TheMostFee, 0. Grade AS Grade FROM tblPlayer o LEFT JOIN tblPlayers Oyn ON O.ID = Oyn.PlayerID GROUP BY O.ID, O.FirstName + ' ' + 0. SecondName, O. Grade . GO CREATE PROCEDURE SpActivateTheCard (@GameID INT, @PlayerID INT, @cardID INT) AS BEGIN DECLARE @LeftFee INT, @Left Tree INT, @LeftStone INT, @Situation INT, @CardFirstName VARCHAR(20) K.Stone, @Situation = OE. Situation, @CardFirstName = K.FirstName SELECT @LeftFee = Oyn. Fee K.Fee, @Left Tree = Oyn. Tree - K.Tree, @LeftStone = Oyn.Stone FROM tblPlayers Oyn INNER JOIN tblGame Oy ON Oy.ID = Oyn. GameID INNER JOIN tblPlayer O ON O.ID = Oyn.PlayerID INNER JOIN tblPlayerRounds OE ON OE.PlayerID = 0.ID AND OE. GameID = Oy.ID INNER JOIN tblCard K ON K.ID = OE. CardID WHERE OE. GameID = @GameID AND OE.PlayerID = @PlayerID AND OE. CardID = @cardID - = IF @LeftFee >=0 AND @Left Tree >=0 AND @LeftStone >=0 AND @Situation = 0 BEGIN BEGIN TRANSACTION BEGIN TRY UPDATE tblPlayerRounds SET Situation = 1 WHERE CardID = @cardID AND GameID @cardID AND GameID = @GameID AND PlayerID = @PlayerID UPDATE tblPlayers SET Fee = @LeftFee, Tree = @LeftTree, Stone = @LeftStone WHERE @GameID = GameID AND @PlayerID = PlayerID RAISERROR("%s NAMED CARD ACTIVATED', 10, 1, @CardFirstName) COMMIT RETURNO END TRY BEGIN CATCH RAISERROR("!!! A PROBLEM OCCURRED WHILE THE INFORMATION WAS UPDATED... THE TRANSACTIONS DONE WILL BE CANCELED... !!!', 16, 1) ROLLBACK RETURN -1 END CATCH END ELSE IF @Situation = 1 BEGIN RAISERROR('!!! CARD ALREADY ACTIVATED !!!', 16, 1) RETURN -1 END ELSE BEGIN RAISERROR(!!! NOT ENOUGH RESOURCES TO ACTIVATE THE CARD !!!', 16, 1) RETURN -1 END END GO CREATE TRIGGER trgActiveCardNumberControl ON tblPlayerRounds AFTER UPDATE AS BEGIN DECLARE @PlayerID INT, @GameID INT, @ActiveCardNumber TINYINT SELECT @PlayerID = PlayerID, @GameID = GameID FROM inserted @GameID AND OE.PlayerID @PlayerID AND OE. Situation = 1 SELECT @ActiveCardNumber = COUNT(*) FROM tblPlayer Rounds OE WHERE OE. GameID IF @ActiveCardNumber = 5 BEGIN DECLARE @FirstName VARCHAR(30), @SecondName VARCHAR(30) SELECT @FirstName = 0.FirstName, @SecondName = 0. SecondName FROM tblPlayer O WHERE O.ID = @PlayerID DECLARE @Fee INT SELECT @Fee = Fee FROM tblPlayers WHERE GameID @GameID AND PlayerID = @PlayerID UPDATE tblPlayer SET Grade += CASE WHEN @Fee BETWEEN 10 AND 50 THEN 1 WHEN @Fee BETWEEN 51 AND 80 THEN 2 WHEN @Fee >= 81 THEN 3 END WHERE ID = @PlayerID = UPDATE tblGame SET Winner = @PlayerID, DateFinish = GETDATE() WHERE ID = @GameID RAISERROR("!!! GAME OVER !!! %s %s WON', 10, 1, @FirstName, @SecondName) END END GO E CREATE TRIGGER trgGradeIncrease ON tblPlayer AFTER UPDATE AS BEGIN DECLARE @PlayerID INT, @NewGrade INT, @LastGrade INT, @Grade Increase INT 1 IF UPDATE(Grade) BEGIN SELECT @PlayerID = ID, @NewGrade - Grade FROM inserted SELECT @LastGrade = Grade FROM deleted SET @Grade Increase @NewGrade - @LastGrade IF @GradeIncrease > 0 RAISERROR("!!! CONGRATULATIONS %d YOU EARN NEW GRADE END -- YOUR NEW GRADE IS %d !!!', 10, 1, @Grade Increase, @NewGrade) END GO ECREATE PROCEDURE spEarn Income (@GameID INT, @PlayerID INT) AS BEGIN DECLARE @CardList TABLE (CardID INT) INSERT INTO @CardList SELECT OE CardID FROM tblPlayerRounds OE WHERE OE. GameID = @GameID AND OE.PlayerID = @PlayerID AND OE. Situation = 1 DECLARE @IncomeFee INT, @Income Tree INT, @IncomeStone INT SELECT @IncomeFee = SUM(K. GainFee), @IncomeTree = SUM(K. GainTree), @IncomeStone = SUM(K. Gainstone) FROM tbiCard K INNER JOIN @CardList KL ON KL. CardID = K.ID UPDATE tblPlayers SET Fee += @IncomeFee, Tree += @IncomeTree, Stone += @IncomeStone WHERE GameID = @GameID AND PlayerID = @PlayerID END GO