Foreign Keys Research Articles

Instance-optimal Truncation for Differentially Private Query Evaluation with Foreign Keys

Instance-optimal Truncation for Differentially Private Query Evaluation with Foreign Keys

DEVELOPMENT OF AN INFORMATION AND LOGICAL DATABASE MODEL FOR ACCOUNTING AND MOVEMENT OF WIND TURBINE EQUIPMENT

The article describes the information and logical model (ILM) of the database of accounting and movement of equipment of wind turbines of the Ermentau wind farm in the Akmola region. IFM is implemented in accordance with the IDEF1X methodology. The relationships of entities are shown and analyzed, the types of connections, the use of primary and foreign keys are determined. The application of the IDEF1X methodology used in the development of relational databases is shown, the conditional syntax is specially designed to build a conceptual scheme. The advantages of the IDEF1X methodology are described.

Database for Monitoring the Technical Condition of Structures as an Integral Part of Industrial Safety

Purpose. Development of a database designed to systematization and store information on the technical condition of industrial structures based on the results of periodic visual and instrumental surveys. Methodology. The information system includes a database, a database management system and an application programme. The most appropriate type of database for monitoring the technical condition of structures is a relational structure. The database model covers owners, buildings, structures, quantitative and qualitative parameters of defects and damage, strength indicators of building structures and tilt of industrial structures. Findings. Structural query language was used in the process of developing a database for monitoring the technical condition of industrial facilities. The created database "Monitoring" includes interconnected tables that provide holistic and efficient storage of information. The tables are linked using foreign keys, which provides easy access and data management. The tables are conditionally divided into those that users fill in and those that are filled in in accordance with legislative and regulatory documents at the stage of database creation. This approach to building a database ensures structured storage of information on the technical condition of buildings, taking into account regulations and legislation. The integration of tables filling in by users and pre-filled tables creates a system that increases the efficiency of monitoring the technical condition of industrial structures. Originality. The scientific novelty of the proposed database lies in an innovative approach to structuring and integrating various types of data related to the technical condition of industrial structures. The system offers an effective combination of data from different formats and sources into a single information space and will further provide a comprehensive analysis of the condition of building structures, which is one of the key factors of industrial safety. Practical value. It is intended to improve the efficiency of industrial structures maintenance and exploitation safety, and provides tools for storing the results of monitoring the condition of building structures.

PresQ: Discovery of Multidimensional Equally-Distributed Dependencies via Quasi-Cliques on Hypergraphs

Cross-matching data stored on separate files is an everyday activity in the scientific domain. However, sometimes the relation between attributes may not be obvious. The discovery of foreign keys on relational databases is a similar problem. Thus techniques devised for this problem can be adapted. Nonetheless, when the data is numeric and subject to uncertainty, this adaptation is not trivial. This paper firstly introduces the concept of <i>Equally-Distributed Dependencies</i>, which is similar to the <i>Inclusion Dependencies</i> from the relational domain. We describe a correspondence in order to bridge existing ideas. We then propose <small>PresQ</small>: a new algorithm based on the search of maximal quasi-cliques on hyper-graphs to make it more robust to the nature of uncertain numerical data. This algorithm has been tested on seven public datasets, showing promising results both in its capacity to find multidimensional equally-distributed sets of attributes and in run-time.

Real-time Tracking System for Distribution Information of Logistics Enterprises Based on IOT Technology

Logistics enterprise distribution information real-time tracking system in the actual application process, there is the problem of low cargo throughput, this study was aimed to design a kind of logistics enterprise distribution information real-time tracking system based on Internet of Things technology. In hardware part: control the reading and writing equipment according to the predetermined working mode, adjust the parameters of the reader, connect the circuit and other accessories; in software part: increase the correlation between the tables by setting foreign keys, build the logistics enterprise distribution information database, control and supervise the logistics activities. The result shows that the average value of cargo throughput between the real-time tracking system of distribution information of logistics enterprises in the paper and two other real-time tracking systems of logistics enterprises is 1412.14 T/H, 1049.38 T/H and 1088.81 T/H respectively, indicating that the real-time tracking system of distribution information of logistics enterprises designed after the application of IOT technology is more perfect.

