Subd ms sql server description. Comparative characteristics of sql database

Facebook

Microsoft SQL Server 2008.

10.1 General structure of the DBMS

To better understand the principles of operation of modern DBMSs, let's consider the structure of one of the most common client-server DBMSs - Microsoft SQL Server 2008. Despite the fact that each commercial DBMS has its own distinctive features, information about how one of the DBMSs is structured is usually sufficient for a quick initial mastery of another DBMS. A brief overview of the capabilities of Microsoft SQL Server - 2008 was given in the section devoted to a brief overview of modern DBMS. In this section, we will consider the main points related to the structure of the corresponding DBMS (database architecture and software structure).

By the architecture (structure) of a database of a specific DBMS we mean the main data presentation models used in the corresponding DBMS as well as the relationships between these models.

In accordance with the various levels of data description discussed in “Various architectural solutions used in the implementation of multi-user DBMSs. A brief overview of DBMSs,” different levels of abstraction of the database architecture are distinguished.

Logic level (DBMS data model level) - a means of representing a conceptual model. Here, each DBMS has some differences, but they are not very significant. Note that different DBMSs have significantly different mechanisms for the transition from the logical to the physical level of presentation.

Physical layer (internal representation of data in computer memory - physical structure of the database). This level of consideration involves examining the database at the level of files stored on the hard drive. The structure of these files is a feature of each specific DBMS, incl. and Microsoft SQL Server.

Rice. 10.1.

10.2. Database architecture. Logic level

Let's consider logic level database views (http://msdn.microsoft.com). Microsoft SQL Server 2008 is a relational DBMS (data is presented in the form of tables). Thus, the main structure of the data model of this DBMS is tables.

Tables and data types

The tables contain data about all the entities in the conceptual database model. When describing each column (field), the user must determine the type of corresponding data. Microsoft SQL Server 2008 supports both traditional data types (character string with different representations, floating point number 8 or 4 bytes long, integer length 2 or 4 bytes, date and time, comments field, Boolean value, etc. ), as well as new data types. In addition, Microsoft SQL Server 2008 provides a special device for creating custom data types.

Let's look at a brief description of some new data types that significantly expand user capabilities (http://www.oszone.net).

hierarchyid data type

The hierarchyid data type allows you to create relationships between data elements in a table in order to specify a position in the hierarchy of relationships between the rows of the table. As a result of using this type of data in a table, the rows of the table can display a specific hierarchical structure that corresponds to the relationships between the data in that table.

Spatial data types

Spatial data is data that defines geographic locations and shapes, primarily on Earth. These could be landmarks, roads, or even the location of a company. SQL Server 2008 has geographic and geometry data types to work with this information. Data type geography works with information for a spherical earth. The spherical earth model uses the curvature of the earth's surface in its calculations. Position information is given by latitude and longitude. This model is well suited for maritime transport, military planning and short-term ground-based applications. This model should be used if the data is stored in the form of latitudes and longitudes.

Data type geometry works with a planar or flat earth model. In this model, the earth is considered to be a flat projection from a certain point. The flat earth model does not take into account the curvature of the earth's surface, so it is used primarily to describe short distances, such as in the database of an application describing the interior of a building.

Types geography And geometry are created from vector objects specified in Well-Known Text (WKT) or Well-Known Binary (WKB) formats. These are formats for transporting spatial data, described in the Open Geospatial Consortium (OGC) Simple Features for SQL Specifications.

Keys

Each table must have a defined primary key – the minimum set of attributes that uniquely identifies each record in a table. To implement a relationship between tables, an additional field (several fields) is included in one of the related tables - the primary key of another table. The additionally included field or fields in this case are called the foreign key of the corresponding table.

In addition to tables, the Microsoft SQL Server 2008 data model includes a number of other components. Let us give a brief description of the main ones.

Indexes

In “Using Formal Apparatus to Optimize Relationship Schemes,” the concept of an index was discussed. Here the concept of index is brought to logic level for user convenience. Indexes are created to speed up the search for the necessary information and contain information about the ordering of data according to various criteria. Indexing can be done on one or more columns. Indexing can be done at any time. An index contains keys built from one or more columns in a table or view. These keys are stored as a structure balanced tree, which supports quick search of rows by their key values in SQL Server.

