Normal form (1NF). Test work on the basic definitions of a database Test work on the basic definitions of a database

Key

Relational database terms and definitions

Relational model.

The main idea of ​​this data model is to represent any data set as a two-dimensional array - a table.

Relational (English) – relationship.

In its simplest form, the relational model describes a single two-dimensional table. Basically, it (the model) describes the relationships between several tables.

Any relational table has the following properties:

Each table element is one data element;

All columns are homogeneous, i.e. all elements in a column have the same type;

Each column in a table has a unique name;

There are no identical rows in the table;

The order of rows and columns can be arbitrary.

The founder of the theory of relational databases is considered to be an IBM employee, Dr. Codd. It appeared in 1970 in the USA and this theory is based on the mathematical apparatus of set theory.

DB table– a two-dimensional array containing information about one class of objects. In the theory of relational algebra, a two-dimensional array is called a relation.

The table consists of the following elements:

- field (attribute)

Record (tuple)

Field contains the value of one of the attributes characterizing database objects. The number of fields in the table corresponds to the number of characteristics characterizing database objects.

Cell contains the specific value of the corresponding field.

Record is a table row. It contains the meaning of all the features characterizing this object. The number of records (rows) corresponds to the number of objects.

A key is a field whose value uniquely determines the value of all other fields in the table.

The key of a table can be not one, but several fields. In this case, the condition of uniqueness and minimality must be met. Each field that is not part of the primary key is called a non-key field of the table. Every table must have a primary key.

Primary key– a table field or fields, each value of which uniquely identifies the corresponding table record. For each primary key there must be one and only one of any of the data columns.

Uniqueness of the key means that at any given time a database table cannot contain any two different records that have the same key field values. The uniqueness condition is mandatory.

Minimality condition key fields means that only the combination of values ​​of the selected fields meets the requirements for the uniqueness of database table records. This means that none of the fields included in the key can be excluded from it without violating uniqueness.

When generating a database table key consisting of several fields, you must:

You should not include table fields in the key whose value alone uniquely identifies records in the table.

You cannot include a non-unique field in the key, i.e. a field whose value is repeated in the table.

Each table must have primary key. If there are fields in the table, the value of each of which uniquely defines the records, then these fields can be accepted as alternative keys.

Example: if you select the TIN (individual taxpayer number) as the primary key, then the passport number as the alternative key.

Normalization of relational database tables

A relational database is a set of tables interconnected. The number of tables in one file or one database depends on many factors:

Composition of database users;

Ensuring information integrity;

Ensuring the smallest amount of memory required and minimal data processing time.

Taking into account these factors when designing a relational database is carried out by methods of normalizing tables and establishing connections between them.

Table normalization is a technique for splitting a single table into multiple tables.

Table normalization is a sequential change in the table structure until it meets the requirements of the last form of normalization.

There are 6 forms of normality, but mostly 1,2,3 levels of normality are used. When describing normal forms, use the following concepts:

Functional dependency between fields;

Multivalued dependency

A functional dependence between fields A and B is a dependence in which each value of A at any time corresponds to a single value B of all possible ones.

Etc. connection between passport number and TIN.

Multivalued functional dependence between fields. Field A multivaluedly determines field B if for each value of field A there is a well-defined set of corresponding values ​​of field B.

Etc. progress table.

A complete functional dependence between a composite field A and a field B is a dependence in which field B depends functionally on field A, and does not depend functionally on any subset of field A.

Transitive functional dependence between fields A and C, if field C functionally depends on field B, and field B functionally depends on field A; in this case, there is no functional dependence of field A on field B.

Mutual independence between fields. Several fields are mutually independent if none of them is functionally dependent on the other.

A table is in 1st normal form when none of the fields contains more than one value and any key field is not empty. First normal form is the basic relational data model. Any table in a relational database is automatically in first normal form. Such a table should not contain fields that could be divided into several fields.

11 answers to test questions on "Databases (DB)"

1. The database is:

a. a set of interrelated data about a certain object, specially organized and stored on an external medium;
b. arbitrary set of information;
c. a set of programs for storing and processing large amounts of information;
d. an interface that supports filling and manipulating data;
e. computer program, allowing in a certain subject area to draw conclusions comparable to the conclusions of a human expert.

