Tutorials

Tutorials, exercises and little snippets for making your computing experience more efficient - arranged in no particular order or subject.

This new feature is expected to take a while to build up, so come back often, or click on the 'Subscribe to posts' link below to stay up-to-date in your favourite RSS reader.

Installing The Latest Stable Version Of Wine On Ubuntu

posted 5 Mar 2014, 05:09 by Alistair Hamilton   [ updated 5 Mar 2014, 05:14 ]

If you want to run software designed for Windows operation systems on your Linux box you'll either need to install them in a virtual machine or try and run them through Wine - the project that aims to provide a Windows compatible API for Linux.

If you are running Ubuntu or one of the many derivative distributions based on it you'll find Wine in the official repositories. However, it's out of date. The version available through your distro is probably sitting at 1.4. However, the current stable release as at the time of writing, is 1.6.2. To install this version, follow the instructions below.

  1. First, make sure you remove any installation of Wine earlier than version 1.6.2. Do this by issing the following command in a terminal window:

    sudo apt-get remove wine
    Enter your sudo password when prompted and press enter.
    Confirm your request to remove wine by pressing enter.

  2. Add the repositories from WineHQ, the official website for the Wine project.

    Select Ubuntu Software Center from the main menu (note, that it may be call Software Center depending on your particular distro).
    Select 'Software Sources' from the 'Edit' menu of the Software Center window.
    Click on the 'Other Software' tab, then click on the 'Add' button to get the following...

    Software Sources


    In the box labeled 'APT Line' type in ppa:ubuntu-wine/ppa and click on the 'Add Source' button
    Enter your sudo password when prompted.

  3. You can now install the latest version of wine from WineHQ by entering the following into a terminal:

    sudo apt-get install wine

    Confirm your password if requested.
That's it. Once the installation process is complete, check that any previously install Windows software is still functional. You may need to reinstall it if it is misbehaving.

Multiple Email Addresses With One Account

posted 15 Oct 2013, 08:45 by Alistair Hamilton   [ updated 2 Jul 2015, 00:59 ]

Do you often sign up for newsletters and forums or register with lots of on-line shops and suppliers?

Conventional wisdom suggests that when you do, you should use an alternative email address for each so that your main email address isn't inundated with junk, offers and other unwanted material. The problem is that, depending on what you signed up for, there may well be information that you need to have access to. As a result, you may find yourself having to log on to many different email accounts - not to mention remembering all those login details.

There is an alternative method that you can use which allows you to use a single email address but keeps things separated and allows you to track which registered service is doing with regard to the emails it sends you.

The following works for both Google Mail accounts as well as those hosted by Microsoft (Outlook.com and Hotmail):

If you have a + character in your email address, the aforementioned services ignore it and everything that follows up to the standard @ symbol. So, if your email address is joebloggs@gmail.com for example, when you sign up to Tesco say, give your email address as joebloggs+tesco@gmail.com and it will still reach you at joebloggs@gmail.com.

Here's the good bit though...

The email you receive will still show it having been sent to joebloggs+tesco@gmail.com

This means that you can set up a filtering rule within your email client to automatically check the addressee and move that email to a folder specifically set up to hold messages from that service, Tesco in this case. This makes it very easy to see how much junk is being sent by these services and very simple to delete it all.

All this without the need to maintain multiple email addresses and accounts.

I've tested this with both gmail.com and outlook.com based email addresses. It may work for other services but I cannot guarantee it as I haven't used them.

Digital Dictation On Linux

posted 2 May 2013, 04:00 by Alistair Hamilton   [ updated 23 Jul 2015, 01:47 ]

I was looking for Linux compatible digital dictation software recently and came up short. There's a host of products available for Windows, but precious little for Linux. NCH, the makers of Express Scribe did have a Linux version for a while but I am reliably informed that it is no longer supported (* see update at the bottom of this article). Their Windows product does run under Wine, but I found it problematic in its use. Specifically, its system wide hot keys wouldn't work.

My efforts in trying to track down suitable, dedicated software proved fruitless though it did get me thinking...

In its basic form, a digital dictation system only needs to replicate the functionality of an old style Dictaphone and tape playback used by countless typists World wide. In other words, all it requires is a mechanism to record the dictation and a method to replay it so that a typist can transcribe it into a document.

These things are readily available. Indeed, you probably have what you need already.

Even the most basic of modern mobile phones have voice recorder functionality. There's your Dictaphone substitute right there, without the need for any additional software. Not only that, most phones will then allow you to email that dictation file to your typist, no matter where she/he is.

The file is nothing more than an audio file, so all your typist needs is a means to play it. You'll be pleased to hear that almost any Linux based audio player will do just that.

Traditionally, typists use a foot pedal to control the playing/pausing/wind/rewind functions of the recording. This is merely a historical relic from the days of analogue Dictaphone and tapes. It was simply the only way to efficiently transcribe the recording and allow the typist to keep their hands on the keyboard. Now, with media keyboards, or the ability to assign hot keys to a keyboard, there's no real reason to use a foot pedal. A proficient typist will soon get used to controlling the audio using the keyboard.

If you want to go to the expense of installing a foot pedal, by all means do so. Search the Internet for Linux compatible models. I do not have access to one so have not tested it as part of this exercise. As you'll gather from the previous paragraph, I do not think it is necessary.

There's a few caveats that you must be aware of:
  1. Most phones record voice as AMR (Adaptive Multi-Rate ACELP Codec) files. This codec must be installed on the typist's Linux box before they can be played directly.
  2. Assuming the codecs are installed, you may find your player doesn't recognise the file name extension and consequently refuses to load it. Clementine is a case in point. However, simply rename the file by changing the AMR extension to MP3 (note this isn't converting the file to MP3) and it loads and plays quite happily.
  3. You may wish to use global hot keys to control the playing of the file, even when the audio software does not have focus. Not all players can do this.
I use Xubuntu, but the following principles should work on other distros:
  1. Using Menu -> System -> Synaptic Package Manager check that the following AMR packages are installed...
    libvo-amrwbenc0, libopencore-amrnb0 and libopencore-amrwb0
  2. Install one of the media player listed below if not already installed. These have been selected primarily due to their global hot key support and of course, their ability to play AMR files.

Suggested Linux media players:
  1. Clementine
    • Can play AMR files but does not load them without first renaming the file extension to MP3.
    • Supports Global Keys allowing control of audio playback even though Clementine does not have focus.
    • Automated playlist importation allowing newly received recording to be listed without user intervention.
    • Integrates with the volume control in the system tray.
    • Not installed in Xubuntu by default.
  2. Audacious
    • Plays AMR files natively when added to play list though importing a directory seems to ignore AMR files unless renamed with a MP3 extension.
    • Supports Global Keys allowing control of audio playback even though Audacious does not have focus.
    • No automated playlist importation.
    • Integrates with the volume control in the system tray.
    • Not installed in Xubuntu by default.
  3. VLC
    • Plays AMR files natively.
    • Supports Global Keys allowing control of audio playback even though VLC does not have focus.
    • The only one of the three, that I can see, that allows the user to change the speed of the playback which may be an issue for some users.
    • No automated playlist importation.
    • Installed in Xubuntu by default.
Of the three listed, I'd prefer Audacious if it had an automated importation facility where one could point it at a folder and any new recording would be automatically added to the play list. It has a simple, clear interface which would be ideal but for that one limitation.

As a result, my vote goes for Clementine. Its automated playlist updating, not to mention the ability for the user to delete the recording from disk directly from the application makes this a winner. It is a pity that AMR files must be renamed with an MP3 connection before they get recognised in the playlist. However, that minor issue can easily be overcome by having a cron job set up to automatically rename such files on a regular basis thus eliminating the need for any user intervention.

One final piece of configuration that you may want to try is a rule within the typists email program to have voice recorder attachments automatically saved to the folder pointed to by your media player, but I'll leave that for you to figure out.


* Update 13 June 2013 - Although NCH claim they no longer support their Linux version of Express Scribe, the software is still available for download from their website. I had previously dismissed this as it always threw an error up on start up. However, I've since revisited it and the error is due to the installation process creating a hidden folder within the home directory for root, rather than the user. If you create a folder "/.nch/scribe" for each user and set that folder as the data folder within the Express Scribe options (Disk Usage tab) you'll be good to go.

How To Avoid Computer Malware

posted 12 Mar 2013, 05:14 by Alistair Hamilton   [ updated 18 Mar 2013, 06:53 ]

Even if you have never had the misfortune of seeing a computer infected by malware, you need to read this.

Malware: the generic term used to describe computer viruses, adware, spyware, scareware, trojans, hijackers, keyboard loggers, worms, root-kits, rogue security software and dialers. In essence, any application installed on your computer that does usually non-beneficial things without your knowledge.

I won't go into the definition of each and every one of those pieces of software. Suffice to say, you do not want them on your computer.

There's only one way to guarantee that your computer never picks up an undesirable application.
NEVER SWITCH IT ON!

That's clearly impractical, so the next best thing is to use an operating system that is not as susceptible as Microsoft Windows to such attacks. I'd recommend the use of Linux.

