Computer science lesson in 9th grade. Topic: “Basic data types and formats. Links" lesson plan in computer science and ICT (9th grade) on the topic

Practical work on databases, methodological development in computer science and ICT (grade 9) on the topic

Practical work on MS Access

Practical work No. 1. “Introduction to DBMS. Creating and editing a database."

• Every table contains information about some real system (process) and, therefore, is its information model;
• Every entry in the table is information about a specific object (event) of a given system;
• The field value in each record is a certain characteristic (property, attribute) of the object;
• Database objects are: tables, queries, forms, reports, macros and modules;
• A master key is a field or collection of fields that uniquely identifies a record in a table.

Task 1. Create a database “Picture Gallery” containing information about the paintings of outstanding artists.

Progress:

1. Let's create a new database (file - create - new database - file name "galer.mdb" - create).
2. Create a table (table - create - constructor - OK).
3. Determine the fields included in the table

Set the key field (select the “picture title” and select the key field command in the context menu).

1. Save the table under the name “gallery” (file - save as... - gallery).
2. Fill the table with data, first opening it with the command (table - gallery - open).

Close the table.

Task 2. Edit the database

1. Design the table to your taste (text font, size, color).
2. Change the width of each column to fit the data width and the height of the rows (Format - Column Width - Fit Data Width).
3. Add a “canvas size” field to the table (insert - column - select rename column from the context menu).
4. Copy the first entry in place of the fifth using the context menu, and change the data in it to the following:

1. Rename the “Painting Title” field to “Canvas”.
2. Delete the last entry using the context menu.
3. Add the following entry at the end of the table:

1. Replace in the entire table “I.I. Shishkin" to "Ivan Shishkin" (Edit - replace - enter replacement format).

Task 3. Complete individual work No. 1 on page 81 (workshop problem 2).

Practical work No. 2. “Generating queries to select, replace and delete a record using logical operations, sorting records.”

• You can search for data in the database using a filter or a query;
• Filters and queries allow you to select records that meet specified conditions;
• A query is an independent database object, and a filter is tied to a specific table. Simple queries contain conditions for selecting records for only one field (, =, , , ), while complex queries contain several conditions for different fields (and, or, not);
• The ordering of records is called sorting. A sort key is a field whose value is used to sort records. The sort order has two options: ascending and descending values.

Task 1. Creating queries and performing sorting.

Progress:

1. Download Access and open the database "galer.mdb".
2. Sort the entries by the “Artists” field alphabetically (select the “Artist” field - entries - sort - sort ascending)
3. Display records of paintings created no later than 1880. To do this, follow the sequence of actions:

Method 1: records - filter - change filter - enter the condition in the “Year of creation” field

Method 2: request - creating a request in design mode - add the “Gallery” table, close, drag all fields into the request form into the “field” line and set the selection condition in the “Year of creation” field

