Foreign key

Abstract: DISCOVER operates on relational databases and facilitates information discovery on them by allowing its user to issue keyword queries without any knowledge of the database schema or of SQL. DISCOVER returns qualified joining networks of tuples, that is, sets of tuples that are associated because they join on their primary and foreign keys and collectively contain all the keywords of the query. DISCOVER proceeds in two steps. First the Candidate Network Generator generates all candidate networks of relations, that is, join expressions that generate the joining networks of tuples. Then the Plan Generator builds plans for the efficient evaluation of the set of candidate networks, exploiting the opportunities to reuse common subexpressions of the candidate networks. We prove that DISCOVER finds without redundancy all relevant candidate networks, whose size can be data bound, by exploiting the structure of the schema. We prove that the selection of the optimal execution plan (way to reuse common subexpressions) is NP-complete. We provide a greedy algorithm and we show that it provides near-optimal plan execution time cost. Our experimentation also provides hints on tuning the greedy algorithm.

Abstract: A Relational Database Management System (RDBMS) having any arbitrary structure is translated into a multi-dimensional data model suitable for performing OLAP operations upon. If a relational table defining the relational model includes any tables with cardinality of 1,1 or 0,1, the tables are merged into a single table. If the relational table is not normalized, then normalization is performed and a relationship between the original table and the normalized table is created. If the relational table is normalized, but not by dependence between columns, such as in the dimension table in a snowflake schema, the normalization process is performed using the foreign key in order to generate the normalized table. Once the normalized table is generated, OLAP measures are derived from the normalized relational table by an automated method. In addition, OLAP dimensions are derived from the normalized relational table and the results of the OLAP measures derivation by an automated method according to the present invention. According to an aspect, it is possible to associate a member of a dimension to another member of the same or another dimension. According to another aspect, it is possible to create a new dimension of analysis, the members of which are all the different values that a scalar expression can take on. According to yet another aspect, it is possible to access the various instances of a Reporting Object as members in an OLAP dimension. According to the yet another aspect, it is possible to apply opaque filters or a combination of them to the data that underlies analysis.

Abstract: Method and system of the invention migrate data from one or more ASCII files and/or from one or more relational databases to one or more relational database tables without the need to write code. In doing so, the invention allows the user to define mapping templates and conditionals to assist in translating and transforming data values. The invention also enforces referential integrity, data dependencies, order of operations, and uniqueness constraints using a predefined set of migration rules templates that are based on the principles of relational design. The invention uses these mapping and migration rules templates to intelligently generate instructions for updating or populating relational database destination tables. The instructions control the data transfer, data translation, data transformation, data validation, foreign key insertion, and the addition of required codes and flags in the destination tables. A migration engine of the system includes a data map architect and an update processor which spawns the templates and migrates the data dynamically, utilizing the data definitions for the destination tables. In addition to supporting conversion efforts, the invention provides support for performing consolidation, restoration from an archive, migration to new instances, upgrading to a new release, adding bolt-ons and enhancements, and changing business requirements. The audit trail in the invention is complete enough that the entire migration process can be reversed without compromising the integrity of either the source or the destination application.

