SQL – Data handling

Intro

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

Part 1

Deliverable : warcraft/mission2.pdf

We now want the characters to be able to have several mounts. We will record the date and time of the addition in the new table.

  1. Propose a data model.
  2. What is the difference between datetime and timestamp?
  3. Allow the addition of mounts by adding a table and modifying the previous one.
  4. Add the necessary functional integrity constraints.
  5. Assign a mount for Daril, Dunbar and Jaco.
  6. Assign several mounts to Sperpinette and Saperlipopette.

Part 2

Deliverable : warcraft/mission2.pdf

We want to separate the factions of Warcraft contained in the Races table.

The modifications will be done in command line.

  1. Propose a data modeling.
  2. Create the faction table. We will save our script in a SQL file with Sublime Text for example.
  3. Insert the data of the table.
  4. Modify the Race table with the right data.
  5. Add the referential integrity constraint.
  6. Delete a faction to check the constraint.
  7. Add the new races.
  8. Change the level of Daril by 100.
  9. Delete Dunbar.
  10. Add new classes
races
(11,1024,"Draeneï","alliance"), 
(22,2097152,"Worgen","alliance"),
(24,8388608,"Pandaren","neutral"), 
(25,16777216,"Pandaren","alliance"), 
(26,33554432,"Pandaren","horde"), 
(27,67108864,"Sacrenuit","horde"),
(28,134217728,"Tauren de Haut-Roc","horde"), 
(29,268435456,"Elfe du Vide","alliance"),
(30,536870912,"Draeneï sancteforge","alliance");
classes
(6,32,"Chevalier de la mort","runic-power",5),
(12,2048,"Chasseur de démons","fury",2);

Part 3

Delivrable : warcraft/mission2.pdf et warcraft/mission2.sql

We want to integrate the export of the member table of three guilds (Q3). https://gitlab.com/cherryclass/wow7/blob/master/members.sql

We will keep the character table.

  1. Propose a data model.
  2. Integrate the data from the members table keeping the same fields
  3. Add an auto-incrementing primary key.
  4. Add the necessary foreign keys.
  5. Perform a dump of the database.