Jayson Gurney of SOHO IT Solutions

Federation of Small Businesses

2007-06-27T11:36:00.001Z

SOHO IT Solutions have opted to join the ranks of other like minded small business by become a member of the Federation of Small Businesses [FSB]. We hope that this will be the start of a healthy business relationship and look forward to meeting some of the local business representatives at the forthcoming networking events in the region.

Guild of Professional & Commercial Web Designers

2007-06-27T11:35:00.002Z

Today I meet Stuart Davies of the Guild of Professional & Commercial Web Designers to discuss SOHO IT Solutions becoming an associate member of the newly established Guild.

Stuart’s explanation of what the Guild was about was very interesting and fitted in well with what I was expecting and looking for. I am sure that in due course my membership will be approved and look forward to a long and professional relationship with the Guild and Stuart.

North East England Service Provider Register

2007-06-27T11:35:00.001Z

Today we applied to join the ranks of other like minded regional business by applying to join the North East England Service Provider Register.

New Entrepreneur Transatlantic Scholarships (NETS-UK)

2007-06-27T11:34:00.002Z

Today I received official confirmation that I had made the final selection for the New Entrepreneur Transatlantic Scholarships (NETS-UK) entrepreneurial trip to Babson College, Massachusetts. I will be attending the selection weekend in Liverpool over the weekend of 29th June - 1 July 2007. This is the final hurdle for this years selection process, from the 40 entrepreneurial candidates that have been selected from all across the UK only 20 will be chosen. I am very honoured to have made this final selection weekend, regardless of the outcome I am proud that I have made it this far. I wish the other 39 candidates all the best and look forward to meeting them on the 29th June in Liverpool.

One Northeast Approval

2007-06-27T11:34:00.001Z

The administrators of The North East England Service Provider Register, One Northeast gave SOHO IT Solutions official approved to be listed as a supplier today.

New Entrepreneur Scholarships (NES)

2007-06-27T11:31:00.001Z

Tonight is the our New Entrepreneur Scholarship graduation. The event is being held at the Ramside Hall Hotel in Durham. I have been elected by my fellow entrepreneurs to give a short speech. I am looking forward to the whole evening and would like to thank those who were involved in the programme, namely Eric Smalley, Neil Hannah, Katy Rain and Julia Gilliam from the University of Teesside.

Optimising your Data

2005-03-02T11:45:00.000Z

Week 4 refered us to the massive and extensive MySQL Reference Manual. In particular, we were drawn to Chapter 12: Getting Maximum Performance from MySQL.

A small and very useful section entitled Get Your Data as Small as Possible (section 12.3), listed various techniques for optimising your tables and minimsing storage space.

Optimisation Recommendations

Use the most efficient (smallest) types possible. MySQL has many specialised types that save disk space and memory.

Use the smaller integer types if possible to get smaller tables. For example, MEDIUMINT is ofthen better than INT.

Declare columns to be NOT NULL if possible. It makes everything faster and you save one bit per column. Note that if you really need NULL in your application you should definitely use it. Just avoid it on all columns by default.

If you dont have any variable length column (VARCHAR, TEXT or BLOB columns), a fixed size record format is used. This is faster but unfortunately may waste some space.

The primary key index of a table should be as short as possible. This makes identification of one row easy and efficient.

Only create the indexes that you really need. Indexes are good for retrieval but bad when you need to store things fast. If you mostly access a table by searching on a combination of columns, make an index on them. The first index part should be the most used column. If you are always using many columns, you should use the column with more duplicates first to get better compression of the index.

If it's very likely that a column has a unique prefix on the first number of characters, its better ti only index this prefix, MySQL supports an index on a part of a character column. Shorter indexes are faster not only because they take less disk space but also because they will give you more hits in the index cache and thus fewer disk seeks.

In some circumstances it can be beneficial to split into two tables that is scanned very often, This is especially true if it is a dynamic format table and it is possible to use a smaller staic format table that can be used to find the relevant rows when scanning the table.

MySQL Reference Manual v3.23.33 (2001)

My Conclusion

I guess that the above recommendations will become easier to implement the more you use MySQL. Like they say practice makes perfect, and what better way to learn.

All the best Jayson

A Special Mention

2005-02-28T15:39:00.000Z

I just wanted to make a very special mention to my lovely financee Donna, who is supporting me 110% in my studies. Without her support it would be impossible for me to study and maintain this blog site. So from the bottom of my heart Donna,

THANK YOU FOR YOUR KIND AND LOVING SUPPORT

Jayson
xxx

TT380 : Week 4 - Summary Notes

2005-02-28T14:32:00.000Z

Okay it's now week 4 of the course and it's time to read hours 9 to 14 of the MySQL course book. So what are these 6 hours about?

Hour 9 - Populating your database tables.
Hour 10 - Selecting data from your tables.
Hour 11 - Advanced usage of SELECT statements.
Hour 12 - Modifying and deleting data.
Hour 13 - More about DELETE.
Hour 14 - Modifying table structure.

Hour 9 Populating your Database Tables

In this hour we will learn how to insert data into out database tables using a variety of methods.

The INSERT command
This MySQL command is the first that we will use to insert data into our database tables. To use this command we need to be in MySQL monitor. So open up a connection to your database and login as normal.

A detailed account has already been written for using this command, and can be accessed via the following link, click here.

It should be noted that although you do not need to specify the names of the column within your table when using the INSERT command. However if you don't then you must ensure that each field within your table has an entry in your INSERT statement even if its empty or is going to use the default value. For example surpose you have a table called employees and you want to add a new record. The employees table has the following fields;

employee_id, employee_startdate, employee_moddate, firstname, lastname, nickname

You are wanting to add the following data into a new record in this table:

James Hummersknott, start date: today. James does not have a nickname. The moddate is obviously also going to be today as thats when you lasted modified the record and the employee_id is your primary key and therefore auto increments.

The command we would use to insert this data is as follows:
INSERT INTO employees
(employee_id, employee_startdate, employee_moddate,
firstname, lastname, nickname)
VALUES
('0', now(), now(), 'James', 'Hummersknott', '')
;
Alternatively if we wanted to simplifiy this and reduce the amount of typing we could use:

INSERT INTO employees
VALUES
('0', now(), now(), 'James', 'Hummersknott', '')
;

The LOAD DATA INFILE method

This is the second method of importing data into your database and again this has been documenetd previously, click here to view the detail blog entry about how to use the LOAD DATA method.

Using mysqlimport

This is our 3rd method of importing data into our database. the mysqlimport must be run from a command prompt and uses a plain text file that contains the data that you want inserting. This command has various switches that you can alter to suit you own personal needs. The following is the basic summary of the syntax that is used for this method of data insertion:
mysqlimport [options] databasename textfile
Okay let's look at that command, the first bit is calling the mysqlimport function. Then we have the chance to enter any options that we wish to use, common options here are:

-L to use a local file
--fields-enclosed-by
--fields-escaped-by
--fields-terminated-by

I have only used 3 of these along with a couple of other ones when I perform my own data insertion using this method. This is the complete command syntax that I used to successfull insert some data from a text file:
mysqlimport -L -v -u jg3723 -pmypassword
--fields-terminated-by=::: --fields-enclosed-by=\"
jg3723 master_name.txt
This successfully entered the contents of master_name.txt into my database called jg3723. It should be noted that this method of data insertion will import the data into the table of the same name as the text file, so in the case of the above example it inserted the data into a table called master_name.

My master_name.txt file consisted of the following data:

"0":::now():::now():::"Jayson":::"Doe"
"0":::now():::now():::"Donna":::"Smith"
"0":::now():::now():::"Tabitha":::"Carr"
"0":::now():::now():::"Naomi":::"Bell"

Hour 10 - Selecting data from your tables

Still to be documeneted - sorry!

Hour 11 - Advanced usage of SELECT statements

Still to be documeneted - sorry!

Hour 12 - Modifying and deleting data

Still to be documeneted - sorry!

Hour 13 - More about DELETE

Still to be documeneted - sorry!

Hour 14 - Modifying table structure

Still to be documeneted - sorry!

My Conclusion

Well that's the end of this particular blog entry and I hope that some of the information is of help to you. Please feel free to comment, all questions will be answered to the best of my ability.

All the best Jayson

Importing data into your TTCFM database

2005-02-28T12:03:00.000Z

This blog will hopefully help those of you that are experiencing problems when trying to upload/import data into the various tables of your database that is located on the OU server. I have tested each of the methods listed here. The data was successfully uploaded and imported into the relevant tables, with no records being skipped and no warning.

Please Note: That if you are using these methods on your own local installation you may well get warnings, I currently have a local setup using the version of MySQL that was distributed on the Certificate of Web Application Development CD-ROM, namely version 4.0.17 and after numerous hours of testing it appears that for some reason, unbeknown to me at present warnings are given when I used the methods detailed in this blog entry. But hey this is for the OU server, and thats the main concern at the moment afterall.

Using the INSERT method

Okay this is the exact procedure that I used to perform this data insert. Using SSH secure shell open a terminal window to the OU server. Change to your web directory using the following command syntax:
cd web
Log into the MySQL monitor using the following syntax, changing the jg3723 to your OU username:
mysql -u jg3723 -p
When prompted enter you OU PI number remember letters should be in CAPITALS. Select the database to be used, in this case it should be the same name as you OU username, using this command syntax:
USE jg3723;
Now its time to write the INSERT statement, for test purposes you could use the following, which is what I entered:
INSERT INTO master_name
VALUES ('',now(),now(),'Amanda','Gurney');
Hopefully, you should see the following message after pressing the ENTER key.
Query OK, 1 row affected (0.00 sec)
Well that's the INSERT method successfull used, so it's now onto the LOAD DATA method next.

Using the LOAD DATA Method

With this method I pre wrote a DDL script and a plain text file that contained the data to be imported, these two files were then uploaded to my OU web directory If you would like to use my DDL script and data file click here to down load them.

Hopefully you will still have your SSH terminal window open and logged into your MySQL database. If not please connect and login to your database again.

You now need to change the DDL file to point to your own database; using Notepad open the loaddata.sql file and change the first line so it reads the name of your database. Then save the file and close it.

Now upload the DDL script and data file to your OU web directory. To check that they are there use the following syntax:
ls
Hopefully you will see the a list similar to mine below:
[jg3723@ttcfm web]$ ls
 all_names.cfm  createtables.sql  loaddata.sql     mytidy.sql
 contactDB.sql  eca               master_name.txt
 [jg3723@ttcfm web]$ 
Okay the files are there so let's import the data. This is done by using the following syntax:
source loaddata.sql
Hopefully you will see the following messages displayed after pressing the ENTER key.
Reading table information for completion of table and column names

 You can turn off this feature to get a quicker startup with -A


 Database changed

 Query OK, 4 rows affected (0.00 sec)

 Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

My Conclusion

Well that's the end of my LOAD DATA and INSERT summary, you should now be able to alter the various commands and files to import any data that you wish to insert into your database. Have fun!

All the best Jayson

8th Kyu - Kamishin Ryu

2005-02-28T10:52:00.000Z

In order to undertake my 8th Kyu grading I required a minimum of 44 hours traning and was required to demonstate the following to an acceptable high standard:

Kamae
Fudo Dachi
Sanchin Dachi
Zenkutsu Dachi

Uchi Waza
Chudan Tsuki

Keri Waza
Kin Geri

Uke Waza
Jodan Uke

Kata
Taikyoku sona Ich

Kumite
Not required

Renzoku Waza
Mae Geri Gyaku Tsuki

Personal Satisfaction and Acheivement

I was pleased to accept this grade in July 2004. I say please because I feel that if I did not want to accept the belt, grade and responsibilty I could have declined the grade/belt when it was offered to me by my Sensei.

What responsibility I hear you say.
Well okay maybe at this low grade it may look to on-lookers that you have none, but in reality you do. You have the responsibility to yourself, your sensei and others to continue to demonstrate the levels of standard that have been achived by yourself whilst grading. Admitted, once one progresses up throught the grades, more responsiblity is emposes on you, but I feel that at all levels you have a responsibility to demonstate those techniques that you have been accessed on to to all newcomers. Doing this as an early stage, I feel will make you a better dan grade once you achieve that level of experience.

Martial Arts

2005-02-28T10:03:00.000Z

Another blog entry that is away from the subject of OU courses, but one that is close to my heart. I have always been keen on all forms of martial arts since an early age. As a child I tried various styles of Karate and even Judo, but like most children I did not stick at it, which I deeply regret now! So any youngers that read this who are training in any style of marital arts - my advise to you is STICK AT IT!

In March 2003, I located a Karate Dojo that interested me. I found a suitable Dojo that was situated in Darlington, County Durham. They offered a good choice of classes, 2 styles of Karate, Kick-Boxing and Kobudo. I promptly joined the club and started training in Kamishin-Ryu karate several times a week and also Kobudo once a month. Due to my OU commitments I have had to stop attending Kobudo, classes but plan to start up again.

So what is Kamashin-Ryu karate?

