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.
- Propose a data model.
- What is the difference between datetime and timestamp?
- Allow the addition of mounts by adding a table and modifying the previous one.
- Add the necessary functional integrity constraints.
- Assign a mount for Daril, Dunbar and Jaco.
- 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.
- Propose a data modeling.
- Create the faction table. We will save our script in a SQL file with Sublime Text for example.
- Insert the data of the table.
- Modify the Race table with the right data.
- Add the referential integrity constraint.
- Delete a faction to check the constraint.
- Add the new races.
- Change the level of Daril by 100.
- Delete Dunbar.
- 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.
- Propose a data model.
- Integrate the data from the members table keeping the same fields
- Add an auto-incrementing primary key.
- Add the necessary foreign keys.
- Perform a dump of the database.