Chapter 4 - Basic Data Definition Language (DDL)

Translating object-oriented database transactions into relational transactions

In the conceptual design of relational databases one of the main goals is to create a conceptual scheme, which minimize redundancies and eliminate deletion and addition anomalies, i.e. to create relation schemes in some good normal form. The study of relational databases has produced a host of normal forms: 2NF, 3NF, BCNF, Elementary-Key Normal Form, 4NF, Weak 4NF, PJ/NF, DK/NF, LTKNF, (3,3)NF, etc. There are two features which characterize these normal forms. First, they consider each relation separately. We believe that a normal form (which reflects the goodness of the conceptual design) should be related to the whole conceptual scheme. Second, the usefullness of all normal forms in relational database design have been based on the assumption that a data definition language (DDL) of a database management system (DBMS) is able to enforce key dependencies. However, different DDLs have different capabilities in defining constraints. In this paper we will discuss the design of conceptual relational schemes in general. We will also define a good normal form (GNF) which requires a minimally rich DDL; this normal form is based only on a primitive concept of constraints. We will not, however, discuss the normalization process itself – how one might, if possible, convert a relation scheme that is not in some normal form into a collection of relation schemes each of which is in that normal form.

Integration of large heterogeneous data collections, gathered usually for making decisions on information security (IS) management issues, requires a preliminary step -- unification of their data models. It is provided by mapping the source data models into the canonical information model. Information and semantics of data definition languages (DDL) and semantics of the operations of data manipulation languages (DML) have to be preserved by the mapping. This research is devoted to the unification of the graph data models (GDM) -- the important kind of the existing various data models. The distinguishing features of modern GDM are discussed as well as their application in the information security (IS) area. The issues of proof of DDL and DML semantics preserving by the mapping of the GDM into the object-frame canonical model are briefly considered. Future work steps in applying research results to different IS management areas are indicated in conclusion.

In this paper, we study the integration of functional logic programming and databases by presenting a data model, and a query and data definition language. The data model is adopted from functional logic programming by allowing complex values. The query and data definition language is based on the use of algebra expressions built from a set of algebra operators over an extended relational algebra. In addition, algebra expressions can be used for defining functions, typical in a functional logic program.

In the previous chapters, we introduced several dimensions that are useful to describe and measure information quality in its different aspects and meanings. Focusing on structured data, database management systems (DBMSs) represent data and relative operations on it in terms of a data model and a data definition and manipulation language, i.e., a set of structures and commands that can be represented, interpreted, and executed by a computer. We can follow the same process to represent, besides data, their quality dimensions. This means that in order to represent data quality, we have to extend data models.

In the previous nine chapters, you've learned everything you need to know to start building dynamic data-driven Web sites. In the next three chapters, you'll apply some real-world understanding to the process and look at building a complete application. Before then, though, you still have one topic to look at—the SQL Data Definition Language (DDL).KeywordsDatabase StructureCreate IndexSecurity CredentialDrop TableQuery OptionThese keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.

CHAPTER 15 - INFORMATION RESOURCE MANAGEMENT

: The purpose of IDA Memorandum Report M-459, An Oracle - Ada/SQL implementation, is to identify and describe a version of a software delivery, an Oracle - Ada/SQL (Structured Query Language) implementation, to the WIS Joint Program Management Office. The software system provides a Level 1 Ada/SQL implementation which interacts with the Oracle database management system (DBMS) . It provides the ability to define Ada/SQL Data Definition Language (DDL) and Ada/SQL Data Manipulation Language (DML) which will be converted to the appropriate SQL calls required by the Oracle DBMS. Ada/SQL is a binding between Ada and the database programming language SQL. The software was developed on a VAX 8600, using VAX/VMS version 4.6, and the DEC Ada compiler, version 1.4-33.

Proposes a query language, called MQL (Multimedia Query Language), for specifying and manipulating various types of multimedia information efficiently. The data models that describe the multimedia applications and varieties of media data types are established with the object-oriented notation. What the query language consists of can be separated into two parts: one is the data definition language and the other is the data manipulation language. In addition to common functions, MQL provides additional features, such as historical alternative multimedia matching queries, complex queries and nested queries. These functions satisfy most of the requirements of multimedia database querying. Consequently, MQL offers more powerful data access mechanisms for multimedia applications.

This paper describes the design of a system that allows the description of a data base independently from applications programs and independent of which languages (COBOL or FORTRAN) are used in the applications programs. The features of a new data definition language called COBOL-FORTRAN Data Definition Language (CFDDL) are discussed. Motivation and justification for a system with such characteristics are given.

