4

I am looking for metadata specifications (data dictionary) for SQL Server and Oracle databases.

By metadata map, I mean a definitive description of what objects make up database model and their relationships (i.e. a server has 0:n databases, a table has 1:n columns, a column has 0:n constraints).

Any versions of these DBMS platforms would be useful.

6

I've Community Wiki'd this

Oracle

  • Storage can hold multiple databases
  • Databases can be opened by multiple instances
  • Server can run run multiple instances
  • A server can only run a single instace for any given database
  • Data is stored in tablespaces
  • A tablespace may be made up of multiple data files
  • Tables/indexes/Materialized Views can be partitioned across multiple segments
  • A segment lives on a tablespace
  • A database object (table, index, stored program unit...) is owned by a user
  • A table may be be referenced (through a foreign key constraint) by another table, including a table owned by another user
  • A table may have at most one primary key constraint
  • A table may have zero or more unique constraints
  • A primary key or unique constraint is logically enforced by exactly one index (though you could have a multi-column unique constraint which is backed up by multiple unique indexes with the columns in a differing order)
  • An index may be unique or non-unique. A unique constraint may be enforced by a unique or non-unique index.
  • A table must have at least one column
  • Each column has a datatype (and optionally a length or precision/scale)
  • A trigger may relate to at most a single table. (Some triggers, such as SERVERERROR, don't relate to a table).
2
  • Thanks, why the cw? It wasn't a definitive answer, however it was certainly useful and deserving a +1
    – johnc
    Mar 8 '11 at 4:19
  • Databases can only be opened by multiple instances in RAC...
    – Gaius
    Mar 8 '11 at 20:46
2

For SQL Server, the Sybase poster will largely apply. The Oracle Interactive Quick Reference may also help.

2
  • Cheers, both very useful
    – johnc
    Mar 8 '11 at 20:56
  • I have a real poster of the Sybase one, 15 years old now :-)
    – Gaius
    Mar 8 '11 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.