Schema Oracle

ArrayExpress implementation at EBI will run on an Oracle 8i platform however, database schema will be easily portable to other RDBMSs. The supported data import format will be MAML, a MIAME-compliant XML language Images will not be stored inside the database, they will be archived on tapes or direct access media such as CD-R or DVD-R. 

Oracle change management pack -compares schemas, bundles changes, predicts errors... 

The approach that has been taken by and large by commercial vendors is to include at most a few small features in the DBMS itself and then provide robust tooling that operates above the database. One feature that is fairly common across systems is transactional DDL-, CREATE, ALTER, and DROP statements can be bundled inside transactions and undone via a rollback. A consequence of this feature is that multiple versions of schemas at a time may be maintained for each table and potentially for rows within the table for concurrent access. Even though multiple versions of schema may exist internally within the engine, there is still only a single version available to the application. PostgreSQL, SQL Server, and DB2 all support this feature in Oracle, DDL statements implicitly mark a transaction boundary and run independently. 

On the other hand, there is little support to propagate schema changes to dependent schema objects, such as views, foreign keys, and indexes. When one alters a table, either the dependent objects must themselves be manually altered in some way, or the alteration must be aborted. The latter approach takes the majority of the time. For instance, SQL Server aborts any attempt to alter a column if it is part of any index, unless the alteration is within strict limits - namely, the alteration is a widening of a text or binary column. Dropped columns simply cannot participate in any index. DB2 has similar restrictions Oracle invalidates dependent objects like views so that they must be revalidated on next use and fails to execute them if they do not compile against the new schema version. 

Since the release of version 9i, Oracle also provides a schema evolution feature called redefinition (Oracle Database lOg Release 2 2005). Redefinition is performed on single tables and allows the DBA to specify and execute multiple schema or semantic modifications on a table. Changes such as column addition or deletion, changing partitioning options, or bulk data transformation can be accomplished while the table is still available to applications until the final steps of the update propagation. 

Oracle Microsoft SQL Server IBM DB2 Altova Diff Dog XEM Kramer (2001), Su et al. (20011 Model-based approaches (X-Evolution, CoDEX, UML) Temporal XML schema... 

Oracle offers two very different ways to evolve an XML schema (Oracle XML Schema Evolution 2008). The first is a copy-based mechanism that allows a great deal of flexibility. Data from an XML document collection are copied to a temporary location, then transformed according to a specification, and finally replaced in its original location. The second is an in-place evolution that does not require any data copying but only supports a limited set of possible schema changes. 

Oracle has supported XML in tables and columns since version 9i (9.0.1) as part of XML DB, which comes packaged with Oracle since version 9.2. One can specify a column to have type XMLType, in which case each row of the table will have a field that is an XML document, or one can specify a table itself to have type XMLType, where each row is itself an XML document. In both cases, one can specify a single schema for the entire collection of documents. For instance, one can specify an XML column to have a specified given schema as follows ... 

Microsoft SQL Server, like Oracle, supports storing a collection of homogeneous XML documents in a relation column (Pal et al. 2006). Whereas instances in an XML-typed column or table in Oracle must conform to a specific schema with a specific global element as root, an XML-typed column in SQL Server validates against any schema in a collection of schemas and allows any global element as root. One specifies an XML Schema Collection in SQL server using a DDL statement CREATE XML SCHEMA COLLECTION [ . ] sql.identifier AS Expression... 

The monotonic nature of alterations to a schema collection X means that, for the most part, documents that conform to collection X will continue to validate against the collection after alteration (maintaining the same reverse-compatibility restriction of the in-place evolution in Oracle). The one exception is if the collection contains a lax validation wildcard or any element whose type is xs anyType. In such a case, the addition of new global elements to the schema collection could cause documents to fail validation. So, if any existing schema elements include such a construct, revalidation of existing documents will happen any time new global elements are added, and if the revalidation fails, the action is aborted. 

Where Tamino differs from Oracle is that Tamino allows the stored data to determine reverse compatibility rather than the schema document versions themselves. One can pass a parameter to the. define command to attempt to do some static validation first - determining just from the documents themselves whether it is possible for reverse compatibility to be guaranteed - but eventually all documents are... 

Oracle Edition-Based Redefinition (2009) Whitepaper. Available at http //www.oracle.com/ technology/deploy/availability/pdf/edition based redefinition.pdf Oracle XML Schema Evolution (2008) Chapter 9 of Oracle XML DB, Developer s Guide, 1 lg Release, May 2008... 

Each of the aforementioned data sources was created and evolved independently over time based on the needs and demands associated with each individual system. In many cases, each system had its own interface in addition to its own data and queries across systems and data sources were difficult or impossible. Most of the data were available in Oracle databases although a significant amount existed only in Excel workbooks and other non-database files. The Oracle data were spread across over 100 database schemas stored on 24 individual database instances. Because of time and resource constraints and our design philosophy, a decision was made to employ a federated approach in which data would be accessed from its original location whenever possible but integrated into a unified view via the Mobius software. 

---