Assuming those last two suggestions are not desirable and you insist on using your Windows based computer, what do you do to minimise the risk of getting such infections, keeping in mind that it is impossible to guarantee you will stop everything?

Such infections are generally caught by one of two ways:

  • Email - Specifically opening a compromised email attachment or clicking on an internet link contained within the email.
  • Compromised website - visiting a, how shall I put it, 'questionable' website and you may find yourself inadvertently and unknowingly downloading and installing all sorts of nasties.

So, how does one avoid these?

Well, the first line of defence is not your computer, but you, the user. Always exercise caution and due diligence. It only takes a single click, a momentary lapse in concentration, to initiate a download/installation of something that may end up costing you money. Try and follow these tips at all times.

  • In everyday computer use, try and avoid logging on as a user with administrative privileges. Use the administrative account for doing just that - administering your computer.
  • Never click on a link contained within an email, especially if it is from someone you do not recognise. Even then, exercise caution as email addresses can be readily forged. Please note, NO LEGITIMATE FINANCIAL ORGANISATION will send you an email asking you to log on to your account via a link in an email. Always go directly to the site by typing the address in your web browser.
  • Make sure your email program does not automatically display images. Whilst disabling images makes your emails look unattractive, it stops the sender logging your email address as valid - a common technique used by spammers.
  • Stick to well known and recognised brands when shopping on line. If in any doubt, do not buy.
  • If you must visit 'questionable' sites do so by booting your computer into Linux running from a USB or CD. If you do get compromised, your Windows installation on your hard disk will remain untouched.
  • Make sure your Windows installation is kept update by making sure Automatic Updates is turned on.
  • Install suitable anti-malware software, but only from a reputable supplier. There are many fake anti-malware tools out there so stick to products from companies such as Avast, AVG, McAfee, Norton etc. I recommend Microsoft Security Essentials as it is a simple, straight forward application and does just as well as the free versions from any other supplier - indeed, it is recommended by Which? Magazine. (Note, Windows 8 comes with Windows Defender which does everything MSE does).

The criminals that create these pieces of software are sophisticated and they use equally sophisticated techniques to compromise your computer.

By far and away the most successful mechanism they use is social engineering. They employ numerous social tricks to fool you, the user, into giving them access to your computer either directly or by installing a piece of software. Frequently, you will not even be aware that their software has been installed on your machine.

Remember, keep your machine up-to-date with security patches; run your anti-malware software regularly; above all, be vigilant. You are the first defence against such software. Don't make it easy for malware to infiltrate your machine.

A malware infestation can manifest itself in a variety of ways. Some of the tell tale symptoms are:

  • Your computer running very slowly.
  • Your computer seems to take an age to start up.
  • Seemingly random windows, usually containing some warning message about viruses, repeatedly pop up and you find it difficult to get rid of them.

If you think you have an infected computer, seek professional help as soon as possible.

Basics Of Database Table Design - Pt7

posted 19 Nov 2012, 05:35 by Alistair Hamilton   [ updated 15 Jun 2015, 07:59 ]

Basics of Database Table Design
By now, you should have a reasonable understanding of the process of table normalisation and why it is important for the integrity of your database and the data contained therein. 

Following the process described in earlier sections of this series will ensure that your database doesn't need to be redesigned whenever you need a new field added to a table, or a completely new table bolted on to your design. It will make querying easier and accurate, removing any possible ambiguities within the results. Duplicate data entries will be eliminated by storing the data in one place and have it related to other pieces of data held in other tables.

Casting our minds back to part one of the series, in order to discuss table design, we jumped ahead of the overall database design process by omitting the specification phase. This is perhaps the most important phase of the process.

For the final part of this series, we are going to look at a technique that sits between the specification and the implementation phases.

Entity Relationship Diagrams

An Entity Relationship Diagram (ERD) is a graphical representation of the design as laid out in the specification. It provides a quick method of laying out the table design before going anywhere near your database design software. In addition, it can often flag up issues with the specification at an early stage that may make it necessary to to revisit the design requirements.

As the name suggests, it allows the relationship between tables to be defined and, in my view, helps with the normalisation process as it is easy to see where tables should be separated.

It should be noted that desktop database software such as MS Access and LibreOffice, not to mention some server based database software, have inbuilt functionality that allows the user to create relationships between tables in a graphical manner in a similar vein to ERDs. However, these are tools for establishing the relationship within the database and is not part of the design process. Indeed, in order for these relationships to be established the corresponding tables must be created beforehand.

