is there a common example SQL schema that shows all the major cardinalities?

Question

I'm writing up documentation for a tool that works with SQL schemas. It would make sense for the documentation to use a common example schema rather than making up a new one. Any recommendations for one that's already defined somewhere?

Ideally it would

• come from a problem domain most readers are already familiar with;
• have an example of all the major cardinalities (1-to-many, optional-to-many, many-to-many, optional-to-one) and have examples with and without "cascade delete" ownership;
• not be mired in meta-terms (not be a schema for schemas for example!)
• be simple;

I remember MS SQL Server 2000 documentation using their PUBS database, which was a database about publishers and authors and books. Not bad. But Microsoft has moved away from it to AdventureWorks and Northwind. Would anyone recommend any of those?

Answer 1

AdventureWorks has a pretty standard (read: not exotic) schema. It's more complex than Northwind, but still simple enough to fully grok.

I assume by optional to many you mean a PK/FK relationship where the FK can be null. I haven't checked AdventureWorks to see if it contains all of these cardinalities, but it would be easy enough to add simple examples for the "optional" ones.

Answer 2

If you are talking cross-platform, I woulnd't recommend MS schemas as they are bound to have SQL Server specific features used in the DDL, so you lose the benefit of having DDL provided to you. AdventureWorks seems to be a decent schema, although I have not looked at it carefully. You could create the DDL manually to recreate a similar one in Oracle and others if you want

You can also find tons of other more generic schema examples here:

Database Answers