Subd. basic database concepts

Useful tips

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 for description, storage and manipulation, independent of application programs, intended for long-term storage in external memory COMPUTER, 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 separate object systems: 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- a database based on a 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.

Annotation: The lecture discusses general meaning 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, formation 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 specific this application transformations of information and/or calculations, providing 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 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 the application program's point of view, a 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, as a type of information technology and a form of data storage, is the construction of a data system that does not depend on the adopted algorithms (software), the technical means used and the 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(PC). 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. Characteristic feature Databases are persistent: 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 logical and physical levels systems: 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 more and more complex objects and their relationships, providing 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 loss of 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 mean systems analysts, data structure designers, 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.

Basic requirements for data banks: reuse of data, simplicity, ease of use, flexibility of use, fast processing requests for data, 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?

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. Relational model data is a collection 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 value primary key there must be sufficient information about the entity type or about the table object (for example, information about academic performance, about a group or about 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.

Basic concepts about databases and DBMS

Parameter name	Meaning
Article topic:
Rubric (thematic category)	Connection

Lecture 3. Topic 4.3 Introduction to the organization of databases and database management systems.

1 Basic concepts about databases

2 Microsoft Access DBMS

Basic concepts about databases and DBMS

Information system(IS)- ϶ᴛᴏ system built on the basis computer equipment, designed for storing, searching, processing and transmitting significant amounts of information, having a certain practical scope of application.

Database- ϶ᴛᴏ IP, which is stored electronically.

Database (DB)– an organized collection of data intended for long-term storage in external computer memory, constant updating and use.

Databases are used to store and search large amounts of information. Database examples: Notebook, dictionaries, reference books, encyclopedias, etc.

Database classification:

1. According to the nature of the stored information:

- Factual – contain brief information about the described objects, presented in a strictly defined format (card indexes, for example: database of the library’s book collection, database of the institution’s personnel),

- Documentary – contain documents (information) of the different types: text, graphic, sound, multimedia (archives, e.g.: reference books, dictionaries, database of legislative acts in the field of criminal law, etc.)

2. By data storage method:

- Centralized (stored on one computer),

- Distributed (used in local and global computer networks).

3. According to the data organization structure:

- Relational (tabular),

- Non-relational.

The term “relational” (from the Latin relatio – relationship ) indicates that such a data storage model is built on the relationship of its constituent parts. Relational the database is essentially a two-dimensional table. Each row of such a table is usually called a record. The columns of a table are called fields: each field is characterized by its name and data type. DB field is a table column containing the values of a certain property.

Properties of the relational data model:

Each table element is one data element;

All table fields are homogeneous, ᴛ.ᴇ. have one type;

There are no identical entries in the table;

The order of records in the table should be arbitrary and can be characterized by the number of fields and data type.

Hierarchical It is customary to call a database in which information is ordered as follows: one element is considered the main element, the rest are subordinates. IN hierarchical In the database, records are arranged in a certain sequence, like the steps of a ladder, and data search can be carried out sequentially “descending” from step to step. This model characterized by such parameters as levels, nodes, connections. The principle of operation of the model is such that several nodes of a lower level are connected using a connection with one node of a higher level.

Node is an information model of an element located at a given level of hierarchy.

Properties of the hierarchical data model:

Multiple lower-level nodes are connected to only one node top level;

A hierarchical tree has only one vertex (the root) and is not subordinate to any other vertex;

Each node has its own name (identifier);

There is only one path from the root record to the more private data record.

The hierarchical database is the Windows Folder Directory, which you can work with by launching Explorer. Top level occupies the Desktop folder. At the second level there are the folders My Computer, My Documents, Network Neighborhood and Recycle Bin, which are descendants of the Desktop folder, being twins. In turn, the My Computer folder is an ancestor in relation to third-level folders, disk folders (Disk 3.5 (A:), C:, D:, E:, F:) and system folders(Printers, Control Panel, etc.).

Network It is customary to call a database in which horizontal links are added to the vertical hierarchical links. Any object must be master and subordinate.

A network database is actually The World Wide Web global computer network Internet. Hyperlinks link hundreds of millions of documents together into a single distributed network database data.

Software designed to work with databases is commonly called database management system(DBMS). DBMS are used for organized storage and processing large volumes information.

Database Management System(DBMS) - a system that provides search, storage, correction of data, generation of answers to queries. The system ensures data safety, confidentiality, movement and communication with other software.

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.

Modern DBMS make it possible to include not only text and graphic information, but also sound fragments and even video clips.

The ease of use of the DBMS allows you to create new databases without resorting to programming, but using only built-in functions. DBMS ensure the correctness, completeness and consistency of data, as well as convenient access to them.

Popular DBMS - FoxPro, Access for Windows, Paradox.

However, it is extremely important to distinguish between databases (DBs) themselves - ordered sets of data, and database management systems (DBMS) - programs that manage the storage and processing of data. Eg, Access application, included in the office software package Microsoft Office, is a DBMS that allows the user to create and process tabular databases.

Principles of designing control systems databases follow from the requirements that a database organization must satisfy:

- Productivity and availability. Requests from the user by the database are satisfied at the speed required to use the data. The user quickly receives data whenever he needs it.

- Minimum costs. Low cost of storing and using data, minimizing the cost of making changes.

- Simplicity and ease of use. Users can easily find out and understand what data is available to them. Access to data should be simple, exclusive possible mistakes from the user's side.

- Easy to make changes. The database can grow and change without disrupting existing uses of the data.

- Possibility of search. A database user can make a variety of queries regarding the data stored in it. To implement this, a so-called query language is used.

- Integrity. Modern databases can contain data shared by many users. It is very important that during the work the data elements and connections between them are not broken. At the same time, hardware errors and various types of random failures should not lead to irreversible data loss. This means that the data management system must contain a data recovery mechanism.

- Security and privacy. Data security means the protection of data from accidental or intentional access to it by unauthorized persons, from unauthorized modification (change) of data or its destruction. Privacy is defined as the right of individuals or organizations to decide when, how, and how much information should be shared with other individuals or organizations.

Below is an example of one of the most common database management systems - Microsoft Access is part of the popular Microsoft package Office - We will learn about basic data types, how to create databases, and how to work with databases.

Basic concepts about databases and DBMS - concept and types. Classification and features of the category "Basic concepts of databases and DBMS" 2017, 2018.

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 in accordance with conceptual structure, describing the characteristics of that data and the relationships between them, a collection of data that supports one or more application areas.

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 considerable size, and this size is always larger than the available one. random access memory, therefore the only one in an accessible way increasing performance speed is data buffering 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 relational databases data

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 a valid data type or domain value.

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 various elements. It is from this property that each value has a primary key relationship. That is, the minimum set of attributes that is a subset of the header this relationship, whose composite value 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 relational DBMS should not provide the user with an explicit internal data structure.

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 the expressions relational algebra and rel calculation formulas are defined over the relations of a relational database and the result of their execution is also a relation. 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 logical design DB.

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 TO THIS new table the SELECT operation can be applied again 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) A means of assigning values and rows of the generated table to global variables. 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 use basic comparison operators. 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 SQL standard features. 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 for all functions except COUNT * is preceded by 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

Triggers a reordering of the tables specified in the FROM into groups, each of which has the same values in the column specified in the 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

Subd. basic database concepts

Definition of Key Terms

Basic requirements for data banks

Database and DBMS users

Brief summary

Self-test questions

1. Basic concepts of databases.

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