In other words, from a design perspective, using these tools is like putting the cart before the horse. An ERD should be created before you go anywhere near creating your tables for real.

You can use numerous packages to create your diagram. Most organisational diagram software could be used. Very large database designs, particularly where numerous designers are involved in the project, will use dedicated ERD software but for the most part, desktop database designers do not need those applications. ERDs for many such databases could be drawn by hand.

Here, I'll be using the Draw program contained within LibreOffice.

So, on to the first example. Here is the ERD representation of the work we did earlier in the series.


Entity Relationship Diagram


A straightforward looking diagram that still needs a little explanation...

The arrows represent the relationship between the tables and this diagram shows two different types corresponding to the type of relationship that can exist between tables.

  1. The relationship between the Department and Employee tables is a one-to-many relationship from Department to Employee.
  2. The relationship between the Employee and Project tables is a many-to-many relationship.

How do we go about ascertaining what type of relationship exists between tables? To do this, one must consider the relationship viewed from one record on each side of the relationship.

For example, looking at the Employee/Department relationship, consider one employee. Any one employee works in one department – a one-to-one relationship.

Now, look from the other side. Any one department will have a number of employees working in it – a one-to-many relationship.

Combining the two results in a one-to-many relationship 'flowing' from Department to Employee.

Please note at this stage that it is quite possible to end up with a one-to-one relationship going both ways., i.e. a net one-to-one relationship between tables. A database designer could then argue that the tables could be merged. However, keep in mind what we have learned about database normalisation and always work to the smallest configuration that makes sense.

Although not part of this series, when creating relationships within MS Access et al, the direction of the arrow defines how the relationship is established which has an effect on how a one-to-one relationship behaves in terms of record updates and deletions. Again, the specification should make that clear.

For the reasons outlined above, it is quite common for a database designer to label each end of the arrow with a 1 and an 'M' (or a specific number) to make it clear exactly what the relationship is.


Now, repeat the process for the Employee and Project tables...

Any one employee could work on a number of projects (one-to-many) and any one project could have a number employees working on it (one-to-many). The net result of this is a many-to-many relationship.

Here we have a problem. Relational database applications aren't great at handling many-to-many relationships. To get around this, we must use another technique to represent the many-to-many relationship with two one-to-many relationships. This can be done by introducing an intermediary table as shown in the updated ERD below.

Entity Relationship Diagram


The name of the intermediary table can be anything you like, though combining the table names makes it clear what it is.

Now that the relationships between table are established, how are they linked? Quite simply, fields containing the same data are used to define how the tables are linked through the relationship. They provide the 'anchor points' of the relationship if you like.

Before we look at that, it's time to introduce the next part of why an ERD is useful.

Field Definition and Types

When designing a database table, we are specifying the data that that table will hold. We do so by defining fields for each record that is pertinent to each record. Part of that process is to define the size of the data for each field.

For example, if we have a surname field, that field will contain text and not numbers. In addition, specifying such a field as a text field will, in most database design applications give a maximum number of characters of 254.

Now, clearly, in the case of a field designed to hold employees' surname, specifying 254 characters per record is going to result in a lot of wasted storage space within the database. Do the same for every other field in each table and it is easy to see how space inefficient it would be.

In addition, defining the correct type helps to minimise data input errors. For example, if a field is configured to hold an integer, then entering anything other then numerical digits would not be allowed.

Therefore correct definition of the field type and size reduces storage requirements and helps to maintain the integrity of the data contained within the database. The importance of this should not be underestimated.


The ERD is an ideal place to put this information – field names, data type and size – as shown in the revised ERD below. Note that those fields in bold and underlined represent the primary key field of the table.

Entity Relationship Diagram


With the field definitions incorporated into the ERD, we can establish how the relationships are configured between tables. We already know what the table relationships are. All that needs to be identified now are the fields that define where the tables are joined through the relationship.

In general terms, relationships are established on corresponding fields in each table that hold the same data (which needs to be of the same data type). More specifically, the one side of the relationship should be 'anchored' to a key field.

Looking at the Department to Employee relationship we see that the 'one' side of the relationship should be 'anchored' to the key field for that table, i.e. DeptID. However, there is no corresponding field in the Employee table.

In order to correct this, we need to introduce an additional field in the Employee table that will hold the DeptID data. This might seem contrary to the normalisation process described in earlier instalments but it is a necessary step in order to establish the relationship within the database.

In other words, the DeptID primary key field from the Department table is introduced to the Employee table. Please note that the new DeptID field in the Employee table is NOT a key field in that table even though it is referred to as a 'foreign key'.

