Information Technology resources

Cambridge IGCSE

Microprocessors in control applications

Microprocessors in control applications video transcript

In this presentation we will be looking at how microprocessors are used in control applications.

In control applications the use of sensors and microprocessors, or computers, are used. The terms ‘microprocessor’ and ‘computer’ are interchangeable here, and you can use either when describing a controlled application, but in this video we will use ‘microprocessor’.

The sensors in a controlled application send data to the microprocessor which then compares the incoming data to stored values or data that has been entered earlier.

The use of an ADC, an analogue to digital convertor, may be needed to convert the analogue data sent from the sensor to digital, so that the microprocessor can process the data.

This is because the data from the sensors will often be in an analogue form and the microprocessor only understands digital data.

The microprocessor will check to see whether the incoming data is outside the given parameters that it has stored, and will take any necessary action.

For example, the microprocessor could send a signal to an actuator to perform a function.

An actuator is a device that moves or controls some mechanism. An actuator turns a control signal into a mechanical action such as an electric motor. Actuators may be based on hydraulic, pneumatic, electric, thermal or mechanical means, but are increasingly being driven by software.

The action the microprocessor takes will ultimately affect the next input value it receives. In this way the microprocessor is controlling the application.

There are many examples of the use of microprocessors in control applications, such as automatic washing machines, automatic ovens and cookers, central heating systems, chemical plants and glasshouse environmental control.

In this presentation we are going to concentrate on automatic ovens, central heating systems and chemical process control.

First, let’s consider how microprocessors control the actions and workings of an automatic fan oven.

The oven has temperature sensors, called thermostats, inside the main body of the oven where food is cooked.

Additionally, on the outside of the oven is a panel that has a number of controls on it to set the cooking time, such as when to switch the oven on and off. This panel also enables control of the oven temperature so that the oven can be used with different foods.

So the automatic oven is a controlled application where the microprocessor controls the length of time and temperature at which food is cooked.

To do this the start time and end time, or the actual cooking time for the food are entered on the control panel, along with the required cooking temperature.

The microprocessor will check the set time against the current time and when they are equal, the oven heating elements are switched on. Once the oven starts the cooking process, the microprocessor constantly checks the end time against the current time and when they are equal, the cooking process is stopped and the microprocessor turns the oven off. Note that the end time may be a time entered by the user, or it may be a value calculated by the microprocessor, based on the cooking time entered by the user.

While cooking is underway, a temperature sensor constantly checks the temperature inside the oven and sends the data to the microprocessor via an ADC.

The microprocessor processes this data and checks it against the required temperature pre-set by the user at the beginning of the cooking process.

If the temperature of the oven is greater than or equal to the pre-set value, the required temperature, the microprocessor sends a signal to the actuator to switch off the heating element and fan, so ensuring that the oven temperature stays at the pre-set value.

If the temperature of the oven is not greater than the pre-set value, the microprocessor takes no action. However, if the temperature of the oven is not greater than the pre-set value and the heating element and fan is off, the microprocessor sends a signal to the actuator to turn on the heating element and fan so that the oven can continue to heat up to temperature.

In this way the microprocessor ensures that the temperature in the oven , once heated up, stays at the pre-set value for the duration of the cooking period.

Finally, once the cooking process is finished, the microprocessor sends a signal to a 'buzzer' or 'beeper' to make a sound to alert the user that the cooking cycle is complete.

So that is how cooking time and temperature are controlled in an oven by a microprocessor. Let’s take a look at another example.

In this example, a gas fired central heating boiler is used to heat water which is then pumped through pipes to radiators that warm a building. How do microprocessors control this application?

Well, first a motorised valve on the gas supply to the boiler is controlled by a microprocessor, and the microprocessor opens the valve if the boiler needs to fire up because the heating is turned on or heating levels are increased.

Once the boiler has fired up and begins to heat the water inside it, a water pump is used to pump the hot water around the central heating system whenever the temperature drops below a pre-set value. This is all controlled by the microprocessor.

So let’s look at how this control application works in more detail:

• The required temperature is keyed in or set by the user on a control panel, and this is stored in the microprocessor's memory. This is the pre-set value. • The temperature sensor, generally called a thermostat, constantly sends data readings to the microprocessor. • The sensor data is first sent to an ADC to convert the analogue data to digital data. • The digital data is sent to the microprocessor. • The microprocessor compares the data with the pre-set value. • If the temperature reading is greater than or equal to the pre-set value then no action is taken. The building is at the right temperature and so it does not require more heat from the radiators. • If the temperature reading is less than the pre-set value, because the building has cooled down, then a signal is sent: o to an actuator, via a DAC or Digital to Analogue converter, to open the motorised gas valve to the boiler, which fires up and begins heating the water inside it. o A signal is also sent to an actuator, via a DAC, to turn on the water pump, and warm water begins to flow to the radiators to heat up the rooms in the building • Once the temperature reading reaches the pre-set value, the sensor sends a signal to the microprocessor, via the ADC, to close the gas valve, turn off the boiler and stop the water pump pumping hot water out to the radiators. • The process continues until the central heating is switched off.

So that is how a simple central heating system works. Of course a more complicated system could have multiple temperature sensors and water pumps controlling the temperature in different parts of the building, but even that works under the same principles of control by a microprocessor comparing temperature data to pre-set values.

Okay, let’s look at one last example of a control application.

Certain chemical processes only work if the temperature is above a certain level and the pH , or acidity level is also at a particular point. Examples of such a process could include the production of yoghurt or fruit juices.

To manage these chemical processes effectively, sensors are used as part of the control system which feed data to the microprocessor, and a heater is used to heat a reactor and valves are used to add acid when necessary to maintain the acidity level.

So let’s look in detail at how sensors and microprocessors are used to control this process? For our example, we are going to assume that the microprocessor must keep the temperature in the reactor at 70 degrees centigrade, and the pH at three point five:

• First, temperature and pH sensors read data from the chemical process going on in the reactor. • This data is converted to digital using ADCs, and is then sent to the microprocessor. • The microprocessor compares the incoming data from the sensors with pre-set values stored in memory and if the: o temperature is less than 70 degrees Centigrade, a signal is sent to switch on the heaters in the reactor. o If the temperature is greater than or equal to 70 degrees Centigrade, a signal is sent to switch off the heaters. o If the pH level is greater than 3.5, remember that things become less acid as the pH level rises, then a signal is sent to open a valve and acid is added to the reactor o If the pH level is less than or equal to 3.5, then a signal is sent to close the valve. • Note that the microprocessor signals will be changed into analogue signals using a DAC so that it can control the heaters and valves. • This process of the microprocessor receiving signals from the sensors, and then sending control signals to the heathers and valves continues as long as the system is activated.

So that is how a microprocessor can control the temperature and acidity levels within a chemical reaction.

In this presentation we’ve covered how microprocessors are used to control such things as automatic ovens, central heating systems and chemical processes.

To follow up on this presentation you should now investigate how microprocessors are used to control the environment within a glasshouse and look to structure your answer in the same way as those contained in this presentation.

Good luck.

Data analysis using functions in Excel

Using functions video transcript

As you are probably aware every formula in Excel starts with an = sign. They can be simple formula using mathematical operators such as C2+D2; a complex formula using nested statements or a formula using functions.

A function has a predefined name such as INT or COUNT and they are used to perform a particular calculation. They are operations built into the spreadsheet.

In this presentation we are going to concentrate on the following spreadsheet functions:

INT, ROUND, COUNT, COUNTA, COUNTIF, SUMIF, IF, nested IF, HLOOKUP and VLOOKUP.

We will start with the INT (integer) function. An integer in mathematics is a word used to describe a whole number; that is a number with no decimals or fractions. In Excel the INT function takes the whole number part of a number and ignores all digits after the decimal point.

You can see that the average number of hours worked by these eleven examiners has 9 decimal numbers after the decimal point. If we now enter the formula =INT(B17) into cell C17 the value given is now 7. In other words all the decimal numbers have been ignored. Okay, lets look at the ROUND function:

In this example , if we enter the formula = ROUND(B17,0) into cell D17; the ROUND function will take the numerical content of the cell and round it to zero decimal places., as denoted by the 0 at the end of the formula.

Remember, if the first digit after the decimal point is five or more then it will increase the number before the decimal point by one.

Rounding can be used with any number of decimal places. For example when using rounding for currencies with two decimal places it can help to avoid calculation errors.

Here, we have now used the formula =ROUND(B17,2) in cell D17 to show the average to two decimal places.

If we had wanted to round to more decimal places, we would simply increase the number at the end of the formula, so using three would round to three decimal places, four to four decimal places and so on. Conversely, if we want to round to the nearest ten, we would use minus one and to the nearest 100 we would use minus two and so on. Simply change the number to round the formula how you want it.

Lets look at the COUNT function:

There are a number of COUNT functions in Excel. However we will concentrate on three – the COUNT, COUNTA and COUNTIF functions.

We will start with the COUNT function. This function is used to count the number of numeric values in a list.

This example contains a list of people who are working on Project 123. Under each person there is a number that represents the number of jobs they have yet to complete for the project. We can see that not everyone has jobs to complete.

Using the COUNT function we can design a formula that will be able to tell us how many people still have to finish work on the project.

If we enter the formula =COUNT(A2:A23) in cell A26, it will look at the range of cells A2 to A23 and count only the cells with numbers in them. It will not count any blank spaces or any cells containing text or a combination of text and numbers. Note we have not included cell A1, which contains a title, nor cell A24 which may be used for something else.

The formula gives us the value 9.

The COUNTA function:

The COUNTA function works in a similar way to the COUNT function. However, rather than counting just the number of numeric values, this function counts the number of numeric or text values displayed in the cells, but it will not count any blank cells within the range.

Note, there is no count function within Excel that just counts text values.

So, if we want to calculate the total number of people working on the project we need to use both the COUNTA and COUNT functions.

We have entered the formula =COUNTA(A2:A23)-COUNT(A2:A23) into cell A28.

This will look at the range A2 to A23 and, using the COUNTA function, it will count the number of cells with text or numbers in them, which is a total of twenty. It will then, using the COUNT function, subtract the number of cells with just numbers in them, which is nine cells, to leave the cells with only text in them.

This gives the value eleven; in other words there are eleven people working on project 123. Okay, now lets look at the COUNTIF function:

The COUNTIF function looks within a given range of cells and counts the number of cells in that range that meet a given condition. The condition is placed within the function and can be a number, text, an inequality or a cell reference.

In this example we can see a list of staff who are working on project 153; with their job title and the number of years of work experience.

If we want to find the number of staff who are directors in the list, we can enter the following formula into cell B24; =COUNTIF($B$3:$B$21,”Director”).

This formula will count the number of times ‘Director’ appears in the range B3 to B21 and display the value in cell B24. Note that we have used dollar signs in the range to denote an absolute reference, so that the range is always in the same place if the formula is replicated for any reason, such as counting the number of people in other job roles.

It is even possible to simplify the COUNTIF formula further as both the search range and condition can be substituted for alternatives. For example, if the range B3 to B21 is given a name such as ‘Job’, and the condition is replaced with a cell reference where that condition is correct , in this case A24, then the formula could be instead written as =COUNTIF(Job,A24)

Now, if we amend the formula entered in cell B24 for each of the cells B25 to B28, we will get the number of staff who undertake each of the jobs. Can you think how else the formula could be written? Okay, so if we wanted to count the number of staff with less than five years experience, we can enter the formula =COUNTIF($C$3:$C$21,”<5”) into cell B31.

This will look at the range C3 to C21 and count the number of cells with a number value of less than 5. The speech marks around <5 are needed to tell Excel that it is dealing with another formula, in this case an inequality, rather than searching for the symbols <5.

We get the value 7 displayed in cell B31, the number of people with less than 5 year’s experience.

And finally, to count the number of staff with ten or more years experience, we enter the formula =COUNTIF($C$3:$C$21,”>=10”) into cell B32, to get a value of 5 staff with 10 or more years of experience.

Now let’s take a look at the SUMIF function:

The SUMIF function works in a similar way to the COUNTIF function. It will compare each value in a range of cells and, if the value matches the given condition, it adds the value in another related cell to give a running total.

So to calculate the total years experience of the programmers, we enter the formula =SUMIF($B$3:$B$21,A35,$C$3:$C$21) into cell B35.

The total for cell B35 starts at 0. The formula then looks at the contents of each row in the range B3 to B21 and compares the value in each cell to the contents of cell A35 (which contains the text “Programmer”).

If these two items are identical it adds the value from the same row within the range C3 to C21 to the total. When all the rows have been checked the total is displayed in cell B35.

We get a total of 36.2 years experience for the programmers.

To calculate the total number of years experience for the engineers, we enter the formula =SUMIF($B$3:$B$21,A36,$C$3:$C$21) into cell B36.

We get a total of 26 years for the engineers, and so on.

The IF function:

The IF function is made up of three parts:

• A condition

• What to do if the condition is met and

• What to do if the condition is not met.

Here is an example of an IF function.

The first part of the formula (A1=5) is a condition. In our example it is testing to see if cell A1 contains the number 5.

The second part of the formula (A2*0.5) is telling Excel what to do if the condition is met. In this case, the contents of cell A2 will be multiplied by 0.5.

The third part of the formula (“No discount”) is telling Excel what to do if the condition is not met. In this case, display the text “No discount”. Lets look at an example of an IF function using our spreadsheet for Project 153.

We have added in a column label ‘Category’ in cell D2. We will now enter the formula =IF(C3>=10,”Very experienced”,”Not experienced”)

We have used C3>=10 instead of C3>9 because one member of staff has 0.2 years of experience. As we have parts of a year in the data, there could be a member of staff with 9.5 years of experience, therefore C3>9 would not work for all the data.

We have not used absolute cell referencing as we want to replicate the formula for all members of staff. This means the reference to C3 needs to change as we replicate the formula.

Now we see the formula replicated for all members of staff.

Nested IF function:

A nested function is one which has one function inside another one. They can be used to handle more than two outcomes, which is a restriction with the ‘normal’ IF function. Sometimes nested functions can contain several functions within each other.

To show this we will again use the spreadsheet for Project 153. We want to determine how experienced each member of staff is, so there will be three conditions:

• >=10 years experience should display “Very experienced”

• >=5 years experience should display “Experienced”

• <5 years experience should display “Not experienced”

We MUST work through the conditions in the correct order. In the formula otherwise we will get an incorrect result. For example, using cell C3, we must:

• Tell Excel what to do if C3 is more than or equal to 10 …

• Tell Excel what to do if C3 is more than or equal to 5 … and

• Tell Excel what to do if none of the above are true, if work experience is less than 5 years

We will amend the formula in cell D3 to: =IF(C3>=10,”Very experienced”,IF(C3>=5,”Experienced”,”Not experienced”))

Note that the highlighted second part of the formula, takes the place of the “if the condition is not met” in the original IF formula. You must be very careful to get the brackets correct – each condition has one open and one end bracket.

If we work through the formula we can see why the ordering of each condition is important. The formula first checks whether the value in cell C3 is greater or equal to 10, and if so it displays “Very experienced”.

If it is not true, it will then check whether the value in cell C3 is greater or equal to 5, if it is, it displays the text “Experienced”.

If not, the resulting text is displayed “Not experienced” as there are no more conditions to be met.

This formula can now be replicated for all members of staff.

Ok, lets review using LOOKUP functions:

The term ‘Look up’ means to search or look up from a list. In Excel there are two main LOOKUP functions, these are HLOOKUP and VLOOKUP. We will start by looking at HLOOKUP.

As the name suggests this function performs a horizontal look up of data. This should be used when the values you wish to compare your data with are stored in a single row. The values to be looked up are stored in the rows below these cells.

If we look at this spreadsheet, we have entered a formula into cell C6 which is going to look up and compare the contents of cell B6 with the contents of each of the cells in the top, horizontal row of the range B2 to H3.

When it finds a match it will take the value or label stored in the second row, which is directly under the matched cell.

The ‘2’ at the end of the formula tells Excel to look in the second row of the given range. This gives us the answer “Programmer”. We can now replicate this formula for all members of staff.

The VLOOKUP function:

Again, as the name suggests, the VLOOKUP function performs a vertical look up of data. This should be used when the values that you wish to compare your data with are stored in a single column.

The values to be looked up are stored in the columns to the right of these cells. The look up data can be stored either in the same file or in a different file.

For our example to demonstrate this, we will be using two different spreadsheets.

The first stores the ICT experts details, with their task code and their client organisation, which is currently empty. The second file is the client organisation file which stores the task code and the client organisation’s name.

We will look up in the client organisation file the name of the client organisation for Laila Aboli first and then replicate the formula for each ICT expert. We enter the formula =VLOOKUP(B3,Client.csv!$A$2:$B$8,2,FALSE) into cell C3.

The formula will look up and compare the contents of cell B3 with the contents of each cell in the left, vertical, column of the range A2 to B8 within the Client file.

The number ‘2’ in the formula tells Excel to look in the second column of this range.

The ‘False’ condition in the formula tells Excel to only display the match if it is an exact match. If we had set this condition to ‘True’ it will find the nearest approximate match.

When it finds a match, it will take the value which is to the right of the matched cell.

In our example, it finds 6 as the match and displays the client organisation ‘LGY’ in cell C3. This can be seen in the image on the left.

We can now replicate this formula for all the ICT experts.

So now you have been introduced to these Excel functions, why not design some more spreadsheets in which you can try out your skills. The best advice when preparing for your exams is to practice, practice, practice all of these functions numerous times, and don’t just work through a past paper. Good luck!

Web development layers

Web development layers video transcript

Professionals who work within the web design industry sometimes like to say that front-end website development is like a three-legged stool. This is because the three legs of the stool are the three layers of web development, the content, presentation and behaviour layers.

When it comes to website design, it is important that you are familiar with these three layers so that you can create an effective design. It is also important that you understand what each of the three separate layers contains and why you need to separate them. Ok, first let’s look at the Content Layer, which is also sometimes called the Structure layer:

The content layer of a web page is the foundation, and it consists of the underlying HTML code for that page. By having a solid HTML foundation, this creates a platform upon which a website can be created.

This is what the website visitor will see, read and interact with, and is normally coded in HTML 5.

The HTML structure can contain text, images, multimedia, such as sound and video, and will include any hyperlinks that visitors can use to navigate around the site.

Here are some basic HTML tags, which are the foundation tags for any Content layer in a web page. Okay, so the next layer is the Presentation Layer, which is also sometimes called the Styles layer:

This layer sets out how a structured HTML web page will look to the websites’ visitors.

It is defined by CSS or Cascading Style Sheets. These files contain the style instructions for how the web page should be displayed in a web browser, and how the content will look to visitors.

All visual styles for a website should be in an external stylesheet, and multiple stylesheets can be used if needed. However, every CSS file needs an HTTP request to fetch it, so using multiple style sheets could therefore affect the operation of the site.

Here is a sample of some CSS code. Here you can see that it is a style for a paragraph indicated by the ‘p’, with the text being set to blue and centre aligned. And finally, we have the Behaviour Layer:

This layer makes a website interactive, and it allows the page to respond to any action from a user, or to change based on a set of conditions.

Most webpages contain scripts, and JavaScript is the most commonly used scripting language for the behaviour layer, but CGI (Common Gateway Interface) and PHP (Hypertext Preprocessor) can also be used.

Here is an example of some JavaScript code. This particular script resets a form. So to recap, we have three web development layers:

The Content or Structure layer which contains the HTML.

The Presentation or Styles layer which is the CSS and …

the Behaviour layer which contains scripts, for example JavaScript. So why should you need to separate the layers?

Well, when you create a web page, its structure should be regulated to your HTML, the visual styles to the CSS and its behaviours to scripts.

Some of the benefits of separating layers include Sharing resources. When an external CSS or JavaScript file has been written, any page on the site can use that file. If a file needs to be amended, for whatever reason, then every page on the site that uses that file will benefit from the change. Therefore, there is no need to edit every page on the site individually, which will be a great advantage if the website is large, otherwise updating or making changes could be very time-consuming.

Second, separate layers promote faster downloads: Once a visitor has downloaded the script or stylesheet for the first time, then it is cached by the web browser. Therefore other pages from the website that are requested in the browser load more quickly because they don’t require that the scripts and stylesheets are downloaded again, which will improve the overall page speed and behaviour.

Third, the use of web development layers supports Multi-person teams producing websites: Many websites are designed by more than one person working on it at the same time. Systems can be used that allow files to be checked in and out to make sure that everyone is working with the most up to date version. This would be very difficult to undertake if styles and behaviours were interwoven within the HTML of the structure documents.

Fourth, using layers promotes better Search Engine Optimisation: If a website has the presentation and behaviour layers separated from the content layer, it is very likely to perform much better within search engines. This is because search engines can crawl the content more effectively and understand the page more easily, without being slowed down by additional presentation or behaviour information if it was included within the Content layer.

Fifth, web development layers support Backwards compatibility: If a website is designed with separate development layers it is more likely to be backwards compatible with older browsers and devices. This is because browsers or devices that cannot use certain CSS styles, or perhaps do not have JavaScript enabled can still view the HTML.

And finally, using web development layers supports Accessibility on websites: Screen reader software can process content from the content layer more easily if it is separated from the presentation or behaviours information. Screen reader software does not need to deal with styles as it cannot use them. CSS and script files are more accessible to people and to browsers.

So, make sure that your website project is built using these three separate building blocks, and your creations will be more robust and accessible to all. Good luck!

Cambridge International AS & A Level

Database normalisation

Database normalisation video transcript

Normalisation is an analytical technique used in database design. It aims to create a database design that has two key beneficial characteristics. Firstly, redundant data is minimised, and secondly, the chance of making the data in the database inconsistent is minimised, which are two of the main problems with flat file databases. To normalise a database, you take the flat file version, or single table version of it, and apply three rules, each in turn, that change the database from its un-normalised form to the third normal form. At each stage, after each rule has been applied, you may see that the original database table gets broken down into two or perhaps even more tables, although the records in them will all still be related.

If you approach it in the right way, normalisation is a straightforward, mechanical process. However, if you approach it in the wrong way, you can become very confused! This animation will show you how to use the normalisation process successfully. So, let’s consider a scenario where normalisation would be required.

Here is midtown – it’s a small village which has a community DVD library. Each member of the library has a membership card with their ID number, name, address, phone number and joining date on it.

When they want to take a DVD out of the library, they present their membership card to the librarian, who gets out the member’s record and the following details are written onto it: The ID of the DVD; The name of the DVD; The date it is due back; The certification of the DVD; What the certification means.

Members can borrow up to 3 DVDs can at any one time. A typical member record form looks like this. Each contain a number of different attributes like MemberID, First Name and DVD ID, against which membership details are placed.

You have been asked, by the Head Librarian, to normalise the database to third normal form, or 3NF.

Normalisation is a very mechanical process, as stated earlier, and it follows a number of pre-defined steps. So let’s work through the steps in detail, starting with the setting-up step, STEP 0. Firstly, you need to set up an analysis table, in landscape, with 5 columns like the one shown here. This could be done on paper if you wish.

Put the following 5 headers across the columns, UNF, 1NF, 2NF, 3NF and Name.

UNF stands for ‘Un-normalised form’. This is your database before you have normalised it. You may sometimes see 0NF used, this means the same thing.

Now list all the attributes in the first column under UNF. Referring back to the member record form.

Now you have to identify any ‘repeating groups’ of attributes in the UNF column. Looking back at the Member Record Form, it is clear that you would only have to enter the member’s personal details once, but for all the details of each DVD borrowed, this data would have to be entered every time a member borrowed a DVD.

Groups of attributes like this that have to be entered over and over again are known as a ‘repeating group’. To show this in the analysis table, put brackets around those attributes.

Finally, you have to identify a ‘primary key’ for both the repeating group and for the group of attributes that are only entered once. So what is a primary key? Well, a primary key is an attribute that will uniquely identify each record in a database table. Very often this could be an ID number or reference number.

For the repeating group in this database the primary key would be DVD_ID, as this will be different for every DVD in the library, whereas other attributes may be the same across multiple DVDs. For the group that is only entered once, then MemberID will be the primary key, again because it will be different for every member. To show this in your analysis table, you underline each one.

So now you have completed Step 0 and the analysis table looks like this. It is in Un-normalised form. Now the table is put into first normal form. A table in first normal form contains no ‘repeating groups’.

To do this, copy across the repeating group from the UNF column to the 1NF column . You do not have to keep the brackets, but keep the primary key underlined.

Now, copy across the attribute that acts as the primary key from the non-repeating group in the UNF column and add it to the repeating group in the 1NF column. Keep this attribute underlined as well. The repeating group now has a ‘compound primary key’, a primary key made up of more than one attribute.

Finally, copy across the non-repeating group from UNF, to the 1NF column, into its own group.

Your database is now in first normal form, with two tables, as you have removed all repeating groups from the Un-normalised form. Step 2 normalises the database to second normal form. A database is in second normal form if it is in first normal form and all the non-key attributes depend ENTIRELY upon the primary key. This sounds difficult, but actually it is far easier to do than to describe!

If any table in first normal form has a simple primary key, one with only one attribute, then it is automatically in second normal form and be copied across to the 2NF column.

So the non-repeating group gets copied across to the 2NF column leaving the analysis table looking like this. Now you have to identify those non-key attributes which are related to only PART of the compound key.

In our example, the non-key attribute DVD_title is dependent upon the DVD_ID attribute, which is part of the compound primary key. If you were given a DVD_ID, you should be able to get the DVD title from it. However, if you were given a MemberID, then this would not let you get back to the title of a DVD.

Also, if you were given a DVD_ID, this should allow you to establish what certificate it is and indirectly what that certificate means. As both of these attributes, Cert and Cert_description, have nothing to do with the MemberID, then they can be moved out of that grouping into their own table. This is because those two non-key attributes do not depend upon both parts of the compound key.

What about the non-key attribute ‘Date_due_back’? A DVD is only due back if a member has it out on loan. If you were given a DVD_ID, it might not be due back as no member has it out on loan. Therefore, to be able to specify Date_due_back, you will need both the MemberID and the DVD_ID.

So to summarise, the attributes DVD_title, Cert and Cert_description are all dependent upon the DVD itself, but not dependent at all on the MemberID part of the compound key. While the Date_due_back attribute is dependent upon the MemberID and the DVD_ID, so is related to the whole compound primary key.

So how do you do this? First you copy those non-key attributes that depend only on the DVD_ID attribute from the 1NF column to the 2NF column and put them into their own table.

Now copy that part of the compound key that these non-key attributes depend upon from 1NF to 2NF, and add it to the new table you have just created. Underline this attribute to show it is the primary key for this new table.

Finally, you copy across any attributes left over from the old table from 1NF to 2NF into their own table.

The database is now in second normal form, as it was in first normal form and there are now no tables where the non-key attributes only depend on part of the primary key. Step 3 normalises the database to third normal form. A database is in third normal form if it is in second normal form and each table has no non-key attributes that depend upon other non-key attributes. Again, this sounds difficult, but it is far easier to do than describe!

