Previous | Table of Contents | Next
Page
149
access your database, you must grant it the select privilege to the table. You can grant a
large number of privileges to the user. In fact, there are no default privileges. To perform any
function, including logging on the database, privileges must be granted.
Some other objects in the database are tablespaces. These objects contain the tables and
other database objects. A sequence is an object that generates unique sequential numbers. A
database trigger is a block of PL/SQL code executed when specific database events occur.
You have now seen how to create the various database objects. The second installment of
the employee project appears at the end of this chapter. You can now create the tables and
the database objects for the project. Future installments will use these tables.
The next chapter covers data manipulation language (dml) commands. These
commands consist of the insert, update, and delete commands. These commands add, modify, or
remove records from the database. You also use these commands in Oracle Forms to maintain
the database.
- Your company needs to begin recording information about its existing
transformers. Your boss has asked you to create an Oracle table to retain this information.
The information you are to keep in the file consists of the following:
serial number alphanumeric length of 10
location alphanumeric length of 30
purchase date alphanumeric length of 8
cost numeric length of 8
manufacturer alphanumeric length of 15
What DDL do you use to create the table?
- After you have created the table, your boss realized that the serial number field
is actually 15 characters long and the table needs to keep track of the gallons of oil. This
is a numeric field with a length of 4. In addition, the serial number item has been
identified as the primary key to the table. The primary key constraints need to be placed on
the table. To make these changes, what ddl do you need?
- After subsequent discussions with the users, you determine that the table will be
very large, and the users will identify transformers based on their addresses. You decide
to index this column to increase the performance of their searches. To create the
index, what ddl do you use?
- Your boss has decided to keep track of each of the transformer's tests. Because
each transformer can be tested multiple times, this tracking requires another table. The
table should have the following fields:
Page
150
serial number alphanumeric length of 15
test date alphanumeric length of 8
voltage number length of 3
amps number length of 3
overhauled alphanumeric length of 1
The value in the overhauled item is either y or n. Create the ddl for this table. Be
certain to include a primary key, foreign key, and check constraint. The on delete cascade
option should also be included.
- A user called you and asked whether you could change the name of the
transformer table to "trf." You decide to create a synonym for this table. What is the DDL for
the synonym?
- A user called and said he runs a report from the employee database listing
each employee's eyeglass cost as a percentage of the department's. It is a rather
complex query, so you decide to create several views to accomplish this report. What are
the statements to create the views and select the data?
- Drop all the items you created in 1_6.
Now that you have learned to define database objects, it is time to create them. At the end
of Part II, you normalized the tables and created a table relationship diagram. These are
the tables that you need to create. You need the following steps to create the database. Appendix
C, Part II, lists the DDL you need to perform these tasks.
- Create a tablespace called emp. The size of the tablespace should be approximately
3 million bytes.
- Create a user id.
- Grant the user id the following privileges:
create session, alter session, create table, alter any table, drop any table,
create view, drop any view, create synonym, drop any synonym, create any index,
alter any index, drop any index, create public synonym, drop public synonym
- Create the tables identified in the table relationship diagrams developed in Part II.
Be certain that each table has a primary key constraint, foreign key constraint
(where applicable), and check constraints (where applicable).
- Create a sequence that will be used to generate payroll numbers. The sequence
begins with the number 1.
Previous | Table of Contents | Next
|