Abstract of master's work

Research and design of the optimal database structure. Development workstations "Applications forms acceptance" and "Enrollment" within the university ACS for the Admissions office of DonNTU

1 Actuality and scientific innovation
2. The purpose and objectives of the research, expected results
3. Review of researches and developments
3.1 Review of international sources
3.2 Review of national sources
3.3 Review of local sources
4 Database Design
5 Ways to construction of optimal database structure in terms of the database performance
Conclusions
References

1. Actuality and scientific innovation

Relational database (DB) and data storage (NAS), based on them, occupy a dominant position in the storage market. According to the research 89% of current and future use relational database software [1]. It can be concluded that the purpose of the research and design of the optimal structure of the database will be relevant as long as the relational database will be popular.

Completing the admissions campaign is one of the most important tasks of the university, and requires a lot of human and time resources, so the automation of all types of work admissions committee is always relevant, and economically viable.

The need for a new development is that the existing automated system (AS) Admissions DonNTU Commission was developed using outdated technology and the rules of admissions. The lack of technical documentation for the previously developed product complicates the support of the system, add implementation of new features or change the current functionality.

2. The purpose and objectives of the research, expected results

The purpose of work - development of software system that implements the input, storage, editing, processing information about the applicants and their cases, as well as the formation of a list of applicants recommended for enrollment in the automatic and manual modes, and provides a minimum effort admissions committee

As part of the development plan to investigate the influence of the DB structure on its performance, and to develop optimal performance in terms of the structure of the database AS "Admissions Committee".

3. Review of researches and developments

There are many ways an optimal database structure design data, which are engaged in research on the institutions to leading companies working with databases.

Automating the process of admissions in the university is a fairly common problem for universities. Accordingly, developments in this area a lot.

3.1 Review of international sources

In the world there are many papers and publications on the theme of "the optimal structure of the database" and "Automation of the admissions of the university." Below is a list of the most popular publications:

Publication Lavon R. Frazier An Admissions Process Transformed with Technology // EDUCAUSE. In this paper we consider the modern means of automation of the receiving institution company [2].

Publication Praful Gavaskar, Sachin Gupta, Diksha Patil , Vikas More. College Office Automation System // International Journal of Current Engineering and Technology. In this paper, the basic problem of selection committee of college and formed their approach to automation [3].

Publication of S. Finkelstein, M. Schkolnick, P. Tiberio Physical Database Design for Relational Databases // IBM Almaden Research Center. In this paper, the problems of the physical database design phase, presents the fundamental principles of database design [4].

3.2 Review of national sources

In Russia and Ukraine, as many scientists engaged in research in the field of optimization of the database structure and automation of the admissions committee of the university. List of the most popular publications is shown below.

Publication Мартынов В.В., Прошкин Е. Н. Подход к анализу характеристик производительности БД // Вестник УТАТУ. Управление, ВТ и И.In this work formed the approach to the assessment of the performance of hardware and software database management system (DBMS) proposed a theoretical approach to solving database performance problems [5].

Publication Ивутин А.Н., Терехин И.С. Повышение надежности и производительности баз данных при помощи репликаций // Известия ТулГУ. Технические науки.The paper discusses the types of replication, of their tasks, as well as the impact of the use replikay the performance and reliability of the database [6].

Publication Белош В.В., Быстров В.В., Дмитриев С.В., Программный модуль автоматизированной обработки документов приемной комиссии вуза // Труды Кольского научного центра РАН. We consider the development of software modules based on modern web technologies that can automate the collection of statements of entrants and store their personal information for the further construction of the pre-enrollment list on the basis of the selection committee KB PetrSU [7].

3.3 Review of local sources

Students who are part of Donetsk National Technical University has been developing automation reception DonNTU Commission has not been found, but had similar themes, but there are students who are engaged in research in the field of optimization of the database.

DonNTU Master's work Бабич К.К. Оптимизации для высоконагруженных реляционных БД и альтернативные решения. The work is dedicated to the creation of highly loaded web system optimization and application of relational databases, to solve the problem of multiple access and high traffic [8].

