CREATING A SCHEMA

In this section, you create a schema named ONLINE_SHOPPE. This schema portrays an online store that operates with a customer base and commodities. Information about customers is stored in the CUSTOMERS table, information about commodities is stored in the COMMODITIES table and order details are stored in the ORDERS table.


 

Creating a User

Database administrators perform many tasks. One of their more common tasks is creating database users and assigning them unique usernames. After users log in to the database with their username and password, they can issue database SQL statements to create objects, query objects, and manage the database.
Creating a user is a way to create a schema. In this section, you execute the CREATE USER statement to create and configure a database user.
Syntax: CREATE USER <USER> IDENTIFIED BY <password>;


Execute the following statements to connect to the database as an administrator and create a user named ONLINE_SHOPPE.
CONNECT sys/<password> as sysdba;
CREATE USER online_shoppe IDENTIFIED BY online;


A schema named ONLINE_SHOPPE was created in Oracle Database.

 

Assigning Privileges

When multiple users access database objects, you can control the authorization of the objects with privileges. Privileges control whether a user can modify an object that is owned by another user. They are granted or revoked either by:

• The instance administrator
• A user with ADMIN privileges
• The object's owner   
In general, there are two types of privileges:

• System privilege: The right to perform a particular action on any object, such as, tables, views and indexes. Only the instance administrator or a user with the ADMIN privilege can assign or revoke system privileges.
• Object privilege: The right to perform a particular action on an object or to access another user's object. An object's owner has all object privileges for that object and can assign object privileges for that object to other database users.
Here are a few of the basic system and object privileges:
System privileges:

• Create a table, a view, or an index that is owned by any user in the database
• Alter a table, a view, or an index in the database
• Drop a table, a view, or an index in the database

Object privileges:

• Insert values into a table
• Create a foreign key dependency for a table
• Select from a table
• Update a table

You use the GRANT statement to assign privileges to users and roles. To assign privileges, you must have been assigned either the ADMIN OPTION or the GRANT ANY PRIVILEGE system privilege.

Syntax: GRANT <grant_privilege> TO <user>;


When you create a user with the CREATE USER statement, the user's privilege domain is empty by default. The administrator assigns privileges to the user based on the tasks that the user may perform in the future. In this tutorial, the ONLINE_SHOPPE user establishes a session, creates a table, and writes DML statements against tables. Execute the following statements to assign the required privileges to the ONLINE_SHOPPEuser:
GRANT CREATE SESSION to online_shoppe;
GRANT CREATE TABLE to online_shoppe;
GRANT UNLIMITED TABLESPACE to online_shoppe;
GRANT SELECT ANY TABLE to online_shoppe;
GRANT UPDATE ANY TABLE to online_shoppe;
GRANT INSERT ANY TABLE to online_shoppe;
GRANT DROP ANY TABLE to online_shoppe;




 

Creating Tables

Before creating tables in the ONLINE_SHOPPE schema, you should understand the concepts of  tables and integrity constraints. 

• Table: Basic unit of data storage in a database. Within a table, data is stored in rows and columns. You define a table with a table name, a set of columns, a data type, and a width.  

• Integrity constraints: Rules for columns in a table. You specify these rules to enforce data integrity within the columns for which they are defined. Basic constraints on Oracle Database include the following:




In this section, you execute the CREATE TABLE statement to create tables.
Syntax: CREATE TABLE [schema.]table
          (column datatype [DEFAULT expr][, ...]);
Perform the following steps to create the CUSTOMERS, COMMODITIES, and ORDERS tables in the schema.


Connect to the ONLINE_SHOPPE schema and create the CUSTOMERS table with the CUSTOMER_ID column as the primary key.
CONNECT online_shoppe/online;
CREATE TABLE customers(
   customer_id    VARCHAR2(4),
   customer_name  VARCHAR2(20),
   address        VARCHAR2(60),
   contact        VARCHAR2(20),
   CONSTRAINT cust_id_pk PRIMARY KEY(customer_id) );



Create the COMMODITIES table with the COMMODITY_ID column as the primary key and the UNIT_PRICE column as a non-null column.
CREATE TABLE commodities(
   commodity_id     VARCHAR2(4),
   commodity_name   VARCHAR2(20),
   unit_price       NUMBER(8,2) NOT NULL,
   CONSTRAINT comm_id_pk PRIMARY KEY(commodity_id) );



Create the ORDERS table with:

• ORDER_ID column as the primary key
• COMMODITY_ID and CUSTOMER_ID as foreign keys
• UNITS and TOTAL_COST as NOT NULL values
• CHECK constraint on numeric columns to accept values greater than zero

CREATE TABLE orders(
   order_id       VARCHAR2(4),
   customer_id    VARCHAR2(4),
   commodity_id   VARCHAR2(4),
   units          NUMBER(8,2) NOT NULL,
   total_cost     NUMBER(8,2) NOT NULL,
   CONSTRAINT ordr_id_pk PRIMARY KEY(order_id),
   CONSTRAINT ordr_cust_fk FOREIGN KEY (customer_id)REFERENCES customers(customer_id),
   CONSTRAINT ordr_comm_fk FOREIGN KEY (commodity_id)REFERENCES commodities(commodity_id),
   CONSTRAINT check_unit CHECK(units > 0),
   CONSTRAINT check_totl CHECK(total_cost > 0) );



 

Inserting, Modifying, and Deleting Records

In this section, you manipulate the records in the tables that you created.


Inserting data: You execute the INSERT statement to add rows of data to a database table.
Syntax: INSERT INTO table [(column [, column...])]
         VALUES (value [, value...]);
Execute the following statements to insert data into the CUSTOMERS, COMMODITIES, and ORDERS tables.
INSERT INTO customers VALUES ('C001', 'BDAVIS', 'Boston', '650.551.4876');
INSERT INTO customers VALUES ('C002', 'SSTEPHEN', 'ST.Louis', '650.501.9321');
INSERT INTO customers VALUES ('C003', 'DCARTER', 'California', '650.507.6632');

INSERT INTO commodities VALUES ('M001', 'DVD Player', 109);
INSERT INTO commodities VALUES ('M002', 'Cereal', 03);
INSERT INTO commodities VALUES ('M003', 'Scrabble', 29);

INSERT INTO orders VALUES ('R001', 'C003', 'M002', 50, 150);
INSERT INTO orders VALUES ('R002', 'C001', 'M003', 30, 87);
INSERT INTO orders VALUES ('R003', 'C003', 'M001', 6, 654);




Modifying data: You use the UPDATE statement to modify rows of data in a database table. Execute the following statement to change the unit price of the DVD player from $109 to $129:
UPDATE commodities SET unit_price = 129 WHERE commodity_name = 'DVD Player';



Deleting data: You use the DELETE statement to delete rows of data from a database table. Execute the following statement to delete the first record in the ORDERS table:
DELETE FROM orders WHERE order_id = 'R001';



 

Undoing and Saving Records

In this section, you use the COMMIT and ROLLBACK statements to change data permanently. You use the ROLLBACK statement to undo the work that was performed in your current transaction and you use the COMMIT statement to save the work that was performed in your current transaction.


Execute the COMMIT statement to save the data manipulation transactions that you performed in the previous section.
COMMIT;



Execute the following statements to delete the row whose order ID is R002 and to query the ORDERS table to ensure that the record was deleted.
DELETE FROM orders WHERE order_id = 'R002';
SELECT * FROM orders;



The output shows that the record was deleted successfully. 

Execute the following statements to undo deletion of the row whose order ID is R002 and to query the table to display the records:.
ROLLBACK;
SELECT * FROM orders;



The previous DELETE statement was rolled back.
Note: You cannot undo transactions after you save them permanently with the COMMIT statement.

 

Removing Tables

In this section, you execute the DROP TABLE statement to remove a table and its data from the database.
Syntax: DROP TABLE <table>;


Execute the DROP TABLE statement to remove the CUSTOMERS table.
DROP TABLE customers;



An error message is displayed because of the referential integrity constraint on the CUSTOMER_ID column.

Include the CASCADE CONSTRAINTS clause to remove the table and its referential integrity constraints..
DROP TABLE customers CASCADE CONSTRAINTS;



 

Revoking Privileges

In this section, you execute the REVOKE statement to revoke user and role system privileges. To revoke a system privilege or a role, you must be assigned the privilege with the ADMIN OPTION.

Syntax: REVOKE <revoke_privilege> FROM <user>;


Connect to the database as the SYS user and revoke the CREATE SESSION privilege for ONLINE_SHOPPE.
CONNECT sys/<password> as sysdba;
REVOKE CREATE SESSION FROM online_shoppe;



Connect to the database as the ONLINE_SHOPPE.

CONNECT online_shoppe/online;



You cannot connect because you do not have the CREATE SESSION privilege.