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.

Schema behaves seem like a parent object as seen in OOP world. so it's not a database itself. maybe this link is useful.

But, In MySQL, the two are equivalent. The keyword DATABASE or DATABASES can be replaced with SCHEMA or SCHEMAS wherever it appears. Examples:

• CREATE DATABASE <=> CREATE SCHEMA
• SHOW DATABASES <=> SHOW SCHEMAS

Documentation of MySQL

SCHEMA & DATABASE terms are something DBMS dependent.

A Table is a set of data elements (values) that is organized using a model of vertical columns (which are identified by their name) and horizontal rows. A database contains one or more(usually) Tables . And you store your data in these tables. The tables may be related with one another(See here).