Database App Developement 602

DAT602 Week 11, Day 2 – ACID and VB “Using”

Today we went further in depth about ACID and looked at the Visual Basic (VB) “Using” command.



Atomicity, Consistency, Isolation and Durability, I covered these briefly in a previous post, ACID can either be a lot of fun or, more importantly, the set of database principles that ensure database transactions are processed reliably.


VB “Using”

I have touched on VB “Using” in another previous post, however, I did not draw great attention to it. The using command is what allows us to tell the program that we want to use the procedures and tables that we have imported from out database, this allows us to send calls for procedures, shown in this example:



DAT602 Week 11, Day 1 – Subqueries

Today we covered subqueries, otherwise known as inner queries or nested queries. Put plainly a subquery is a query that is used inside of the WHERE clause of a parent query, doing this creates further restrictions on the data that is selected with the parent query.


Subquery Rules

Subqueries can be used in the WHERE clause of SELECT, INSERT, UPDATE, and DELETE statements. The subquery cannot be executed outside of the WHERE clause and it must be enclosed in parenthesis. Here is a complete list of rules from Tutorials Point:



Using Subqueries

Subqueries are used primarily in SELECT statements with the following basic syntax


Subqueries used in an INSERT use the following syntax


Subqueries used in an UPDATE use the following syntax


Subqueries used in a DELETE statement use the following syntax


DAT602 Week 10, Day 2 – Console Apps

Today we covered connecting Visual Studio to mySQL (for those who didn’t know how) and then calling our stored procedures using Visual Basic and printing the result into a console application.


Connecting Visual Studio and mySQL

For my experience connecting Visual Studio to mySQL you can look at my blog post from Week 4, Day 2, which is when I successfully connected Visual Studio to the mySQL database that I have been working on since Milestone One.


Calling Stored Procedures and Printing the Results

As for calling the procedures, well, this is nice and simple, however, printing the results caused me multiple headaches until, finally, I got it!

Here is an example of a test procedure that calls the Find User procedure and prints the first result that is returned from that procedure:


The procedures that caused the most pain were those that returned multiple results across multiple columns, I kept getting errors from the SQL Reader and the internet was, for the most part, not much help. Luckily Todd mentioned the creation of database views and, with that information, I stumbled across this piece of code:


This code calls the procedure that selects all of the online players with their high scores and prints each result using a for-loop, the trick here was changing the result of this procedure in Visual Studio from the default to the view I created in the database.


DAT602 Week 10, Day 1 – Milestone Two, Transactions, ACID and Views

Welcome to the first week back from the “break”, in class today we had an overview of what we will be covering this week and in the coming weeks and our Milestone Two hand in was extended by a week (from May 12th to May 19th).

So today we had a brief introduction to Views, ACID and Transactions, we also briefed over Milestone Two again and Todd gave us some subjects to research ourselves – SQL Exception handling and Test app development.



Views are like a filtered version of the database, where one only sees what one wants you to see. This is providing a user with permissions to see only select parts of the database.



ACID, not to be confused with the mind-altering substance, is a database acronym that stands for Atomicity, Consistency, Isolation and Durability. These four principles are “a set of properties that guarantee that database transactions are processed reliably.” From <http://www.w3resource.com/mysql/mysql-transaction.php – 01/05/2017>

Atomicity ensures that transactions are either completed in whole or not completed at all. This means that if part of a transaction works and the DBMS encounters an error, the changes that were made in the previous commands of the transaction will be rolled back.

Consistency is ensuring that data is protected in the event of a DBMS crash, most importantly that you won’t lose your data.

Isolation is about ensuring that only one transaction is being completed on a piece of data, or table, at any one time. For example, one table cannot be updated twice in one command.

Durability relates mainly to the way the DBMS interacts with the hardware platform that is running the DBMS. Everything from backup strategies and ensuring the hardware box does not lose power, such as with an Uninterrupted Power Supply, right through to the way the CPU interacts with the DBMS.



“A transaction is a logical unit of work that contains one or more SQL statements. Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.” From <http://www.w3resource.com/mysql/mysql-transaction.php – 01/05/2017>

Transactions and ACID are interrelated, this is because transactions help ensure atomicity and transactions need isolation.

Although you can technically use DDL within transactions, in a way, doing that defeats the whole purpose of using transactions. This is because DDL statements cannot be rolled back (undone in the event of an error), therefore, it is best practice to use only DML statements within transactions.


Milestone Two

Look at feedback and adjust the database structure as necessary. We are required to hand in 3 things for this milestone, a report (a continuation of the report handed in for Milestone One), an SQL file (new additions to the SQL file from Milestone One) and the Visual Studio project folder, all to be zipped into a single file and handed in prior to the prescribed date on Moodle.

When we are writing about handling multiplayer gameplay, we are required to define the ACID terms (Atomicity, Consistency, Isolation and Durability), then describe how they work within a DBMS and finally, we must describe how we have implemented ACID into our own apps database.

DAT602 Week 7, Day 2 – Even More Procedures

Today we wrote procedures for our projects to check username and passwords match in the database, we also covered the 5 login attempts and locking the user account.