Robustness against Read Committed for Transaction Templates with Functional Constraints

The popular isolation level Multiversion Read Committed (RC) trades some of the strong guarantees of serializability for increased transaction throughput. Sometimes, transaction workloads can be safely executed under RC obtaining serializability at the lower cost of RC. Such workloads are said to be robust against RC. Previous work has yielded a tractable procedure for deciding robustness against RC for workloads generated by transaction programs modeled as transaction templates. An important insight of that work is that, by more accurately modeling transaction programs, we are able to recognize larger sets of workloads as robust. In this work, we increase the modeling power of transaction templates by extending them with functional constraints, which are useful for capturing data dependencies like foreign keys. We show that the incorporation of functional constraints can identify more workloads as robust that otherwise would not be. Even though we establish that the robustness problem becomes undecidable in its most general form, we show that various restrictions on functional constraints lead to decidable and even tractable fragments that can be used to model and test for robustness against RC for realistic scenarios.

A Survey of Bigdata Analysis, Extracting Data and Mapping the Data

Data mapping is one of the simplest terms is to map source data fields and their related target data fields. Mapping can have a varying degree of complexity, depending on the number, data types, schema, primary keys, and foreign keys of the data sources. Nowadays, Archaeological research is based on an interdisciplinary approach which makes use of a wide range of technologies allowing for the collection of data and information about sites and archaeological findings. The purpose of archaeology is to learn more about past societies and the development of the human race. An essential part of the archaeological data is related to spatial information that links historical contents to the metric reconstruction of monuments and artifacts, and show their mutual relations in a map. A critical a part of the archaeological records is associated with spatial data that links ancient contents to the metric reconstruction. By processing a steady stream of all real-time data, organizations can make time-sensitive decisions faster than ever before, monitor emerging trends, course-correct rapidly and jump on new business opportunities. To design a data mapping framework process, the data from various sources uses a new proposed technique. To secure the high profile raw and analyzed data using the combination of hardware and software any key generation for data extraction and mapping. The information can be accessed only through the authenticated source of the framework and hence duplication and data theft is extremely difficult. This paper follows the various data mapping techniques handled in previous work and also shows the limitations of existing techniques.

A Model for Migrating Relational Database to NoSQL Database

Relational Database Management Systems (RDBMS) is widely used to store the Data and Information. As we already know RDBMS can store the data in the form of table but if we want to know the complex data relationships and interconnected information, we have to convert the relational database to graph database. Professionals working with airline databases will be able to quickly query highly connected data thanks to graph databases' ability to quickly uncover and manage new and beneficial associations. Relational databases use expensive huge joins to query related data, whereas graph data-stores contain direct references to their neighboring nodes. In order to handle the enormous quantity of airline data being created at a very high pace, scalability was therefore greatly needed. Additionally, the majority of airline data is semi- or un-structured, necessitating a schema-less database. In this paper a methodology to issues that occurs while converting an Airline relational to a graph database. By utilizing the source's restrictions and schema. Conjunctive SQL searches over the source are supported by the method and converted into graphs. procedures that traverse the target. To demonstrate the viability of the solution and the effectiveness of query response across the target database, the experimental outcomes are shown. Nodes are mapped to tuples, while edges are mapped to foreign keys. Constraints were maintained during the change. In order to build the suggested solution, SQL2Neo, popular graph database Neo4j and MySQL as relational databases were both used. Index Terms—Database migration, Graph, data, attributes, Relationships, tables.

PrivLava: Synthesizing Relational Data with Foreign Keys under Differential Privacy

