Read an Excerpt
From Chapter 5: Database Objects, Access, and Security
...There are several objects that can exist in a database. The various objects that are owned by an end user exist in what is called the end user's schema. Each user that is defined in the database has their own schema. For example, the Oracle database user with the userid of JONES has a corresponding schema called JONES. There are several different types of schema objects. The different types of schema objects include:- Table. A database object used to store data in row column format. Each row is also referred to as a record.
- View. A database object that shows a customized presentation of a table (or group of tables).
- Synonym. A database object that is an alias for another database object such as a table or view.
- Index. A database object used to speed across to table data.
The various types of database objects can be created using SQL*PLUS. The following examples will show how database objects are created.
Creating a Table
Here's an example of creating a table:
SQL> create table emp
(empno number (4),
ename varchar2 (1 0),
job varchar2 (9),
mgr_no number(4),
hire_date date,
sal number (7, 2),
comm number (7, 2),
deptno number (2));
In this example, we have created a table called "emp." The table will contain information about the employees that work for the company. The information will be arranged into records. Each record will contain information about a employee. The records that will make up the table have eight columns.
Tocomputer the size of a record, we would use the following technique:
- Number fields are computed using the formula: number of bytes = precession/2 + 1. In our example, we would computer the number of bytes used by the fields empno, mgr_number, sal, comm, and deptno. This would result in the following computation: number of bytes = (4/2+1) + (4/2+1) + (7/2+1) + (7/2+1) + (2/2+1) = 17.
- One character requires one byte. Therefore, the fields denoted by varchar2 will result in the following computation: number of bytes = 10+9=19.
- Date fields require 7 bytes.
We must keep in mind that this is the maximum size of the record. In reality the space held by the record may be less. This is due to the following:
- The datatype varchar2 stands for variable length character. This means that if the number of bytes for the column data being inserted is less then the defined column length then the remaining bytes are not used. For example, if the persons last name is "Jones" then only 5 bytes are used to store the 5 characters.
- For number fields the same is true. The internal representation for the number may require less then the maximum number of bytes.
A view is a tailored presentation of data stored in one or more tables. A view can be used to hide various details of the underlining table from the person(s) that are accessing the tables data.
Here is an example of creating a view:
SQL> create view empvu
as select empno, ename, job
from emp;
In this example, we have created a view called "empvu" from the table "emp." The view "empvu" consists of three columns—empno, ename, and job. The end user that needs access to employee names and their jobs would access the view rather then the underlying table...