I posted my start to this on Week 2, Day 2 – More on Procedures and Functions. This class gave me a chance to correct the errors I had in that design and create working procedures as outlined below, however, I’m sure there will be a way to optimise these.


Procedure Code

This Procedure is written with the assumption that my previous procedure (Check user exists) found that the username was valid.


This procedure is linked to the Check/Update login attempts procedure to check and increment the login attempts and finally, lock the account if the user attempts to login unsuccessfully too many times.




When calling this procedure with a valid username and password it produces a message the states that the username and password match, when using it with an invalid username and password combination it produces a message with the remaining login attempts, for the first 5 attempts, if it is used with an incorrect combination 5 times the account becomes locked and a message is displayed stating this.

If a player makes an invalid attempt it will increment the login attempts, however, if they make a valid attempt within the 5 tries their attempts counter will be reset. If the account has been locked though, it will still display a message that the account is locked even with a valid username and password combo.





DAT602 Week 7, Day 1 – Project Feedback, Writing More Procedures and mySQL REPLACE

For today’s class, Todd went over some of the common mistakes he has been seeing while marking our projects, including issues with Foreign Key placement and relationship types. After this, he set us to task by getting us to create/modify his procedure to create new users in our databases. Finally, those of us that finished writing the procedure were asked to research the REPLACE function for mySQL.


Project Feedback

The Foreign Key placement issues Todd talked about did not affect me as it is not a mistake I had made, but he mentioned including the child key as a Foreign Key in the parent table and why we should not do this. Next, he went into the relationships, what he talked about regarding this did not apply directly to me either as I did not make that specific mistake, however, when he gave us individual feedback he noticed that my design included a “Golf”, which made linking information between certain tables overly complex and not ideal.

He suggested to link the PlayerBoard and Session tables directly, so going from this:


To something like this:


However, I will look into this further during the work I complete for Milestone 2, which, involving the procedures, will highlight the issues I may have with my initial design.


Writing SQL procedure

The next part of the class we were asked to create a procedure that will add new users to the Database, this was mine:


I also added a procedure to select the players and their high scores and order them by the high score:




REPLACE is an extension to the SQL standard, it either performs an INSERT, or DELETE then INSERT. REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. REPLACE makes sense only if a table has a PRIMARY KEY or UNIQUE index. Otherwise, it becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.

DAT602 Week 6, Day 2 – More on Procedures and Functions

For today’s class, we covered the presentations that the teams had made, my team’s presentation is included in my previous blog post. After the presentations, we started working on procedures that will be helpful for our projects.


Finding Users

We started with a procedure to find a specific user in the database, including ‘IF’ statements to handle exceptions, this was mine:


This was supposed to include the registration commands also, however, I did not include these.


Checking Username and Password Match

Next, I wrote a procedure to check if the username and password matched, this was written with the assumption that the above check was successful and that the username entered in the above function would be passed to this new function. Also, I would like to note that this in incomplete and not without bugs.


And that concluded the lesson.

DAT602 Week 6, Day 1 – Procedures and Functions with Slideshow Fun

Today we covered Procedures and Functions, part of this was making a Google Slides presentation too. We had a talk about parameters and what they actually are, we also covered the side effect of using queries on a database and how they manipulate the data, which is, in no way, related to the Ciderfect, which is reflective of the feeling and mindstate you reach after drinking too much Cider.


Parts of a Procedure


This lovely image kindly demonstrates the parts that make up a procedure, they start with a create and an outline of the parameters, next comes the body of the procedure (which is what it actually does), before and after these though, we must first change and reset the delimiter, which I have covered in previous blogs.

An important thing to note also is that procedures don’t necessarily have to reference a table, they can simply perform calculations/manipulations to the data given to them by the subroutine too, such as in this example:




SELECT CONCAT('Hello ', PlayerID) AS UserNumber;



CALL helloPlayerID(2017);

You’ll notice in this example that there is also a function called CONCAT, this is short for Concatenate –  “link (things) together in a chain or series.”, The AS part of the command sets the column name.



After covering procedures and functions we split into groups to make a presentation that demonstrated out collective knowledge. My group was Dean, Arno and myself, however, admittedly due to the short timeframe and prior commitments my contribution to this presentation was regretfully exceptionally minor.

This slideshow requires JavaScript.


DAT602 Week 5, Day 2 – ADO.NET, Command Line App Development

Today we covered the requirements for Milestone 2, command line applications and Unit Testing.


Command Line Applications

Command line applications are applications that work via written commands, in other words, no buttons or GUI. For Milestone 2 we will be developing a Command Line “Testing” application for the database we created in Milestone 1.


Command Prompt in Windows is an example of a “command line” application.


Unit Testing

Unit testing is a software development process in which the smallest testable parts of an application, called units, are individually and independently scrutinised for proper operation. Unit testing can be done manually but is often automated.” (searchsoftwarequality.techtarget.com/definition/unit-testing – 03/04/2017)

Unit testing consists of the three A’s, Arrange, Act and Assert.

Arrange -Initialises the variables

Act – Invokes the method that needs testing

Arrange – Verifies the Methods behaviour



We will be building a testing application for our database project.