Discussion:
create schema
(too old to reply)
Kim Thai
2006-08-01 17:24:45 UTC
Permalink
Hi,

Can someone help to define the term "schema" in sybase? Does it have the
same mean as in Oracle? To me "schema" simply means the database DDL
script for creating a specific database structure. I am working with
someone who has Oracle back ground and keep persuade me to create a db using
"create schema" command in ASE. I am not sure what is the benefit of using
"create schema". I cannot find much document about this. I appreciate any
of your replies.
unknown
2006-08-01 17:39:51 UTC
Permalink
from reference manual

create schema
Description
Creates a new collection of tables, views, and permissions for a database
user.
Syntax
create schema authorization authorization_name
create_oject_statement
[ create_object_statement ... ]
[ permission_statement ... ]
Parameters
authorization_name
must be the name of the current user in the database.
create_object_statement
is a create table or create view statement.
permission_statement
is a grant or revoke command.
Examples
Example 1
Creates the newtitles, newauthors, newtitleauthors tables, the tit_auth_view
view, and the corresponding permissions:
create schema authorization pogo
create table newtitles (
title_id tid not null,
title varchar(30) not null)
create table newauthors (
au_id id not null,
au_lname varchar(40) not null,
au_fname varchar(20) not null)
create table newtitleauthors (
au_id id not null,
title_id tid not null)
create view tit_auth_view
as
select au_lname, au_fname
from newtitles, newauthors,
newtitleauthors
where
newtitleauthors.au_id = newauthors.au_id
and
newtitleauthors.title_id =
newtitles.title_id
grant select on tit_auth_view to public
revoke select on tit_auth_view from churchy
Usage
·Schemas can be created only in the current database.
·The authorization_name, also called the schema authorization identifier,
must be the name of the current user.
·The user must have the correct command permissions (create table and/or
create view). If the user creates a view on tables owned by another database
user, permissions on the view are checked when a user attempts to access
data through the view, not when the view is created.
·The create schema command is terminated by:
·The regular command terminator ("go" is the default in isql).
·Any statement other than create table, create view, grant, or revoke.
·If any of the statements within a create schema statement fail, the entire
command is rolled back as a unit, and none of the commands take effect.
·create schema adds information about tables, views, and permissions to the
system tables. Use the appropriate drop command (drop table or drop view) to
drop objects created with create schema. Permissions granted or revoked in a
schema can be changed with the standard grant and revoke commands outside
the schema creation statement.
Standards
Standard
Compliance Level
SQL92
Entry level compliant
Permissions
create schema can be executed by any user of a database. The user must have
permission to create the objects specified in the schema; that is, create
table and/or create view permission.
Post by Kim Thai
Hi,
Can someone help to define the term "schema" in sybase? Does it have the
same mean as in Oracle? To me "schema" simply means the database DDL
script for creating a specific database structure. I am working with
someone who has Oracle back ground and keep persuade me to create a db
using "create schema" command in ASE. I am not sure what is the benefit
of using "create schema". I cannot find much document about this. I
appreciate any of your replies.
K.T.
2006-08-01 17:59:54 UTC
Permalink
thank you. So what is the benefit for using it.
Post by unknown
from reference manual
create schema
Description
Creates a new collection of tables, views, and permissions for a database
user.
Syntax
create schema authorization authorization_name
create_oject_statement
[ create_object_statement ... ]
[ permission_statement ... ]
Parameters
authorization_name
must be the name of the current user in the database.
create_object_statement
is a create table or create view statement.
permission_statement
is a grant or revoke command.
Examples
Example 1
Creates the newtitles, newauthors, newtitleauthors tables, the
create schema authorization pogo
create table newtitles (
title_id tid not null,
title varchar(30) not null)
create table newauthors (
au_id id not null,
au_lname varchar(40) not null,
au_fname varchar(20) not null)
create table newtitleauthors (
au_id id not null,
title_id tid not null)
create view tit_auth_view
as
select au_lname, au_fname
from newtitles, newauthors,
newtitleauthors
where
newtitleauthors.au_id = newauthors.au_id
and
newtitleauthors.title_id =
newtitles.title_id
grant select on tit_auth_view to public
revoke select on tit_auth_view from churchy
Usage
·Schemas can be created only in the current database.
·The authorization_name, also called the schema authorization identifier,
must be the name of the current user.
·The user must have the correct command permissions (create table and/or
create view). If the user creates a view on tables owned by another
database user, permissions on the view are checked when a user attempts to
access data through the view, not when the view is created.
·The regular command terminator ("go" is the default in isql).
·Any statement other than create table, create view, grant, or revoke.
·If any of the statements within a create schema statement fail, the
entire command is rolled back as a unit, and none of the commands take
effect.
·create schema adds information about tables, views, and permissions to
the system tables. Use the appropriate drop command (drop table or drop
view) to drop objects created with create schema. Permissions granted or
revoked in a schema can be changed with the standard grant and revoke
commands outside the schema creation statement.
Standards
Standard
Compliance Level
SQL92
Entry level compliant
Permissions
create schema can be executed by any user of a database. The user must
have permission to create the objects specified in the schema; that is,
create table and/or create view permission.
Post by Kim Thai
Hi,
Can someone help to define the term "schema" in sybase? Does it have the
same mean as in Oracle? To me "schema" simply means the database DDL
script for creating a specific database structure. I am working with
someone who has Oracle back ground and keep persuade me to create a db
using "create schema" command in ASE. I am not sure what is the benefit
of using "create schema". I cannot find much document about this. I
appreciate any of your replies.
Mark A. Parsons
2006-08-02 02:21:46 UTC
Permalink
'create schema' is basically a way of incorporating a (limited) set of DDL
commands into a single unit of work ... similar to the concept of wrapping
a bunch of DML commands in a transaction as a single unit of work.

