MTN South Africa, through MTN SA Foundation, is a proud supporter of the CAT and IT digital books.

As an organisation rooted in technology, we believe in providing a new bold digital world to communities we operate in. This unique digital book provides the fundamental knowledge necessary for a sound grounding from which to make practical use of the complete and indispensable application-oriented information regarding Computer Applications Technology (CAT) and Information Technology (IT). It is a foundational reference for today’s secondary school learners and teachers alike - as well as for the next generation of CAT and IT students.

Information Technology Practical Book Grade 12

ISBN 978-1-928388-56-2

First published in 2019 © 2019. Copyright in the text remains with the contributors.

Quality Assurance team for Information Technology

Allison Philander, Carina Labuscagne, David Peens, Denise van Wyk, Edward Gentle, Jugdeshchand Sewnanen, Julian Carstens, Magdalena Brits, Shamiel Dramat, Shani Nunkumar and Zainab Karriem

Restrictions

You may not make copies of this book in part or in full – in printed or electronic or audio or video form – for a profit seeking purpose.

Rights of other copyright holders

All reasonable efforts have been made to ensure that materials included are not already copyrighted to other entities, or in a small number of cases, to seek permission from and acknowledge copyright holders. In some cases, this may not have been possible. The publishers welcome the opportunity to redress this with any unacknowledged copyright holders.

Dear Learner

Welcome to the IT Practical Grade 12 textbook, and welcome to programming.

If this is your first time learning how to program, don’t worry. This textbook has been designed to teach anyone – regardless of experience – how to program. If you follow along with all the examples then you will be an experienced programmer who has written more than 50 programs by the end of this book.

Programming and programming languages, much like real languages, can only be learned through practice. You cannot sit at home and learn to speak French from a textbook. In the same way, you cannot read this book and hope to be a programmer at the end of it. Instead, you will need to write every bit of code and create every program shown in this book. Even if all you do is follow the steps of the examples on your own computer, you will learn how to write code. Once you have mastered the code, you will be able to comfortably use it in your own programs.

For you to master programming, try to work through as many of the programs given to you. Each program has been designed to both teach you new concepts and reinforce existing concepts. The book will start by teaching you how to create simple programs. However, by the end of the book you will be creating useful programs and fun games to play.

Programming is not only about knowing and using the programming language. There are also important theoretical concepts that you will need to understand, and planning and problem-solving tools that you will need to master. The best-coded program in the world will not be useful if it solves the wrong problem. This book has therefore been divided into the following chapters:

Chapter 1: Programming fundamentals

Chapter 2: Object-oriented programming

Chapter 3: Two-dimensional arrays

Chapter 4: Databases and SQL

Before getting started with algorithms, watch the video in the QR code.

To give you the most opportunities to learn, this book will give three types of programming activities:

Examples

Examples will guide you through the creation of a program from start to finish. All you need to do with examples is to follow the step-by-step guidance provided to you.

Guided activities

Guided activities have a program that you need to create on your own. Your teacher will provide you with the solution. These solutions should be used as an opportunity to compare your program, and to see where you may have made errors or left something out.

Activities

Activities are programs that your teacher can give to you as classroom activities or homework. With these programs, you will only be assessed on how well your program works, so use your creativity to come up with a solution!

‘Take note’ and ‘Did you know’ boxes

The boxes provide extra, interesting content that might caution you to ‘take note’ of something important; or give you additional information. Note that the content in the ‘Did you know’ boxes will not be part of your exams.

New words

These are difficult words that you may not have encountered before. A brief explanation for these words are given.

QR Codes, Videos and Screen captures

These will link you to online content. When you are in the eBook, you can easily access the links.

Consolidation activities

This is a revision activity based on what you have covered in the chapter. Take time to answer the questions on your own. You teacher may also use these to assess your performance during class.

CHAPTER UNITS

Unit 1.1 | Problem solving
Unit 1.2 | Procedures and functions in the Delphi run–time libraries
Unit 1.3 | Procedures and functions
Unit 1.4 | User interface design
Unit 1.5 | Databases

Learning outcomes

At the end of this chapter you should be able to:

use algorithms, flowcharts and pseudocode to plan applications

debug applications using a variety of different techniques

create applications using the Delphi IDE

list and describe the most commonly used properties

create events for Delphi applications

use number variables and functions in applications

use string variables and functions in applications

use arrays and files in applications

use different looping structures in applications

use conditional structures in applications

create user-defined methods for applications

describe and implement the principles of user interface design

dynamically create Delphi components in applications

create and read text files in Delphi applications

create a connection to a database using Delphi components

use data from a database in applications.

INTRODUCTION

In this chapter, you will briefly look at all the concepts learned in Grade 10 and Grade 11. To ensure this information is fresh in your mind, this chapter contains five new programs for you to create. These include a statistical simulator, a fake virus application, and a word clock. Work carefully to complete these programs and the units in this chapter.

The remaining chapters of this book will rely on the fact that you are competent and confident with your knowledge about the concepts that we revise in Chapter 1.

UNIT
 1.1   Problem solving

SOFTWARE ENGINEERING: A GUIDE FOR YOUR PAT

Problem solving refers to the process through which a solution is found to a complex problem. Since programmers spend most of their time solving problems, they need to have a method to systematically tackle new problems.

The problem-solving method consists of four steps. These are illustrated in the diagram below and explained briefly:

Did you know

Programming is a way of thinking – not a rote skill. Learning about ‘For’ loops is not learning to program, any more than learning about pencils is learning how to draw.

4 TIPS TO SOLVE PROGRAMMING PROBLEMS

https://www.youtube.com/watch?v=IsLeirHcoJQ

THE PROBLEM-SOLVING METHOD

One of the best problem-solving strategies is: do something.

Take note

First solve the problem, then write the code.

– John Johnson

If, at any time, you get stuck on trying to figure out a solution to a problem, brainstorm your solution using a pencil and paper before trying to write the code for the program. Remember that the most important part of planning is creating a detailed implementation plan that describes how you will solve the problem. To create this plan, you need to make a list (or checklist) of every task that needs to be completed.

In Grade 10 we looked at the user story. This tool is well-suited to help you as you create your detailed implementation plan.

Example 1.1   Creating a detailed implementation plan

TEMPLATE: USER STORIES

Once we have used our user story to create an implementation plan to solve our problem, we have a good idea about which tasks are needed to solve our problem.

Next we can start looking at screen layout for the tasks – this is driven by the user stories.

Here is an activity diagram, which provides an excellent way of capturing the broad picture.

Figure 1.1: Activity diagram – Overview of system (UML)

Once we have completed our activity diagram, we can now consider building a prototype in Delphi, building screens and linking them. However, there will be no functionality yet.

We call this evolutionary prototyping and it demonstrates the logical program flow and navigation between screens. This helps you ensure that the layouts are functional and satisfy the usability requirements.

Take note

In Unit 1.4 of this chapter, we will discuss useful design tips.

Also see Shneiderman’s Eight Golden Rules of Interface Design in Annexure A.

WHAT ABOUT THE DATA?

Because we have an implementation plan as well as a basic Delphi prototype to help us solve our problem in place, we can begin looking at the data we need to complete the tasks we have identified. By using the ‘user stories’ you can get a good idea of what data you will need.

In Grade 10 you also looked at a tool called the ‘noun-verb analysis’. This tool is useful for extracting this information from the user stories. Look at the the second column of Figure 1.1 above. Work through the following steps:

Work through the ‘user stories’ template and collect all of the nouns (YELLOW). In this way you can determine what objects or entities you need to manipulate. The verbs (GREEN) will give you more details of how this should be done.

WHAT’S UML AND WHY DO YOU NEED IT?

https://www.youtube.com/watch?v=8CBnAmYnwk0

List all the details of the entities. You can unpack them using the information you researched earlier.

Create a ‘data dictionary’ in which you can add the name of the data fields you have identified and their types. An example is shown below:

Did you know

Nicholas Wirth used this title on his famous Pascal book: Algorithms + Data Structure = Program

Now you are ready to set up a database if your problem requires it.

Create the UML diagram to illustrate your database and classes. An example is shown below:

Figure 1.2: UML diagram to illustrate your database and classes

Think about how you will complete each of the tasks identified in the user stories. Start by ordering the user stories by importance: ‘essential’, ‘important’ and ‘nice to have’. Consider how things will interact with each other.

Test each small sub-program as you go. To do this, you can use the debugging techniques such as trace-tables, variable watches, error catching and trace printing to find errors.

Make good use of flowcharts or Pseudocode to plan the implementation of algorithms you require. Remember the principles of IPO as you continue with the designs of the algorithms. This will ensure that you end up with a robust solution.

When you get stuck with the implementation of a particular sub-task, create a small, standalone program to sort out the problem, and then move it back into the main program. Because of the detailed implementation plan, you can systematically complete all the small, individual tasks listed on your plan.

Check to see if the plan solved the problem. To do this, test that each sub-task meets its goal.

New words

Double – The Double data type can store 15 - 16 digits in the fraction part of a decimal number and thus support greater precision.

Debugging – The process of detecting and removing existing and potential errors (also called ‘bugs’) in software code that can cause it to behave unexpectedly or crash.

Test the program for any calculation errors or logical problems. To do this, you can use techniques like trace tables, variable watches, error catching. Make use of these debugging tools and/or trace printing to find errors.

Output samples to ensure the program works as expected.

Look back to your initial problem statement and the ‘user stories’ and compare your output to the requirements. This final check ensures that you have not only met the requirements listed on your plan, but also the original requirements from the user stories. Remember to include extreme values, for example boundary values, minimum values, and so on, in your test data to ensure the program works for any value.

Take note

Your program might:

fail to solve the problem

only partially solve the problem

create new problems.

If your evaluation reveals any remaining problems, you can repeat the problem-solving process, focusing on these new problems.

UNIT
 1.2   Procedures and functions in the Delphi runtime libraries

In this unit you will learn about some of the procedures and functions in the Delphi run–time libraries. These will help you as you create different programs to solve problems that you may need to solve as a programmer.

DATE AND TIME

The DateTime variable is a container for a variable of type double. When you look at the variable it shows a date and time (for example, 1 January 2019 06:00:00), but the actual value stored in the variable is a number with a decimal (for example, 43418.25). When working with this value, the whole number represents the full days since 30 December 1899, while the decimal value represents a fraction of a day (that is, the time).

To create a DateTime variable, you can use one of the techniques shown in the table below:

There are also four named functions you can use to access specific times. They are:

Yesterday: Returns the DateTime of 00:00:00, the previous day.

Date: Returns the DateTime of 00:00:00, today.

Tomorrow: Returns the DateTime of 00:00:00, tomorrow.

Now: Returns the current date and time.

To do calculations with a DateTime variable, you add (or subtract) the number or fractions of days to the variable. If you want to set the DateTime’s value equal to now, seven days ago, you can subtract seven days from the current DateTime as follows:

Seven days ago

tLastWeek := Now – 7;

To add or subtract times, you need to add or subtract the appropriate decimal value. The table below shows the decimal value per unit for hours, minutes and seconds.

Did you know

It is usually easier to remember the calculation used to obtain the decimal unit than the exact decimal unit.

One of the biggest advantages of the DateTime variable is the functions related to it. The table below shows some of the most useful DateTime functions:

FUNCTIONS 1 - DELPHI

https://www.youtube.com/watch?v=biFMeKdzMjE

PROCEDURES - DELPHI

https://www.youtube.com/watch?v=9Myahw6q68I

To use these methods, you need to add the DateUtils library to your project. This is done by including the text DATEUTILS in the USES section of your application. Once added, you can use any of the functions inside the DateUtils library.

Example 1.2   Word Clock

Open the project in the Word Clock folder. We will now create the code for the Word Clock. To do this, you need to:

1.Use a variable to store the current time.

2.Use functions to isolate the seconds, minutes and hours from the time.

3.Use a function to ensure that the hours only includes the numbers 1 to 12.

4.Determine whether the time of day is in the morning, afternoon or evening.

5.Write this information to the correct labels every second.

Once you are done, save your application in the folder 01 – Word Clock.

Solution

For this application, you only needed to use the timer’s OnTimer event. The code below gives one possible method for solving this problem, although there are many other solutions that would also work.

OnTimer event

procedure TfrmWordClock.tmrTimerTimer(Sender: TObject);

var

   tTime : TDateTime;

   sTimeOfDay : String;

   iHours : Integer;

begin

   lblSeconds.Caption := FormatDateTime('s', Now) + ' seconds and';

   lblMinutes.Caption := FormatDateTime('n', Now) + ' minutes past';

   tTime := Now – Date;

   if tTime < 0.5 then

     sTimeOfDay := ' in the morning';

   if (tTime >= 0.5) and (tTime < 0.75) then

     sTimeOfDay := ' in the afternoon';

   if tTime >= 0.75 then

     sTimeOfDay := ' in the evening';

   iHours := StrToInt(FormatDateTime('h', tTime)) mod 12;

   if iHours = 0 then

     iHours := 12;

   lblHours.Caption := IntToStr(iHours) + sTimeOfDay;

end;

Once you are done, run and save your application.

In this code, the first two lines make use of the FormatDateTime function to display the seconds or minutes of the current time (NOW). Since the timer updates every second, the time calculated as NOW will increase every second, resulting in the labels showing the newest time every second.

The tricky part of this code is to update the third line, which shows whether the hours are in the morning or evening, as well as showing the hours between 1 and 12 (and not 0 to 24). To do this:

Start by subtracting the current date (date function) from the current date and time (now function).

This will return only the time which will be a fraction between 0 and 1 (with 0 representing 00:00 AM, 0.5 representing 12:00 PM and 1 representing 00:00 AM the next day).

With this decimal value, you can use IF-THEN statements to determine whether it is currently the morning, afternoon or evening.

Make sure the hours are always between 1 and 12. To do this, you start by isolating the hours from the time. While this can be done in a number of ways, the code above uses the FormatDateTime function to display a string that only contains the hours. This string is then converted into an integer.

By using the mod 12 mathematical operator, you calculate the remainder of the hours divided by 12. This will always return the correct hour value under 12. However, when it is 12 o’clock in the morning or night, the remainder will be 0, which is incorrect. To fix this, an IF-THEN statement is used to change the value from 0 to 12.

The hours are converted back to a string and assigned, together with the time of the day, to the label’s caption.

ARRAYS AND FILES

ARRAYS

Arrays are variables that can be used to store multiple related variables of the same type (called elements). As long as there is space left in the array, you can continue writing new information to it. Each array has an array index, which is an integer that indicates the number of the element you want to access in the array. An array can be created using the following syntax:

aName : Array[FirstIndex..LastIndex] of Type;

CREATING AN ARRAY

https://www.youtube.com/watch?v=kW7s90JvZLM

FOR-loops are often used to access each of the items in your array. For example, if the value of each banking transaction is stored in an array variable, you can find the total of all transactions as follows:

For-loops and arrays

for i := 0 to Length(aTransaction) do

begin

  dTotal := rTotal + aTransaction[i];

end;

FILES

To store data used or created by your program permanently, you need to store data in a file on your computer. When working with files in an application, you need to follow five steps. These are:

declare the TextFile variable

assign a text file name to the variable

indicate how the file will be used (rewrite, reset or append)

use the file in the application

close the file

The code snippet below, which writes the phrase “Hello, World!” to a file, shows each of these steps.

File syntax

var

  fOutputFile : TextFile;   // Declare the variable

begin

  AssignFile(fOutputFile, 'output.txt');   // Assign the variable

  Rewrite(fOutputFile);   // Indicate how the file will be used

  WriteLn(fOutputFile, 'Hello, World!');   // Use the file

  CloseFile(fOutputFile);   // Close the file

end;

The following functions can be used to write to or read from a TextFile.

To read all the data from a TextFile, the two functions from the table are usually combined with a WHILE-DO-loop, as shown in the code snippet below.

While loop to read files

while not Eof(fName) do

  ReadLn(fName, sOutput);

Activity 1.1

Answer the following questions using a pen and paper.

1.1.1Give the method and syntax for the following:

a.Converting a string to an integer.

b.Calculating the remainder of division.

c.Rounding a real number to an integer.

d.Copying characters from one string to another string.

e.Finding the year of a date and time.

f.Determining if a year is a leap year.

g.Declaring an array with 5 integer elements.

h.Opening a file to add text to it.

1.1.2What is the difference between the APPEND, REWRITE and RESET procedures?

Activity 1.2

Update your Word Clock activity so that it looks as follows. The date at the bottom of the clock should update automatically and be shown in a single label.

Figure 1.3: The World Clock

UNIT
 1.3   Procedures and functions

CODE YOUR OWN

Procedures and functions are groups of statements that are created that complete a specific task. These procedures and functions can be created for a number of different reasons. They:

break the code into smaller, more manageable pieces.

make the code easier to understand.

make it easier to find problems.

make it easier to add new functions or update the code.

allow you to re-use certain algorithms from different events.

For these reasons, professional programmers try to make use of procedures and functions whenever their code becomes too long or difficult to manage. A general guideline is that you should try to not have the same code repeated, keep procedures (including events) to less than 15 lines of code, and they should almost never be more than 30 lines of code. Just as importantly, each procedure should do only one thing and it should do this thing well.

Both procedures and functions can accept variable inputs (called parameters), but only functions can return a value. The following syntax is used to create a procedure:

Procedure with value parameters

procedure ProcedureName(parameterName1 : type1;

parameterName2 : type2);

var

  var1 : Type;

begin

  Statement1;

  Statement2;

  ...

  Statement1000;

end;

Once a parameter value is sent to a procedure, it works exactly the same as any other variable. The only difference between parameters and variables is that the parameters are defined next to the procedure name and that the parameters start with a value. To call a procedure, the following syntax is used.

Calling a procedure

ProcedureName(Value1, Value2 ... Value1000);

Where the values must be the same number, in the same order and of the same type as the parameters in the declaration the procedure.

There are only three differences between the function syntax and the procedure syntax:

All functions start with the key word function.

After the parameters are listed, functions must specify the variable type of the return value.

Inside the function, a value must be assigned to the variable Result. This value will be returned once the last line of the function has been executed.

Function syntax

function FunctionName(parameter1 : Type; parameter2 : Type): ReturnType;

var

  var1 : Type;

begin

  Statement1;

  Statement2;

  ...

  Result := Value of ReturnType;

end;

A function can be called in the same way as a procedure except that its return value must be assigned to a variable. To call a function:

Calling a function

ReceivedValue := FunctionName(Value1, Value2 ... Value1000);

Custom functions and procedures must be declared after the implementation section of a project but before the first automatically created procedure. This allows all the automatic procedures following the custom functions to make use of the custom functions.

Custom functions and procedures also have access to the form’s global variables and components. However, in order to access the components, the procedure and functions must first use the form’s name, followed by a full stop, followed by the component’s name. This is shown in the code below.

Accessing a component from user defined methods

sValue1 := frmName.lblData.Caption;

sValue2 := frmName.edtData.Text;

Example 1.4   Use reading from and writing to a file to copy the contents

Open the project in the folder 01 – Copy File and insert the following code:

Declare the procedure copyMyFile in the private section:

 procedure copyMyFile(fromFile : String; toFile :String);

Press <Control + shift + C> to create the stub for the procedure.

Add the following code:

procedure TForm1.copyMyFile(fromFile, toFile: String);

var

  infile : TextFile;

  outFile: TextFile;

  line : String;

begin

  if Not FileExists(fromFile) then

   showMessage(fromfile + ‘NOT FOUND’)

  else

  begin

   AssignFile(inFile, fromFile);

   Reset(infile);

   AssignFile(outFile, toFile);

   Rewrite(outFile);

   while not eof(inFile) do

   begin

     readln(infile, line);

     writeln(outFile, line);

   end;

   closeFile(infile);

   closeFile(outFile);

   showMessage(‘File copied’);

  end;

Add the code below to the [Copy File] button Onclick event:

procedure TForm1.btnCopyFileClick(Sender: TObject);

var

  fromFile: String;

  toFile: String;

begin

   fromFile := InputBox('File Copy', 'Copy from – filename:','');

   toFile := InputBox('File Copy', 'Copy to – filename:','');

   copyMyFile(fromFile, toFile);

End;

Run the program and enter the filename Acronyms.txt . This file will be copied to a file with the name of your choice, that is, the name you enter in the second input box.

Activity 1.3

Using pen and paper, create the following user-defined functions.

1.3.1A custom function that accepts a number and an exponent and calculates the solution.

1.3.2A function that returns only the largest value from an array.

1.3.3A function that returns the nth largest value from an array, where n is provided by the user.

UNIT
 1.4   User interface design

The difference between an application that is used by millions of people and an application that is left untouched is often the user interface. This is because applications with easy, fun, beautiful, simple and intuitive interfaces are enjoyable to use, while poorly designed user interfaces can be incredibly confusing and frustrating to use.

WHAT IS USER INTERFACE DESIGN?

https://www.youtube.com/watch?v=WtoK7BzaIsA

This unit looks at the principles of user interface design, how a multi-form user interface can be created and how dynamic user interfaces can be created.

PRINCIPLES OF USER INTERFACE DESIGN

The following principles should be used when creating user interfaces:

Put users in control

Minimise the effort

Eliminate useless items

Give visual cues

Give feedback Be consistent

Accommodate all users. The first step to building a good user interface is to give users control. This means that users should be able use the application and complete tasks in ways that feel intuitive to them.

Take note

Also see Shneiderman’s Eight Golden Rules of Interface Design in Annexure A.

The next core concept is to minimise the effort it takes to use the program. This means you should not ask users to enter the same information more than once, not use unfamiliar jargon or strange terms, make the transition to the next step obvious, help users to provide the correct information, and protect users work so that they never accidentally lose work.

