TT380 - Week 1 Summary Notes

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