Electronic notebook of Maria Medvedevskaya. Concept of field, record

| Planning lessons for the school year (textbook for Federal State Educational Standards by L.L. Bosov, A.Yu. Bosov) | §1.5 Database as a domain model. Relational Databases

Lesson 6
§1.5 Database as a domain model. Relational Databases

Keywords:

Information system
database
relational database
record
field
key

1.5.1. Information systems and databases

Modern man in his practical activities increasingly uses various information systems that provide storage, retrieval and delivery of information according to his requests. Examples of information systems are:

Big city reference address service;
a transport information system that provides not only the ability to obtain reference information on train and plane schedules, but also the purchase of railway and air tickets;
information retrieval system containing legal information.

The central part of any information system is the database.

A database (DB) is a collection of data organized according to certain rules, reflecting the state of objects and their relationships in a certain subject area (transport, medicine, education, law, etc.), intended for storage in the external memory of a computer and permanent use.

A database can be thought of as an information model of a domain.

The main models of data organization in databases are hierarchical, network and relational (Fig. 1.14).

Rice. 1.14. Models for organizing data in a database: a - hierarchical, b - network, c - relational

Hierarchical model Data organization ensures the ordering of objects by levels. There are connections between objects: each object can be connected to objects at a lower level. Such objects are said to have an ancestor-child relationship. The hierarchical data organization model is implemented in the folder system of the Windows operating system. Searching for an object in such a database can be quite labor-intensive due to the need to sequentially go through several previous hierarchical levels.

Network model The data organization does not impose any restrictions on the relationships between objects: it can contain objects that have more than one ancestor. This data organization model is implemented on the World Wide Web of the global computer network Internet.

Relational databases are the most widely used. We will look at them in more detail.

1.5.2. Relational Databases

A relational database (RDB) uses a relational data model based on representing data in the form of tables.

A relational database can consist of one or more interconnected rectangular tables.

(Fig. 1.15).

Rice. 1.15. Relational database table structure

A record contains information about one object described in the database: one product sold in a store; about one book available in the library; about one employee working at the enterprise, etc.

The field contains information about the values ​​of only one of the characteristics (attributes, properties) of the object: product name; cost of goods; quantity of goods available; book title; author of the book; year of publication; surname, name, patronymic of the employee; dates of birth; specialties, etc. Field values ​​in one column refer to one characteristic of the object.

The database field has name, type and length.

All table field names must be different.

The type of a field is determined by the type of data the field contains.

Main field types:

Numeric - for fields containing numeric information;
text - for fields containing various sequences of characters;
logical - for fields in which data can take only two values: YES (TRUE, 1) and NO (FALSE, 0);
date - for fields containing calendar dates (in our country it is customary to write the day, and then the month and year).

Field length is the maximum number of characters that can be contained in the field.

To record the table structure, you can use the following form:

TABLE NAME (FIELD NAME 1, FIELD NAME 2, ...)

For example, you can describe a single-table “Weather Calendar” database like this:

WEATHER CALENDAR (DAY, TEMPERATURE, HUMIDITY, PRESSURE, WIND DIRECTION, WIND SPEED)

Here the DAY field will be of the “date” type, the TEMPERATURE, HUMIDITY, PRESSURE, WIND SPEED fields will be of the numeric type; WIND DIRECTION field - text type.

There should be no matching records in the table. In other words, there must be a field or a set of fields whose values ​​are different for all records.

For example, the values ​​of the DAY field in a Weather Calendar database will always be different between records.

In the database

STUDENT (FAST NAME, FIRST NAME, PATRONICAL NAME, DATE OF BIRTH, BIRTH CERTIFICATE SERIES, BIRTH CERTIFICATE NUMBER, CLASS)

Only the values ​​of a combination of fields such as BIRTH CERTIFICATE SERIES and BIRTH CERTIFICATE NUMBER will probably not match.

A field or set of fields whose values ​​are not repeated in records (are unique) is called a database table key.

THE MOST IMPORTANT

Database (DB)- a set of data organized according to certain rules, reflecting the state of objects and their relationships in a certain subject area (transport, medicine, education, law, etc.), intended for storage in the external memory of a computer and permanent use. A database can be thought of as an information model of a domain.

The main models for organizing data in databases are hierarchical, network and relational. Relational databases (RDBs) use a relational data model based on presenting data in the form of tables.

A row in an RDB table is called a record, a column is called a field.. A field or a set of fields whose values ​​are not repeated in different records (are unique) is called a database table key.

Questions and tasks

1. Read the presentation materials for the paragraph contained in the electronic appendix to the textbook. Use these materials when preparing answers to questions and completing assignments.