Representation

A view is a virtual table whose contents are determined by a query. The view is formed based on the SELECT SQL query, generated according to the usual rules. Thus, the view is a named SELECT query.

Like a real table, a view consists of a collection of named columns and rows of data. Until a view is indexed, it does not exist in the database as a stored collection of values. Rows and columns of data are retrieved from tables specified in the query that defines the view and created dynamically when accessing the view. The view acts as a filter on the underlying tables that it references. A view-defining query can be initiated on one or more tables or other views in the current or other databases. Additionally, you can use distributed queries to define views of data from multiple heterogeneous sources. This is useful, for example, if you need to combine data structured in this way that belongs to different servers, each of which stores data from a specific department of the organization.

Assemblies

Assemblies are dynamic library files that are used in an instance of SQL Server to deploy functions, stored procedures, triggers, user-defined aggregations, and user-defined types.

Restrictions

Constraints allow you to specify the method by which the Database Engine automatically ensures database integrity. Constraints specify rules for allowing certain values in columns and are a standard mechanism for ensuring integrity. It is recommended to use constraints rather than triggers, rules, and defaults. Query Optimizer also uses constraint definitions to build high performance implementation plans requests.

Rules

Rules are another special mechanism designed to ensure database integrity, similar in functionality to some types of restrictions. Microsoft notes that when enabled, the use of restrictions is preferable for a number of reasons and may be removed in a future version.

Default values

Default values determine what values to populate a column with if no value is specified for that column when you insert a row.. The default value can be any expression whose result is a constant, such as a constant itself, a built-in function, or a mathematical expression.

Last update: 06/24/2017

SQL Server is one of the most popular database management systems (DBMS) in the world. This DBMS is suitable for a wide variety of projects: from small applications to large, highly loaded projects.

SQL Server was created by Microsoft. The first version was released in 1987. And the current version is version 16, which came out in 2016 and will be used in the current guide.

SQL Server has long been exclusively a database management system for Windows, but starting with version 16, it is also available on Linux.

SQL Server is characterized by such features as:

Performance. SQL Server is very fast.

Reliability and safety. SQL Server provides data encryption.

Simplicity. This DBMS is relatively easy to work with and administer.

The central aspect in MS SQL Server, as in any DBMS, is the database. A database is a repository of data organized in a specific way. Often the database physically represents a file on the hard drive, although this correspondence is not necessary. Database management systems or DBMSs are used to store and administer databases. And just MS SQL Server is one of such DBMS.

MS SQL Server uses a relational model to organize databases. This database model was developed back in 1970 by Edgar Codd. And today it is actually the standard for organizing databases.

The relational model involves storing data in the form of tables, each of which consists of rows and columns. Each row stores a separate object, and the columns contain the attributes of that object.

A primary key is used to identify each row within a table. The primary key can be one or more columns. Using a primary key, we can reference a specific row in a table. Accordingly, two rows cannot have the same primary key.

Through keys, one table can be linked to another, that is, relationships can be organized between two tables. And the table itself can be represented as a relationship.

To interact with the database, the SQL (Structured Query Language) language is used. The client (for example, an external program) sends a request in SQL using a special API. The DBMS properly interprets and executes the request, and then sends the execution result to the client.

SQL was originally developed by IBM for a database system called System/R. At the same time, the language itself was called SEQUEL (Structured English Query Language). Although neither the database nor the language itself were subsequently officially published, traditionally the term SQL itself is often pronounced as a “sequel.”

In 1979, Relational Software Inc. developed the first database management system, called Oracle, which used the SQL language. Due to the success of this product, the company was renamed Oracle.

Subsequently, other database systems that used SQL began to appear. As a result, in 1989, the American National Standards Institute (ANSI) codified the language and published its first standard. After this, the standard was periodically updated and supplemented. Its last update took place in 2011. But despite the existence of a standard, DBMS manufacturers often use their own implementations of the SQL language, which are slightly different from each other.

There are two varieties of the SQL language: PL-SQL and T-SQL. PL-SQL is used in DBMSs such as Oracle and MySQL. T-SQL (Transact-SQL) is used in SQL Server. In fact, this is why T-SQL will be considered within the current guide.

Depending on the task that the T-SQL command performs, it can be one of the following types:

