As this is the first blog of the Dat602 course i will be going over the course material so far.
We have been given the task of designing a small game that will be developed and hosted in the Mysql program. This is exciting as i never knew it was possible to create a small game run entirely through a database program.
the first task we have been given, was to create a small presentation regarding the course overview of the course and what we will be covering throughout the duration of the subject.
For the Presentation my group consisted of Pukar, muaz and Myself. I was covering Milestone 1) which focused more on the design of the database needed for the game while Muaz was going to cover Milestone 2) which took the designs from Milestone 1) and explained how the Queries where going to coded
Pukar was then going to finish off the presentation with Milestone 3) which took a deep look at the necessary reports that needs to follow the assignment.
On the day of the presentation Muaz had left our year and started at year 1 so both Pukar and myself had to complete the presentation with just the two of us. Overall the presentation when well and both myself and Pukar presented our findings to the best of our abilities.
After the presentation we where instructed to develop a brief for our game which gave an over view of what the game was going to be about. the Brief is below.
The Brief Blog)
FirstMan
The player starts in a cave where they take control of a caveman that needs to venture out from the safety of their cave and collect food,water and firewood to keep themselves healthy.
The player’s hunger, thirst and warmth will drop slowly overtime and they will need to collect resources in order to keep their condition high.
As the player travels the landscape they may come across dangers as animals that will attack the player if the player is in range. The objective of the game is to survive and online players and help or steal another player’s resources.
Game Resources-
Wood- used for fires to increase the player’s warmth.
Food- used to feed the player and keep their hunger up.
Water-used to keep the player’s thirst up so they do not dehydrate.
Dangers-
Wolves- will walk in packs and attack a player if they come within range (5 blocks)
bears-will walk in packs and attack a player if they come within range (3 blocks)
Other players- players can both be friendly or hostile
The aim of the game is to gather enough resources to survive the long winters.
Storyboard Blog)
Storyboards can either be done with proper modeling software or it can be done simply with pen and paper. The beauty with story boarding in such a manner can mean that any changes needing can happen quickly and without cost. As creating story boards can be done quickly most of the time a designer can draft examples while still interviewing the client.
The normal procedure with story boarding is to write a small paragraph under each picture describing what is happening in each picture. By doing paragraphs under each picture developers can develop the application with a good understanding what each screen needs to do
For my game design I needed an area where logged in players could operate from creating games ,joining games, creating characters and chatting with other online players. In my first design players could save and load their progress, have access to admin commands through a wide range if buttons. As this is an assessment my design was one of function not beauty.
For the design of my tile based game I first designed a map that was meant to hold multiple maps that the player would be able to transverse between. Hover I scrapped that ideas as i realized that the complexity of undertaking such a task would add unnecessary to the stress of the assessment.
but thanks to story boarding i was able to make those design changes at the drop of a hat with little to no effort.
Logical diagram Blog)
The Logical diagram takes the Entities and Attributes from the conceptual model and makes a series of table that are connected by crow foot relationships. The Entities from the conceptual model form their own tables and all the Attributes become the column names.
By Transforming the Conceptual model to a Logical model you are diving deeper into the how the program processes work thanks to the introduction of Primary and Foreign keys. It is important to note that in a logical diagram you cannot have a many to many relationship and so a join table is created.
Crud Diagram Blog)
A Crud table is a method of documenting the way in which each tables must be manipulated in order to complete each process.
The acronym C.R.U.D stands for C=insert R=select U=Update D=Delete
The way in which it works is by listing all the tables from the database and listing all the expected processes that should be preformed. Then the idea is then to place the appropriate action that needs to taken in the box adjacent to the desired process.
One of the main actions the crud table is used for is to help the programmers know what they need to accomplish in order to make the data base preform as intended.
MYSQL
MYSQL WORKBENCH Blog)
The workbench is a graphical user interface or GUI that is used to database modeling. Above is a picture of the Workbench. When you first load the program you need to create a new Schema (Database) Once you created the Schema you have tell the workbench to use the schema so you can start to write Sql scripts.
Once you have told the workbench which Schema to use you can start to write scripts in order to create some tables.
Above is an example how a query is written in order to create a table. Once a table has been created you can see the table has been added to the database as seen below.
Once a table has been created you can start filling the tables with data. this is achieved by creating insert queries. Like below.
The great aspect for my sql is the fact that when data is inserted using the insert statements you can insert multiple fields in one single script. This is helpful when you have large amounts of data to insert into each table.
Group by Blog)
The Group by statement does what its name implies and groups rows with the same value into one sum row. It is common for a group by to have a aggregate function within the same statement.
Aggregate functions are (COUNT, MAX, MIN, SUM, AVG).
The above picture is an example from W3schools where they have a test database storing customer information. As you can see in the small snippet Mexico has 2 customers. By running the Group by statement we will get a list of the total sum of all customers for each county displayed in a single row.
So in the SQL statement above we are using an aggregate function of COUNT with the CustomerID column, and we are just selecting the Country column from within the Customers table.
Finally we are grouping the Country column with a Group by statement.
The final line in the query is simply ordering the results from the CustomerID column into a descending list. The results are pictured below.
Group by is useful statement because it removes any duplicated results and places them in 1 column.
Stored procedures and functions Blog)
A stored procedure is a way in which mysql scripts can be executed repeatedly or with multiple commands without any needed input from a user. When you want to create a procedure you first need to manually change the Delimiter. The Delimiter has a default setting of ; the Delimiter is the way in which MySQL knows when one transaction starts and ends. In order for a stored procedure to call multiple operations they need to be able to use semicolon without terminating the entire procedure. As you are changing the Delimiter to any temporary symbol you want more commonly assigned.
Once you have selected the Delimiter of your choice you start the procedure by declaring the create procedure and give it a name of your choice, then you fill out the procedure between the Begin and end statements. Once you have completed filling out all the queries you want the procedures to execute, you need to reset the Delimiter back to its default setting of a semicolon after the end call statement.
Finally when you want to execute the statement you call the statement with the call command followed by the name you have given the procedure.
Covid 19 BLog)
Not long into our studies a virus hit the Globe with potential devastating results that originated from China in Wuhan City, Hubei Province. At fist as the world was getting overrun with the Virus and many lives were lost NewZealand at first remained largely untouched. Sadly eventually the Virus found itself at New Zealand borders and entered the population from a small group of people who had recently traveled overseas. In the Passed before Counties were connected with international flights potential global virus killers found it more difficult to take hold of the world. However into today’s world international travel is to easy and quick the potential for virus to spread beyond the borders of their origin is much more likely. This was the case for Covid 19 it had the best possible start a virus could have. Being such a high tourist destination China and with all their exotic foods China was the perfect location for any world pandemic virus to start Another key ingredient for the virus to spread so quickly throughout the world was that it took 14 days for people to start showing any symptoms which was plenty time for the virus to spread.
Monday 23rd March is when the prime minster declared New Zealand was going to go into Full lock-down by Wednesday 25th Midnight. This meant we all had to remain home and only venture out to the grocery store when needed. This was the worse possible time to have forced lock down as the subject matter was getting harder. If you had Children the schools gave the parents homework for their children to do so that they would not lose to much of the schooling year. Trying to do studies myself and teaching my son his schooling material was hard and it showed in my course material.
Microsoft Teams Blog)
As we needed to remain home for a long period of time NMit like many other educational organizations had to overcome the restrictions of the pandemic and still try meet the needs of their students. NMit like many others turned to Microsoft Teams as their preferred method of teaching, as it provided a good stable platform that met all their needs.
Before we could start using the application for learning all the lecturers needed to amend their course material in order to make it possible to delivered remotely. After a 2 week stand down period courses started up again through Teams. All the lectures did a fantastic job considering the circumstances. Even though we live in a time where it is possible to work remotely from home covering difficult course material from home with no direct contact with anyone added to the difficultly of the course.
As the weeks progressed people where staring to get used to the flow of teams and attendance was up for all the classes. Each class session was recorded which proved useful and i believe should be a standard procedure for all classes even after the lock down.
Joins Blog)
It is possible to join multiple tables into one search result so that the user can retrieve the information they need from the database. As for the principles of Normalization all data needs to be segregated and stored in a manner that prevents data duplication.
In the example picture above we have two tables that meet the rules of Normalization that are linked together by primary and foreign keys. For example orderNumber. In the orders table you will have a primary key that is unique and in the Orderdetails table the OrderNumber becomes a foreign key, this connection thanks to primary and foreign keys is who tables that store information seperate are able to display their data to the user via joins. If there is no primary and foreign key relationship between two tables then there is no way the data can be shared via joins.
Lets explain how the join state works:
- first you list all the columns that you want to be displayed in the join table. These columns will come from all the tables that you wish to view.
- Once you have selected all the columns you wish to have you need to start listing all the tables you will need in order to achieve the connection, and decide which table they all should join onto.
- You list the way in which you want the join to be such, as inner or outer joins. This is followed by the On statement which links the primary and foreign key connections.
So as you can see in the after the on statement you need to list all the primary and foreign key connections without them the join clause would not work.
How do you join a table that is not directly linked with another table but shares a relationship through a connection table.
In such cases it is possible to join tables that are spread out throughout the logical diagram all that needs to happen is to pick the starting table and start performing the “Primary key hope”, which links all the connections between the tables you wish to display. For example you may want to list a the name of a customer who bought a product from a particular sales person but on the Logical diagram these two tables are separated through Five other tables. Better start the “Primary key Hope”.
Assessment 2 Blog)
Making the Database Scripts work in Visual Studio as a console test.
The image above is code in Visual Studio 2019 that links to my database in MySql and runs the Register Player procedure that was created and stored in the Mysql workbench. Lets break down to the code to get a better understanding of how it all works.
In order to get Visual Studio’s able to recognize MySql so it can connect and access the database tables you first need to install the MySql package. The way we do this is by right clicking in the program in the properties window and then navigate down to the Manage NuGet Packages.
In order to be able to connect the Mysql database with visual studio we need to place these three lines of code to tell Visual Studio where and how to connect to MySql workbench.
Once you have connected with Mysql you are able call the procedure and execute it from within Visual Studio. The End result for the test connection is
For testing purposes as you start the program it automatically runs all the stored prodecures in mysql workbench that Visual studio’s have access too.
Final thoughts
It is rather fun to have a small program run in Visual Studio’s which has access to a database. This means that clients can access the data they need but without placing the database at any risk.
Assessment 3 Blog)
For milestone3 we had to take the code from the testing console application and implement it into a GUI application.
At first the thought of making a game using MySql workbench did not seam possible and at the start of the course no-one knew that mysql was capable of.
As the course progressed we learnt about stored procedures that were kept on with mysql. The beauty about stored procedures is that they can execute multiple transactions with one call of the transaction.
The way in which we had to make Visual Studio connect to our MySql workbench so it could have access to the stored procedures
Lets take a closer look at these lines of code and see how by using this with the mySql Nugut package we are able to connect to MySQL remotely from another program such as Visual Studio 2019.
First we create a private Variable string called ConnectionString that stores the value of the path to reach the database needed for the game. First we direct the program to the correct server(Localhost port 3306)in our case. Secondly we tell the program which database we are going to use such as Winter_Is_Coming, And Last we hardcode the user name and password so the program can get access without any user input.
When I first got told we were making a game in mysql and had to come us with a concept for the tile game i made a design with many aspects on how i wished to see my game look at the end of the course. Sadly with the lack of time due to other Assessments the end result of the game only housed the core functionality that was outlined in the course material.
Bachelor of Information Technology (NMIT) 