Architecture, Design, Development, and Usage of ODBDetective 1.0

“ODBDetective is an Oracle database (Oracle) metadata mining tool for detecting violations of some crucial database (db) design, implementation, usage, and optimization best practice rules (bpr). This paper presents the set of bprs that is considered by the first full version (1.0) of ODBDetective, the db axioms from which they are derived, the corresponding tool’s facilities, and the essentials of its actual architecture, design, development, and usage, including the results of a case study on an Oracle production db. Moreover, even this first ODBDetective version also allows for storing semantic decision data on desired db scheme improvements, which will prove very useful to automatic improvement code generation in subsequent versions of this tool.”


Introduction
Too often, databases (dbs) are very poorly designed, implemented, queried, and manipulated.As a consequence, they allow storing implausible data, sometimes even do not accept plausible data, are very slow when data accumulates, and need too much unneeded disk and memory space, as well as, especially, programming and maintenance effort.
Consequently, significant efforts were made for designing and developing panoply of tools for investigating db issues, proposing better solutions to be carried out by db administrators (DBA), and even for automatic db schemes improvement.They range from those provided by db relational management systems (RDBMS) manufacturers to third party ones.
For example, Oracle provides Automatic Workload Repositories (AWR) views [1], Automatic SQL Tuning [2], SQL Access Advisor (including Partition Advisor), Real-time SQL Monitoring (the latter three available only in Enterprise Editions), as well as other advisors.Embarcadero Technologies includes in its DB PowerStudio for Oracle [3] a dedicated tool called DB Optimizer.Details on them, as well as on other theoretical and practical related work are provided in [4] and [5].
Moreover, there are also best practice rules in this field, like, for example, Oracle's ones [6].From a more comprehensive one [7], derived from experience, as well as from a set of db axioms [7], a relevant subset [8] was the base for selecting the 28 ones that are implemented in ODBDetective 1.0, according to 32 derived investigation types (see section 2).Note that three Oracle's ones (namely reduce data contention, choose indexing techniques that are best for your application, and use static SQL whenever possible) are included in ODBDetective too.
ODBDetective is a metadata mining tool for Oracle dbs, storing its mining and investigation results in its own Oracle db, together with additional semantic support decision data.
ODBDetective 1.0 was developed in Oracle 11g and MS Access 2010, as a MSc.Dissertation thesis to be publicly defended in July 3 rd , 2013, at the Bucharest Polytechnic University [5].
Section 2 presents the 20 axioms, 28 considered best practice rules, and the corresponding ODBDetective's 1.0 32 investigation types.Section 3 presents ODBDetective's architecture.Section 4 documents its db (back-end) and application (front-end) design and implementation.Section 5 contains essentials of ODBDetective's usage and the results A6.Constraints optimality axiom: for any db scheme, no implied constraint should be enforced and the constraint set should be enforceable in the minimum possible time (that is there should not exist another equivalent constraint set whose enforcement takes less time in the given context).

Implementation
A7. Constraints enforcement axiom: for each fundamental table, any of its constraints whose type is provided by the target RDBMS has to be enforced through that RDBMS; all other db constraints should be enforced by all software applications built on top that db.

A8. Rows uniqueness axiom:
Any fundamental table row should be always uniquely identifiable according to all existing uniqueness constraints in the corresponding sub-universe and it should correspond to a unique object from the corresponding object set.

A9.
No void rows axiom: For any fundamental table, at least one semantic fundamental column should not accept null values.A10.Referential integrity axiom: any foreign key value should always reference an existing value (dually, no db should ever contain dangling pointers).

A11. Primary keys axiom:
Any fundamental table should have a surrogate integer primary key whose range cardinality should be equal to the maximum possible cardinality of the corresponding modeled object set; except for tables corresponding to subsets, primary keys should store auto generated values.

A12. Foreign keys axiom:
Any foreign key should be simple (that is not concatenated), reference a surrogate integer primary key and have as range exactly the range of the referenced primary key.
A13. Key Propagation Principle axiom: any mapping (that is manyto-one or one-to-many relationship) between two fundamental object sets should be implemented according to the Key Propagation Principle: a foreign key referencing its co-domain (that is the "one" side) is added to the table corresponding to its domain (that is the "many" side).

A14. No superkeys axiom:
No superkey should ever be enforced; generally, no other constraints than those declared in the corresponding conceptual db scheme should ever be enforced.

A15.
Relevant data and processing axiom: Any query should consider and minimally process, in each of its steps, only relevant data.