The objects model (or object oriented [OO] model) is a conceptual-application model that is used to define a database schema representing a certain reality. The model views the world as consisting of objects belonging to classes. The objects of these classes have attributes, behavior (i.e., functions), and various relationships with other objects. The objects model can be presented as a class diagram (also termed OO diagram or objects diagram). Like an entity relationship diagram (ERD), the class diagram has two main goals: 1. To serve as a communication medium between the developers (analysts/ designers) and the users or their representatives. The diagram is created as a result of the interactions between the two parties, during which they discover and define the users’ information needs; the diagram serves like a contract between these two sides which summarizes the users’ needs. 2. To be the basis for further development of the information system (IS). Based on the diagram, it should be possible to design the database schema of the application, and (partially) the functions that it will have to perform. For that, it is necessary to transform the class diagram into an equivalent verbal description—an objects schema. This is done using an object definition language (ODL), similar to data definition language (DDL) in the relational model. In principle, all components of the class diagram are mapped to the objects schema. However, the objects schema includes more details which are not included in the diagram. For example, in the diagram each attribute has a name, and some attributes may have specific constraint definitions (e.g., key, unique); in the objects schema there are more detailed definitions, including the attributes’ domains or data types (e.g., numeric, char., real, etc.) and lengths. Another example, in the class diagram, we only write the names of the classes’ functions, while in the objects schema we specify the parameters of the functions. As aforementioned, there is a great deal of similarity between the OO and ER models and diagrams, since the ER model is one of the sources from which the objects model originated. But there are differences between the two models, which we will review later on. One of these differences is that the ER model is “static,” that is, it only deals with the data structure, while the objects model also includes “behavior,” that is, the functions that operate on the data. The rest of this chapter is dedicated to describing the components of the objects model and the class diagram. The description is organized in four main categories: objects and classes, attributes, relationships, and functions.

This paper presents HERMES, a prototype DB engine that defines a powerful query language for trajectory databases, which enables the support of mobility-centric applications, such as Location-Based Services (LBS). HERMES extends the data definition and manipulation language of Object-Relational DBMS (ORDBMS) with spatio-temporal semantics and functionality based on advanced spatio-temporal indexing and query processing techniques. Its implementation over two ORDBMS and its utilization in various domains proves the expressive power and applicability of HERMES in different application domains where knowledge regarding mobility data is essential. As a proof-of-concept, in this paper HERMES is applied to a case study related with vehicle traffic analysis, demonstrating its flexibility and usefulness for delivering custom-defined LBS.

Structured Query Language (SQL) is commonly used in Relational Database Management Systems (RDBMS) and is currently one of the most popular data definition and manipulation languages. Its core functionality is implemented, with only some minor variations, throughout all RDBMS products. It is an effective tool in the process of managing and querying data in relational databases. This paper describes a method to effectively automate the conversion of a data query from a Natural Language Query (NLQ) to Structured Query Language (SQL) with Online Analytical Processing (OLAP) cube data warehouse objects. To obtain or manipulate the data from relational databases, the user must be familiar with SQL and must also write an appropriate and valid SQL statement. However, users who are not familiar with SQL are unable to obtain relevant data through relational databases. To address this, we propose a Natural Language Processing (NLP) model to convert an NLQ into an SQL query. This allows novice users to obtain the required data without having to know any complicated SQL details. The model is also capable of handling complex queries using the OLAP cube technique, which allows data to be pre-calculated and stored in a multi-dimensional and ready-to-use format. A multi-dimensional cube (hypercube) is used to connect with the NLP interface, thereby eliminating long-running data queries and enabling self-service business intelligence. The study demonstrated how the use of hypercube technology helps to increase the system response speed and the ability to process very complex query sentences. The system achieved impressive performance in terms of NLP and the accuracy of generating different query sentences. Using OLAP hypercube technology, the study achieved distinguished results compared to previous studies in terms of the speed of the response of the model to NLQ analysis, the generation of complex SQL statements, and the dynamic display of the results. As a plan for future work, it is recommended to use infinite-dimension (n-D) cubes instead of 4-D cubes to enable ingesting as much data as possible in a single object and to facilitate the execution of query statements that may be too complex in query interfaces running in a data warehouse. The study demonstrated how the use of hypercube technology helps to increase system response speed and process very complex query sentences.

Databases are well suited to the task of describing and organizing research datasets, however, the difficulties of using database management systems effectively have resulted in their limited usage among domain scientists. Scientists operate in an environment that is changing steadily with new experimental protocols, instruments, and discoveries that impact what datasets they generate and how they describe and organize them. In order to manage datasets for a scientific application, scientists need to routinely revise their database schemas to reflect these changes. Unfortunately, evolving a database is one of the well-known and most difficult aspects of database usage. The conventional data definition and manipulation languages offer relatively low-level programming abstractions to perform complex database evolution tasks, and therefore require specialized technical skills not possessed by most domain scientists. A simplified means of expressing database evolution operations can reduce the effort of keeping the scientific database in sync with changing requirements. This paper presents a high-level, user-oriented, schema evolution framework with an algebra of specialized schema modification operators. The approach allows introduction of novel operators as motivated by new requirements and is amenable to well established optimization techniques for efficient planning and execution. We present the framework and its implementation, and we demonstrate its utility in an exemplar use case and performance evaluation.

