DataBase modelization

DATABASE MODELIZATION

MLD (logical model of the database)

Explanations

We got three tables.

• The first one, the Map table, contain the name of the map (primary key), its dimensions (width, height) and the number of diamond and the time to finish the level.

• The second, the ObjectType table, contain the differents types of elements (primary key) that will compose the Map (Dirt, Rock, Monsters, character, Diamond, Wall).

Finally, these two tables are linked by the ObjectMap relation which contain the primary key of the Map table and the ObjectType table, and this key will additionally be composed of the x and y coordinates (avoids redundancy of entry in the Table).

---

CUSTOMIZATION DATABASE

• Updating or deleting an entry in Map is propagated in ObjectMap :

ObjectMap_Map_MapName_fk FOREIGN KEY (MapName) REFERENCES Map (MapName) ON DELETE CASCADE ON UPDATE CASCADE

So if a map is deleted from the Map table, the elements that compose it are removed from the ObjectMap.

• The update of an Object Type entry is propagated in ObjectMap the deletion will be blocked :

ObjectMap_ObjectType_TypeObject_fk FOREIGN KEY (TypeObject) REFERENCES ObjectType (TypeObject) ON UPDATE CASCADE

If a ObjectType change in TypeObject table, it will change in ObjectMap table.

---

Stored routines

We got nine stored routines :

• sql addMap(IN nameMap VARCHAR(255), IN width INT, IN heigh INT, IN nbrDiamond INT, IN timeremaining INT) Allow to add a Map in dataBase parameter :

• nameMap : name of the map (VARCHAR)
• width map (INT)
• Height map (INT)
• nbrDiamond : number of diamond which compose the map (INT)
• timeremaining : Time remaining to complete the game (INT)

• sql addMapElement(IN nameMAP VARCHAR(255), IN nameElement VARCHAR(255), IN X INT, IN Y INT) parameter : -- nameMAP : The name of the map where the object will be added (VARCHAR)

• nameElement : The name of the element to add (VARCHAR)
• X : the coordinate x on the map (INT)
• Y : the coordinate y on the map (INT)

• sql addObjectType(IN OType VARCHAR(255))

• sql getMap(IN NameMap VARCHAR(255))

• sql getMapNames()

• sql getMapObjects(IN NameMap VARCHAR(255))

• sql removeMap(IN nameMap VARCHAR(255))

• sql removeMapElement(IN nameMAP VARCHAR(255), IN nameElement VARCHAR(255))

• sql removeObjectType(IN OType VARCHAR(255))