2. What is an information system? Give an example of an information system.

3. What is a database? Do you think that in a broad sense, a telephone directory, an address book, a library catalog and other means that allow us to store data in an orderly form can be called databases? Discuss these questions in a group.

4. Name the main ways to organize data in databases.

5. What databases are called relational?

6. What is a recording? What information does it contain?

7. What is a field? What information does it contain?

8. List the main types of RDB fields.

9. For single-table database fields

indicate the type of each field.

10. What is a database table key? What can serve as a key in the COLLECTION database from task 9?

11. Consider the composition, field types and key of a single-table database:

a) TRAVEL AGENCY;
b) VIDEO LIBRARY;
c) CAR SHOWROOM;
d) REGIONS of the Russian Federation.

Test"Database as a domain model"

Computer programs work with a colossal amount of information that needs to be stored somewhere. Databases are created specifically for this purpose, providing a structured presentation of information and convenient access to it. One of the most popular ways to organize such storage is tabular, in which you can select special field types for different types of information. This makes it easier to manipulate data and save resources.

Tabular Databases

Tabular, or relational, are widely used because of their convenience and developed infrastructure. There are many DBMS - management systems that provide complete control over application information.

Each database consists of several tables that represent a specific entity or entity relationship. For example, data about university students or information about exam results can be presented in the form of a table.

The columns of a table are called fields and contain a specific attribute of an entity. So, in the “Students” table the fields are:

• Full Name;
• record number;
• Date of Birth;
• phone number.

The rows are called records and represent a single real object (a specific student).

The number of columns (fields) of a table is determined when it is created and does not change anymore. Rows can be added, deleted and edited at any time.

At first glance, it is obvious that the information stored in the "Full Name" field differs significantly from the information in the “Record No.” field or in the “Date of Birth” field. Since manipulations with different types of data are carried out using different algorithms, it is advisable to determine in advance what type of information will be stored in a specific table field.

Each database determines what types of fields it can process. Basic types of information, for example, numeric, symbolic, are supported in any system. In addition, some databases may provide their own

Fields and their properties

A record field is the smallest named unit of information in a database. It has two required properties:

• a unique name within the table by which it can be accessed;
• the type of data stored in it.

The field can be marked as unique or key.

The unique property means that the value of this field cannot be repeated for all table records.

Key fields are those that are most actively involved in data selections. Indexes will be built on them - additional structures that facilitate searching.

Each table in the database must have a primary key that is unique for each record and uniquely identifies it. It may consist of one or more fields. It makes most sense to choose fields that have short values ​​as the primary key. For example, in the “Students” table, the “Text No.” field can serve as the primary key.

Integrity Properties

Maintaining the integrity of the data is essential for normal, error-free operation. This means that every field in every record must take exactly the value that is expected. For example, the record number will always be a number, but the student’s name should not contain numbers.

In addition, some fields are absolutely necessary to describe the entity, while others are optional. A student may not have a phone number, but he always has a name and a record book.

Ensuring data integrity is controlled by several properties:

• the field type determines the type of data that can be its value;
• mandatory prohibits entering entries into the table with an empty field;
• the default value allows you to not fill in the field, but not leave it empty;
• uniqueness ensures unambiguous identification of an entity within a table;
• the maximum or exact length of the field value in characters;
• data formatting method;
• various additional conditions (maximum and minimum date).

The main types and formats of fields supported by most DBMSs:

• numeric - integer and real;
• string;
• binary;
• brain teaser;
• date and time;
• enumerations and sets.

In some databases, hyperlinks, monetary values, notes, and error messages can be separated into a separate type.

The type of a field determines the set of integrity constraints that can be applied to it. In many, these types are specified and initially include a number of restrictions. A good example is the TINYINT type in MySQL, which accepts integers within a limited range.

Strings

String values ​​can contain any characters. The main limitation is the length.

Strings can be fixed or variable length. In the second case, the maximum possible size is usually set. The most common length limit for strings in a database is 255 characters.

The names of string field types may differ in different DBMSs. Most popular:

• CHAR - fixed length up to 255 characters. If the string is smaller than the specified size, it will be padded with spaces.
• VARCHAR, TINYTEXT - variable length up to 255 characters, an additional byte is spent to store the size.
• TEXT, MEMO - variable length up to 65,535 characters.
• MEDIUMTEXT - maximum 16,777,215 characters.
• LONGTEXT - maximum 4,294,967,295 characters per line.

The string type of the database field allows you to store passwords, short descriptions, personal data, addresses, phone numbers, articles. No mathematical operations are performed with this information. Strings can be compared in lexicographical order.

Very large chunks of text can also be stored in BLOB fields, which are discussed below.

