• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Schema and Database

 
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What is the difference between Schema and Database?
 
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 165
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Marshal
Posts: 80869
505
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I Googled and found a tutorial, and there are bound to be lots more.
 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 165
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 1179
Mac OS X Eclipse IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Jason Pirkey
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic