What is the difference between a schema and a table and a database?

schema -> floor plan

database -> house

table -> room

A relation schema is the logical definition of a table - it defines what the name of the table is, and what the name and type of each column is. It's like a plan or a blueprint. A database schema is the collection of relation schemas for a whole database.

A table is a structure with a bunch of rows (aka "tuples"), each of which has the attributes defined by the schema. Tables might also have indexes on them to aid in looking up values on certain columns.

A database is, formally, any collection of data. In this context, the database would be a collection of tables. A DBMS (Database Management System) is the software (like MySQL, SQL Server, Oracle, etc) that manages and runs a database.

In a nutshell, a schema is the definition for the entire database, so it includes tables, views, stored procedures, indexes, primary and foreign keys, etc.

This particular posting has been shown to relate to Oracle only and the definition of Schema changes when in the context of another DB.

Probably the kinda thing to just google up but FYI terms do seem to vary in their definitions which is the most annoying thing :)

In Oracle a database is a database. In your head think of this as the data files and the redo logs and the actual physical presence on the disk of the database itself (i.e. not the instance)

A Schema is effectively a user. More specifically it's a set of tables/procs/indexes etc owned by a user. Another user has a different schema (tables he/she owns) however user can also see any schemas they have select priviliedges on. So a database can consist of hundreds of schemas, and each schema hundreds of tables. You can have tables with the same name in different schemas, which are in the same database.

A Table is a table, a set of rows and columns containing data and is contained in schemas.

Definitions may be different in SQL Server for instance. I'm not aware of this.

From the PostgreSQL documentation:

A database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable. Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database he is connected to, if he has privileges to do so.

There are several reasons why one might want to use schemas:

• To allow many users to use one database without interfering with each other.

• To organize database objects into logical groups to make them more manageable.

• Third-party applications can be put into separate schemas so they do not collide with the names of other objects.

Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.

As per https://www.informit.com/articles/article.aspx?p=30669

The names of all objects must be unique within some scope. Every database must have a unique name; the name of a schema must be unique within the scope of a single database, the name of a table must be unique within the scope of a single schema, and column names must be unique within a table. The name of an index must be unique within a database.

More on schemas:

In SQL 2005 a schema is a way to group objects. It is a container you can put objects into. People can own this object. You can grant rights on the schema.

In 2000 a schema was equivalent to a user. Now it has broken free and is quite useful. You could throw all your user procs in a certain schema and your admin procs in another. Grant EXECUTE to the appropriate user/role and you're through with granting EXECUTE on specific procedures. Nice.

The dot notation would go like this:

Server.Database.Schema.Object

or

myserver01.Adventureworks.Accounting.Beans

In oracle Schema is one user under one database,For example scott is one schema in database orcl. In one database we may have many schema's like scott

I try answering based on my understanding of the following analogy:

• A database is like the house
• In the house there are several types of rooms. Assuming that you're living in a really big house. You really don't want your living rooms, bedrooms, bathrooms, mezzanines, treehouses, etc. to look the same. They each need a blueprint to tell how to build/use them. In other words, they each need a schema to tell how to build/use a bathroom, for example.
• Of course, you may have several bedrooms, each looks slightly different. You and your wife/husband's bedroom is slightly different from your kids' bedroom. Each bedroom is analogous to a table in your database.
• A DBMS is like a butler in the house. He manages literally everything.