1. Display records of Shishkin’s paintings painted before 1890 (using method 2, enter the selection condition “Shishkin” in the “Artist” field of the request form, and enter the selection condition in the “Year of Creation” field
2. Display only the “Canvas” and “Artist” fields for all records, with the year of creation before 1890 (using method 2, in the request form, in the “Display on screen” line, check the “Canvas” and “Artist” fields and in In the “Year of creation” field, enter the selection condition
3. Display records of paintings painted by Shishkin and Vasiliev before 1880 (using method 2, enter the condition “Ivan Shishkin” in the “Artist” field of the request form, and “F. Vasiliev” in the “or” line, in the “Year” field creation" enter the selection condition
4. Delete all records about F. Vasiliev’s paintings from the database (using method 2, in the main menu “Request” - delete and enter “F. Vasiliev” in the “Selection condition” line in the “Artist” field).
5. Sort the records in the database by the “Year of Creation” field in descending order.

Task 2. Complete individual work No. 4 on page 92 (workshop problem 2).

Practical work No. 3. “Generating reports and forms using a wizard.”

• The form displays one record in a user-friendly manner;
• The form can contain various control elements (text fields, buttons, switches), as well as labels;
• For beautiful printing of documents, it is advisable to use reports. Reports are arbitrary database objects and are created based on tables, forms and queries.

Task 1. Let’s create a form in the form of a table for viewing and entering data, containing the fields “Canvas”, “Artist” and “year of creation” according to the table “Gallery”.

Progress:

1. Download Access and open the database "galer.mdb".
2. Let's create a form for the "Gallery" table (Select the Forms object - create a form using the wizard - create - in the "Create Forms" window, select the "Gallery" table, and the fields "Canvas", "Artist", "year of creation" - select the appearance forms – tabular – design style – Rice paper – set the name of the form “Pictures” and the option to open the form for viewing and entering data.
3. Let's enter the data into the form

Task 2. Using the wizard, create yourself two different types of forms for the “Gallery” table.

Task 3. Let’s create a report on the “Gallery” table in the form of a table for viewing and entering data, containing the fields “Canvas” and “Artist”.

Progress:

Let's create a report (Select the Report object - create a report using the wizard - create - in the "Create reports" window, select the "Gallery" table, and the "Canvas" and "Artist" fields - - select the grouping level - - and sort in ascending order by the field " artist" - - select the report layout type - column, sheet orientation - landscape - - select the required design style - business - - set the report name "Pictures" and the option for further actions - view the report - ).

Task 4. Using the wizard, create two types of reports for the “Gallery” table yourself.

Task 5. Complete individual work No. 3 on page 85 (workshop problem 2).

Practical work No. 4. “Designing the structure of a multi-table database and its creation using a DBMS.”

• Multi-table databases are used to avoid slowdown of the processing procedure due to redundancy of information and duplication of various records in many fields;
• Data normalization is the process of reducing tables to third normal form:

1. All data that is planned to be included in the database is presented in tabular form (first normal form);
2. A main key or two is defined and all fields that do not depend on it are separated into a separate table (second normal form);
3. They select fields that partially depend on the main key and create the following table, establishing a relationship between the tables - this ensures the integrity of the database (third normal form).

Task 1. Let's create a database that will store students' exam grades.

Progress:

You can get by with one table, which includes the fields “Last name”, “First name”, “Subject”, “Teacher”, “Grade”. But then a lot of data will be repeated.

Therefore, create three tables - “Students”, “Subjects”, “Grades” - and fill them out. In the first two tables, the first field will be of type counter, and the rest will be of text type. In the third table, the first field will be of counter type, and the rest will be of numeric type.

Table “Students”: Table “Subjects”:

1. Click on the Data Schema icon. You'll see the tables we created and the relationships Access created. It automatically associated fields with the same names and compatible types. In other cases, this can be done manually, using the mouse - just drag one field onto another.
2. Please note that grading is not convenient. To give Volkov an “excellent” grade in algebra, you need to remember the student and subject codes. Fortunately, Access can operate with codes and provide information in text form. Let's do this for items.

Select the “Item Code” field in the “Grades” table and specify the Substitution Wizard type. As the data source table, select the “Items” table and the “Item Code” and “Name” fields. The names of items will now be visible in table mode.

1. Do the same for the students' last names. Now make sure that the substitution is performed from two fields, that is, the last name and first name are displayed. To do this, select the “Student Code” field, the Substitution tab, then select Row Source and click on the button with the ellipsis on the right. The Query Builder will open, working with it in the same way as working with a regular query. Instead of the Last Name field, enter the string [Last Name]&" "&[First Name]. Now both the student's last name and first name will be visible.
2. Add information about two or three more students to the “Students” table. Go to the "Ratings" table. Please note that information about added students is at the end of the list. To sort the list alphabetically, call the query builder for the Student ID field and set the sort to ascending. Do the same for the Item Code field.
3. The “Evaluation Code” field is not interesting to us (unlike Access). Select this column in Datasheet view and choose Format, Column Width. Set the column width to zero. The same result can be achieved by dragging the right border of the column towards the left.
4. Note that Access allows you to store multiple records of the same student taking the same course. Let’s make the “student-subject” set unique. To do this, in design mode, set the key field option for these fields. This can be done using the Edit menu or the toolbar.
5. Change the names of the Student Code and Subject Code fields. To do this, select the Signature line on the general tab. Now the “Ratings” table will look like this:

1. Make it so that in the “Rating” field, instead of numbers, you can write “excellent”, “good”, etc. To do this, create a separate table and use the Substitution Wizard. When creating a table, please note that there are four rating designations: 1 - “excellent”, 2 - “good”, 3 - “satisfactory”, 4 - “unsatisfactory”. Connect:

10. Fill out the “Grades” table. 11. Save the results of your work in the school.mdb file

Test lesson on DBMS.

Task 1. Create a tabular database with the table name Phone directory"""""»" based on the proposed structure.

Task 2. Fill in the table with data (at least 10).

Task 3. Create a database query to select records about subscribers who have a phone number starting with the numbers 46.

Task 4. Sort the data so that the records in the table are arranged in alphabetical order by the field last name .”

Task 5. Based on the table “telephone directory”, build a form containing the fields “last name” and “date of birth”.

Task 6. Prepare a report with the title address book using the table telephone directory. Report parameters: has the form of a table, includes the fields “last name”, “first name” and “home address”, last names are arranged in alphabetical order, sheet orientation is landscape.

Task 7. Determine how many female subscribers are in your directory.

Additional task: Complete individual work No. 8 on page 107 (workshop problem 2).

Databases and database management systems. Lecture notes on the discipline

Lecture notes on the discipline

Functions performed by the DBMS

A database management system (DBMS) is a system software that contains a set of language and software tools designed for creating, maintaining and sharing databases among many users and applications.

Typically a DBMS performs the following functions:

1.Centralized data definition.

The DBMS contains tools for describing the database schema. The database schema or metadata is stored in what is called a data dictionary . The data dictionary subsystem monitors the safety of the definitions of all data elements during various user manipulations with the data.

2.Data protection and ensuring its integrity.

-The DBMS contains tools for identifying users and authorizing access to data (users use only authorized operations with data).

-Integrity and consistency are ensured by monitoring the restrictions imposed on the data, as well as by creating database backups. Descriptions of restrictions on the values ​​of data elements are stored in the data dictionary. Backup programs automatically record changes made to the database and provide the ability to restore the current state of the database in the event of system failures.

3. Simultaneous access to data for several users.

One of the main functions of a DBMS is providing access to data and maintaining a database. Centralized data storage means there is a high likelihood that two or more users will need the same data at the same time. If one user accesses the data while another makes changes to it at the same time, conflicting data will be obtained. DBMSs have complex mechanisms for blocking updated data from access by other users. Parallel queries on the same data are usually executed sequentially.

4.Providing users with tools for working with data.

This is one of the most important functions of a DBMS, allowing the user to formulate queries to the database and receive various reports without writing application programs.

5. Providing application programmers with the means to create application systems, i.e. applications.

An application is a program or set of programs that provides automation of information processing for an applied task. Applications can be created within or outside the DBMS environment - using a programming system that uses database access tools, for example, Delphi or C++ Builder. Applications developed in a DBMS environment are often called DBMS applications .

and applications developed outside the DBMS are called
external applications
. To work with a database, DBMS tools are often sufficient and there is no need to use applications, the creation of which requires programming. Applications are developed mainly in cases where it is necessary to ensure the convenience of working with the database for unskilled users or the DBMS interface is not suitable for users.