Answer: a

2. A relational database (DB) file record may contain:

a. extremely homogeneous information (data of only one type);
b. text information only;
c. heterogeneous information (data of different types);
d. only logical values;
e. purely numerical information;

Answer: c

3. Let's assume that some database contains the fields LAST NAME, YEAR OF BIRTH, INCOME. When searching by YEAR OF BIRTH > 1958 AND INCOME<3500 будут найдены фамилии лиц:

a. having an income of at least 3500, and older than those born in 1958.
b. those with an income of less than 3,500, and those born in 1958 and later;
c. having an income of less than 3500, and born in 1958 or later;
d. having an income of less than 3500, and born in 1959 and later;
e. having an income of less than 3,500, and those born in 1958;

Answer: d

4. Which of the options is not a function of the DBMS?

a. implementation of languages ​​for defining and manipulating data
b. providing the user with language tools for data manipulation
c. user model support
d. data protection and integrity
e. coordination of design, implementation and maintenance of the database

Answer: e

5. The database management system is a software product included in:

a. application software.
b. operating system;
c. unique software;
d. system software;
e. programming systems;

Answer: e

6. What is the smallest unit of data storage e DB?

a. stored field
b. stored file

d. stored record
e. stored byte

Answer: a

7. What must be included in a DBMS?

a. query language processor
b. command interface
c. visual shell
d. help system

Answer: a, b

8. List the benefits of a centralized approach to data storage and management.

a. ability to share data
b. data integrity support
c. redundancy agreement
d. reduction of inconsistency

Answer: a, b, c, d

9. Let's assume that a certain database is described by the following list of records:

1 Ivanov, 1956, 2400
2 Sidorov, 1957, 5300
3 Petrov, 1956, 3600
4 Kozlov, 1952, 1200

Which of the records in this database will change places when sorted in ascending order by the first field:

a. 3 and 4;
b. 2 and Z;
c. 2 and 4;
d. 1 and 4
e. 1 and 3;

Answer: c

10. The structure of the relational data base (DB) file changes:

a. when any entry is changed;
b. upon destruction of all records;
c. when deleting any field.
d. when adding one or more records;
e. when deleting a range of records;

Answer: c

11. What is a collection of stored records of the same type called?

a. stored file
b. database view
c. none of the above
d. logical database table
e. physical database table

Test work on basic database definitions

Option 1

1. Choose the most accurate definition.

The database is

software for working with data

an information model that allows you to store and work with data.

information organized into rows and columns

program in a programming language

one of the products in the Microsoft Office suite

2. The most accurate analogue of a relational database can be:

unordered set of data;

vector;

family tree;

two-dimensional table;

data network.

3. The database contains information about school students: last name, class, test score, practical task score, total number of points. What type of field should the TOTAL POINTS field be?

empty table

everything above is correct.

5. The key field is

1) the very first field of the record

2) counter

5) there is no correct answer

6. Field is it?

table row;

table column;

7.

text information only;

symbolic 2) Date 3) logical 4) numeric 5) any type

4. Does a table that does not have any records contain any information?

empty table,

empty table

a table cannot exist without records;

everything above is correct.

5. The key field is

1) the very first field of the record

2) counter

3) a field whose value uniquely identifies a record in the table

4) a field whose value always starts with 1

5) there is no correct answer

6. Field is it?

table row;

table column;

a set of similar data;

some indicator that characterizes with a numerical, text or other value.

7. A relational database (DB) record may contain:

heterogeneous information (data of different types);

extremely homogeneous information (data of only one type);

text information only;

purely numerical information.

represents a numeric, text or other value.

7. A relational database (DB) record may contain:

heterogeneous information (data of different types);

extremely homogeneous information (data of only one type);

text information only;

purely numerical information.

Option 2

1. Database is

a collection of data organized according to certain rules

a set of programs for storing and processing large amounts of information

interface that supports filling and manipulating data

a certain body of information

everything above is correct

2. Instead of a “relational” database, you can use

network

tabular

hierarchical

flat

there is no right answer

contains information about the structure of the database;

does not contain any information;

contains information about future entries;

everything above is correct.