Possible integrity constraints: string length, mandatory, default value.

Numbers

There are a large number of number formats supported by databases: integer, long integer, real, floating point and fixed point.

Mathematical operations can be performed on numbers. The database can store both positive and negative numeric values. DBMSs define several numeric types that have different size limitations.

For integers:

• TINYINT, byte - range of values ​​0 - 255 (or -127 - 128);
• SMALLINT - from 0 to 65.535 (from -32.768 to 32.767);
• MEDIUMINT - from 0 to 16.777.215 (from -8.388.608 to 8.388.607);
• INT - from 0 to 4294967295 (from -2.147.483.648 to 2.147.483.647);
• BIGINT - from 0 to 18.446.744.073.709.551.615 (from -9.223.372.036.854.775.808 to 9.223.372.036.854.775.807).

For real numbers:

• FLOAT - the number of characters in the fractional part of the mantissa is not more than 24.
• DOUBLE, REAL - a double precision number, after the dot there can be up to 53 characters.

There is another specific type of database field - DECIMAL (NUMERIC). This is the same number as DOUBLE, but written as a string.

You should always choose the smallest possible field size. For example, one TINYINT byte is sufficient to store a student’s exam grade. This allows you to save database resources. For example, BIGINT fields are among the most rarely used types, since almost no application operates with numbers in such a huge range.

Possible data integrity restrictions:

• size;
• data formatting (in some DBMS): numbers can be presented in percentage, exponential, monetary format;
• fractional part size;
• default value;
• uniqueness;
• auto-completion (record numbering).

Fields with a numeric data type often become the primary index of a table (as long as the values ​​are unique).

Counter

Counter fields have a numeric data type, but their value is assigned to each new record automatically by the database itself. Each time the counter is simply increased by one, providing row numbering in the table.

Due to their uniqueness, such fields can be used as a surrogate primary key, because they allow each record to be uniquely identified.

The counters have no integrity restrictions, since the database itself takes care of filling them.

date and time

Fields with the data type "Date" and "Time" are very convenient for working. They allow you to save data in various formats:

• DATE - only date in the format "YYYY-MM-DD", for example, "2018-04-04";
• DATETIME - date along with time in the format "YYYY-MM-DD HH:MM:SS", for example, "2018-04-04 17:51:33";
• TIME - only time in the "HH-MM-SS" format;
• YEAR - year in the format "YY" (17) or "YYYY" (2017);
• TIMESTAMP is a time stamp that can indicate, for example, the exact moment a record was entered into the database. The format can be different, for example, "YYYYMMDDHHMMSS".

The main integrity limitation is the way the data is formatted.

Boolean values

The simplest type of information is logical, or Boolean. It allows only two mutually exclusive values: TRUE (true, 1) and FALSE (false, 0).

Fields with are used to store so-called flags that can be used to indicate whether a student receives a scholarship or not.

Binary data

Databases provide the ability to store large amounts of information. Audio and video files, images, and fragments of compiled code are stored in BLOB form (Binary Large Object).

Fields intended to record such data must be one of the following types:

• BINARY - fixed length binary string;
• TINYBLOB;
• BLOB;
• MEDIUMBLOB;
• LONGBLOB;
• OLE object (Object Linking and Embedding, technology for linking and inserting objects) - in Microsoft Access;

The binary data array has no user-defined integrity constraints. Different databases implement work with blob objects in different ways.

Transfers

In some DBMSs, it is possible to create a field whose value will be selected from a predefined list of valid values. This is very similar to how a radio button works in HTML.

This type of field is called ENUM. There can be a maximum of 65,535 string values ​​in the allowed list, of which only one is selected.

The integrity constraint in this case is obvious - all possible values ​​of the base field are predefined and cannot take other values.

Sets

The SET data type works very similarly. It also accepts a list of valid string values, but allows you to select multiple of them at once. This is how the checkbox element works. The maximum number of elements in a set is 64.

Choosing the right database field type is important to how your application works. This is due to saving resources and different ways of processing different types of information.

When designing and building a database, it is important to be clear about the format and integrity constraints of the information in each field of each table. From the suitable types offered by a particular DBMS, it is recommended to choose the one that takes up the least amount of space.

31. Properties of fields, their purpose.

The main properties of table fields are listed below:

-Field name– determines how the data of this field should be accessed during automatic operations with the database. One database cannot have two fields with the same name, since the computer will become confused about their contents.

-Field type– defines the type of data that can be contained in this field.

-Field size – defines the maximum data length ( in characters). Symbols are encoded one or two bytes, so we can conventionally assume that the field length is measured in bytes.