Figure 1.4: Medium (https://medium.com/) minimises user effort allowing users sign-in with a Google or Facebook account

Next, visual elements that are not helping users should be eliminated or hidden until needed. In this way, users are not overwhelmed by all the options and it is clear to users what they are expected to do. Similarly, you should also eliminate steps or tasks that are irrelevant to the solution of the problem.

One way in which applications can be made simpler is by using visual cues. These cues help users to understand where they are in your application, what they are doing, how the application works and what you expect them to do next. Popular visual cues include colours, photos, arrows, icons, animations or a single larger component.

Figure 1.5: WebAfrica uses bright colours to draw the attention to interactive elements

The fifth key concept is to give users feedback. This means that users should receive an acknowledgement when they interact with your application. This can include a progress bar, a message, a loading animation, a button changing colours or sound. By providing user with immediate feedback, they never have to worry that their action was not recorded.

Consistency in user interface design is another key concept. Users who have used some parts of your application should be able to predict how other parts of your application will work. Similarly, items should generally remain in the same place, be the same size and have the same colour across your screens.

Figure 1.6: Buttons with inconsistent positions can create frustrated users

The final concept is to accommodate different users. Users can differ according to:

What they want from your application.

Their skills and experience.

Their ability to use your application.

The tools they are most comfortable with.

Their expectations.

If you want all these users to enjoy using your application, you need to carefully consider each element of your application.

MULTI-FORM USER INTERFACES

To create a multi-form (or multi-screen) user interface, you need to follow three steps:

Create the second form

Open the second form

Pass data between the forms

The example below shows how to create a second form.

Example 1.5   Creating a form

Load the project FriendBookLogin

You will see this form has been created for you.

To a create a new form:

1.Open the File menu in RAD Studio.

2.Select the New option, the click on the Form – Delphi option.

3.You should see a new form open in your project, and Unit2.pas appear in the Project Manager panel.

4.Save this unit in the same folder as your project and give the name SignUp-u.

5.You can now edit the second form by clicking on its Design tab.

6.Copy the design as illustrated:

Example 1.6   Linking forms

To link forms in a Delphi application:

1.Find the USES statement at the top of your first form’s FriendBookLogin_u code .

2.Add the name of the unit SignUp_u to the uses section. This allows your first form to access your second form.

3.In the OnClick event of the [Go To SignUp] button add the second form’s Show method frmSignUp.Show.

4.To close the second form, either click on the [Close] button in the top-right corner or run the form’s Close method frmSignUp.Close.

5.Experiment by changing the frmSignUp.Show to frmSignUp.Showmodal. Explain in your own words how these two instructions differ.

Once you have created your second form, you need to link the two forms so that you can swap between them.

The final step is to pass information between the forms. There are two different ways to do this:

By including a unit in the USES section, you gain access to its visible variables through the form e.g. frmSignUp.iNumber. You also gain access to the properties of its components directly e.g. frmSignUp.lblName.Text.

By saving the data in an external file (such as a textfile), any unit can read the data from the file.

In most situations, you will use the first option to share data between forms. However, saving the data to a file can be useful if the data needs to be stored permanently.

By using these techniques to create new forms, link the forms and share data between the forms, you can add any number of forms to an application. This helps you to create a more organised application where each form serves a specific purpose.

DYNAMIC USER INTERFACES

Dynamic user interfaces are interfaces in which some or all of the user interface components are created using code or while the application is running. By creating user interface components using code, you can dynamically update your user interface based on user decisions, amount of data available or on certain conditions being met.

The following section will look at the different ways to create visual and interactive components.

VISUAL COMPONENTS

To create a non-interactive component using code, you need to follow three steps:

Declare an empty variable of the component’s class.

Create the component and assign it to the empty variable.

Use the component variable to assign values to the required properties.

For example, when creating a TLabel component, you should start by declaring a global label variable.

Declaring global components

var

  frmMain: TfrmMain;

  lblDynamicLabel : TLabel;

BUILDING EFFECTIVE USER INTERFACES

https://www.youtube.com/watch?v=7LWiM7OJick

Next, you should use the TLabel class’s Create method to create and assign a label to this variable.

Creating the component

procedure TfrmDynamicItems.btnCreateLabelClick(Sender:

TObject);

begin

  lblDynamicLabel := TLabel.Create(Self);

end;

Finally, you need to set the properties of the label to ensure it is visible to the user.

Standard label properties

lblDynamicLabel.Parent := Self;

lblDynamicLabel.Text := 'Hello, World!';

lblDynamicLabel.Top := 100;

lblDynamicLabel.Left := 100;

lblDynamicLabel.Width := 100;

lblDynamicLabel.Height := 20;

The table below lists a few of the most important properties.

INTERACTIVE COMPONENTS

Creating an interactive component during run–time with an event (such as an OnClick, OnChange or OnTimer event), can be done in seven steps:

Declare the component variable.

Create the component and assign it to the variable.

Set the component properties.

Declare the name of a custom procedure in the public section of your form.

Add the (Sender: TObject) parameter to the procedure’s name to identify the type of component.

Create the custom procedure for your event in the code.

Assign the name of the procedure to the appropriate event property (for example, the OnClick property).

The first three steps of this procedure are identical to those of visual components.

For the fourth step, you need to add the name of the custom event handler, that will be linked to your interactive component’s event, to the public section of your code. The public section can be found inside the type section, and above the global variables and implementation sections. The image below shows a custom procedure called ButtonClick added to the public section. .

Figure 1.7: A custom procedure in the public section

As shown in the image above, all custom event handlers must have TObject parameter called Sender.

The next step is to create the custom event handler, which is technically a method linked to your form. This can be done in the same way as the custom procedures created in the previous unit. However, since this procedure is a form method that forms part the form, it needs to include the full name of the form before the procedure name. The code below shows an example of the ButtonClick form procedure.

Dynamic OnClick method

procedure TfrmMain.ButtonClick(Sender: TObject);

begin

  ShowMessage('Hello, Button!');

end;

Finally, the method name (excluding the “TfrmMain” part) can be assigned to the appropriate event property when the button is created.

btnDynamic := TButton.Create(Self);

btnDynamic.Parent := Self;

btnDynamic.Text := 'Click me!';

btnDynamic.Left := 10;

btnDynamic.Top := 10;

btnDynamic.OnClick := ButtonClick;

Example 1.7   Photo Bomb

At the start of the 21st century when most computers were still using Windows XP, the most common type of virus was a virus that caused infected computers to automatically open hundreds of new windows containing advertisements. These windows would flood the user’s screen and open more quickly than they could be closed. Since most of these advertisements were for adult-only websites, these viruses were especially embarrassing for their victims!

For this application, you will simulate these old viruses. To start the virus, the user will enter the name of an image and click on a button with the text [Activate Virus]. This button will increase the size of the form and create a new copy of the image every few milliseconds. The position of each image should be randomly selected inside the form.

You can use your own image or the teacher will provide the stop.bmp image to you. Take note, this image must be placed in your application’s win32\debug folder.

The project should be saved in the folder called 01 – Photo Bomb.

Solution

For this application, you can create a very simple user interface. Even though it is not visible, this interface includes a disabled timer.

Once the [Activate Virus] button is clicked, the following OnClick event is run.

Activate Virus OnClick event

procedure TfrmPhotoBomb.btnActivateVirusClick(Sender: TObject);

begin

  frmPhotoBomb.Height := 640;

  frmPhotoBomb.Width := 800;

  tmrTimer.Enabled := True;

end;

This event simply resizes the form and enables the timer. Once the timer has been enabled, the following OnTimer event activates every few milliseconds (based on the timer’s interval).

OnTimer event

procedure TfrmPhotoBomb.tmrTimerTimer(Sender: TObject);

var

  iRandomTop, iRandomLeft : Integer;

  imgPhoto : TImage;

begin

  randomize;

  iRandomTop := Random(frmPhotoBomb.Height) – 150;

  iRandomLeft := Random(frmPhotoBomb.Width) – 150;

  imgPhoto := TImage.Create(Self);

  imgPhoto.Parent := Self;

  imgPhoto.Top := iRandomTop;

  imgPhoto.Left := iRandomLeft;

  imgPhoto.Width := 300;

  imgPhoto.Height := 300;

  imgPhoto.Picture.LoadFromFile(edtImageName.Text);

end;

Looking at this code, you start by randomly selecting a coordinate for the top and left properties of the image. These positions are based on the height and width of the form minus half the height and width of the image. This will ensure that at least half the height and width of the image is always visible, regardless of the randomly selected position.

The next step is to dynamically create the image. This includes setting all the standard properties for the image, including the image’s parent, top, left, width, height properties, and using the Picture.LoadFromFile( ) method.

Activity 1.4

Answer the following questions in your own words.

1.4.1List and describe four principles of user interface design.

1.4.2What are the three steps needed to create a multi-form application?

1.4.3List five common properties that should be set when dynamically creating a label.

1.4.4What is the syntax for an OnClick event declaration used with a dynamically created button?

Activity 1.5

Create the following multi-form application:

1.5.1The project has two forms.

1.5.2Each form contains a textbox with a default value and a button.

1.5.3When the button on the first form is pressed:

a.swap the values between the two textboxes.

b.hide the active first form and display the inactive second form.

UNIT
 1.5   Databases

When programmers want to save data, their first choice is to use a database. This section will look at five parts of using a database with your application:

Creating a database

Connecting a Delphi project to a database

Reading data from a database

Writing data to a database

Using the data from a database

Modifying/manipulating the data from a database

CREATING A DATABASE

The example below shows you how to create a simple, single-table database.

Take note

When creating mock data for a database, it is useful to use a site such as Mockaroo Random data generator.

https://mockaroo.com/

DATABASES IN DELPHI

https://www.youtube.com/watch?v=dwb0wv6IJqA

Example 1.8   Creating a database

To create an Access database:

1.Open Microsoft Access from the Start Menu.

2.Double click on the Blank database option in the main window.

3.In the window that opens, click on the Open icon .

4.Select an appropriate folder to save your database and click OK.

5.Select Microsoft Access Database (2002-2003) as the file type.

6.Enter the name of your database in the File name text box.

7.Click OK to close the window, then click on the Create button to create the database.

8.Find the Table 1 table in the All Access Objects panel on the left side of Access.

9.Right click on this table and select the Rename option.

10.Enter the name of your table and press Enter.

11.Inside the table, click on the Click to Add option at the top of the table.

12.Select the field type then enter a name for the field.

13.Continue adding fields to your table until all fields have been added.

14.Enter values into these fields to create new records.

15.Save the database in the same folder as your application and close Microsoft Access.

CONNECTING TO A DATABASE

To connect to a database in Delphi, you will need to use three invisible components:

TADOConnection: Creates a connection to an external database.

TADOTable: Uses the database connection to connect to a specific table inside your database.

TDataSource: Create a connection between your TADOTable and Delphi visual components.

The first two of the components can be found from the dbGo list in RAD Studio’s Tool Palette, while the DataSource component can be found from the Data Access list.

These components should not be added directly to a unit, but rather to a data module. A data module is a special form or container to keep all the database components organised and together. By separating the database connections from any form, you can import the database into each form through the “uses” section.

Figure 1.8: The dbGo list from the Tool Palette

Example 1.9   Adding a data module

To add a data module to your project:

1.In the Project Manager panel in the top right corner of the screen, right click on the .exe project file.

2.Select the Add New option and click on Other.

3.Inside the New Items window, select the Data Module option and click OK.

4.This will add a new data module to your project.

5.Save the data module in your project folder and rename the data module form (for example, dbmName).

6.Select your main form and open the Code screen.

7.Add the data module’s name (e.g. dbmName) to the USES section of your main form’s code.

Once you have created the data module, you can add the database connection components to it.

Example 1.10   Adding database connection components

To add the database connection components:

1.Add a TADOConnection component to the data module and change the name, for example, conCards.

2.Change the connection’s LoginPrompt property to False to avoid receiving a login prompt every time you run the application.

3.Double click on the connection component in your data module. This will open the Connection String window.

4.Click on the [Build] button.

5.In the Data Link Properties window, select the Microsoft Jet 4.0 OLE DB Provider (.mdb database) or Microsoft Database Engine (.accdb database) option and click Next.

6.In the Connection tab, enter your database name and extension in the textbox.

7.Select your database and click Open for the .mdb database option.

8.In the Connection tab click on the Test Connection button. You should receive a message stating that the test connection succeeded.

9.Click OK, then click OK again.

Now that the database has been connected to your Delphi project, you can add a connection to a specific table. To do this:

1.Add a TADOTable component to your data module.

2.Click on the dropdown list next to the Connection property of your table and select the name of your database connection component.

3.Change the value of the TableName property to the name of the table you are connecting to.

4.Change the table’s Active property to True.

5.Add a TDataSource component to your application.

6.Change its DataSet property to the name of your TADOTable component.

7.Save your application.

You now have a connection directly to a table on your database.

READING DATA FROM A DATABASE

Once a database connection has been made, you can start using and displaying the data in your application. The easiest way to do this is to display the database table as a table (or TDBGrid component) in Delphi. The TDBGrid component can be added from the Data Controls list in the Tool Palette.

Figure 1.9: The TDBGrid component in the Grids list

Example 1.11   Using the grid component

To link a database table to a grid component:

1.Add a TDBGrid to your form.

2.Select the Grid component and change its DataSource property to the name of your TDataSource component.

3.Save and test our application. If all the connections were made correctly, you should now see the data from your database table appear on your grid component.

WRITING DATA TO A DATABASE

To add a record to a database, you will use two of the TADOTable component’s methods.

By using the append or insert method, an empty row is added to the end of your database. You can then set the values in this row, much like you would change the values of a variable.

Adding values to database fields

tblCards.Append;

tblCards[‘fieldName1’] := 'Hello';

tblCards[‘fieldName2’] := 'World';

tblCards.Post;

Take note, the field names are placed inside single-quotation marks, inside square brackets, after the table name. This syntax will always be used to access specific fields in a table.

USING THE DATA FROM A DATABASE

To use the data from a database (without first showing it in a grid), you use an algorithm that is similar to the algorithms used to read text files:

Selecting the first record of the database.

Create a while loop that runs until you reach the end of the file.

Inside the loop, read the relevant fields for your application.

Using these values, manipulate the data as needed by your application.

Finally, move to the next record and repeat the process.

The table below shows the table methods that allow you to iterate through the data.

Once you have selected the correct record, you can use the tableName[‘fieldName’] syntax to read the fields’ data. The code below shows an example of these methods being used.

Using the data methods

dbmData.tblName.First;

while not dbmData.tblName.Eof do

begin

  sValue := dbmData.tblName['fieldName'];

  dbmData.tblName.Next;

end;

MODIFYING THE DATA FROM A DATABASE

To modify data saved in a database:

Select the record you want to modify.

Set the record to edit mode (using the Edit method).

Set the new values for the record.

Post the new values to the database.

The following code shows how this can be done, using the Locate method to select the correct record.

Modifying data

dbmData.tblName.First;

dbmData.tblName.Locate('fieldName', searchValue, []);

dbmData.tblName.Edit;

dbmData.tblName['fieldName'] := newValue;

dbmData.tblName.Post;

To see how a database can be used in practice, work through the following example.

Example 1.12   Big Bucks setup

You and a group of friends have decided to create Big Bucks, an application to track debts within your circle of friends. The application will record every payment between friends and automatically determine if a payment is a new loan or a repayment.

To create Big Bucks:

1.Open a new application and save it in the folder 01 – Big Bucks.

2.Create the following user interface.

The box on the right of the table is a TStringGrid component called grdBigBucks.

3.Create a new data module called bigBucks_d.pas.

4.Change the name property of the data module to dbmBigBucks.

5.Add a TADOConnection component to the data module (called conBigBucks) and a TADOTable component to the data module (called tblBigBucks).

6.Add a TDataSource component to the data module called sorBigBucks.

7.Copy the BigBucks.mdb database from your teacher to your project’s folder.

8.Select the conBigBucks connection and click on the [Edit ConnectionString] button at the bottom left of the Object Inspector.

9.Click on the Build button.

10.Select the Microsoft Jet 4.0 OLE DB Provider option and click Next.

11.Use the … button to select the “BigBucks.mdb” database then click on the [Test Connection] button.

12.If the test is successful, click on the OK button, then click on the OK button in the Connection String window.

13.Change the connection’s LoginPrompt property to False and the Connected property to True.

14.Select the tblBigBucks table component.

15.Change the Connection property to conBigBucks, the TableName property to Payments and the Active property to True.

16.Select the sorBigBucks component and set the DataSet property to tblBigBucks.

17.Open the code editor of the bigBucks_u unit.

18.Add bigBucks_d to the USES section of the code. This imports the data module into your unit.

uses

  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants,

  System.Classes, Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls,

  Vcl.Grids, bigBucks_d;

19.Select the grdBigBucks component and set the DataSource property to sorBigBucks.

20.Save and run your application. You should see the headings from your Payments table in your string grid.

Congratulations, you have just created the database connection for your application. In the next activity, you will create the code that uses this database.

Example 1.13   Big Bucks code

In the previous example, you set up the database connection for your Big Bucks application. In this example, you will allow users to add a payment of money between two friends. This payment will have one of three impacts:

If no current debt exists between the two friends, the payment will record a new debt between the friends.

If receiver already owes the sender money, an additional debt will be created.

If the sender owes the receiver money, the payment will be used to first pay-off this debt (and any other debts that are found). If money is left over, a new debt will be created between the sender and receiver.

To create this application:

1.Open the project saved in your 01 – Big Bucks folder.

2.Save the values entered into the three text boxes into variables called sSender, sReceiver and rRemainder.

3.Add the following conditional statement to your code.

Appending condition

if rAmount > 0 then

begin

  dbmBigBucks.tblBigBucks.Append;

  dbmBigBucks.tblBigBucks['sender'] := sSender;

  dbmBigBucks.tblBigBucks['receiver'] := sReceiver;

  dbmBigBucks.tblBigBucks['payment'] := rAmount;

  dbmBigBucks.tblBigBucks['creation_date'] := Now;

  dbmBigBucks.tblBigBucks.Post;

end;

This conditional statement checks if the amount in rAmount is positive before adding the data to the database. While this condition may not be relevant right now, it will be used later to check if there is any money left after a repayment was made. It is important to remember that, when you add a record to a database, start with the Append (or Insert) function and end with the Post method. The lines in between these two statements specify the values that need to be added to the different database fields.

4.Save and test your application. You should now be able to add debts to the database.

The next step is to check if a payment can be made against an existing debt before any new debts are made. To do this:

5.Before you append the data, read the first record of the database. This can be done using the dbmBigBucks. tblBigBucks.First method.

6.Create a WHILE-DO loop that repeats while the database is not at the end of the file and while rAmount is larger than 0.

7.At the end of the WHILE-DO loop, use the table’s Next method to move to the next record in the database. Without this line, the WHILE-DO loop may continue running forever.

WHILE-DO-loop

dbmBigBucks.tblBigBucks.First;

while (not dbmBigBucks.tblBigBucks.Eof) and (rAmount > 0) do

begin

  // Check if an existing debt exists between the sender and receiver

  // Check if the existing debt is equal to the current payment

  // Check if the existing debt is larger than the current payment

  // Check if the existing debt is smaller than the current payment

  dbmBigBucks.tblBigBucks.Next;

  end;

8.Create a conditional statement inside the WHILE-DO loop to check if the payment is a repayment of debt.

You know that the payment is a repayment if the database contains a record where the current sender was the receiver, while the current receiver was the sender. This means you need to read each record inside the while statement and see if this condition is met.

IF-THEN statement

if (sSender = dbmBigBucks.tblBigBucks['receiver']) and (sReceiver =

dbmBigBucks.tblBigBucks['sender']) then

begin

  // Store value of existing debt

  // Check if the existing debt is equal to the current payment

  // Check if the existing debt is larger than the current payment

  // Check if the existing debt is smaller than the current payment

end;

9.When a record is found where this condition is met, store the value of the payment field in a variable called rExisting.

10.Create a condition to check if rAmount is greater than or equal to rExisting.

11.Inside the condition, run dbmBigBucks.tblBigBucks.Delete method to delete the selected record.

12.Set the value of rAmount to equal rAmount minus rExisting.

Cancelling a debt

if rAmount >= rExisting then

begin

  dbmBigBucks.tblBigBucks.Delete;

  rAmount := rAmount – rExisting;

end;

Taking a look at this code, the condition checks if the amount paid is enough to fully repay the debt. If it is, the existing debt record is deleted and rAmount is adjusted to reflect the debt that has been repaid. If there is any money left in rAmount, the WHILE-DO loop will continue looking through the database to see if there are additional debts to repay. If there are no debts to repay, the WHILE-DO loop will exit. Since rAmount is still larger than 0, a new debt will be added to the database.

13.Save and test your application. You should now be able to remove debts by making a payment in the opposite direction.

14.Create a condition to check if rExisting is smaller than rAmount. This will mean that the debt is not fully repaid and needs to be adjusted based on the amount repaid.

15.Inside the conditional statement, run the command dbmBigBucks.tblBigBucks.Edit to edit the table.

16.Set the payment field of the table equal to rExisting minus rAmount.

17.Set the repayment_date field equal to the date returned by the Now function.

18.Use the Post command to make these changes to the database.

19.Set rAmount equal to 0, since all the money has been used to repay the debt.

Partially repaying a debt

if rAmount < rExisting then

begin

  dbmBigBucks.tblBigBucks.Edit;

  dbmBigBucks.tblBigBucks['payment'] := rExisting – rAmount;

  dbmBigBucks.tblBigBucks['repayment_date'] := Now;

  dbmBigBucks.tblBigBucks.Post;

  rAmount := 0;

end;

Since rAmount will always be 0 for a partial repayment, the WHILE-DO loop will end once this payment is made and no additional changes will be made to the database.

20.Save and test your application. You should now be able to add debts, fully repay debts and partially repay debts.

Congratulations, you just created an application that can easily store hundreds (or even thousands) of money transfers between people! To do this, you needed to create a database connection, add information to the database, read information from the database, and modify existing data on the database.

Databases will be covered in more detail in Chapter 4.

Activity 1.6

Answer the following questions using pen and paper.

1.6.1Which two Delphi components are needed to connect to a database and database table?

1.6.2Which Delphi component can be used to display the data from a database?

1.6.3Write down Delphi commands to do the following:

a.Select the next record in a table.

b.Set the value of the database field Name to Kagiso.

c.Check if the database field Number has a value of 7.

d.Save changes made to the database.

e.Read each record in a table.

CONSOLIDATION ACTIVITY  Chapter 1: Programming fundamentals

QUESTION 1

Komani Game Reserve in the Eastern Cape offers accommodation and the chance to see three of the ‘Big Five’ animals. Do the following:

Open the incomplete program in the 01 – Question1 folder.

Compile and execute the program. The program has no functionality currently.

Follow the instructions below to complete the code for QUESTION 1.1 to QUESTION 1.5.

1.1A picture file called elephant.png has been included in the root folder of the Question1_p Delphi project. Write code for the following:

•Display the text “KOMANI GAME RESERVE” on pnlHeader.

•Change the colour of pnlHeader to black.

•Change the font colour of pnlHeader to white.

•Display the elephant.png picture on the imgQ1_1 component.

•Disable btnQ1_1.

Example output:

1.2The game reserve is situated on a rectangular piece of land and the dimensions of the reserve are as follows:

•length: 40.4 km

•width: 27.8 km

The values for the length and width of the game reserve are stored in two global constants called dReserveLength and dReserveWidth, respectively.

Calculate the perimeter of the game reserve and display the result on lblQ1_2A in the following format:

  PERIMETER: <calculated perimeter> km

The surface area of a rectangle is calculated with the following formula: A = W × L

Calculate the surface area of the game reserve and display the result on lblQ1_2B in the following format:

  SURFACE AREA: <calculated surface area> square km

Example output:

1.3Visitors who want to spend more than 1 day in the reserve have a choice between the following two accommodation options:

Write code for the following:

•Check whether the user has indicated that accommodation is required in the cbxAccommodation component.

•In case the user has indicated that accommodation is required, check which accommodation option was chosen in rgpChaletOption and extract the number of nights he/she needs accommodation for from sedNumNights.

•Calculate and display the cost of accommodation on pnlQ1_3. If the user did NOT indicate that accommodation is required, display the text “NO ACCOMMODATION” on pnlQ1_3.

Example output:

1.4The game reserve hosts three of the Big Five. These are elephants, buffalo and lions. Visitors are able to report sightings, which give other visitors an idea of which animals there are to see on a given day. Sightings are compiled into a string of keys (single characters) that indicate which animals have been sighted.

Each key in the string represents the following animals:

Buffalo and elephant sightings are common, but lion sightings are rare.

Write code for the following:

•Extract the sightings from edtSightings.

•Use the string of keys to create a list of animals sighted and display this list on redQ1_4.

•Count the number of lion sightings and display the result on a message box in the following format:

  Lion sightings: <number of lion sightings>

Example of output if EEBEBEEBLEELB was entered as the sightings string:

Example of output if EBBEBL was entered as the sightings string:

1.5Potential visitors need to be able to make a reservation. A reference code needs to be generated for each reservation.

Reference codes are compiled as follows:

•The first part of the reference code is a hash-symbol ( # ).

•The second part of the reference code is a random number ranging from 1000 to 9999 (both inclusive).

•The third part of the reference code consists of the first two letters of the visitor’s surname. Both letters are in UPPER CASE.

Write code for the following:

•Test if the visitor’s surname is entered in the edtSurname component. If edtSurname is empty, display an appropriate error message and set the focus to edtSurname.

•If the visitor’s surname is entered, extract it from edtSurname and generate a reference code.

•Display the reference code on pnlQ1_5.

Example output:

QUESTION 2 Database manipulation

This section consists of two questions. The following important notes are applicable to both questions:

You are NOT allowed to modify or add to the supplied data in any way.

Good programming techniques must be applied when coding your solutions.

NO marks will be assigned for hardcoding. Use control structures and variables where necessary.

NO FILTERS MAY BE USED.

SCENARIO:

The HealthActive gym is currently running a healthy living program where a member’s health status is captured and checked.

The Health.mdb database contains one table called Members.

The Members table is structured with the following fields:

Example data from the Members table:

Take note

Connection code has been provided.

When the btnDBRestore button is clicked, the data in the database will be restored to the original data.

The name of the table to be used in your code must be tblMember, which is the TADOTable object connected to the database.

Do the following:

•Compile and execute the program in the 01 – Question2 folder. The program currently has limited functionality

•Complete the code for each question as described in [Question 2.1] and [Question 2.2].

•The program contains a graphical user interface with two-tab sheets labelled [Question 2.1 ] and [Question 2.2].

2.1Data processing

Select tab sheet [Question 2.1 ], which displays the following user interface when the program is run:

Complete the code to meet the requirements specified in QUESTION 2.1.1 to QUESTION 2.1.3

2.1.1Radiogroup [2.1.1]

Write code for the OnClick event of the radiogroup that will sort the contents of the Members table and display it on the database grid called dbgTable as follows:

•Radiobutton 1: From newest to oldest according to the join date.

•Radiobutton 2: Ascending order of customer names and surnames.

2.1.2The user will be asked to enter an account number. Write code to search the database table to check if the member’s account number appears in the table. If it is found, the date on which the member joined the program must be displayed in the following format:

Example of output if Account number: DreS284 is typed into edtSearch.

If the record is not found, a suitable message must be displayed.

2.1.3A person’s body mass index (BMI) is a measure for indicating nutritional status in adults. It is defined as a person’s weight in kilograms divided by the square of the person’s height in metres (kg/m²). For example, an adult who weighs 70 kg and whose height is 1.75 m will have a BMI of 22.9.

70 (kg)/1.752 (m²) = 22.9 BMI

For adults over 20 years old, BMI falls into one of the following categories:

Write code to display a list of all members their BMI rounded to 1 decimal and their respective nutritional health status, neatly in columns on the redOutput:

Example output:

2.1.4Write code to calculate and display on the redOutput which percentage of male members are smokers.

Display:

•the number of male members who are smokers

•the percentage of the male members who are smokers rounded to two decimals places

•the total number of members in the table.

Example of output:

2.2Data maintenance

Select tab sheet [Question 2.2], which displays the following user

Complete the code to meet the requirements specified in QUESTION 2.2.1 to QUESTION 2.2.3.

2.2.1The email address for the member with member number PraM786 has been incorrectly captured as mpragnell2n@studiopress.c. Add code to correct the email address to mpragnel@studpress.com

2.2.2You want the user to be able to delete the record that he/she selects. Write the code to delete the current record from the table. Keep track of the name of the member that will be deleted. Display a message once the record has been deleted, as follows (if the third record in the table was selected):

2.2.3The details of a new member have been supplied in the appropriate components. Complete the code to insert this member into a new record in the table. The account number must be constructed using the first 3 letters of the surname, the member’s initial and any random 3 digit number (between 100 and 999).

QUESTION 3

SCENARIO

You have been asked by the organisers of a Gymnastics competition in Strand to complete a program to manipulate the numbers of learners from 10 schools in the vicinity who are taking part.

Open the project in the folder 01 – Question 3 folder.

The incomplete main form unit called Question3_u.pas

Currently the program has no functionality.

An incomplete form class Question3_u is provided with the following graphical user interface:

Complete the code for each section of QUESTION 3 as specified in QUESTION 3.1 to QUESTION 3.4 below.

In the given program the following two global parallel arrays have been created:

a String array called arrSchools with the names of the ten schools that have registered to take part.

an Integer array called arrNumGymnasts with the number of the learners from each school who will be competing.

3.1When this button is clicked a call to the DisplayArrays procedure is made.
Do the following:

•Create a procedure called DisplayArrays that will receive the heading as a parameter.

In the implementation of the procedure:

•display the heading as indicated in the parameter

•loop through the two given arrays and display the information in two columns in the rich edit redOutput, as indicated in the btnDisplayClick event handler

•Call the procedure and use the heading “The schools participating” in the parameter to display the information.

•Add code to determine and display the total number of gymnasts.

Example output:

3.2The organisers want a list of the schools with the number of gymnasts sorted from lowest to highest as indicated in the screen shot below. Write the code to sort the arrays to achieve this.

Use the DisplayArrays procedure with the appropriate heading as a parameter to display the sorted list.

Example output:

3.3The organisers want a code for each of the schools. The code consists of the first letter of each of the words of the school’s name and a random number in the range 100 to 999 (both included), for example, RGHS367 for Rhenish Girls’ High School.

Do the following:

Create a function called GenerateCode that will receive the name of the school as a parameter and return the generated code.

In the implementation section of the function extract the first letter from each word in the name of the school and then add a randomly generated number between 100 and 999 (both included).

Take note

Your code should provide for the fact that each user might type the name of the school in a different case (lower and/or upper case)

If the name of the school was not found, an appropriate message should be displayed in the rich edit.

In the btnCodeClick event handler loop through the array called arrSchools, call the GenerateCode function for each of the schools in the list and display the generated codes as indicated in the screenshot below:

3.4You want the user to type in the name of a school and then determine and display how many learners from that school are competing. Use an Inputbox to get the name of the school to search for, and then display the number of learners competing in redOuput, as indicated in the screenshot below.

Example output:

QUESTION 4

SCENARIO:

Your principal asked you as an IT student to help write a program which will determine whether anyone in your school has a birthday on any given day. He wishes to use the program every morning before school to check whose birthdays he has to announce today.

Do the following:

Compile and execute the program in the 01 – Question 4 folder.

Complete the code for each question, as described in QUESTION 4.1 to QUESTION 4.3.

Supplied GUI:

4.1Add code to the On Create event handler of the form to do the following:

4.1.1Display the current (today’s) date in the edit boxes provided

4.1.2Check if the file, Birthdays.txt exists. Display a suitable message if the file does not exist.

The text file contains data in the following format:

Name,Surname,Gender,Year-Month-Day

Example of the first 5 lines:

Noluvo,Mdlungwana,F,2001-07-12

Laureka,Wallace,F,2000-03-01

Michelle,van Heerden,F,2001-05-23

Maria,Kok,F,2000-09-19

Brewster,Attew,M,2001-12-09

4.2Work on the [Check Birthdays] tab sheet. Add code to the event handler of btnDisplay.

4.2.1Create suitable variables for each component of a line from the text file

4.2.2Open the Birthdays.txt file for reading. Assign this file to the given global text file variable, tfBirthdays.

4.2.3Ensure that all the data is removed from redBirthdays when the button is clicked.

4.2.4Make use of variables to store the current year, month and day. Ensure that the program will still work if any other day of the year is entered.

4.2.5Loop through the text file and determine if any of the entries are on the date entered in the edit boxes. Display the name, surname and age of the records that match.

4.2.6Determine the total number of birthdays for this day and display it with the output.

Example output: (for the date 2018/08/16)

4.3Study the interface for the [AddBirthday] tab sheet

4.3.1Write code for the [Add Birthday] button to extract all the information from the components.

Add validation to check that only ‘M’ or ‘F’ was added to edtGender.

Display a message if anything else was added.

4.3.2Once the button is clicked, the student’s information has to be added to the text file, and a suitable message has to be displayed.

QUESTION 5

5.1Which type of loop can be used to read each entry in an array?

a.CASE

b.FOR-loop

c.REPEAT-loop

d.WHILE-DO loop

5.2Determine which of the following are objects, properties or events.

5.3Give an example of a binary variable.

5.4Name ONE way of preventing programming errors.

5.5A user is required to input a FOUR-character security code which contains ONE alphabetical character and THREE digits. The first character must be alphabetical, for example “D845”.

Write an algorithm, using pseudocode, to validate the code for the correct format once the code has been entered. NOTE: Use at least ONE loop must be part of your solution.

CHAPTER UNITS

Unit 2.1 | Defining a user-defined class
Unit 2.2 | Using the class

Learning outcomes

At the end of this chapter you should be able to

describe the class as a data type

discuss the different access specifiers

describe attributes and methods as part of a class

define a class:

add attributes to a class

declare and implement methods in a class

instantiate objects of the class

use the object in your application.

OOP IN DELPHI

https://www.youtube.com/watch?v=gRUvQglZ5jI

Watch out!

We use analogies to describe the concept of an object; don’t get too attached to these analogies. They only serve to illustrate a point. Instead, concentrate on drawing out the abstract nature of an object as we apply an everyday term to a programming technique.

INTRODUCTION

In this chapter we will look at Object-Oriented Programming (OOP) in more detail. To understand OOP and user-defined classes, think about your national ID card. Each South African citizen is issued with an ID smart card using an ID blueprint.

Figure 2.1: An ID card is like a custom class

New words

Object-Oriented Programming (OOP) – refers to a type of computer programming (software design) in which programmers define not only the data type of a data structure, but also the types of operations (functions) that can be applied to the data structure

Example of some of the details captured for your ID:

photo

name

surname

identity number

nationality

date of birth

We refer to these data items as fields.

The ID smart card can be:

scanned to transfer the data on it

presented to verify the person’s identity.

New words

attributes – the data fields of the class

behaviour – the code that provides the interaction with the attributes

instances of the class – a data type that describes the attributes and behaviour of the object to be model electronically

encapsulation – the grouping of attributes and behaviour in one entity

We say the fields used to gather data are attributes of the card and what the card can be used for Is the behaviour of the card.

The attributes and behaviour form a blueprint for creating an ID smart card. We refer to the blueprint as a class. The instance of a class is referred to as an object.

From the one blueprint each South African citzen can get an Identity Smart Card i.e many Instances of the class or card objects can be created. Like an array a class can have many data items, however whereas an array can only have data Items of the same type whereas a class can have data items of different data types. As a record in a database has data items of different types, so does the data items of a class. The grouping of data together with the functions to interact with the data is called encapsulation.

OOP-techniques enable programmers to:

create independent modules that are not influenced by other parts of the program

create reusable code

improve the integrity of a program and its data.

In this chapter you will learn how to declare classes, implement its methods, instantiate an object of the class and use the object’s methods in an application.

UNIT
 2.1   Defining a user-defined class

After the introduction, you might think that classes are a brand-new concept that you will have to learn from scratch. Fortunately this is not the case. You have been using multiple classes in every single program you have created, like the TForm class. If you create a new Delphi project and open the code, you will see the following lines near the top of your code.

USER DEFINED OBJECTS

https://www.youtube.com/watch?v=OuMu4dgJZ8M

Figure 2.2: TForm1 class

By looking at the declaration of the TForm1 class above, you can see the basic structure of a class definition, using the key words: Type, class, private, public and end. These are the essential keywords for declaring a class and implementation to provide it with behaviour.

TForm1 has TForm as its base class.

The keyword, private, marks the section where the attributes and methods are declared, that should not be accessible from outside the class.

The keyword, public, marks the section where we declare methods that serves as the interface to the internal features of the class. These methods are accessible from outside the class.

The implementation section is where all the events, procedures and functions are placed. Any active code that you want to execute should be placed in this section.

Take note

Notice how the Form1 variable is declared to be of type TForm1. An instance of class TForm1, also called an object, can be assigned to Form1.

DECLARING A CLASS

The code block below shows the basic structure of a class declaration.

Class declaration syntax

type

ClassName = class(optional BaseClass)

 Private

//declare attributes and private methods here

 Public

//declare public methods here

 End;

In order to understand whether to declare a public or a private method, you need to understand how access specifiers work. As their name suggests, access specifiers tell your class which methods should be visible (or accessible) to units outside the class, and which methods should only be visible inside the class.

New words

Access specifiers – is a defining code element that can be determine which elements of a program are allowed to access a specific variable or other piece of data

There are four types of access specifiers:

Class attributes or fields are only declared in the private section of the class. This data hiding ensures that access is only given to class members and protects the integrity of the data.

Once the attributes and methods have been declared, you need to write code to implement the methods. This is done in the implementation section. Once the methods are implemented your class definition is complete and ready to be tested.

Activity 2.1

Answer the following questions in your own words:

2.1.1What is a class?

2.1.2What is an object?

2.1.3Give an example of two classes that you have used in your programs.

2.1.4Write down the syntax for declaring a class.

2.1.5What is the difference between the public and private keywords?

2.1.6Why should class attributes be private and not public?

2.1.7Find the errors in the following basic class structure:

implementation

 className : class(optional BaseClass);

 Private

Attribute1: String;

Method1;

 Public

Attribute2: Integer;

Method2;

To define a class in Delphi, you need to take the following steps:

create a new unit file that will only contain the code for the class in the TYPE section name the class

add attributes and methods in the PRIVATE section

add methods in the PUBLIC section

constructor method

accessor (or getters) methods

mutator (or setters) methods

other auxiliary/helper methods

ToString method for quick access to the state of an object.

Read the following case study, which will be used throughout this unit to look at each of the steps above.

Case Study Second-hand phones

Imagine you are building a web application that facilitates buying and selling second-hand phones. Once the site is up and running, you expect there to be hundreds of users selling and buying phones at any time. You will recognise a phone as an entity similar to the ID-card. Each phone has a number of attributes you need to record, including the phone’s brand, the phone’s model number, the seller’s price and the phone’s date of purchase. How would you store this information?

The best way to store this information is to encapsulate it in a custom class named TPhone.

Your TPhone class might have the following essential properties:

Brand: String

Model: String

Owner: String

PurchaseDate: TDateTime

Price: Double

Figure 2.3: How would you store the data needed to sell phones?

We could also consider a method to work out the age of the phone. So the buyer can get a quick feel about the value for money of the offer.

An object of the type TPhone can store the brand, model, owner, purchaseDate and price, together with methods to access and manipulate it, such as a method that is able to return the age of the phone.

Once you have defined the TPhone class, you can create one or more TPhone objects in your application (or even a list or array of TPhone objects). Each of these objects will contain a unique set of information and will represent a phone.

CLASS UNIT FILE

The first step in creating your new application that facilitates buying and selling second hand phones (see Case study above) is to create a new unit file for the new class. Just as a data module separates your data from any specific form, the unit file separates the class from a form. This allows you to use the class in multiple forms in a multi-form application and even in other applications. By separating the class from the form, you keep the code clean, making it easier to keep track of your code and reduce the repetition of code.

In order to create an object to be used in your application, a design specification for your model needs to be written. This is then captured in a class diagram. The class diagram lists the attributes and behaviour for all objects of the class.

In the case of our PhoneClass, the formal class diagram looks like this:

As we develop the app, you will see how this directs the process.

A class definition is a coded version of the class diagram with all methods implemented. Once the class has been defined, it can be used in an application like any other data type.

New words

reusability – is an important OOP principle.

Activity 2.2 Defining classes: Pen and paper

In each case, clearly indicate where in the class unit file the code to declare the class, the attributes and methods should be placed.

Do not implement the methods.

2.2.1A TCar class containing the attributes Model, Brand, Year, RetailPrice and the following methods:

a.A default and a parameterised constructor.

b.A getDetailedModel function.

c.A setRetailPrice procedure and getRetailPrice function.

d.A toString function.

e.A getVATPrice function.
Name the unit CarClass.

2.2.2A TSong class containing the attributes Artist, Song, Album, TrackNumber and Duration (in seconds) and the following methods:

a.A Default and a parameterised constructor.

b.Getters and setters for all attributes.

c.A toString method.

d.A getQuickReference method that returns a string.

e.A getMinuteDuration method that returns a string.
Name the unit SongClass.

2.2.3A TQuadratic class for the class diagram given below:

Name the unit QuadraticClass.

2.2.4A TCone class according to the partial Cone class diagram given below. Complete the declaration of the class, adding getters and setters that you may need.
Name the unit ConeClass.

Activity 2.3 Defining classes: Delphi projects

2.3.1Open the project saved in the carPriceList folder. Add a unit CarClass and provide code to implement the TCar class you wrote in Activity 2.2.1.

a.Complete the constructor, getters and setters you have generated.

b.The toString method returns a string that displays the lines attribute label: attribute value below each other.

c.The getDetailedModel function returns a string in the format Year Brand Model.

d.The getVATPrice function returns the car’s retail price multiplied by 1.15.

2.3.2Open the project saved in the myPlaylist folder. Add a unit SongClass and implement the TSong class you wrote in Activity 2.2.2. Complete the class definition as follows:

a.Code the constructor, getters and setters you have generated.

b.The toString method returns a string that displays the lines attribute label: attribute value below each other.

c.The getQuickReference function returns a string in the format ‘Song,~Album~Artist’.

d.The getMinuteDuration function converts the song duration from seconds to minutes and seconds and returns a string in the format mm:ss.

2.3.3Open the project saved in the quadraticEquations folder. Add the unit QuadraticClass and code the class definition you wrote in Activity 2.2.3. Implement:

a.The constructors.

b.CalculateDiscriminant.

c.calculateRoots to return the roots in format, ‘x1 = 0.00 : x2 = 0.00’. Use the formula:

d.HasRealRoots should return true if the discriminant is >= 0.

e.HasRationalRoots should return true if the discriminant is a perfect square.

f.A toString method to return the quadratic in the form:

Hint: use chr (178) to display superscript 2.

2.3.4Open the project saved in the coneCalculations folder. Add the unit ConeClass and code the class definition you wrote in Activity 2.2.4. Implement the methods listed in the class diagram and the getters and setters you added.

2.3.5Update your TPhone class by adding getters for Brand, Model and Owner.

LET’S REVISE

Did you know

Generally, we let the method calling calculateAge deal with the display of the age.

Using many short methods reduces complexity.

Each method should only have one task.

Did you know

A method name followed by its parameters is called the method signature.

Overloading is declaring more than one method with the same name. Overloading allows us to have multiple methods that share the same name, but with a different number of parameters and types.

Example:

      Public

           Constructor create; overload;

           Constructor create (brand, model, owner : String;

purchaseDate : TDateTime; price : Double/Real); overload;

UNIT
 2.2   Using the class

Before other units in your application can use your class, they need to import the class unit. Having access to the custom class allows the application to create objects of the imported class and call its public methods.

PROGRAM INTERACTION WITH THE CLASS

Activity 2.4 Pen and Paper

2.4.1Write down the syntax for instantiating a class.

2.4.2Given is the constructor declaration for a TNGO class:
Constructor create(Name: string; Funds : Double: Donations : Integer; Code: String); For (a) to (g) below, state whether an accessible NGO object will be created. If not, then state why.

a.NGO.create(‘Helping Hand’, 23500.00, 50, ‘HH007’);

b.NGO := TNGO.create(‘Helping Hand’, ‘HH007’, 23500.00, 50);

c.NGO := TNGO.create(‘Helping Hand’, 23500.00, 50, ‘HH007’);

d.TNGO := NGO.create(‘Helping Hand’, 23500.00, 50, ‘HH007’);

e.NGO := TNGO.create(‘Helping Hand’, 23500.00, 50);

f.TNGO.create(‘Helping Hand’, 23500.00, 50, ‘HH007’);

g.NGO := TNGO.create(‘Helping Hand’, 23500.00, 50.00, ‘HH007’);

2.4.3Give a Delphi statement to import the class, NGOClass into the form unit.

2.4.4How will you ensure that the value of an attribute cannot change after the object is created?

2.4.5What is the function of the toString-method?

2.4.6What criteria will you use to decide if a method belong to the class or not?

2.4.7Which one of the following is the correct syntax for the signature(header) of a get-method? Also state why the others are incorrect.

a.Procedure methodName(parameter: datatype);

b.Function methodName(parameter: datatype): dataType;

c.Procedure methodName: dataType;

d.Function methodName: dataType;

Did you know

Non-governmental organisations, or NGO were first called such in Article 71 in the Charter of newly formed United Nations in 1945. While NGOs have no fixed or formal definition, they are generally defined as non-profit entities independent of governmental influence

Activity 2.5

2.5.1Open the project saved in the 02 – carPriceList folder. Provide code to:

a.Import the CarClass unit in the frmCar unit.

b.Declare the global variables Car of type TCar and an Integer variable Row initialised to 1, in the implementation section. Remember the var keyword.

c.In the [Save] button’s onclick event handler, provide code to:

i.Store the inputs brand, model, year and retail price.

ii.Create the Car object

iii.Call the methods getDetailedModel and getVATPrice to display the model details and the retail price in the string grid.

Did you know

When using a class, always check for for potential helper methods. Any statement or function that will be used more than once and is only related to the class data should be moved to a private helper method inside the class.

2.5.2Open the project saved in the 02 – myPlaylist folder. Provide code to:

a.Import the SongClass unit in the frmSong unit.

b.Declare a global variable mySong of type TSong and an Integer variable Row initialised to 1, in implementation section. Remember the var keyword.

c.In the [AddtoPlaylist] button’s onclick event handler, provide code to:

i.Store the inputs Artist, Album, Song, TrackNumber and duration.

ii.Create the mySong object.

iii.Call the methods getQuickReference and getMinuteDuration to display the quick reference and the minute duration in the string grid.

CHALLENGE

2.5.3The mathematics teacher at school Overcrowded High is requesting your help. The teacher wants an application that will generate quadratic equations in the following categories:

those that have rational roots

those that have irrational roots.
He also wants only those equations for which coefficients a, b and c are non-zero.
The equations and their roots must be sent to a file. The teacher wants to print the file and give each of the 60 learners a unique set of three problems to solve.
Open the project HelpMathTeacher in the folder quadraticEquations. The code for the buttons [Save] and [Shuffle List] has been provided.
You are required to import the QuadraticClass and declare a variable of type TQuadratic.
In the Make list event-handler, the code to generate permutations of the coefficients a, b and c is given as three nested loops. Insert code in the nested loop to do the following:

a.Test for non-zero coefficients.

b.Create a Quadratic object.

c.Check which problem type is selected.

d.Test if the current object meets the selected problem type.

Build a line with the quadratic equation and its roots.

Add this line to the list box.
In b to d above, use the methods of the Quadratic class you imported.

2.5.4The Maths Literature teacher has asked you to develop an application to help the learners check their homework based on cones. The App. is required to calculate three values for a cone:

slant height, surface area and volume given the height and diameter of the base.

The following buttons have been provided in the Project file inside the 02 – Cone Calculation:

a.In the form class add code to import the class.

b.Button [Create New Cone]: demonstrates the parameterised constructor. Use two (2) input boxes to get the parameters required to instantiate the object, create the object and use the toString method to display the state of the Cone object in the memo. Code to enable the other buttons is given.

c.Button [Slant Height]: using the cone created in b, display the slant height in the memo.

d.Button [Volume]: using the cone created in b, display the volume in the memo.

e.Button [Surface Area]: using the cone created in b, display the surface area in the memo.

One of the reasons custom classes are so powerful is that they allow you to keep lists of objects, with all the data related to each object stored in the object itself. This is a lot more reliable than creating multiple parallel lists, as inconsistencies can easily appear in parallel lists (for example, if an item is deleted from one list but not from the others).

Our SellMyPhone App. only uses one object at a time and reuses the object variable for each instatiation. Once a second phone is created you can’t go back to the first object to change the price of the phone. You will have to create another phone object for the same cell phone and new price. This shortcoming motivates the use of a list to store objects so that we can use them in a meaningful way.

Did you know

You will not be examined on your ability to create arrays (or lists) of objects. However, when creating your own applications, custom objects will often be used with arrays and lists.

CONSOLIDATION ACTIVITY  Chapter 2: Object-oriented programming

QUESTION 1

1.1Explain the concept of encapsulation in object-orientated programming.

1.2What is the purpose of a constructor in object-orientated programming?

1.3Explain why you would use a getter without setters.

1.4The following class diagram has been suggested as part of the transport program to manage their drivers:

f.The minus sign (-) shows that the declaration is private while the plus sign (+) shows that the declaration is public. Explain the difference between a public declaration of attributes and a private declaration of attributes and methods.

g.Write down an example of an auxiliary method from the Driver class.

1.5Define object-orientated programming.

QUESTION 2

For this application, open the project saved in the 02 – Question 2. Once done, save your project in the same folder.

Tourists visiting South Africa often travel to different areas of the country. Normally, guests to a bed and breakfast (B&B) have to pay the bill for all the extra items they ordered during their stay when they check out. To provide a service that makes them more appealing than other B&Bs, the Petersen Group has decided to let their guests transfer their accumulated extra costs between the guesthouses in each town. Guests will have to pay the bill for these items when they check out at the last guesthouse on their journey. They decided that the best way to manage this is to e-mail a text file indicating the extra costs of the guests to the next guesthouse.

You have been asked to write the program to handle the extra costs of the guests. The data is stored in a text file named “Extras.txt” in the following format: GuestNo#GuestName#ExtraType#CostPerItem An example of some of the data in the text file:

1#Mr G Ferreira#Phone#7.05

2#Mrs L Honeywell#Drinks#71.95

3#Ms I Mendes#Kitchen#39.95

1#Mr G Ferreira#Kitchen#23.95

1#Mr G Ferreira#Drinks#7.15

4#Mr B Khoza#Taxi#127.25

2.1Define a class named TExtraItem. Create appropriately named and typed private fields to hold the following data (suggested field names are given in brackets):

•guest number (guestNum)

•item type description (itemType)

•cost per item (cost)

2.2Write a constructor method that accepts the guest number, the item description and the cost per item as parameters. All the fields must be initialised in the constructor.

2.3Write an appropriately named get method (accessor method) to return the guest number.

2.4The company uses a 25% markup on cost per item to determine profit. Write a method named calculateProfit that calculates and returns the profit (that is, cost*25/100).

2.5Write a method named calculatePrice that calculates the final price of the item (that is, cost + the calculated profit).

2.6Write a method named toString that builds and returns a string with information on the item formatted as follows:
Item type<tab>Cost<tab>Profit<tab>Final Price
Any numbers must be formatted to two decimal places.

2.7Create an array named arrItems that holds TExtraItem objects. Write code in the OnActivate event handler of the form to read information from the text file Extras.txt according to the following steps:

a.Test if the text file exists. Display a suitable message if the file does not exist and terminate the program.

b.Use a loop to:

•read a line of text from the text file.

•separate the text into the guest number, item type and cost.

•use this information to create a new TExtraItem object and place the object in the array named arrItmes.

c.Use a counter field to keep track of how many items there are in the array.

2.8When the user clicks the [List Items] button, the program must do the following:

a.Allow the user to enter a guest number.

b.Search through the array. Each time an item for the guest is found:

•calculate the profit using the percentage mark-up and calculate the final price.

•display the information using the toString method.

•add the final price for each item to get a grand total.

c.When the search is complete the program must:

•display the total amount due for the guest.

•display an appropriate message to say that there are no extra charges for this guest, if no items have been found.

An example of the final output is shown below:

QUESTION 3

For this application, open the project saved in the 02 – Question 3. Once done, save your project in the same folder.

A constellation is a group of related stars that covers the night sky. Some stars are considered to be navigational, while others are passive. A navigational star is used to assist with calculating direction and movement.

The application you will be using has the following user interface.

3.1Write code for a constructor method that will receive the name of the star, its magnitude, its distance from the Earth and the constellation it belongs to as parameters. Set the FOUR respective attributes to the received parameter values and initialise the fNavigationalStatus attribute to FALSE.

3.2Write code to create an accessor method for the constellation attribute fConstellation.

3.3Write code for a mutator method called setNavigationalStatus, which will receive a Boolean value as a parameter and set the navigational status attribute to the received value.

3.4Write code for a method called determineVisibility that will determine and return a description of the visibility of the star. The visibility of a star depends on its distance from Earth in light years and its magnitude.
Use the following criteria to determine the description of visibility that applies to a star:

3.5Write code to create a toString method which returns a string formatted as follows:

•<name of star> belongs to the <constellation> constellation.

•The star has a magnitude of <magnitude> and is <distance from Earth> light years away from Earth.

If the star is a navigational star, add the following line:

•<name of star> is a navigational star.

Otherwise, add the line:

•<name of star> is a passive star.

3.6For the [Instantiate Object] button, the user is required to select the name of a star in the combo box. Once done, write code to do the following:

a.Extract the name of the selected star from the combo box.

b.Use a conditional loop and search in the text file for the name of the selected star. The loop must stop when the name of the star has been found in the file.

c.If the name of the star has been found, do the following:

•Instantiate a TStar object using the objStarX object variable that has been declared globally as part of the given code.

•Test whether the star is a navigational star using the aNavigationStars array and set the value for the navigational status attribute accordingly.

d.If the name of the star has NOT been found in the text file, display a message to indicate that the star was not found.

3.7When the [Display] button is clicked:

a.Display the details of the star in the rich edit component redDescription using the toString method.

b.Load the image of the constellation that the star belongs to into the imgStar component. The file name of the image to be displayed is the name of the constellation the star belongs to. All image files have the extension “.jpg”.

3.8The brightness and visibility of a star is dependent on the magnitude and the distance of the star from Earth. When the [Visibility] button is clicked, call the relevant methods to display the name and visibility of the star as a Message Box.

For example, if Mimosa is selected and the [Visibility] button is clicked, you should see the following message.

•Star: Mimosa
Visibility: Hardly visible to the naked eye

CHAPTER UNITS

Unit 3.1 | 2D arrays
Unit 3.2 | 2D arrays with data
Unit 3.3 | Application for 2D arrays

Learning outcomes

At the end of this chapter you should be able to

describe the concept of 2D arrays

define the structure of 2D arrays

input data to 2D arrays using different sources

use the data from 2D arrays

output the data from 2D arrays using column and row headings.

2D ARRAYS

https://www.youtube.com/watch?v=lCepY3luREc

INTRODUCTION

Imagine that you are writing a chess application. One of the main tasks of your application would be to record the position of the pieces after every move. To do this, your application would need to analyse each square of the chessboard after a move and record whether it contains a piece or not. How would you do this?

Figure 3.1: A chessboard contains eight rows and eight columns

You could create 64 individual variables with each variable storing the name of the piece on it. However, with 64 variables, you would need to access each variable individually since you cannot use a loop to iterate through them. Furthermore, the computer would not understand the relationship between these squares (that is, that one square is above another one), making it very difficult to use in the rules of your game.

A better solution would be to create eight array variables, with each array containing the squares from one row. This would have the benefit of your program understanding how all the squares in the rows are related. However, you would still need to individually access the eight arrays, and your computer will still not understand the relationship between the different arrays.

In fact, the best way to handle this data is to create a single two-dimensional (or 2D) array. A 2D array uses two indices: one for the row and one for the column. This has a number of very significant advantages:

You can store the data (elements) using a single variable.

You can scroll through the elements using a nested FOR-loop.

The relationship between the elements (that is, which squares are next to which) is captured in the indices, making it possible to use these relationships in your code.

In this chapter, you will learn more about 2D arrays and how they can be used in programming.

UNIT
 3.1   2D arrays

Since Grade 11, all your examples of arrays have been of one-dimensional arrays. A one-dimensional array stores an i-number of elements, of a specific type. These elements can be accessed using an index (usually “i”). For example, if you created an array to store the first five powers of three, the values could be represented using a single row with five elements.

The third element in this array can be accessed using the array name and the index of the element in square brackets.

Accessing an element in a 1D array

iValue := aNumbers[3];

In contrast, 2D arrays store two-dimensional data which is represented using a grid with rows and columns. Each element in the 2D array has two indices (usually I and J), with the first index giving the row number and the second index giving the column number.

If you look at the 2D grid below, you will see that it is made up of five rows and three columns. The value of the first index indicates the number of the row. This means that aNumbers[1, J] (where value of the first index is 1) refers to the elements in the first row, while aNumbers[2, J] refers to the second row of elements. In contrast, the numbers in the second index indicates the column number. For example, aNumbers[I, 3] refers to the elements in the third column of the array.

The table below shows a visual representation of a 2D array.

The numbers in the array were created by raising the index I (the row number) to the power of J (the column number).

The table effectively shows how 15 integer values can be represented uniquely using the same identifier, aNumbers, and the two indices surrounded by square brackets to distinguish between them. It shows 15 unique integer variables that can be accessed or be assigned new values. Variables must be declared before they can be used. How is the 2D array declared?

DECLARING A 2D ARRAY

To declare a 2D array, you can use the following syntax:

Where I gives the number of rows in the array and J gives the number of columns. As can be seen from this declaration, 2D arrays have the same general limitations as 1D arrays, namely:

all the elements of the array must have the same type

the array has a fixed size.

Activity 3.1  Declaring 2D arrays

Using a pen and paper, declare the following arrays.

3.1.110 rows and 10 columns of integers.

3.1.25 rows and 4 columns of strings.

3.1.3500 rows and 3 columns of reals.

3.1.4250 rows and 400 columns of strings.

3.1.5an array to model weeks and days in the month of May

Have you noticed that the 2D array models displays things in a similar manner to a spreadsheet using only one data type? That data type can be any of the built-in datatypes as well as those you declare.

ACCESSING ELEMENTS IN A 2D ARRAY

To access a specific element in a 2D array, you need to give both the row and column number.

Example 3.1   Accessing and using an element in a 2D array

Use the aNumber array above to reference the numbers given in the examples below.

When working with 2D arrays, it is important to remember that the first index indicates the row number while the second index indicates the column number.

ASSIGNING VALUES TO 2D ARRAYS

Once declared, you can assign values to, and read values from the array by providing the column and row index.

Example 3.2   Assigning values to and array element

The activity below shows how this can be used in a real-life example.

Activity 3.2  Chess and 2D arrays

Look at the chessboard below. As you learned in the introduction of this chapter, the position of pieces on a chessboard can be represented using a 2D array with 8 rows and 8 columns.

Based on this position, complete the following tasks.

3.2.1Declare an array of strings called aBoard, to represent the 8 × 8 squares on the chess board.

3.2.2Assign the position of all the pieces to the correct element in array aBoard. Make sure to use different strings for white and black pieces or access elements with the same pieces.

3.2.3At the top right of the board, there are two yellow squares. These squares show that the white king moved from the first column to the second column in the previous move. How would you code this move using your array?
The names of the pieces are given in the table below:

Since the data is saved in an array, you can use the information as part of your program. For example, you can easily count which colour has the most pieces by stepping through your array and counting the first letters stored for each square (“w” for white or “b” for black).

USING LOOPS TO STEP THROUGH AN ARRAY

While assigning values one-by-one can be useful (as in the chess example), it is more common to use FOR-DO loops to assign values to an array. The nested FOR-DO loop is the perfect control structure to scroll through a 2D array. The indices must stay within the declared boundaries or an out of bound error will occur.

Use the length function to ensure that the indices remain within the boundaries.

var

    aNumbers : Array [1..7,1..5,1..8] of Integer;

begin

    showMessage(IntToStr(Length(aNumbers)));          //output 7

    showMessage(IntToStr(Length(aNumbers[1])));       //output 5

    showMessage(IntToStr(Length(aNumbers[1,1])));     //output 8

end;

Note how without any indices Length returns the first upper boundary when the lower boundary is 1. By fixing the first index, any number from 1 to 7 will work, giving the upper boundary of the second index, and so on. If a fourth index [,1..12] was given, how will the length expression change to return its upper boundary, 12?

Example 3.3   Nested-FOR-DO loop

1.Use a nested FOR-DO loop to assign the sum of the row and column numbers to each element of array aNumbers.

var

  aNumbers : Array[1..3, 1..2] of Integer;

  i, j: Integer;

begin

1:  for i := 1 to length(aNumbers) do

2:    for j := 1 to length(aNumbers[1]) do

3:      aNumbers[i, j] := i + j;

end;

The code will produce the following 2D array:

Visual representation of the array

To see how values are assigned to the array in this FOR-DO loop, you can use a trace table. By stepping through the code bit-by-bit, it becomes easier to see how each element of the array is assigned a value.

For the code snippet above, add more rows to the trace table below and complete tracing through the nested FOR-loop. Track the values of the following variables:

1.   i

2.   j

3.   Name of array element (including indices)

4.   Value of the array element.

2.Use the same nested loop pattern to extract/access data from the 2D array.
In this example we have two options:

•build a line of output and then when complete add it to the Memo. Note the line is set to null after adding to the Memo.

•build a single line which include line breaks then add.

for i := 1 to length(aNumbers) do

begin

 for j := 1 to length(aNumbers[1]) do

 line := line + format('%d ', [aNumbers[i, j]]) ;

 Memo1.lines.add(line);

 line := '';

end;

//alternative

for i := 1 to length(aNumbers) do

begin

 for j := 1 to length(aNumbers[1]) do

 line := line + format('%d   ', [aNumbers[i, j]]) ;

 line := line + sLineBreak;

end;

Memo1.lines.add(line);

Activity 3.3  Nested-FOR-DO loop

Now that you have seen how 2D arrays can be manipulated with nested FOR-DO loops, create the following 2D arrays using pen and paper (include the variable declarations):

3.3.1An array containing the multiplication table for the first six numbers.

3.3.2An array containing the powers table for the first ten numbers and three exponents. You can use the Power function to do this.

3.3.3A 10 by 10 2D array of numbers

•Fill the array with random numbers in the range 100 to 999.

•Extract the numbers from the 2D array and in a memo component display as a 10 by 10 table.

•Insert an empty line and display the leading diagonal on the next line in the memo.

USES OF 2D ARRAYS

Up to now, you have looked at very specific uses of 2D arrays, but these arrays are incredibly useful in a number of different situations. Two of the most common general uses are when you need to store a few pieces of information about every item in a list, and when you need to store a grid of information. Here are some examples:

An application’s usernames and passwords can be read using a 2D array.

The results of two tests and an exam for 500 students can be stored in a 500 by 3 array.

A list of companies’ incomes, expenses and balances can be store in a 2D array.

The multiplication table is a grid that can be stored in a 2D array.

The pixels of an image can be represented as a grid using a 2D array.

The numbers on a sudoku puzzle can be stored in a 9 by 9 array.

The 4 suits of cards containing 13 cards each can be stored in a 13 by 4 array.

Various board games where the board has a grid like structure.

From the examples you will see that a 2D array is suitable for modelling, or storing, most data in a table form. A 2D array can also be used to store the data of a CSV file – as long as all of the elements have a common type.

Figure 3.2: Tables and grids of information can be represented with 2D arrays

Activity 3.4

Open the application saved in 03 – Array Questions. You should see the following user interface.

Create the following 2D arrays and display their values in the lstAnswer component. Make use of the tab space character (#09) to display the values from the columns separately.

3.4.1The 6 by 6 multiplication table.

3.4.2A 9 by 9 table containing a random value between 1 and 9 for each element.

3.4.3A 10 by 5 table containing the sum of the indices.

3.4.4A 7 by 5 table of randomly generated lowercase characters.

Did you know

The listbox component has a TabWidth property which determines how much space is used by the tab space character. This property has a default value of 0 which means tab space characters are not shown, unless you change the property.

For the rest of this chapter, you will be creating applications that work with 2D arrays.

UNIT
 3.2   2D arrays with data

In the previous unit, the data for the arrays were either entered manually (as in the chess example) or generated automatically using numbers. An additional method can be used to add data to arrays which is to read the data from a text file or database. To see how this can be done, you will use the following dataset (saved in the marks.csv text file), which contains information about five students at your school.

However, before you can start working with the data you first need to add it to a 2D array.

Example 3.4   Adding data to an array

Open the project saved in your 03 – School Marks folder. You should see the following user interface.

In the following example testing for the existence of the file left out to keep the code simple. The code for the CSVIntoArray procedure is given below:

Procedure code

procedure CSVIntoArray;

var

 sData : String;

 iComma, i, j : Integer;

 fCSV : TextFile;

 // aData : Array[1..5, 1..7] of Integer; (already declared globally)

begin

 AssignFile(fCSV, 'marks.csv');

 Reset(fCSV);

 ReadLn(fCSV, sData);

 for i := 1 to 5 do

begin

 ReadLn(fCSV, sData);

 for j := 1 to 7 do

 begin

  iComma := Pos(',', sData);

  if iComma := > 0 then

  begin

   aData[i, j] := StrToInt(Copy(sData, 1, iComma - 1));

   Delete(sData, 1, iComma);

  end

  else

   aData[i, j] := StrToInt(Copy(sData, 1, Length(sData)));

  end;

 end

end;

Activity 3.5

Based on the code in the example 3.4 above, answer the following questions:

3.5.1What is the purpose of this procedure?

3.5.2Describe the purpose of the following lines:

a.for i := 1 to Length(aData) do

b.ReadLn(fCSV, sData);

c.for j := 1 to Length(aData[1]) do

d.iComma := AnsiPos(‘,’, sData);

e.aData[i, j] := StrToInt(Copy(sData, 1, iComma - 1));

f.Delete(sData, 1, iComma);

3.5.3When will the value of iComma be equal to 0?

3.5.4Why is there a ReadLn function before the loop starts?

3.5.5In the TStringlist version, describe the purpose of the following lines:

a.inList := TStringList.Create;

b.inList.DelimitedText := sData;

c.aData[i,j] := strtoint(inList.Strings[j-1]);

3.5.6Why does inList.Strings[j-1] have j-1 as the index?

3.5.7Create an event for the [Read CSV] button that displays the data from the array in the list box. Make sure to run the CSVIntoArray procedure at the start of this event.

WORKING WITH ROWS OF DATA

In your 2D array above, each row contains information about a single entity (in this case, a student). This is typical when you read data from a CSV file or a database into an array. In contrast, each column contains information about a specific field. For example, the first column contains all the student numbers, while the second column contains the students’ ages.

When you want to work with the data of a row, you can keep the row number (or first index) constant while using a FOR-DO loop to update the values of the second index.

Example 3.5   Find the largest mark in the first row

Stepping through a row

iLargest := 0;

for j := 4 to Length(aData) do

begin

 if aData[1, j] > iLargest then

  iLargest := aData[1, j];

end;

In this snippet, a FOR-DO loop was created that repeats once for each column of the array. The FOR-DO loop’s counter I is then used to iterate through each element of the first row of aData in order to find the largest value.

To see how this technique can be applied to your school’s dataset, complete the following activity.

Activity 3.6 Stepping through a row

Using the 2D array created for School Marks, create events for the [1] and [2] buttons to calculate and display the following:

3.6.1The 3rd student’s average mark for his tests, assignment and exam.

3.6.2The 2nd student’s highest mark.

WORKING WITH COLUMNS OF DATA

As the previous case study shows, stepping through the values of a single row is useful when you want to make comparisons or do calculations for a specific item in your array. However, when you want to do calculations or make comparisons using all the values of a single field, you need to step through a column.

To step through a column, you keep the second index constant while using a FOR-DO loop to update the values of the first index.

Example 3.6   Determine the age of the oldest student

Stepping through the first column

iAge := 0;

for i := 1 to length(aData) do

begin

 if aData[i, 2] > iAge then

  iAge := aData[i, 2];

end;

Take note, the FOR-DO loop counter was changed to I. This is because the letter I is used for the first index of a 2D array. Inside the FOR-DO loop, the second index (column number) of the array is now held constant while the first index (row number) changes as the loop repeats.

To use this in an application, complete the following activity.

Activity 3.7 Stepping through a column

Using the 2D array created for School Marks, create events for the [3] and [4] buttons to calculate and display the following:

3.7.1The total fees outstanding.

3.7.2The average age of students.

Once complete, save the project in the 03 – School Marks folder.

STEPPING THROUGH ALL THE DATA

The final way to step through the data is to use a nested FOR-DO loop statement. With a nested FOR-DO loop statement, the outside loop can be used to systematically select each of the rows or columns. The inside FOR-DO loop can be used to step through the values of the selected columns or rows. By combining two FOR-DO loops, you can therefore step through all the data in a table.

Example 3.7   Determines the number of A’s scored by the class

Stepping through all the data

iCount := 0;

for i := 1 to Length(aData) do

 for j := 4 to length(aData[1]) do

  if aData[i, j] > 80 then

   Inc(icount);

lstResults.Items.Add(IntToStr(iCount));

With this information in mind, complete the following activity.

Activity 3.8 Stepping through all the data

Using the 2D array created for School Marks, create events for the [5] and [6] buttons to calculate and display the following:

3.8.1The highest single mark between the tests, assignment and exams.

3.8.2The average of all the marks.

Once complete, save the project in the School Marks folder.

Activity 3.9

Using the 2D array created for School Marks, create events for the [7] to [15] buttons to calculate and display the following:

3.9.1The 5th student’s lowest mark.

3.9.2If the 4th student obtained their personal highest mark for the assignment.

3.9.3If the 2nd student’s average is above 90.

3.9.4The age of the oldest student.

3.9.5The student number of the student who obtained the highest mark for the exam.

3.9.6The student number with the highest outstanding fees.

3.9.7The student with the highest average mark.

3.9.8The lowest overall mark between all tests, exams and assignments.

3.9.9The lowest mark of the student with the student number “200004”.

Once complete, save the project in the School Marks folder.

UNIT
 3.3   Applications for two-dimensional arrays

Now that you are more comfortable working with a two-dimensional array, you can use that knowledge to create the game Sudoku. In Sudoku, players start with a 9 × 9 grid with a few numbers inside each 3 × 3 square in the grid. The goal of the game is to fill each empty space in the grid with a number between 1 and 9, in such a way that there are no duplicate numbers within any:

Row

Column

Diagonal

3 × 3 square

ARRAYS IN DELPHI - PART 1

https://www.youtube.com/watch?v=0MuOM3cT30M

The image below shows an example of an incomplete and completed Sudoku game.

Figure 3.3: A starting and completed game of Sudoku

To create a game of Sudoku in Delphi, you will need to do the following:

Create a 9 × 9 array to store the user’s values and a grid to display the values.

Create a Duplicate Checker algorithm which checks if there are any duplicates in a list of numbers.

Create a nested-loop that sends each of the nine rows to the Duplicate Checker.

Create a nested-loop that sends each of the nine columns to the Duplicate Checker.

Create a nested-loop that sends each of the nine 3 × 3 squares to the Duplicate Checker.

Create a function to save or load starting positions.

ARRAYS IN DELPHI - PART 2

https://www.youtube.com/watch?v=-KKRZqlmO9U

This chapter will complete the first four of these tasks. However, if you would like, you can complete the last two tasks on your own.

Example 3.8   Setting up Sudoku

To create the Sudoku game, you need to start by creating a grid to display the results and an array to store the results. You also need to build the link between the grid and the array, allowing values entered into the grid to be stored in the array. To do this:

1.Open the project saved in the 03 × Sudoku folder. You should see the following user interface.

Activity 3.10

Update your Sudoku game to inform the player when he or she wins the game. To win the game, the following conditions need to be met:

3.10.1There should be no 0 values in the array.

3.10.2There should be no duplicates in any rows or columns.

Once done, save your application in the Sudoku folder.

CONSOLIDATION ACTIVITY  Chapter 3: Two-dimensional arrays

QUESTION 1

1.1Which of the following lines can be used to access an element from a 2D array?

a.aNumbers[4;3]

b.aNumbers[4][3]

c.aNumbers[4,3]

d.aNumbers[4,3,7]

1.2Write the code you would use to declare a 2D Boolean array with 6 rows and 15 columns called aWinLose.

1.3A 2D array called aRainfall has been declared to contain the average rainfall per month for five towns.

a.What structure must be used to access the monthly rainfall for all towns.

b.Write the code used to show the monthly rainfall figures in table form.

1.4A two-dimensional array called aStock has been used to record the quantities of the four items for the four departments, as shown in the table below.

Write pseudocode to calculate the total stock per item and store these values in the array.

1.5Three parallel arrays called aPassengers, aStations and aMonths have been declared to contain the number of passengers that pass through a train station each month.

a.Why it is NOT possible to use a 2D array instead of three parallel arrays to store this data as given?

b.What changes would you make to store the data into a 2D array?

QUESTION 2

For this application, open the project saved in the 03 – Question 2 folder. Once done, save your project in the same folder.

The online-shopping website of MajorMax allows customers to buy items online from various departments at their store. The manager of the company must analyse their weekly sales figures.

The GUI below shows the interface of the program used by MajorMax to keep track of their weekly sales figures.

The program contains code that declares two arrays, aDepartments and aSales.

The aDepartments array contains the names of the various departments that sell products online

The aSales array is a two-dimensional array that contains the sales figures for the first six weeks of the year for each department. The rows in the array represent the various departments and the columns represent various weeks.

2.1When the [Sales Information] button is clicked, display the content of the aSales array with suitable headings in the redOutput component provided. All monetary values must be displayed in currency format with TWO decimal places, as shown below.

2.2A report of all underperforming departments per week is required. A department is underperforming when their sales figure is lower than the average sales for all the departments for that week. When the [Display underperforming departments] button is pressed, display a report with the sales figures of all underperforming departments. All monetary values must be displayed in currency format with TWO decimal places.

The output below shows an example of an underperforming report for the first three weeks:

2.3Currently the data in the aSales array represents the sales figures for the first six weeks of the year. Before the sales figures for the next week (Week 7) can be added to the array and analysed, the current data for Week 1 must be backed up to a text file. To do this:

a.Use Delphi code to create a new text file. The name of the text file is the number of the week of the sales figures that are archived. For example, if the sales figures for Week 1 are archived in the file, then the name of the text file will be Week 1.txt.

b.Save the data (with headings) to the new text file, a shown below.

c.When the data for Week 1 has been archived, the data for Week 2 in the arrSales array must be moved to the position of Week 1 in the array; the data for Week 3 must be moved to Week 2, and so on.

d.For test purposes, the sales data for the new week must be randomly generated values between R500 and R5 000.

e.Use code to update the labels used to display the number of the week. The new report should look as follows.

QUESTION 3

For this application, open the project saved in the 03 – Question 3 folder. Once done, save your project in the same folder.

A new game called Galaxy Explore is planned and needs to be developed. The purpose of the game is to prepare a grid with a number of randomly placed planets that are not visible to the player. The player must then guess the position of the planets on the grid. The grid will be referred to as the Game board.

The GUI below shows an early version of the user interface for the program.

The program must do the following:

Populate the 2D array when the game starts.

Allow the user to guess the positions of invisible planets placed randomly on the game board.

Determine whether the player has won or lost and terminate the game. The player wins when he/she identifies two planets on the game board within five guesses.

3.1When the [Start game] button is pressed:

a.The [Play] button must be enabled and the rich edit components must be cleared.

b.A dash character (-) represents an open space in the aGame and a hash character (#) represents a planet. The 2D array must first be populated with open-space characters.

c.The content of the aGame array must be displayed in the game board area.

d.The aGame array must be updated to include the appropriate number of planets. The level of difficulty selected from the radio group rgbQ2 determines the number of planets. The following rules apply:

•Difficulty level 1: 50 positions in the array must be replaced by planets (#).

•Difficulty level 2: 40 positions in the array must be replaced by planets (#).

•Difficulty level 3: 30 positions in the array must be replaced by planets (#).

e.The value ‘0’ must be displayed on the panel pnlQ2NumberOfGuesses, which indicates the total number of guesses. The image below shows the user interface after the [Start game] button is pressed.

3.2When the [Play] button is clicked:

a.The player guesses the position of a planet by selecting a row number and a column number from the combo boxes provided.

b.If the position of a planet is guessed correctly:

•Update the display to show the position of the planet (#) that was guessed correctly.

•Update the number of correct guesses on the panel pnlQ2NumberOfGuesses.

c.In the aGame array, replace the planet character (#) that was guessed correctly with the “Y” character to show which planets were identified during the game play session. This information is required for the [Reveal Planets] button.

d.If the position guessed is NOT the position of a planet, display the row and column values of the incorrect guess in the redQ2Incorrect output area.

e.Allow the player to guess the positions of planets repeatedly until he or she wins or loses the game.

•A game is won as soon as the positions of two planets are correctly guessed.

•A game is lost if fewer than two planets are guessed within the allowed five guesses.

f.Use a message box to display a suitable message based on the outcome of the game, either ‘Game won’ or ‘Game lost’.

g.Disable the [Play] button when the game is over. The image below shows the output if the positions of two planets were guessed correctly within two guesses:

The image below shows the output if the player lost the game. The position of only one planet was guessed correctly within five guesses:

3.3When the [Reveal planets] button is clicked, write code to display the game board with all the randomly placed planets revealed.

CHAPTER UNITS

Unit 4.1 | Select and sort columns
Unit 4.2 | Select columns and rows
Unit 4.3 | Calculated columns
Unit 4.4 | Aggregate functions
Unit 4.5 | Data maintenance
Unit 4.6 | Querying two tables
Unit 4.7 | Database applications

Learning outcomes

At the end of this chapter you should be able to

select columns and calculated fields

select columns and rows using conditions

use functions and aggregate functions when selecting data

update, add and delete data using SQL statements

query one or two tables using SQL statements

use the data from an SQL statement in a Delphi application.

WHAT IS SQL

https://www.youtube.com/watch?v=hWU2pvj_xIc

INTRODUCTION

SQL stands for Structured Query Language, which is a standardised language that can be used to work with almost all relational databases. This includes database management software (DBMS) like Microsoft SQL Server, Oracle, SQLite, MySQL and Microsoft Access.

New words

relational database – a database structured to recognise relations between stored items of information

SQL can also be used inside most programming languages, including Delphi, to communicate with databases. This allows you to complete almost any task on a database, including:

adding, changing and deleting records in a table

selecting data/records from tables

displaying sorted records selected from a Database

performing calculations on fields and records in tables

grouping data/records from tables

joining tables.

In this chapter, you will learn how to use SQL to do these tasks, both inside and outside of Delphi. For each unit inside this chapter, you will look at what the different SQL commands do, what their syntax is, and how they can be used in Delphi. To do this, your teacher will provide you with a database containing information on the 100 most successful movies in history, together with an executable file (MoviesSQL.exe). You will also be given a second database containing healthcare data on carnivores, together with the executable file (CarnivoresSQL.exe).

Illustrated below are some screenshots of the databases that you will use to test your SQL statements.

Figure 4.1: A snippet of the database you will be using (created using MoviesSQL.exe)

Figure 4.2: Opening screen of the database you will be using (created using MoviesSQL.exe)

Figure 4.3: No SQL statement entered for the database you will be using (created using MoviesSQL.exe)

Figure 4.4: Missing semicolon in the database you will be using (created using CarnivoresSQL.exe)

Figure 4.5: Syntax Error in the database you will be using (created using CarnivoresSQL.exe)

Figure 4.6: Missing or misspelled table in the database you will be using (created using CarnivoresSQL.exe)

Figure 4.7: Successly loading data in the database you will be using (created using CarnivoresSQL.exe)

UNIT
 4.1   Select and sort columns

The single most important SQL command you will learn is the SELECT command. This command selects the data from a database, allowing you to either use it in an application or show it to the user. For example, your school might use a database containing all the learners’ information and marks. When they create your report card, they use the SELECT command to select specific information (certain fields) about you (such as your name, surname and marks) and place that in a report.

SORTING DATABASES IN DELPHI

https://www.youtube.com/watch?v=kV2g0yKeB7s

Other examples of SELECT being used include:

social networks selecting messages and status updates relevant to you

games selecting the correct enemies and graphics to show

music applications selecting the correct song from the database.

In this unit, you will learn how to:

select different fields

select distinct values

order selected data

Did you know

It is convention, but not required, to write SQL commands like SELECT and INSERT in uppercase letters. This makes it easier to read SQL statements. The basic structure of a SQL query is always the same with the same order in keywords even if some of the clauses are omitted.

SELECT FIELDS

The SELECT statement can consist of different clauses depending on the information you want to display. A SELECT query doesn’t change the values in the database, it only displays what is in the database.

SELECT syntax

SELECT field_name1, field_name2, …, field_name100 FROM table_name;

The fields are separated with a comma (,) and only the listed fields will be displayed.

Looking at the tblMovies table from your example database, you could select and show the title and income of the tblMovies table. To do this, you would use the following query:

Selecting movies and income

SELECT title, income FROM tblMovies;

Title and income are field names and tblMovies is the name of the table.

Did you know

The asterisk (*) symbol can be used as a wildcard in place of field names to select all the fields.

Example 4.1   Running SQL Queries in the testSQL app

1.Open the folder 04 – Movies; make sure the database Movies.mdb is present; and run the MoviesSQL.exe file by double-clicking it.

2.Enter the following SQL query in the SQL edit box at the top of the interface.

SELECT query

SELECT title, income FROM tblMovies;

3.Click on the [Execute] button under the SQL edit box. You should now see the data from your query displayed in the Query window below.

Figure 4.8: The Query1 table only shows the “title” and “income” fields

Congratulations, you have just created your first SQL query!

Example 4.2   More examples to try

Use the procedure above to run these SQL statements.

Note that once the program is running, you may change the SQL and execute the new statement without restarting the program. Use the Snipping tool to record your results.

Activity 4.1

4.1.1For each of the example queries, how many records were selected?

4.1.2How do the answers compare to the total number of records in the original tables?

4.1.3In your own words describe what the “SELECT fields FROM table” statement do?

4.1.4What will be the difference in output if number 1 in the table above is changed to SELECT release_date, title FROM tblMovies.

4.1.5A group of learners were asked to display the title, genre and income of all the movies in the database, comment on the answers given below. If you think it’s wrong then state why?

a.SELECT FROM tblMovies title, genre, income;

b.SELECT movies, genre, income FROM tblMovies;

c.SELECT title, genre, income, FROM tblMovies;

d.SELECT genre, income, title FROM tblMovies;

4.1.6Using a pen and paper, write SQL queries to select the following data.

a.The generalName and numadults from the tblCarnivores

b.The scientificName and the generalName from the tblCarnivores

c.The visitDate and reasonForVisit from the tblVetVists

d.The generalName, numadults, numYoung and enclosureSize from the tblCarnivores

e.The reasonForVisit and followUp from the tblVetVists

Once you have written down the queries, test the queries by opening the folder testSQL_app; make sure the database Carnivores.mdb is present; and run the CarnivoresSQL.exe file as you did in the previous examples.

DISTINCT

There are many situations where you may want to only select the unique (or distinct) values in a field, that is, no duplicates in the same column (field). Placing the DISTINCT keyword directly after the SELECT keyword informs the DBMS that you want to select all distinct values from a specific column. The syntax for DISTINCT is shown below:

DISTINCT syntax

SELECT DISTINCT field_name FROM table_name;

Example 4.3

If you would like to know what the possible genres of movies in the database is, you only want to see the distinct genres.

SELECT DISTINCT genre FROM tblMovies;

Figure 4.9: A list of distinct genres from the database

In this image, the first row is empty. This is because there are empty (or null) values in the genre column. Note no duplicate values appear in the column.

Example 4.4   More examples to try

Activity 4.2

4.2.1Study table tblCarnivores and table tblVetVisits in the Carnivores database; correct the mistakes in the following SQL statements.

a.SELECT DISTINCT ReasonForVisit FROM Carnivores;

b.SELECT FROM tblVetVisits DISTINCT EnclosureNo;

Did you know

This is very useful if you need the values to fill a combo box with distinct values for user selection.

Using a pen and paper, create queries to select the following data.

4.2.2The unique FamilyNames for the animals in the tblCarnivores.

4.2.3A list of the different reasons for the vet’s visits.

4.2.4A list of distinct enclosures.

Run CarnivoresSQL.exe to test your queries.

4.2.5Based on the query results, how many unique animal family names are there?

Queries like these are often used to create a list of unique items that meet some criteria. For example, you might create a query to identify movie studios that makes low-quality movies or to identify cities closely linked to the film industry. In Unit 4.2, you will learn how to add criteria to your selection.

ORDER BY

In the previous sections, you used the SELECT statement to select data. Now, you will learn how to sort the data that has been selected using the ORDER BY clause. To do this, you use the following syntax:

ORDER BY syntax

SELECT field_name1, field_name2,... FROM table_name

ORDER BY field_name1 order_type;

As you can see from the syntax, the ORDER BY clause introduces a new instruction to your SQL query. The instruction tells the DBMS to organise the results of the query based on the values of a specific field. The specific field must be one or more of the fields in the select-clause. If more, then the fields must be separated by commas. Each field must have its own order type indicated e.g. ORDER BY field1 ASC, field2 DESC;

Example 4.5

If you want to organise your list of movies from the highest income to the lowest income, you could use the following query.

Order from the Highest to the lowest income

SELECT * FROM tblMovies

ORDER BY income DESC;

Figure 4.10: Movies from highest to lowest income

As the image shows, at the end of 2018, Avatar was the highest grossing film, earning more than R39 billion.

Did you notice the word “DESC” at the end of the query? This keyword tells your database to show the results in descending order (from largest to smallest).

There are two order types you can use:

ASC: Short for ascending, this organises your data from the smallest value to the largest value. ASC can be omitted and the data will automatically be order in ascending order.

DESC: Short for descending, this organises your data from the largest value to the smallest value.

Example 4.6   More examples to try

Activity 4.3 Queries using ORDER BY

Using the database Carnivores.mdb create and test the following ordered queries using the testSQL_app for Carnivores:

4.3.1All of the general names of carnivores arranged alphabetically.

4.3.2All of the vet’s visits listed by date starting with the last visit.

4.3.3All of the scientificNames of the Carnivores listed alphabetically.

4.3.4The screenshot shows the fields FamilyNames and ScientificName selected from table tblCarnivores and arranged in a certain order.
Provide the SQL statement that could have produced this output.

Activity 4.4 Select and sort columns

Create the following ordered queries and test in MoviesSQL.exe:

4.4.1Select the income, score and title from the tblMovies table.

4.4.2Select the name, city and province fields from the tblStudios table.

4.4.3Select the title and score fields and order the table based on the score (from highest to lowest) from the tblMovies table.

4.4.4Select the title, income and release_date fields and order the table based on the release date (from oldest to newest) from the tblMovies table.

4.4.5Select all cities from the tblStudios table. Every city must appear only once.

4.4.6Select all movies organised by genre (ascending) and title (ascending).

4.4.7Select all movies organised according to score (highest to lowest) and release date (oldest to the newest).

4.4.8Select all movies organised by genre (ascending) and income (highest to lowest).

4.4.9Select all movies organised by studio_id (from lowest to highest) and score (from highest to the lowest).

UNIT
 4.2   Select columns and rows

In Unit 4.1 we selected columns and displayed all the data available in the selected columns. Now imagine your bank details occupy one row in a database table and your consultant only needs your record. Why fetch the whole table? There must be a way to select just the row that the consultant requires. This is exactly what the function of the WHERE clause is: to select the rows according to some specific conditions. In the case of your bank account, the consultant can select just the row with your details.

The WHERE syntax is given below:

WHERE syntax

SELECT field_names

FROM table_name

WHERE condition1 <Logic Operator > condition 2;

Take note

Every record that meets the conditions in the WHERE clause will be selected.

You use the same syntax as the normal SELECT syntax, but simply add the WHERE clause at the end of it (before the semicolon).

SQL does not register the line breaks in an SQL statement. As such, SELECT, FROM and WHERE can be placed on separate lines to make the statement easier to read. This is especially important with complex queries.

RELATIONAL OPERATORS

The Boolean expressions used with the WHERE command, are similar to those used in Delphi. The following table lists the relational operators that can be used in SQL and shows how it is applied in the WHERE clause.

Table 4.1: Relational operators that can be used in SQL

Note, in SQL, strings must be surrounded by single quotation marks normally used in Delphi.

To see how the operators are used, work through the following examples.

Example 4.7   Creating a query with WHERE and one CONDITION

To create a query with WHERE:

1.Using the database Movies.mdb create and test the following queries with the testSQL_app MoviesSQL.exe:

WHERE query

SELECT title FROM tblMovies WHERE genre = 'Superhero';

This code will show all the movies in the database where the genre is ‘Superhero’. Remember, in SQL, strings must be surrounded by quotation marks.

By clicking on the [Execute] button, you should see the following result:

Example 4.8   More examples to try

Activity 4.5 Select and sort columns

Using the App. in the folder 04 – Movies with the database Movies.mdb; create and test queries that will select the following data:

4.5.1All fields of fantasy movies.

4.5.2The title and date of all movies with an income lower than R12 billion.

4.5.3The genre of all movies with a score higher than or equal to 80.

4.5.4The name, the city and province of all studios in the United States.

4.5.5All movies where the studio_id is 2 organised by genre (ascending) and income (descending).

Using the App in the folder 04 – Carnivores with the database Carnivores.mdb; create and test queries that will select the following data:

4.5.6All species of Carnivores that have an endangered rating of “VU”.

4.5.7All animals kept in enclosure ZC2.

4.5.8All animals where the number of adults is greater than 5.

WILDCARDS AND THE LIKE OPERATOR

Wildcards are special characters that can be used in SQL to represent any character or number of characters (letters or numbers) in a string. These wildcards are used in a WHERE clause in combination with the LIKE operator to find values that match a specific pattern in a field.

LIKE syntax

SELECT field_names FROM table_name

WHERE field_name LIKE 'wildcard_pattern';

Did you know

Visit https://www.w3schools.com/sql/sql_wildcards.asp for a list of wildcards.

There are two wildcard characters that can be used in wildcard patterns:

By using these characters to query your database, you can search for movies starting with the word ‘The’ by placing a percentage symbol (in Delphi) or an asterisk symbol (in Access) after it.

Example 4.9   Creating a query with WHERE and a CONDITION using LIKE with wildcards

Run the MoviesSQL app, enter and test the following queries:

Movies starting with “The”

SELECT * FROM tblMovies

WHERE title LIKE 'The%';

The results can be seen in the figure below.

Figure 4.11: All movies whose names start with “The”

Example 4.10   More examples to try

Activity 4.6

Using the App. in the folder 04 – Movies with the database Movies.mdb; create and test queries that will select the following data:

4.6.1All movies ending with the word ‘out’.

4.6.2All movies where the score starts with an 8 (using an underscore).

4.6.3Spider-Man 2 and Spider-Man 3 (but no other Spider-Man movies).

4.6.4All movies where the name starts with ‘Harry’ and ends with an ‘e’.

4.6.5All movies ending with the word ‘man’.

Using the App. in the folder 04 – Carnivores with the database Carnivores.mdb; create and test queries that will select the following data:

4.6.6All Carnivores where the name ends in ‘mongoose’.

4.6.7All vet visits where the animal had an injury or were injured.

4.6.8All Vet visits where the vet treated ears.

In Delphi the ‘_’character is useful if you want to select data with a specific length, but not specific characters. For example, if you wanted to select mobile phone numbers starting with 072, you could use the wildcard string ‘072_______’ (i.e. 7 underscore characters). This would find all 10-digit telephone numbers starting with 072.

BOOLEAN OPERATORS

As with the Delphi conditional statements, you can use Boolean operators to combine conditions in the WHERE clause. The table below shows three of the most common Boolean operators that can be used in SQL.

Table 4.2: Most common Boolean operators that can be used in SQL

OPERATOR PRECEDENCE

The Boolean operators are executed in the following order:

Parenthesis

Multiplication, division

Subtraction, addition

NOT

AND

OR

To change the order of operations, you will need to use of brackets – much the same way as you do in Mathematics.

Example 4.11   Creating a query with WHERE and compound CONDITIONS

Using the apps in the folder with the databases 04 – Movies Movies.mdb and 04 – Carnivores folder with the database Carnivores.mdb; create and test queries that will select the following data.

SELECT * FROM tblMovies

WHERE (genre = 'Superhero') AND (score > 80);

The query selects all fields of superhero movies where the score is larger than or equal to 80. This will show the following results.

Figure 4.12: All superhero movies with a score above 80

Example 4.12   More examples to try

Activity 4.7

4.7.1The clause WHERE genre LIKE ‘%drama%’ and the clause WHERE (genre = ‘Drama’) OR (genre = ‘Historical drama’) selected the same records.
Will this always be the case? Motivate your answer.

4.7.2Which one of the following WHERE clauses will select action and superhero movies with and income less than R20 000 000 000:

a.WHERE genre = ‘action’ OR genre = ‘superhero’ AND income < 20000000000;

b.WHERE genre = ‘action’ AND genre = ‘superhero’ AND income <= 20000000000;

c.WHERE genre = ‘action’ OR genre = ‘superhero’ AND income < 20000000000;

d.WHERE (genre = ‘action’ OR genre = ‘superhero’) AND income < 20000000000;

4.7.3Complete the following SQL statement:
SELECT * _____ tblMovies ______ genre = ‘Animated’ ______ score > 85;

Using the apps in the folder with the databases 04 – Movies Movies.mdb and 04 – Carnivores folder with the database Carnivores.mdb; create and test queries that will select the following data.

4.7.4All movies with a genre of fantasy or a studio_id equal to 4.

4.7.5All movies that are not superhero or action movies.

4.7.6All action movies with a score less than 50 and those greater than 60.

4.7.7All Carnivores where the adults are over 5 years old and they do not belong to the Viverridae family.

SPECIAL OPERATORS

SQL also includes a few special operators that help you to simplify complex conditions. These operators, their functions and their syntax are shown in the table below.

Table 4.3: Special SQL operators

Example 4.13

BETWEEN example

SELECT * FROM tblMovies

WHERE score BETWEEN 55 AND 65;

This query will result in the following list of movies.

Figure 4.13: Movies with a score between 55 and 65

Take note that each of these movies have a score between 55 and 65, and a few movies (like one of the Harry Potter movies) has a score of exactly 65.

The next example shows how the IN command works.

Example 4.14

IN example

SELECT * FROM tblMovies

WHERE genre IN ('Drama', 'Historical Drama', 'Musical');

This query will create a list of movies where the genre is either “Drama”, “Historical Drama” or “Musical”.

Figure 4.14: All drama, historical drama and musicals

While the same results could be achieved by writing a very long SQL query containing OR operators, the IN operator is a lot easier to write and read.

The next example shows how the IS NULL command works.

Example 4.15

IS NULL operator

SELECT * FROM tblMovies

WHERE genre IS NULL;

This final example query will select all records that do not have a value for the genre field. This can be especially useful if there are important differences between data with values and without values or when you are trying to find and fix any gaps in your data.

Figure 4.15: Movies without a genre

Activity 4.8

Using the apps in the folder with the databases 04 – Movies Movies.mdb and 04 – Carnivores folder with the database Carnivores.mdb; create and test queries that will select the following data.

Use special operators in each.

4.8.1Title and studio_id of all movies with a studio_id of 1, 4, 5 or 9.

4.8.2Title and genre of all movies with an income between R12 billion and R18 billion.

4.8.3Title, date and score of all movies without a release date.

4.8.4Title and genre of all movies that are not action, adventure, fantasy or superhero.

4.8.5Title of all superhero movies with a score between 60 and 80.

4.8.6The generalName and EnclosureSize of all enclosures greater than 30 m² and less than 40 m²

Up to now, dates from the SQL database have only been selected, without being manipulated or used in any conditions. This is because, dates, like strings, have their own rules and functions which need to be used.

DATES IN CONDITIONS

To use a date in a condition, you need to surround the date with the hash (#) symbol. The date does not have to follow a specific format since SQL does a good job of interpreting different dates. This means that, if you want to enter the date 5 January 2018, you could use a number of different formats, including:

#5 January 2018#

#5 Jan 18#

#2018/01/05#

The one date format you should not use is the South African standard format of day/month/year (that is, #05/01/2018#) since SQL may interpret this as month/day/year, giving you incorrect results. Instead, it is safer to use the international date format which goes from the largest unit of time to the smallest (that is, year/month/day).

DATE FUNCTIONS

The following four date functions can also be used when working with dates in your database.

Table 4.4: Date functions

Example 4.16

1.Which movies from your database were released on or after 1 January 2018?

Date condition

SELECT * FROM tblMovies

WHERE release_date >= #2018/01/01#;

Figure 4.16: Movies released since the start of 2018

2.Which movies from the database were released in 2012?

condition using a Date function

SELECT * FROM tblMovies WHERE YEAR(release_date) = 2012;

When using dates in conditional statements, all the Boolean operators, including AND, OR and BETWEEN, can be used.

Activity 4.9

Using the apps in the folder with the databases 04 – Movies Movies.mdb; create and test queries that will select the following data.

4.9.1Show all of the movies released before the start 1994.

4.9.2Show all of the movies released after 10 June 2016.

4.9.3Show all of the movies released between the start of 2000 and the end of 2005.

4.9.4Show all of the movies released on the first day of the month.

4.9.5Show all of the movies released in the last three months of the year.

4.9.6Show all of the movies released in 2009.

Activity 4.10 Select columns and rows

Write down the following queries using pen and paper. Make sure that all calculated fields have field names.

4.10.1Select the title and score of all fantasy movies.

4.10.2Select all movies made by the second studio.

4.10.3Select all movies with an income smaller than R13 billion and a score below 50.

4.10.4Select all movies with a score above 90 that are not animated.

4.10.5Select all, superhero, fantasy and science fiction movies using the IN operator.

4.10.6Select all movies that do not have a release date.

4.10.7Select all movies that contain the word ‘me’.

4.10.8Select all movies released before 1999.

UNIT
 4.3   Calculated columns

When working with datasets, you may wish to perform some calculations on a field before returning the selected data. For example, your Movies database is showing the income of the movies in Rands, even though most of the income was earned in dollars. In situations outside of South Africa, you may wish to first convert the income to dollars before displaying the results. To do this, you need to make use of a calculated field.

CALCULATED FIELDS

https://www.youtube.com/watch?v=MAPYm9I9WQc

A calculated field is displayed in the same was as any other field on your table. The only difference between a normal field and a calculated field is that, as the name suggests, the calculated field is calculated each time you run your query. This means that the data from the calculated field is never stored on the database itself, but instead is recalculated each time.

New words

calculated field – the field that is calculated each time you run your query

To create a calculated field, you enter the calculation into your SQL query as a selected field.

Calculated field syntax

SELECT field_names, calculation

FROM table_name;

The problem with these queries is that the calculated fields do not have a heading. This makes it difficult to interpret the results. To assign a name to the heading, you need to use the AS clause, as shown in the syntax below.

Calculated field syntax with AS

SELECT field_names, calculation AS calculated_field_name

FROM table_name;

Calculated field names cannot be used in the other clauses such as ORDER BY.

SQL calculations use the same mathematical operators (and order of operations – BODMAS) as calculations inside Delphi.

Example 4.17

In the query below, the income is divided by 14 to convert the Rands into Dollars. This is therefore a calculated field. Using the AS command, the calculated field is given the name “dollar_income”.

Calculated field example

SELECT title, income/14 AS dollar_income

FROM tblMovies;

Looking at the image alongside, you will see that there is a new calculated field called dollar_income which shows the original income of the movies divided by 14.

Figure 4.17: Selection with a calculated field

Activity 4.11 Select columns and rows

Using the apps in the folder with the databases 04 – Movies Movies.mdb and 04 – Carnivores folder with the database Carnivores.mdb; create and test queries that will select the following data.

4.11.1All movies showing the movie title and a calculated field called lower_score that subtracts 10 from the score.

4.11.2All movies showing the movie title and a calculated field called income_in_billions that divides the income by 1 000 000 000.

4.11.3All carnivores listing the general name and a calculated field Total_Number_Animals where the family name is ‘Canidae’.

FUNCTIONS IN CALCULATIONS

Previously we used the DATE functions as part of the conditions to select records. Functions can also be used in calculated fields as part of the calculation or to format the result of a calculation.

NUMBER FUNCTIONS

As you start using calculated fields more often, you will notice that these fields are often not formatted correctly.

Example 4.18

All carnivores listing the general name and a calculated field AreaPerAnimal where area_per_animal is less than 6 m² per animal to determine overcrowded enclosures. The calculated field name cannot be used in the WHERE clause, only the actual calculation.

SELECT GeneralName, enclosureSize /(numAdults + numYoung)AS AreaPerAnimal

FROM tblCarnivores

WHERE enclosureSize /(numAdults + numYoung) <6;

To fix this, you can use the four functions shown in the table below as part of your SQL commands:

Replace the SELECT clause above with the ones below.

SELECT GeneralName, STR(enclosureSize /(numAdults + numYoung)) AS

AreaPerAnimal FROM tblCarnivores;

SELECT GeneralName, ROUND(enclosureSize /(numAdults + numYoung),2) AS

AreaPerAnimal FROM tblCarnivores;

SELECT GeneralName, INT(enclosureSize /(numAdults + numYoung)) AS

AreaPerAnimal FROM tblCarnivores;

SELECT GeneralName, FORMAT(enclosureSize /(numAdults + numYoung),'00.00')

AS AreaPerAnimal FROM tblCarnivores;

Take note that the format string from the FORMAT function takes similar string values to Delphi’s. A few FORMAT examples with their outputs are shown below:

FORMAT(release_date, ‘dd mmmm yyyy’) — 15 October 2018

FORMAT(release_date, ‘dd-mm-yy’) — 15-10-18

FORMAT(release_date, ‘dd mmm yyyy hh:nn:ss’) — 15 Oct 2018 12:15:52

FORMAT(income, ‘0.00’) — 15130000000.00

FORMAT(income, ‘0.##’) — 15130000000

FORMAT(income, “Currency”) — R15,130,000,000.00

FORMAT(NumYoung/(NumYoung + NumAdults), ‘0%’) — 84%

Activity 4.12

4.12.1How does the use of INT change the format of the output AreaPerAnimal compared to the original output?

4.12.2Explain the difference in output when ‘00.00’ and ‘##.##’ was used to display AreaPerAnimal.

4.12.3What will the AreaPerAnimal output look like if the format string is ‘#.00’?

Create the following queries with calculated fields using a pen and paper, making sure to give each calculated field a meaningful name.

4.12.4Show all movies’ titles and incomes (converted to an integer).

4.12.5Show all movies’ titles and incomes (rounded to 1 decimal point).

4.12.6Show the movies’ titles and scores (divided by 100 and formatted as a percentage).

4.12.7Show only the release dates of movies (formatted as 15/12/2018).

4.12.8Show the movies’ titles, income (formatted as a currency) and score (divided by 100 and formatted as a percentage).

DATE FUNCTIONS

When creating calculated columns, the names of functions (such as YEAR, MONTH, and DAY) cannot be used as field names. See list of DATE functions in Unit 4.2 on page 127.

Example 4.19

Select all movie titles and the year of their release and display the latest movies first.

SELECT title, YEAR(Release_date) AS YearReleased FROM tblMovies ORDER BY

YEAR(Release_date) DESC;

Activity 4.13

Use MoviesSQL app with the databases “Movies.mdb”; create and test queries that will select the following data.

4.13.1Show all movie titles, release_dates and the year of their release.

4.13.2Show all the movie titles released on the first day of the month.

4.13.3Show all the movie titles released in the last three months of the year.

4.13.4Show all the movie titles released in 2009.

4.13.5Show all the movie titles release dates and their age (in days).

4.13.6Show all the movie titles release dates and their age (in months)

STRING FUNCTIONS

Just like Delphi, SQL allows you to manipulate strings in different ways. The table below shows four different string functions you can use in your SQL queries.

Table 4.5: Four different string functions used in a SQL query

Example 4.20

Show the first five characters of all studios’ names.

SELECT DISTINCT LEFT (name, 5) AS FirstFive FROM tblStudios;

Activity 4.14

Create the following queries using string functions. Make sure to give each calculated field a relevant name.

4.14.1Show the second, third and fourth character of all studios’ cities.

4.14.2Show all movies’ titles, as well as the length of the titles.

4.14.3Show the first and last letter of all the studios’ names.

4.14.4Show the first 2 letters of the enclosure name from the table carnivores as the Enclosure types.

COMBINING STRINGS

The final type of calculated field is created by combining two strings. To do this, you simply add the one string to the second string using the plus (+) operator. This can be combined with functions such as STR and FORMAT to combine numbers or dates with strings.

Example 4.21

Appending the ‘%’ symbol to the score.

Integer score to percentage

SELECT title, STR(score) + '%' AS score_percentage FROM tblMovies;

Activity 4.15

Create the following queries using tblMovies from the Movies.mdb database and the tblVetVisits from the Carnivores.mdb database with a combined string using pen and paper, making sure to give each calculated field a relevant name.

4.15.1Show all movie titles’ and incomes, where the income is converted to billions (i.e. divide by 1 000 000 000) and the string ‘bn’ is added to the end of it.

4.15.2Show the movie studios’ names and locations (as ‘City, Province, Country’).

4.15.3Show all animals where the followUp field has the value false. List the animal name, reason for visit and the Animal_ID, and the string, ‘No follow up required’.

Always remember to consider spaces when combining strings. In the third question, a space is added after each comma.

Activity 4.16 Calculated fields

Write down the following queries using tblMovies from the Movies.mdb database and tblVetVisits from the Carnivores.mdb database using pen and paper. Make sure that all calculated fields have appropriate field names.

4.16.1Show all the movie titles’ release dates and their age (in years).

4.16.2Show month and day of vet’s visits.

4.16.3Select all movies released on the 12th day of the month.

4.16.4Select the movie titles and the income divided by 1.15 to remove VAT. Use an appropriate heading and format.

4.16.5Select all titles and show the year, month and day of release separately from the tblMovies table.

4.16.6Display the first two and last two letters of each movie’s title.

UNIT
 4.4   Aggregate functions

Up to now, all the calculated fields have manipulated the values of an individual record. This is useful if you want to work with the data from records but is not very useful if you want to work with the overall data from the dataset. When you want to answer question about the overall dataset, you need to use the aggregate SQL functions.

AGGREGATE FUNCTION

https://www.youtube.com/watch?v=0s1w50wLC1w

In this section, you will learn about five aggregate functions.

Table 4.6: Five aggregate functions

Each of these functions will complete a calculation on your dataset and return a single value as the result. Since these functions return a single value (rather than a value for each record), they cannot be used in a query with other fields (such as title or genre).

Example 4.22

SELECT title, SUM(income)

AS total_income

FROM tblMovies;

Doing this will result in an error.

Figure 4.18: Aggregate function with a normal field

Here is the syntax for the aggregate function:

syntax for aggregate functions

SELECT Aggregate_function(field_name) AS calculated_name

FROM table_name WHERE condition;

There are hundreds of different uses for the aggregate function. It allows you to answer questions about your entire dataset. Let’s look at some examples:

Example 4.23

To find the sum of the income from all movies, you could do the following query:

SUM

SELECT SUM(income) AS total_income FROM tblMovies;

Example 4.24

To find the average of the income from all movies, you could write the following query:

AVG

SELECT AVG(income) AS avg_income FROM tblMovies;

Example 4.25

To find the lowest score given to an action movie, you could use the following query.

MAX or MIN example

SELECT MIN(score) as lowest_score

FROM tblMovies

WHERE genre = 'Action';

Figure 4.19: Finding the lowest score given to an action movie

Example 4.26

To count the number of superhero movies, you could write the following query:

COUNT with WHERE

SELECT COUNT(genre) AS num_of_superhero

FROM tblMovies

WHERE genre = 'Superhero';

Figure 4.20: Number of superhero movies

Activity 4.17

Using a pen and paper, write down SQL queries to answer the following questions.

4.17.1How many movies have a score below 50?

4.17.2What is the maximum income earned by any movie?

4.17.3What is the minimum score earned by an animated movie?

4.17.4What is the total income earned by all movies containing the words ‘Harry Potter’ in their title?

4.17.5What was the average score received by all movies from studio_id2?

Once complete, use these queries in the MoviesSQL App.

GROUP BY

The problem with aggregate functions is that they only return a single value. If you want to compare the scores obtained by different genres of movies, you would need to write a separate query or create a separate calculated field for each genre. This is not very practical, especially not with large databases. To fix this problem, you can use the GROUP BY clause.

As the name suggests, the GROUP BY clause groups your records according to their value in a specific field. Once this has been done, the aggregate function is applied to each of these groups. The specific field must also be included in the SELECT clause to serve as a label for the groups. The GROUP BY clause comes at the end of the statement after the FROM and WHERE clauses.

The GROUP BY clause uses the following syntax:

GROUP BY syntax

SELECT group_field_name, aggregate_function (value_field_name)

AS result_field_name

FROM table_name

GROUP BY group_field_name;

Example 4.27

Using the GROUP BY clause you could group all of the movies in your database according to genre and then calculate the average score for each genre. Using this syntax, you can view the average score of the different genres of movies in your database with the following query.

GROUP BY example

SELECT genre, AVG(score) AS average_score

FROM tblMovies

GROUP BY genre;

Figure 4.21: Average score of different genres of movies

Activity 4.18

Create queries from tblCarnivores that show the following information.

4.18.1The number of movies on the list, per genre.

4.18.2The total income earned, per studio.

4.18.3The release date of the most recently released movie by each studio.

4.18.4The average score of movies earning more than R14 000 000 000, per genre.

4.18.5The number of movies earning more than R14 000 000 000, per studio.

HAVING

The HAVING clause allows you to add a condition to the grouped results, so that only groups meeting the condition will be shown. This is done by placing the HAVING clause after the GROUP BY clause, as shown in the syntax below.

HAVING syntax

SELECT group_field_name, aggregate_function(value_field_name)

AS result_field_name

FROM table_name

WHERE condition

GROUP BY group_field_name

HAVING group_condition;

Using this syntax, the group condition will check if an aggregate function returns a result that is larger than, smaller than, or equal to a specific value.

Example 4.28

Create a query that shows number of movies per genre where the genre has five or more movies.

HAVING

SELECT genre, COUNT(*) AS num_of_Movies

FROM tblMovies

GROUP BY genre

HAVING COUNT(*) >= 5;

Figure 4.22: Genres with more than 5 movies

The calculated field name cannot be used in the HAVING clause, only the actual calculation.

Activity 4.19

Using the HAVING clause, create queries that show the following information.

4.19.1The number of movies per studio_id, only showing studios with 10 or more movies.

4.19.2The total income per studio where the total income earned is greater than R150 000 000 000.

4.19.3The average score per genre where there are at least 5 movies in the genre.

Take note

In 4.19.3, the ‘group’ condition (HAVING clause) uses a different aggregate function to the aggregate function in the calculated field.

Activity 4.20 Aggregate functions

Write down the following queries using pen and paper.

Make sure that all calculated fields have field names.

Do not show any empty records.

Use appropriate formatting.

4.20.1Find the earliest release date for any movie.

4.20.2Find the average score earned by all movies, rounded to 2 decimals.

4.20.3Find the highest income earned by an animated movie.

4.20.4Find the number of movies with an income larger than R15 billion.

4.20.5Find the average income of movies with a score above 90.

4.20.6Find the average income of movies based on their genre.

4.20.7Find the maximum score of movies based on their studio.

4.20.8Find the maximum score of movies based on their studio. Do not show empty studio_id’s and display the studio names instead of the studio id number.

4.20.9Find the minimum score of movies based on their release year (a calculated column).

4.20.10Show the total income of all genres that have earned more than R100 billion.

4.20.11Show the average score of all studios, rounded to 2 decimals, that have a score above 70.

UNIT
 4.5   Data maintenance

In the previous units, you learned how to use the SELECT statement to select and display data from your database. In this unit, you will learn how you can make changes to a database by using the INSERT INTO, UPDATE and DELETE statements. These statements change the data values in the database.

HOW TO CONNECT AND INTERACT WITH A DATABASE

https://www.youtube.com/watch?v=dwb0wv6IJqA

INSERT INTO

To add additional records to a database, you use the INSERT INTO statement. The syntax for this statement is shown in the code snippet below:

INSERT INTO

INSERT INTO table_name (field_names)

VALUES (new_values);

When using the INSERT INTO statement, the different field names are separated using commas. Similarly, the values added to those fields must also be separated with commas and must be in the same order (and data type) as the fields listed in the previous line/listed field names. The field names in the first bracket can be omitted if values for ALL the fields are inserted. The values must then be in the same order as the field names in the database table.

Example 4.29

Create the following query to add the movie “Aquaman” to the tblMovies table:

INSERT INTO

INSERT INTO tblMovies (id, title, studio_id, income,

release_date, score, genre, studio_ID)

VALUES (101, "Aquaman", 4, 14353000000, #2018/12/21#,

55, “Superhero”,);

When inserting data into a table, pay careful attention to the following:

The field names are spelled correctly.

All field names are separated by a comma.

The values are added in the same order and data type, as the field names.

A value is added to the primary key field.

The value added to the primary key field is unique.

All string values are surrounded by double quotation marks.

All date values are surrounded by the hash (#) symbol.

Activity 4.21

Using a pen and paper, write down the queries needed to add the following data to the correct tables.

4.21.1tblMovies table:

4.21.2tblStudios table

Rather than creating three INSERT INTO statements to add movies to your database, you can use a single query where the new records are separated by commas, as shown in the syntax below.

INSERT INTO syntax (multiple records)

INSERT INTO table_name (field_names)

VALUES (new_values1),

(new_values2),

…,

(new_values3);

As with a single record, you need to make sure that the values of the three records are aligned with the field names. If the record does not have a value for a specific field, a comma should still be added but the space before the next comma can be empty.

Example 4.30

The code below inserts the three movies Bohemian Rhapsody, Fantastic Beast and Ant-Man and the Wasp.

INSERT INTO tblMovies (ID, title, studio_id, income, release_date, score,

genre)

VALUES

(102, "Bohemian Rhapsody", 3, 10846000000, #2018/11/2#, 49, "Musical"),

(103, "Fantastic Beasts: The Crimes of Grindelwald", 4, 9069000000,

#2018/11/16#, 52, "Fantasy"),

(104, "Ant-Man and the Wasp", 2, 8718000000,, 70, "Superhero");

Take note

The release_date of Ant-Man and the Wasp is not known and therefore left blank between the commas.

UPDATE

The UPDATE statement allows you to make permanent changes to a record’s values. This is useful to fix mistakes, update information or add missing information to existing records. However, if used incorrectly, it also has the ability to replace the data from your entire database with garbage, so it is important to be very careful when using the UPDATE statement.

The UPDATE statement has the following syntax:

UPDATE syntax

UPDATE table_name

SET field_name1 = Newvalue, field_name2 = Newvalue, ...

WHERE condition;

As with most SQL queries, you start by selecting the table you will be using. In the second line, you select the fields and set the new values. When setting the values, make sure that the values are in the correct format and follow the field’s rules (such as adding a unique value for the primary key). Finally, in the last line, you use the WHERE statement to select the fields whose values you would like to change.

Example 4.31

In the tblMovies table, the movie ‘Independence Day’ does not have an income or genre. To add values to these fields, you can use the following query:

UPDATE example

UPDATE tblMovies

SET income = 11444000000, genre = 'Science Fiction'

WHERE title = 'Independence Day';

The WHERE clause is incredibly important when updating values, since the changes made by the UPDATE statement will be applied to each record meeting the WHERE clause conditions. If you leave out the WHERE clause, you will update each record in your table and have no way of recovering the old data.

In order to ensure you do not update the incorrect records, follow these guidelines when creating a WHERE clause:

When updating a single record, you can use the primary key to select the correct record.

Before making changes to the data, first create a separate SELECT query to ensure that you are only selecting the appropriate records before using UPDATE statement.

With this information in mind, complete the following activity.

Activity 4.22

Take a look at the image below.

4.22.1Using a pen and paper, write down the queries needed to add the missing information to the films.

Take note

The Lord of the Rings: The Two Towers was released in 2002 on 18 December.

4.22.2Write down an SQL query that you use to increase the income of all films before the year 2000 by 10%.

DELETE FROM

The final SQL statement you will learn about is DELETE FROM, which deletes all records meeting the specified condition. The syntax for DELETE FROM is given in the code snippet below.

DELETE syntax

DELETE FROM table_name

WHERE condition;

As with the UPDATE statement, it is incredibly important that only the records you want to delete meet the WHERE condition, since any other records meeting this condition will also be deleted.

Example 4.32

To delete the 100th record from the tblMovies table, you could use the following query.

DELETE examples

DELETE FROM tblMovies

WHERE id = 100;

Activity 4.23

Using pen and paper, write down queries that will delete the following records:

4.23.1The movie released on 5 May 2017.

4.23.2The movie called ‘Despicable Me 2’

4.23.3All superhero movies.

4.23.4All movies with ‘Star Wars’ in the title.

4.23.5All movies released before the 5th day of the month.

4.23.6All movies with a score below 60 and an income below 14 000 000 000.

Take note

First create a backup for the original disk before you write the update and delete SQLs

Activity 4.24 Data maintenance

Write down the following queries using pen and paper. Make sure that all calculated fields have field names.

4.24.1Add the following South African film studio to the tblStudios table.

4.24.2Add the following films to the tblMovies table using a single query.

4.24.3Update the score of the movie with the ID 106 to 46.

4.24.4Change the genre of all science fiction movies to ‘Science-Fiction’.

4.24.5Delete all movies released before the year 2000 from the tblMovies table.

4.24.6Delete all studios not based in the United States from the tblStudios table.

UNIT
 4.6   Querying two tables

In a relational database, there are many situations where you may want to select data from more than one table at the same time. To do this, you will use a special condition in the WHERE clause to link the two tables.

To create a link between two tables, you use the following syntax.

Join syntax

SELECT table1.field_name, table2.field_name

FROM table1, table2

WHERE table1.foreign_key = table2.primary_key;

As the syntax shows:

The first line selects the field names and the tables from which they must be taken.

The second line lists both the table names in the FROM clause, separating the table names with a comma.

In the WHERE clause, you set the foreign key of one table equal to the primary key of the other table. This condition creates the link between the tables. If this condition is omitted in the WHERE clause your result set will display a lot of duplicate values.

Example 4.33

The query below will show the name of the film as well as the name of the studio that produced it.

Join example

SELECT tblMovies.title, tblStudios.name

FROM tblMovies, tblStudios

WHERE tblMovies.studio_id = tblStudios.studio_id;

Running this query will return the following results:

Figure 4.23: Movie titles and studio names

To filter this selection, you need to add a second condition to the WHERE command using the Boolean operator AND. This is shown in the syntax below.

Join syntax with a condition

SELECT table1.field_name, table2.field_name

FROM table1, table2

WHERE table1.foreign_key = table2.primary_key AND condition;

If you would like to show the movie title and studio names of all movies with a score greater than or equal to 90, you could create the following SQL query.

Join with the condition example

SELECT tblMovies.title, tblStudios.name

FROM tblMovies, tblStudios

WHERE (tblMovies.studio_id = tblStudios.studio_id)

AND (tblMovies.score >= 90)

ORDER BY tblMovies.score;

Figure 4.24: Filtered list of movie titles and studio names

The previous SQL-statement got too long and can be shortened by introducing aliases for the table names. We have used aliases for calculated fields using the AS keyword, similarly we can use the AS keyword to set single-letter as an alias for a table name. Why? Count how many times tblMovies was used in the previous SQL statement and you will see how much shorter the statement will be if tblMovies gets replaced with the letter M. See the code below.

Note: once a table alias is used it must be applied everywhere in the statement.

using table aliases

SELECT M.title, S.name

FROM tblMovies AS M, tblStudios AS S

WHERE (M.studio_id = S.studio_id) AND (M.score >= 90)

ORDER BY M.score;

Activity 4.25

By joining the tblMovies and tblStudios table, create queries that show the following information.

4.25.1The title and date from the tblMovies table and the name from the tblStudios table.

4.25.2The title, score and income from the tblMovies table and the city and province from the tblStudios table.

4.25.3The title, score and income from the tblMovies table and the name from the tblStudios table where the score is below 40.

4.25.4The title from the tblMovies table and the name from the tblStudios table where the city is Los Angeles.

4.25.5The title, score and income from the tblMovies table and the name from the tblStudios table where the income is above 15000000000.

Activity 4.26 Querying two tables

Write down the following queries and run them using the MoviesSQL App. Make sure that all calculated fields have field names.

4.26.1The title, score and movie studio name for all movies.

4.26.2The title, release year and studio city for all movies.

4.26.3All fields from the tblMovies and tblStudios tables.

4.26.4All fields from movies with a score above 90 not made in Hollywood.

4.26.5All superhero movies made by Disney.

4.26.6The average score of all movies, grouped by studio name.

4.26.7Run the CitiesInSA_App.exe, write down queries that will answer the following questions, as well as the answers to these questions.

a.What is the largest city in Mpumalanga?

b.In which province is the city Allemansvlei?

c.What is the longitude and latitude of Kwazulu-Natal’s capital city?

d.In which province is the city Nkwali and what is the province’s population?

e.What are the capital cities and largest cities of all provinces with a population greater than 6 million people?

UNIT
 4.7   Database applications

When creating an application that uses SQL, you should not expect your users to be able to write SQL queries. Instead, you should create applications that automatically builds the SQL queries based on the input from your application’s components.

In this unit, you will create an application that builds these queries automatically using data entered in components.

THE MUSIC SEARCHER APP

For this application, the user interface, database and database connection has already been created. Your task is to build the SQL queries. Open the project in the Music searcher folder to enter the example code and complete Activity 4.27.

Activity 4.27

The user interface is shown below. As the image shows, the user interface contains a grid with a lot of information about different songs from a “music” table. At the bottom of the user interface you can perform different searches.

4.27.1The user must enter an artist name in the artist edit box. When the button [Search Artist] is clicked, the SQL query should return all records for the artist entered.

SQL query: a string in Delphi code

'SELECT * FROM music WHERE artist = "' + sArtist + '"';

NOTE: The double quotation marks around sArtist. This ensures that the value entered is inserted in the SQL statement as a string so that the test artist = “some value” won’t give a data type mismatch error. Double quotation marks are not required if the field in the condition is not a String. For date tests the ‘#’ symbols instead of the double quotes.

If the user enters ‘Casual’ and click Search Artist output will be:

Search Artist event

var

 sSqlQuery : String;

 sArtist: String;

Begin

 sArtist := edtArtist.Text;

 sSqlQuery := 'SELECT * FROM music WHERE artist = "' + sArtist + '"';

 lblSqlQuery.Caption := sSqlQuery;

 dmoMusic.qryMusic.SQL.Text := sSqlQuery;

 dmoMusic.qryMusic.active := true;

end;

4.27.2When the button [Search Album] is clicked a SQL query is needed to search for the any album containing the word entered. Let sAlbum be the variable to hold the user input. Remember the LIKE operator and wildcard ‘%’ must be used.

SQL query: a string in Delphi code

‘SELECT * FROM music WHERE album LIKE "%' + sAlbum + '%"';

Search Album event

var

 sSqlQuery : String;

 sAlbum : String;

begin

 sAlbum := edtAlbum.Text;

 sSqlQuery := 'SELECT * FROM music WHERE album LIKE "%' + sAlbum +

 '%"';

 lblSqlQuery.Caption := sSqlQuery;

 dmoMusic.qryMusic.SQL.Text := sSqlQuery;

 dmoMusic.qryMusic.Active := True;

end;

4.27.3When the button [Search Number] is clicked a SQL query must be built that includes the field selected, the relation operator selected and the value entered in the edit. Let sField, sSymbol and sValue hold the data entered by the user then the SQL statement will be:

SQL query: a string in Delphi code

'SELECT * FROM music WHERE '+ sField + sSymbol + sValue;

If the user is searching for songs released in the year 2010 the output will be:

Search Number event

var

 sSqlQuery : String;

 sField, sSymbol, sValue: String;

Begin

 case cbxField.ItemIndex of

  0: sField := 'year';

  1: sField := 'duration';

  2: sField := 'popularity';

  3: sField := 'loudness';

  4: sField := 'beats_per_minute';

 end;

 case cbxSymbol.ItemIndex of

  0: sSymbol := ' = ';

  1: sSymbol := ' > ';

  2: sSymbol := ' < ';

 end;

 sValue := edtNumber.Text;

 sSqlQuery := 'SELECT * FROM music WHERE '+ sField + sSymbol + sValue;

 lblSqlQuery.Caption := sSqlQuery;

 dmoMusic.qryMusic.SQL.Text := sSqlQuery;

 DmoMusic.qryMusic.active := true;

end;

Did you know

The wildcard characters in Delphi are the percentage symbol (%) for zero to many characters, and the underscore symbol (_) for a single character.

Activity 4.28

4.28.1Provide code for the [Search Song] and [Search Genre] buttons to complete the Music Searcher App. Use wild cards in both so that any song or any genre with the word entered can be found.

4.28.2Provide code for the [Search all] button as follows:

•Create a query that always searches for all fields (using the AND operator).

•Place each of the values from the text boxes into the search query.

•If a textbox does not contain a value, replace this value with a wildcard character that will match all records.

Congratulations, you just created an application that allows users to search through thousands of songs with the click of a button!

Activity 4.29 Using data from components

Open the application saved in your Cities of South Africa folder. Create the following events for this application:

4.29.1An event to insert new cities to your cities table.

4.29.2An event to update the population of your provinces table.

4.29.3An event to delete all cities meeting specific criteria from your cities table.

4.29.4Using this application, complete the following tasks:

a.Add any city of your choice to the database.

b.Update the population of Gauteng to 14 717 000 (the 2018 estimate).

c.Delete the city with the ID of 6800.

CONSOLIDATION ACTIVITY  Chapter 4: Databases and SQL

QUESTION 1

1.1The following database called students shows the details of a number of students attending a university, as well as the students’ average for their course’s subjects. Use this table to answer the questions that follow.

Write a SQL query to:

a.View all the information, sorted from the lowest student number to the highest student number.

b.Show a list of courses without duplicates.

c.Show the name, surname and average of all the students that have the character ‘a’ in either their name or surname.

1.2The following database called Appliances shows the products sold at an appliance and electronics store. It shows the price for these products in Rands and how many units of the product are stored in the store’s warehouse.

Write a SQL query to:

a.view all the products that have a price above R2000?.

b.show the total value of all the stock.

QUESTION 2

For this application, open the project saved in the 04 – Question 2. Save your project.

The Petersen Group CC wants to use software to assist their staff in answering queries from management. In the development of the software, a database called BandB.mdb has been created. The program is incomplete. Your task will be to complete the program that will be used to answer queries from management.

2.1Open the data module and ensure that all the database components have been connected correctly. You should see the following user interface.

Complete the SQL statements for each button as indicated by questions 2.2 to 2.7 that follow:

2.2Complete the code in the [List] button by formulating an SQL statement to display all the fields from tblClients table sorted by Surname and then by FName (first name).

2.3Complete the code in the [Mr Ferreira] button by creating a query that will calculate the total amount owed by Mr Ferreira (ClientNo field equal to 1). This is a calculated answer. The name of the calculated field must be Total Due and the result must be formatted to display the amount with TWO decimal places.

2.4All the bookings for the English football fans have been cancelled. Complete the code for the [English] button by creating a query to delete all English clients (Nationality field equal to English).

2.5The group uses a 25% markup when calculating the selling price. Using the tblOrders table, complete the code for the [Cost] button by creating a query that will list the Date, Category, SellingPrice and Cost (selling price minus the 25% mark-up) for each item ordered by Guiseppe Ferreira (ClientNo field equal to 1). Cost is a calculated field and must be named Cost.

Use the following formula to calculate the cost: Cost = Selling price × 0.75

2.6The Petersen Group has decided that they want to support all tourists by giving them a R5 discount on the selling price of every item they have ordered if the item’s selling price is R30 or more. Complete the code for the [Discount] button by writing a query that will reduce the selling price of the relevant items by R5 in the tblOrders table.

2.7Complete the code for the [Faltemeyer] button by writing a query that will add the following client data to the tblClients table:

Mr Harald Faltemeyer, ID 7407185683074, Swedish

NB: IDNumber field only accepts strings, while the SA field only accepts boolean values.

QUESTION 3

The city of Coruscant has called for the development of an application to keep track of drivers that violate the city’s speed limits.

The TrafficViolations.mdb database contains two tables called tblOwners and tblViolations.

The tblOwners table has the following fields:

The tblViolations table is structured with the following fields:

The OwnerID field has been used to link the two tables. The dates and times captured in the database are stored as strings.

Complete SQL the code to answer QUESTION 3.1. to QUESTION 3.5.

3.1All fields must be displayed for all records in the tblOwners table, sorted from Z to A on surname.

3.2Concatenate the following in a field called Owner for all records in the tblOwners table that were born in or after the year 1993 (also display the date of birth):

•Surname followed by a comma and a space.

•The first letter of the owner name followed by a dot ( . ).

3.3Using the Violations table, display the average speed recorded for each location where speeding violations were captured in a new field called AverageSpeed. The averages need to be rounded off to ONE decimal place.

3.4Use both tables to display the surname of each owner and the number of traffic violations belonging to each owner in a field called ViolationCount.

3.5Two records in the tblOwners table have been captured by error and need to be removed from the database. Remove the records identified by the keys (OwnerID) 2987593875 and 9867398476 from the tblOwners table.

ANNEXURE
AShneiderman’s ‘Eight Golden Rules of Interface Design’

These rules were obtained from the text Designing the User Interface by Ben Shneiderman. Shneiderman proposed this collection of principles that are derived heuristically from experience and applicable in most interactive systems after being properly refined, extended, and interpreted. To improve the usability of an application it is important to have a well designed interface. Shneiderman’s ‘Eight Golden Rules of Interface Design’ are a guide to good interaction design.

1. STRIVE FOR CONSISTENCY.

Consistent sequences of actions should be required in similar situations; identical terminology should be used in prompts, menus, and help screens; and consistent commands should be employed throughout.

2. ENABLE FREQUENT USERS TO USE SHORTCUTS.

As the frequency of use increases, so do the user’s desires to reduce the number of interactions and to increase the pace of interaction. Abbreviations, function keys, hidden commands, and macro facilities are very helpful to an expert user.

3. OFFER INFORMATIVE FEEDBACK.

For every operator action, there should be some system feedback. For frequent and minor actions, the response can be modest, while for infrequent and major actions, the response should be more substantial.

4. DESIGN DIALOG TO YIELD CLOSURE.

Sequences of actions should be organized into groups with a beginning, middle, and end. The informative feedback at the completion of a group of actions gives the operators the satisfaction of accomplishment, a sense of relief, the signal to drop contingency plans and options from their minds, and an indication that the way is clear to prepare for the next group of actions.

5. OFFER SIMPLE ERROR HANDLING.

As much as possible, design the system so the user cannot make a serious error. If an error is made, the system should be able to detect the error and offer simple, comprehensible mechanisms for handling the error.

6. PERMIT EASY REVERSAL OF ACTIONS.

This feature relieves anxiety, since the user knows that errors can be undone; it thus encourages exploration of unfamiliar options. The units of reversibility may be a single action, a data entry, or a complete group of actions.

7. SUPPORT INTERNAL LOCUS OF CONTROL.

Experienced operators strongly desire the sense that they are in charge of the system and that the system responds to their actions. Design the system to make users the initiators of actions rather than the responders.

8. REDUCE SHORTTERM MEMORY LOAD.

The limitation of human information processing in shortterm memory requires that displays be kept simple, multiple page displays be consolidated, windowmotion frequency be reduced, and sufficient training time be allotted for codes, mnemonics, and sequences of actions.

[Source: http://www.cs.utexas.edu/users/almstrum/cs370/elvisino/rules.html, accessed 13 June 2019]

ANNEXURE
B10 usability heuristics for user interface design

By Jakob Nielsen on January 1, 1995

Summary: Jakob Nielsen’s 10 general principles for interaction design. They are called ‘heuristics’ because they are broad rules of thumb and not specific usability uidelines.

VISIBILITY OF SYSTEM STATUS

The system should always keep users informed about what is going on, through appropriate feedback within reasonable time.

MATCH BETWEEN SYSTEM AND THE REAL WORLD

The system should speak the users’ language, with words, phrases and concepts familiar to the user, rather than system-oriented terms. Follow real-world conventions, making information appear in a natural and logical order.

USER CONTROL AND FREEDOM

Users often choose system functions by mistake and will need a clearly marked ‘emergency exit’ to leave the unwanted state without having to go through an extended dialogue. Support undo and redo.

CONSISTENCY AND STANDARDS

Users should not have to wonder whether different words, situations, or actions mean the same thing. Follow platform conventions.

ERROR PREVENTION

Even better than good error messages is a careful design which prevents a problem from occurring in the first place. Either eliminate error-prone conditions or check for them and present users with a confirmation option before they commit to the action.

(Read full article on preventing user errors.)

RECOGNITION RATHER THAN RECALL

Minimize the user’s memory load by making objects, actions, and options visible. The user should not have to remember information from one part of the dialogue to another. Instructions for use of the system should be visible or easily retrievable whenever appropriate. (Read full article on recognition vs. recall in UX.)

FLEXIBILITY AND EFFICIENCY OF USE

Accelerators — unseen by the novice user — may often speed up the interaction for the expert user such that the system can cater to both inexperienced and experienced users. Allow users to tailor frequent actions.

AESTHETIC AND MINIMALIST DESIGN

Dialogues should not contain information which is irrelevant or rarely needed. Every extra unit of information in a dialogue competes with the relevant units of information and diminishes their relative visibility.

HELP USERS RECOGNIZE, DIAGNOSE, AND RECOVER FROM ERRORS

Error messages should be expressed in plain language (no codes), precisely indicate the problem, and constructively suggest a solution.

HELP AND DOCUMENTATION

Even though it is better if the system can be used without documentation, it may be necessary to provide help and documentation. Any such information should be easy to search, focused on the user’s task, list concrete steps to be carried out, and not be too large.

Did you know

For more information on interface design, you can visit these websites:

Bruce ‘Tog’ Tognazzini’s list of basic principles for interface design (https://asktog.com/atc/principles-of-interaction-design/). The list is slightly too long for heuristic evaluation but serves as a useful checklist.

Examples of the 10 heuristics in Web applications (http://designingwebinterfaces.com/6-tips-for-a-great-flex-ux-part-5).

The 10 usability heuristics applied to everyday life (just for fun) (https://www.zenhaiku.com/archives/usability_applied_to_life.html).

Full set of 2,397 usability guidelines (across multiple reports) (https://www.nngroup.com/reports/).

I originally developed the heuristics for heuristic evaluation in collaboration with Rolf Molich in 1990 [Molich and Nielsen 1990; Nielsen and Molich 1990]. I since refined the heuristics based on a factor analysis of 249 usability problems [Nielsen 1994a] to derive a set of heuristics with maximum explanatory power, resulting in this revised set of heuristics [Nielsen 1994b].

REFERENCES

Molich, R., and Nielsen, J. (1990). Improving a human-computer dialogue, Communications of the ACM 33, 3 (March), 338-348.

Nielsen, J., and Molich, R. (1990). Heuristic evaluation of user interfaces, Proc. ACM CHI’90 Conf. (Seattle, WA, 1-5 April), 249-256.

Nielsen, J. (1994a). Enhancing the explanatory power of usability heuristics. Proc. ACM CHI’94 Conf. (Boston, MA, April 24-28), 152-158.

Nielsen, J. (1994b). Heuristic evaluation. In Nielsen, J., and Mack, R.L. (Eds.),

Usability Inspection Methods, John Wiley Sons, New York, NY.

Note: Many people ask if they can use these heuristics in their own work. Yes, but please credit Jakob Nielsen and provide the address for this page [nngroup.com/articles/ten-usability-heuristics] or cite the paper above [Nielsen 1994a]. If you want to print copies of this page or reproduce the content online, however, please see the Copyright link below for details.

(Copyright ) 2005 by Jakob Nielsen. ISSN 1548-5552

(Copyright ) 1998-2017 Nielsen Norman Group, All Rights Reserved.

ANNEXURE
CUsing SQL in Delphi

So far, you have only used SQL in Microsoft Access. In this Annexure, you will learn how to use SQL in Delphi. To do this, you will create a simple database application with a single textbox that you can use to enter SQL commands. Once an SQL command is entered and the Filter button is pressed, the grid will show the filtered database.

To create this application, you will need to use the TADOQuery component which can be found from the dbGo list in the Tool Palette. This component, together with the standard TADOConnection component will be added to a data module, from where they can be used in your application.

TADOQuery component in a dbGo list

Once the database connection has been set up and the TADOQuery component has been added to your data module, you can use the query component to create and SQL query. This is done by adding a string with the query to the SQL.Text property, as will be shown in the examples below.

Example C Creating the SQL connection in Delphi

To create the SQL connection in Delphi:

1.Create a new project and save it in the folder Best Movie Statistics.

2.Create the following user interface.

3.Right click on the executable project file in the Project Manager, select Add New and click on the Other option. This will open the New Items window.

4.Select Data Module and click OK.

5.Save the data module as movies_d in the project folder.

6.Change the data module’s name to dmoMovies.

7.Add a TADOConnection component, a TADOQuery component and a TDataSource component to the data module.

8.Rename the components to conMovies, qryMovies and dsMovies.

9.Select conMovies and click on the three dots button next to its ConnectionString property.

10.Click on the Build button.

11.Select the Microsoft Jet 4.0 OLE DB Provider and click Next.

12.Click on the three dots button, select the Movies.mdb file and click Open.

13.Remove the ‘path’ from the connection string to be able to open the application on other computers

14.Test the connection, then click OK. Click OK again.

15.Change the LoginPrompt property of the database connection to False, and the Connected property to True.

16.Select qryMovies and set its Connection property to conMovies.

17.Select dsMovies and set its DataSet property to qryMovies. This connects the data source to your movies query.

18.Add movies_d to the uses list at the top of your main form.

19.Save and test your application. Your application should now open without a problem.

Congratulations on successfully creating the SQL query connection! As this example shows, creating the connection is very similar to using a TADOTable component to connect to a database. However, as the next example will show, the query component is more flexible as it allows you to enter different SQL queries.

Example C Using SQL in Delphi

To create the SQL connection in Delphi:

1.Create a new project and save it in the folder Best Movie Statistics.

2.Create the following user interface.

3.Right click on the executable project file in the Project Manager, select Add New and click on the Other option. This will open the New Items window.

Did you know

Remember, whenever you access a component from a different form or data module, you always start by first accessing the form or data module.

4.In your event, set the Active property of your qryMovies component to True.

5.Save and test your application by entering a valid SQL query in the text box and the pressing the [Query] button. You should see that data appear in your grid component.

Well done, you now have a Delphi application that can create run SQL queries for you.

While this application is very straightforward, it shows how SQL queries can be run in Delphi. A later unit will show a more complex example of a Delphi application using SQL.

ANNEXURE
DComponent names and description

ANNEXURE
EThe ASCII table

In 1963, the American Standards Association published a table which linked 127 different letters and symbols to numbers. This table was called the ASCII table, which is short for the American Standard Code for Information Interchange.

With ASCII, the first 32 characters in the table are programming characters that cannot be shown on the screen. These include characters like a carriage return character (which shows where a new line should start) and a horizontal tab character which added some horizontal space. The full list of these 32 programming characters is given in the table below.

Table 14.1: The programming characters

The next 95 characters are all visible characters that you can see on the screen.

Table 14.2: Visible characters

The final 127th character is the DELETE character, which is used when something needs to be removed or deleted.

ANNEXURE
FEnrichment

LISTS

Lists work in a similar way to arrays. They allow you to store a large number of elements that can be accessed using an index and which must all be of the same type. However, lists differ from arrays in three important ways:

The size of a list is not fixed.

Lists contain useful, built-in methods that allow you to manipulate the list and the data in the list.

The index of the first list element is always 0.

Lists are created in three steps:

Add the “Generics.Collections” to your application’s “uses” section.

Define the list in the variables section.

Create the list in an event.

Once the “Generics.Collections” library has been added to your project, you can declare your variable in the variable section as follows.

var

 lName : TList<Type>;

Finally, the list needs to be created in an event. This is done with the following code:

lName := TList<Type>.Create;

While it takes a bit of effort to define the list, once it is done you can take advantage of the many advanced methods that are included in the TList object. These methods include:

You have already learned about things such as Double, String, Array, and List. But what if you are dealing with a group of data types that form a single entity, that is, different but related data types that need to be grouped together to represent an entity – something similar to an ID card or an account. In this case it would be useful to consider the data as a special type, something that not only contains the data but also the functions that may be applied to it. We refer to this as encapsulation.

The TList type in Delphi is an example of this type of data. It contains all the essential features to hold items in a list as well as the functions to manage the list. We call the entity TList a class, and instances (objects) of this class will each have its own data plus the given functions to manage the data. (See the simplified Tlist API in Appendix A.)

In the code snippet below, constructors are used to create a list object and button object.

Constructor example

listNames := TList<String>.Create;

btnDynamic := TButton.Create(Self);

In the first line, the Create function is used to create a TList object with the type String and assign it to the TList object “listNames”. In the second line, a TButton object is created and assigned to the object name “btnDynamic”. In this example, the constructor function requires the “Self” parameter. We refer to this method of abstracting the essential common features of an entity to create a data type, as Object Oriented Programming (OOP). In this way, the data structure becomes a class for creating objects (entities) that include both data and functions (code). Programmers can use this to create instances of the entity much like using the TList object to create a number of lists with in your program.

New words

data type – a particular kind of data item, as defined by the value it can take, the programming language used, or the operations that can be performed on it.

abstracting – to create an object and assign it to an object name

In the next example we will demonstrate how useful it is to have a list of objects that can be filtered to get information.

Example F.1 Adding data to an array

Alternative: Using TStringList to extract data form a delimited string

You can use a TStringList to extract the parts between the delimiters from a delimited string. In this example we do this in the inner loop. You can read the data in a loop in exactly the same way as the example above. However sometimes when you have a small file and you may want to use the method below, it puts the complete file into a TStringList you can then use a second TStringList to remove the delimiters as illustrated.

In this example we do not need to specify the delimiter as it defaults to ‘,’.

Var

   sData: String;

   i, j: Integer;

   inputLines: TStringList;

   oneLine: TStringList;

   // aData : Array[1..5, 1..7] of Integer; (Global variable)

begin

   inputLines := TStringList.Create;

   oneLine := TStringList.Create;

   inputLines.LoadFromFile('marks.csv'); //put file into inputLines

   For i := 1 To 5 Do

   Begin

      oneLine.delimitedText := inputList[i]; // default delimiter is ','

      For j := 1 To 7 Do

       aData[i,j] := strtoint(oneLine[j-1]);//array 1-7 stringlist 0-6

   End;

Displaying the array data

CSVIntoArray;

for i := 1 to Length(aData) do

begin

   sOutput := '';

   for j := 1 to Length(aData[1]) do

      sOutput := sOutput + IntToStr(aData[i, j]) + #09;

   lbxResults.Items.Add(sOutput);

end;

This code loops through and creates rows of data separated by a tab space character. Once a single row has been saved in the sOutput variable, it is added to the listbox before a new row is started.

Take note, since you are looping through the rows of data, you need to place the row variable (“i”) in the outer-loop and the column variable (“j”) in the inner-loop.

Example F.2 Monte Carlo simulator

A Monte Carlo simulation is a mathematical tool used to determine how likely certain outcomes are in uncertain situations. Rather than trying to use complex mathematics to calculate an exact probability, mathematicians create a computer simulation with all the relevant variables and then run hundreds of thousands of tests, recording the result of each test. After these tests have been run, the mathematicians can look at the results to see how often each outcome occurred.

The Monte Carlo simulation is named after the suburb Monte Carlo in the city of Monaco, which is famous for having one of the most prestigious casinos in the world. The name Monte Carlo was chosen because Monte Carlo simulations are great at analysing gambling probabilities and odds.

Figure 14.2: The Monte Carlo Casino in Monaco (photo by Bohyunlee)

In Blackjack, players with a hand value larger than 16 are likely to win, while players with a hand value less than 16 are likely to lose (unless both cards have the same value).

Did you know

Many of the James Bond films, including Casino Royale, are set at the Monte Carlo Casino.

For this example, you need to build a Monte Carlo simulator that will analyse the probabilities in Blackjack. Specifically, you want to know how often someone will pick up one of the following combinations of cards:

An ace and a jack (called Blackjack).

Two cards with the same number. Two cards with a combined value of exactly 21.

Two cards with a combined value larger than 16 and smaller than 21.

Two cards with a combined value of exactly 16.

Two cards with a combined value of under 16.

The value of cards is equal to the number on the card (so a two of hearts has a value of 2), except for the king, queen and jack which have a value of 10, and an ace which has a value of 11.

To create the Monte Carlo simulator, you will need to:

1.Create a user interface.

2.Create a virtual deck of cards.

3.Create a loop that will run the simulations a user-determined number of times.

4.Select two random cards from the deck (without being able to select the same card twice).

5.Calculate the combined value of the cards.

6.Record the category of result (blackjack, smaller than 16, and so forth).

7.Display the results.

Open the project in the F1 – Monte Carlo simulator folder and add code as described in the solution below:

Solution

To create this program, you needed to complete a number of tasks. For each of these tasks, there is more than one possible solution, so the code below will simply show one way to create the Monte Carlo simulator.

The first step is to create the user interface.

The user interface below simply shows the different criteria as well as the number of draws that matched these criteria.

Create a deck of cards.

In this solution, a list is used to create a deck of cards since the list functions could be useful to add or delete cards from the list. However, an array can also be used.

Creating the deck of cards

lCards := TList<Integer>.Create;

for i := 1 to 52 do

begin

   iCardValue := i mod 13;

   if iCardValue = 0 then

     iCardValue := 13;

   lCards.Add(iCardValue)

end;

The FOR-loop runs 52 times, once for each card in the deck. By calculating the remainder of i mod 13, the loop ensures that all the values are between 1 to 13 (ace to king). These values are then added to the iCards list.

Create the loop that will run a specific number of times. This loop is shown below.

Game loop

iSimulations := StrToInt(edtNumberOfGames.Text);

for j := 1 to iSimulations do

begin

   // Select two random cards

   // Calculate the value of the two cards

   // Categorise the cards based on their values

end;

Once you have your game loop, select two cards inside the loop. The code below shows how these cards are selected.

Selecting two cards

iRandom1 := Random(52);

iRandom2 := Random(52);

while iRandom1 = iRandom2 do

   iRandom2 := Random(52);

aSelectedCard[1] := lCards[iRandom1];

aSelectedCard[2] := lCards[iRandom2];

INTRODUCTION TO THE MONTE CARLO SIMULATION

https://www.youtube.com/watch?v=pDExObTMHTs

The code starts by selecting two random numbers between 0 and 51. These numbers will be used to draw cards from the list of cards. Since the same card cannot be drawn twice, a WHILE-DO loop will repeat until iRandom1 and iRandom2 have different values. Once you have two unique integers, they are used to select two cards from the iCards list and assign it to the aSelectedCard[1] and aSelectedCard[2] array elements.

The cards selected at this stage have a value of 1 (ace) to 13 (king). However, not all cards have the same value as their number. Specifically, cards with the number 11, 12 and 13 (jack, queen and king) have a value of 10, while cards with the number 1 (ace) have a value of 11.

The next step is to store the values of the two selected cards.

Selecting two cards

iRandom1 := Random(52);

iRandom2 := Random(52);

while iRandom1 = iRandom2 do

   iRandom2 := Random(52);

aSelectedCard[1] := lCards[iRandom1];

aSelectedCard[2] := lCards[iRandom2];

In this code, a FOR-loop is used to assign values to both the selected cards. Inside the FOR-loop, a case statement looks at the selected card’s number and assigns an appropriate value to the aCardValue[1] and aCardValue[2] array elements. Once the values for the two cards has been assigned, they can be added together to obtain the total value.

At this point, you know the value of both the cards as well as the specific cards that were selected. The last two steps is to assign the cards to a category before displaying the values for the categories in your UI.

Categorising the cards

Case iTotalValue of

       1..15    : iBelow16 := iBelow16 + 1;

       16        : iExactly16 := iExactly16 + 1;

       17..20  : iAbove16 := iAbove16 + 1;

       21        : iExactly21 := iExactly21 + 1;

End;

// Checks if the cards have the same number

if aSelectedCard[1] = aSelectedCard[2] then

  iSameNumber := iSameNumber + 1;

// Checks if an ace and jack were selected

if ((aSelectedCard[1] = 1) and (aSelectedCard[2] = 11)) or

((aSelectedCard[2] = 1) and (aSelectedCard[1] = 11)) then

  iBlackJack := iBlackJack + 1;

The CASE statement at the start of the code snippet looks at the total card value and increments the category variable based on this value. Once this is done, a separate IF-THEN statement is used to check if the two selected cards have the same number. Finally, an IF-THEN statement is used to check if one of the cards is an ace (1) and the other a jack (11) in order to increment the iBlackJack variable. This is the last step that occurs inside the loop.

Once outside of the loop, all the values are converted to strings and written to the labels.

Writing the values

lblTotalGames.Caption := IntToStr(iSimulations);

lblExactly21.Caption := IntToStr(iExactly21);

lblAbove16.Caption := IntToStr(iAbove16);

lblExactly16.Caption := IntToStr(iExactly16);

lblBelow16.Caption := IntToStr(iBelow16);

lblBlackJack.Caption := IntToStr(iBlackJack);

lblSameNumber.Caption := IntToStr(iSameNumber);

By running this application, you will see that your chances of picking up a BlackJack is roughly 1.2% (or 12 000 / 1 000 000)!

Glossary

A

abstracting to create an object and assign it to an object name

access specifiers is a defining code element that can be determine which elements of a program are allowed to access a specific variable or other piece of data

append to open an existing file for writing, set the file pointer to the end of the file and allows you to add data to the file

append to add an empty row to the end of your database table

array is a data structure that store a set values (elements) of the same type liked to a single variable name

assume supposed to be the case, without proof

attributes the data fields of the class

B

behaviour the code that provides the interaction with the attributes

binary search is an algorithm used in computer science to locate a specified value (key) within an array

bubble sort to compare adjacent elements

C

Caesar cipher a substitution cipher on which each letter in plaintext is ‘shifted’ a certain number of places down the alphabet

calculated field the field that is calculated each time you run your query

Ceil to round a real number up to the highest integer value

CHR to return the corresponding character of an ASCII code

circular dependency to cause an application to crash

CompareText to compare two strings for equality, ignoring case

concatenates to joins strings together into one result string

conditional to put its condition first before executing the looping back

D

data module a sealed, removable storage module containing magnetic disks and their associated access arms and read/write heads

data type a particular kind of data item, as defined by the value it can take, the programming language used, or the operations that can be performed on it.

DEC to decrement an ordinal type variable

decremental the act or process of decreasing or becoming gradually less

Delete to delete a number of characters from a string starting from a start position

delimiters to show the start and ends of individual pieces of data

dynamic instantiation when a component or object is created during run-time

E

encapsulation the grouping of attributes and behaviour in one entity

encrypted message to encode information to prevent anyone other than its intended recipient from viewing it

end of file <eof> to indicate the end of a file when the file is saved

end of line <eoln> to indicate the end of the line when the [Enter] button is pressed

Entity Relationship Diagram to show the relationships of entity sets stored in a database

event an occurrence of something

exception is generally an error condition or event that interrupts the flow of your program

Exception Handling a way to prevent a program from crashing when a file does not exist

F

FileExists to determine whether a file exists or not

first argument is a string that holds instructions for formatting

Floor to round a real number down to the lowest integer value

formal parameter to declare variable(s) next to the procedure name

Frac to return the decimal part of a real number

function the operation of something in a particular way

G

global is a programming language construct, a variable that is declared outside and function and is accessible to all the functions throughout the program

H

homogenous elements of the same type

I

INC to increment the ordinal type variable passed to it

incremental relating to or denoting an increase or addition

independent to run on its own

index the position of the element in an array

inner loop the inner part of a nested loop

Insert to insert one string into another string

insert to add an empty row at the current position of your database table

instance an example or single occurrence of something

instances of the class a data type that describes the attributes and behaviour of the object to be model electronically

instantiate represent as or by an instant

L

linear search is a process that checks every element in the list sequentially until the desired element is found

local variable variables that have a local scope

logical file is a variable (in RAM) that points to the physical file on your storage medium

LowerCase to converts uppercase characters in a string to lowercase

Luhn algorithm is a simple checksum formula used t validate a variety of identification numbers, such as credit card numbers, IMEI numbers, and Canadian Social Insurance Numbers

M

method overloading to have more than one method with the same name

method overloading to have more than one method with the same name

method signature is the number of arguments and their data type

method signature to name a method and its formal parameters list

methods predefined instructions

N

naming convention to name things (generally agreed scheme)

non-local is a variable that is not defined within the local scope

null to represent an empty value

O

Object-Oriented Programming (OOP) refers to a type of computer programming (software design) in which programmers define not only the data type of a data structure, but also the types of operations (functions) that can be applied to the data structure

ORD to return the ordinal value of a character

outer loop the outer part of a nested loop

P

physical file to name an external file name found on a storage device and contains the actual data

Pi is a predefined constant that returns a real number giving a useful approximation of the value Pi

Pos to return to the start position of one string within another string as an integer

post command to permanently save the values to the database table

POWER to raise a base to a power and returns a real answer

procedure an official way of doing something

properties the components or building blocks

R

Random to generate a random number from 0 to less than 1

RandomRange to generate a random integer number from Num1 to one less than Num2

related information information belonging in the same group

relational database a database structured to recognise relations between stored items of information

reusability is an important OOP principle

Round to round a real number to an integer value

S

second argument holds the values that needs to be converted into a formatted string

selection sort to select the element that should go in each array position either in ascending or descending order sequence

SETLENGTH to change the size of a string

sorted to sort an element in numerical order

SQRT to return the square root of a number

step through to step through means that you are working through a program line by line

STR to convert an integer or real number into a string, with optional basic formatting

T

Trunc to remove or chop off the decimal part of the real number. It returns an integer after the truncation

U

unambiguous not open to more than one interpretation

Upcase to convert a single letter character to uppercase

UpperCase to converts lowercase characters in a string to uppercase

user-defined is methods written by programmers themselves

V

VAL to convert a string to a numeric value

validate to try and lessen the number of errors during the process of data input in programming

value parameter when a procedure is called, memory locations are created for each of the formal parameters and the values of the arguments are assigned to the corresponding formal parameters. Changes made to a value parameter will not affect its corresponding argument. When the procedure is exited, the memory locations of the formal parameters ‘die’ away

ANNEXURE
                       Notes