Delete has several way it can be implemented. Such as cascade which was mentioned in a previous blog. We may set the value to null with set null. We can also validate the deletion with restrict, which prevent a row form being deleted if another thing reefers to it.

These create table statement are added to the end of the delete statement. They also must be place near the end of the table with rows we want to delete.

We may also set up similar behavior for update statements.

To be able to pass things into SQL we need a list of parameters.

A connection string is a string which tells a program to connect to a specific database.

Based on the 28/5/19 dat602 class.

Functions can’t return values for a set. For our project we will need to find a way of working around this and tricking the program into be leaving that the the function is returning a single function.

Procedure can pretty much do anything so we don’t have to worry about this with them.

Procedure let us re use code and automate things. This will be useful in our project by allowing us to reuse parts of the program such as change a user statics or updating a pieces persecution.

Transitional squeal allows as to make a change to a database with out committing it, so we don’t have to worry about breaking the database. We must use commit to commit it and we can use rollback to go back to the prior state.

The default perameter in mySQL is IN so we must tell it that we want stuff to be passed out.

Made based on the 27/5/19 Dat602 class.

For milestone two we need to create SQL procedures and transaction for our game.

We must research ACID which is something I have never heard of so I must study this out of class.

We must create a test base base as well as a basic test class in visual studio to test all of the database.

Part of this assessment is to create a command line method to test every part of the database.

We may change parts of our plan if we find an area of our plane doesn’t work out.

A definer gives the function the administration rights of who ever creates the function not the person running.

Invoker has the program take the priority of the person running the function.

Invoker is safer than a definer because a definer can allow anyone to alter the database even if they don’t normally have those permissions, this could be bad with something like an SQL injection attack which could coarse irreversible damage to the database.

Out allows a procedure to pass a variable similar to how you would pass a variable by Ref in visual basic.Out parameters passes things out In has thing passed to it.

A cursor will allow us to move for a set based system to a row based system.

This blog is based on the 22/5/19 dat602 class.

Group By, Finding sets within sets.

We think of columns as groups of things but sometimes there are groups within the column. For us to access these and divide the table up by these we can use group by. Some of the different ways these can be used are counting the amount of a certain thing. This could be useful, for example you could count the number of a person with a specific job in a table about a city. I should thing about how I could possibly use it within my project it would certainly be useful but I am currently unsure how I would use it. I might need to look into this more.

Having is another thing with lets a select a specific subset of rows.

Useful commands (show tables;) (describe <tablename>); The first first show what tables we currently have created. The second show a table with all of the row and there attributes. These are useful tool when it comes to managing our database as they allow us to visualize our database which can let us find mistake or errors.

If we try and sum something like the population of all country then we could have a problem where all the countries are added together, this is when group by comes in. Group By allows as to group similar objects together before we sum the objects. This would once again be useful when we add together entities which share an attribute.

Like allows us to compare wild card within mySQL.

Based 21/05/19 dat602 class

Joins but more…

Straight joins are weird, I might need to look into them but Todd doesn’t know what they are so I don’t think they will be that important.

Natural joins are similar to other joins but might be useful to look into.

Full outer joins don’t exist within MySQL but unions are similar so I might want to look further into these as Full outer joins might be useful. For example with my project I might want to join my piece table with the board table to represent the board with the pieces in their places.

During this class we will be working on project work.

I am doing we with my project currently I need to finish off my create table statement, create a written narrative and make sure all of my columns name line up with what I have used in my narratives.

Based on the 20/5/19 Dat602 class.

It’s all about joins. Our next couples of classes will be about joins and project work.

The whole of a relational database is that all of the data is related to each other. This is the point of them, each table relates to other tables via foreign keys. This is useful as it allows us to relate data from different tables together.

We have a lot of way for interacting with these intersections.

Normalization is about separating into set which are different and logically relate to each other, normalization also separates things so their is one primary key for each row and their is no transient dependence between each other. For this class we only have to bring our database to 3rd normal form. I am a little rusty on my 3rd normal form so I will have to do more research on this. The 3rd normal form will be useful as it will allow us to be able to be mostly sure that we wont brake anything or coarse extra work with our CRUD statements.