Duis autem vel eum iriure dolor in hendrerit in vulputate velit esse molestie consequat, vel illum dolore eu feugiat nulla facilisis at vero eros et accumsan et iusto odio dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te feugait nulla facilisi. Typi non habent claritatem insitam; est usus legentis in iis qui facit eorum claritatem. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Nam liber tempor cum soluta nobis eleifend option congue nihil imperdiet doming id quod mazim placerat facer possim assum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Eodem modo typi, qui nunc nobis videntur parum clari, fiant sollemnes in futurum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum.

My Sensei's

Sensei Phil Snewin

Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Eodem modo typi, qui nunc nobis videntur parum clari, fiant sollemnes in futurum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum.

Official Website: kamishin ryu karate do

Sensei Fred Bateman

Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Eodem modo typi, qui nunc nobis videntur parum clari, fiant sollemnes in futurum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum.

Official Website: kodokan martial arts

What is Kobudo?

Duis autem vel eum iriure dolor in hendrerit in vulputate velit esse molestie consequat, vel illum dolore eu feugiat nulla facilisis at vero eros et accumsan et iusto odio dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te feugait nulla facilisi. Typi non habent claritatem insitam; est usus legentis in iis qui facit eorum claritatem. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Nam liber tempor cum soluta nobis eleifend option congue nihil imperdiet doming id quod mazim placerat facer possim assum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Eodem modo typi, qui nunc nobis videntur parum clari, fiant sollemnes in futurum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum.

My Sensei

Sensei Fred Bateman

Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Eodem modo typi, qui nunc nobis videntur parum clari, fiant sollemnes in futurum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum.

My Kamishin-Ryu Gradings

Although obtaining a blackbelt is one of my life long ambitions, I have decided that this should not be the main aim of my martial arts training. Continuous training, personal develpment and commitment to training will I am certain leaded me to my life long ambition. But unlike some people, training and development and learning will not stop there, to me that will be the start of my next level of marital arts training.

8th Kyu grading syallabus

7th Kyu grading syallabus

6th Kyu grading syallabus

All the best Jayson

A Little More About Me

2005-02-28T09:21:00.000Z

I thought I would include a little section devoted to me. This section is where I am going to publish information relating directly to me. It's probably not going to be that interesting but I thought it would make a change from all the course related material that is going to be published here.

1970

On the 24th January 1970 I was born to my parents, Susan Water and Barry Gurney in Chelsea, London.

1986

I left secondary school with only my Certificate of Secondary Education qualifications (8 in total) and I decided that it was a case of going to 6th Form to try and get a few General Certificate of Secondary Education under my belt.

1987

I left 6th Form with a few better grades for some of my Certificates of Secondary Education (9 in total), newly aquired General Certificate of Secondary Education (3 in total) plus a couple of other qualifications; one by the Royal Society of Arts and a newly form qualification which I was one of the first in the county to take, namely a Certificate of Pre-Vocational Education.

1988

4 th January 1988, I waved good-bye to my mum at the train station in my home town of Ashford and set off on my journey to join the Royal Corps of Transport, whos training depot was based in sunny Aldershot.

After completing my basic military training I embarked on trade training where I gained my Heavy Goods Vehicle License. It was then time to join my first active unit, 66 Squadron Royal Corps of Transport, based in Tidworth, Hampshire. After a few month at the unit it was time to head of to Germany for a NATO training exercise, which unbeknown to me was going to be the changing point of my military career. After just 3 days in Germany, I substained a serious injury to my spine, which took several hospitals and doctors many hours to decide what needed to be done.

1990

Finally, the British Military decided that I needed a spinal fusion which they performed on my in the March. After spending some time off to recover, it was time for intense rehabilitation, which lasted several months. I was unhappy at this as it meant I was unable to go to Cyprus with the main unit, instead I was left behind on reserve party duties. Things turnt out ok in the end as when I returned to my unit I was promoted to Lance Corporal.

1991 - 1993

Various posting around the UK, and in January 1993 I was medically discharged from the Army.

1994 - 1997

Time spent recoverying form ongoing medical problems realting to my spinal injury. Mid 1997, I went to college to gain qualifications in Information Technology and started to do some teacher training related courses.

1988

Gained an NVQ Level 3 in Implementing IT Solutions and part of my NVQ Level 4 TDLB. Started work as an on-site maintenance engineer.

1999

After careful consideration, I started my own IT consultancy company and my first major client was Orange PCS. Went to Germany and Italy to do consultancy work for two International Telecom companies.

2000

Started work for an International Internet hosting company - Senior Network Administrator and Project Manager. Designed and implemented a corporate wide Microsoft messaging platform. Obtained Microsoft certification status.

2003

Started to study with the Open University, with the aim of gaining various IT and Computing qualifications whilst on-route to acheiving a BSc Honours degree.

2004

Obtained 8th & 7th Kyu in Kamishin Ryu Karate. Reunited with my father after 28 years at Darlington Railway Station. Meet my 3 sisters, Elizabeth, Nicola and Lauren for teh first time in December 2004. Also meet about 35-40 other relatives from my fathers side of the family.

MySQL version 4.x Quick Reference Guide

2005-02-27T15:41:00.000Z

There are loads of Quick Refernce Guides available on the WWW for MySQL, so I have create mine with a slight twist in that I have also included notes on where they have been used in course book/weekly study guides. That way hopefully it will help you see exactly how each command/function can be used.

COLUMN TYPE

