This is probably a n00blike (or worse) question. But I've always viewed a schema as a table definition in a database. This is wrong or not entirely correct. I don't remember much from my database courses.
2,212 5 5 gold badges 33 33 silver badges 41 41 bronze badges asked Nov 18, 2008 at 13:41 13.7k 5 5 gold badges 23 23 silver badges 17 17 bronze badgesIn oracle schema is just referred to a user. That is when we create an user the schema will be created now we can add tables, views,indexes packages etc. as
Commented Dec 9, 2014 at 9:48schema -> floor plan
database -> house
table -> room
9,978 5 5 gold badges 73 73 silver badges 110 110 bronze badges answered Nov 18, 2008 at 13:45 MusiGenesis MusiGenesis 75.1k 41 41 gold badges 196 196 silver badges 337 337 bronze badgesA little cryptic, but not wrong; but do you really think a selfproclaimed n00b would understand that?
Commented Nov 18, 2008 at 13:49Good analogy. I would change "floor plan" to "blue prints" because the schema has more than just tables, and blue prints contain the wiring and heating and plumbing.
Commented Nov 18, 2008 at 13:49"Schema" really just means "plan". I've seen it used to refer to the entire database, or just one table or view.
Commented Nov 18, 2008 at 13:50@Paul: good points. I originally wrote "blueprints" but changed it because it doesn't really connote "house" (as least not to me).
Commented Nov 18, 2008 at 13:52Yea I tend to view a schema as a user in Oracle. So definitions even vary from db to db :D How annoying.
Commented Nov 18, 2008 at 14:05A 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.
answered Nov 18, 2008 at 13:49 Ian Varley Ian Varley 9,417 5 5 gold badges 30 30 silver badges 34 34 bronze badges A database schema also includes indexes, views, etc. Commented Nov 18, 2008 at 13:50surely this is not correct. a database is not a collection of tables as such. You can have 30 databases without having one table for instance.
Commented Nov 18, 2008 at 13:57 @Robert - what would be the point of having a database without any tables in it? Commented Nov 18, 2008 at 14:00Even if you don't have any tables, that's still a collection of tables - it's an empty collection of tables.
Commented Nov 18, 2008 at 14:01No point I can think of but the definition of a database doesn't change just because there is/isn't tables there. I have to agree I would expect to find tables in a database :)
Commented Nov 18, 2008 at 14:03In a nutshell, a schema is the definition for the entire database, so it includes tables, views, stored procedures, indexes, primary and foreign keys, etc.
answered Nov 18, 2008 at 13:48 Graeme Perrow Graeme Perrow 57k 24 24 gold badges 84 84 silver badges 125 125 bronze badges simple an elegant answer. Commented Aug 16 at 18:56Schema 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:
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).
answered Feb 15, 2012 at 9:40 406 6 6 silver badges 12 12 bronze badges valuable answer Commented May 7, 2019 at 22:10This 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.