Answering database queries while preserving privacy is an important problem that has attracted considerable research attention in recent years. A canonical approach to this problem is to use synthetic data. That is, we replace the input database R with a synthetic database R* that preserves the characteristics of R, and use R* to answer queries. Existing solutions for relational data synthesis, however, either fail to provide strong privacy protection, or assume that R contains a single relation. In addition, it is challenging to extend the existing single-relation solutions to the case of multiple relations, because they are unable to model the complex correlations induced by the foreign keys. Therefore, multi-relational data synthesis with strong privacy guarantees is an open problem. In this paper, we address the above open problem by proposing PrivLava, the first solution for synthesizing relational data with foreign keys under differential privacy, a rigorous privacy framework widely adopted in both academia and industry. The key idea of PrivLava is to model the data distribution in R using graphical models, with latent variables included to capture the inter-relational correlations caused by foreign keys. We show that PrivLava supports arbitrary foreign key references that form a directed acyclic graph, and is able to tackle the common case when R contains a mixture of public and private relations. Extensive experiments on census data sets and the TPC-H benchmark demonstrate that PrivLava significantly outperforms its competitors in terms of the accuracy of aggregate queries processed on the synthetic data.

R2T: Instance-optimal Truncation for Differentially Private Query Evaluation with Foreign Keys

Answering SPJA queries under differential privacy (DP), including graph pattern counting under node-DP as an important special case, has received considerable attention in recent years. The dual challenge of foreign-key constraints and self-joins is particularly tricky to deal with, and no existing DP mechanisms can correctly handle both. For the special case of graph pattern counting under node-DP, the existing mechanisms are correct (i.e., satisfy DP), but they do not offer nontrivial utility guarantees or are very complicated and costly. In this paper, we propose the first DP mechanism for answering arbitrary SPJA queries in a database with foreign-key constraints. Meanwhile, it achieves a fairly strong notion of optimality, which can be considered as a small and natural relaxation of instance optimality. Finally, our mechanism is simple enough that it can be easily implemented on top of any RDBMS and an LP solver. Experimental results show that it offers order-of-magnitude improvements in terms of utility over existing techniques, even those specifically designed for graph pattern counting.

Technical Perspective on 'R2T: Instance-optimal Truncation for Differentially Private Query Evaluation with Foreign Keys

Increased use of data to inform decision making has brought with it a rising awareness of the importance of privacy, and the need for appropriate mitigations to be put in place to protect the interests of individuals whose data is being processed. From the demographic statistics that are produced by national censuses, to the complex predictive models built by "big tech" companies, data is the fuel that powers these applications. A majority of such uses rely on data that is derived from the properties and actions of individual people. This data is therefore considered sensitive, and in need of protections to prevent inappropriate use or disclosure. Some protections come from enforcing policies, access control, and contractual agreements. But in addition, we also seek technical interventions: definitions and algorithms that can be applied by computer systems in order to protect the private information while still enabling the intended use.

When is it safe to run a transactional workload under Read Committed?

The popular isolation level multiversion Read Committed (RC) exchanges some of the strong guarantees of serializability for increased transaction throughput. Nevertheless, transaction workloads can sometimes be executed under RC while still guaranteeing serializability at a reduced cost. Such workloads are said to be robust against RC. This paper provides a high level overview of deciding robustness against RC. In particular, we discuss how a sound and complete test can be obtained through the formalization of transaction templates. We then increase the modeling power of transaction templates by extending them with functional constraints which are useful for capturing data dependencies like foreign keys. We show that the incorporation of functional constraints can identify more workloads as robust than would otherwise be the case. Even though the robustness problem becomes undecidable in its most general form, we establish that various restrictions on functional constraints lead to decidable and even tractable results that can be used to model and test for robustness against RC for practical scenarios.

Migrating a Well-Established Longitudinal Cohort Database From Oracle SQL to Research Electronic Data Entry (REDCap): Data Management Research and Design Study.