[NATIONAL] CHAR (M) [BINARY]
county CHAR (75)
pg 80
[NATIONAL] VARCHAR (M) [BINARY]
pg 79
BIGINT [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
BIT
not used as yet
BLOB
not used as yet
CHAR
not used as yet
DATE
not used as yet
DATETIME
not used as yet
DECIMAL [ (M [,D] ) ] [ZEROFILL]
not used as yet
DOUBLE PRECISION [ (M,D) ] [ZEROFILL]
not used as yet
DOUBLE [ (M,D) ] [ZEROFILL]
not used as yet
ENUM ('value1' , 'value2' , 'value3', and so on)
pg 80
FLOAT [ (M,D) ] [ZEROFILL]
not used as yet
INT [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
INTEGER [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
LONGBLOB
not used as yet
LONGTEXT
not used as yet
MEDIUMBLOB
not used as yet
MEDIUMINT [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
MEDIUMTEXT
not used as yet
NUMERIC (M,D) [ZEROFILL]
not used as yet
REAL [ (M,D) ] [ZEROFILL]
not used as yet
SET ('value1', 'value2', 'value3' and so on)
not used as yet
SMALLINT [ (M) ] [UNSIGNED] [ZEROFILL]
pg 79
TEXT
pg 81
TIME
not used as yet
TIMESTAMP [ (M) ]
not used as yet
TINYBLOB
TINYINT [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
TINYTEXT
not used as yet
YEAR [ (2|4) ]
not used as yet

COMPARISON OPERATOR

=
not used as yet
< >
not used as yet
!=
not used as yet
<= not used as yet
< not used as yet
>=
not used as yet
>
not used as yet
<=>
not used as yet
COALESCE (list)
not used as yet
expr BETWEEN min AND max
not used as yet
expr IN (value, and so on)
not used as yet
expr NOT IN (value, and so on)
not used as yet
INTERVAL (N,N1,N2,N3)
not used as yet
IS NOT NULL
not used as yet
IS NULL
not used as yet
ISNULL (expr)
not used as yet

LOGICAL OPERATORS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

CONTROL FLOW FUNCTIONS

COMMAND HERE
not used as yet

STRING FUNCTIONS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

STRING COMPARISON FUNCTIONS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

MATHEMATICAL FUNCTIONS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

ARITHMETIC OPERATORS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

DATE AND TIME FUNCTIONS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

CAST FUNCTIONS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

BIT FUNCTIONS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

MISCELLANEOUS FUNCTIONS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

FUNCTIONS FOR USE WITH GROUP BY CLAUSES

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

INSERT

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

SELECT

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

JOIN

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

HANDLER

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

UPDATE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

DELETE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

TRUNCATE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

REPLACE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

UNION

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

LOAD DATA INFILE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

CREATE DATABASE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

DROP DATABASE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

CREATE TABLE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

DROP TABLE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

ALTER TABLE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

RENAME TABLE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

CREATE INDEX

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

DROP INDEX

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

USE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

DESCRIBE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

BEGIN/COMMIT/ROLLBACK

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

SET TRANSACTIONS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

Installing ColdFusion MX 7 on a Windows XP Pro SP2 Platform

2005-02-27T14:29:00.000Z

The aim of this installation guide is to give you a clear and easy to understand guide for installing a local version of ColdFusion MX 7 Developer Edition onto a Windows XP Pro SP2 platform for use during the study of TT380: Databases within Website Design.

Step 1: Download the required installation files
Step 2: Prepare system for installation
Step 3: Install ColdFusion MX7 Developer Edition
Step 4: Install and Configure MySQL ODBC data source
Step 5: Test your installation and configuration

STEP 1: OBTAIN THE REQUIRED INSTALL FILES

The ColdFusion Express version 4.0 that is on the Cert in Web Apps Development CD-ROM has caused me numerous nightmares when it came to configuring my MySQL ODBC data source. My answer was to install a copy of the latest release ColdFusion MX 7 Developer Edition. This version is as far as I am currently aware FREE for development use so there is no time limit on its usage.

Okay let’s get the download! Using you web-browser, enter the following URL:

http://www.macromedia.com/cfusion/tdrc/index.cfm?product=coldfusion

If you are not already registered with Macromedia, you will need to do this and then revisit the above URL. Registration is FREE, simple and rather painless. When you return to the download page select the option to download ColdFusion MX 7 Developer Edition, which should be at the top of the page, then select the English Windows 275.79 MB option using the select button, then click on download. Save the file a suitable location on your system, so that you can easier retrieve it later.

BE WARNED that this download can take some time depending on you particular internet connection.

STEP 2: PREPARE SYSTEM FOR INSTALLATION

Whilst the installation file is downloading, it’s a good idea to gather the necessary information ready for your installation. Please remember that this installation brief is for use with an OpenSA web-server (Apache), which is distributed on the Cert in Web Apps Development CD-ROM. Note the following information which is what I personally used for my installation, I also used the default installation for OpenSA, so if you decided to install in any other manner you will need to alter the information below to mirror your own installations:

What is the serial number for ColdFusion MX 7?
Use the developer edition option
What is the type of installation?
Server Configuration
ColdFusion MX 7 installation directory?
C:\CFusionMX7
Configure web server or use built-in web server?
Configure
Choose web server to configure?
Apache
Apache configuration directory?
C:\OpenSA\Apache\Conf
Apache path to server binary?
C:\OpenSA\Apache
ColdFusion MX Administrator password?
You choose this but keep it a secret
Do you want to enable RDS?
NO

STEP 3: INSTALL COLDFUSION MX7 DEVELOPER EDITION

Okay you have the downloaded installation file and the required installation notes. Let’s get on with the main installation then. Ensure that the Apache web server is running you can do this by checking the service via the Windows XP Administrators control panel. Shut down all previous ColdFusion services if you have previously installed them – again this can be performed via the Windows XP Administrators control panel. Now double click on your downloaded installation file and follow the on screen instructions. Be sure to use the information that we gathered in Step 2, to ensure that the correct information is given to the installation routine. Hopefully all went well for you and you now have an installed ColdFusion MX 7 server.

STEP 4: INSTALL AND CONFIGURE MYSQL ODBC DATA SOURCE

Using the ColdFusion Administrator which is accessed via your web-browser and is delivered to you via your local web-server we are now going to configure your MySQL data source. Open your web-browser and enter the following address:

http://localhost/cfide/administrator/index.cfm

Enter the password that you set during the setup routine then click on the login button. You will then be in the ColdFusion Administrator, so now on the left you will see a link to Data & Services, click on this and then click on the Data Source link. We are now going to add a new data source, so enter the name of the data source, for the purposes of this installation guide enter the following: chelsea then using the selection button below select MySQL (3.x) as the driver. Then click the add button.

The next page is where we are going to configure this new data source. Enter the following information into their respective boxes:

Database - contactDB
Server - localhost
Username - root
Password - your MySQL root password

Then click the submit button, if all was ok with the data that you have just entered, the screen will change back to the original Data Source screen, BUT the newly created data source named chelsea will be verified as OK.

STEP 5: TEST YOUR INSTALLATION AND CONFIGURATION

Using your HTML editor or Notepad, enter the following test code and then save then save it as odbctest.cfm Sample test script is as follows:
<>html<>
<>head<>
<>title<>ColdFusion ODBC Test< /title >
< /head >
<>body<>
<>cfquery name="databases" datasource="chelsea" debug="yes">
show databases;
< /cfquery >
<>cfoutput<>
<>p<>Today is #DateFormat(now())#< /p >
< /cfoutput >
< /body >
< /html >
Once the above has been created and save it to your root web directory, open up your web-browser and request the page via you local web-server. Calling the test file is simple - enter the following into your web-browsers address bar:

http://localhost/odbctest.cfm

If all is working correctly then the page will display the current date in your browser. Please note that the script only attempts to make a connection to your data source, and that no database data will be displayed. If the current date is not displayed then unfortunately, something is a miss. But if all goes well this will not be the case.

My Conclusion

If you managed to get the current date displayed in your web-browser, then it looks like you have successfully installed and configured ColdFusion MX 7 Developer Edition to connect and access your MySQL data source. WELL DONE!

So what next I hear you say – well the world is your oyster as they say. Now that this has been configured and tested, you can start to develop your own ColdFusion pages. I hope that this BLOG helps some of you with you local installation of ColdFusion MX 7, if you have any comments feel free to post them.

BTW - this was written from memory so I have my fingers crossed and hope that there are no errors, but I am certain that you will be the first to tell me if there is - any errors will be investigated and subsequent amendments will be made.

All the best Jayson

MySQL Installation Cleanup

2005-02-27T13:59:00.000Z

Okay so you have just installed your MySQL database onto your server. It's now an ideal time to perform a cleanup on it to delete the un-necessary users, privileges and databases. This BLOG details serveral steps that I recommend you perform on you newly onstalled MySQL sever. All of which I have performed on my own installation to check that they work correctly.

Here the simple list of tasks that this blog entry will aid you in performing:

Task 1 - Set the 'root' user password
Task 2 - Delete anonymous users that are installed
Task 3 - Delete the remote 'root' user
Task 4 - Delete the test database
Task 5 - Remove unwanted privileges
Task 6 - Flush the servers privileges

Task 1 - Set the 'root' user password

The first task that I would recommend you do is create a root password, as by default the installation of MySQL leaves this all important user account unprotected as the password is left blank.

So who do you create/set the password for the root user? Well we use the MySQL monitor, so open a command prompt window and navigate to your MySQL directory. This will depend on where you installed MySQL to and also on your operating system. For the purpose of this BLOG I will assume you have installed your system the same as I have done locally using a Windows XP Pro system. I installed MySQL to C:\mysql so when I open a command prompt I enter the following syntax to access the mysqladmin utility:
cd c:\mysql\bin    
Now enter the following command at the command prompt:
mysql -u root -p
If no password has been set, which should be the case for you if this is performed directly after a fresh installation of MySQL, you can just press the ENTER key. The following command will allow you to set a password for your super-duper root user:
SET PASSWORD FOR
'root'@'localhost'=PASSWORD('yourpassword');
Now that you have set the new password its advised to flush your privileges, this is performed using the following syntax:
FLUSH PRIVILEGES;

Task 2 - Delete anonymous users

This step will delete any anonymous users that are installed Note that the following command uses two single ' characters and not a "
DELETE FROM mysql.user WHERE User=''

Task 3 - Delete the remote root user

Now its time to removes the remote root user using the following command syntax:
DELETE FROM mysql.user
WHERE User='root'
AND Host !='localhost';

Task 4 - Delete the test database

Now we are going to delete the test database that the installation process creates. Use the following command syntax:
DROP DATABASE test;

Task 5 - Remove un-necessary privileges

Now its time to remove any un-necessary privileges from the mysql database using the following command syntax:
DELETE FROM mysql.db
WHERE Db ='test'
or Db = 'test\\_%';

Task 6 - Flush the privileges on the Server

Let's now flush the server's privileges and we are then ready to quit the MySQL monitor and start to use our lovely new MySQL server. Flushing is performed using the following command syntax:
FLUSH PRIVILEGES;

My Conclusion

Well that now concludes this BLOG and hopefully has helped you cleanup your newly installed MySQL server. If you have any comments please feel free to post them and I will try and answer them to the best of my ability.

All the best Jayson

DBManager Professional v3.0.2

2005-02-27T13:52:00.000Z

After attempting to install and use the version of DBManager that was distributed on the Cert Web Application Development CD-ROM, I encountered a few issues and decided that I would be better off using the latest version of the product.

This was support by a comment in one of the TT380 user conferences, where a fellow student mentioned they had successfully installed and could recommend the lastest version, namely version 3.0.2.

I waited no longer and opened my web-browser and navigated to the applications web-site to download the newest version. I was please to see that they had a FREE version available. You too can download this excellent Database Management Tool from:

http://www.dbtools.com.br/EN/downloads.php

The download was reasonably quick and very easy after first registering. Once it downloaded I proceded to install it, which again was an easy task to do.

After the initial installation the application loaded and I was presented with a very nice and professional looking interface. I was given the option on the right of the screen to Add a New Server. This needs to be setup before you can start using this excellent utility. Clicking on the Add new Server displays a server properties window. In this you need to select the various options, in my case I only needed to enter the root users password for my MySQL database. I then used the 'test button' and it confirmed it could connect to my database, I then click on the 'OK button' and my setup was finished.

I returned to the main display page and was presented with my MySQL server and all its databases, tables and users. It was that simple - and I highly recommend it to everyone who connects to any type of database.

Please feel free to leave comments on this Blog, I will try to answer any questions that arise to the best of my ability.

All the best Jayson

Securing a MySQL Server on Windows

2005-02-27T13:35:00.000Z

Most of my fellow TT380 collegues are most likely using a Windows based platform and if you are not lucky enought or even too lazy to build a suitable linux based server for the duration of this course you may well be interested in know how to secure a MySQL server based on a Windows Platform. I must also hold my hands high and say that I have not been bothered to install a suitable linux server.

I have not had the time to personally check each stage of this process yet - it's on my 'To-do-list', but due to the source of where I have located the information I would be willing to trust it. Mind you can't hold me to that should something go wrong - LOL.

The full and detailed article can be found at the following address:

http://dev.mysql.com/tech-resources/articles/securing_mysql_windows.html

But like most things on the WWW, it is subject to change, move and even deletion. For this reason I am going to summarise the steps to be taken in securing a Windows based MySQL Server. In-time I will recreate the entire article in my own words - thus avoiding plararism etc, and also include any additional information that I might have picked up along the way. This re-write is obviously also on my 'To-do-list'.

How to secure your Windows MySQL Server

The overal securing of a MySQL server installation requires various uniquie steps to be performed. It should also be noted now that once this procedure has been performed it still requires monitoring to ensure any new security breaches and back-doors etc are quickly identified and secured. I therefore recommend that you subscribe to a couple of the various security bulletins that are available on the WWW.

Okay that being said - lets identify the areas to be addressed:

Step 1: Install MySQL on a Recent, NT-based version of Windows

Step 2: Install MySQL on an NTFS File System
Step 3: Install MySQL on a Standalone Machine
Step 4: Install the Latest Production Version of MySQL
Step 5: Secure the MySQL User Accounts
Step 6: Disable TCP/IP Access
Step 7: Bind the TCP/IP Address
Step 8: Firewall the Server
Step 9: Run The MySQL Service as a Limited User
Step 10: Encrypt the Data Folder
Step 11: GRANT the Minimum Privileges Necessary
Step 12: Change the Name of the Root User

As previously mentioned, at this stage of my blog development, I have not had the opportunity to document or actually follow the above staged in fine detail. Given time I shall carry out a detailed investigation of these stages.

My Conclusion

Some of these steps have already been documented in my previous blog titled 'MySQL Installation Cleanup'.

As you can see with a few simple step you can easierly secure your newly installed MySQL server. Obviously, security is an on-going task and is also a specialised area. There are however, a vast amount of detailed articles, guides etc on the WWW to aid you in your quest to keep your server secure - Good luck and please feel free in sharing your comments.

All the best Jayson

TT380 - Useful References and Resources

2005-02-26T13:44:00.000Z

As with any courses the amount of references and resources used is enourmous. Just looking at the proposed reading guides contained in courses main weekly study guides, I now I am going to end up with loads of potential important links. If you are anything like me after a while your web-browsers favourites or bookmarks tend to get some what messy and unorganised.

I thought that whilst I was using my personal blog for making course notes, comments and summaries, why not use it to manage my resources and references as well. So here we are at my resource and reference blog entry for TT380.

Web Articles and Sites

Official TT380 Course Web-site
Normalising your database - First Normal Form (1NF)
Normalising your database - Second Normal Form (2NF)
Normalising your database - Third Normal Form (3NF)
Microsoft Consulting Case Studies
Unisys Case Studies
Transaction Processing Performance Council
Database Server Performance and Tuning
Background SQL
Ocelot Computer Service Inc.

Scripts

no entries yet

Other

no entries yet

If you have any suitable links that you feel could be added to the list, please feel free to use the comments section, which I will review and using the given information update this blog entry.

I hope that this page is as much use to you as it is too me! Feel free to leave comments via the comments section on this blog entry.

All the best Jayson

TT380 : Week 3 - Summary Notes

2005-02-26T13:29:00.000Z

Okay it's now week 3 of the course and it's time to read hours 6 to 8 of the MySQL course book. So whats these 3 hours about?

Hour 6 - Planning and creating your database
Hour 7 - Creating your database tables - Part I
Hour 8 - Creating your database tables - Part II

Hour 6 - Planning and creating your database

Okay, lets see about creating our database. One way to do this is via the mysqladmin using the following command:
mysqladmin -u username -p create datebasename
I have not added the password for the user, instead it will be prompted once the command has been entered. Alternatively, if you want to include the password within the command use the following syntax:
mysqladmin -u username -ppassword create datebasename
So lets see the whole command with dummy username, password and database we want to create:
mysqladmin -u root -pA51gA23 create chelseafc
Once the database has been created it needs to have its permissions and privileges set. Its advised to create a new user other than the main root user account who can administer your newly created database. We can do this using the MySql monitor using a command similar to the following:
mysql -h localhost -u root -p
You will be prompted for the root user password, enter it and press enter - Remember that its case sensetive. We the need to use the mysql database which holds all the servers permission and accounts etc.
use mysql;
Now we are ready to enter the command to create our new database admin user who we will call supercontact for the chelseafc database.
GRANT ALL ON chelseafc.*
TO 'supercontact'@'localhost'
IDENTIFIED BY 'password';

Hour 7 - Creating your database tables - Part I

This hour of the book is about the common data types that you can define within your MySQL tables and creating tables. So lets create our table:
CREATE TABLE players (
player_id SMALLINT UNSIGNED,
player_dateadded DATETIME,
player_datemodified DATETIME,
firstname VARCHAR (75),
lastname VARCHAR (75)
);
The above statement would create our table but we can improve this which is what we will do in the next hour. To issue the command to create the above table as it stands you would issue it via the MySQL monitor, remember you need to login to the monitor first.
mysql -u supercontact -p
Enter the password when prompted. Now we need to tell MySQL what database we wish to create the tables in:
USE chelseafc;
We can now enter the above statment into the monitor. Alternatively, we could use an external file, that contained the statement. This method is my prefered method of issuing commands, as it allows me to create them in advanced or via E-R diagram applications (E-R diagrams will be a subject for a later blog entry). To create the external file I use Notepad and enter the command(s) that I want to issue. The file is then saved to a suitable location with a meaningful name and the following file extension .sql. At the top of the file you should add a line that tells the server what database you want this table to be created in. So following our previous table creation example the new file would read:
USE chelseafc;
CREATE TABLE players (
player_id SMALLINT UNSIGNED,
player_dateadded DATETIME,
player_datemodified DATETIME,
firstname VARCHAR (75),
lastname VARCHAR (75)
);
To use this file to create our database table use the following syntax:
mysql -u supercontact -p

Hour 8 - Creating your database tables - Part II

The aim of this hour is to define our data types for the table fields, add keys, indexes and to create our final table creation statement. First of our table needs a primary key which can not contain a NULL value. This is done by adding the following to the chosen table field:
NOT NULL PRIMARY KEY AUTO_INCREMENT
The next step is to think about what fields in the table should be indexed, indexing helps improve searches BUT care should be given as you can degrade a servers overall performance if you index all data fields. The syntax for indexing a field is as follows:
INDEX idx_fn (firstname);
So lets put this all together and create are full table creation statement ready for use.
USE chelseafc;
CREATE TABLE players (
player_id SMALLINT UNSIGNED NOT NULL
PRIMARY KEY AUTO_INCREMENT,
player_dateadded DATETIME,
player_datemodified DATETIME,firstname VARCHAR (75),
lastname VARCHAR (75),
INDEX idx_fn (firstname)
);
Thats all that needs to be entered in the text file, enter this and save it as myfirsttable.sql The time has arrived to create the table. This is done via the MySQL monitor using the following syntax:
mysql -u supercontact -p 
You will be prompted for the supercontact password, enter this and press ENTER. To check that the table was created we can use the following syntax from within the MySQL monitor:
SHOW tables;

My Conclusion

Well this concludes the main areas for Week 3. Obviously the book goes into more detail, but hopefully you will get the main points from this summary. Please feel free to comment, or post questions relating to any of the areas discussed in this Blog entry.

All the best Jayson

TT380 - Week 2 Summary Notes

2005-02-24T21:50:00.000Z

Okay its the 2nd week of the course and its time to read Hours 4 and 5 of the MySQL course book. So what are these 2 hours about?

Hour 4 - Using the MySQL Client
Hour 5 - Securing MySQL
Hour 4 - Using the MySQL Client

Hour 4 - Using the MySQL Client

In this hour the aim is to become familiar with the following client tools:

mysql - Built-in MySQL Monitor

mysqladmin - Built-in Administrator tool

mysqldump - Built-in backup utility tool

phpMyAdmin - 3rd party add-on

MySQL-Front - 3rd party add-on

MySQLManager - Add-on

MySQL Monitor
This is a command-line utility that is shipped with MySQL. To access this utilty simply enter the following at a command prompt:
mysql
If you have not done anything to the initial installation of your server this simple command will log you into your MySQL server with full blown access permissions. This is due to the default installtion creates a root user with no password. As previously mentioned in other blog entries this is a security risk and MUST BE ADDRESS ASAP.

Once the root user has a valid password, you will still be able to login to the server using the same command, but this time you also include a user and password string. See below for an example of login as the root user:
mysql -u root -p
The above command will prompt you to enter the password for the root user, once this has been entered you have full access once again. Once you have logged into your server you will probably want to access a database, this is acheived by issuing a statement to the server telling it what database you wish to use. See the example statement below for the syntax:
USE databasename;
A full list of commands that are available to you when you are logged in can be displayed using the following:
help;
How to exit the MySQL Monitor
To exit the monitor and ensure maximum security for your server always log-out correctly. This is again a simple process - just type in the following:
exit;
Using mysqladmin
This admin applicatins is used to create and drop databases and viewing the current server status. It should be noted that if you are using this application throught your ISP you may find that you are limited to what commands can be issued. You can obtain help by using the following:
mysqladmin --help
To create a database:
mysqladmin create databasename
To drop (delete) a database:
mysqladmin drop databasename
Using mysqldump
This application allows you to backup your database table structure and also create the relevant DDL scripts that can be used to recreate your entire database scheme. A extreamly useful option of this particular tool is the ability to export the schema to an external DDL file or sql script. This is performed using the following command:
msqldump databasename > filename.sql
I recommend that you always use this function prior to dropping (deleting) a table/database. Like most of the command you can obtain help for this function using the following syntax:
mysqldump --help
phpMyAdmin
This 3rd party add-on provides all the same functions as previously discussed in this blog. It however, requires not only a MySQL server, but also PHP and a web-server. Its very popular in the web-hosting arena, as it allows users to manage their own web-space, database etc. For the purpose of this course namely TT380, I have not installed this locally, but it's on the 'To-do-list'.

MySQL-Front and MySQLManager
These are another two excellent frontend GUI interfaces to allow you to manage your MySQL server. Again I have not installd these and its therefore unfair for me to comment on their uses etc. I do know that some course members are however using them. I instead opted to useanother 3rd party application, DBManager Professional v3.0.2. I have blogged seperately about this particular application.

Hour 5 - Securing MySQL

This hour is aimed at teaching your about securing your installation of MySQL. MySQL is continuosly monitoring 3 key things:

Where you are accessing from (your host)

Who you are (username and password)

What you are allowed to do (your privileges)

When MySQL is installed a database called mysql is automatically created and it has several tables defined within it. This database is used to record all privileges that are set on all users, fields, columns, tables and databases.

The 2 Step Authentication Process
As previously mention MySQL checks 3 things during its authentication process, These are performed in 2 steps:

Step 1 -MySQL looks at the host from where you are connecting from and the username and password pair. If the host is allowed to make the connection, your password is correct for the given username and finally the username is assigned as being able to access from the said host MySQl goes onto Step 2. If this step fails an error message is given.

Step 2 - MySQL then checks that the command that you are trying to issue is infact allowed to be issued by you on the database, table or field.

It is a common practice for applications to utilise this type of authentication, it allows for different levels of users, basic users can select data to view it whilst others are given great access allowing them to insert and delete data. Working with User Privileges Depending on you set-up will determine what permissions you have.

For example if you are connecting to your ISP hosting, the chances are you will only have a single user account and one database. Your users will however, have full control allowing you to create, delete, insert as you please on you won database. On the other hand if you are running a local server for say an internal intranet, then you mau have several users and several databases. Running your own server will almost certainly require you to add, amend and even delete users. So let us now look at how you can do just that!

How to Add Users
Adding users is very easy using DBManager Professional or a similar third-party application, however, its also very easy to do via a command-line using the GRANT command. You need to access the MySQL monitor using the root user. Once logged into the server you could use a command similar to the following to create a new user:
GRANT privileges
ON databasename.tablename
TO username@host
IDENTIFIED BY 'password';
Obviously the privileges given would depend on the users role, a complete list of these will follow this section. The example above uses both databasename and tablename, this would allow you to restrict the users privileges to that particular table, as opposed to allowing privileges across the entire databasename [replace .tablename with .*]. The username and host I think are like the password - self explanatory.

However, an additional wildcard is available to you; using % rather than the host would give way to a very large host list.

The other way is to use an INSERT command statement.

Removing Privileges
At times it is necessary to revoke certain users privileges, again this is easierly done via any of the previously mentions third-party applications. And again its just as easy to issue the command straight from a command-line. The syntax for this command is as follows:
PEVOKE privileges
ON databasename.tablename
FROM username@hostname;
As with before the privileges to be revoked would depend on the user, the databasename/tablename and hostname again can also be dealt with like those mention in the GRANT command above. Changes to the privileges table take immediate effect but as a safe measure and for the sake of a few extra keystrokes it's advised to issue a FLUSH PRIVILEGES; command statement.
To completely remove a user from the database for example, when an employee leaves the company, you would need to issue a special DELETE statement to remove the said user from the user table in the mysql database. So what's this special DELETE statement that I have to issue to delete a user completely I hear you say! The statement below will do just that for you:
DELETE FROM mysql.user
WHERE User='username'
AND Host != 'localhost';
That should delete the user specific [replace username with the relevant username] who is connected via localhost from the main mysql.user table.

My Conclussion

Well once again we come to the end of this weeks study from the course book. Please feel free to post any comments. I will attempt to answer any questions to the best of my ability.

All the best Jayson

TT380 - Week 1 Summary Notes

2005-02-24T21:28:00.000Z

Okay its the first week of the course and its time to read Hours 1 to 3 of the MySQL course book. So whats these 3 hours about?

Hour 1 - Introduction to MySQL
Hour 2 - Understanding Database Terminology
Hour 3 - Learning the Database Design Process

Hour 1 - Introduction to MySQL What is MySQL?

MySQL [pronounced my ess que ell] is probably the most widely used Open Source Database. It is used for personal web-site, corporate intranets and large multi-national high traffic web applications. MySQL is a relational database management system (RDBMS). It is a very fast, robust and easy to use system compared to counter-parts like Oracle. MySQL is also FREE to use in most cases, but a commercial version does exsist.

Depending on the version of MySQL that you use will depend on whether certain standard relational database features are available to you. I believe that Pre version 4.x of MySQL lack the ability to use the following standard features:

transaction support

foreign keys

sub-selects within queries

What is a Relational Database?

A relational database is collection of tables which are made up from coloumn and rows. The tables relate to each other through specific values contained in certain columns.

Hour 2 - Understanding Database Terminology What makes a Database?

A database created using MySQL creates three seperate files on the servers filesystem. These three files contain the table definations, the data and the indexes used.

Tables

The table is the largest element of a database and in order of creation its second inline after the database it's self. There are several types of tables but that will be learnt later in the book/course/blog.

Fields
These give structure to your tables and provide a place to store your data. A table can contain a maximum of 3,398 fields, but it is unlikely that any well design database would have this many in a single table. Defining fields is possibly the most important part of designing an effective and efficient database. Fields can be assigned default values. When a record is created any default values are automatically entered into the relevant fields.

Records
A record is an entry in the tables. A record may be complete or incomplete. Incomplete may or may not mean empty it all depends of how you defined your fields. Records that contain all fields with valid data are seen as important if you want to maintain accuracy within your data.

Keys
Keys are a powerful element of your tables and records. Keys are used to link tables together. These are defined by assiging them to fields when creating your tables. MySQL have to types of keys; Primary and Unique. A unique key means just that no other field can contain the same data. A primary key is similar must also contain unique values, but it is also used to link tables together. Each table can only have one primary key, but it's allowed various unique keys.

Indexes
An index in a database can be compared to an index at the back of a book. It provides a reference to important and key information. Primary keys are automatically indexed but any other field can be manually indexed. Indexing can greatly improve the speed of your web application, but caution should be used. It's recommended that you only index fields that contain values that would be used in a search criteria.

Hour 3 - Learning the Database Design Process Table

Types Relational Databases as you can guess by it name, establish relationships between the tables it contains. There are various forms that these relationships can manafest in:

One to One

One to Many

Many to Many

One to One Relationship
This type of relationship is when a key in one table only appears once in another. An example is if Table A contained a persons name and Table B contained their date of birth. This is because a person can only have one birthday.

One to Many Relationship
This type of relationship is where a key in one table may appear many times in another. An example is Table A contains car manufactures and Table B contains models. This is because a car manufacture can have serveral models of cars.

Many to Many Relationship
This type of relationship normally causes issues in well design databases. It is therefore common to break-down a many to many relationship into several one to many relationships. This type of relationship doesn't make it easy to establish true relationship between tables, and when drawn out may even look like tables have no relationship.

Normalisation
This was another topic for this hour, but I have decided to give this its own blog entry as I feel it is an important topic and warrants more space. If nothing else it will give me chance to understand the topic better myself. Sorry you will just have to continue reading my blogs - I will try and get this one out very soon mind!

My Conclusion

Well folks thats the end of the first weeks study! There was a lot of reading involved, some from the course book, of which I have summarised here, the rest of the reading was from the course study guide and the rest and probably the heaviest load was from various articles that were/are posted on the WWW.

(Note the use of WWW and not Internet. Sorry thats just from other courses - people tend to refer to the two as being the same and they are not infact.)

If you have any comments please feel free to post them and I will attempt to answer any questions to the best of my ability.

All the best Jayson

TT380 Databases within Website Design

2005-02-24T20:45:00.000Z

This is the the 4th short course offered by the Open University, that makes up their Certificate in Web Application Development. This particular course concentrates on the use of MySQL and ColdFusion for devlivering dynamic database contents to your web application.

The course uses two course books along with online conferences and weekly study guides. The course books used are:

SAMS Teach Yourself MySQL in 24 Hours
SAMS Teach Yourself ColdFusion Express in 24 Hours

The course requires you to complete and submit 3 computer marked assessment and a final ECA project which normal consist of several parts; a report and some coding; obviously the full requirements for this ECA are NOT ALLOWED to be discussed in detail thus I am unable to comment any further.

For the duration of the course you are given restricted access to an OU server thats hosts an installation of MySQL and ColdFusion. Due to the permission restrictions imposed on this server, I would recommend the installation and configuration of your own local servers. You would require the following:

A web server
ColdFusion Server
MySQL Server
ODBC data sources

For my own installation I decided to opt for the following configuration, namely because of the fact that it would allow me to continue with my development after the course had finished without the need to purchase any applications/software.

A web server - OpenSA (an apache web server).
ColdFusion Server - I Choose to install a newer version from the web due to limited ODBC data source support in ColdFusion Express, which comes on the accompanying CD-ROM. My installation is ColdFusion MX 7 Developer Edition.
MySQL Server - I used the version that was distributed by the OU.

The course is scheduled for 12 weeks and is divided into weekly topics. The current weekly schedule is listed below:

Week 1 - The role of the database in web applications
Week 2 - Scalability, security and connectivity
Week 3 - Introducing database design
Week 4 - Working with a database manager
Week 5 - The coldfusion mark-up language
Week 6 - Server-side scripts and queries - Part I
Week 7 - Server-side scripts and queries - Part II
Week 8 - Advanced queries across multiple tables
Week 9 - Aggregrate functions and transaction proc
Week 10 - Full text searches
Week 11 - ECA Project
Week 12 - ECA Project

As the weeks pass I shall upload relevant notes for each of the topics

All the best Jayson

My OU Courses

2005-02-24T18:14:00.000Z

As this Blog is going to be a haven for comments, thoughts etc for the courses that I have taken with the Open University I thought it appropriate to include a page that summaries what courses I have taken, that are in progress and also those I have planned.

Courses Already Passed

T183 Design and the Web
TT280 Web Basics: Design, Development and Management
TT281 The Client Side of Application Development

Courses Awaiting Results

T187 Vandalism in Cyberspace: Understanding and Combating Malicious Software
TT282 The Server-side of Application Development

Current Courses

M206 Computing: An Object-oriented Approach
TT380 Databases in Website Design

Scheduled Courses

TT381 Open Source Development Tools - Starts May 2005
TT382 Web Server Management, Performance and Tuning - Start Oct 2005

My Blog Site-Map

2005-02-24T17:38:00.000Z

Welcome to my blogger Site-Map - every page that I create will be manually listed in here and hopefully it will be ease the location of the entry you are looking for.

My Open University Courses

TT380 Database within website design

Weekly Summaries

Additional entries relevant to this course

M206 Computing: An Object-Oreinted Approach

Block Summaries

Additional entries relevant to this course

entry 1
entry 2

Personal Information

A Special Mention!
Matial Arts

8th Kyu - Kamishin Ryu
7th Kyu - Kamishin Ryu