Normal form (1NF). Checking work on the main definitions of the database Checking work on the main definitions of the database
Key
Terms and definitions of relational databases
relational model.
The main idea of this data model is to represent any data set as a two-dimensional array - a table.
Relational (English) - relation.
In the simplest case, the relational model describes a single two-dimensional table. Basically, it (the model) describes the relationship 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 are of 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 an employee of IBM, 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 relational algebra theory, 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 features that characterize the objects of the database. The number of fields in the table corresponds to the number of features that characterize database objects.
Cell contains the specific value of the corresponding field.
Recording is a table row. It contains the value of all features that characterize given 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.
A table key 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 field or fields of a table, each value of which uniquely identifies the corresponding table entry. For each primary key, there must be one and only one of any of the data columns.
Key uniqueness means that at any given time a database table cannot contain any two distinct records that have the same key field values. The uniqueness condition is mandatory.
Minimum condition key fields means that only the combination of the values of the selected fields meets the requirements for the uniqueness of the 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, the value of which by itself 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 is uniquely determined by the records, then these fields can be taken as alternative keys.
Example: if you select TIN (individual taxpayer number) as the primary key, then the passport number as an alternative key.
Normalization of relational database tables
A relational database is a set of tables connected to each other. The number of tables in one file or one database depends on many factors:
Composition of database users;
Ensuring the integrity of information;
Ensuring the least amount of memory required and the minimum processing time.
Accounting for these factors when designing a relational database is carried out by methods of normalizing tables and establishing links between them.
Table normalization is a way to split one table into multiple tables.
Table normalization is a sequential change in the structure of a table until it satisfies the requirements of the latest form of normalization.
There are 6 forms of normality, but 1,2,3 levels of normality are generally 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 of B from all possible.
Etc. connection between passport number and TIN.
Multivalued functional dependence between fields. A field A multi-valuedly defines a 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 the field B depends functionally on the field A, and does not functionally depend on any subset of the field A.
Transitive functional dependence between the fields A and C, if the field C is functionally dependent on the field B, and the field B is functionally dependent on the field A; in this case, there is no functional dependence of the field A on the 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 1 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 the test questions on "Databases (DB)"
1. The database is:
a. a set of interconnected data about a certain object organized in a special way 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, which allows in some 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. exclusively homogeneous information (data of only one type);
b. only text information;
c. heterogeneous information (data of different types);
d. only logical values;
e. exclusively numerical information;
Answer: c
3. Suppose some database contains the fields SURNAME, YEAR OF BIRTH, INCOME. When searching by the condition YEAR OF BIRTH > 1958 AND INCOME<3500 будут найдены фамилии лиц:
a. having an income of at least 3,500 and older than those born in 1958.
b. those with an income of less than 3,500, i.e. those born in 1958 and later;
c. having an income of less than 3,500, and born in 1958 or later;
d. having an income of less than 3500, and born in 1959 and later;
e. those with 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. support for user models
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 that is part of:
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 storing and managing data.
a. ability to share data
b. data integrity support
c. redundancy convention
d. reduction of inconsistency
Answer: a, b, c, d
9. Suppose some 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 sorting in ascending order, performed by the first field:
a. 3 and 4;
b. 2 and Z;
c. 2 and 4;
d. 1 and 4
e. 1 and Z;
Answer: c
10. The structure of the relational database (DB) file changes:
a. when any record is changed;
b. when all records are destroyed;
c. when removing any field.
d. when adding one or more entries;
e. when deleting a range of records;
Answer: c
11. What is a set of stored records of the same type called?
a. stored file
b. database view
c. none of the above
d. database logical table
e. physical database table
Verification work on the main definitions of the database
Option 1
1. Choose the most accurate definition.
The database is
software for working with data
information model that allows you to store and work with data.
information organized in rows and columns
programming language program
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, grade, test score, practical task score, total score. What type 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.
only text information;
character 2) Date 3) logical 4) numeric 5) any type
4. Does a table with no records contain any information?
empty table,
empty table
a table without records cannot exist;
everything above is correct.
5. The key field is
1) the very first field of the record
2) counter
3) a field, the value of which uniquely determines the entry 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 a numerical, textual or other value.
7. A relational database (DB) record may contain:
heterogeneous information (data of different types);
exclusively homogeneous information (data of only one type);
only text information;
purely numerical information.
draws a numeric, textual, or other value.
7. A relational database (DB) record may contain:
heterogeneous information (data of different types);
exclusively homogeneous information (data of only one type);
only text information;
purely numerical information.
Option 2
1. The database is
a set 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
certain amount of information
everything above is correct
2. Instead of a "relational" database, you can use
network
tabular
hierarchical
flat
no correct 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 a numerical, textual or other value.
7 .
record numbers only;
data of only one type;
network
tabular
hierarchical
flat
no correct answer
3. Which elements of the table store the database data:
in the fields; 2) in lines; 3)in columns; 4) in records; 5) in cells.
4. Does a table with 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. What field can be considered unique?
a field whose values cannot be repeated;
a field that has a unique name;
a field whose value has an increment property;
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 a numerical, textual or other value.
7 . In the field of a relational database (DB) can be written:
record numbers only;
both numeric and text data at the same time;
data of only one type;
only the time of creation of records.
a field that has a unique name;
a field whose value has an increment property;
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 a numerical, textual or other value.
7 . In the field of a relational database (DB) can be written:
record numbers only;
both numeric and text data at the same time;
data of only one type;
only the time of creation of records.
Option 3
1. Database - is it?
2 .
3
5) there is no correct answer
4.
the number of records in the database;
has a limited size;
:
table dimension;
list of column names and row numbers of tab
faces.
7. Expression value 0.7-3>
logical;
numerical;
symbolic;
text.
Option 3
1. Database - is it?
a set of data collected on a single diskette;
data intended for the operation of the program;
a set of interrelated data organized according to certain rules, providing for general principles for describing, storing and processing data;
data sent over communication networks.
2 . Is it 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 located in an arbitrary order;
A database in which it is possible to establish horizontal links in addition to vertical hierarchical links.
3 . Each database entry contains
1) information about all system objects
2) information about a separate object of the system
3) a certain characteristic of the object
4) textual and numerical information
5) there is no correct answer
4. The structure of a relational database (DB) is completely defined 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;
the number of records in the database;
content of records stored in the database.
5. What is the peculiarity of the "counter" field?
serves to enter numerical data;
serves to enter real numbers;
the data is stored not in the field, but in another place, and only a pointer to where the text is located is stored in the field;
has a limited size;
has the property of automatic assignment.
6. The structure of the data table is determined :
table dimension;
list of table column names;
7. The expression value 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 defined 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;
the number of records in the database;
content of records stored in the database.
5. What is the peculiarity of the "counter" field?
serves to enter numerical data;
serves to enter real numbers;
the data is stored not in the field, but in another place, and only a pointer to where the text is located is stored in the field;
has a limited size;
has the property of automatic assignment.
6. The structure of the data table is determined :
table dimension;
list of table column names;
a list of column names and row numbers of a table.
7. The expression value 0.7-3>2 refers to the following data type:
logical;
numerical;
symbolic;
text.
a list of column names and row numbers of a table.
7. The expression value 0.7-3>2 refers to the following data type:
logical;
numerical;
symbolic;
text.
Two options for final test items in informatics. Theme "Databases"
Bochkareva Svetlana Sergeevna, 15.11.2017
12796 717
Development content
| OPTION 1. |
|
| MULTILEVEL |
|
| OPTION 2 |
|
| From one or more related tables |
|
| And DBMS located in the same computer |
|
Development content
FI___________________________________________ class_____
Test on the topic: "Databases" 1 option
The set of spreadsheets and the entire complex of hardware - software tools for their storage; change and search for information; to interact with the user;
- an organized set of data intended for long-term storage in the external memory of a computer and permanent use;
- software that manages the storage and processing of data;
are custom dialog boxes that are stored on a computer as a special type of object.
– the most important tool for selecting data based on given conditions;
- a program that allows you to create databases, as well as provides processing (sorting) and data retrieval
– customizable dialog boxes stored in the computer as objects of a special type;
Databases (DB) are:
Database management systems are:
A set of databases and the entire complex of hardware and software for their storage; change and search for information for user interaction
Select the item where the ACCESS DBMS objects are correctly listed:
A) tables, select 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;
without reports;
without tables;
without forms;
without macros;
Factual
Centralized
Hierarchical
Centralized
Documentaries
Microsoft Access
Open Office.org Calc
Microsoft PowerPoint
no requests?
By the nature of the stored information, databases are:
According to the structure of the organization of database data, there are:
Specify database management systems:
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) Boolean field
B) Numeric field.
B) counter.
Table row containing a set of property values in database columns
Database table header
Table column containing the values of a specific property
The database field is
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 with 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.
A set of data about a single object
Database name
Homogeneous data about all objects
Ability to view multiple entries at the same time
Difficult to view and edit data
In tabular databases, the record contains
List the advantages of tabular databases:
Requests are 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?
Passport ID
Date of Birth
Login Email mail + password
Ability to see only one entry
Contains a large number of fields
Easy to view and edit data
Selecting Records That Match Search Conditions
Printing Ordered Records
Ordering records by the values of one of the fields
What data cannot be a database key?
List the disadvantages of the database - form:
What is sorting data in a database?
Requests are for:
A) data processing: ordering, filtering, etc.
B) data storage
C) entering new data into the database.
The request is an independent database object
Request can be simple or complex
How is a query different from a filter?
Finish the sentence:“A hierarchical database has a _________________ _________________________________________________ structure”
What data can be a database key?
TIN+SNILS
City of residence
Relationship between tables. Choose the point where the statement is incorrect:
) The Data Schema window can be opened via the Tools/Data Schema menu
B) Checkboxes Cascade update related fields and Cascade delete related records provide simultaneous update or deletion 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 field of the main table be the 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 performed;
logical expressions that define search conditions;
fields by the value of which the search is carried out;
numbers of records that meet the search conditions;
the number of the first record in order that satisfies the search criteria?
Suppose some database contains the fields SURNAME, YEAR OF BIRTH, INCOME. When searching by condition: YEAR OF BIRTH1958 AND INCOME
those with an income of less than 3,500 and those born in 1958 or later;
those with an income of less than 3,500 or older than those born in 1958;
those with an income of less than 3,500 or those born in 1958 or later;
with an income of less than 3,500 and born in 1959 or later.
Suppose some 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 in ascending order of this database, if it is carried out by the first field:
Why, when closing a table, Access does not offer to save the entered data:
program flaw;
because the data is stored immediately after being entered into the table;
because the data is only saved after closing the entire database?
Development content
FI ______________________________________________ class____