Compare this to the imported fields in the intermediary table, EmployeeProject. These foreign keys, whilst not key fields in themselves within the EmployeeProject table, combine to make a joint key field of the EmployeeProject table.

Note that most intermediary tables contain just the foreign keys from the tables involved in the relationship. That is all that is needed to simulate the many-to-many relationship involved. However, there is nothing to stop such a table holding additional information.

For example, the EmployeeProject table in our small database may be the place to record the total number of hours spent by each employee on each project.

The final ERD is shown below.

Entity Relationship Diagram


That diagram draws the series to an end. I'd encourage you to practise the techniques discussed throughout this series. Hopefully you will feel better equipped to design your own databases in MS Access et al.

If you have any questions or would like further assistance with your database design, then please call Alistair on 0131 3333972 or 07790 860067 to discuss your requirements.

Thanks for your time..


Basics Of Database Table Design - Pt6

posted 9 Nov 2012, 04:16 by Alistair Hamilton   [ updated 19 Nov 2012, 06:36 ]

Basics of Database Table Design
Now that you are familiar with the first two normal forms of database table design, as well as the the different types of key or primary field, we move on to the last of the normalisation levels that will be discussed in this series.

If you recall from part 2 of the series, the Third Normal Form is defined as "eliminates functional dependencies on non-key fields by putting them into a separate table. At this stage, all non-key fields are dependent on the key, the whole key and nothing but the key".

In essence, each record in a table should have fields that describe attributes of that particular record or entity. For example, we may have an employee table which has fields that indicate which department the employee works in and where that department is located. We've already seen that we should remove the department field and stick it in its own table. However, to illustrate the point, I've modified the Employee as shown below:

EmployeeID

Employee

Department

Department Location

EMP001

John Smith

Software Engineering

Building 1

EMP002

Jill Jones

Software Engineering

Building 1

EMP003

Thomas Harding

Software Engineering

Building 1

EMP004

Lee Walsh

Human Resources

Building 2

EMP005

Joe Jordan

IT

Building 1

EMP006

Neil Watson

Web Design

Building 1

EMP007

John Smith

Human Resources

Building 2



The Third Normal Form specifies that each non-key field should describe the entity (or contribute to its description) identified by the key field. In the case of the Employee table above, while we may have a need to find out which building Lee Walsh works in, that building identity has nothing to do with the employee himself. The same argument goes for the department.

The Department Location is a functionally dependent on the Department field which of course is a non-key field and therefore needs to be removed to its own table. The field make no contribution to the data held that specifically defines or describes an employee.

All the time, one must think of each table in terms of just that table and the data that the table is designed to hold.

Summary

The main thrust of this series has been to give non-technical users of desktop database applications such as MS Access and LibreOffice the basic tools for designing their database tables in order to ensure the integrity of the data being stored.

The process of normalisation is fundamental to that and, although only the first three of five normalisation levels have been discussed, getting your database tables into the Third Normal Form will ensure that your database design will be able to handle changes and additions while giving you accurate query results when asked.

While the normalisation process described in this series may seem complicated, it is essential for the integrity of the databases you are designing. It is worth the effort in getting to grips with the techniques involved.

If I was to summarise the techniques described and give a general rule of thumb that will cover most situations it is this:

Break all your tables down into their smallest component parts that make sense and create separate tables for each of them.

The series is almost finished now. There's one thing to cover before the end. Something that will allow you to design your database tables quickly and which normalises the tables almost automatically as you do so. Look out for the last part when I'll introduce Entity Relationship Diagrams and explain why they are one of the first tools you should use even before you go anywhere near your database application software.


Basics Of Database Table Design - Pt5

posted 21 Oct 2012, 04:54 by Alistair Hamilton   [ updated 19 Nov 2012, 06:35 ]

Basics of database table design
Before continuing the discussion on optimising database tables through normalisation, I'm going to take a slight detour in this episode.

I mentioned in earlier discussions the term 'key field'. Indeed, in the definition of the various normalised forms introduced in part two of the series, the idea of a key field is specifically referenced in those definitions.

So what exactly is a key field?

Put simply, the key field is the field within a database record that makes that record unique within the database table in which it is held. It can be made up of a single field or a number of fields that , when considered together are unique (though not necessarily unique on their own).

As ever, things are best explained with an example so let's consider the Employee table we came across in previous episodes.

Employees

Home Address

John Smith

5 Main Street, Sometown

Jill Jones

22 The Glebe, Sometown

Thomas Harding

1A High Street, Elsewhere

Lee Walsh

10 Station Road, Mytown

Joe Jordan

19 Bank Drive, Mytown