DonNTU Master's work Афонов И. В. Исследование свойств распределённых систем хранения данных. This paper investigates the performance of the existing models of distributed and replicated databases. [9].

DonNTU Master's work Мошкола А. Я. WEB – ориентированная комплексная система управления факультетом. The work is designed WEB - oriented integrated management system of the faculty which includes managing contingent student movement [10].

4. Database Design

Storage AS "Admissions Office" data. It is a database. As a database for the system selected Microsoft SQL Server 2012, because it is used for other tasks within the existing ACS DonNTU.

Design developed the system database will be carried out in three stages:

1) Conceptual design.

2) Logical design.

3) Physical design.

The first step is to build information models of objects for which data is to be stored in the database (for example, an applicant). Such models are used without regard to the DBMS. The result is a conceptual model of data base, which includes:

- A description of information objects and connections between them;

- A description of constraints, ie, requirements for valid data values ??and relationships between them;

In step logic design created database schema-specific specific data model, in this case - the relational. For relational data model, logical model - a set of relations schemes by primary keys, as well as relations between relations that represent foreign keys. At this stage, also performed normalization - the process of bringing the database structure to the form, which ensures a minimum logical redundancy, and is not intended to increase or decrease of productivity, or increase or decrease the volume of the physical database. The ultimate goal of normalization is to reduce potential inconsistencies in the stored information database [11].

At the stage of physical design data is created for a specific database schema. Its specificity may include restrictions on the names of database objects supported restrictions on types of data, etc. In addition, the specificity of a particular database at the physical design includes selection decisions related to the physical storage medium (selection of disk memory management methods, the division on the DB files and devices, data access methods), creating indexes, etc.

5. Ways to construction of optimal database structure in terms of the database performance

Typically, database performance is measured in terms of transaction performance. To solve the problem of increasing the performance you need to create a transaction that may exist in the database, but in most cases the practical form initially fully prescribed transactions to the database is very complex organizational task. In addition, some of the problems associated with the performance can be detected only by testing the trial operation stage and a database [12].

In order to determine the transactions used in the AU "Admissions committee" should consider the functionality they implement. AS "Admissions committee" includes the following features:

1) Data storage of all university applicants in a single database.

2) Personalized access to information with the delineation of the rights of users in accordance with official powers.

3) Registration of personal data, passport data, photos and statements of applicants.

4) Editing profiles and applications of applicants who have submitted documents.

5) Taking into account the documents submitted by the applicant.

6) Formation and control of enrollment plan.

7) Formation of the necessary documents for the applicant (receipts, statements, questionnaires, etc.).

8) Creating a list of recommended for admission to university.

9) Admission (formation of lists, protocols and orders on enrollment).

10) Formation of statistical reporting for the analysis of data about the applicants submitted applications and the results of entrance examinations in the context of faculties, specialties, forms of learning, types of financing, and others.

Also, you need to define the main objects of the AU data model "Admissions committee":

1) A person - applicant describes a model of personal data;

2) Case - describes the data model of the application the applicant;

3) Object reference - describes the data model reference unit.

The object "Person" is a composite consisting of several sub-objects:

1) Photo.

2) Passport data.

3) The addresses and contact details.

4) Education documents.

On the basis of the functional AC "Admissions Office" and the main objects of the data model, you can make a list of the main transaction database:

1) Add a new person.

2) Add a photo for a person.

3) Enter the person's passport data.

4) Editing person passport data.

5) Addition of address and contact person data.

6) Edit the address and contact person data.

7) Adding a document on the formation of the person.

8) Data editing a document on the formation of the person.

9) Add a new business.

10) Editing business data.

11) Preparation of reference.

12) Preparation of the list of cases with reference to the person's data.

13) Getting statistics on cases submitted and grouping by various parameters.

The priority tasks for the AS "Admissions committee" is input, recording, storage applicants and applications filed by them, and the most important stage of the receiving company is receiving DonNTU entrants cases in which the frequency of input of new data and edit existing will be maximum. Consequently, the database structure should be focused on maximizing the performance transaction records and updates.

Based on the documentation MS SQL Server 2012 to improve performance inserting transactions and data updates are put forward the following requirements [13]:

- Short term transactions in order to minimize blockages and improve concurrency.

- High database normalization.

- Minimization of historical and statistical data, or complete absence.

- Minimize the use of indexes.

However, the AS "Admissions committee" is implementing another important functionality - a public web-service which displays the competitive position of the applicants in real time. Web-server that provides this service generates a large number of requests to multiply the sample data on various parameters. For optimum performance, perform data queries, use views mechanism that is implemented in MS SQL Server. The principle of views shown in Figure 1.

Figure 1 - Demonstration of concepts of the views

(Animation: 5 frames, 40 KB)

When using views performance gains achieved through the use of caching query mechanism, implemented in MS SQL Server, which allows you to reduce the execution time of identical queries. Also, this database allows you to create indexes for view, without affecting the original table.

Conclusions

To create the optimal structure of the database AS "Selection committee" should ensure storage of the main objects of the data model in a variety of tables to avoid data redundancy via normalization, minimize the use of indexes, and views should be used to multiply the sample data.

References

1. DB-Engines Ranking [Electronic resource] DB-Engines.com // URL: http://db-engines.com/en/ranking reference date: 05.06.2015).

2. An Admissions Process Transformed with Technology [Electronic resource] educause.edu // URL: http://er.educause.edu/~/media/files/article-downloads/eqm0034.pdf(reference date: 05.06.2015).

3. College Office Automation System [Electronic resource] inpressco.com // URL: http://inpressco.com/wp-content/uploads/2015/03/Paper47849-851.pdf(reference date: 05.06.2015).

4. Physical Database Design for Relational Databases // IBM Almaden Research Center [Electronic resource] // URL: http://www.cs.cmu.edu/~natassa/courses/15-823/F02/papers/p91-finkelstein.pdf(reference date: 05.06.2015).

5. Подход к анализу характеристик производительности БД [Electronic resource] cyberleninka.ru // URL: http://cyberleninka.ru/article/n/podhod-k-analizu-harakteristik-proizvoditelnosti-bd(reference date: 05.06.2015).

6. Повышение надежности и производительности баз данных при помощи репликаций [Electronic resource] cyberleninka.ru // URL: http://cyberleninka.ru/article/n/povyshenie-nadezhnosti-i-proizvoditelnosti-baz-dannyh-pri-pomoschi-replikatsiy(reference date: 05.06.2015).

7. Программный модуль автоматизированной обработки документов приемной комиссии вуза [Electronic resource] cyberleninka.ru // URL: http://cyberleninka.ru/article/n/programmnyy-modul-avtomatizirovannoy-obrabotki-dokumentov-priemnoy-komissii-vuza(reference date: 05.06.2015).

8. Бабич К. К. Оптимизации для высоконагруженных реляционных БД и альтернативные решения [Electronic resource] // Портал Магистров ДонНТУ, 2011г. URL: http://masters.donntu.ru/2013/fknt/babich/diss/index.htm(reference date: 24.05.2015).

9. Афонов И. В. Исследование свойств распределённых систем хранения данных [Electronic resource] // Портал Магистров ДонНТУ, 2011г. URL: http://masters.donntu.ru/2007/fvti/afonov/diss/index.htm(reference date: 24.05.2015).

10. Мошкола А. Я. WEB – ориентированная комплексная система управления факультетом [Electronic resource] // Портал Магистров ДонНТУ, 2011г. URL: http://masters.donntu.ru/2009/fvti/moshkola/diss/index.htm(reference date: 24.05.2015).


11. Christopher J. Date - Introduction to Database Systems - 8th edition - Moscow:. Williams 2005 - page 1328.

12. Создание физической модели базы данных. Учет влияния транзакций [Electronic resource] // НОУ Интуит URL: . http://www.intuit.ru/studies/courses/1095/191/lecture/4985(reference date: 24.05.2015)

13. Сравнение оперативной обработки транзакций [Electronic resource] // Microsoft TechNet URL: https://technet.microsoft.com/ru-ru/library/ms187669(v=sql.105).aspx (reference date: 24.05.2015)