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.
nice :D thought there was more to it than that
anbanm
2008-11-18 13:48:50
A little cryptic, but not wrong; but do you really think a selfproclaimed n00b would understand that?
Stein G. Strindhaug
2008-11-18 13:49:17
Good 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.
Paul Tomblin
2008-11-18 13:49:50
"Schema" really just means "plan". I've seen it used to refer to the entire database, or just one table or view.
MusiGenesis
2008-11-18 13:50:54
@Paul: good points. I originally wrote "blueprints" but changed it because it doesn't really connote "house" (as least not to me).
MusiGenesis
2008-11-18 13:52:25
@Stein: that's pretty harsh on anbanm - he got it. :)
MusiGenesis
2008-11-18 13:52:58
surely schemas are in the database so the order is wrong? I'm specifically talking about Oracle but a database contains many schemas.
Robert
2008-11-18 13:54:58
@Robert: this might be just semantic, but inside Oracle a "schema" is more synonymous with "user" or "owner".
MusiGenesis
2008-11-18 13:57:59
Yea I tend to view a schema as a user in Oracle. So definitions even vary from db to db :D How annoying.
Robert
2008-11-18 14:05:03
I botch my Oracle-speak all the time, coming from SQL Server.
MusiGenesis
2008-11-18 14:07:34
I agree with Paul Tomblin, I prefer "blueprints"
Alex Baranosky
2009-01-24 19:37:45
@GordonG: have you ever seen a blueprint for a house?
MusiGenesis
2009-01-25 00:10:15
+2
A:
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.
Graeme Perrow
2008-11-18 13:48:45
+10
A:
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.
Ian Varley
2008-11-18 13:49:56
surely 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.
Robert
2008-11-18 13:57:50
@Robert - what would be the point of having a database without any tables in it?
Paul Tomblin
2008-11-18 14:00:22
Even if you don't have any tables, that's still a collection of tables - it's an *empty* collection of tables.
Jon Skeet
2008-11-18 14:01:06
No 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 :)
Robert
2008-11-18 14:03:27
@Ian: don't forget that a database will also have constraints - referential constraints (primary keys, foreign keys), uniqueness constraints, and check constraints. It might also include triggers and procedures.
Jonathan Leffler
2008-11-18 14:08:14
Also, @Robert, I think the "database" you're talking about is the term used by the commercial vendors, with a specific meaning in terms of configuration. The more "formal" definition of a database is just "a collection of data" (which includes DBMS databases, spreadsheets, etc.).
Ian Varley
2008-12-19 22:35:35
+1
A:
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 there 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 obviously 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.
Robert
2008-11-18 13:52:18
+2
A:
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
Sam
2008-11-18 20:46:41
A:
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
prasadj
2010-02-09 06:04:28