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.

From Here…

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.

Review Exercises

  1. 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?

  2. 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?

  3. 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?

  4. 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.

  1. 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?

  2. 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?

  3. Drop all the items you created in 1_6.

EMPLOYEE DATABASE PROJECT INSTALLMENT 2

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.

  1. Create a tablespace called emp. The size of the tablespace should be approximately 3 million bytes.

  2. Create a user id.

  3. 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
  4. 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).

  5. Create a sequence that will be used to generate payroll numbers. The sequence begins with the number 1.

Previous | Table of Contents | Next