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.

We learnt about crud matrix today. We started with Crud first development, which means we took and entities and expanded on, from there Todd created a query to retrieve that piece of information and check if the username is in existence.

He then turned it into a Procedure.

We are learning this is this is a good way to plan out our project as it allows us to workout what funtions we need before we start planning things out

Some important notes from this is procedure have to start and ends with Delimiter which is what I missed about the first day work.

The crud matrix has a representing of what type of query is need to preform a specific action.

(C)reate = insert a record

(R)etrieve = Select

A schema is a way of making sure that we know the names of all of the different variables and there types.

This post is based on the 7/5/19 Dat602 class.

We have learnt about what a CRUD matrix is!

We learnt this because it is important for transitioning from screen designs to a program which properly communicates with a database.

A CRUD table is a table based on the screen designs we create. It stands for Create, Read, Update, Delete.

///// The table/matrix contains the events which we can expect to happen based on a screen design and how the date base will be changed based on this action. ////

We use 127.0.0.1 as the address for running a local database. This is the address that needs to be enter as we create the database. This will be useful in the future , as we will probably use the same loopback address.

“ Back quotes are used to be able to bypass a reserved word and name a column after that word. However this is not recommended, as it could create confusion. I would only use this when I am facing errors and trying to isolate the problem or if I am in a situation where a column needs a specific name.

I found that back quotes do not work when you try and insert information into the table.

I couldn’t keep up with the second part of the work, so the program isn’t functional. I will need to look into this in my own time to try and make it work.