Abstract: Database. Database concept

Twitter

Lecture 11 – Databases (DB or in English DB) and their management systems:

Basic concepts of databases;

Basics of database design;

DBMS Microsoft Access and its main capabilities;

Creation of databases – tables and relationships between them.

1. Basic concepts of databases.

Database is a collection of structured and interconnected data, organized according to certain rules that provide general principles for describing, storing and processing data.

There are 4 main data models - lists (flat tables), relational databases, hierarchical and network structures.

For many years, flat tables (flat databases) such as lists in Excel were predominantly used. Currently, relational data models are most widely used in database development. The relational data model is a set of the simplest two-dimensional tables - relations (English relation), i.e. The simplest two-dimensional table is defined as a relation (a set of records of the same type united by one topic).

The name relational data model comes from the term relation. Relational databases use several two-dimensional tables in which rows are called records, A columns by fields, between the records of which connections are established. This method of organizing data allows data (records) in one table to be linked to data (records) in other tables through unique identifiers (keys) or key fields.

Basic concepts of relational databases: normalization, relationships and keys

1. Principles of normalization:

Each database table should not have duplicate fields;

Each table must have a unique identifier (primary key);

Each primary key value must have sufficient information about the entity type or table object (for example, information about academic performance, group or students);

Changing values in table fields should not affect information in other fields (except for changes in key fields).

2. Types of logical connection.

A relationship is established between two common fields (columns) of two tables. There are one-to-one, one-to-many, and many-to-many relationships.

one to one– each record from one table corresponds to one record in another table;

one - to - many– each record from one table corresponds to several records from another table;

many - to - one– multiple records from one table correspond to one record in another table;

many - to - many– multiple records from one table correspond to several records in another table.

The type of relationship in the created relationship depends on the method of defining the associated fields:

A one-to-many relationship is created when only one of the fields is a primary key or unique index field.

A one-to-one relationship is created when both fields being linked are key fields or have unique indexes.

A many-to-many relationship is actually two one-to-many relationships with a third table whose primary key consists of the foreign key fields of the other two tables

3. Keys. A key is a column (there can be multiple columns) added to a table that allows it to be linked to records in another table. There are two types of keys: primary and secondary (external).

Primary key– this is one or more fields (columns), the combination of values of which uniquely identifies each record in the table. The primary key does not allow Null values and must always have a unique index. A primary key is used to link a table to foreign keys in other tables.

Foreign (secondary) key is one or more fields (columns) in a table that contain a reference to a primary key field or fields in another table. A foreign key defines how tables are joined.

Of two logically related tables, one is called the primary key table or main table, and the other is called the secondary (foreign) key table or subtable. DBMSs allow you to compare related records from both tables and display them together in a form, report, or query.

There are three types of primary keys: counter (counter) key fields, simple key, and composite key.

Counter field(Data type “Counter”). A field data type in a database in which a unique numeric value is automatically entered into the field for each record added to the table.

Simple key. If a field contains unique values, such as codes or accession numbers, then this field can be defined as a primary key. Any field that contains data can be defined as a key, as long as the field does not contain duplicate or null values.

Composite key. In cases where it is impossible to guarantee the uniqueness of the values of each field, it is possible to create a key consisting of several fields. This situation most often occurs for a table that is used for a many-to-many relationship between two tables.

It must be noted again that The primary key field must contain only unique values in each row of the table, i.e. no match allowed,and in a secondary or foreign key field, matching values in table rows are allowed.

If you have difficulty choosing the appropriate type of primary key, then it is advisable to select the counter field as the key.

Programs that are designed to structure information, place it in tables and manipulate data are called database management systems (DBMS): MS SQL Server, Oracle, Informix, Sybase, DB2, MS Access, etc.

Annotation: The lecture discusses the general meaning of the concepts of database (DB) and database management system (DBMS). Basic concepts related to the database are given, such as algorithm, tuple, object, entity. Basic requirements for a data bank. Definitions of DB and DBMS.

Purpose of the lecture: Understand the difference between a database and a database management system. Familiarize yourself with the basic requirements for a data bank and the basic definitions related to databases and DBMSs.

Let's consider the general meaning of the concepts of database (DB) and database management system (DBMS).

From the very beginning of development computer technology Two main directions for its use have emerged.

The first direction is the use of computer technology to perform numerical calculations that take too long or are impossible to perform manually. The emergence of this direction contributed to the intensification of methods for numerically solving complex mathematical problems, the development of a class of programming languages focused on convenient recording numerical algorithms, establishing feedback with developers of new computer architectures.

The second direction is the use of computer technology in automatic or automated information systems. In the broadest sense, an information system is software package, the functions of which are to support reliable storage of information in computer memory, perform application-specific transformations of information and/or calculations, and provide users with a convenient and easy-to-learn interface. Typically, the volumes of information that such systems have to deal with are quite large, and the information itself has a rather complex structure. Classic examples of information systems are banking systems, systems for reserving airline or train tickets, hotel rooms, etc.

In fact, the second direction arose somewhat later than the first. This is due to the fact that in the early days of computing, computers had disabilities in terms of memory. It is clear that we can talk about reliable and long-term storage of information only if there are storage devices that retain information after turning off the electrical power. RAM usually does not have this property. In the beginning, two types of external memory devices were used: magnetic tapes and drums. At the same time, the capacity of magnetic tapes was quite large, but by their physical nature they provided sequential access to the data. Magnetic drums (they are most similar to modern magnetic disks with fixed heads) allowed random access to data, but were of limited size.

It is easy to see that these restrictions are not very significant for purely numerical calculations. Even if a program must process (or produce) a large amount of information, when programming, you can think about the location of this information in external memory so that the program runs as quickly as possible.

On the other hand, for information systems in which the need for current data is determined by the user, the presence of only magnetic tapes and drums is unsatisfactory. Imagine a ticket buyer who, standing at the ticket office, must wait until the magnetic tape is completely rewinded. One of the natural requirements for such systems is the average speed of operations.

It was the requirements for computing technology from non-numerical applications that caused the emergence of removable magnetic disks with movable heads, which was a revolution in the history of computing. These external memory devices had a significantly larger capacity than magnetic drums, provided satisfactory speed of data access in random access mode, and the ability to change the disk package on the device made it possible to have an almost unlimited data archive.

With the advent of magnetic disks, the history of data management systems in external memory began. Previously, each application program that needed to store data in external memory itself determined the location of each piece of data on a magnetic tape or drum and performed exchanges between RAM and external memory using low-level firmware (machine instructions or calls to the corresponding programs operating system). This mode of operation does not allow or makes it very difficult to maintain several archives of long-term stored information on one external storage device. In addition, each application program had to solve problems of naming parts of data and structuring data in external memory.