DDL (Data Definition Language). This type includes various commands that create a database, tables, indexes, stored procedures, etc. In general, data is determined.

In particular, we can classify the following commands as this type:

CREATE : creates database objects (the database itself, tables, indexes, etc.)

ALTER: modifies database objects

DROP: Removes database objects

TRUNCATE: removes all data from tables

DML (Data Manipulation Language). This type includes commands for selecting data, updating it, adding it, deleting it - in general, all those commands with which we can manage data.

The following commands belong to this type:

SELECT: retrieves data from the database

UPDATE: updates data

INSERT: adds new data

DELETE: deletes data

DCL (Data Control Language / Data Access Control Language). This type includes commands that manage data access rights. In particular, these are the following commands:

GRANT: grants permissions to access data

REVOKE: revokes data access rights

The first version of Microsoft Servers SQL was introduced by the company back in 1988. The DBMS was immediately positioned as a relational one, which, according to the manufacturer, had three advantages:

stored procedures, thanks to which data retrieval was accelerated and its integrity was maintained in multi-user mode;
constant access for administration without disconnecting users;
an open server platform that allows you to create third-party applications that use SQL Server.

2005, codenamed Yukon with advanced scaling capabilities, was the first version to fully support .NET technology. Support for distributed data has improved, and the first reporting and information analysis tools have appeared.

Integration with the Internet made it possible to use SQL Servers 2005 as the basis for creating e-commerce systems with simple and secure access to data through popular browsers using the built-in Firewall. The Enterprise version supported parallel computing on an unlimited number of processors.

Version 2005 was replaced by Microsoft SQL Server 2008, which is still one of the most popular database servers, and a little later the next version appeared - SQL Servers 2012, with support for compatibility with the .NET Framework and other advanced information processing technologies and Visual Studio development environment. A special SQL Azure module was created to access it.

Transact-SQL

Since 1992, SQL has been the standard for accessing databases. Almost all programming languages use it to access the database, even if it seems to the user that he is working with the information directly. The basic syntax of the language remains the same to ensure compatibility, but each database management system vendor has tried to add additional features to SQL. It was not possible to find a compromise, and after the “standards war” there were two leaders left: PL/SQL from Oracle and Transact-SQL in Microsoft Servers SQL.

T-SQL procedurally extends SQL to access Microsoft Servers SQL. But this does not exclude the development of applications on “standard” operators.

Automate your business with SQL Server 2008 R2

Reliable operation of business applications is extremely important for modern businesses. The slightest database downtime can lead to huge losses. The Microsoft SQL Server 2008 R2 database server allows you to reliably and securely store information of almost unlimited volume, using management tools familiar to all administrators. Vertical scaling up to 256 processors is supported.

Hyper-V technology makes the most of the power of modern multi-core systems. Supporting multiple virtual systems on a single processor reduces costs and improves scalability.

Analyze your data

For fast real-time analysis of data streams, the SQL Server StreamInsight component is used, which is optimized for this type of task. It is possible to develop your own applications based on .NET.

Uninterrupted operation and data security

Support for optimal performance at any time is ensured by the resource regulator built into the server. The administrator can manage the load and system tools, set limits for applications on the use of processor and memory resources. Encryption functions provide flexible and transparent protection of information and maintain a log of access to it.

Unlimited database size

Data storage can be scaled quickly and securely. Users can use Fast Track Date Warehouse's pre-built templates to support disk arrays up to 48 TB. The basic configuration supports equipment from leading companies such as HP, EMC and IBM. Information compression functions according to the UCS 2 standard allow you to use disk space more economically.

Improving the efficiency of developers and administrators

New software wizards allow you to quickly resolve underutilized servers, improve control, and optimize performance without the need for external third-party expertise. Monitor application and database performance, find improvements in dashboards, and speed up updates and installations.

Tools for personal business analysis

There has never been a consensus among companies as to who should do analytics—IT departments or users directly. The system for creating personal reports solves this problem through modern tools for safe and effective construction, analysis and modeling of business processes. Direct access to databases in Microsoft Office and SharePoint Server is supported. Corporate information can be integrated with other types of content, such as maps, graphics, and videos.

Convenient collaboration environment

