Topicm32121e353820d760_1528449000663_0Topic

Operations on a database

Levelm32121e353820d760_1528449084556_0Level

Third

Core curriculumm32121e353820d760_1528449076687_0Core curriculum

Secondary school

II. Programming and solving problems with the use of computer and other digital devices.

Basic range. The student:

3) prepares solutions for solving problems using selected applications:

d) searches for information using a database based on at least two tables, defines relations, applies filtering, formulates queries, creates and modifies forms, prints reports

Extended  range. The student meets the requirements specified for the basic  range, in addition:

4) uses selected advanced applications to prepare solutions for complex problems:

d) designs and creates a relational database composed of many tables and also a network database application for data related to a solving problem, formulates queries, creates and modifies forms and reports, uses SQL language to search for information in the database and to modify it, cares for the integrity of data, security and data protection in the database,

Timingm32121e353820d760_1528449068082_0Timing

45 minutes

General objectivem32121e353820d760_1528449523725_0General objective

Designing databases.

Specific objectivesm32121e353820d760_1528449552113_0Specific objectives

1. Describing a Database Management System.

Learning outcomesm32121e353820d760_1528450430307_0Learning outcomes

The student:

- describes a Database Management System,

- performs operations on databases.

Methodsm32121e353820d760_1528449534267_0Methods

1. Sentence completion.

2. Learning through observation.

Forms of workm32121e353820d760_1528449514617_0Forms of work

1. Individual work.

2. Class work.

Lesson stages

Introductionm32121e353820d760_1528450127855_0Introduction

By using the sentence completion method, the students revise the basic terms related to databases.

Data are stored in _______ (tables).

A database can contain multiple _______ (tables).

The table must have its unique _______ (name in the database).

Columns in a spreadsheet correspond to _______ (fields) in a table and rows correspond to _______ (records).

All data values are based on simple _______ (data types).

A unique field throughout a table that clearly identifies a given record is called a _______ (primary key).

Procedurem32121e353820d760_1528446435040_0Procedure

[Interactive graphics]

A Database Management System (DBMS) is a programme or an entire computer system used for managing a database. Apart from ensuring access to data in the database, it enables performing many operations that are necessary to ensure the correct operation of the database, such as: administrationadministrationadministration, ensuring security, optimisingoptimisingoptimising, reportingreportingreporting or a range of additional mechanisms.

Task 1

On the internet, find popular Database Management Systems.

Structured Query Language (SQL) is a language used for creatingcreatingcreating and modifyingmodifyingmodifying databases and for insertinginsertinginserting and retrieving data from databases. It enables performing the following basic operations:

- creating and modifying databases,
- creating, modifying and deletingdeletingdeleting tables,
- inserting, modifying and deleting data in tables,
- searchingsearchingsearching for data in tables (queries).

Most database programmes installed on desktop computers include mechanisms that enable performing these operations without knowledge of the SQL syntaxSQL syntaxSQL syntax.

Inserting data:

The easiest method of insertinginsertinginserting data into a database is importing the data from the existing data sources directly to tables in the database.

The INSERT statement in SQL is used to insert data into a database table.

Task 2

Download the file and import the file with the data to a database.

[Attachment 1]

[Illustration 1]

In the resulting table you will find the information about the books: their author, title and the year of their first edition. Add five records with your favourite books.

ModifyingmodifyingModifying data:

Modifying data refers to changing the content of fields in existing records. To modify (update) data in SQL you must use the UPDATE statement.

Task 3

In the table from the previous task change the data in a selected record.

Deleting data:

DeletingdeletingDeleting data refers to removing an entire record or a group of records in a table. To delete data in SQL you must use the DELETE statement.

Task 4

In the table from the previous task, delete two selected records.

One of the operations performed the most frequently on databases is searchingsearchingsearching for data. A statement in SQL that realises queries to a database is the SELECT statement.

Task 5

In the table from the previous task, find the books meeting the following criteria:

- issued after 2010,
- the Author field begins with the letter ‘C’,
- in the Title field, the second letter is ‘i’.

If you have problems with the task, see the solution.

[Illustration 2]

Data in the form of records are entered into the tables of a database in an unorganized way. To order them, we usually sort a selected column of the table in an ascending or descending order.

A statement in SQL that realises sorting tables is the SELECT statement together with the ORDER BY statement. It performs sorting data, while its parameters defining the sort order are: ASC – in an ascending order (alphabetically) and DESC – in a descending order.m32121e353820d760_1527752263647_0Data in the form of records are entered into the tables of a database in an unorganized way. To order them, we usually sort a selected column of the table in an ascending or descending order.

A statement in SQL that realises sorting tables is the SELECT statement together with the ORDER BY statement. It performs sorting data, while its parameters defining the sort order are: ASC – in an ascending order (alphabetically) and DESC – in a descending order.

Task

In the table from the previous task, sort the books from the one issued the least recently.

If you have problems with the task, see the solution.

[Illustration 3]

Lesson summarym32121e353820d760_1528450119332_0Lesson summary

Databases are used for storing truly huge amounts of data. Thanks to them you can easily search for needed information and modify it.m32121e353820d760_1527752256679_0Databases are used for storing truly huge amounts of data. Thanks to them you can easily search for needed information and modify it.

Selected words and expressions used in the lesson plan

administrationadministrationadministration

creatingcreatingcreating

Database Management System, DBMSDatabase Management System, DBMSDatabase Management System, DBMS

deletingdeletingdeleting

in a descending orderin a descending orderin a descending order

in an ascending orderin an ascending orderin an ascending order

insertinginsertinginserting

modifyingmodifyingmodifying

optimisingoptimisingoptimising

reportingreportingreporting

searchingsearchingsearching

sortingsortingsorting

SQL syntaxSQL syntaxSQL syntax