Neil Watson

15 Highgrove, Thistown



The key field is used to identify each record within a table. By definition, the contents of the key field cannot have duplicate entries. They must be unique.

In the employees table as it currently stands, the obvious field to use as the key field would be the 'Employees' field. At the moment, it contains unique entries. No employee name is repeated. Therein lies the problem with using this field as the key.

What happens if the company employs a new member of staff with the name John Smith? With the 'Employees' field set as the key field the details for the new member of staff could not be entered into the table without violating the uniqueness of the key field. Indeed, most database applications wouldn't allow the creation of a new record under such circumstances.

One of the solutions to this would be to use BOTH the 'Employees' and 'Home Address' fields and create what is known as a JOINT KEY.

In this case, each field can have repeating content, but when the two fields are considered as a pair, no pair has the same joint content as you can see in the updated table below where the new employee has been added at the bottom:

Employees

Home Address

John Smith

5 Main Street, Sometown

Jill Jones

22 The Glebe, Sometown

Thomas Harding

1A High Street, Elsewhere

Lee Walsh

10 Station Road, Mytown

Joe Jordan

19 Bank Drive, Mytown

Neil Watson

15 Highgrove, Thistown

John Smith

73 Alderbank, Elsewhere



However, this solution is not without potential problems.

Although our two employees named 'John Smith' are adequately catered for using a joint key made up of the 'Employees' and 'Home Address' fields, the table still has a problem if, in the perhaps unlikely event, that two people, with the same name living at the same address, are employed by the firm. Sounds unlikely perhaps, but the possibility exists for a father and son for example to be hired by the firm.

Although such a scenario may be unusual, it is better to design the table to take this possibility into account rather than wait for the issue to cause a problem in the future. In the case of our employees table, the obvious thing to do would be to insert a new field that would hold each employee's ID number and use that as the key field.

Note that many database applications have a mechanism where an auto-incrementing field can be used as a key field. Personally, I do not like using them as it makes it very easy for, what is effectively, duplicate records to be inserted into the table. The database software would treat them as unique records because of the auto-incremented field.

There is another term that you may come across when designing databases. A FOREIGN KEY is a field in a table that contains the contents from the key field of a related table. It is NOT a key field in and of itself but is used to set up relationships between tables. This will be the topic of conversation in a later episode.

From this you can hopefully see why defining the key fields within your tables is an important step in the design process. It has a direct influence on the normalisation process. If you recall from part 2 of the series, The definition of the 3rd Normal Form is :
  • Third Normal Form – eliminates functional dependencies on non-key fields by putting them into a separate table. At this stage, all non-key fields are dependent on the key, the whole key and nothing but the key.

This will be discussed in the next part of the series.


Until next time...


<< Basics of Database Table Design - Pt4

Basics Of Database Table Design - Pt4

posted 9 Oct 2012, 05:45 by Alistair Hamilton   [ updated 21 Oct 2012, 04:57 ]

Basic Database Table Design - Pt4
In the last episode, Basics Of Database Table Design – Pt3, we looked at the first of the normal forms. This eliminated repeating columns of information within the database and subsequently removed a number of issues that limited the table design.

Now we move on the the second of or normalisation procedures where we eliminate fields from each table that have nothing to do (or are only loosely linked) with the objects or records that the table is holding. Before we do that however, we need to expand the somewhat simplistic tables that have been used so far.

Let us consider an expanded Employees table used earlier:

Employees

Home Address

Job Function

Department

John Smith

5 Main Street, Sometown

Developer

Software Eng

Jill Jones

22 The Glebe, Sometown

Developer

Software Eng

Thomas Harding

1A High Street, Elsewhere

Analyst

Software Eng

Lee Walsh

10 Station Road, Mytown

HR Manager

Human Resources

Joe Jordan

19 Bank Drive, Mytown

System Admin

IT

Neil Watson

15 Highgrove, Thistown

Developer

Web Design


Second Normal Form

If you recall from part 2, getting our tables into second normal form involves the removal of fields that are not related to, or are only partially related to, the information that the table contains.

In the example table above, the information being held is supposed to be about the employees. Each field in the table should be directly related to each corresponding record.

Clearly, the Home Address field is related to the Employees field, but what about the Job Function and the Department fields? What if we included another field in the table that holds the Employees' salary?

This is where you may start to find some ambiguity in the interpretation of the data being held within the table. You may find it easier to look at it from the point of view of the field concerned. For example, consider the Department field. 

While it is obvious that an employee will work in a particular department, each department itself does not have anything to do with the definition of what an employee is within the database. Its inclusion within the table above brings nothing extra to the information stored about each employee.