Empower your employees to access information, collaborate, and analyze data with the PowerPivot Excel spreadsheet application. The program allows you to analyze information and model business processes and publish reports for public access on the Internet or SharePoint.

For visual creation of internal reports, the Report Builder 3.0 system is offered, which supports many formats and a wide range of predefined templates.

Work with databases for free

The company provides small projects and novice developers with a special free version of Microsoft SQL Server Express. This includes the same database technologies as the "full" versions of SQL Server.

Visual Studio and Web Developer development environments are supported. Create complex tables and queries, develop Internet applications with database support, and get direct access to information from PHP.

Take advantage of the full power of Transact-SQL and the most advanced data access technologies of ADO.NET and LINQ. Stored procedures, triggers and functions are supported.

Concentrate on business logic elements, and the system will optimize the database structure on its own.

Create rich reports of any complexity. Use search capabilities, integrate reports with Microsoft Office applications, and add geographic location information to documents.

Applications being developed can work without a connection to the database server. Synchronization is performed automatically using proprietary transactional replication technology Sync Framework.

Administer your infrastructure using management policies for all databases and applications. Common operational scenarios reduce the time spent optimizing queries and creating and restoring enterprise-scale backups.

SQL Server 2008 R2 Express Edition is ideal for quickly deploying websites and online stores, programs for personal use, and small businesses. This is a great option to get started and learn.

Manage your databases using SQL Server Management Studio

Microsoft SQL Server Management is a specialized environment for creating, accessing, and managing databases and all elements of SQL Server, including reporting services.

The system combines in one interface all the capabilities of administration programs from earlier versions, such as Query Analyzer and Enterprise Manager. Administrators receive software with a large set of graphical development and management objects, as well as an expanded language for creating scripts for working with the database.

The Microsoft Server Management Studio code editor deserves special attention. It allows you to develop scripted scripts in Transact-SQL, program multidimensional data access queries and analyze them with support for saving results in XML. Creation of queries and scripts is possible without connecting to a network or server, with subsequent execution and synchronization. There is a wide selection of pre-installed templates and version control system.

The Object Browser module allows you to view and manage any built-in Microsoft Servers SQL objects across all servers and database instances. Easy access to the information you need is critical for rapid application development and version control.

The system is built on the Visual Studio Isolated Shell system, which supports extensible settings and third-party extensions. There are many communities on the Internet where you can find all the necessary information and code examples for developing your own data management and processing tools.

According to the research company Forrester Research, the Microsoft SQL Server 2012 database server was among the top three leaders in the corporate information storage market at the end of 2013. Experts note that the rapid growth of Microsoft's market share is due to the corporation's comprehensive approach to automating business processes. Microsoft SQL Server is a modern platform for managing and storing data of any type, complemented by analytics and development tools. Separately, it is worth noting the ease of integration with other company products, such as Office and SharePoint.

As noted above, the choice of a specific architecture for building an information system includes two main components: the choice of a server platform (selection of a server OS and DBMS) and the choice of platforms for client workstations. In this section, we will dwell in more detail on the features of choosing a specific DBMS. When choosing a database, it is very important to choose the database that best meets the requirements for the information system, i.e. it is necessary to decide which automation model is being implemented (automation of document flow or business processes). First of all, when choosing a DBMS, you need to take into account the following factors:

the maximum number of users simultaneously accessing the database;
client software characteristics;
server hardware components;
server operating system;
personnel qualification level.

Today there are a large number of different SQL database servers known. Let's take a closer look at the following four leading server DBMSs - Oracle8i, IBM DB2, Microsoft SQL Server and Informix - and compare their performance at each of the main stages of operation:

system configuration,
monitoring,
setting,
Query Processing,
development of server and client modules.

We will carry out this analysis taking into account the fact that the number of client places ranges from 50 to 500, and the management of the DBMS should be as efficient as possible. The research was carried out on a Pentium II-based server platform with 128 MB of RAM, equipped with a 13 GB disk with an EIDE interface in a RAID level 0 configuration (of course, it would be better to use a HDD with a SCSI interface). System management was entrusted to Windows NT Server 4.0. and Linux.

Oracle8i.

