Data Model and Relational Database Design for Highway Runoff Water-Quality Metadata

For additional information, contact: Director, New York Water Science CenterU.S. Geological Survey 425 Jordan Rd Troy, NY 12180 (518) 285-5695 http://ny.water.usgs.gov/ The laboratory for analysis of low-ionic-strength water at the U.S. Geological Survey (USGS) office in Troy, N.Y. analyzes samples collected by USGS projects in the Northeast. The laboratory’s quality-assurance program is based on internal and interlaboratory quality-assurance samples and quality-control procedures developed to ensure proper sample collection, processing, and analysis. For the time period addressed in this report, the quality-assurance/quality-control data were stored in the laboratory’s SAS data-management system, which provides efficient review, compilation, and plotting of quality-assurance/quality-control data. This report presents and discusses samples analyzed from July 1995 through June 1997. Quality-control results for 19 analytical procedures were evaluated for bias and precision. Control charts show that data from ten of the analytical procedures were biased throughout the analysis period for either high-concentration or low-concentration samples but were within control limits; these procedures were: acid-neutralizing capacity, total monomeric aluminum, ammonium, calcium, chloride, dissolved organic carbon, magnesium, nitrate (ion chromatography), nitrate (colorimetric method), and sulfate. Four of the analytical procedures were occasionally biased but were within control limits; they were: fluoride, pH, silicon, and sodium. Results from the filter-blank and analytical-blank analyses indicate that all analytical procedures in which blanks were run were within control limits, although values for a few blanks were outside the control limits. Sampling and analysis precision are evaluated herein in terms of the coefficient of variation obtained for triplicate samples in 14 of the 19 procedures. Data-quality objectives (DQO’s) were met by at least 92 percent of the samples analyzed in all procedures except acid neutralizing capacity (80 percent of samples met objectives), total monomeric aluminum (87 percent of samples met objectives), organic monomeric aluminum (89 percent of samples met objectives), and chloride (89 percent of samples met objectives). The data are insufficient to evaluate the DQO’s for total aluminum. Results of the USGS interlaboratory Standard Reference Sample Program indicated acceptable data quality for most constituents over the time period. The results of the P-sample (low-ionic strength constituent) analysis indicated high data quality with good ratings in all studies. The T-sample (trace constituent) had unacceptable ratings in two studies, but received satisfactory ratings in the others. The N-sample (nutrient constituent) studies had an unacceptable rating in one and an excellent rating in the other. Environment Canada’s NWRI program results indicated that at least 90 percent of the samples met data-quality objectives in 9 of the 12 analyses; exceptions were calcium, chloride, and silicon. Data-quality objectives were not met for calcium samples in two NWRI studies, but all of the samples analyzed were within control limits for the remaining studies. Data-quality objectives were not met for 32 percent of samples analyzed for chloride and 27 percent of samples analyzed for silicon. Results from blind reference-sample analyses indicated that data-quality objectives were met by at least 90 percent of the calcium, pH, potassium, and sodium samples. Data-quality objectives were met by 77 percent of the chloride samples, 83 percent of the magnesium samples, and 80 percent of the sulfate samples. There is insufficient data to evaluate the specific conductance samples.

The main problem has been managing databases because they are continuously expanding quickly and getting more complicated in terms of volume, variety, and velocity. Currently, Relational Database Management Systems (RDMS), also known as SQL, or traditional search engines are primarily used to manage and utilize data collection. Due to their highly semantic properties and applications, relational databases have emerged as powerful and superior during the previous two decades. The handling of a high volume of data and the variability in data type and structure has become a laborious task since Big Data entered the IT industry. Relational databases are inadequate to handle such big data due to their rigid data limitations, structure, relations, and other factors. As a result, data aggregation is rendered impossible. NoSQL databases offer a practical and understandable foundation for combining massive amounts of data, structures, and interactions. Data modeling and migration are now needed in order to define the issue. There isn't yet a tool for switching from relational to no-SQL databases. For this migration, relational (SQL) database queries must be converted to NoSQL database queries. Since relational databases (RDBMS) have found it difficult to keep up with modernization, NoSQL has emerged as the most practical database. This essay aims to highlight and assess the applications of NoSQL, the notion of NoSQL data modeling, and the NoSQL database migration procedure. NoSQL, NoSQL Roadmap, Data Modeling, ETL Approach for Data Migration, Data Loading, and Data Extraction.

Relational Database Design and Implementation

Many types of maritime incident databases have been established that allow people to learn from past incidents and develop corresponding mitigation measures. However, our investigation of multiple international and national databases shows that most existing databases only record basic information regarding incidents in a single table. Lots of useful information is not included in the database (i.e., limited extension of the database). Meanwhile, some basic information is recorded tautologically (i.e., data redundancy). In this paper, two widely used databases are taken as examples, the Global Integrated Shipping Information System and the Lloyd's List Intelligence, to explain these common problems of existing databases. To overcome these limitations and improve the efficiency of data maintenance, this paper develops a relational maritime safety management database. The entity-relationship model is first used to depict the inter-related semantic information surrounding maritime incidents, and a relational database model is subsequently formed. Microsoft Access is employed to implement the proposed database, and a database application is also designed to demonstrate the utility of the database. Our preliminary study shows that the proposed database is implementable and has potential usage for both industry and academic research.