-Field format– determines how data is formatted in cells belonging to the field.

-Input mask– defines the form in which data is entered into the field (data entry automation tool).

-Signature- This is the information that appears in the column header. It should not be confused with the field name; if the signature is not specified, then the field name is displayed in the header. Different fields. for example, you can set the same signatures. This will not interfere with your computer because the fields still have different names.

-Default value– the value that is entered into the field cells automatically (data entry automation tool).

-Condition on value– a constraint used to check the correctness of data entry (an input automation tool that is typically used for data that has a numeric, currency, or date type).

-Error message– a text message that is displayed automatically when you try to enter erroneous data in a field (error checking is performed automatically if the property is set Condition on value).

-Obligatory field– a property that determines whether this field must be filled in when filling out the database.

-Blank lines– a property that allows the entry of empty string data (from the property Obligatory field differs in that it does not apply to all data types, but only to some, for example text).

-Indexed field– if a field has this property, all operations related to searching or sorting records by the value stored in this field are significantly accelerated. In addition, for indexed fields, you can make sure that the values ​​in records will be checked against this field for duplicates, which allows you to automatically eliminate data duplication.

Since different fields can contain data of different types, the properties of the fields can vary depending on the data type. For example, for data that represents real numbers, the number of decimal places is an important property. On the other hand, for fields used to store pictures, sound recordings, video clips, and other OLE objects, most of the above properties are meaningless.

3 3.Key fields. Creating relationships between tables in MS Access .

Each table outlines key field. As such, select a field in which the data cannot be repeated.

When creating a table structure, one field (or one combination of fields) can be designated as key. The computer works differently with key fields. It checks their uniqueness and sorts faster by such fields. The key field is an obvious candidate for creating connections. If the author did not specify a key field when creating a table, the database management system will politely remind you that a primary key field would not interfere with the table.

A field of type Counter is often used as a primary key in tables. By definition, it is impossible to enter two identical values ​​into such a field, since the field value is incremented automatically.

The structure of relationships between tables is called data schema.

Creating relationships between tables

The main advantages of database management systems are realized when working not with individual tables, but with groups of interrelated tables. To create relationships between tables, the Access 9x DBMS has a special dialog box called Data Schema.

The Data Schema window is opened by clicking on the button of the same name on the toolbar or by command Tools > Data Schema.

If previously there were no connections between the database tables, then when you open the Data Schema window, the Add Table window simultaneously opens, in which you can select the necessary tables to include in the structure of inter-table relationships.

If the relationships between the tables have already been specified, then to introduce a new table into the data diagram, you need to right-click on the data diagram and select Add table from the context menu.

Having entered all the tables that need to be linked into the data schema, you can begin to create relationships between the fields of the tables.

The relationship between fields is established by dragging the field name from one table to another onto its corresponding related field.

After dragging, the Links dialog box opens, where you can set the properties of the resulting link.

Enabling the Ensure data integrity condition checkbox allows you to protect against cases of deleting records from one table, in which the associated data of other tables will remain unconnected.

For an integrity condition to exist, the main table field must be a key field, and both fields must be of the same type.

The Cascading update of related fields and Cascading deletion of related records checkboxes ensure that data in all subordinate tables is simultaneously updated or deleted when they change in the main table. If Sokolov’s client gets married and changes his last name to Voronova, he will only have to make a change in the last name field of the Clients table. In other tables, changes will occur automatically.

The Data Schema dialog box visually displays the relationships between tables. To delete a connection, right-click on the connection line and use the Delete command from the context menu.

1. What is a database?
Answer: A database (DB) is a specifically organized collection of data related to a specific subject area, intended for storage in the external memory of a computer and permanent use.

2. What is the difference between factual and documentary databases?
Answer: Factual databases contain brief information about the objects being described, presented in a strictly defined form. Documentary databases contain extensive information of various types: text, graphic, audio, multimedia.

3. What is database distribution?
Answer: This is a database, different parts of which are stored on different computers on networks.

4. What is an information system? Give examples of information systems.
Answer: An information system is a combination of a database and the entire complex of hardware and software for storing, changing and retrieving information for interaction with the user. Examples of an information network are ticket sales systems for passenger trains and airplanes.

5. What do you know about relational database?
Answer: Relational databases have a tabular organization. A table row is called a record, a column is called a field. Also, there should be no matching records in a relational database.

6. What is a record, field? What information do they contain?
Answer: A record is a table row, a field is a column. One record contains information about one object of the real system, the model of which is presented in the table. Fields are various characteristics (sometimes called attributes) of an object. The field values ​​in one line refer to one object.

