File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Schema and Database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Schema and Database" Watch "Schema and Database" New topic
Author

Schema and Database

Phillipe Rodrigues
Ranch Hand

Joined: Oct 30, 2007
Posts: 165
What is the difference between Schema and Database?


Thanks,
Goutham Pallipati
Greenhorn

Joined: Aug 13, 2008
Posts: 20
Hi,

a database can have multiple logical partitions called Schemas and you can manage the size,access,creation and deletion of schemas without effecting the other parts of the Database.

each schemas can be mapped to one or more DB Files and can be associated with a user.

Database should contain at least one schema and one or more schemas contribute to a database.
Phillipe Rodrigues
Ranch Hand

Joined: Oct 30, 2007
Posts: 165
Please clear my doubt with reference to the below example:

I login in to oracle using
useranme:abc
password:abcxyz

Create a database named "test" with 3 tables in it: table 1,table 2,table 3


Now for the above what does schema refer to?
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39396
    
  28
I Googled and found a tutorial, and there are bound to be lots more.
Jason Pirkey
Greenhorn

Joined: Aug 06, 2008
Posts: 9
You didn't reference a schema in the example above.

If you created a database named "test_database". In the "test_database" database you can create schemas "test_database_schema_1", "test_database_schema_2", and "test_database_schema_3". In each of these schemas you can create tables "table_1", "table_2", and "table_3".

Now if user1 logged into the "test_database" database there would be three schemas each containing three tables.

Does this help you?
Phillipe Rodrigues
Ranch Hand

Joined: Oct 30, 2007
Posts: 165
From the above details does it mean that the all 3 schemas tables:
contain different data,update or modification in particular schema's table will not reflect to other schemas.

Is it that use of schemas is to distinguish users for the tables?

Is it that a database has atleast one schema as default?

Do we create schema before or after database?
Rene Larsen
Ranch Hand

Joined: Oct 12, 2001
Posts: 1179

Originally posted by Phillipe Rodrigues:
From the above details does it mean that the all 3 schemas tables:
contain different data,update or modification in particular schema's table will not reflect to other schemas.

Is it that use of schemas is to distinguish users for the tables?

Is it that a database has atleast one schema as default?

Do we create schema before or after database?


If you will have a correct answer, you need to tell us which database you want information about.

Oracle have e.g. databases (SIDs) which then have a number of schema (database users).

MySQL don't use the term schema the same way.


Regards, Rene Larsen
Dropbox Invite
Jason Pirkey
Greenhorn

Joined: Aug 06, 2008
Posts: 9
Phillipe,

In regards to an Oracle database you are correct. Each of the table_1 tables in the different schemas could have the exact same table structure and contain different data. Also an insert, update, or deletion of data in one table will have no effect on the data in a table with the same name found in other schema. There is also nothing that says each of the table_1 tables have to have the same structure in the different schemas. One table could have 5 columns and another 8 columns.

If I am not mistaken every user has a schema, but I don�t think you have to have a user for each schema. Also just because a user has a schema doesn�t mean they can create a table in it.

Yes there are some default schemas and accounts. All schemas are created after the database except those default schemas of course. There are also schemas and accounts like scott you want to delete as soon as possible.
Jason Pirkey
Greenhorn

Joined: Aug 06, 2008
Posts: 9
I asked around and it appears I was mistaken. Rene's correct in that one could characterize a schema as being synonymous with a user. The best way I can explain it from the people I asked about it is a schema is created when a user is created and the objects a user owns reside in their schema.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Schema and Database