For joins we must find a link between the keys of a table. If one table doesn’t contain a link to another table then we must find a common link between them.

Inner joins only join where the two columns meet.

We may use join clauses in the from function in a select statement. This is the way that is normally use how ever they are about equivalent to having a select statement from where the two key match.

Outer joins(both left and right), these select all of the rows from a table even ones which don’t correlate, it just set the extra values to null.

ble even ones which don’t correlate, it just set the extra values to null.

Cross joins are bad leave them alone.

Self joins are a way of join a table on its self.

We are working on our assignment today.

We discussed how the position of the pieces will be track.

It was decided there are three attributes for each place column, row and ring.

If the column position +1 and -1 are full the pieces is stolen otherwise the program moves on to checking the row and finally if the piece is on the second column or row it check the row +1 and -1.

To manage this complexity I have decided to have a table to manage the board.

I have decided that I don’t need a scoreboard table as it only keeps track of score which can be handle by the piece table.

Based on the 15/5/19 Dat602 class.

Today we briefly covered delete, the most important note for this is if you don’t specifies what to delete, the delete function will automatically delete all of the things from the table.

If we don’t the query will run if it correct an we will loss all data and be sad.

‘*’ Gives you all of the columns when use in SQL this will be useful for something like a select statement that we might want to select all of the columns in a table but don’t want to type them all out, I should probably use this with my project to save my self time and also make my project easier to read.

When using delete if we delete something which is used as a foreign key we will get an error if we don’t delete the text which is being referenced first.

This is a way around this called cascade delete, we will cover this in a future lesion however I might want to look into this future as it might be useful with my project as it will stop us from having to write multiple delete query for one function of the program.

Based on the 14/5/19 Data 602 class.

To day I got my head around crud I’ve have taken my user walk-though story board and taken the interaction with my database I have found and added them to my Crud Table. This is useful to know as crud tables let use all of the useful SQL statements that we will need to Manage out program.

We will use update statement to change columns which already have values this will be useful for the program for statements such as increasing score.

I need to think about interesting was of using this statement, it easy to use Update to simple update a specific row or column but using SQL’s logic we can update information in interesting ways which might be useful for our program, for example we a piece is take other 3 players have their scores updated we might be about to find a useful way of decreasing the players score at once.

We went though an example where we wanted to update someones age because it is their birthday, we did this via an update query, we used a where statement using CurDate() which get the current date, we used ‘=’ but there was a little confusion weather you may use the ‘=’ operator with dates or not, I will need to look into this for future information.

Based on the 13/5/19 data 602 class.

Avoid loop function as it goes against the nature of structured code and repeats a certain area of code with out any while function to leave.

[] Means is optional so you only have to add it if you want to. This comes up in documentation to show that an value can be set for a certain part of the code making it easier to read the documentation.

… Means you can continue on with as much as you like. This comes up in places like with insert statement where we can import as may set of date as we want with a single statement.

We are leaning about MySQL statement and there syntax.

The difference between a function and a procedure is that a function returns a value and a procedure doesn’t.

As with procedure functions need DELIMITER.(*1)

Concat is the the thing which lets you add multiple bits of data next to each other. We will most likely need this to create some sort of variable in our project for example we might want to save a record of a we could fuse the name and the date of the game.

To get a function to produce an out put we need to use select statements.

SQL case functions only preforms one of the when conditions and not all of them this is useful as we will need a functionality like this for looking up usernames, since we will only have one of each username at any given time we will want the code to stop after it has found the username.

While is the better version of loop as it check for a condition, this prevents us from aimlessly running though functions and having to make a limits. This is useful to know because making code readable is an important part of programing and loops make code harder to read.

While loop check for condition at the start and repeat conditions repeat code if a condition at the end of a statement is true.

We will likely use IF statement to preform the log in function for a project.

Has to tell a function is Deterministic to show that the function will give a predictable return value.

(*1) MySQL :: MySQL 8.0 Reference Manual :: 13.6.5.2 IF Syntax, 13/05/2019, https://dev.mysql.com/doc/refman/8.0/en/if.html.