To start with, any table in second normal form that has zero or only one non-key attribute is automatically in third normal form. They can be moved across straight away. In our example, this applies to the second table of attributes. You do not need to look at any attributes that are part of a key (the underlined attributes). Going from second normal form to third normal form only concerns non-key attributes. In this step you’re looking for any non-key attribute that is directly related and dependent upon another non-key attribute. In our example, the only place where this occurs is in the third table.

The description of any certificate is obviously dependent upon the actual certificate Itself. If you were given a certificate ID then you would be able to get its description. So Cert_description is dependent upon Cert.

Therefore, in our example, you must move Cert_description, but copy across Cert and create a new table in 3NF. Notice the difference between ‘move’ and ‘copy’. Move means that there will be only one occurrence of Cert_description in 3NF, but there will be two occurrences of Cert in 3NF as we have copied it across – the reason for this is that Cert will act as the link between the DVD table and the Certificate table.

You need to identify which attribute will be the primary key in this new table. It will be Cert as Cert_description depends upon Cert. Underline Cert to show that it is the primary key.

You can now move across all the other tables as they have no non-key dependencies.

Finally, give each table a name in the final column.

If you have done everything correctly, then your analysis table will look like this.

Note that Cert in the DVD table has an Asterix next to it, because this is a foreign key in that table, and a primary key in the Certificate table. It will be used to link the DVD table to the Certificate table, as stated earlier. Now that the database has been normalised to third normal form, it has four related tables like this.

The database has been designed so that data redundancy is minimised, and the introduction of data inconsistency has been reduced, because the need to add, delete and amend data more than once has been removed.

Take your time and understand exactly what you have to do at each step – it will be the same for each database design. Good luck!

Data analysis using functions in Excel

Data analysis using functions in Excel video transcript

Lets start by looking at the COUNTIFS function.

If conditional counting is required with two or more conditions, then the COUNTIFS function is the most efficient option to use.

In this example, we have a list of employee's first names, their gender and their age. We are looking for any employee whose name starts with an 'A' and are aged over 25.

We have entered the formula =COUNTIFS(A2:A17,"a*",C2:C17,">25") into cell E4 to find this information. The formula will check the first name in the list to see it starts with an 'A', if it does it will then check their age in column C, if they are aged over 25 it adds one to the count, and so on for each name.

In this example, there is one employee whose name starts with an 'A' and is aged over 25 – Amelia.

Okay, now lets look for the number of female employees who are aged over 25. We have entered the formula =COUNTIFS(B2:B17,"Female",C2:C17,">25") into cell E6 to find this information. This formula will first check in column B whether the employee is male or female. If they are female, it will then check in column C if they are aged over 25. If the check is positive in both instances it will add one to the count.

As you can see, there 6 employees who meet these criteria. Okay, lets now look at the AVERAGEIF function.

The AVERAGEIF function is the most efficient solution to use if a conditional mean, or average, within a single condition is required.

In this example, we have entered the formula =AVERAGEIF(A2:A9,"*S*",B2:B9) into cell D2. This formula looks in column A for the number of fruits that contain the letter 'S' within their name; and then looks in column B for the quantity of each of those fruits that met the first criterion.

In our example there are two fruits that have the letter 'S' in their name, Strawberry and Grapes. The total quantity of strawberries and grapes is 45. Therefore 45 divided by 2 is 22.5 as shown in cell D2.

There is also an AVERAGEIFS function. The AVERAGEIFS function is the most efficient method to use if a conditional mean, or average, is required with two or more conditions.

In this example we have entered the formula =AVERAGEIFS(C2:C9,A2:A9,"Peter",B2:B9,"B") into cell B12. This formula will give us the average quantity of product B that Peter has sold. In our example there are two instances where Peter and product B correspond which result in a total of 313 products sold. Therefore 313 divided by 2 equals 156.5. However, as you cannot sell half a product, this is rounded up to 157, as shown in cell B12. The SUMIFS function

The SUMIFS function is the most efficient method to use if a conditional total is required with two or more conditions. In this example, we have used three different conditions to check; Zone East, Salesman Peter, and Product A, and it has returned the sum of the values which meet those conditions.

The formula entered into cell E12 is =SUMIFS(E2:E9,A2:A9,A12,B2:B9,B12,C2:C9,C12). The only cell to meet all three conditions is E4 and therefore 1239 is displayed.

It is also possible to use wildcard characters to specify the three different conditions.

In this example, the first condition range is A2 to A9 and the condition in cell A12, which will only sum cells that have the letter 'O' in them. The second condition range is B2 to B9 and the condition in cell B12, which will only sum cells that have the letter 'P' in them. It will apply to all the cells which met condition 1.

The third condition range is C2 to C9 and the condition in cell C12, which will sum cells with any type of value. It will apply to all the cells which met conditions 1 and 2. The only cells which meet all three conditions are E2 and E3, which sum to 3543 as shown in cell E13. The Median function.

The Median function is used to find the middle value when all the data items are listed in ascending order.

Here, we have entered the formula =MEDIAN(C3:C29) into cell F24 to find the middle salary for the members of staff in the list. Note that the salaries are in ascending order.

You should also notice that the median salary is not one in the list of salaries. This is because there is an even number of staff members, therefore the median function will add together the two salaries in the middle and divide the total by 2.

In this case, it added together the salaries of Rupinder Singh and Kirsty King, which total 23500, and divided it by 2, which gives 11750.

The Mode function.

The Mode function is used to find the piece of data that occurs the most frequently.