'single unit of work' => it's an all-or-nothing situation ... either all
objects get created (ie, all DDL commands succeed) ... or they all fail.

Although I've never had a need to use 'create schema' I can see where it
may be of benefit to some folks to know that a set of related
tables/views/permissions were successfully created as a group, eg, if a
view definition fails you may not want the associated tables left out in
the database without that view present.

It may also be of benefit for folks writing a stand-alone installation
program ... deal with one unit of work and one error message ... as opposed
to having to figure out which object(s) failed and then recreating said
objects piece meal.

Personally I think it has limited benefits ... why not include 'create
index', 'create proxy', 'create trigger' and 'create proc'?
Post by K.T.
thank you. So what is the benefit for using it.
Post by unknown
from reference manual
create schema
Description
Creates a new collection of tables, views, and permissions for a database
user.
Syntax
create schema authorization authorization_name
create_oject_statement
[ create_object_statement ... ]
[ permission_statement ... ]
Parameters
authorization_name
must be the name of the current user in the database.
create_object_statement
is a create table or create view statement.
permission_statement
is a grant or revoke command.
Examples
Example 1
Creates the newtitles, newauthors, newtitleauthors tables, the
create schema authorization pogo
create table newtitles (
title_id tid not null,
title varchar(30) not null)
create table newauthors (
au_id id not null,
au_lname varchar(40) not null,
au_fname varchar(20) not null)
create table newtitleauthors (
au_id id not null,
title_id tid not null)
create view tit_auth_view
as
select au_lname, au_fname
from newtitles, newauthors,
newtitleauthors
where
newtitleauthors.au_id = newauthors.au_id
and
newtitleauthors.title_id =
newtitles.title_id
grant select on tit_auth_view to public
revoke select on tit_auth_view from churchy
Usage
·Schemas can be created only in the current database.
·The authorization_name, also called the schema authorization identifier,
must be the name of the current user.
·The user must have the correct command permissions (create table and/or
create view). If the user creates a view on tables owned by another
database user, permissions on the view are checked when a user attempts to
access data through the view, not when the view is created.
·The regular command terminator ("go" is the default in isql).
·Any statement other than create table, create view, grant, or revoke.
·If any of the statements within a create schema statement fail, the
entire command is rolled back as a unit, and none of the commands take
effect.
·create schema adds information about tables, views, and permissions to
the system tables. Use the appropriate drop command (drop table or drop
view) to drop objects created with create schema. Permissions granted or
revoked in a schema can be changed with the standard grant and revoke
commands outside the schema creation statement.
Standards
Standard
Compliance Level
SQL92
Entry level compliant
Permissions
create schema can be executed by any user of a database. The user must
have permission to create the objects specified in the schema; that is,
create table and/or create view permission.
Post by Kim Thai
Hi,
Can someone help to define the term "schema" in sybase? Does it have the
same mean as in Oracle? To me "schema" simply means the database DDL
script for creating a specific database structure. I am working with
someone who has Oracle back ground and keep persuade me to create a db
using "create schema" command in ASE. I am not sure what is the benefit
of using "create schema". I cannot find much document about this. I
appreciate any of your replies.
Mark A. Parsons
2006-08-02 02:26:14 UTC
Permalink
ps - I've been working with Sybase for ~14 years and have never used
'create schema' ... point being that you *CAN* live a long and prosperous
(?) life as a Sybase DBA without having to use the 'create schema' command.
Post by Mark A. Parsons
'create schema' is basically a way of incorporating a (limited) set of
DDL commands into a single unit of work ... similar to the concept of
wrapping a bunch of DML commands in a transaction as a single unit of work.
'single unit of work' => it's an all-or-nothing situation ... either all
objects get created (ie, all DDL commands succeed) ... or they all fail.
Although I've never had a need to use 'create schema' I can see where it
may be of benefit to some folks to know that a set of related
tables/views/permissions were successfully created as a group, eg, if a
view definition fails you may not want the associated tables left out in
the database without that view present.
It may also be of benefit for folks writing a stand-alone installation
program ... deal with one unit of work and one error message ... as
opposed to having to figure out which object(s) failed and then
recreating said objects piece meal.
Personally I think it has limited benefits ... why not include 'create
index', 'create proxy', 'create trigger' and 'create proc'?
Post by K.T.
thank you. So what is the benefit for using it.
Post by unknown
from reference manual
create schema
Description
Creates a new collection of tables, views, and permissions for a
database user.
Syntax
create schema authorization authorization_name
create_oject_statement
[ create_object_statement ... ]
[ permission_statement ... ]
Parameters
authorization_name
must be the name of the current user in the database.
create_object_statement
is a create table or create view statement.
permission_statement
is a grant or revoke command.
Examples
Example 1
Creates the newtitles, newauthors, newtitleauthors tables, the
create schema authorization pogo
create table newtitles (
title_id tid not null,
title varchar(30) not null)
create table newauthors (
au_id id not null,
au_lname varchar(40) not null,
au_fname varchar(20) not null)
create table newtitleauthors (
au_id id not null,
title_id tid not null)
create view tit_auth_view
as
select au_lname, au_fname
from newtitles, newauthors,
newtitleauthors
where
newtitleauthors.au_id = newauthors.au_id
and
newtitleauthors.title_id =
newtitles.title_id
grant select on tit_auth_view to public
revoke select on tit_auth_view from churchy
Usage
·Schemas can be created only in the current database.
·The authorization_name, also called the schema authorization
identifier, must be the name of the current user.
·The user must have the correct command permissions (create table
and/or create view). If the user creates a view on tables owned by
another database user, permissions on the view are checked when a
user attempts to access data through the view, not when the view is
created.
·The regular command terminator ("go" is the default in isql).
·Any statement other than create table, create view, grant, or revoke.
·If any of the statements within a create schema statement fail, the
entire command is rolled back as a unit, and none of the commands
take effect.
·create schema adds information about tables, views, and permissions
to the system tables. Use the appropriate drop command (drop table or
drop view) to drop objects created with create schema. Permissions
granted or revoked in a schema can be changed with the standard grant
and revoke commands outside the schema creation statement.
Standards
Standard
Compliance Level
SQL92
Entry level compliant
Permissions
create schema can be executed by any user of a database. The user
must have permission to create the objects specified in the schema;
that is, create table and/or create view permission.
Post by Kim Thai
Hi,
Can someone help to define the term "schema" in sybase? Does it
have the same mean as in Oracle? To me "schema" simply means the
database DDL script for creating a specific database structure. I
am working with someone who has Oracle back ground and keep persuade
me to create a db using "create schema" command in ASE. I am not
sure what is the benefit of using "create schema". I cannot find
much document about this. I appreciate any of your replies.
unknown
2006-08-02 03:06:56 UTC
Permalink
even I have never used it :)
Post by Mark A. Parsons
ps - I've been working with Sybase for ~14 years and have never used
'create schema' ... point being that you *CAN* live a long and prosperous
(?) life as a Sybase DBA without having to use the 'create schema' command.
Post by Mark A. Parsons
'create schema' is basically a way of incorporating a (limited) set of
DDL commands into a single unit of work ... similar to the concept of
wrapping a bunch of DML commands in a transaction as a single unit of work.
'single unit of work' => it's an all-or-nothing situation ... either all
objects get created (ie, all DDL commands succeed) ... or they all fail.
Although I've never had a need to use 'create schema' I can see where it
may be of benefit to some folks to know that a set of related
tables/views/permissions were successfully created as a group, eg, if a
view definition fails you may not want the associated tables left out in
the database without that view present.
It may also be of benefit for folks writing a stand-alone installation
program ... deal with one unit of work and one error message ... as
opposed to having to figure out which object(s) failed and then
recreating said objects piece meal.
Personally I think it has limited benefits ... why not include 'create
index', 'create proxy', 'create trigger' and 'create proc'?
Post by K.T.
thank you. So what is the benefit for using it.
Post by unknown
from reference manual
create schema
Description
Creates a new collection of tables, views, and permissions for a
database user.
Syntax
create schema authorization authorization_name
create_oject_statement
[ create_object_statement ... ]
[ permission_statement ... ]
Parameters
authorization_name
must be the name of the current user in the database.
create_object_statement
is a create table or create view statement.
permission_statement
is a grant or revoke command.
Examples
Example 1
Creates the newtitles, newauthors, newtitleauthors tables, the
create schema authorization pogo
create table newtitles (
title_id tid not null,
title varchar(30) not null)
create table newauthors (
au_id id not null,
au_lname varchar(40) not null,
au_fname varchar(20) not null)
create table newtitleauthors (
au_id id not null,
title_id tid not null)
create view tit_auth_view
as
select au_lname, au_fname
from newtitles, newauthors,
newtitleauthors
where
newtitleauthors.au_id = newauthors.au_id
and
newtitleauthors.title_id =
newtitles.title_id
grant select on tit_auth_view to public
revoke select on tit_auth_view from churchy
Usage
·Schemas can be created only in the current database.
·The authorization_name, also called the schema authorization
identifier, must be the name of the current user.
·The user must have the correct command permissions (create table
and/or create view). If the user creates a view on tables owned by
another database user, permissions on the view are checked when a user
attempts to access data through the view, not when the view is created.
·The regular command terminator ("go" is the default in isql).
·Any statement other than create table, create view, grant, or revoke.
·If any of the statements within a create schema statement fail, the
entire command is rolled back as a unit, and none of the commands take
effect.
·create schema adds information about tables, views, and permissions to
the system tables. Use the appropriate drop command (drop table or drop
view) to drop objects created with create schema. Permissions granted
or revoked in a schema can be changed with the standard grant and
revoke commands outside the schema creation statement.
Standards
Standard
Compliance Level
SQL92
Entry level compliant
Permissions
create schema can be executed by any user of a database. The user must
have permission to create the objects specified in the schema; that is,
create table and/or create view permission.
Post by Kim Thai
Hi,
Can someone help to define the term "schema" in sybase? Does it have
the same mean as in Oracle? To me "schema" simply means the database
DDL script for creating a specific database structure. I am working
with someone who has Oracle back ground and keep persuade me to create
a db using "create schema" command in ASE. I am not sure what is the
benefit of using "create schema". I cannot find much document about
this. I appreciate any of your replies.
A.M.
2006-08-02 02:05:45 UTC
Permalink
Post by Kim Thai
Can someone help to define the term "schema" in sybase? Does it have the
same mean as in Oracle? To me "schema" simply means the database DDL
script for creating a specific database structure. I am working with
someone who has Oracle back ground and keep persuade me to create a db using
"create schema" command in ASE. I am not sure what is the benefit of using
"create schema". I cannot find much document about this. I appreciate any
of your replies.
Ignore the Orable twit. The nearest equivalent would be create database.
Orable does not have the concept of multiple databases so Orable
wonks usually use create schema.

-am © MMVI
Loading...