Super Mario Bros. 3 �SQL Database

​

Ross Gardner

CIT 225 Course Project

About Super Mario Bros. 3 �SQL Database

Entity Relationship Diagram [ERD]�

Data Entry

World Map

Section 1

World table

About �World table

• The world table provides the statistics, or the details, of each world in the game.
• This includes what the player sees on each world map: world name and theme, regular and unique levels, fortresses, toad houses, spade panels, and enemy courses.
• The boss fought in each world is also known in the world’s airship.

Warp Whistle table

About �Warp Whistle table

• There is a 1 to Many relationship between the world and warp whistle tables.
• One world can have multiple warp whistles hidden, but one warp whistle can only be hidden in one place.
• Additional Note: There are three warp whistles total, so some worlds do not have a warp whistle hidden in them.

White Mushroom House table

About �White Mushroom House table

• There is a 1 to 1 relationship between the world and white mushroom house tables.
• There is only one white mushroom house per world that can be unlocked from meeting the criteria in a certain level in each world.
• Additional Note: World 8 does not have a white mushroom house to unlock.

Enemy Course &�World Has Enemy Course tables

About �Enemy Course table

• Each world has enemy courses where the player can retrieve either a level powerup (ex. Magic Wing) or a world map item (ex. Jugems Cloud).
• Like the standard levels, the player has a time limit and is vulnerable to receiving damage. But the player must defeat the enemy to beat the course as opposed to reaching a finish line in the standard levels.

About �World Has Enemy Course table

• There is a Many to Many relationship between the world and enemy course tables.
• One world can have many different enemy courses and one specific enemy course can be found in many different worlds.

Examples:

• World 2 has two different enemy courses.
• Worlds 1, 3, 5, and 6 all have the same specific enemy course.

Level Overview

Section 2

Level table

Level table [continued]

Level table [continued]

Level table [continued]

Level table [continued]

About �Level table

• There is a 1 to Many relationship between the world and level tables.
• One world can have many levels, but each level is only assigned to one world.

Theme Music table

About �Theme Music table

• Each level in the game has music playing in the background as the player is making its way through the course of the level.
• The music varies with the level’s theme.

Level Has Theme Music table

Level Has Theme Music table [continued]

Level Has Theme Music table [continued]

About �Level Has Theme Music table

• There is a Many to Many relationship between the level and theme music tables.
• One level can have a variety of theme music playing in the background from the beginning to the end, and many levels can share the same theme music.

Examples:

• Level 1-3 has the Overworld music playing in the background as well as the Coin Heaven music playing at one point in the level.
• All the fortresses in the games have the same Fortress theme music playing the background.

Powerup table

About �Powerup table

• In every level in the game, powerups are found that act as a boost of strength or ability to help the player overcome the level’s obstacles.
• The powerups found in each level vary with the level’s obstacles.

​

Level Has Powerup table

Level Has Powerup table [continued]

Level Has Powerup table [continued]

About Level Has Powerup table

• There is a Many to Many relationship between the level and powerup tables.
• One level can have many different powerups found along the path to the finish line, and one specific powerup can be found in many different levels.

Examples:

• Level 1-2 has the Super Leaf powerup and a Starman powerup found along the path to the finish line.
• The Fire Flower powerup is found in a total of 37 levels in the game.

Enemy table

Enemy table [continued]

About �Enemy table

• In every level in the game, enemies are found along the path and act as the main obstacle.
• The enemies can do harm to the player, causing the loss of a powerup or a life.
• The enemies found in each level vary with the level’s theme.

Level Has Enemy table

Level Has Enemy table [continued]

Level Has Enemy table [continued]

About Level Has Enemy �table

• There is a Many to Many relationship between the level and enemy tables.
• One level can have many different enemies found along the path to the finish line, and one specific enemy can be found in many different levels.

Examples:

• Level 2-4 has the Boomerang Bro, Cheep-Cheep, green and red Koopa Paratroopas, red Para-Goomba, and Venus Fire Trap enemies all along the path to the finish line.
• The green Koopa Troopa enemy is found in a total of 21 levels in the game.

User Interface

UI

• This is a simple explanation about how my website will look like.
• When people select one of the 8 worlds, they will be directed to the next page where the levels of that world are listed. Clicking on a certain level will direct the user to another page where the level’s statistics are listed.

UI Select Statement & Table

Business Insides

What is the total level count in each world?

• In this scenario, a player is asking what the total number of levels are in each world in the game.
• I use the COUNT function to count all levels with their respective world id and add a column that gives the total count for each world.

What are all the levels that the ‘Lakitu’ enemy appears in?

• In this scenario, a player is asking for all the levels that the ‘Lakitu’ enemy appears in.
• To get this result, I join the level and level has enemy tables to get their related key: level id, and then I join the enemy table to get their related key: enemy id.

What are all the enemies that appear in level 5-7?

• In this scenario, a player is asking for all the enemies that appear in level 5-7.
• Like the previous scenario, I join the enemy and level has enemy tables to get their related key: enemy id, and then I join the level table to get their related key: level id.

​

What levels have a time limit not equal to 300 seconds?

• In this scenario, a player is asking what levels have a time limit not equal to the most common time limit of 300 seconds.
• To get this result, I used the not equal operator <>. The result set shows all levels that have a time limit above or below, but not equal to 300 seconds.

Where, in what worlds, are all three warp whistles located?

• In this scenario, a player is asking where all three warp whistles are located.
• To get this result, I used a left join that lists all eight worlds (including duplicates) with the warp whistle locations in the right column. Null values are used for the worlds that don’t have a warp whistle found in them.

Conclusion

Those are some examples, questions and answers that SQL can offer from the data. I believe there are more questions players may ask about this game that SQL can provide an answer for from this database.