A historic step was the move to file management systems. From point of view application program file is named area external memory into which data can be written to and read from. Rules for naming files, how data stored in a file is accessed, and the structure of that data depend on the specific file management systems and possibly depending on the file type. File management system takes care of allocating external memory, mapping file names to appropriate external memory addresses, and providing access to data.

Any information processing and decision-making task can be represented in the form of a diagram shown in Fig. 1.1.

Rice. 1.1.

Definition of Key Terms

Let us define the main terms. As components The diagram identifies information (input and output) and the rules for its transformation.

Rules can be in the form of algorithms, procedures and heuristic sequences.

Algorithm	- sequence of rules for transition from initial data to result. The rules can be executed by a computer or a human.
Data	- a set of objective information.
Information	- information previously unknown to the recipient of information, adding to his knowledge, confirming or refuting provisions and corresponding beliefs. Information is subjective in nature and is determined by the level of knowledge of the subject and the degree of his perception. Information is extracted by the subject from the relevant data.
Knowledge	- a set of facts, patterns and heuristic rules with the help of which the task is solved.

The sequence of data processing operations is called information technology (IT). Due to the significant amount of information in modern challenges it must be ordered. There are two approaches to ordering.

Data is associated with a specific task (array technology) - organized by use. At the same time, algorithms are more mobile (can change more often) than data. This necessitates the reordering of data, which can also be repeated in different tasks.
In this regard, another widely used database technology has been proposed, which is storage ordering.

Under database (DB) understand a collection of data stored together with such minimal redundancy that it can be used optimally for one or more applications. Purpose creating databases, like varieties information technology and forms of data storage, is the construction of a data system that does not depend on the adopted algorithms (software) used technical means and physical location of data in the computer; providing consistent and holistic information for unregulated requests. The database assumes its multi-purpose use (several users, many forms of documents and queries of one user).

Knowledge Base (KB) is a set of databases and used rules received from decision makers (DMs).

Along with the concept of “database,” there is the term “data bank,” which has two interpretations.

Data is currently being processed decentralized(at workplaces) using personal computers (PCs). Initially, centralized processing on large computers was used. Due to centralization, the database was called a data bank and therefore often no distinction is made between databases and data banks.
Database- database and its management system (DBMS). A DBMS (for example, FoxPro) is an application for creating databases as a collection of two-dimensional tables.

Data Bank (BnD)	is a system of specially organized data, software, language, organizational and technical tools designed for centralized accumulation and collective multi-purpose use of data.
Databases (DB)	is a named collection of data that reflects the state of objects and their relationships in the subject area under consideration. A characteristic feature of databases is persistence: data is constantly accumulated and used; the composition and structure of data, necessary for solving certain applied problems, are usually constant and stable over time; individual or even all data elements may change - but these are also manifestations of constancy - constant relevance.
Database management system (DBMS)	is a set of language and software tools designed for creating, maintaining and sharing a database with many users.

Sometimes archives are included as part of a data bank. The basis for this is a special mode of data use, when only part of the data is under the operational control of the DBMS. All other data is usually located on media that is not operationally managed by the DBMS. The same data at different points in time can be included in both databases and archives. Data banks may not have archives, but if they do, the data bank may also include an archive management system.

Effective management external memory are the main function of the DBMS. These usually specialized tools are so important from an efficiency point of view that without them, the system simply will not be able to perform some tasks simply because they will take too much time to complete. However, none of these specialized functions are visible to the user. They provide independence between the logical and physical levels of the system: the application programmer does not have to write indexing programs, allocate disk memory, etc.

Basic requirements for data banks

The development of the theory and practice of creating information systems based on the concept of databases, the creation of unified methods and means of organizing and retrieving data make it possible to store and process information about increasingly complex objects and their relationships, providing the multidimensional information needs of different users. The basic requirements for data banks can be formulated as follows:

Reuse of data: users must be able to use the data in a variety of ways.
Simplicity: Users must be able to easily find out and understand what data is available to them.
Ease of use: users should be able to access data in a (procedurally) simple manner, with all the complexities of data access hidden within the database management system itself.
Flexibility of use: accessing or searching for data should be done using various methods access.
Fast processing of data requests: queries for data must be processed using a high-level query language, not just application programs written for the purpose of processing specific queries.
Interaction language end users with the system must provide end users with the ability to obtain data without the use of application programs.

The database is the basis for the future growth of application programs: databases should enable the rapid and cheap development of new applications.

Saving mental labor costs: existing programs And logical structures data should not be altered when changes are made to the database.
Availability of application programming interface: Application programs must be able to perform data queries simply and efficiently; programs must be isolated from file locations and addressing methods data.
Distributed data processing: the system must operate in conditions computer networks and ensure efficient user access to any distributed database data located anywhere in the network.
Adaptability and extensibility: the database must be configurable, and customization should not cause overwriting of application programs. In addition, the set of predefined data types supplied with the DBMS must be extensible - the system must have tools for defining new types and there should be no differences in the use of system and user-defined types.
Data integrity control: the system must monitor errors in the data and check the mutual logical consistency of the data.
Data recovery after failures: automatic recovery without losing transaction data. In the event of hardware or software failures, the system must fall back to some consistent data state.
Aids must allow the developer or
Linguistic means;
Software;
Technical means;
Organizational and administrative subsystems and normative and methodological support.

Organizational and methodological means is a set of instructions, methodological and regulatory materials, descriptions of the structure and procedure for the user to work with the DBMS and database.

Database and DBMS users

Users (DBMS) can be divided into two main categories: end users; database administrators.

We especially need to talk about the Database Administrator (DBA). Naturally, the database is built for the end user (UC). However, it was initially assumed that the CPs would not be able to work without a specialist programmer, who was called a database administrator. With the advent of DBMSs, they took over a significant part of the functions of DBAs, especially for databases with a small amount of data. However, for large centralized and distributed databases, the need for a DBA remains. In broad terms, DBAs are understood as system analysts, designers of data structures and information support, designers of processing technology, system and application programmers, operators, subject matter and software specialists. maintenance. In other words, in large bases data, these could be teams of specialists. The responsibilities of the ADB include:

analysis of the subject area, information status and users;
designing structure and modifying data;
setting and ensuring integrity;
data protection;
ensuring database recovery;
collection and statistical processing of requests to the database, analysis of the efficiency of the database;
work with the user.

Brief summary

A database (DB) is a named collection of data that reflects the state of objects and their relationships in the subject area under consideration.

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

The main requirements for data banks: reuse of data, simplicity, ease of use, flexibility of use, fast processing of data requests, interaction language.