Provenance is critical to the scientific integrity of archived data. It establishes the authenticity of science data by providing a detailed account of its origin, ownership, processing history, and any transformations that occurred over its lifecycle. In addition to validating authenticity, provenance facilitates the scientific evaluation of data and can be decisive in legal contexts regarding ownership and access.The Planetary Data System Information Model (PDS4 IM) provides some support for provenance such as limited processing history and source product identifiers.Nevertheless, the need for a more comprehensive and formalized approach to provenance tracking has been expressed across the PDS for derived data products, superseded unique identifiers, and the curation of data collections over time.To address these needs the W3C PROV Data Model (PROV-DM), Figure 1, has been adopted as the foundation for a robust, interoperable provenance model in PDS4.The PROV-DM Standard PROV-DM is a W3C Recommendation designed to support the interchange of provenance information on the Web. It introduces a generic yet expressive vocabulary for modeling entities, activities, and agents, as well as their interrelations such as wasDerivedFrom, wasGeneratedBy, wasAssociatedWith, and used. The adoption of PROV-DM in PDS4 provides a well-supported foundation for provenance interoperability. Figure 1 - PROV-DM: The W3C PROV Data ModelUsing GPT_4o, phase one consisted of the development of a general model aligned with PROV-DM and implemented as a PDS4 Local Data Dictionary (LDD), an extension to the PDS4 Information Model. As a general model, this LDD can in turn be specialized to support a diverse set of use cases across the PDS archive.Four use cases are currently under consideration. Each is conceptualized below by defining their PROV-DM entities and relationships.Voyager Corrected Image - This historical use case illustrates derivation of a corrected image from raw data and calibration files.Entities: CorrectedProduct, Raw Product, Calibration File Activities: Radiometric/Geometric Correction and Artifact Removal, Voyager Data Acquisition, Processing pipeline “vlev1” Agents: The software module components of “vlev1”, for example, findrx, remrx, and voycal. Relations: wasDerivedFrom, used, wasGeneratedBy, wasAttributedTo, wasAssociatedWith Superseded Logical Identifier (LID) - This use case tracks the relationship between a superseded identifier and a new identifierEntities: Superseded LID, New LID Activities: Superseding Activity Agents: PDS Discipline Node, data engineer Relations: wasDerivedFrom, wasAssociatedWith PDS Collection Stewardship – This use case describes the lifecycle of a collection across multiple versions and curatorial responsibility transfers.Entities: PDS Collection Version 1, PDS Collection Version 2 Activities: Archive, Management Agents: Discipline Node A, Discipline Node B Relations: wasGeneratedBy, wasAssociatedWith, used Planetary Science Observation – This use case captures the entities and relationships involved in a science observation.Investigation Goal: Volatile Mapping on Ryugu Spacecraft: Hayabusa2 (operated by JAXA) Instruments: Near-Infrared Spectrometer (NIRS3), Thermal Infrared Imager (TIR) Activity: Observation Campaign 1 Entities: Digital objects (e.g., spectra, images, thermographs) Targets: Crater A, Boulder Cluster B, Equatorial Ridge, Polar Region, Landing Site Z Relations: wasDerivedFrom, wasGeneratedBy, used, wasAssociatedWith, wasInformedBy A pseudo-XML representation representing some of the entities, activities, agents, and relationships for the first use case is provided below. Voyager Corrected Image Derived From EDR Image The VoyagerISSCorrectedImage was derived from the EDRImage. entity_voyager_corrected_image entity_voyager_edr_image activity_data_correction Some challenges remain in finalizing the implementation strategy:Identify and define the possible reasons for identifier supersession: Superseded, Replaced, Error, etc. Determine the suitability and granularity of W3C PROV relationships for the PDS. Determine appropriate use of bidirectional vs. unidirectional references between data products. Develop policies and procedures for governance to ensure consistent provenance recording and utilization. Conclusion The application of the W3C PROV-DM standard to the PDS4 information model represents a significant step toward improving the traceability, interoperability, and scientific trustworthiness of planetary data. The use cases presented demonstrate a broad applicability across contexts, from spacecraft imaging pipelines to multi-node stewardship models. Continued refinement of relationship types and implementation strategies will ensure alignment with both scientific practice and digital preservation principles.References W3C. (2013). PROV-DM: The PROV Data Model. https://www.w3.org/TR/prov-dm/AcknowledgementsThe research was carried out at the Jet Propulsion Laboratory, California Institute of Technology, under a contract with the National Aeronautics and Space Administration.During the preparation of this work the author(s) used GPT4o in order to transform natural language to First Order Logic (FOL) and then to several Data Definition Languages (DDL). After using this tool/service, the author(s) reviewed and edited the content as needed and take(s) full responsibility for the content of the publication.© 2025 California Institute of Technology. Government sponsorship acknowledged.