Oracle8i package, endowed with the most advanced set of functions for working with the Java language and accessing data via the Internet, and a system for optimizing simultaneous access. The only drawback of this DBMS is the complexity of administration, however, all the costs of its implementation and development will subsequently be paid off by efficient and reliable operation. In our country, for many years now, a number of specialists have cultivated a negative attitude towards the Oracle DBMS as an expensive and complex DBMS. Both of these theses are controversial. Firstly, the level of complexity is a relative concept. When using the Oracle DBMS on the NT platform, it will require almost the same effort as when using MS SQL. In the case of working on a UNIX platform, we can confidently note that for professional Unix users the Oracle environment is simple, understandable and accessible. As for the high cost, there have been positive changes here too. In addition to the fact that Oracle offers a number of different scalable solutions depending on the number of clients served, it also, following global trends, developed a version of its most popular DBMS for LINUX and posted it on its WEB server (www.oracle.com) for free use. Among the main properties of the Oracle DBMS, the following should be noted:

Highest reliability.
The ability to split large databases into sections (large-database partition), which makes it possible to effectively manage gigantic gigabyte databases;
Availability of universal information security tools;
Effective methods for maximizing request processing speed;
Bitmap indexing;
Free tables (in other DBMSs all tables are filled immediately upon creation);
Parallelization of operations in a request.
Availability of a wide range of development, monitoring and administration tools.
Focus on Internet technology.

Solutions that are not inferior to Oracle's developments can only be found in DB2 from IBM. Focus on Internet technology is the main motto of modern Oracle products. In this regard, we can note the interMedia packages, which provide data processing in multimedia formats, and Jserver, a built-in tool for working with the Java language, which combines the capabilities of the Java language with the capabilities of relational databases (the ability to compose not only internal database programs in Java ( stored procedures and triggers), but also to develop Enterprise JavaBeans components and even run them on the server). Enterprise JavaBeans components are the basic modules that make up Internet applications in the Java language.

Oracle adheres to the principle that all important functions must be managed from a single center, so the proposed interMedia module provides users with the most advanced capabilities for working with multimedia objects:

Very developed tools for processing audio clips;
Still images;
Video clips;
Geographic data (with a whole set of functions related to location determination included in the Locator module).

Oracle8i implements today's best tools for object-oriented database design, including table structures that allow inheritance of properties and methods of other table database objects, which will avoid errors when constructing databases and facilitate their maintenance.

It should also be noted that the multiversioning concurrency optimization system developed by Oracle is one of the most important characteristics of the Oracle architecture (a similar function is only available in the InterBase DBMS from InterBase from Inprise). This function eliminates the situation when one user has to wait for another to complete changes to the contents of the databases (i.e., there are no read locks in Oracle). This feature allows Oracle8i to complete more transactions per second per user than any other database. In terms of performance level when working in a WEB environment under LINUX, Oracle takes an honorable second place after the MySQL DBMS, while significantly surpassing all other DBMSs in reliability and security.

Microsoft SQL Server DBMS

The most important characteristics of this DBMS are:

ease of administration,
ability to connect to the Web,
performance and functionality of the DBMS server mechanism,
availability of remote access tools,

The set of administrative management tools for this DBMS includes a whole set of special wizards and tools for automatically setting configuration parameters. Also, this database is equipped with excellent replication tools that allow you to synchronize PC data with database information and vice versa. The included OLAP server makes it possible to save and analyze all the data available to the user. In principle, this DBMS is a modern, fully functional database that is ideal for small and medium-sized organizations. It should be noted that SQL Server is inferior to other DBMSs under consideration in two important indicators: programmability and operating tools. When developing client database applications based on Java and HTML languages, the problem of insufficient SQL Server software often arises and using this DBMS will be more difficult than DB2, Informix, Oracle or Sybase systems. The global trend in the 21st century has become an almost universal transition to the LINUX platform, and SQL Server operates only in the Windows environment. Therefore, using SQL Server is advisable, in our opinion, only if the ODBC standard is used exclusively to access the contents of the database, otherwise it is better to use other DBMSs.

The IBM DB2 DBMS is the result of almost 30 development and research efforts by IBM. The latest version of this DBMS (6.x) is distinguished by one of the most thoughtful sets of management and optimization tools and a database engine that allows expansion from a portable PC with Windows 95 to a whole cluster of S/390 mainframe computers running OS/390.