Users (DBMS) can be divided into two main categories: end users; database administrators.

Self-test questions

Define a database.
Define a data bank.
Name two interpretations of the data bank.
What is a database management system?
Basic requirements for a data bank.
What is data, information, knowledge?
DBMS and database users?
Basic functions of a database administrator.
What makes it possible to quickly and cheaply develop new applications?

Working with Access DBMS

Laboratory work No. 1. Introduction to the Access DBMS

Goal of the work: mastering the techniques of working in the process of creating a database in MS Access (analysis of the subject area, data analysis, building a data model, creating a structure and filling out database tables).

Basic Database Concepts

The basis of many information systems (primarily information and reference systems) are databases.

Database - a set of related data, organized according to certain rules, providing general principles of description, storage and manipulation, independent of application programs, intended for long-term storage in external computer memory, constant update and use.

In most cases, a database can be considered as an information model of some real system, for example, the book collection of a library, the personnel of an enterprise, educational process at school and so on. Such a system is called subject area database and information system to which it is included.

Classification by data storage method divides the database into centralized and distributed.

All information and centralized database stored on one computer. This can be a standalone PC or a network server that can be accessed by client users. Distributed databases used in local and global computer networks. In the latter case, different parts of the database are stored on different computers.

There are three types of data structure: hierarchical, network and tabular. Accordingly, based on the structure of the database, they are divided into hierarchical databases, network databases and relational (tabular) databases.

The word "relational" comes from the English relation- attitude. Attitude- a mathematical concept, but in the terminology of data models, relationships are conveniently depicted in the form of a table.

IN Lately Relational databases have become the most common type of database. It is known that any data structure can be reduced to tabular form.

A structured representation of data is called data model. The main information unit of a relational database is table. Relational databases use tabular data model. The database can consist of one table - a single-table database, or many interrelated tables - a multi-table database.

The structural components of the table are records and fields.

Each record contains information about a separate object of the system: one book in the library, one employee of the enterprise, etc. And each field- this is a certain characteristic (property, attribute) of objects: the title of the book, the author of the book, the last name of the employee, year of birth, etc. The table fields must have non-matching names.

In this case, the table rows correspond tuples relations, and the columns are attributes. Key name any function of the attributes of a tuple that can be used to identify the tuple. Such a function can be the value of one of the attributes (simple key), specified by an algebraic expression that includes the values of several attributes (composite key). This means that the data in the rows of each of the columns of a composite key may be repeated, but the combination of data in each row of those columns is unique.

For each relational database table, a master key- the name of a field or several fields, the totality of whose values uniquely identify the record. In other words, the value of the master key should not be repeated in different records.

To represent the row structure of a table, the following form is used:

Table_name (FIELD_NAME_1, FIELD_NAME_2, ....)

The names of the fields that make up the main key are underlined.

Each table field has a specific type.

Type is the set of values that a field can take and the set of operations that can be performed on those values. There are four main types for database fields: character, numeric, logical And date of.

Software designed to work with data sets is called a database management system - DBMS.

The most widely used on personal computers are relational databases that use tabular presentation of data.

The main actions that a user can perform using the DBMS:

Creating a database structure;

Filling the database with information;

Changing (editing) the structure and content of the database;

Searching for information in the database;

Data sorting;

Database protection;

Checking the integrity of the database.

Conclusion

A database is an organized collection of data intended for long-term storage in external computer memory, regular updating and use.

A database is an information model of a specific subject area.

Classification of databases is possible according to the nature of the information: factual and documentary databases; by data structure: hierarchical, network, relational databases; by data storage method: centralized and distributed databases.

Relational databases (RDBs) are the most common type of database that uses a tabular representation of data.

Relational database- database based on relational model.

Basic concepts of organizing data in a RDB: table, record, field, field type, main table key.

DBMS (database management system) is software for working with databases.

Basic information about the database. Concepts: Database, Subject area, Data structuring, Database management systems.

Database (DB)- a structured, organized set of data describing the characteristics of any physical or virtual systems.

“Database” is often simplified or mistakenly called Database Management Systems (DBMS). It is necessary to distinguish between a set of data (the database itself) and software designed to organize and maintain the database (DBMS).

The main purpose of information systems is to promptly provide the user with information about the outside world by implementing a question-answer relationship. Question-answer relationships, receiving interpretation in the outside world (the world outside the information system), make it possible to select for the information system a certain fragment of it - a subject area - which will be embodied in an automated information system. Information about the outside world is presented in the information system (IS) in the form of data. This limits the possibilities of semantic interpretation of information and specifies the semantics of its presentation in the IS. The totality of these data allocated to the IS, the connections between them and operations on them forms an information and functional model of the subject area that describes its state with a certain accuracy.

Data structuring– agreement on the method of data presentation.

OBD control systems - specialized program(usually a set of programs) designed to organize and maintain a database. To create and manage information system A DBMS is necessary to the same extent as a translator is needed to develop a program in an algorithmic language.

Main functions of the DBMS:

· data management in external memory (on disks);

· data management in random access memory using disk cache;

· logging changes, backup and database recovery after failures;

· support for database languages (data definition language, data manipulation language).

Typically, a modern DBMS contains the following components:

core, which is responsible for managing data in external and RAM memory and logging,

database language processor, providing optimization of requests for retrieving and changing data and creating, as a rule, machine-independent executable internal code,

runtime support subsystem, which interprets data manipulation programs that create user interface with DBMS

and service programs(external utilities) providing a number of additional capabilities for maintaining the information system.

DBMS classification

By data model

Based on the type of database being managed, DBMSs are divided into:

· Network

Hierarchical

· Relational

Object-relational

Object-oriented

According to the architecture of data storage organization

local DBMS (all parts of the local DBMS are located on one computer)

· distributed DBMS (parts of the DBMS can be located on two or more computers)

2. Classification of databases according to the method of data access.

By method of accessing the database

File servers

In file server DBMSs, data files are located centrally on a file server. The DBMS kernel is located on each client computer. Data is accessed via a local network. Synchronization of reads and updates is carried out using file locks. The advantage of this architecture is the low load on the server CPU, but the disadvantage is high load local network.

At the moment, file server DBMSs are considered obsolete.

Examples: Microsoft Access, Borland Paradox.

Client-server

Such DBMSs consist of a client part (which is part of the application program) and a server (see Client-server). Client-server DBMSs, unlike file-server ones, provide access control between users and have little load on the network and client machines. The server is a program external to the client, and can be replaced by another if necessary. The disadvantage of client-server DBMS is the very fact of the existence of the server (which is bad for local programs- they have more convenient embedded DBMS) and large computing resources consumed by the server.

Examples: Firebird, Interbase, MS SQL Server, Sybase, Oracle, PostgreSQL, MySQL.

