In SQL using the database script below, write stored procedures to do the follow
ID: 3713427 • Letter: I
Question
In SQL using the database script below, write stored procedures to do the following:
- Add a new monster class (just call it newMonsterClass)
-Add a new monster (you can name it Zebulon if you need a name. The settings can be whatever, I will change them anyway. I get that part)
-Assign a monster to a gamer
CREATE DATABASE MonsterGame;
CREATE TABLE Grades
(
GradeKey int IDENTITY(1,1) PRIMARY KEY NOT NULL,
GradeName varchar(50) NOT NULL,
MaxLevel int NOT NULL
)
SET IDENTITY_INSERT Grades ON
INSERT Grades (GradeKey, GradeName, MaxLevel)
VALUES
(1, '1 Star', 15),
(2, '2 Star', 20),
(3, '3 Star', 25),
(4, '4 Star', 30),
(5, '5 Star', 35),
(6, '6 Star', 40)
SET IDENTITY_INSERT Grades OFF
CREATE TABLE MonsterElements
(
MonsterElementKey int IDENTITY(1,1) PRIMARY KEY NOT NULL,
MonsterElement varchar(50) NOT NULL
)
SET IDENTITY_INSERT MonsterElements ON
INSERT MonsterElements (MonsterElementKey, MonsterElement)
VALUES
(1, 'Fire'),
(2, 'Water'),
(3, 'Wind'),
(4, 'Light'),
(5, 'Dark')
SET IDENTITY_INSERT MonsterElements OFF
CREATE TABLE MonsterClasses
(
MonsterClassKey int IDENTITY(1,1) PRIMARY KEY NOT NULL,
MonsterClass varchar(50) NOT NULL,
CreateDate smalldatetime DEFAULT(GETDATE()) NOT NULL
)
SET IDENTITY_INSERT MonsterClasses ON
INSERT MonsterClasses (MonsterClassKey, MonsterClass)
VALUES
(1, 'Paladin'),
(2, 'Unicorn'),
(3, 'Pirate Captain'),
(4, 'Barbaric King'),
(5, 'Kung Fu Girl'),
(6, 'Epikion Priest'),
(7, 'Frankenstein'),
(8, 'Mummy'),
(9, 'Penguin Knight'),
(10, 'Drunken Master'),
(11, 'Living Armor')
SET IDENTITY_INSERT MonsterClasses OFF
CREATE TABLE Monsters
(
MonsterKey int IDENTITY(1,1) PRIMARY KEY NOT NULL,
StartingGradeKey int NOT NULL,
MonsterClassKey int NOT NULL,
MonsterElementKey int NOT NULL,
MonsterName varchar(100) NOT NULL,
CreateDate smalldatetime DEFAULT(GETDATE()) NOT NULL,
Active bit DEFAULT(1) NOT NULL
)
SET IDENTITY_INSERT Monsters ON
INSERT Monsters (MonsterKey, StartingGradeKey, MonsterClassKey, MonsterElementKey, MonsterName) VALUES
(1,5,1,1,'Ophilia'),
(2,5,1,2,'Josephine'),
(3,5,1,3,'Louise'),
(4,5,1,4,'Jeanne'),
(5,5,1,5,'Leona'),
(6,5,2,1,'Alexandra'),
(7,5,2,2,'Eleanor'),
(8,5,2,3,'Diane'),
(9,5,2,4,'Amelia'),
(10,5,2,5,'Helena'),
(11,4,3,1,'Carrack'),
(12,4,3,2,'Galleon'),
(13,4,3,3,'Barque'),
(14,4,3,4,'Brig'),
(15,4,3,5,'Frigate'),
(16,4,4,1,'Surtr'),
(17,4,4,2,'Aegir'),
(18,4,4,3,'Hraesvelg'),
(19,4,4,4,'Mimirr'),
(20,4,4,5,'Hrungnir'),
(21,4,6,1,'Chloe'),
(22,3,6,2,'Rina'),
(23,3,6,3,'Michelle'),
(24,4,6,4,'Iona'),
(25,3,6,5,'Rasheed'),
(26,4,5,1,'Hong Hua'),
(27,4,5,2,'Xiao Lin'),
(28,4,5,3,'Ling Ling'),
(29,4,5,4,'Liu Mei'),
(30,4,5,5,'Fei'),
(31,3,7,1,'Bulldozer'),
(32,3,7,2,'Tractor'),
(33,3,7,3,'Crane'),
(34,3,7,4,'Driller'),
(35,3,7,5,'Crawler'),
(36,3,8,1,'Sonora'),
(37,3,8,2,'Nubia'),
(38,3,8,3,'Namib'),
(39,3,8,4,'Sahara'),
(40,3,8,5,'Karakum'),
(41,3,9,1,'Naki'),
(42,3,9,2,'Toma'),
(43,3,9,3,'Mav'),
(44,3,9,4,'Dona'),
(45,3,9,5,'Kuna'),
(46,3,10,1,'Xiao Chun'),
(47,3,10,2,'Mao'),
(48,3,10,3,'Huan'),
(49,3,10,4,'Tien Qin'),
(50,3,10,5,'Wei Shin'),
(51,3,11,1,'Iron'),
(52,3,11,2,'Nickel'),
(53,3,11,3,'Copper'),
(54,3,11,4,'Silver'),
(55,3,11,5,'Zinc')
SET IDENTITY_INSERT Monsters OFF
CREATE TABLE Gamers
(
GamerKey int IDENTITY(1,1) NOT NULL PRIMARY KEY,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
CreateDate smalldatetime NOT NULL DEFAULT (GETDATE()),
Active bit NOT NULL DEFAULT (1)
)
SET IDENTITY_INSERT Gamers ON
INSERT Gamers (GamerKey, FirstName, LastName) VALUES
(1, 'Wade', 'Watts'),
(2, 'James', 'Halliday'),
(3, 'Samantha', 'Cook')
SET IDENTITY_INSERT Gamers OFF
CREATE TABLE GamerMonsters
(
GamerMonstersKey int IDENTITY(1,1) NOT NULL PRIMARY KEY,
MonsterKey int NOT NULL,
GamerKey int NOT NULL,
CurrentLevel int NOT NULL,
CurrentGrade int NOT NULL,
Active bit DEFAULT(1) NOT NULL
)
INSERT GamerMonsters (MonsterKey, GamerKey, CurrentLevel, CurrentGrade) VALUES
(1, 1, 33, 5),
(12, 1, 40, 6),
(21, 1, 30, 4),
(8, 2, 35, 5),
(16, 2, 30, 4),
(31, 2, 25, 3),
(31, 2, 35, 5),
(43, 2, 40, 6),
(34, 2, 1, 4),
(33, 2, 1, 4),
(33, 2, 1, 4),
(34, 2, 1, 3),
(10, 3, 40, 6),
(18, 3, 35, 5),
(24, 3, 40, 6),
(54, 3, 25, 3),
(55, 3, 25, 3),
(53, 3, 1, 3),
(55, 3, 1, 3),
(50, 3, 1, 3)
ALTER TABLE Monsters
ADD CONSTRAINT fk_MonsterGrade
FOREIGN KEY (StartingGradeKey) REFERENCES Grades(GradeKey)
ALTER TABLE Monsters
ADD CONSTRAINT fk_MonsterClass
FOREIGN KEY (MonsterClassKey) REFERENCES MonsterClasses(MonsterClassKey)
ALTER TABLE Monsters
ADD CONSTRAINT fk_MonsterElement
FOREIGN KEY (MonsterElementKey) REFERENCES MonsterElements(MonsterElementKey)
ALTER TABLE GamerMonsters
ADD CONSTRAINT fk_GamerMonsterKey
FOREIGN KEY (MonsterKey) REFERENCES Monsters(MonsterKey)
ALTER TABLE GamerMonsters
ADD CONSTRAINT fk_GamerKey
FOREIGN KEY (GamerKey) REFERENCES Gamers(GamerKey)
ALTER TABLE GamerMonsters
ADD CONSTRAINT fk_GamerCurrentGrade
FOREIGN KEY (CurrentGrade) REFERENCES Grades(GradeKey)
/* Simple Random Number Generation */
DECLARE @myRandomNumber decimal (4,4)
SET @myRandomNumber = RAND()
PRINT @myRandomNumber
/* Random Number between two values 10 and 20 with balanced distribution */
DECLARE @Counter int = 1
DECLARE @Lowest int = 10
DECLARE @Highest int = 20
WHILE (@Counter < 1000)
BEGIN
DECLARE @myRandomNumberBetween int
SET @myRandomNumberBetween = @Lowest + FLOOR((@Highest - @Lowest + 1) * RAND())
PRINT @myRandomNumberBetween
SET @Counter = @Counter + 1
END
Explanation / Answer
CREATE PROCEDURE add_monester
@mk int,@gk int,@ck int,@ek int,@name varchar,@date smalldatetime,@active bit
AS
insert into Monesters ( MonsterKey,StartingGradeKey,MonsterClassKey,MonsterElementKey,MonsterName,Date smalldatetime, Active)values(@mk,@gk,@ck,@ek,@name,@date,@active)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.