DB2 comes in two editions: DB2 Workgroup and DB2 Enterprise Edition. This DBMS implements all the innovative database engine technologies known from previous versions of DB2, such as parallelization of query processing, a full set of replication tools, query summary tables to improve database performance, object-oriented database design capabilities, and Java language tools. Add to this that the DB2 system is equipped with a full range of multimedia extensions that allow you to store and manipulate text, audio and video, images and geographic data. We can say that the database clustering technology developed by IBM specialists has no analogues in terms of scaling capabilities. These extensions greatly facilitate the process of developing applications for the Web, as well as programs containing photo images and large text reports. The DB2 system is also quite competitive as a platform for application development because there is a Stored Procedure Builder tool that automatically converts an SQL statement into the corresponding Java class and includes it in the database structure. DB2 6.1 significantly improves interoperability with other database management systems by enabling the use of Microsoft's OLE DB specification, a new database access standard. The DB2 DBMS administration tools, which in the new version are rewritten in Java and can be obtained from the Web, deserve the highest praise.

The main disadvantages of this DBMS are the relative complexity of administration and the lack (yet) of implementations for popular server operating systems, such as LINUX.

In this DBMS, thanks to the Index Smart-Guide, it is possible to configure, forming optimal indexes for a given number of hits, characterizing the typical load on the database. DB2 is the only package that allows you to generate summary tables, which significantly improves the efficiency of a DBMS as a data warehouse. A pivot table is a temporary work area used by the database to store answers to frequently received queries. Well, with new features, parallelization, and the ability to select almost any join type and index (except perhaps bitmap indexes), DB2 6.1 is the lowest-cost high-performance system available. The administrative management tools of this DBMS are quite consistent with the level of tasks being solved, in addition, it provides exceptionally broad capabilities for working with multimedia data and for programming (which the Microsoft SQL Server system clearly lacks).

DBMS from Informix.

Recently, there has been a transition from relational DBMS to object-oriented ones (which can be clearly seen in the example of Oracle). Informix, also following this concept, announced a new Centaur DBMS solution based on the Informix Dynamic Server 7.3 relational database and the Informix Universal Data Option object-relational database and combining the high performance of the Dynamic Server when working with data with the versatility and multimedia functions of the Universal Data Option. This implementation is intended for the development of Internet systems. Presumably, this DBMS will have a flexible development environment with scalability corresponding to the intensive workloads characteristic of the Internet, and tools for working with new types of data, which with the development of the Web have become used everywhere. The Java tools implemented in the new system will allow developers to create stored procedures, user programs and DataBlades components in this language, which Informix calls custom database extensions.

From the point of view of Inforix customers, this will be a big step forward, since until now, when working with DataBlades, they could only use C and SPL, Informix's internal language for writing stored procedures. In addition, Centaur will have built-in ActiveX object handling. This will make it possible, for example, to create stored database procedures in Visual Basic; However, this requires that the Centaur package be executed in a Windows NT environment.

Centaur will be an add-on to Informix Dynamic Server and will work with the database format traditional for this package, so that users will still have all the previous functions at their disposal, and upgrading the system to the level of the new version will not be associated with great difficulties. In addition, Centaur will retain all of the design and programming capabilities that have made Informix Universal Server an outstanding technical achievement. The new system will be equipped with tools for object-oriented database design, creation of specialized tables and indexing programs; it will allow users to build their own functions into queries and not rely solely on standard SQL tools.

Conclusions.

Having considered the main characteristics of the architectures for constructing AIS, server operating systems and DBMS, in the future we will choose the Internet/Intranet architecture as the AIS architecture, Linux as the server OS, and Oracle 8i as the DBMS. The summary table presents the comparative characteristics of the two most common solutions today based on Microsoft SQL Server 7.0 (on NT) and Oracle8i (on Unix, Linux).

	Microsoft SQL Server 7.0
Administration
Graphical tools
Ease of maintenance
Data engine
Working with multiple CPUs	Acceptable
Join function and index selection
Simultaneous access by multiple users
Multimedia data processing
Web connection
Audio, video, image processing
Search by this text
Interoperability		Acceptable
Pairing with other databases
Single registration
Work under various operating systems	Acceptable
Programming capabilities	Acceptable
Stored procedures and triggers
Internal programming language
Building databases
SQL language
Object-oriented systems
Working with branches
Replication
Distributed Transaction Processing
Remote administration
Organization of data warehouses and preparation of reports
Download Tools
Analysis tools