7. Determine the names of the fields in the tables “Home Library” (Table 2.1), “Weather” (Table 2.2), “Progress” (Table 2.3), “Electives” (Table 2.5).
Answer:
- The names of the "Home Library" table are: "Number", "Author", "Title", "Year" and "Shelf".
- Names of the “Weather” table: “Day”, “Precipitation”, “Temperature, °C”, “Pressure, mmHg”, “Humidity, %”.
- Names of the “Progress” table: “Student”, “Russian”, “Algebra”, “Chemistry”, “Physics”, “History”, “Music”.
- Names of the “Electives” table: “Student”, “Geology”, “Floriculture”, “Dancing”.

8. What is the primary key of a database? What types of keys are there?
Answer: A primary key in a database is a field (or a set of fields) whose value is not repeated across different records. A key can be one field (simple key) or several fields (composite key).

9. Name the objects and information about which the records in the “Weather”, “Achievement”, and “Electives” databases contain. Define the keys in these databases.
Answer: The primary key in the "Weather" database is "DAY", in "Achievement" - "STUDENT", in "Electives" - "Student".

10. Define the following concepts: field name, field value, field type. What are the field types? What values ​​does each field type define?

Answer: Each table field has its own unique name, value and type. The type determines what kind of information is stored in the field and what actions can be performed with it. There are four main types of fields used in the database: numerical, symbolic, logical, "date ".

11. Determine the types of all fields in the “Home Library”, “Weather”, “Schools” tables.
Answer: “Home Library” - “Number”, “Year”, “Shelf” - numeric, “Author” and “Title” - symbolic; “Weather” - “Day” - “date” type, “Precipitation” - symbolic, “Temperature, °C”, “Pressure, mmHg”, “Humidity, %” - numeric; “Schools” - “City”, “Director”, “Address”, “Telephone” - symbolic, “School number” - numeric.

12. Define the structure (composition of fields), keys and field types for relational databases under the following names:
a) "Countries of the World";
b) "My classmates";
c) "Movies";
d) "Telephone directory";
e) “My doctor visits.”
Answer: - “Countries of the world”: Factual structure, keys - “population”, “territory”, field types - symbolic, numeric, possibly “date”;
- "My classmates": Factual structure, key - "last name", field types - symbolic, "date";
- "Movies": Documentary structure, key "title", field types - symbolic, "date", numeral;
- "Phone directory": Factual structure, key - "phone number", field type - character
- “My visits to the doctor”: Documentary structure, field type - “date”, symbolic.

Table elements:

Record (string)- a data element containing information about only one object (product, manufacturer, employee, student). Unlike a field, a record does not have a name, but has a number.

Duplicate entries are prohibited.

Field (column) is the smallest data element that has a name and a value. The number of columns in the table is determined by the number of selected descriptive details of the object. For example, the Student object has descriptive details (field names): Full name, Date of birth, Address.

Under table structure understand a set of table fields indicating their names, types and properties.

Field.

Field name is a string of no more than 64 characters including spaces.

Field type specifies what values ​​the field can contain. The Access DBMS defines the following: field types :

Ø Text - a line of text no longer than 255 characters;

Ø MEMO field - text up to 64,000 characters;

Ø Numerical - to write numbers;

Ø Date Time - to record date and time;

Ø Monetary - numeric type for recording monetary amounts; when viewing its values, the currency symbol is displayed;

Ø Counter - a numeric type, the values ​​of which are changed automatically by the system;

Ø Logical - logical type, the values ​​of which are Yes or No, True or False, On or Off;

Ø OLE Object Field – an object created by another application;

Ø Substitution Wizard allows you to create a combo box to select values ​​from a database table.

The concept of a primary key. Database structure. Types of relationships between tables.

To find the desired record in the table, use search key method . It consists of finding a record using the value of one of its fields. This field is called key or simply key. If one of the keys unique , i.e. its values ​​are not repeated among all records in the table, then it is called primary key . This key always points to only one record, unlike other keys, which point to a specific set of records (possibly no records at all) and whose values ​​can be repeated. Typically, the primary key is a special numeric field, the value of which is automatically increased by the DBMS when adding records to the table. This field is called identifier .

Database structure create tables and connections between them - data schema.

Communication (relationship)) is set between two table fields, and these fields must be one type and one size .

Before setting connections tables need to be closed, since it is impossible to establish connections between open (editable) tables.

Table relationships in relational databases are: “ one to one" And " one-to-many».

Relation like " one to one"is established between two primary keys of different tables, each record of one table corresponds to one record of the other.

Relation like " one-to-many"- between the primary key of one table and the key of another table (the most widespread), each record of one table can correspond to several records of another. One table is defined as home, another - subordinate.