everything above is correct.

6 . Recording is it?

table row;

table column;

a set of similar data;

some indicator that characterizes with a numerical, text or other value.

7 .

record numbers only;

data of only one type;

network

tabular

hierarchical

flat

there is no right answer

3. In which table elements are database data stored:

in the fields; 2) in lines; 3) in columns; 4) in records; 5) in cells.

4. Does a table that has no fields contain any information?

contains information about the structure of the database;

does not contain any information;

a table without fields cannot exist;

contains information about future entries;

everything above is correct.

5. Which field can be considered unique?

a field whose values ​​cannot be repeated;

a field that has a unique name;

a field whose value has the property of increasing;

a field whose values ​​are repeated;

everything above is correct.

6 . Recording is it?

table row;

table column;

a set of similar data;

some indicator that characterizes with a numerical, text or other value.

7 . The following can be written in a relational database (DB) field:

record numbers only;

both numeric and text data simultaneously;

data of only one type;

only the time of creation of records.

a field that has a unique name;

a field whose value has the property of increasing;

a field whose values ​​are repeated;

everything above is correct.

6 . Recording is it?

table row;

table column;

a set of similar data;

some indicator that characterizes with a numerical, text or other value.

7 . The following can be written in a relational database (DB) field:

record numbers only;

both numeric and text data simultaneously;

data of only one type;

only the time of creation of records.

Option 3

1. What is a database?

2 .

3

5) there is no correct answer

4.

number of records in the database;

has a limited size;

:

table dimension;

list of column names and row numbers of the tab

faces.

7. Expression value 0.7-3>

logical;

numerical;

symbolic;

text.

Option 3

1. What is a database?

a set of data collected on one floppy disk;

data intended for the operation of the program;

a set of interrelated data organized according to certain rules that provide general principles for describing, storing and processing data;

data sent over communication networks.

2 . What is a relational database?

A database in which information is organized in the form of rectangular tables;

A database in which the elements in a record are ordered, i.e. one element is considered the main one, the rest are subordinate;

A database in which records are arranged in random order;

A database in which it is possible to establish horizontal connections in addition to vertical hierarchical relationships.

3 . Each database entry contains

1) information about all system objects

2) information about a separate system object

3) a certain characteristic of the object

4) text and numerical information

5) there is no correct answer

4. The structure of a relational database (DB) is completely determined by:

a list of field names and an indication of the number of database records;

a list of field names indicating their width and types;

number of records in the database;

content of records stored in the database.

5. What is special about the “counter” field?

serves for entering numeric data;

used to enter real numbers;

the data is stored not in the field, but in another place, and the field only stores a pointer to where the text is located;

has a limited size;

has the property of automatic assignment.

6. The structure of the data table is determined :

table dimension;

a list of table column names;

7. The value of the expression 0.7-3>2 refers to the following data type:

logical;

numerical;

symbolic;

text.

5) there is no correct answer

4. The structure of a relational database (DB) is completely determined by:

a list of field names and an indication of the number of database records;

a list of field names indicating their width and types;

number of records in the database;

content of records stored in the database.

5. What is special about the “counter” field?

serves for entering numeric data;

used to enter real numbers;

the data is stored not in the field, but in another place, and the field only stores a pointer to where the text is located;

has a limited size;

has the property of automatic assignment.

6. The structure of the data table is determined :

table dimension;

a list of table column names;

a list of column names and row numbers of the table.

7. The value of the expression 0.7-3>2 refers to the following data type:

logical;

numerical;

symbolic;

text.

a list of column names and row numbers of the table.

7. The value of the expression 0.7-3>2 refers to the following data type:

logical;

numerical;

symbolic;

text.

Two final options test tasks in computer science. Topic "Databases"

Bochkareva Svetlana Sergeevna, 15.11.2017

12796 717

Development content

OPTION 1.
	MULTI-LEVEL

OPTION 2
	From one or more related tables
	And DBMS located on the same computer

Development content

FI__________________________________________class_____

Test on the topic: “Databases” 1st option

Databases (DB) are:

1. A set of spreadsheets and the entire hardware complex - software for their storage; changes and search for information; for user interaction;

   – an organized collection of data intended for long-term storage in the external memory of a computer and permanent use;

   – software that controls data storage and processing;

   – customizable dialog boxes, saved on the computer as special types of objects.