Here we have entered the formula =MODE(C3:C28) into cell F25 to find the salary that occurs the most frequently. It gives 10000 which we can clearly seen in the list is the salary that does appear the most times.

Note that unlike the median function, when finding the mode, the data list does not have to be in any order. The subtotal function can be used in place of a number of other functions.

The first variable passed to this function tells it the type of function to calculate. For example if we enter the formula =SUBTOTAL(1, C3:C12). This will calculate the AVERAGE salary in the range of cells C3 to C12.

If we changed the function to =SUBTOTAL(9,C3:C12) it will now calculate the SUM of salaries in the range of cells C3 to C12.

The Subtotal function can be used in many different ways, by changing the SUBTOTAL code, as can be seen here.

The INDEX function returns values from a given location in a table, where the user specifies the row and column position of the item in the table.

In our example here, we entered the formula =INDEX(B2:B13,6) into cell F1, as we want to find the quantity for the month of June. The formula returns the value 1904 as this is the sixth value in the range B2 to B13.

Now in this example, we entered the formula =INDEX(B2:D13,6,2) into cell F3. This time the formula returns the value 167 as this is the sixth value in column 2 from the range B2 to D13. The MATCH function searches for a specified item in a range of cells and returns the relative position of that item in the range.

In our first example of using the MATCH function, we have entered the formula =MATCH(A1,A3:A8,1) into cell B1.

This formula matches the contents of cell A1, in this case the number 5, with the first '5' it finds within the range A3 to A8. The final '1' instructs Excel to return the largest value which is equal or less than the lookup value. Note that the values must be stored in ascending order.

In our example here, the formula has returned the value '3'. This is because the value '4' is the largest value which is equal to or less than the lookup value of 5. Remember it returns the position of the value in the list. '4' is in position 3 in the list, hence the value '3' is displayed.

In our second example, we have entered the formula =MATCH(D1,D3:D8,0) into cell E1. This time the formula matches the contents of cell D1, the number 2, with the first '2' it finds within the range D3 to D8. The final '0' in the formula instructs Excel to find an exact match.

In the example, there is an exact match in position 3 within the list, so the value '3' is returned.

There is a third alternative for the final match attribute, which is -1. If -1 is used, then Excel is instructed to return the smallest value which is equal or greater than the lookup value. This time the values list must be in descending order.

In this example here, we have entered the formula =MATCH(H1,H3:H8,-1) into cell I1.

The formula has returned the value '2' because the number '5' in the list is the smallest value which is equal or greater than the lookup value, which is 4 in our case, which is in position 2. Okay, lets look at examples of using INDEX and MATCH functions together.

This first example is a basic lookup. We want to find the name of the employee who has the Employee ID EMP-132. We have entered EMP-132 into cell D2 and then entered the formula =INDEX(B2:B11,MATCH(D2,A2:A11,0)) into cell E2. Notice we are using zero as the final match attribute, which means Excel is instructed to find an exact match.

The MATCH part of the formula matches the EMP ID column and returns the cell number for the ID we are looking for. In our case it is in row 6 of the range.

Then, the INDEX part returns the employee's name from the name column, using the same row number. In our case it returns the name 'Richard'.

Using INDEX and MATCH together is a powerful formula and can be used in different ways to 'fill' some gaps in the other lookup functions we use, for example lookup to the left, which cannot be done using VLOOKUP.

The next example demonstrates how looking to the left for a value can be accomplished using INDEX and MATCH.

In our table you can see we have the invoice number column after the amount column. So that if we wanted to lookup the amount on any particular invoice, this could not be achieved using VLOOKUP. However, using INDEX and MATCH it is.

We first enter the invoice number we want to find the amount for, into cell F4. We then enter the formula =INDEX(B2:B13,MATCH(F4,C2:C13,0)) into cell G4.

First, as you can see, we have referred to the amount column in the INDEX part of the formula. This is the column where we need to get the value from. Second, in the MATCH function part we have specified the invoice number, referred to the invoice column and used zero for the exact match. Third the MATCH function returns the cell number of the invoice from the range. Finally, INDEX uses that number to return the amount from the corresponding cell from the amount column. Upper and Lower functions;

The UPPER function returns the contents of a string as upper-case characters. The LOWER function returns the contents of a string as lower-case characters. Both these functions are easy to use.

This example demonstrates the UPPER function by entering the formula =UPPER(B3) into cell D3. This displays BRIAN SARGENT all in capital letters.

To demonstrate the LOWER function, the formula =LOWER(A3) is entered into cell E3; this now displays bsa, which are Brian Sargent's tutor’s initials in lower case. Okay, we will look at how to work with strings and follow this with concatenate strings.

A string is a name for a list of text characters, but it can include numbers. The word concatenate means to join or link together, and there is both a function and an operator in Excel to perform this.

In this example, we have been asked to show the full name of each member of staff in column E. The easiest way to join two strings is to use the & (ampersand) operator. In column E we have used the formula =A3&” “&B3. This formula takes the contents of cell A3, adds a space, then the contents of cell B3, so that it displays Jasmine Patel.

In the next example, we have now been asked to show the staff member's name in column F in the format ‘family name: given name’.

Again the easiest way to do this is to use the formula =A3&": "&B3. This takes the contents of cell A3, adds the text in the speech marks, a colon and a space, then the contents of cell B3, so it displays Patel: Jasmine.

This method could not be used if, in an examination, you were asked to use a function to join strings, as the ampersand operator is not a function. Ok, lets use this same spreadsheet to look at the Concatenate function:

The Concatenate function can take up to 30 strings and join them together and displays the result as text. In column E we need to show the member of staff's full name.