Built-in

An embedded DBMS is a library that allows you to store large amounts of data on a local machine in a unified manner. Data can be accessed through SQL or through special DBMS functions. Embedded DBMSs are faster than conventional client-server systems and do not require server installation, therefore they are in demand in local software that deals with large volumes data (for example, geographic information systems).

The most important target designing an information model - developing a consistent structured interpretation of reality existing information the subject area being studied and the interaction between its structural components.

The concept of a conceptual data model is associated with the methodology of semantic data modeling, i.e. presenting data in the context of its relationships with other data. The main objects of the conceptual model are entities and relationships.

Essence- some isolated object or event of the modeled system, which has a certain set of properties - attributes. An individual element of this set is called an "entity instance". An entity may have one or more attributes that uniquely identify each instance of the entity, and may have any number of relationships to other entities.

Rules for entity attributes:

· Each attribute must have a unique name.

· An entity can have any number of attributes.

· An entity can have any number of inheritable attributes, but the inherited attribute must be part of the parent entity's primary key.

· For each instance of an entity, there must be a value for each of its attributes (the Not Null rule).

· No instance of an entity can have more than one value for its attribute.

When building a database:

1. determine the GOAL

2. define functions

External level– what needs to be presented in a structured form;

Conceptual design– information objects are lined up and connected with each other + external level

3. convert conceptual model into the database model.

Relationships between objects:

1:1, 1:to many, many to many.

Data Models

· Network

· Hierarchical

· Relational

· Object-relational

· Object-oriented\

Network: to basic concepts network model databases include: level, element (node), connection.

Knot is a set of data attributes that describe an object. In a hierarchical tree diagram, nodes are represented as vertices in the graph. In a network structure, each element can be connected to any other element.

Network databases are similar to hierarchical databases, except that they have pointers in both directions that connect related information.

Although this model solves some of the problems associated with the hierarchical model, executing simple queries remains quite complex.

Also, since the logic of the data retrieval procedure depends on the physical organization of this data, this model is not completely independent of the application. In other words, if the data structure needs to be changed, then the application needs to be changed.

Hierarchical: consists of objects with pointers from parent objects to children, connecting related information together.

Hierarchical databases can be represented as a tree consisting of objects at different levels. The top level is occupied by one object, the second - by objects of the second level, etc.

There are connections between objects; each object can include several lower-level objects. Such objects are in the relationship of an ancestor (an object closer to the root) to a child (an object of a lower level), and it is possible that an ancestor object has no descendants or has several of them, while a descendant object must have only one ancestor. Objects that have a common ancestor are called twins.

Relational: The concept of relation is associated with the developments of the famous English specialist in the field of database systems, Edgar Codd.

These models are characterized by simplicity of data structure, user-friendly tabular representation and the ability to use the formal apparatus of relational algebra and relational calculus for data processing.

The relational model focuses on organizing data in the form of two-dimensional tables. Each relational table is a two-dimensional array and has the following properties:

Each table element is one data element

· all columns in the table are homogeneous, that is, all elements in the column have the same type (numeric, character, etc.)

Each column has a unique name

There are no identical rows in the table

· the order of rows and columns can be arbitrary

The basic concepts of relational DBMS are: 1) attribute 2) relationships 3) tuple

Relational database model

Relational data model is a set of interconnected two-dimensional tables. Each table contains information about homogeneous database objects and has the following properties:

Each table element represents one data element;

Elements of one column are homogeneous;

Each column has a unique name;

The table does not contain two or more identical rows;

The order of rows and columns is arbitrary.

Such tables are called relational. Data can be retrieved from multiple tables simultaneously. This is possible if you establish connections between the tables. Tables are linked to each other in order to ultimately reduce the size of the database. Each pair of tables is connected if they have identical columns.

Relational table row- is called a record, and columns are called fields. A record represents one instance of an information object. The field reflects some property of this object. Each field is characterized by: a name; type; size.

A key can be used to uniquely identify each entry. A key can consist of one or more fields in a record. If a key consists of several fields, it is called composite. The key must be unique and uniquely identify the entry. Using the key value, you can find a single record. Keys also serve to organize information in the database.

The following operations are possible on relational tables:

Merge tables with the same structure. The result is a common table: first the first, then the second (concatenation).

Intersection of tables with the same structure. Result – those records that are in both tables are selected.

Subtracting tables with the same structure. Result - those records are selected that are not in the subtracted one.

Sample (horizontal subset). Result – records that meet certain conditions are selected.

Projection (vertical subset). The result is a relation containing some of the fields from the source tables.

Cartesian product of two tables. The resulting table's records are obtained by concatenating each record of the first table with each record of the other table.

Relational database tables must meet the requirements for normalizing relationships.

Logic functions

IIF(condition, value_if_true, value_if_false). Queries can produce a generalized group field value in the same way as a single field value. This is done using aggregate functions. Aggregate functions produce a single value for an entire table group. There is a list of these functions: fields.

QBE selection queries.

Select queries do not change the contents of the database; they only serve to display data that meets specified conditions. Sample requests can be of the following types:

Simple sample request;

Request with parameter;

Request with results;

Cross request;

A query with a calculated field.

A simple select query is designed to retrieve data from one or more tables and display it in Datasheet view.

The simple request form contains six lines:

Field name;

Table name;

Sorting;

Display (indicates whether the field will be present in the dynamic data set);

Selection condition (contains the first condition limiting the data set);

Or (contains other data restriction terms).

Developing a simple query is carried out in several stages:

Table selection;

Selecting fields (adding fields to a request);

Establishment of selection criteria;

Setting the order of records (sorting).

A cross-query calculates the sum, mean, cardinality, and other statistical functions, grouping the data and displaying it in a compact form that resembles a pivot spreadsheet.

A cross-query is created using the appropriate wizard or in the query designer. The request form specifies which field values will be used in calculations or as row and column headings.

Cross request is a special type of group request. Line Group operation must be included. The request must be set to: at least three parameters– row header field, column header field and value selection field. Fields used as rows and columns must contain the Group function in the Group Operation row. To create a request, you must complete the following steps:

Create new request for the table(s), including the required fields in the layout;

Execute the QUERY/CROSS command;

In the Crosstab row, indicate which field is used as row headers, which field is used as column headings, and which field is used to perform calculations in accordance with the selected group operation;

In the Group operation line of the value field, you must select the resulting function.

Query with parameter is a query that prompts the user to enter data in a dialog box, such as a condition to return records or a value to be contained in a field. You can create a query that prompts you to enter multiple pieces of information, such as two dates. This will return all records between the specified two dates.

Queries with parameters are convenient to use as the basis for creating forms and reports. For example, you can create a monthly income report based on a query with parameters. When this report is displayed, a prompt will appear on the screen to enter the month whose income interests the user. After entering the month, the required report will be presented on the screen.

To create a request with a parameter, you must enter the text of the invitation to enter this in the Selection conditions line for a given field, enclosing it in rectangular brackets. You can set parameters for several fields or define several parameters for selection for one field using a multi-line condition entry together with logical operation"OR".

QBE queries - actions.

Executing the request- action leads to changes in the contents of the database. You should be careful when executing such queries, as careless use of these queries can lead to irreversible loss of information in the database. Therefore, Access automatically marks requests - actions in the database window with the symbol «!» .

When you create a query, Access creates a select query by default. If necessary, you can use query designer commands to specify a different query type.

There are 4 types of change requests:

- request to add;

- update request;

- removal request;

- request to create a table.

Request to add allows you to add records to the specified table, not only the current database, but also any other database. The structure of the query table record does not necessarily have to match the structure of the table to which records will be added. For example, a query record may have fewer fields if the fields in the receiving table are not required to be filled out. Field type mismatch is allowed if it is possible to convert the data type of one field to the data type of another field.

To create a request, you must complete the following steps:

Create a selection query and debug it (add tables whose field values will be used to add records);

Cancel the Display property for query fields;

Execute the REQUEST/ADD command to convert to an add request. In this case, the Addition line appears in the request form. Next, you need to include in the request form the fields whose data will be added to the receiving table. You can also enter conditions for selecting records to add.

Specify the name of the table where records will be added;

Execute the REQUEST/Run command.

If the receiving table contains a key field, then the added records must have the same key field (according to the database integrity conditions).

The technology for creating other types of requests - actions is similar.

Request for update allows you to change the group of records selected based on the selection criteria. You can specify one or more fields in an update request by doing necessary settings in the Update line. For a field to be updated, in the Update line, you must enter a value or expression that defines the new value. Once completed, a dialog box opens indicating the number of records updated.

Removal request allows you to delete records from one or more tables at the same time. A delete request deletes entire table records that meet the selection criteria, so if you want to delete the values of individual fields in a record, you must create an update request. As this query runs, Access displays the data that will be deleted. In order to be able to view all the fields of the records to be deleted, you should drag the “*” symbol from the first line of the list of fields of the table whose records you want to delete with the mouse into the first line of the request form, into the first free column. In this case, the name of the table will appear in this column in the Field line, and the From value will appear in the line named Delete.

Query to create a table creates a new table based on a dynamic data set. The new table retains the names, data types, and field sizes as they were in the underlying query tables. Other field properties are not inherited.

Types of forms

You can create the following types of forms in Access:

Column form or full screen form;

Tape form;

Tabular form;

Main/subform;

Pivot table;

Form - diagram.

A column form is a collection of input fields arranged in a certain way with their corresponding labels and controls. The form allows you to display the fields of only one record on the screen.

A ribbon form is used to display fields from multiple records. The fields are not necessarily arranged in a table, but a column is assigned to one field, and the field labels are arranged as column headings.

A tabular form displays data in table view.

The main/subform is a combination of a column form and a table form. It makes sense to create it when working with related tables that have a one-to-many relationship.

The PivotTable form is executed by the PivotTable Wizard. Excel tables based on Access tables and queries (the PivotTable Wizard is an object embedded in Access; to use it in Access you must have Excel installed). A pivot table is a crosstabulation of data in which the summary data is located at the intersection of rows and columns with the current parameter values.

Form with diagram. In Access, you can insert a chart created by Microsoft Graph into a form. Graph is an OLE embeddable application and can be launched from Access. You can work with an embedded chart in the same way as you would with any OLE object.

Designing forms

When you create a new form, the New Form dialog box appears, in which you should select:

Method for creating a form;

Data source (from the list).

Access offers the following ways to create a form:

1. Using Autoform. Autoform allows you to create three forms standard types: column, ribbon, table. In this case, all fields of the data source are inserted into the form.

2. Using the Form Wizard. During the dialogue with the user, the wizard creates a form of one of three standard types. In this case, user-selected fields from the data source are inserted into the form.

3. Using the form designer. The form is designed by the user in the form designer window.

The following technology is convenient when creating a new form: the form is created using an autoform or the form wizard, and then modified in design mode.

The source of the form data is one or more related tables and/or queries.

Form structure

The form consists of five main sections:

1. Form title. The contents of the form title area appear at the top of the form window.

2. Header. The contents of the header area appear after the header at the top of the screen on each form page (if the form is multi-page). Typically, the header area contains the table header (column headings).

3. Data area. The data area contains fields in which data is displayed.

4. Footer. The contents of the footer area (date, page no., etc.) are displayed on each screen page at the bottom of the form.

5. Form note. The contents of this area appear at the bottom of the last screen page of the form.

The form may contain all sections or only some of them.

Form properties

Like any Access object, a form has properties. The values of these properties determine appearance forms. The form's "Properties" window can be called up, for example, by right-clicking on the black square at the intersection of the rulers and from context menu select the PROPERTIES command.

The properties window of the selected object contains the following tabs:

Layout – properties that specify the layout of the form;

Data – properties that define the data source, data type, format, etc.;

Events – a list of events associated with the object;

All – a list of all properties.

Basic properties of the form:

Caption (this property is located on the LAYOUT tab) – specifies the name of the form, which is displayed in the title bar in the form window.

Default mode – determines the mode of opening the form (simple form, ribbon, table).

Allowed modes – the property specifies whether it is possible to switch from table mode to form mode and back using VIEW menu commands. The property can take the following values:

everything is possible;

table – not possible, only viewing in table mode is possible;

form – not possible, only viewing in form mode is possible.

Allow modification determines whether data can be changed through the form, i.e. sets the read-only status.

Allow deletion determines whether the user can delete data through the form.

Allow adding determines whether the user can add records through the form.

The data entry determines the form's opening mode. Can take the values "Yes" (the form opens only to add new records) and "No" (existing records are displayed in the form).

Record locking defines how a record is blocked and how it is implemented when two users try to change the same record.

The following properties determine whether the below will be output listed items in the form window:

Scroll bars;

Window menu button;

Window size button;

Window close button;

Window border type;

Contextual help button.

The record number field determines whether buttons for moving through records will be displayed in the form window.

Form controls

A control element is any form or report object that is used to display data on the screen, design, or execute macro commands. Controls can be bound, calculated, or free.

A bound (attached) control is attached to a field in the underlying table or query. When you enter a value in the associated control, the table field of the current record is automatically updated. The table field is the data source of the associated control.

