Understand Relational Databases

Understanding the history and theory behind relational databases helps us achieve a deeper understanding of Oracle SQL. This information helps us avoid repeating old mistakes, and it gives us the confidence needed to occasionally ignore the theory and build practical solutions. This book assumes you are already familiar with relational databases and SQL, so the information provided here is not merely introductory; it is foundational.

The challenge to prepare the graduates for working in a constantly changing environment like software engineering requires an effective learning framework. This chapter presents a tool, integrated in the Moodle learning management system, that allows students to train the process of designing relational databases. The tool also allows them to practice with SQL queries that are executed over relational databases previously designed. This chapter also describes the result of a qualitative analysis of its use in an engineering course offered at the University of Valladolid and focused on the teaching of the Web applications development. The results of the refereed study reveal that the tool was found useful by both students and teachers to support the teaching and learning process of relational databases.

Introduction. Why a Handbook of Relational Database Design? Introduction to Logical Data Modeling. Relational Concepts and SQL. Introduction to Relational Database Design. Build Skeletal User Views. Add Keys to User Views. Add Detail to User Views. Validate User Views through Normalization. Determine Additional Attribute Business Rules. Integrate User Views. Translate the Logical Data Structure. Translate the Logical Data Integrity. Tune by Establishing Storage-Related Access Mechanisms. Tune by Adding Indexes. Tune by Introducing Controlled Data Redundancy. Tune by Redefining the Relational Database Structure. Special Design Challenges. Appendix. Bibliography. Index of Design Rules. Index.

The Pathfinder XQuery compiler has been enhanced by a new code generator that can target any SQL:1999-compliant relational database system(RDBMS). This code generator marks an important next step towards truly relational XQuery processing, a branch of database technology that aims to turn RDBMSs into highly efficient XML and XQuery processors without the need to invade the relational database kernel. Pathfinder, a retargetable front-end compiler, translates input XQuery expressions into DAG-shaped relational algebra plans. The code generator then turns these plans into sequences of either SQL:1999 statements or view definitions which jointly implement the (sometimes intricate) XQuery semantics. In a sense, this demonstration thus lets relational algebra and SQL swap their traditional roles in database query processing. The result is a code generator that (1) supports an almost complete dialect of XQuery, (2) can target any RDBMS with a SQL:1999 language interface, and (3) exhibits quite promising performance characteristics when run against high-volume XML data as well as complex XQuery expressions.

Embedded devices are increasingly being used for data collection and on-device data analysis for applications in environmental and infrastructure monitoring, health and wearable computing, and sensor and mobile systems. Processing data on the device rather than transmitting it over a network for analysis reduces energy consumption, network bandwidth usage, and results in more robust and longer functioning devices. A key challenge is enabling efficient data management on devices that may only have a few KBs of memory and limited code space. Previous work has demonstrated that using relational databases is possible on embedded devices with restrictions on the queries that can be processed. In this work, we eliminate one of the key barriers to using relational technology on embedded devices, which is the massive overhead involved in SQL parsing and translation that can take up to 50% of the code and memory resources on the device. Our approach allows developers to continue to use relational APIs and SQL during development which are then pre-compiled when deployed on the device. This produces all of the benefits of relational systems without the on-device overhead and limitations. Experimental results demonstrate that query pre-compiling can reduce query parse times by up to 90% and on-device execution times by up to 50%. The technique is applicable to a wide range of embedded systems and databases.

In this paper we succinctly approach the theoretical basis of the presented research on mediation and exchange of experiences between professors and students and students among themselves considering Vygotsky’s social historical view. Next we present the research methodology, as well as the Smart Tutorial System built to evaluate the way possible levels of assistance between the different actors are dealt with, in the case of study of relational algebra, database and Structured Query Language-SQL in higher courses in Information Technology – IT. Subsequently, we have put this system in operation and, finally, we have considered the appropriate solutions proposed by students to the queries defined as parameters in the teaching of relational algebra, database and SQL, and some results and conclusions obtainable by means of such analysis. Index Terms - educational software, levels of assistance, mediation, smart tutorial system.