At the same time, client places can function on almost any platform; the means for clients to access the DBMS is either CGI (Perl) or JAVA applications. In this case, the following requirements are imposed on the AIS server part:

2.3. The relational model as a platform for the development of modern information systems using the example of an interactive patent support system for technological design.

And so we looked at various approaches to the internal organization of databases. And as a result, we came to the conclusion that it was necessary to use the relational model, since it solves one of the main problems - making changes to the database during its use. Indeed, in a relational data meringue, the problem of data synchronization does not arise at all, since the data is stored in one copy. To make this issue clearer, here are the differences between traditional and relational databases.

Operation to be performed	Traditional Databases	Relational Databases
Application Development	You need to determine what information is required by different applications and create a series of common files.	It is necessary to determine the types of data stored and the relationships between them
Implementation of applications	Incoming data is written to master files; One data element is written to each information cell of each main file.	Different types of data are recorded in data tables corresponding to those types. As a result, every piece of information is stored in one single place
Application modification	A review of the database structure is required, followed by a rewrite of the main files that are affected by the changes being made, and the redesign of all applications that use these files	It is enough to find and modify the table that should contain the definition of the new type of data. The data itself is stored in other tables that are not affected by such changes.
Making partial changes to data	It is necessary to read each main file from beginning to end, modifying the data cells that change and leaving all other read cells unchanged.	In the corresponding tables, it is enough to select many rows in which changes need to be made, and make these changes using a single SQL statement.

So, the main features of relational databases:

The structure of a relational database is determined by the data stored in it and is not fixed at the time of completion of development (i.e., it is flexible and scalable).
Data structures can be given very descriptive names.
Data is stored in a single copy; all options for reading and modifying data are performed only with this data instance, which greatly facilitates data synchronization between many applications and users.
Data is stored according to clearly defined and strictly enforced rules.

Introduction

1. SQL-Server DBMS: main features and its application in EDMS

Conclusion

Bibliography

Introduction

A document is the main way of presenting information in any modern enterprise. The importance of the safety and skillful use of enterprise information resources for successful business is undeniable. The ability to make the right decision and respond to the situation in a timely manner, to respond flexibly to all market changes depends not only on the talent and experience of managers. The effectiveness of enterprise management also depends on how intelligently document management is organized in it. In fact, ineffective use of accumulated information (or, even worse, its loss) can lead to the loss of the entire business. After all, information or documents not received on time means, first of all, lost money, time and missed opportunities. As a result, at any enterprise where active work with various documents is carried out, sooner or later the problem of systematization, processing and secure storage of significant amounts of information arises. Modern electronic document management systems play an important role in optimizing the activities of an enterprise of any size and profile.

In order to choose an EDMS that suits the organization in all respects, you need to study a lot. In particular, not only the EDMS themselves, but also the DBMS.

The purpose of this work is to get acquainted with the SQL Server DBMS and individual elements of the EDMS “Directum” and “Euphrates-document flow”.

1. familiarity with the SQL Server DBMS, its main features and application;

2. Determining the route of movement of documents in the EDMS “Directum” and “Euphrates-document flow”

1. SQL-Server DBMS: main features and its application in EDMS

The SQL-Server DBMS appeared in 1989 and has changed significantly since then. Huge changes have been made to the product's scalability, integrity, ease of administration, performance and functionality.

Microsoft SQL Server is a relational database management system (DBMS). In relational databases, data is stored in tables. Related data can be grouped into tables, and relationships between tables can also be established. This is where the name relational comes from - from the English word relational (related, related, interdependent). Users access data on the server through applications, and administrators directly access the server to perform database configuration, administration, and support tasks. SQL Server is a scalable database, which means it can store large amounts of data and support many concurrent users accessing the database.

Microsoft SQL Server 6.5 is one of the most powerful DBMS in client-server architecture. This DBMS allows you to meet the requirements for distributed data processing systems, such as data replication, parallel processing, support for large databases on relatively inexpensive hardware platforms while maintaining ease of management and use.