A calculated control is created based on expressions. Expressions can use table or query field data, data from another form or report control, and functions.

Free controls are designed to display data, lines, rectangles, and pictures on the screen. Free controls are also called variables or memory variables.

All controls can be added to a form or report using the Controls toolbar, which appears when you work with the form or report.

The main controls are:

Caption is an element designed to display text. The inscription may consist of one or several lines. Is a free element. There are inscriptions that are free and attached to another element (signatures).

Free text is used to set headings and comments. Created by the "Inscription" button on the toolbar.

The signature is created simultaneously with the creation of the element to which it is attached. The signature is used in conjunction with fields, checkboxes, radio buttons, and lists.

Fields are elements designed to display data or enter data. Fields can be attached or free. The contents of free fields are not saved anywhere.

A field is the main control element when working with databases, as it allows you to display and edit data from database tables.

Adding a free field to the form is done using the "Field" button on the toolbar. Adding an attached field (linked to a table field) is done in design mode as follows:

In the "Form Designer" panel, select the "Field List" button;

From the displayed list of base table fields, select the desired field and drag it into the form data area. You can drag one or a selected group of fields.

You can enter calculated fields into the form. The calculated field is a free field. To create it, you need to select the Field button on the toolbar and insert it in the desired place on the form, and then enter the expression directly into the "File" element or as the value of the Data property. In a calculated field, the expression must begin with the "=" sign. Expressions can be entered manually or form expression builder.

Control elements Switches, Switches, Checkboxes. The operating principle of these controls is exactly the same; they differ only in appearance.

Elements are used to display boolean data and return a value (-1) to their associated table field if the button is true, and 0 otherwise.

You can enter a default value to display a specified state. if this value is not set, the element will be in the Null state, which corresponds to the False value.

Group – a control element designed to accommodate several switches, radio buttons or check boxes. Elements within a group function in harmony. The maximum number of elements is 4, and one element can be selected at a time. The group returns a number that corresponds to the number of the selected element.

The Checkbox and Switch controls can be used not only in a group, but also individually.

The checkbox can be associated with a Boolean field in the underlying table or query. If a checkbox is associated with a logical field in the base table, then the Checked/Unchecked state corresponds to the field values.

The checkbox can be a free element. In this case it is used in special dialog boxes to accept user input.

The Switch control element can be used in a similar way.

List boxes (List Box and Combo Box) are controls that allow you to select desired value from several (list). A list is a collection of rows containing data. Rows can contain one or more columns with headings.

The List control can be attached (linked) or free. The joined list passes the selected value to the base table/query field. A free list returns a value that is used in another element or to look up a record in the underlying table/query.

Lists are created using a wizard. Most of the properties of the List control element are generated automatically during the wizard. They can then be changed.

Basic properties of lists:

1. Data source type: table / query; list of values; list of fields; VBA function.

2. Data source – indicates the actual data source: for a table / query – the name of the table / query; for a list of values – the values of the list elements separated by “;” (for example, Gender – m;f).

3. Attached column – a field of the base table to which the list is attached.

4. Number of columns – the number of columns in the list. If the data source is a list of values, then the elements from the list are distributed into rows and columns.

5. Column width – specified as a numeric value using “;”. You can hide an attached list column if it contains multiple columns. To do this, you need to set the column width to 0. The value is not displayed when the list is displayed, but when you select a row, the value from the attached column appears in the base table field.

6. Number of Rows – Defines the maximum number of rows displayed in the combo box.

Buttons are a control element used to perform some action. To perform an action, the Button Press property of the button must be associated with some macro or event handling procedure.

The button is created by a wizard. The wizard will allow you to create 30 buttons different types and associates them with event handling procedures. The Caption property defines the text on the button. The Picture property defines the picture on the button.

Page breaks, Set of tabs - allow you to create multi-page forms. The most convenient way is to use the Tab Set element. With its help, a form is created whose pages are combined into one control. Switching between pages is done by selecting a tab.

When you add a Tab Set control to a form, it creates two tabs. You can add any controls to a tab except Tab Set. You cannot move other controls from other parts or pages of the form to the tab; you can only copy them.

You can change the size of the Tab Set element, the order and names of the tabs.

The Page Break control is used to specify horizontal breaks between controls on a form. To navigate through pages, use the PgUp and PgDn keys. When a Page Break element is inserted into a form, it is marked with a small dotted line on the left border of the form.

When creating a multi-page form, it is advisable to add headers and footers to the form.

Subforms are designed to display one form inside another. The primary form is called the main form. A subordinate form is one that is located inside the main one.

The subform is most convenient for displaying tables or queries that have a one-to-many relationship. In this case, the main form can only be displayed as a simple form, and the subform is usually displayed in tabular form. The main form can contain any number of subforms, as long as each subform fits within the main form. It is possible to create a subform of two nesting levels

You can create a subform:

By adding a Subform element to the form;

By dragging a form from the database window to another open form;

Master of subordinate forms.

Report structure

Main sections of the report:

Report title – printed at the beginning of the report on the title page, containing the title of the report;

Header – printed at the top of each page; usually contains column headers;

Group header – printed before processing the first record of the group; as a header it can contain the field by which grouping is performed;

Data Area – prints each record from the data source;

Group note – printed after the last group record has been processed; may contain summary data for records included in the group;

Footer – printed at the bottom of each page, may contain, for example, the date the report was printed, the report page number;

Report note – printed at the end of the report after all records have been processed, and may contain summary data for all records.

Designing a report

You can create a report using a wizard or in design mode. Both methods can be used. Wizards allow you to speed up the process of creating a report, then you can refine it in design mode. Report wizards allow you to create three types of reports: column report (simple), group / summary and post stickers.

Technology for creating a simple report in a column:

1). While on the REPORTS tab, click the CREATE button.

2). In the New Report window:

Select the Auto Report to Column tool;

Select a data source in the form of a table or query;

Click OK.

Technology for creating a multi-column report:

1). Create a simple columnar report.

2). Select the Page Setup command from the FILE menu. In the Page Setup dialog box, select the Columns tab and set:

In the Grid Options group, the number of columns that should be displayed on each page (Number of Columns field), line spacing width (Spacing field), distance between columns (Columns field);

In the Column Size group, the column width (Width field) and row height (Height field);

Send your good work in the knowledge base is simple. Use the form below

Students, graduate students, young scientists who use the knowledge base in their studies and work will be very grateful to you.

Posted on http://www.allbest.ru/

DATABASE SYSTEMS SECURITY

Llecture 1. Basic concepts of database systems

Database- a collection of data stored in accordance with a data schema, the manipulation of which is carried out in accordance with the rules of data modeling tools.