Database management systems are:

1. – the most important tool for selecting data based on specified conditions;

   – a program that allows you to create databases, as well as providing processing (sorting) and searching for data

   – customizable dialog boxes saved on the computer as special types of objects;

A set of databases and the entire complex of hardware and software for storing them; changing and searching information for user interaction

Select the item where ACCESS DBMS objects are correctly listed:

A) tables, selection queries, queries with a parameter, forms, reports.

B) tables, queries, macros, forms.

C) tables, queries, forms, reports, macros, modules.

Without which objects a database cannot exist:

without modules;

no reports;

without tables;

without forms;

no macros;

without requests?

Based on the nature of the stored information, databases are divided into:

1. Factual

   Centralized

   Hierarchical

According to the structure of data organization, databases are:

1. Centralized

   Documentary

Specify database management systems:

1. Microsoft Access

   OpenOffice.org Calc

   Microsoft Power Point

Field when entering new entry a number is automatically entered into it, one more than the value of the same field in the previous record. This field is called:

A) Logical field

B) Numeric field.

B) Counter.

The database field is

1. A table row containing a set of property values ​​in database columns

   Database table header

   A table column containing the values ​​of a specific property

In order to change the structure of the created table you need to:

A) enter design mode

B) use table wizard mode

C) open the table and change the field names

Does a table that has no fields contain any information?

contains information about the structure of the database;

does not contain any information;

a table without fields cannot exist;

contains information about future entries.

In tabular databases, the record contains

1. A set of data about one object

   Database name

   Homogeneous data about all objects

List the advantages of tabular databases:

1. Ability to see multiple records simultaneously

   Difficult to view and edit data

Requests are used for:

B) data storage

C) entering new data into the database.

What line will the surname IVANOV occupy after sorting in ascending order in the CLASS field?

What data cannot be a database key?

1. Passport ID

   Date of Birth

   Login email email + password

List the disadvantages of the database - form:

1. Ability to see only one entry

   Contains a large number of fields

   Easily view and edit data

What is called sorting data in a database?

1. Selecting records that meet your search criteria

   Printing ordered entries

   Ordering records by the value of one of the fields

Requests are used for:

A) data processing: ordering, filtering, etc.

B) data storage

C) entering new data into the database.

How is a query different from a filter?

1. A query is an independent database object

   The request can be simple or complex

Complete the sentence:“A hierarchical database has a _________________ ___________________________________________________ structure”

What data can be a database key?

INN+SNILS

City of residence

Relationship between tables. Select the point where the statement is incorrect:

) The Data Schema window can be opened via the Tools/Data Schema menu

B) The Cascading update of related fields and Cascading deletion of related records checkboxes ensure simultaneous updating or deleting of data in all subordinate tables when they change in the main table.

C) When creating a relationship between fields, it is not necessary that the main table field be a key one.

Finish the sentence: “A relational database consists of ___________________

_______________________________________________________________»

Search keys in database management systems (DBMS) are called:

the range of records in the database file in which the search is carried out;

logical expressions that define search conditions;

fields whose value is used to search;

numbers of records that satisfy the search conditions;

number of the first record in order that satisfies the search conditions?

Let's assume that some database contains the fields LAST NAME, YEAR OF BIRTH, INCOME. When searching by condition: YEAR OF BIRTH1958 AND INCOME

having an income of less than 3,500 and those born in 1958 and later;

those with income less than 3,500 and older than those born in 1958;

having an income of less than 3,500 or those born in 1958 and later;

having an income of less than 3500 and born in 1959 and later.

Let's assume that a certain database is described by the following list of records:

1 Ivanov, 1956, 2400;

2 Sidorov, 1957, 5300;

3 Petrov, 1956, 3600;

4 Kozlov, 1952, 1200;

Which of the records will change places when sorting this database in ascending order if it is carried out by the first field:

Why, when closing a table, Access does not prompt you to save the entered data:

flaw in the program;

because the data is saved immediately after being entered into the table;

because the data is only saved after the entire database is closed?

Development content

FI__________________________________________class____