The article focuses on comparing relational and non-relational databases using applications written in C#. The aim of the study is to understand in which cases relational databases are preferred and when it is worth considering the use of non-relational databases. The research examines the speed of data retrieval, updating, and deletion, in the context of five different databases, including relational ones like PostgreSQL, MySql, Oracle, and non-relational ones such as Neo4j and MongoDB. The data consists of 1,578,098 records. In the case of relational databases, a unified database model was applied, while in NoSQL databases, the data model was appropriately adjusted to the specific type of non-relational database. Differences in the execution time of database queries are analyzed based on indexing strategies and query complexity. Special attention is given to query performance efficiency in the context of using C# and libraries that facilitate the connection between the application and databases. The conducted research indicates that the PostgreSQL database achieves the lowest average query response times.

There is an established equivalence between relational database Functional Dependencies (FDs) and a fragment of switching algebra that is built typically of Horn clauses. This equivalence pertains to both concepts and procedures of the FD relational database domain and the switching algebraic domain. This study is an exposition of the use of switching-algebraic tools in solving problems typically encountered in the analysis and design of relational databases. The switching-algebraic tools utilized include purely-algebraic techniques, purely-visual techniques employing the Karnaugh map and intermediary techniques employing the variable-entered Karnaugh map. The problems handled include; (a) the derivation of the closure of a Dependency Set (DS), (b) the derivation of the closure of a set of attributes, (c) the determination of all candidate keys and (d) the derivation of irredundant dependency sets equivalent to a given DS and consequently the determination of the minimal cover of such a set. A relatively large example illustrates the switching-algebraic approach and demonstrates its pedagogical and computational merits over the traditional approach.

Modern large-scale information systems often use multiple database management systems, not all of which are necessarily relational. In recent years, NoSQL databases have gained acceptance in certain domains while relational databases remain de facto standard in many others. Many "legacy" information systems also use relational databases. Unlike relational database systems, NoSQL databases do not have a common data model or query language, making it difficult for users to access data in a uniform manner when using a combination of relational and NoSQL databases or simply several different NoSQL database systems. Therefore, the need for uniform data access from such a variety of data sources becomes one of the central problems for data integration. In this paper we provide an overview of the main problems, methods, and solutions for data integration between relational and NoSQL databases, as well as between different NoSQL databases. We focus mainly on the problems of structural, syntactic, and semantic heterogeneity and on proposed solutions for uniform data access, emphasizing some of the more recent proposals.

The article outlines the methods for creating SQL queries in relational databases. The use of the structured query language SQL in relational databases is substantiated. It provides information about the SQL standard and the three-tier database organization system. The author describes the choice of a data model based on the conceptual level using to that end an example of the Kokand Pedagogical Institute as the relational database model. A relational conceptual diagram of the information model of a pedagogical institute is compiled. Such a conceptual diagram is depicted using a cluster. Objects of the subject area are depicted in the form of tables, which differ from each other in geometric shapes or colors. The relationships between tables in Microsoft Access are presented. The basic rules for creating and filling tables in SQL using the instructions CREATE TABLE and INSERT INTO are considered. The syntax of the SELECT statement is given. All offers of the SELECT statement and their order are listed. Examples are given for compiling simple queries and subqueries in SQL using the SELECT statement for the database of the Kokand Pedagogical Institute. Information about the order of execution of internal and external requests is given. The article considers the ORDER BY offer of a SELECT statement for sorting query results.

In the database field, the requirement of high level facilities for retrieval/update operations is now increasing rapidly. Our approach from the relational database point of view for contribution to this problem is to provide 1) efficient processing of relational retrieval/ update operations, and 2) a high level user interface. In order to achieve this goal, a new concept concerning "abstracted characteristics" is presented. Abstracted characteristics are defined to be characteristics abstracted from sets of tuples in relations stored in the database. A classification of abstracted characteristics is presented. Functional dependencies, which play an important role in relational database design, and time-dependent functional dependencies are pointed out to be useful in processing retrieval/update operations. Some important applications of abstracted characteristics are discussed. Among them: 1) efficient processing of retrieval/update operations, 2) powerful view update checking facilities, 3) providing some rough meanings of null responses and 4) a high level user interface.

Chapter 2 - Querying

In this paper, we develop techniques to produce interoperable queries with object and relational databases. A user poses a local query in a local query language, against a local object or relational schema. We transparently produce appropriate queries with respect to a remote target object or relational schema, corresponding to some remote database which contains data relevant to the user's query. Mapping knowledge to resolve representational heterogeneities in local and remote schemas is expressed in a canonical representation, CRmapping, and is independent of the particular data model. A canonical representation CRquery is also used to resolve heterogeneities of query languages. A set of heterogeneous transformation algorithms define the appropriate transformations from the local queries to the remote queries. The use of canonical representations (CR) allows us to represent queries independent of the particular query language, and to resolve representational conflicts in a uniform manner, independent of models and query languages.

The Federal Highway Administration (FHWA) provides stewardship over the construction, maintenance and preservation of the Nation's highways, bridges and tunnels. News releases cover current events, research, highway safety, construction, mobility, and preservation efforts.

The Federal Highway Administration (FHWA) provides stewardship over the construction, maintenance and preservation of the Nation's highways, bridges and tunnels. News releases cover current events, research, highway safety, construction, mobility, and preservation efforts.

The Federal Highway Administration (FHWA) provides stewardship over the construction, maintenance and preservation of the Nation's highways, bridges and tunnels. News releases cover current events, research, highway safety, construction, mobility, and preservation efforts.

The Federal Highway Administration (FHWA) provides stewardship over the construction, maintenance and preservation of the Nation's highways, bridges and tunnels. News releases cover current events, research, highway safety, construction, mobility, and preservation efforts.