In addition, because each department may have numerous employees working within it, listing each department in the table above results in that department name being listed in numerous locations/records. This is always a recipe for user input errors and so on. If a department name changes for example, it would have to be changed in any number of records within this table.

In this case, it is better to remove the Department field and stick it in its own table where other information can be held that specifically defines each department.

A similar argument can be put forward for the Job Function field.

As mentioned earlier, The interpretation of this normalisation rule can be somewhat open when considering whether a field should be removed to its own table. This is where the specification process comes in. Reference to the design objectives will help you out here.

For example, the idea of including an employee's salary in the table above may seem quite reasonable. However, what happens if you also want to include pay grades and so on? At that point it must be argued that such remuneration information should be removed.

After removing these fields, the expanded Employees table is now in Second Normal Form.

Employees

Home Address

John Smith

5 Main Street, Sometown

Jill Jones

22 The Glebe, Sometown

Thomas Harding

1A High Street, Elsewhere

Lee Walsh

10 Station Road, Mytown

Joe Jordan

19 Bank Drive, Mytown

Neil Watson

15 Highgrove, Thistown



In summary, the Second Normal Form involves the removal of fields that do not had any direct relationship with the information being held by a table. Each field within a table must have something to do with the other fields of that corresponding record within the table. More specifically, it should contain information that is pertinent to the Key Field – the record's unique identifier.

Although the Third Normal Form is the next step in this normalisation process, we'll be taking a slight detour in the next instalment to explain what we mean by Key Field and the issues that arise from it.

Block Parasites, Adverts And Questionable Websites

posted 30 Sep 2012, 13:03 by Alistair Hamilton   [ updated 10 Sep 2015, 02:14 ]

Block unwanted content
There's a free, simple technique that can aid in the fight against malware infecting your machine. By changing the contents of your 'Hosts' file, your browser (or any other application on your computer) can be prevented from reaching certain websites. If you know that a website is suspect, then make the corresponding entry in your hosts file and you'll get an error message displayed instead.

The technique can be used to block sites your children visit; block 3rd party cookies; unwanted banners; page counters; or stop intrusive adverts from being displayed. It stops malware 'calling home' and has the additional benefit of reducing your broadband usage because the request to download or retrieve content is not sent over the Internet.

The hosts file on you computer is used to list domain names to IP address mappings and is used as part of the process that your browser uses when communicating on the Interent. By default, a hosts file will typically contain entries such as those below:

127.0.0.1 localhost
127.0.1.1 computername

# The following lines are desirable for IPv6 capable hosts
::1     ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

Aside from these, you can add anything else you like. Indeed, some malware browser hijackers are known to insert their own entries, substituting common websites such as google.com with a mapping to a website of their choosing (usually one that installs other malware on your computer). For this reason, you should always make sure that this file is protected and can only be changed by a user with the necessary permissions.

Before we demonstrate its use, make a backup copy of your hosts file so that you can get back to where you were in case things get messed up. The hosts file on Windows machines is located in the folder C:\Windows\System32\drivers\etc. On Linux based machines you'll find in /etc.

Remember, you need administrative privileges to edit this file. Open your hosts file in your favourite text editor and append at the bottom the following:

82.103.136.226    www.microsoft.com

Once you save the file, try going to www.microsoft.com. What should happen is, rather than going to the Microsoft site, you'll end up being redirected by your hosts file to distrowatch.com (assuming distrowatch.com hasn't changed its IP address since this article was written) from where you'll be able to download and test Linux - you know you want to.

Joking aside, this is exactly the technique that some browser hijackers use to redirect you to much more dangerous content.

Having completed the test, edit your entry so that it reads:

127.0.0.1    www.microsoft.com

This IP address is known as your network card's loopback address. In essence, any calls to this address do not go anywhere. Once you've saved the file, try going to www.microsoft.com again. Your browser should inform you that it cannot find the file or couldn't connect to the server.

This is the technique you are going to use to block access to websites. Put each entry on a separate line and simply assign each website to the loopback address of 127.0.0.1, then save the file.

UPDATE: It is recommended that you use 0.0.0.0 instead of 127.0.0.1. This resolves a slowdown issue that occurs due to a change made by Microsoft to the "TCP loopback interface" in Windows 8.1. Earlier versions of Windows is not affected nor is Linux systems though using 0.0.0.0 on those operating systems doesn't do any harm.

Clearly, continually editing this file when you come across a website you wish to block is very inefficient. If you consider the amount of questionable websites out there, not to mention all those advert serving sites that most companies seem to use these days, it will take you a long time indeed to create the corresponding entries in your hosts file.