With the increasing popularity of object-relational technology, it is becoming important to have a methodology which allows designers to exploit the great modelling power of object-oriented conceptual models (OOCMs) and yet which still facilitates implementation on relational database systems. This paper presents a practical solution for the implementation of different types of object-oriented association relationships, which include a wide range of collection types (i.e. sets, lists, arrays and bags), into relational database tables. The implementation strategies raise a number of constraints relating to data integrity in the database system. These constraints are associated with (i) normalisation of the resulting relational tables, and (ii) data integrity after insertion/deletion. In order to ensure completeness of the strategies, some techniques for the implementation of inverse traversal in association relationships are also provided. An example is used throughout this paper to demonstrate and evaluate the proposed method.

We implement an efficient image retrieval system via a content-based approach in the relational database system. In our system, as the preprocessing stages, we extract the color and texture features from the input image to get the retrieval keywords in the image database. First, our system classifies the images roughly by the color features using the ART2 neural network model. The classified information is stored in a relational database with the original image. To retrieve images similar to the query image, we extend the relational SQL statement coupled with the ART2 function in the WHERE condition clause. Next, to retrieve final results of images, we compare the query image with the candidate images which have the same category information by the texture features. The proposed system is implemented on Oracle DBMS. It shows satisfactory retrieval results in a test of the sample image database.

Nearest neighbor (NN) search in high dimensional space is an important problem in many applications. Ideally, a practical solution (i) should be implementable in a relational database, and (ii) its query cost should grow sub-linearly with the dataset size, regardless of the data and query distributions. Despite the bulk of NN literature, no solution fulfills both requirements, except locality sensitive hashing (LSH). The existing LSH implementations are either rigorous or adhoc. Rigorous-LSH ensures good quality of query results, but requires expensive space and query cost. Although adhoc-LSH is more efficient, it abandons quality control, i.e., the neighbor it outputs can be arbitrarily bad. As a result, currently no method is able to ensure both quality and efficiency simultaneously in practice.

The article analyzes current cyber threats targeting Ukraine in the context of hybrid warfare, particularly attacks conducted by the Russian Federation. The analytical foundation of the study is based on official reports from the National Security and Defense Council of Ukraine, the general NATO report, and the 2024 report of the State Service of Special Communications and Information Protection of Ukraine. The main types of attacks identified within critical information infrastructure are systematized, with a focus on those directed at governmental agencies, energy, transport, and communication sectors. Based on the identified threats, the necessity to enhance the protection of information systems against unauthorized access is substantiated through the implementation of cryptographic methods. The study highlights the relevance of applying cryptographic mechanisms to ensure the confidentiality, integrity, and authenticity of data in digital environments. A conceptual software module was developed using a relational SQL database platform to store cryptographic keys, certificates, and encrypted user messages. The module enables controlled data management, providing multilevel user authorization and protecting information from unauthorized modifications. Experimental testing confirmed the effectiveness of the selected cryptographic methods and demonstrated their practical applicability in information security systems. The obtained results underscore the value of the proposed approach for strengthening cybersecurity in the face of modern threats.

