SQL – Data structure

World of Warcraft is a massively multiplayer online role playing game released in 2004. Our goal is to build a database and manipulate the data from the Blizzard API.

Intro

  • Create the class diagram or relational diagram when modeling is requested

Part 1

Deliverable gitlab : warcraft/mission1.pdf
save the queries and the result of each question through a screen print in a PDF.

A character in Wow has an ID, a name, a registration date and a level. He can choose a class and a race. A class has only one race.

The character Daril registered on 20-02-2019. He is a Human Paladin of level 60. Jaco is a level 100 Orc Monk registered on 15-08-2005. Dunbar is a level 15 Gnome Priest registered on 17-11-2017. Zakyku is a level 115 Goblin Druid registered on 18-10-2018.

  1. Propose a data model.
  2. Create the race and class tables locally based on your modeling and the provided scripts. We will use the Adminer interface to access MySQL. Pay attention to the error in script 2.
  3. Delete the races_mask and classes_mask columns.
  4. Create the character table by indicating the class identifier without the foreign key constraints. The race is determined by the class. So you don’t have to indicate the race in character.
  5. Insert the data corresponding to the previous text.
  6. Delete the race Human. What non-visible problem is generated?
  7. Execute the following line ALTER TABLE `classes` ADD INDEX `races_id` (`races_id`);
    Reinsert Human.
  8. Check that your races_id field is of the same typez in both tables (without the null too). Add the functional integrity constraint to check that a class has a race in the class table.
  9. Delete the Goblin race. What is happening ?
CREATE TABLE IF NOT EXISTS classes (
classes_id INT primary key,
classes_mask INT NULL,
classes_name VARCHAR(20) NULL,
classes_power_type VARCHAR(11) NULL,
races_id INT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 

INSERT INTO classes VALUES
(1,1,"Guerrier","rage",1),
(2,2,"Paladin","mana",1),
(3,4,"Chasseur","focus",5),
(4,8,"Voleur","energy",6),
(5,16,"Prêtre","mana",8),
(7,64,"Chaman","mana",3),
(8,128,"Mage","mana",4),
(9,256,"Démoniste","mana",5),
(10,512,"Moine","energy",2),
(11,1024,"Druide","mana",9);

CREATE TABLE IF NOT EXISTS races (
races_id INT primary key,
races_mask INT NULL,
races_name VARCHAR(20) NULL,
races_side VARCHAR(8) NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 

INSERT INTO races VALUES
(1,1,"Humain","alliance"),
(2,2,"Orc","horde"),
(3,4,"Nain","alliance"),
(4,8,"Elfe de la nuit","alliance"),
(5,16,"Mort-vivant","horde"),
(6,32,"Tauren","horde"),
(7,64,"Gnome","alliance"),
(8,128,"Troll","horde"),
(9,256,"Gobelin","horde"),
(10,512,"Elfe de sang","horde"),

Part 2

Deliverable gitlab : warcraft/mission1.pdf

The character Saperlipopette is a level 50 Tauren Priest. Zigomar is a level 10 Blood Elf mage. Sperpinette is a level 105 Troll Priestess.

  1. Insert the data corresponding to the previous text with today’s registration date. What non-visible problem is generated? Do not solve it.
  2. Propose a data modeling to solve this problem.
  3. By modifying the tables in SQL and according to your modeling, allow the addition of priests. We will think of adding the constraints of functional integrity allowing to verify that the race and the class exist.

Part 3

Deliverables gitlab :
warcraft/mission1.pdf
warcraft/mission1.sql

Characters can now own a mount.

  1. Propose a data modeling.
  2. Make the mount table by correcting the script.

https://gitlab.com/cherryclass/wow7/blob/master/mounts.sql

  1. Allow by modifying a table the addition of mounts (foreign key).
  2. Assign the mount 29046 to Zakyku.
  3. Delete the mount 29046 from wow, is the constraint checkedMake a dump of the database (export to sql format).