The formula =CONCATENATE(A3," ",B3) is entered into cell E3. This takes the contents of A3, adds a space, then the contents of cell B3. It displays Jasmine Patel.

As before, we have been asked to show the member of staff's full name in the format ‘family name: given name’ in column F. We have entered the formula =CONCATENATE(A3,": ",B3) into cell F3 to do this.

This will take the contents of cell A3, add the text in the speech marks, a colon followed by a space, then the contents of cell B3.

It displays Patel: Jasmine as required. Data can be extracted from strings in different ways.

There are three functions that are used most often for the extract. They extract characters from the left, right or middle of the string. We will start with the LEFT function. This function extracts a number of characters from the left side of a string.

To demonstrate the LEFT function, we will enter =LEFT(A4,6) into cell B4. This will extract the first 6 digits form the code in cell A4. It displays the digits 140118.

The RIGHT function extracts a number of characters from the right side of the string. To demonstrate the RIGHT function we entered =RIGHT(A4,3) into cell C4. This will extract the last 3 digits from the string. It displays the digits 114.

The MID function extracts a number of characters from the middle of a string. To use the MID function you must specify the string to be extracted from, then the starting position within the string, then the number of characters to be extracted. To demonstrate the MID function we have entered =MID(A4,11,1) into cell D4. This extracts from the contents of cell A4, starting at the eleventh character, a single character. It displays the character X. The FIND function;

There are two functions in Excel that allow you to search within a string. Both work in a similar way, the FIND function allows case-sensitive searching but cannot be used with wildcard characters. The SEARCH function is case-insensitive but allows searching with an * or wildcard symbol. In this presentation we will concentrate on the FIND function. The FIND function returns a numeric value that represents the position of a character or sub-string within a string.

In this example we are using the FIND function to find a specific character within the string in cell B3. We entered the formula =FIND(":",B3) into cell D3. The formula will look character by character for the string ":", in our case a single colon. When it finds the first colon in the string in B3, it returns the numeric position of that character, which is 7.

In this next example, we have extended this example to extract the first name from the string in B3. We have entered the formula =LEFT(B3, FIND(":",B3)-1) into cell D3.

This formula will take the left characters from B3. To find the number of characters to be extracted, Excel has located the position of the colon in the string and has extracted all the characters up to that point. The '-1' removes the colon itself from the extract. This returns the string 'Brian'. Okay, let see what we need to do if we want to extract the family name from the string in B3.

The family name is stored after the colon, so we will use the RIGHT function for the extraction. However, this is slightly more complex because we have to work out the length of the overall string and subtract the number of characters up to the colon, as well as the spaces to get the number of characters in the family name. To find the overall length of the string we have used the function LEN.

We entered the formula =RIGHT(B3,(LEN(B3)-FIND(":",B3)-1)) into cell E3.

The formula takes the right characters from B3. To find the number of characters to be extracted, Excel has calculated the length of the string, then subtracted the length of the first name and the '-1' removes space.

It returns the string 'Sargent'. We have now replicated the formulae down for all the other tutors. Excel is able to compare strings in various ways. Most of these methods ignore the case.

For example, here we have entered the formula =IF(B3=D3,"Y","N") into cell E3. This formula compares the contents of cell B3 with the contents of D3. If they are the same it will return "Y" if not it will return "N".

In this example the result is "Y", which tells us that Excel does not compare letter case with an IF function.

The EXACT function compares two strings to check whether they are exactly the same, including letter case.

Here, we have entered the formula =IF(EXACT(B3,D3),"Y","N") into cell E3. This now does return the expected result "N" as the two strings are not identical. The ISERROR function is used to determine if a numeric expression represents an error.

The ISERROR function returns True if the expression argument indicates and error; otherwise it returns False. ISERROR finds all the following errors in Excel - #N/A, #VALUE!, #REF, #DIV/0!, #NUM!, #NAME?, #NULL! If the ISERROR function finds any one of these in the cell then it will return the result True or else False.

In this example, we have a month list in column A, a target list in column B and in column C an achieved list. In column D we have calculated the efficiency level by dividing the achieved number by the target number.

The problem is we get some errors as #DIV/0!

If either the denominator is zero we get the error #DIV/0! when we are dividing numbers. We can find which cells have an error value by using the ISERROR function. We entered the formula =ISERROR(D2) into cell D2 and replicated the formula down the remaining cells in the column.

If there is an error it is displaying TRUE or else FALSE. However, this is not really serving our purpose. We want the data to be shown as zero if there is an error or else do the normal calculation. What we need to do is use the IF function in conjunction with the ISERROR function so that we have a more meaningful outcome. We entered the formula =IF(ISERROR(C2/B2),0,C2/B2) into cell D2.

The IF will check if the calculation (C2/B2) is returning an error. If the calculation is returning an error it will display 0, or else do the calculation as required and show the actual outcome. We then replicated this formula down the remaining cells in the column to display the results. In this presentation we have covered some, but not all, of the extra functions you will need to know for the AS and A Level practical examinations. Remember that you also need to know the functions introduced at IGCSE which are available in another animation.

You will need to practice the use of these so that you become proficient in their use in readiness for the examination.

Do not just work through a few past papers as they will not cover all the functions required, but find examples of using all these functions shown in this animation, and practice the use of them.

Good luck!

Topic outline

The resources listed below are relevant to Cambridge IGCSE and AS&A Level syllabuses. Always refer to your syllabuses for specific requirements.

Microprocessors in control applications

Data analysis using functions in Excel

Web development layers

Database normalisation

Data analysis using functions in Excel