Accurate geological interpretation depends on integrating multiple remote sensing datasets, as single-sensor methods often miss the full spatial and spectral complexity of outcrops. This study introduces a multi-sensor workflow that combines UAV photogrammetry, close-range smartphone LiDAR, and VNIR – SWIR hyperspectral imaging. The datasets are fused during preprocessing, stored in a relational SQL database, and embedded in a 3D visualization environment for interactive analysis. Within this platform, users can perform real-time rendering and point-based spectral interrogation of georeferenced data at centimeter-to-meter scales. We applied this workflow to the Vaca Muerta Formation, identifying four stratigraphic units and resolving vertical mineralogical gradients, including the distribution of illite, smectite, gypsum, and chlorite. Classification accuracy was comparable to traditional hand specimen analysis, while integrated visualization revealed structural and mineralogical boundaries missed by single-sensor methods. We assess how spatial resolution, mesh simplification, and sensor alignment influence geometric fidelity, uncertainty, and interpretive reproducibility. By embedding spectral data in a georeferenced 3D framework, our workflow enables reproducible stratigraphic segmentation and facies differentiation in fine-grained, low-contrast sedimentary systems. Though developed for geology, the approach is transferable to other fields requiring precise multi-sensor integration, such as urban planning, civil engineering, and digital twin development.

This article discusses data security and controls, primarily in the context of data base management systems (DBMSs), with an emphasis on basic principles and mechanisms that have been successfully used by practitioners in actual products and systems. The general discussion does not focus on particular products, although a later section does review several products to illustrate previously discussed concepts. The discussion of data bases is limited specifically to relational DBMSs, which store data in relations that have specific mathematical properties. All examples given are in SQL. It is assumed that readers are familiar with rudimentary concepts of relational data bases and SQL. This article begins with a review of basic security objectives, followed by a discussion of access controls in the current generation of commercially available DBMSs. The problem of multilevel security is then introduced, including a review of techniques developed specifically for multilevel security.

Целью исследования является повышение эффективности экосистемы города и уменьшение временных затрат на формирование сравнительного анализа исследуемых компонентов в атмосферном воздухе. Представлена структура исходных данных, предоставленных лабораторией охраны окружающей среды для визуализации мест загрязнений атмосферы. Приведены проектные решения разработки программного обеспечения в виде ментальных схем и блок-схем. В результате разработано программное обеспечение с использованием географической информационной системы (ГИС) с открытым кодом и системой управления реляционными базами данных MS SQL Server, что делает ПО универсальным с возможностью использования в любой местности. The purpose of the study is to increase the efficiency of the city ecosystem and reduce the time spent on the formation of a comparative analysis of the studied components in the atmospheric air. The authors present the structure of the initial data provided by the environmental protection aboratory for visualization of atmospheric pollution areas. Design solutions for software development in the form of mental diagrams and block diagrams are given. As a result, special software was developed using an open source geographic information system (GIS) and a relational database management system MS SQL Server, which makes the software universally applicable in any area.

Farmers manage the cropland in a decentralized manner at present in China,and usually fertilization could not be applied rationally and scientifically.This paper presented a variable fertilization system for dispersive farmer,which was designed on the basis of Geographic Information System(GIS).The system referred a relational database SQL Server 2008 as a built-in database,in order to store,inquire and update kinds of information,such as the variability of soil,the crop yields over the years,and the fertilization formulas.The statistical analysis of the soil attribute can be realized by spatial interpolation,the conversion from raster to vector,data fusion,and overlay analysis in the system.And a fertilization formula for a cropland can be generated automatically by invoking the yield model and the fertilization model.Furthermore,the fertilization formula can be delivered to SMC6480 to match the fertilizer scientifically.The experimental results indicate that the system is stable and reliable,and all the modules ran well.

The COCOON project was intended to extend the concepts and the architecture of relational database management systems (DBMSs) beyond nested relational to object-oriented ones. Based upon the nested relational DBMS kernel DASDBS, we have built a prototype implementation of the COCOON model. Key characteristics of COCOON are: generic, set-oriented query and update operators similar to relational algebra and SQL updates, respectively; object-preserving semantics of query operators, which allows for the definition of updatable views; a separation of the two aspects of programming language “classes”: type vs. collection; predicative description of collections, similar to “defined concepts” in KL-One-like knowledge representation languages; automatic classification of objects and views (positioning in the class hierarchy); physical clustering of subobjects via the use of nested relations as the internal storage structures; support for the optimization of both, the physical DB design and query transformation, by corresponding optimizers.