Fortunately, there's a quicker way as someone has already done it for you. Head over to http://winhelp2002.mvps.org/hosts.htm from where you'll be able to download a very comprehensive hosts file for free. You'll also find further advice on using your hosts file. The file is updated regularly but you are not stopped from adding your own entries as well.

Remember, this is NOT a substitute for proper security measures. You should still be using anti-malware and anti-virus software. You still need to keep your system up-to-date and excercise due diligence. This is simply another weapon in the armoury to help keep your computing life free of undesirables.

Basics Of Database Table Design - Pt3

posted 26 Sep 2012, 02:02 by Alistair Hamilton   [ updated 21 Oct 2012, 04:48 ]

Basics of database table design
Last time, we started looking at some example data with a view to explaining the issues that frequently arise when database table design is not considered fully. We Introduced the concept of data 'normalisation' as a mechanism to maintain data intergrity, reduce errors, minimise data storage requirements and provide an element of 'future proofing' should the database design need to expand at a later date.

An example table was introduced (reproduced below for convenience) which demonstrated a number of issues that would cause problems. What we are going to do now, is discuss how these problems are resolved by making sure our tables adhere to the 'normalisation' rules introduced in Basics of Database Table Design - Pt2.

First up is the First Normal Form but before that, please refresh your memory of our example data in the table below.


Employees

Project 1

Project 2

Project 3

John Smith

CD Records Database

Internet Plug-ins


Jill Jones

Traffic Control Software

CD Records Database

Case Manangement

Thomas Harding

Traffic Control Software



Lee Walsh

Internet Plug-ins

Case Management

Satellite Navigation

Joe Jordan

Satellite Navigation

Traffic Control Software


Neil Watson

MP3 Development

Multi-media Training


Louise Harrower

Spreadsheet Utilities

Office Integration




To recap, the table above suffers from the following problems:
  1. Querying this table will be difficult. For example, how do we go about calculating the number of people working on a particular project?
  2. In the various 'Project' columns, the title of various projects are being repeated. This can lead to data input errors through misspelling and so on.
  3. Jill Jones and Lee Walsh are currently involved in three projects. What happens if they are assigned a forth? How is that information recorded? The only way would be to add an additional field to the table.
  4. As some people do not work on all current projects, the corresponding 'cell' will be empty. However, in database table design, this lack of information still takes up storage space. The bigger the table, the more storage space is wasted.
The solution to this is to ensure that the table is in First Normal Form by eliminating repeating groups and putting each into a separate table and connecting them with a one-to-many relationship.

In our example, the project information is in repeating groups, i.e. the Project 1, Project 2 and Project 3 columns. We need to pull that information out so that we end up with two tables: one holding the Employees data and the other, listing all the projects. Like so....

Employees


Project

John Smith


Internet Plug-ins

Jill Jones


CD Records Database

Thomas Harding


Case Management

Lee Walsh


Traffic Control Software

Joe Jordan


Multi-media Training

Neil Watson


Satellite Navigation



MP3 Development



Office Integration



Spreadsheet Utilities



We now have two tables that are related. (As a side note: the contents of the tables solely define what the table holds which implies that these simple example tables are in 2nd Normal Form – that's the topic for Pt 4).

The important point to note is that when Jill Jones is assigned another project all that needs to happen is a new record is placed in the Projects table.  There is NO NEED to redesign the Project table each time – simply add another record instead.

The relationship between the Employees and the Projects tables handles the link between them. In essence, for each employee in the example table, there may or may not be any number of corresponding projects in the Project table. In other words, John Smith may work on three projects, yet Lee Walsh may not be assigned any. Irrespective of the combination, it is handled by the table design and the relationship between the tables concerned. I’ll be discussing the relationship between tables in more detail later in the series.

Similarly, if a new employee joins the firm, their details are simply added to the Employee table. Their inclusion doesn't affect the Project table which will not have any 'empty storage'.

Hopefully, you'll see from this simple example, that just getting your tables into First Normal Form, provides a major improvement, automatically eliminating numerous potential issues with your database design.

Just to recap:

First Normal Form involves eliminating repeating groups (columns that hold the same or similar type of data) of information and placing all that data into its own table. This eliminates repeating data; removes empty cells thereby reducing data storage requirements; reduces data input errors by having specific data stored in one place and one place only; and, perhaps equally important, makes it much easier to incorporate new data without having to redesign the table each time.

In the next installment, I'll be moving on to the Second Normal Form where the fields within each record of a table must uniquely define the record.

1-10 of 12