AndroMDA

ALWAYS FINDING RIGHT PEOPLE

Database Schema

A database schema of a database system is its structure described in a formal language supported by the database management system. The term "schema" refers to the organization of data as a blueprint of how the database is constructed. The formal definition of a database schema is a set of formulas or sentences called integrity constraints imposed on a database. These integrity constraints ensure compatibility between parts of the schema. All constraints are expressible in the same language. A database can be considered a structure and realization of the database language. The states to create a conceptual schema are transformed into an explicit mapping, the database schema. This describes how real-world entities are modeled in the database.

A database schema specifies, based on the database administrators knowledge of possible applications, the fact second enter the database, or those of interest to the possible end-users. The notion of a database schema plays a same role as the notion of theory in predicate calculus. A model of this "theory" closely corresponds to a database, which can be seen at any instant of time as a mathematical object. Thus ischemic and contain formulas representing integrity constraints specifically for an application and the constraints specifically for a type of database, all expressed in the same database language. In a relational database, the schema defines the tables, fields, relationships, views, indexes, packages, procedures, functions, queues, triggers, types, sequences, materialized views, synonyms, database links, directories, XML schemas and other elements. A database generally stores his schema and a data dictionary. Although a scheme is defined in text database language, the term is often used to refer to a graphical depiction of the database structure. In other words, schema is the structure of the database that defines the objects in the database. In an Oracle Database system the term "schema" has a slightly different connotation.

Ideal requirements for schema integration

The requirements listed below influence a detailed structure of schemas that are produced. Certain applications will not require that all of these conditions are met, but these four requirements are the most ideal.

1. Overlap preservation - each of the overlapping elements specified in the input mapping is also in a database schema relation.
2. Extended overlap preservation - source specific elements that are associated with the sources overlapping elements are passed through to the database schema.
3. Normalization - independent entities and relationships in the source data should not be grouped together in the same relation in the database schema. In particular, source specific scheme elements should not be grouped with overlapping schema elements, if the grouping co-locates independent entities or relationships.
4. Minimality - if any elements of the database schema are dropped in the database schema is not ideal.

Example of two schema integrations

Suppose we want a mediated (database) schema to integrate two travel databases, Go-travel and Ok-travel.
Go-travel has two relations:
Go-flight (f-num, time, meal (yes/no))
Go-price (f-num, date, price)
(f-num being the flight number)
Ok-travel has just one relation:
Ok-flight (f-num, date, time, price, nonstop (yes/no)
The overlapping information in Ok-travelís and Go-travelís schemas could be represented in a mediated schema:
Flight (f-num, date, time, price)

Oracle database specificity

In the context of Oracle databases, a schema object is a logical data storage structure. An Oracle database Associates a separate schema with each database user. A schema comprises a collection of schema objects which include: tables, views, sequences, synonyms, indexes, clusters, database links, snapshots, procedures, functions, packages. On the other hand, non-schema objects may include: users, roles, contacts, directory objects. Schema objects do not have a one-to-one correspondence to physical files on disk that store their information. However, Oracle databases store schema objects logically within a table space of the database. The data of each object is physically contained in one or more of the tablespaceís datafiles. For some objects such as tables, indexes etc. a database administrator can specify how much disk space the Oracle RDBMS allocates for the object within the tablespaceís datafiles. There is no necessary relationship between schemas and tablespaces: a tablespace can contain objects from different schemas, and the objects for a single schema can reside in different tablespaces.