Providing user-friendly electronic data collection tools for large multicenter studies is key for obtaining high-quality research data. Research Electronic Data Capture (REDCap) is a software solution developed for setting up research databases with integrated graphical user interfaces for electronic data entry. The Swiss Mother and Child HIV Cohort Study (MoCHiV) is a longitudinal cohort study with around 2 million data entries dating back to the early 1980s. Until 2022, data collection in MoCHiV was paper-based. The objective of this study was to provide a user-friendly graphical interface for electronic data entry for physicians and study nurses reporting MoCHiV data. MoCHiV collects information on obstetric events among women living with HIV and children born to mothers living with HIV. Until 2022, MoCHiV data were stored in an Oracle SQL relational database. In this project, R and REDCap were used to develop an electronic data entry platform for MoCHiV with migration of already collected data. The key steps for providing an electronic data entry option for MoCHiV were (1) design, (2) data cleaning and formatting, (3) migration and compliance, and (4) add-on features. In the first step, the database structure was defined in REDCap, including the specification of primary and foreign keys, definition of study variables, and the hierarchy of questions (termed "branching logic"). In the second step, data stored in Oracle were cleaned and formatted to adhere to the defined database structure. Systematic data checks ensured compliance to all branching logic and levels of categorical variables. REDCap-specific variables and numbering of repeated events for enabling a relational data structure in REDCap were generated using R. In the third step, data were imported to REDCap and then systematically compared to the original data. In the last step, add-on features, such as data access groups, redirections, and summary reports, were integrated to facilitate data entry in the multicenter MoCHiV study. By combining different software tools-Oracle SQL, R, and REDCap-and building a systematic pipeline for data cleaning, formatting, and comparing, we were able to migrate a multicenter longitudinal cohort study from Oracle SQL to REDCap. REDCap offers a flexible way for developing customized study designs, even in the case of longitudinal studies with different study arms (ie, obstetric events, women, and mother-child pairs). However, REDCap does not offer built-in tools for preprocessing large data sets before data import. Additional software is needed (eg, R) for data formatting and cleaning to achieve the predefined REDCap data structure.

MMKP: A mind mapping knowledgebase prototyping tool for precision medicine.

BackgroundWith significant advancements in the area of precision medicine, the breadth and complexity of the relevant knowledge in the field has increased significantly. However, the difficulty associated with dynamic modelling and the disorganization of such knowledge hinders its rapid development potential.ResultsTo overcome the difficulty in using the relational database model for dynamic modelling, and to aid in the organization of precision medicine knowledge, we developed the Mind Mapping Knowledgebase Prototyping (MMKP) tool. The MMKP implements a novel design that we call a “polymorphic foreign key”, which allows the establishment of a logical linkage between a single table field and a record from any table. This design has advantages in supporting dynamic changes to the structural relationships in precision medicine knowledge. Knowledge stored in MMKP is presented as a mind map to facilitate human interaction. When using this tool, medical experts may curate the structure and content of the precision knowledge in a flow that is similar to the human thinking process.ConclusionsThe design of polymorphic foreign keys natively supports knowledge modelling in the form of mind mapping, which avoids the hard-coding of medical logic into a rigid database schema and significantly reduces the workload that is required for adapting a relational data model to future changes to the medical logic. The MMKP tool provides a graphical user interface for both data management and knowledgebase prototyping. It supports the flexible customization of the data field constraints and annotations. MMKP is available as open-source code on GitHub: https://github.com/ZjuLiangsl/mmkp.

Best Practices for Relational Database Optimization using Microsoft SQL

The main problem when querying a database is the response time. The research was of an applied type, using two databases: control and experimental. Three computers have been used to execute ten queries to the two databases, running 4 consecutive times and obtaining an average. The results found were that, of the ten consultations carried out, seven consultations obtained better results in the experimental DB and three in the control DB. It is concluded that the best practices to optimize a database are: to create clustered indexes on columns frequently used in searches or to perform sorts, create non-clustered indexes on primary or foreign keys that do not have clustered indexes, use calculated columns, operators, and listing of proper columns in queries, however, the use of indexes should be restricted because they affect insert, update, and delete operations.&#x0D; Keywords: relational database, database optimization, SQL, indexing.

Performance of Graph and Relational Databases in Complex Queries