A16. Fastest manipulation axiom:
At least in production environments, data manipulations should be done with best possible algorithms and technologies, so that processing speed be the fastest possible.

A17.
Reduce data contention: Intelligently use hard disks, big files, multiple tablespaces, partitions, and segments with adequate block sizes, separate user and system data, avoid constant updates of a same row, etc., in order to always reduce data contention to the minimum possible.

A18. Minimize db size:
Regularly shrink tablespaces, tables, and indexes for maintaining high processing speeds and minimal db and backup sizes.

A19. Maximize use of RDBMS statistics:
Regularly gather and intelligently use statistics provided by RDBMS for fine-tuning dbs.

A20. Follow RDBMS advisors recommendations:
Regularly monitor and apply all recommendations of RDBMS advisors.

Best Practice Rules
Design BPR0 (Data plausibility): All actual constraints should be enforced in all fundamental (that is not temporary, not derived) tables, in order to guarantee their data instances plausibility.

BPR1 (Surrogate primary keys):
Each fundamental table should have an associated primary surrogate key: an one-to-one integer property (range restricted according to the maximum possible corresponding instances cardinality-see BPR2 below), whose sole meaning should be unique identification of the corresponding lines (this being the reason to refer to them as surrogate or syntactic keys too).
Note that very rarely, by exception, such a surrogate key might also have a semantic meaning: for example, rabbit cages may be labelled physically too by the corresponding surrogate key values (and, obviously, no supplementary attribute should then be added for unique identification, as it would redundantly duplicate the corresponding surrogate key).Also note that the surrogate primary key #T (or TID) of a table T can be thought of as the x for all other columns of T (e.g.let T=COUNTRIES, with Country (1)='U.S.A.', Such minimal primary keys favor optimal foreign keys (see BPR5 below) with least possible time for computing joins.If storage space is a drastic concern, you might not add them to tables that are not referenced; otherwise, it is better to always add them both for avoiding tedious supplementary DBA tasks when they will become referenced too, as well as for homogeneity reasons.Obviously, derived/computed tables, be them temporary or not, may have no primary keys.Note that, not only in the Relational Data Model (RDM) [9], but also in all RDBMS versions, any key may be freely chosen as the primary one and that, actually, as a consequence, unfortunately, the vast majority of existing dbs are using concatenated and/or semantic, not only numeric, surrogate primary keys.

BPR2 (Instances cardinalities):
Surrogate keys should always take values in integer sets whose cardinalities should reflect maximum possible number of elements of the corresponding sets.
For example, #Cities values should be between 0 and 999 for states/regions/departments/lands/etc.(e.g. in Oracle, NUMBER (3)), or 99,999 (e.g.NUMBER(5)) for countries, or 99,999,999 (e.g.NUMBER( 9)) worldwide, whereas #Countries values should be between 0 and 250 (e.g.NUMBER(3)).Note that, for example, not specifying cardinality in Oracle (e.g. using only NUMBER), means that the system is using its corresponding maximum (i.e.NUMBER(38), which needs 22 bytes that not only wastes space, but is much slower, as it cannot be processed (e.g. for joins) by CPU arithmetic/logic units, which are the fastest ones, in only one memory cycle).