MS SQL Server is not intended directly for developing custom applications, but performs database management functions. The server has tools for remote administration and operations management, organized on the basis of an object-oriented distributed management environment.

Microsoft SQL Server 6.5 is intended solely to support systems running in a client-server environment. It supports a wide range of development tools and is extremely easy to integrate with applications running on a PC.

SQL Server can replicate information in databases of other formats, including Oracle, IBM DB2, Sybase, Microsoft Access and other DBMS (if there is an ODBC driver that meets certain requirements).

Microsoft SQL Server 6.5 contains the Administrator Assistant. This tool allows you to assign basic database maintenance procedures and define a schedule for their implementation. Database maintenance operations include checking page allocation, integrity of pointers in tables (including system ones) and indexes, updating information required by the optimizer, reorganizing pages in tables and indexes, creating backup copies of tables and transaction logs. All of these operations can be set to run automatically according to a schedule specified by the administrator.

Software and hardware requirements

One of the main events that determined the future fate of Microsoft SQL Server was Microsoft's decision to focus exclusively on supporting the Windows NT platform. You can find many arguments confirming both the correctness and the incorrectness of such a decision. As a result of its adoption, the popularity of SQL Server is determined primarily by the popularity of the platform it supports, currently Windows 2000 and its future descendants. This database management system is so tied to the operating system that its reliability, scalability and performance are determined by the reliability, scalability and performance of the platform itself, and the position of SQL Server in the market will depend on the release of new versions of Windows

The more distributed computing is used, the more important it becomes to be able to store data anywhere, such as on a workstation or laptop. Despite the claims of some analysts that desktop DBMSs are no longer needed in the era of Internet applications, they are still widely used in all areas of business. SQL Server can be used on any Intel-compatible computer running Windows 9x, Windows NT, Windows 2000. There is also a version of SQL Server 2000 for Windows CE, designed for use on mobile devices.

One of the advantages of SQL Server is its ease of use, particularly administration. SQL Server Enterprise Manager, included in all editions of Microsoft SQL Server (except MSDE), is a fully functional and fairly simple tool for administering this DBMS.

According to the Transaction Processing Performance Council (TPC), SQL Server is now the rdsman in terms of productivity.

Thus, the main advantages of SQL-Server are:

High degree of data protection.

Powerful tools for working with data.

High performance.

Storing large amounts of data.

Storing data that requires confidentiality or where its loss is not permissible.

Gradually, this product, starting with a small but ambitious project, turned into what users deal with today. The main features in the latest versions once again confirm the fact that Microsoft continues to develop its products, trying to meet the increasing demands of consumers.

2. Determining the route of movement of documents in the EDMS “Directum” and “Euphrates-document flow”

Modules responsible for document flow are usually called document routing modules. In general, loose and rigid document routing is used. With free routing, any user participating in the document flow can, at his own discretion, change the existing (or set a new) route for documents. With rigid routing, the routes for passing documents are strictly regulated, and users do not have the right to change them. However, with hard routing, logical operations can be processed when the route changes when some predetermined conditions are met (for example, sending a document to management when a specific user exceeds his official authority, say, financial). Most EDMS systems include a routing module; in some, it must be purchased separately. In particular, full-featured routing modules are developed and supplied by third parties.

Euphrates

Cognitive Technologies offers the Euphrates program. It runs in MS Windows 95/98/NT/2000 and provides comprehensive automation of office work, including registration, execution control, organization and maintenance of an electronic archive of documents received from a variety of sources. The key capabilities of the system include:

Creation of corporate electronic archives;

Entering paper documents into the system database using a scanner and CuneiForm recognition system;

Text search based on document contents and details;

Morphological analysis of documents to improve search efficiency.

In addition, the system supports graphic formats (TIF, PCX, JPG, BMP, GIF), Excel spreadsheet format and provides a quick view mode while preserving the original formatting.

Desktop options Secretariat, Accounting, Human Resources, Insurance Company, Euphrates Home Base allow you to organize the workplace for each specific employee. You can flexibly control the presentation of information, select fonts, etc. Printing information of any type is carried out both through the appropriate application and directly from Euphrates. There is a set of service utilities for testing the database, compressing it and archiving it. The open interface allows you to create and connect filters to work with information of any type and format.