Database- a collection of data organized according to a conceptual structure that describes the characteristics of that data and the relationships between them, a collection of data that supports one or more areas of application.

DBMS- This is a set of programs and linguistic tools for general or special purposes that ensure the management, creation and use of a database.

DBMS classification

I. According to the data model.

1. Hierarchical;

2. Network;

3. Relational;

4. Object-oriented;

5. Object-relational;

II. By database access method.

1. File server;

2. Client-server;

3. Built-in.

Basic functions DBMS

1. Direct control files DB .

Providing the necessary structures with external memory, both for storing data directly included in the database, and for service purposes (for example, to speed up searches).

2. Control buffers operational memory .

DBMSs usually work with databases of significant size, and this size is always larger than the available RAM, so the only available way to increase performance speed is to buffer data in RAM, therefore, developed DBMSs support their own set of RAM buffers with their own buffer replacement discipline.

3. Control transactions .

Transaction- the sequence of operations on the database of the DBMS in question, as a single whole. Either the transaction is completed successfully and the DBMS records the changes in the database made by this transaction, or none of these changes are reflected in any way on the state of the database. The concept of a transaction is necessary to maintain the logical integrity of the database.

4. Journaling .

One of the main requirements for a DBMS is the reliability of data storage in external memory. Storage reliability means that the DBMS is able to restore the last consistent state of the database after any hardware or software failure. Usually, 2 types of hardware failures are considered: 1) soft failure, which can be interpreted as a sudden stop of the computer; 2) hard failure, which can be interpreted as a sudden computer failure, which is characterized by the loss of part of the information on external media. Magazine- a special part of the database, not available to users DBMS and maintained with special care, which receives records of all changes in the main part of the database.

5. Support for database languages.

To work with databases, special languages are used, called database languages. IN earlier versions The DBMS supported several languages, specialized in the functions they performed. Usually there were two: a language for highlighting database structures and a language for manipulating data. Nowadays such a language is TSQL.

Typical organization of a modern DBMS

1. Core .

Responsible for managing data in external memory, managing RAM buffers, transaction management and logging in the DBMS. You can highlight the kernel components: data manager, buffers, transactions, logs.

2. Compiler language .

The main function of a language compiler is to compile language statements under some controlled program.

3. Utilities DB .

They highlight procedures that are too expensive to perform using the database language, such as database loading, global integrity loading, and so on.

Lecture2. Introduction to the relational data model

The relational approach to database organization was pioneered in the late 1960s by Edgar Codd. In modern data, this approach is one of the most common.

The advantages of the relational approach are:

Based on a small number of intuitive abstractions that enable simple modeling of the most common problem domains. These abstractions can be precisely and formally defined. The theoretical basis of the relational approach is the apparatus of set theory and mathematical logic. The relational approach allows for non-navigational data manipulation without the need to know the specific physical structure of the database in external memory.

Basic concepts of relational databases

1. Data type;

3. Attribute;

4. Cortege;

5. Attitude;

6. Primary key.

Data type

Data values stored in a relational database are typed, that is, the type of each stored value is known. The concept of a data type in the relational model fully corresponds to the concept of data types in a programming language.

Domain

In general, a domain is determined by specifying some basic type data, which includes elements of the domain and arbitrary logical expression applied to an element of this data type (domain constraint). An element will only be a domain element if the domain constraint calculation returns TRUE. Each domain is associated with a Name unique among the names of all domains and the corresponding database.

Relation head, tuple, relation body, relation value, relation variable

The head of a relation diagram r relation (Hr) is a finite set , where A is the name of the attribute, T denotes the name of some base type or previously defined domain. By definition, all attribute names in the header are required to be distinct.

The tuple tr corresponding to the header Hr is the set of ordered triplets of the form: , where v must be valid value data type or domain.

The body Br is an arbitrary set of tuples tr.

The value Vr is the pair of sets Hr and Br. Header and data body.

Primary key

A primary key is a subset of which, at any time, the value of the primary key in any tuple in the body of a relation is different from the value of the primary key in any other tuple in the body of that relation. And no proper subset S has this property.

Fundamental Properties of Relationships

1. Absence of duplicate tuples. This property follows from the definition of the relation body as a set of tuples. In classical set theory, by definition, a set consists of different elements. It is from this property that each value has a primary key relationship. That is, a minimum set of attributes that are a subset of the header of a given relationship, the composite value of which uniquely defines the relation tuple. The concept of a primary key is extremely important in connection with the concept of database integrity.

2. Lack of ordering of tuples.

3. Lack of ordering of attributes.

4. Atomicity of attribute values. The values of all attributes are atomic. This follows from the definition of a domain as a potential set of values of a scalar type. That is, domain values cannot contain values with visible structure, including sets of values. The main thing about the atomicity of attribute values is that the relational DBMS should not provide the user with an explicit structure of internal data.

Data types

Whole numbers Character strings Money Numbers departments

Domains (except primary key)

Primary key And numbers prop-ov. Names Dimensions salaries Numbers departments

Attributes

Lecture 3. Relational algebra and normalization

Basic means of manipulating relational data components are defined by 2 basic mechanisms for manipulating relational data.

1) relational algebra based on set theory and based on mathematics. logic (on predicate calculus) relational calculus. In turn, two types of relational calculus are considered: domain and predicate calculus. All mechanisms have one important property: they are closed with respect to the concept of relation. This means that relational algebra expressions and relational calculation formulas are defined over relations relational base data and the result of their execution is also a relationship. Specific language of manipulation rel. databases are said to be relationally complete if any query expressed using a single expression is relational. algebra or a single relational calculus formula can be expressed using a single operator in that language.

2) Basic idea rel. algebra is that the relations of phenomena. sets, then the means of manipulating relations can be based on traditional set-theoretic operations, supplemented by some special operations specific to the database.

Set-theoretic operations include:

Union

intersection

difference

direct product

Special Operations:

relationship limitation

relationship projection

connecting relationships;

division of relations;

In addition, they include an assignment operation, which allows you to save the results of algebraic relations, and a renaming operation, which makes it possible to correctly form the title of the resulting relation.

General interpretation of rel. operations:

1) When performing the operation of combining two relations, a relation is produced that includes all tuples included in at least one of the operand relations.

2) The operation of intersection of two relations produces a relation that includes all tuples included in both operand relations.

3) A relation that is the difference of two relations includes all tuples included in the relation of the first operand such that none of them is included in the relation that is the second operand.

4) When performing the direct product of two relations, a relation is produced whose tuples are... (a concatenation of) the tuples of the first and second operand.

5) The result of restricting relations by some condition is a relation that includes tuples of the operand relation that satisfy this condition.

