The processing by the district therapist data in this article is analyzed. All documents and reports that exist in the subject area are considered. It is substantiated that non-normalized relations describe the subject area most adequately. With the help of non - normalized relations, a model of data of the system of district therapists is built. Non-normalized relationships, nested relationships, subject area analysis, medicine, disease data about diseases, accounting ORCID: 0000-0002-9676-0180 (V. Lytvyn); 0000-0002-5361-8854 (A. Hryhorovych); 0000-0002-5828-067X (V. Hryhorovych); 00000002-9448-1751 (L. Chyrun); 0000-0001-6417-3689 (V. Vysotska); 0000-0003-2403-0784 (M. Bublyk)
The first stage of creating any information system is the description and modeling of the subject area. The problem of adequate data presentation at this stage does not lose its relevance.
The classical relational data model is not adequate in an extremely wide range of applications.
Various programs of processing databases, such as systems for getting information ( Information
Retrieval Systems ) or system of automatic design and manufacturing (Computer - Aided Design and
Manufacturing , CAD / CAM) require handling structured entities. Non-normalized relations [
The possibilities of non-normalized relationships are implemented in many DBMS on the market
(System 2000 and ADABAS [
Use technologies of inaccurate sets to derive rules from the data table and further assess the
quality of the classifier based on these rules [
A hypercube of data for the ambulance service is built and operations with it are described, in particular - an algorithm for constructing a decision tree and forming a set of dependencies [11];
The peculiarities of the researched area are described and the ways of presenting hierarchical information as lists and frames are considered, the information restructuring scheme is given [12]; EMAIL: Bublyk)
2020 Copyright for this paper by its authors. The formation of the knowledge base in the form of logical functions by finding patterns in databases about patients and building on their basis a system of making a decision for predicting diagnosis are described [14-18]; Comparing the impact of ways of choosing a set of examples to build a decision tree on the quality of prognosis are made [19-24]; Assessment of dependence quality of prognosis in a group of patients with different methods of discretization of a continuous parameter, which is the patient's age, is given [25-29]; Recommendations considering organization of the data preparation process aware also given [13]. Thus, the problem of implementation of information technology in medicine is important and urgent; an enormous amount of publications of different kinds is devoted to it.
Analysis of subject area for information system of processing medical information, which is contained in the medical passports and other documents and is used, for example, used by district therapists, is given in this research. The required information about the structure of data and tasks, which are solved in the subject area, provided by the district therapist of Drohobych medical district number 13 - Bihunyak Halyna Yaroslavivna.
The purpose of the article is to show that an adequate data model for the system of processing of medical information can built with the help of non-normalized relations.
The professional activity of a district therapist is to treat patients of a medical district. The effectiveness of this work largely depends on the objectivity and completeness of information about the patient's health, previous illnesses and medications. Thus, the designed information system must ensure the accumulation of information and its effective processing. The main source of data is a medical passport. In the current practice, the medical passport (form number025/о of medical documentation) performs the task of information accumulation. A medical passport is issued for each patient in order to accumulate medical information. In addition, a sheet with immunization, a sheet with health insurance and a least of major deferred diseases and surgical interventions are filled. Each time during the examination of the patient by the district therapist, the relevant data are entered. The diagnosis (primary, clarifying, final) is determined, information on treatment, data on temporary incapacity for work is recorded and the date of control appearance is appointed. If necessary, the results of specialist consultation and laboratory tests are entered. The general structure of the form of the Medical passport is shown in Fig. 1 – as rectangles are marked sub - tables. It is seen that the information contained in the medical passport, it is natural to present through non-normalized (nested) relationships. We should note that not every sub-table is a nested relationship - if the sub-table contains only one row (such as the value of the field "blood type" in the sub-table Signal marks for life), then such an attribute should transferred from the nested relationship to the level up. In addition, there are sub-tables (for example, Critical diseases) that contain only other sub-tables and do not contain atomic attributes. In this case, there is no need to create a separate nested non- normalized relation for the external sub-table. It is easier to represent the internal sub-tables by nested relations of the first level of the hierarchy - see Fig.1 (the proposed relations are marked on the right above the corresponding rectangles) and Fig. 2 (general structure of the relationship Medical passport - dotted lines indicate relationships that may be empty). Let us consider the structure of the abnormal relation R 1 (Medical Passport) in more detail. Its atomic attributes and nested relationship can described of next so. Primary key attributes: K1a (District code) and K1b (Patient code). The domains of the attributes a1 (Surname), b1 (Name), c1 (Patronymic), f1 (Passport) are letter strings - previously unknown values, the domain of the attribute d1 (Date of birth) are valid dates of birth. The e1 (Gender) attribute can only take two values: "M" and "W". The g1 (Address) attribute is actually compound: g1 ≡ {g1a, g1b, g1c, g1d}, although it is not reflected in the form of a medical passport - it consists of the attributes g1a (Settlement), g1b (Street), g1c (House number), g1d (Apartment number). Attribute domains g1a and g 1b contain the names of settlements and streets that are part of the site. Therefore, it cannot be arbitrary letter strings: when designing a data scheme, it would be a good idea to provide directory tables with the names of settlements and streets, which will be the values of the substitution for the attributes g1a and g1b. relevant and tables and - directory tables should be updated by the user. Attribute l1 (Retirer) compound (l1 ≡ {l1a, l1b}) - it describes whether the patient is retired and gives the characteristics: l1a from what year the patient retired and l1b - reasons for it. If the patient is not retired, the attributes {l1a, l1b} will be empty. The attribute domain l1a (From year) contains integers whose value is the year of retirement. Attribute l1b (Cause) acquires one of the values "by illness", "by length of service", "by age" (fixed set of values from which the choice is made), "other reason" - an arbitrary text value entered by the user - these values should be selected from the directory table that is updated by the user. Attribute m1 (Attached for medical examination) - is also composed: m1 ≡ {m1a, m1b}. The domain of the attribute m1a contains the numbers of medical sites (integers or NULL values), and the domain of the attribute m1b - their names (letter strings) or NULL values, you should implement a directory table of names of sections, the values of which will be updated by the user. The domain of attribute n1 (Special account) is the value NULL or letter strings, which acquire the following values: "participant in the war", "liquidator of Chernobyl", "pupil", "student", "lonely", "repressed" - they should provide the appropriate reference table that can be updated by the user. The domain of attribute o 1 (Blood group) contains only four values («I», «II», «III», «IV»), attribute p 1 (Rhesus factor) acquires only one of two values («+», «-»). The domain of the attribute r 1 (Observed in … from… year) is the value NULL or integers - the corresponding years. Thus, we obtain a refined scheme of atomic attributes of the non-normalized relation R1 and the domains of the values of these attributes can described as follows: dom (R1.K1a) { integer } dom (R1.K1b) { integer } dom (R1.a1) { varchar } dom (R1.b1) { varchar } dom (R1.c1) { varchar } dom (R1.d1) { date } dom (R1.e1) { varchar } {“M”, “W”} – only fixed values dom (R1.f1) { varchar } dom (R1.g1a) { varchar } directory table which is updated by the user dom (R1.g1b) { varchar } directory table which is updated by the user dom (R1.g1c) { varchar } dom (R1.g1d) { integer } dom (R1.h1) { varchar } dom (R1.i1) { varchar } directory table which is updated by the user dom (R1.k1) { varchar } directory table which is updated by the user dom (R1.l1a) { integer } dom (R1.l1b) { varchar } directory table which is updated by the user dom (R1.m1a) { integer } dom (R1.m1b) { varchar } directory table which is updated by the user dom (R1.n1) { varchar } directory table which is updated by the user dom (R1.o1) { varchar } {“I”, “II”,“III”, “IV”} – only fixed values dom (R1.p1) { varchar } {“+”, “–”} – only fixed values dom (R1.r1) { integer }
The nested relation R1.1 (pathogenic carrier) consists of two attributes a1.1 (Year) and b1.1 (Name). The domain of attribute a1.1 (Year) contains integers; and the domain of the attribute b1.1 (Name) - the corresponding letter strings "staphylococci", "streptococci", "HIV", and "viral hepatitis antigen". For improve the processing of this data it will be necessary to implement a directory table that contains lists of relevant values and can be updated by the user.
Each of the three nested relationship R1.2 (Chronic and occupational diseases), R1.3 (Past surgery), R1.4 (Past infectious diseases) consists of two attributes: Name of illness or surgery - а1.2, а1.3, а1.4 and Year - b1.2, b1.3, b1.4. The domains of the attributes а1.2, а1.3, and а1.4 (Name of the disease or operation) will be a set of letter strings - so you should think about the appropriate directory table that will supplemented by the user. The domains of attributes b1.2, b1.3, b1.4 (Year) will be integers.
The nested relation R1.5 (Special marks - lifelong) contains three attributes a1.5 (Privilege category), b1.5 (identity card number), and c1.5 (Seal and signature of the head of the department). The nested relation R1.6 (Special marks- variables) contains three attributes a1.6 (Name), b1.6 (Year), c1.6 (Doctor's signature). The domain of the attribute a1.5 (Privilege category) will be the letter rows that should select from the directory table to populate by the user. The domains of attributes c1.5 (Seal and signature of the head of the department), a1.6 (Name) and c1.6 (Doctor's signature) will be arbitrary letter lines; a domain attribute of b1.5 (Identity card number) and b1.6 (Year) - integers.
The nested relation R1.7 (Dispensary group) contains two attributes a1.7 (Year) and b1.7 (Group). The domain of the attribute a1.7 (Year) will be integers; and the domain of the attribute b1.7 (Group) letter strings ("D1", "D2", "D3", "D4").
The nested relation R1.8 (Letter of preventive vaccinations) contains the attributes a1.8 (Name), b1.8 (Date), c1.8 (Dose), d1.8 (Series), e1.8 (Reaction), f1.8 (Signature). The domain of the attribute a1.8 (Name) will be the letter rows that should selected from the directory table to be populated by the user; domain attribute b1.8 (Date) - valid dates. The domain of the attribute c1.8 (Dose) consists of real numbers. The domain of attributes d1.8 (Series), e1.8 (Reaction) and f1.8 (Signature) will be arbitrary letter strings.
The nested relation R1.9 (Letter of voluntary health insurance) contains the attributes a1.9 (Insurance company), a1.9 (Type), c1.9 (Amount), d1.9 (Term: from), e1.9 (Term: to), f1.9 (Signature). Domain of attributes a1.9 (Insurance Company) and b1.9 (View) will be alphabetic strings, which should selected from the table – directory table that will updated by the user; attribute domain c1.9 (Amount) monetary values. The attribute domains d1.9 (Term: from) and e1.9 (Term: to) will be valid dates. The domain of the attribute f1.9 (Signature) consists of arbitrary letter strings.
The nested relation R1.10 (Temporary disability) contains the attributes a1.10 (Start date), b1.10 (End date), c1.10 (number of letter or certificate), d1.10 (Name of disease), e1.10 (Information about hospitalization), f1.10 (Doctor's signature). The domains of the attributes a1.10 (Start Date) and b1.10 (End Date) will be valid dates. Attribute domain c1.10 (number of letter or certificate) - arbitrary letter strings; the domain of the attribute d1.10 (Name of the disease) will be the letter rows that should selected from the reference table, which is filled in by the user; the attribute domains e1.10 (Information) and f1.10 (Doctor's Signature) consist of arbitrary letter strings.
The nested relation R1.11 (Disability) contains the attributes a1.11 (Date of certification and recertification), b1.11 (number of certificate), c1.11 (Name of disease), d1.11 (Category), and e1.11 (Signature of the head of department). The domain of attribute a1.11 (Date of certification and re-certification) consists of valid dates. The domain of attribute b1.11 (number of certificate) contains arbitrary letter strings; the attribute domains c1.11 (Name of disease) and d1.11 (Category) will be the letter rows that should selected from the corresponding user table -reference books. The domain of attribute e1.11 (Signature of the head of department) consists of arbitrary letter strings.
The nested relation R1.12 (Letter of final (revised) diagnoses) contains the attributes a1.12 (Date), b1.12 (Final (revised) diagnosis), c1.12 (Diagnosis established), and d1.12 (Doctor's name). The domain of attribute a1.12 (Date) consists of valid dates. Attribute domain b1.12 (Final (refined) diagnosis) contains letter rows that should selected from the reference table to populate by the user. The domain of the attribute c1.12 (Diagnosis established) consists of the letter values "+", "-". The domain of the attribute d1.12 (Doctor's last name) contains arbitrary letter strings.
The nested relation R1.13 (Review therapist) contains the attributes: a1.13 (Date), b1.13 (Complaints), c1.13 (History), d1.13 (General condition), e1.13 (Revitalisation), f1.13 (Skin), g1.13 (Lymph nodes), h1.13 (Thyroid gland), i1.13 (Bone and joint system), j1.13 (Frequency of respiration in one minute), k1.13 (Respiration in the lungs), l1.13 (Percussion pulmonary sound), m1.13 (Heart tones), n1.13 (BP), o1.13 (Pulse), p1.13 (Oropharynx), q1.13 (Teeth), r1.13 (Tonsils), s1.13 (Other data), t1.13 (Abdominal palpation), u1.13 (Liver), v1.13 (Chair), w1.13 (Urination), x1.13 (Edema), y1.13 (Additional information), z1.13 (Diagnosis), aa1.13 (Group of follow-up), ab1.13 (L / leaf number), ac1.13 (From), ad1.13 (To), ad1.13 (Regime), af1.13 (Active surveillance), ag1.13 (Control visit), and ah1.13 ( Doctor ).
The scheme of connections of the non-normalized relation R1 with all reference tables is shown in Fig. 3 (directory table with fixed attribute of values is marked with a shaded rectangle with a bold font; relationships that can be empty are indicated by a dotted outline). Analysis of sputum. These documents do not contain sub-tables and are represented by using relations R3 , R4 , R5 , R6 , R7 , R8.
The relation of R3 (Blood test) is a flat table. It contains the attributes a3 (Surname), b3 (Name), c3 (Patronymic), d3 (Settlement), e3 (Street), f3 (House), g3 (Apartment), h3 (Division), i3 (HB), j3 (Erythrocytes), k3 (Leukocytes), l3 (ESR), m3 (Haematocrit), n3 (Date), o3 (Signature).
The scheme of relations of the relation R 3 with its directory table is shown in Fig. 5. (Aspartate aminotransferase), ah7 (amylase), ai7 (Creatine phosphokinase), aj7 (Lactate dehydrogenase), ak7 (Alkaline phosphate), al7 (Cholinesterase), am7 (Other), an7 (Date of issue of the analysis), ao7 (Laboratory assistant).
Relation R9 (Statistical form for registration of final (revised) diagnoses) -a flat table (Fig. 11). Its attributes are a9 (Surname), b9 (Name), c9 (Patronymic), d9 (Age), e9 (Gender), f9 (Location), g9 (Street), h9 (House), i9 (Apartment), j9 ( Precinct ); k9 (Does he live in the service area) - its value is "yes" or "no"; l9 (Type of diagnosis) - its value is "final" or "specified"; m9 (Diagnosis); n9 (diagnosis that is for the first time) - its value is "+" or NULL; o9 (ICD-X code), p9 (Instead of a previously established diagnosis), q9 (Contingents), r9 (When the disease is detected), s9 (Type of injury and poisoning), t9 (Date), u9 ( Doctor ). Since the document form Outpatient sheet contains a sub-table, it can represented by the non- normalized relation R10 (Fig. 12). hospitalization), r13 (Date of admission to hospital), s13 (Date of discharge from hospital), t13 (Short history/anamnesis), u13 (Treatment and labour recommendations), v13 (Date), w13 (Physician). The nested relation R13.1 (Full diagnosis) contains the attributes: a13.1 (Diagnosis) and b13.1 (Type of disease) - the value of this attribute: "underlying", "concomitant", "complication" (Fig. 16). C4.dim3 (Kind of population) = {“FR of population”, “Population that is not organised”} C4.dim4 (Dynamics) = {“Subjected”, “Passed”}
C4.dim5 (Deteched at first) = {“Accute patologies”, “Chronic patologies”} The analysis of activity is made out in the form of the Report on dispensary group for a year, quarterly in the form of Analysis of work, monthly - in the form of the analysis Precancerous diseases.
Report on dispensary group can represented by a cross table - a hypercube of data C5, which contains 6 dimensions:
C5.dim1 (Year) = set of years of reporting C5.dim2 (number of a district) = { R1.K1a } C5.dim3 (Doctor) = set of surnames of doctors C5.dim4 (Nurse) = set of surnames of nurses C5.dim5 (Diagnosis) = set of names of diagnoses C5.dim6 (Dynamics) = {“Registered in total”, “Registered with +”, “Were registered”, “Taken in total”, “Taken with +”, “Deregistered”, “Registered at the end of the year”, “From them retirees”}
Analysis of work report can represented by a cross table - a hypercube of data C6, which contains 6 dimensions:
C5.dim1 (Year) = set of years of reporting C5.dim5 (Guarter) = set of numbers of quarters C5.dim2 (number of a district) = { R1.K1a } C5.dim3 (Doctor) = set of surnames of doctors C5.dim4 (Nurse) = set of surnames of nurses C5.dim6 (Categories and dynamics) = a set of names of categories of the population and names of changes of a condition
Precancerous diseases report can represented by a cross table - a hypercube of data C7, which contains 6 dimensions:
C7.dim1 (Date) = set of dates of reporting C7.dim2 (number of a district) = { R1.K1a } C7.dim3 (Doctor) = set of surnames of doctors C7.dim4 (Nurse) = set of surnames of nurses C7.dim5 (Nosology) = set of names of diagnoses C7.dim6 (Dynamics) = {“Was registred on «D»”, “Taken”, “Deregistred”, “Deregistred because of recovery”, “Moved to c-r”, “Other”, “Registred now”}
All these reports display the number of corresponding records of the ratio R1 - that is, the fact table for each hypercube is based on the corresponding queries to R1. Let's summarize the considered document flow that is connected with the activity of the district therapist (see the scheme shown in Fig. 19). Each time during the examination of the patient by the district therapist, the relevant data are entered in the Medical Passport (R1). If necessary, the doctor refers the patient for examination using a referral for consultation, examination, procedure, transfusion (R2). The laboratory department receives the results of laboratory tests: Blood test (R3), Urine test (R4), Analysis of fecal for helminths and protozoa (R5), Liver function test (R6), Biochemical blood test (R7), Analysis of sputum (R8).During admission, a statistical coupon for registration of final (revised) diagnoses (R9) is filled in for each patient with an acute diagnosis, and an Outpatient sheet (R10) is kept. If necessary, patients are prescribed a Prescription (R11), fill in The card of the patient of the day hospital, the inpatient care at home (R12) and The extract from the patient's medical card (R13). To obtain a sanatorium voucher, a Certificate for a sanatorium voucher (R14) is issued. After presenting a permit for sanatorium or outpatient treatment, a sanatorium card is issued (R15). Quarterly, therapist submits Report on the disabled (C1), Analysis of the implementation of the plan of diphtheria vaccination (C2), Oncology examinations of the population (C3) and data on FR examination (C4).
The world that surrounds us contains mostly complex objects - those that contain simpler components. Therefore, it is not surprising that it is possible to build models of such a large number of subject areas by means of non-normalized relations. The article shows that medical data is hierarchical in its nature - documents that describe medical information are tables that contain subtables. Consequently, the system of processing medical data can adequately implemented by using non-normalized (nested) relationships. The document flow, related to the activity of the district therapist is analysed; Based on the analysis, a data scheme that consists of a set of non-normalized relations is constructed, the domains of all attributes are defined, and connections and dependencies between attributes are described. The designed scheme can use in the implementation of a computer information system for processing medical information.
[11] R. Bergenthum, J. Schick, Verification of Logs-Revealing Faulty Processes of a Medical Laboratory, Transactions on Petri Nets and Other Models of Concurrency X. Springer, Berlin, Heidelberg, 2015, pp. 1-18. doi: 10.1007/978-3-662-48650-4_1 [12] L. Byczkowska-Lipińska, A. Wosiak, Multimedia NoSQL database solutions in the medical imaging data analysis, Przegląd Elektrotechniczny, volume 12, 2013, pp. 234-237. [13] D. R. Schlegel, J. P. Bona, P. L. Elkin, Comparing small graph retrieval performance for ontology concepts in medical texts, Biomedical Data Management and Graph Online Querying, Springer, Cham, 2015, pp. 32-44. doi: 10.1007/978-3-319-41576-5_3 [14] V. Lytvyn, Y. Burov, P. Kravets, V. Vysotska, A. Demchuk, A. Berko, Y. Ryshkovets, S.
Shcherbak, O. Naum, Methods and Models of Intellectual Processing of Texts for Building Ontologies of Software for Medical Terms Identification in Content Classification, CEUR Workshop Proceedings, Vol-2362, (2019) 354-368. [15] V. Vysotska, V. Lytvyn, Y. Burov, A. Gozhyj, S. Makara, The consolidated information webresource about pharmacy networks in city, CEUR Workshop Proceedings, (2018) 239-255. [16] L. Chyrun, E. Leshchynskyy, V. Lytvyn, A. Rzheuskyi, V. Vysotska, Y. Borzov, Intellectual Analysis of Making Decisions Tree in Information Systems of Screening Observation for Immunological Patients, CEUR Workshop Proceedings, Vol-2362, (2019) 281-296. [17] A., Sachenko, V., Kochan, V., Turchenko, V., Tymchyshyn, N.Vasylkiv, Intelligent nodes for distributed sensor network, in: Proceedings of IEEE Instrumentation and Measurement Technology Conference 3, 1999, pp. 1479-1484. doi: 10.1109/IMTC.1999.776072 [18] M. Komar, V. Golovko, A. Sachenko, S. Bezobrazov, Intelligent system for detection of networking intrusion, in: Proceedings of the 6th IEEE International Conference on Intelligent Data Acquisition and Advanced Computing Systems: Technology and Applications, IDAACS, 2011, pp. 374-377. doi: 10.1109/IDAACS.2011.6072777 [19] O. Cherednichenko, O. Kanishcheva, O. Yakovleva, D. Arkatov, Collection and Processing of a
Medical Corpus in Ukrainian, CEUR workshop proceedings, Vol-2604, (2020) 272-282. [20] M. Odrekhivskyy, V. Pasichnyk, N. Kunanets, A. Rzheuskyi, G. Korz, D. Tabachyshyn, The Use of Modern Information Technology in Medical and Health Institutions of Truskavets Resort, CEUR Workshop Proceedings, Vol-2631, (2020) 184-197. [21] R. Perkhach, D. Kysil, D. Dosyn, I. Zavuschak, Y. Kis, M. Hrendus, A. Vasyliuk, M. Sadova, M.
Prodanyuk, Method of Structural Semantic Analysis of Dental Terms in the Instructions for Medical Preparations, CEUR workshop proceedings, Vol-2604, (2020). 662-669. [22] O. Cherednichenko, N. Babkova, O. Kanishcheva, Complex Term Identification for Ukrainian
Medical Texts, CEUR Workshop Proceedings, Vol-2255, (2018) 146-154. [23] S. Fedushko, Adequacy of Personal Medical Profiles Data in Medical Information Decision
Making Support System, CEUR Workshop Proceedings, Vol-2544, 2020. [24] N. Melnykova, O. Markiv, Semantic approach to personalization of medical data, Computer Sciences and Information Technologies, CSIT, 2016, pp. 59-61. doi: 10.1109/STCCSIT.2016.7589868 [25] N. Melnykova, N. Shakhovska, T. Sviridova, The personalized approach in a medical decentralized diagnostic and treatment, 14th International Conference The Experience of Designing and Application of CAD Systems in Microelectronics, CADSM, 2017, pp. 295-297. doi: 10.1109/CADSM.2017.7916139 [26] S. Fedushko, N. Shakhovska, Y. Syerov, Verifying the medical specialty from user profile of online community for health-related advices, CEUR Workshop Proceedings 2255 (2018) 301310. [27] Y. Kryvenchuk, N. Shakhovska, I. Shvorob, S. Montenegro, M. Nechepurenko, The smart house based system for the collection and analysis of medical data, CEUR Workshop Proceedings 2255, (2018) 205-214. [28] I. Perova, Y. Bodyanskiy, Fast medical diagnostics using autoassociative neuro-fuzzy memory,
International Journal of Computing, 16(1), (2017) 34-40. [29] I. Perova, Y. Bodyanskiy, Adaptive human machine interaction approach for feature selectionextraction task in medical data mining, International Journal of Computing, 17(2) (2018) 113119.