Abstract: List of Figures. List of Tables. Foreword. Preface. Acknowledgments. Steering. Major Developers. Non-code Contributors. History of POSTGRESQL. Introduction. University of California at Berkeley. Development Leaves Berkeley. POSTGRESQL Global Development Team. Open Source Software. Summary. Issuing Database Commands. Starting a Database Session. Choosing an Interface. Choosing a Database. Starting a Session. Controlling a Session. Typing in the Query Buffer. Displaying the Query Buffer. Erasing the Query Buffer. Getting Help. Exiting a Session. Summary. Basic SQL Commands. Relational Databases. Creating Tables. Adding Data with INSERT. Viewing Data with SELECT. Selecting Specific Rows with WHERE. Removing Data with DELETE. Modifying Data with UPDATE. Sorting Data with ORDER BY. Destroying Tables. Summary. Customizing Queries. Data Types. Quotes Inside Text. Using NULL Values. Controlling DEFAULT Values. Column Labels. Comments. AND/OR Usage. Range of Values. LIKE Comparison. Regular Expressions. CASE Clause. Distinct Rows. Functions and Operators. SET, SHOW, and RESET. Summary. SQL Aggregates. Aggregates. Using GROUP BY. Using HAVING. Query Tips. Summary. Joining Tables. Table and Column References. Joined Tables. Creating Joined Tables. Performing Joins. Three- and Four-Table Joins. Additional Join Possibilities. Choosing a Join Key. One-to-Many Joins. Unjoined Tables. Table Aliases and Self-joins. Non-equijoins. Ordering Multiple Parts. Primary and Foreign Keys. Summary. Numbering Rows. Object Identification Numbers (OIDs). Object Identification Number Limitations. Nonsequential Numbering. Nonmodifiable. Not Backed Up by Default. Sequences. Creating Sequences. Using Sequences to Number Rows. Serial Column Type. Manually Numbering Rows. Summary. Combining SELECTs. UNION, EXCEPT, and INTERSECT Clauses. Subqueries. Subqueries as Constants. Subqueries as Correlated Values. Subqueries as Lists of Values. NOT IN and Subqueries with NULL Values. Subqueries Returning Multiple Columns. ANY, ALL, and EXISTS Clauses. Summary. Outer Joins. Subqueries in Non-SELECT Queries. UPDATE with FROM. Inserting Data Using SELECT. Creating Tables Using SELECT. Summary. Data Types. Purpose of Data Types. Installed Types. Character String. Number. Temporal. Logical. Geometric. Network. Internal. Type Conversion Using CAST. Support Functions. Support Operators. Support Variables. Arrays. Large Objects (BLOBs). Summary. Transactions and Locks. Transactions. Multistatement Transactions. Visibility of Committed Transactions. Read Committed and Serializable Isolation Levels. Locking. Deadlocks. Summary. Performance. Indexes. Unique Indexes. CLUSTER. VACUUM. VACUUM ANALYZE. EXPLAIN. Summary. Controlling Results. LIMIT. Cursors. Summary. Table Management. Temporary Tables. ALTER TABLE. GRANT and REVOKE. Inheritance. Views. Rules. LISTEN and NOTIFY. Summary. Constraints. NOT NULL. UNIQUE. PRIMARY KEY. Foreign Key/REFERENCES. Modification of Primary Key Row. Multicolumn Primary Keys. Handling NULL Values in the Foreign Key. Frequency of Foreign Key Checking. CHECK. Summary. Importing and Exporting Data. Using COPY. COPY File Format. DELIMITERS. COPY Without Files. Backslashes and NULL Values. COPY Tips. Summary. Database Query Tools. Psql. Query Buffer Commands. General Commands. Output Format Options. Output Format Shortcuts. Variables. Listing Commands. Large Object Commands. Psql Command-line Arguments and Start-up File. Pgaccess. Summary. Programming Interfaces. C Language Interface (LIBPQ). Pgeasy (LIBPGEASY). Embedded C (ECPG). C++ (LIBPQ++). Compiling Programs. Assignment to Program Variables. ODBC. Java (JDBC). Scripting Languages. Perl. TCL/TK (PGTCLSH/PGTKSH). Python. PHP. Installing Scripting Languages. Summary. Functions and Triggers. Functions. SQL Functions. PL/PGSQL Functions. Triggers. Summary. Extending POSTGRESQL Using C. Write the C Code. Compile the C Code. Register the New Functions. Create Operators, Types, and Aggregates. Summary. Administration. Files. Creating Users. Creating Databases. Access Configuration. Local. Host and Hostssl. User Mappings. Examples. Backup and Restore. Server Start-up and Shutdown. Monitoring. Performance. System Tables. Internationalization. Upgrading. Summary. Additional Resources. Mailing List Support. Supplied Documentation. Commercial Support. Modifying the Source Code. Frequently Asked Questions (FAQs). Installation. Getting POSTGRESQL. Creating the POSTGRESQL User. Configuration. Compilation. Installation. Initialization. Starting the Server. Creating a Database. PostgreSQL Nonstandard Features by Chapter. Reference Manual. ABORT. ALTER GROUP. ALTER TABLE. ALTER USER. BEGIN. CLOSE. CLUSTER. COMMENT. COMMIT. COPY. CREATE AGGREGATE. CREATE CONSTRAINT TRIGGER. CREATE DATABASE. CREATE FUNCTION. CREATE GROUP. CREATE INDEX. CREATE LANGUAGE. CREATE OPERATOR. CREATE RULE. CREATE SEQUENCE. CREATE TABLE. CREATE TABLE AS. CREATE TRIGGER. CREATE TYPE. CREATE USER. CREATE VIEW. createdb. createlang. createuser. DECLARE. DELETE. DROP AGGREGATE. DROP DATABASE. DROP FUNCTION. DROP GROUP. DROP INDEX. DROP LANGUAGE. DROP OPERATOR. DROP RULE. DROP SEQUENCE. DROP TABLE. DROP TRIGGER. DROP TYPE. DROP USER. DROP VIEW. dropdb. droplang. dropuser. ecpg. END. EXPLAIN. FETCH. GRANT. initdb. initlocation. INSERT. ipcclean. LISTEN. LOAD. LOCK. MOVE. NOTIFY. pg_ctl. pg_dump. pg_dumpall. pg_passwd. pg_upgrade. pgaccess. pgtclsh. pgtksh. postgres. postmaster. psql. REINDEX. RESET. REVOKE. ROLLBACK. SELECT. SELECT INTO. SET. SHOW. TRUNCATE. UNLISTEN. UPDATE. VACUUM. vacuumdb. Bibliography. Index. 0201703319T04062001