In developing NoSQL databases, a major motivation is to achieve better efficient query performance compared with relational databases. The graph database is a NoSQL paradigm where navigation is based on links instead of joining tables. Links can be implemented as pointers, and following a pointer is a constant time operation, whereas joining tables is more complicated and slower, even in the presence of foreign keys. Therefore, link-based navigation has been seen as a more efficient query approach than using join operations on tables. Existing studies strongly support this assumption. However, query complexity has received less attention. For example, in enterprise information systems, queries are usually complex so data need to be collected from several tables or by traversing paths of graph nodes of different types. In the present study, we compared the query performance of a graph-based database system (Neo4j) and relational database systems (MySQL and MariaDB). The effect of different efficiency issues (e.g., indexing and optimization) were included in the comparison in order to investigate the most efficient solutions for different query types. The outcome is that although Neo4j is more efficient for simple queries, MariaDB is essentially more efficient when the complexity of queries increases. The study also highlighted how dramatically the efficiency of relational database has grown during the last decade.

Upaya Peningkatan Kemampuan Penggunaan Microsoft Access Programming Bagi Pegawai Unit Usaha Bank Sumut Syariah

This training aims to improve the ability to use microsoft access programming for employees of the Bank Sumut Syariah business unit. This training was attended by all employees because this training is considered very important to support the performance of the employees. Currently, the Bank Sumut Syariah business unit has implemented technology based on Microsoft Access Programming in the context of carrying out all office activities. This training is carried out by means of socialization to employees, discussions, training and also direct practice of its use. The service activities for the Microsoft Access Programming Training have been carried out well. The service activity for the Microsoft Access Programming Training received an enthusiastic response from the participants of the Bank Sumut Syariah unit employees. Microsoft Access Programming training is carried out in stages and distributed in 14 meetings with a student center learning system and project based learning model so that employees can solve work problems with the skills that have been taught. Microsoft Access is a relational computer database application program that is used to design, create and process various types of data with large capacities. Database is a collection of related tables. Between tables with one another are related to each other, so it is often called a relational database. The relations between tables are connected by a key, namely the primary key and foreign key

Interactive Visual Exploration of Big Relational Datasets

We propose and implement a tool for the access, analysis, and visual exploration of very large relational databases and data warehouses. To this end, we represent the database as a graph in which each table is represented by a tree rooted at the key and in which there is an edge from table T to table T’ if there is an attribute of T which is a foreign key of T’. The interface allows the user to define analytic queries by clicking on nodes and edges of the graph. The user actions are then translated into SQL group-by queries that are evaluated by the underlying database management system. Additionally, the interface allows the user to visualize the analysis results, and to interact with visualizations in order to explore the database content.

Natural Stored and Inherited Relations

A stored and inherited relation (SIR) is a 1NF stored relation (SR) enlarged with inherited attributes (IAs). We show that one may consider a usual scheme of an SR R with foreign keys as implicitly defining a natural SIR R. IAs do not introduce normalization anomalies, while a usual select-project-join query to SR R with foreign keys becomes select-project only towards natural SIR R. Such queries become thus logical navigation free (LNF), without any additional data definition work, unlike at present. We show how the usual SR schemes provide for natural SIRs. We show also that making a popular DBS SIR-enabled should be simple. Pre-existing relations with foreign keys could be trivially converted to SIRs, preserving existing applications, while providing for LNF queries for new ones. We postulate every major DBS becoming SIR-enabled “better sooner than later”.

Retrofitting GDPR compliance onto legacy databases

New privacy laws like the European Union's General Data Protection Regulation (GDPR) require database administrators (DBAs) to identify all information related to an individual on request, e.g. , to return or delete it. This requires time-consuming manual labor today, particularly for legacy schemas and applications. In this paper, we investigate what it takes to provide mostly-automated tools that assist DBAs in GDPR-compliant data extraction for legacy databases. We find that a combination of techniques is needed to realize a tool that works for the databases of real-world applications, such as web applications, which may violate strict normal forms or encode data relationships in bespoke ways. Our tool, GDPRizer, relies on foreign keys, query logs that identify implied relationships, data-driven methods, and coarse-grained annotations provided by the DBA to extract an individual's data. In a case study with three popular web applications, GDPRizer achieves 100% precision and 96--100% recall. GDPRizer saves work compared to hand-written queries, and while manual verification of its outputs is required, GDPRizer simplifies privacy compliance.