BPR3 (Semantic keys):
Any fundamental (not temporary, not derived) table corresponding to an object set that is not a subset (of another object set) should have associated all of its corresponding semantic (candidate, ordinary) keys: either one-to-one columns or minimally one-to-one column products.With extremely rare exceptions (see the rabbit cages example in BPR1 above), any such non-subset table should consequently have at least one semantic key: any of its lines should differ in at least one non-primary key column (note that there is not only one NULL value, as, for example, Oracle and MS SQL Server erroneously assume in this context, but an infinite number of NULLs, all of them distinct!).Only subsets and derived/ computed ones might not have semantic keys.
Note that any table may have more than one semantic key (and, according to a theorem from [7], combining those published in [10] and [11,12], a maximum number of keys equal to the combination of n taken [n/2] times, where n is the total number of semantic, nonprimary key columns of the table and [x] is the integer part of x).Obviously, in order to reject implausible instances, all of them (just like all of the other existing constraints) should always be included in the corresponding conceptual models, schemes, and implementations.
For example, in any context, COUNTRIES has the following two semantic keys: CountryName (there may not be two countries having same name) and Code (no two countries may have the same code, those used from vehicle plates to the U.N.); STATES has three keys: State • Country (there may not be two states of a same country having same name), Code • Country (there may not be two states of a same country having same code), and TelPrefix • Country (there may not be two states of a same country having same telephone prefix).Derived set *ExtStates, computed from STATES and COUNTRIES above (e.g. with an inner join between them on the object identifier #Country and Country, implemented as a foreign key in STATES referencing #Country, be it in a temporary table or in a persistent one), extending STATES with, say, CountryName, should not have any key (and no other constraint either!).Subset IPS of EMPLOYEES, storing only those employees having retention bonuses, together with their corresponding periods and amounts, should not have any semantic key either.
Note that, for all subsets tables, all of their lines may always be uniquely identified semantically too (through the subset surrogate primary key, see BPR1 above) by all of the keys of the corresponding superset table (in the above example, any important people in IPS, by all keys of EMPLOYEES).Also note that, obviously, subset tables too may have other semantic keys (not only their syntactical primary ones).

BPR4 (No superkeys):
We should never consider superkeys (i.e.one-to-one column products that are not minimal, that is for which there is at least one column that can be dropped and the remaining sub-product is also one-to-one; equivalently, superkeys are those products that properly include keys, i.e. they include at least one key without being equal to it), either conceptually, or, especially, for implementations.We should always stick to keys (see BPR3 above).
For example, obviously, within the U.S., both StateCode and StateName are and will always be unique (one-to-one, hence keys); trivially, these two constraints should be added to any dbs including the STATES table (obviously, only when the sub-universe is limited to the U.S. or to any other particular country; worldwide, these two columns are not one-to one anymore-see BPR3 above).
Even if not that trivial, you should never also add either a product superkey with any of them (e.g.StateCode • StatePopulation) or, worse, of both of them (StateCode • StateName): the result will not only be an unjustified bigger db, but especially a slower one, as it will have to enforce this superfluous constraint too for any insert or update concerning at least one of the corresponding values.Obviously, it would be even worse to replace such a key with, in fact, one of its superkeys: for example, if you do not add the two single unique constraints above, but, instead, you add the constraint that their product (StateCode • StateName) is unique, then you allow for implausible instances (e.g. that there may be several states having same name, but different codes, and/or several ones having same codes, but different names).
Note that, unfortunately, not only MS Access, SQL Server, Oracle, or MySQL do not reject superkeys!BPR5 (Foreign keys): Any foreign key should always reference the primary key of the corresponding table.Hence, it should be a sole integer column: we should never use concatenated columns, neither non-integer columns as foreign keys.Moreover, their definitions should match exactly the ones of the corresponding referenced primary keys (see BPR2 above).This rule is not only about minimum db space, but mainly for processing speed: numbers are processed by the fastest CPU subprocessors, the arithmetic ones (and the smaller the number, the fastest the speed: for not huge numbers, only one simple CPU instruction is needed, for example, in comparing two such numbers for, let's say, a join), whereas character strings need the slowest sub-processors (and a loop whose number of steps is directly proportional to the strings lengths); moreover, nearly a thousand natural numbers, each of them between 0 and nearly 4.3 billion, are read from the hard disk (the slowest common storage device) with only one read operation (e.g. from a typical index file), while reading a thousand strings of, let's say, 200 ASCII characters needs 6 such read operations.
Please note that, again, unfortunately, not only RDM, but almost all RDBMSs too allow foreign keys, be them concatenated or not, to reference not only primary keys (see BPR1 above), but anything else, including non-keys (provided that all of the corresponding columns belong to a same table ).
Please also note that, most unfortunately, it is a widespread practice to declare concatenated primary keys containing concatenated foreign keys for chains of referencing tables; consequently, even if the root of such a chain has only a column as its primary key, the next table in the chain should have a primary key with arity of at least two, the third one's arity should be of at least three, etc.
For example, in very many dbs, COUNTRIES' primary key is

BPR6 (At least one not null non primary key column per table):
Any table should have at least one not accepting nulls column, other than its primary key: what would a line having only nulls (except for its syntactic surrogate key value) stand for?

BPR7 (No constraints on not fundamental tables):
Temporary and derived (computed) tables should not have any constraints enforced: as they are not fundamental, they should be read-only for users; moreover, being computed from valid data with valid expressions, their instances are always plausible.Consequently, adding constraints on them would only slow down processing speed and increase db size for nothing.

BPR8 (No superfluous fundamental tables or rows):
Fundamental tables should be useful.If, for example, the instances of such a table are always empty, then that table is superfluous.Similarly, if the set of values (the image) of a column of a non-empty fundamental table is always empty, that column is superfluous.A fundamental table on which no object depends upon, except for its triggers, might also be superfluous.

Implementation in Oracle
BPR9 (Reduce data contention): Critical tables, (almost) static ones, core ones (to the enterprise, being used by several applications), very large ones, temporary ones, all of the others, and indexes of any db should be placed in distinct tablespaces (and all of them also distinct from the system ones) for optimal fine-tuning (e.g.caching all frequently used small static tables in memory-see BPR10 below-, setting adequate block sizes, etc.), and thus performance.
Whenever several hard disks are available, tablespaces should cleverly exploit them all (e.g.storing critical tables tablespace on the fastest one).Create associated data files with auto extension enabled, rather than creating many small ones.Separate user data from system dictionary data to reduce I/O contention.
As data contention can substantially hurt application performance, reduce it by distributing data in multiple tablespaces and partitions, avoid constant updates of a same row (e.g. to calculate balance), and run periodic reports instead.

BPR10 (Caching small frequently used tables):
Always cache small, especially lookup static (but not only) tables that are frequently used.

BPR11 (Use correct data types):
Using incorrect data types might decrease the efficiency of the optimizer, hurt performance, and cause applications to perform unnecessary data conversions.Don't use strings to store dates, times or numbers.Ensure that conditional expressions compare the same data types.Do not use type conversion functions (such as TO_DATE or TO_ CHAR) on indexed columns: use instead the functions against the values being compared to a column.

BPR12 (Not adding unnecessary indexes):
Indexes should not be added either for small instances tables or for columns containing mostly nulls in which you do not search for NOT NULL values.Obviously, there should not be more than one index on same columns (although there are RDBMSs allowing it!Fortunately, Oracle does not.).

BPR13 (Concatenated indexes column order):
For concatenated indexes, the order of their columns should be given by the cardinality of their corresponding duplicate values: the first one should have the fewest duplicates, whereas any other one should have more duplicates than its predecessor and fewer than its successor; columns having very many duplicates or NULLs should then be either placed last, or even omitted from indexes.
For example, as in a CITIES table there are much more distinct ZipCode values (rare duplicates being possible only between countries) than Country ones (as, generally, there are very many cities in a country), the corresponding unique index should be defined in the order <ZipCode, Country> (not as <Country, ZipCode>).

BPR14 (Indexes best types):
Normal (B-Trees) indexes should be used except for small range of values, when bitmap ones should be used instead.Note, however, that bitmap indexes are greatly improving queries, but are significantly slowing down updates and that they are available only for Enterprise Oracle editions and not for the standard ones too.

BPR15 (Indexes effectiveness):
For improving indexes effectiveness, DBAs should regularly gather statistics on them.(Note that on some RDBMS-e.g.Oracle starting with 10g-this can be scheduled and performed automatically.)

BPR16 (Avoid row chaining):
If pctfree setting is too low, updates may force Oracle to move rows (row migration).In some cases (e.g. when row length is greater than db block size) row chaining is inevitable.When row chaining and migration occur, each access of a row will require accessing multiple blocks, impacting the number of logical reads/writes required for each command.Hence, never decrease pctfree and always use largest block sizes possible.

Querying and manipulating
BPR17 (Use parallelism).Whenever possible, be it for querying and/or manipulating, use parallel programming! 1. Process several queries in parallel by declaring and opening multiple explicit cursors, especially when corresponding data is stored on different disks and the usage environment is a lowconcurrency one.
2. Create and rebuild indexes in parallel.
3. Use PARALLEL ENABLED functions (including pipelined table ones), which allows them to be safely used in slave sessions of parallel query evaluations.

BPR18 (Avoid dynamic SQL):
Whenever possible, avoid dynamic SQL and use views and/or parameterized stored procedures instead; when it is absolutely needed, keep dynamicity to the minimum possible (i.e.keep dynamic only what cannot be programmed otherwise and for the rest use static SQL) and prefer the dynamic native one, introduced as an improvement on the DBMS_SQL API, as it is easier to write and executes faster.
Note that DBMS_SQL is still maintained because of the inability of native dynamic SQL to perform so-called "Method 4 Dynamic SQL", where the name/number of SELECT columns or the name/number of bind variables is dynamic.Also note that native dynamic SQL cannot be used for operations performed on a remote db.Views and stored procedures have obvious advantages: not only they are already parsed, but they also have associated optimized execution plans stored and ready to execute.

BPR19 (Avoid subqueries):
Subqueries allow for much more elegant and close to mathematical logic queries, but are generally less efficient than corresponding equivalent join queries without subqueries (except for cases when RDBMSs optimizers replace them with equivalent subqueryless queries).

BPR20 (Use result cache queries):
For significant performance improvement of frequently run queries with same parameter values, always use result cache queries and query fragments: as their results are cached in memory (in the result cache, part of the shared pool), there is no more need to re-evaluate them (trivially, except for the first time and then only for each time when underlying data is updated).

BPR21 (Use compound triggers):
Always use compound triggers instead of ordinary ones, not only for new code, but also by replacing existing ordinary ones, whenever possible: they improve not only coding efficiency, but also processing speed for bulk operations.

BPR22 (Set firing sequences):
Always use, whenever necessary, setting the triggers firing sequence (clause FOLLOWS), in order to control their execution order.

BPR23 (Use function result cache):
Whenever appropriate, use result cache functions, which enhances corresponding code performance (e.g., according to Oracle, with at least 40% and up to 100% for most of pure Oracle PL/SQL code).

Optimization BPR24 (Regularly shrink tables and tablespaces):
Whenever rows are deleted from table instances, physical disk space they occupy is not freed, as, in fact, Oracle only marks them for deletion.Not only to gain storing space, but especially to speed up queries, you should regularly shrink involved tables and tablespaces.

BPR26 (Use key-compressed indexes):
Especially for unique multicolumn indexes, key compression should always be used for obtaining smaller and faster indexes: they eliminate repeated occurrences of key column prefix values, by sharing prefix entries among all suffix entries in index blocks.Note that the COMPRESS clause can be specified during either index creation or rebuild.

BPR27 (Follow Oracle advisors recommendations):
Oracle advisors are almost always right, so the best thing to do is to regularly monitor and do what they are suggesting, preferably automatically; failed ones should especially be monitored and necessary actions should be taken a.s.a.p.
ODBDetective 1.0 investigation types Design 1. Fundamental tables having no not-null columns (except for the primary key).
3. Fundamental tables (not corresponding to subsets) that don't have semantic keys.
5. Tables having non-numeric primary key columns.
9. Empty fundamental tables, their associated indexes and constraints.
10. Empty columns in non-empty fundamental tables, their associated indexes, and constraints.
11. Tables on which no other interesting (i.e.not trigger only referring to the corresponding table) object (e.g.view, stored procedure, etc.) depends upon.

Implementation
12. User tables and/or indexes in system tablespaces.
17.Over and under-sized foreign keys.
18. Concatenated indexes with wrong columns order.
20. Normal (B-tree) indexes that should be of bitmap type (as their columns have less than y% distinct values or more than z% null ones).
21. Bitmap indexes that should be of normal (B-tree) type (as their columns have more than y% distinct values and less than z% null ones).

Usage
23.Total number of sub-queries and each ones position in source code.
24.Total number of dynamic SQL executions and each ones position in source code.
25.Not parallel enabled functions and each ones position in source code.
26.Not result cache functions and each ones position in source code.
27.Not result cache queries and each ones position in source code.
28.Not compound triggers and each ones position in source code.
29. Triggers without firing sequences and each ones position in source code.
31.Indexes without recent statistics gathered on them.
32. Indexes to be compressed (on tables having less than w lines).

ODBDetective 1.0 Architecture
The 3-tier architecture of ODBDetective is presented in Figures 1  and 2: The light GUI, developed in MS Access 2010, provides users with a simple, three levels menu and forms/reports for displaying/printing mainly investigation results, but also for accepting corresponding parameter values (e.g. the desired thresholds for caching tables or compressing indexes), as well as managing Oracle target servers and dbs (users) needed data (server names, connection strings, dbs names, users accounts and passwords, IPs, etc.), and additional semantic decision support data; it also provides radio buttons for selecting desired detection options (e.g.full list of subsection 2.2 above or only partial ones) and buttons for launching metadata mining, investigations, etc. GUI-triggered events are handled by the VBA BL tier sub-layer, which is made up of three class forms (for servers, dbs, and investigation options respectively) and a library used by all of them.This sub-layer contains methods for adding, updating, and deleting data on targeted servers and dbs, deleting no more needed investigation data, adding (and even updating catalogue metadata) for the currently selected db, etc.
As usual, especially when extended SQL is available (PL/SQL in this case), there is also a RDBMS engine-based BL sub-layer, which, in this case, mainly includes detection and investigation views, but also triggers, sequences, stored functions and procedures, etc. Between these two BL sub-layers, a four level middleware pack is needed to bridge MS and Oracle, which comprises ADO (included in Access), MS ODBC Driver Manager, Oracle ODBC Driver for Windows, and Oracle Client 11g.
All MS Access 2010 layers of the solution are encapsulated in a pure code (except for the table which drives the menu) concurrent db called ODBDetective.accdb,which can be deployed on any number of workstations having (VPN) access to an Oracle 11g server where the ODBDetective's SQL & PL/SQL BL sub-layer, as well as its DB layer reside.Through db links, ODBDetective can mine and import metacatalog data from any number of other Oracle servers' dbs too.

Database design and implementation
ODBDetective's db design and implementation were done according to the algorithms presented in [7] and [13].For investigating possible violations of the best practice rules presented in section 2.2 above by Oracle db schemes, ODBDetective has to mine in the corresponding server catalog for metadata on dbs (users), tablespaces, objects, their dependencies, tables, views, columns, constraints, indexes, their columns, triggers, and PL/SQL stored functions and procedures code.Fortunately, Oracle provides both DBA and user versions of views for all such metadata, from which ODBDetective extracts only relevant columns into its own corresponding db tables.
Besides the above corresponding object sets, ODBDetective offers some dictionary-type ones (for decrypting Oracle internal codifications), as well as a set of Oracle servers and one of dbs (just like, for example, Oracle's SQL Developer, for saving needed connections data).Figure 3 presents the ODBDetective's structural Entity-Relationship (E-R) Diagram (E-RD).
Note that, for graphical reasons, canonical injections are represented as, instead of ⊂.Also note that, again for graphical reasons, although indexes are Oracle objects too, in Figure 3 this inclusion (INDEXES ⊂ OBJS) is not depicted.Moreover, note that, except for the properties of SERVERS and DBS, all other ones (except for some few properties added for future extensions and *DB of OBJS, as well as *Tablespace of OBJS, which are computed) are obtained through data mining, so they are read-only: consequently, there are no restrictions on them.Note too that the Package property of METHODS (which abstracts both stored PL/SQL functions and procedures) is not compulsory (as they may be methods defined outside PL/SQL packages).Perhaps the most important thing to note is that Oracle metacatalogs are uni-server: although all of its g versions are supporting grids of interconnected servers, there is no centralized metacatalog: consequently, ODBDetective had to add to its db computed property *Server of TABLESPACES.
Oracle, in fact, does not abstract PL/SQL stored function and procedures into a same methods set (so, for ODBDetective, METHODS=FUNCTIONS ⊕ PROCEDURES), neither differentiates between views and tables columns (although most of the views ones RL5 (servers' non-existence restrictions): whenever SID is null, ServiceName may not be null and vice-versa (i.e.whenever ServiceName is null, SID may not be null) (that is either SID or ServiceName should be specified, but not both of them)

Mathematical scheme
By applying the algorithms for translating E-RDs and restriction lists into mathematical schemes (see [7], [13]) and for detecting and designing constraints to the above E-RD and restrictions list, the following refined mathematical scheme is obtained:

SERVERS
Initial keys (according to RL4) are {S#, ServerName, HostIP}.Results of applying the algorithm for keys detection are given in Table 1.
Consequently, SERVERS does not have any other keys than the three initial ones.
Results of applying the algorithm for keys detection are given in Table 2. Consequently, DBS does not have any other keys than the initial one.

ServiceName
No No There may be any number of servers having same ServiceName; ServiceName cannot take part in any SERVERS key.

Pw
No No There may be any number of servers having same Pw; Pw cannot take part in any SERVERS key.

SMode
No No There may be any number of servers having same SMode; SMode cannot take part in any SERVERS key.

Protocol
No No There may be any number of servers having same Protocol; Protocol cannot take part in any SERVERS key.
Table 1: Applying the algorithm for keys detection according to RL4.

Table 2:
Applying the algorithm for keys detection according to RL9.

DB
No Yes There may be any number of dbs having same names (on different servers); according to RL9, DB takes part in the Server DB key.

Pw
No No There may be any number of dbs having same Pw (even on a same server); Pw cannot take part in any DBS key.

Server
No No There may be any number of dbs on a same server; according to RL9, Server takes part in the Server DB key.
These non-relational constraints are implemented in ODBDetective in VBA, which can provide users with context sensitive and immediate error messages (whilst Oracle is returning only context independent error messages, which are not immediate, at cell level, but always delayed at the row one).All ODBDetective's tables, constraints, sequences, triggers, db links, stored procedures, and views were implemented according to the algorithm [7,13] for translating relational schemes and associated non-relational constraint lists into Oracle 11g dbs, by using Oracle SQL Developer.Using Oracle's db links, DBA and user views, as well as parameterized stored procedures (the parameters being db links), ODBDetective is filling its db with all needed metadata for investigation, thus impacting very few (only in read-only mode, only from the metacatalogs, only once) on the customers' investigated Oracle remote servers, dbs, and network bandwidth (typically, the size of the ODBDetective's db is only some 200MB).Mined metadata is investigated by ODBDetective with the aid of (hierarchies of) views, built upon its db.
Note that, unfortunately, for tables SERVERS and DBS whose instances are managed by the ODBDetective MS Access application, their constraints had to be also implemented in VBA: in order to avoid getting the Oracle context-insensitive corresponding error messages, VBA is enforcing all these constraints too.Also note that if you declare NOT NULL constraints in Oracle, when users are trying to leave such a column cell null, unfortunately, corresponding VBA BeforeUpdate trigger-type methods are not invoked, as ADO is first passing nulls to Oracle, which is rejecting them.Consequently, the only way to give users context sensitive error messages in such cases (and, moreover, to help them by undoing accidentally emptied cells) is not to enforce NOT NULL constraints in Oracle, but only in VBA.This is why ODBDetective's NOT NULL constraints are not enforced in Oracle.
Dually, but for the same reason of displaying context sensitive error messages and undoing implausible data updates, as well as because of the fact that Oracle, ODBC, and ADO error trapping in VBA is problematic, the rest of the ODBDeetective's relational type constraints are enforced both in Oracle and VBA.
For example, there are times when Oracle or ODBC errors are not triggering ADO and/or Access corresponding errors; much worse, there are even contexts (e.g.connecting to an Oracle db) when, dually, although there is no Oracle, ODBC, or Access error, ADO is however reporting an error!
It was decided that the best software life cycle that suits ODBDetective's needs is the Prototype Software Development one, as there is only one developer, so that Incremental Prototyping methodology was used.
The only human actor that will interact with the ODBDetective application is the User, who analyzes db schemes for detecting anomalies of their design, implementation, and usage, and provides statistics and decision support data, for improving dbs performance by correcting their schemes in order to eliminate best practices violations.Figure 4 presents the ODBDetective system's use cases: ODBDetective's menu is a very simple one, consisting only of three levels, with a main menu page, a sub-menu one, and over 60 forms (some of them used as sub-forms on other three hierarchical levels of embedding), implemented by using MS Access standard Switchboard Manager.Most of the forms (over 40) are read-only, as they only  display mining and investigation data.The rest of them also allow for writing additional semantics and decision support data, so they also have associated VBA classes for enforcing corresponding constraints.
In order to avoid hard-codding and re-definitions, all commonly used constants and methods are grouped in a General VBA library.
Please note that, generally, VBA programming for ODBCconnected back-ends is more demanding than for MS Jet or ACME, or even SQL Server ones: for example, re-querying and even refreshing are not automatic (but need explicit programming), ADO, so Oracle too, are taking precedents in the events chain over VBA trigger-like methods (which, normally, is not the same).Figure 5 presents ODBDetective 1.0 main menu and Figure 6 its submenu.Figure 7 shows the Manage known servers and users window, with which users may add, update, and/or delete both Oracle db servers connection and db links data.Figure 8 displays the Investigations window.Figure 9 presents the Global Statistics window, which displays totals on both mined data and investigation results.Figure 10 shows the Table details form, which displays essential mined table, columns, triggers, constraints, and indexes data.Other similar forms display metadata on tablespaces, objects and dependencies between them, views, sequences, and PL/SQL source code lines, as well as Oracle's advisors log and recommendations for the investigated db. Figure 11 displays an example of detailed investigation result (tables violating       one best practice rule).Note that, for example, form Table details, between others, also allows for storing decision support data.Also note that, unfortunately, the only Oracle recommendation type is, in this case too, to perform shrinking: a useful, but not at all an enough one [4] also presents in detail the results of a ODBDetective's case study: investigating a production Oracle db consisting of 3,860 user objects (and 5,649 dependencies between them)-with only 116 being temporary and 191 automatically generated indexes ones-, out of which 586 tables (totaling 8,621 columns and 129,956,314 rows) interconnected by 415 foreign keys, with 66 temporary ones, and 2,842 indexes (out of which 2,651 are explicitly defined); their instances' plausibility is enforced by 3,860 constraints, out of which 278 are unique ones, with 229 primary and 49 semantic keys, 3,167 are check constraints, the remaining 415 being referential integrities; there are also one view, 151 sequences, 19 triggers, 127 packages (containing 1,647 procedures and 467 functions) totaling 129,730 PL/SQL lines.Running all of the 32 ODBDetective 1.0 options, discovered were the following: 9 180 empty non-temporary tables, on which there were defined 211 indexes and 243 constraints;

Oracle table schemes default values
gather statistics on indexes): For improving indexes effectiveness, DBAs should regularly gather statistics on them (by invoking stored procedures DBMS_STATS.GATHER_SCHEME_ STATISTICS and DBMS_STATS.GATHER_TABLE_STATISTICS). Note that, starting with 10g, this can be scheduled and performed automatically by the GATHER_STATS_JOB of the Automatic Statistics Collection tool.Moreover, you might use the COMPUTE STATISTICS clause of the CREATE INDEX statement.
are not automatically copied into Access forms, VBA object properties like NewRecord and OldValue are not available, etc.; moreover, as Oracle does not provide auto-numbering, you should first define corresponding Oracle sequences and triggers and then you should carefully program in VBA all corresponding methods, as these Oracle trigger generated surrogate key values are only generated just before inserting new rows in Oracle tables (and are null before), whereas Access auto-numbering generates them before saving any new data in virtual memory, long before saving it to disk, in dbs.Odbdetective 1.0 Usage and Case Study Installing ODBDetective 1.0 is very easy, as it only needs executing a SQL script for creating and initializing its Oracle 11g db and copying the ODBDetective.accdbMS Access 2007-2013 file in any folder of a pc running Windows 7/8.Configuring Access to remotely link to the Oracle db needs installing the Oracle 11g client (freely downloadable from the Oracle website), which also includes the corresponding Oracle ODBC driver for Windows (32 or 64bit, depending on your actual platform).

Figure 7 :
Figure 7: OBD Detective's application Manage known servers and users window.

Figure 11 :
Figure 11: OBD Detective's application example of investigation results.
TBLS_TYPES (the quasi-static set of ODBDetective table types)

table " ,
"Small table to be cached", "System tables: null value"} RL20 (table types' mandatory properties): Code, Comments RL21 (table types' uniqueness restrictions): Code and Comments are unique (there may not be two ODBDetective table types with same code or comment) The cycle having nodes DEPENDENCIES and OBJS is of commutative type, but with length = two, corresponding to the binary homogeneous relation DEPENDENCIES, which should obviously be acyclic (as no Oracle object may depend either directly or indirectly on itself); as this constraint is enforced by Oracle, no other explicit constraint needs to be added to the math scheme;

•
The cycle having nodes TRIGGERS, COLUMNS, VIEWS_ COLS, *COLUMNS, VIEWS, OBJS is of commutative type, but should not commute, as, trivially, any trigger attached to a table column is a different object than any view whose columns are based on that table column: no other explicit constraint needs to be added to the math scheme;

Add Server Connection Edit Server Connection Add Database Link Edit Database Link Mine Target Db Metadata Set Db to Investigate See Mined Metadata See Oracle Advisor Log User See Oracle Advisor Recommendations Investigate Target Db Issues Delete Database Link Delete Server Connection uc
9 89 fundamental not empty tables on which no interesting object (table, PL/SQL code, etc.) depends on; 9 no cached table, although there were 168 such candidates for x=1,000 and 58 for x=100; 9 268 constraints on temporary tables (257 check, 10 primary, and one semantic keys); 9 2 tables having migrated rows; 9 311 empty columns, out of which 161 were not VARCHAR (1 BLOB, 2 CLOB, 3 CHAR, 49 DATE, and 106 NUMBER), and on which defined were 24 constraints (1 check, 1 unique, and 22 foreign keys) and 21 indexes; 9 292 keyless non-empty fundamental tables; 9 40 tables having concatenated primary keys (3 quaternary, 8 ternary, and 29 double); 9 15 not numeric primary keys (11 VARCHAR2, 1 CHAR, and 3 DATE);