6) When performing a projection of relations onto a given set of attributes, ...... whose tuples are produced by taking the corresponding values, from the tuples of the operand relation.

7) When two relations are connected according to some condition, a resulting relation is formed, the tuples of which are a contentation (concatenation) of the tuples of the first and second relation and satisfy the condition.

8) The division operation has two operands: binary and unary relations. the resulting relation consists of a single-attribute value, including the value of the first attribute of the tuples, the first operand such that the set of values of the second attribute, with a fixed value of the second attribute, coincides with the set. values of the second operand.

9) The rename operation produces a relation whose body is the same as the body of the operand, but the attribute names are changed.

Features of set-theoretic operations of relational algebra. base given compilation relational

The concept of compatibility of relations about associations

Two relations are join-compatible only if they have the same headers; more precisely, this means that the headers contain the same set of attributes, and the same attributes are defined on the same domain. If two relations are almost compatible by union, that is, in everything except attribute names, then they can be made completely compatible by using the rename operation.

The concept of compatibility by taking an extended direct product. In the event that the set of names of these relations do not intersect. All 4 set-theoretic operations are rel. algebras are associative.

Special relational operations

Restriction operation. Requires two operands, constrained relations, and a simple conditional constraint.

a comp-on b - ....

a comp-on const - a literally specified constant

As a result of the constraint operation, a relation is produced whose header matches the header of the operand relation, and the body includes those tuples of the operand relation for which the constraint value is TRUE.

The operation of connecting relations requires the presence of two operands, the connected relations and the third operand, as in the operation of connecting relations, it has the same form. The result of a join operation is a relation obtained by performing the operation by constraining the direct product of relations a and b.

The operation of dividing relations. The result of dividing a by b is a unary relation consisting of tuples v such that in relation to the tuples set (w) includes many values...

Relational database design

When designing, two problems are solved: how to map subject-object domains into abstract data models so that this mapping does not contradict the semantics of the subject domain and is as good as possible; this problem is often called the problem of logical database design.

the second is how to ensure efficient execution of queries in the database. How, given the peculiarities of a specific subd, will the data be located in external memory and the creation of additional pages, for example indexes, will be required. This is a problem - a physical problem. database design

Database design using normalization is a classic approach in which the entire process, the entire design process, is reduced in terms of relational data models by the method of sequential relations to a satisfactory set of relation schemes. The design process is a process of normalizing patterns of relationships. Each trace the normal form has better properties than the previous one. In the theory of rel database, a trace of the following norms of forms is distinguished:

1 standard form

2 standard form

3 standard form

Normal Beuys-Codde form

4 normal form

5 normal form or normal projection form of a connection.

Basic properties of form norms:

Each sci-fi track is in some sense better than the previous one.

When moving to the next NF, the properties of the previous NF are preserved.

Definition 1. Functional dependence with respect to r, attribute y is functionally dependent on attribute x only if each value of x corresponds to exactly one value of y.

Complete functional dependence r(x) r(y) if y does not depend functionally on any exact value of x.

Transitive dependency if attribute Z exists, that there are functional dependencies...

A non-key attribute is any relation attribute that is not part of the primary key.

Mutually independent attributes (2 or more) - unless one of these attributes is functionally dependent on the others.

A relation is in 1nf when each of its tuple contains only one value for each of the attributes. In the relational model of relationships, they are all in normal form.

second normal form only if it is in first normal form and every non-key attribute is completely dependent on the primary key.

Third normal form. The relation r is found if it is found in the second and each non-key attribute is intransitively dependent on the primary key.

EMPLOYEE	DEPARTMENT	TELEPHONE
	ACCOUNTANT
	ACCOUNTANT
	SUPPLIER

Lecture 4. Language operatorsSQL

SELECT is used to retrieve any amount of data from one or more tables. In general, the result of executing a SELECT statement IS ANOTHER TABLE. This new table can again be subject to a SELECT operation, and so on.

1) An independent command to obtain and display rows in a table formed from columns and rows of one or more view tables

2) as an element of the WHERE or HAVING condition. This is called a false request

3) selection phrase in CREATE VIEW DECLARE CURSOR INSERT commands

4) Global assignment tool variable values and rows of the generated table. INTO

Has the following format.

SELECT [ select data and perform transformations in accordance with the specified expressions and or functions before outputting them

Element, -\\-) FROM enumeration.......

base table | representation],

Rows from the specified tables must comply with the specified list of row selection conditions

Phrase]

GROUP BY is an operation for grouping by a specified list of columns in order to obtain a single aggregated value for each group.

HAVING - condition for filtering by groups.

5. Simple sampling.

* serves as an alias for

selecting a specific field

Elimination of duplicates. To eliminate duplicates and at the same time order the list, a query with the DISTINCT keyword is used.

Sampling of calculated values.

The SELECT clause can contain not only a list of columns, but also an expression.

Selection using the WHERE clause. In the syntax of the selection phrase the required lines tables can be used basic operators comparisons. And the ability to use compound logical expressions.

WHERE P1 = 6 AND P2 = 8

You can use BETWEEN to select values in an interval.

BETWEEN is convenient to use when working with given specified intervals, the beginning and the end, which are located in different tables.

Using the IN operator.

IN (3, 4, 5)<=>P1= 3 OR P1 = 4 OR P1 = 5

Using LIKE

This operator allows you to find all values of a specified column that matches a pattern.

Replaces any single character.

% - replaces any sequence of n characters.

Involvement of undefined value (NULL). IS is used to check for an undefined value.

Sampling with ordering. The ORDER BY operator is used to sort data. Can be sorted in ascending order by default. The ASC keyword will allow you to sort in descending order.

Data Aggregation

There are a number of standard functions in SQL. Except for the special case COUNT *, each of these functions ... columns of some table and creates several values.

COUNT - number of values in the column

SUM - the sum of the values in the column

AVG - average value in a column

The column must contain numeric values. The argument of all functions except COUNT * is preceded by the keyword DISTINCT, indicating...

And COUNT* is used to count all rows in the table with duplicates.

If the GROUP BY clause is not used, then the SELECT can only include SQL functions or expressions containing such functions.

GROUP BY clause

Initiates a re-arrangement of the tables specified in FROM into groups, each of which has same values in the column specified in GROUP BY. This phrase does not imply sorting.

The HAVING clause plays the same role for groups as the WHERE clause does for strings. Those are used to exclude groups.

Posted on Allbest.ru

Abstract: Database. Database concept

1. Basic concepts of databases.

Definition of Key Terms

Basic requirements for data banks

Database and DBMS users

Brief summary

Self-test questions

Send your good work in the knowledge base is simple. Use the form below

Similar documents

Popular articles

Latest articles

Sections

Pages

Special projects

Contacts