Lab Exercise-4
Part-A
To perform the following assignments, refer to the tables in the JustLee Books database. 1.
Create a sequence for populating the Customer# column of the CUSTOMERS table. When setting the start and increment values, keep in mind that data already exists ex ists in this table. The options should be set to not cycle the values and not cache any values, and no minimum or maximum values should be declared.
2.
Add a new customer row by using the sequence created in Question 1. The only data currently available for the customer is as follows: last name = Shoulders, first name = Frank, and zip = 23567.
3.
Create a sequence that generates integers starting with the value 5. Each value should be three less than the previous value generated. The lowest possible value should be 0, and the sequence shouldn’t be allowed to cycle. Name the sequence MY_FIRST_SEQ.
4.
Issue a SELECT statement that displays NEXTVAL for MY_FIRST_SEQ three times. Because the value isn’t being placed in a table, use the DUAL table in the FROM clause of the SELECT statement. What causes the error on the third SELECT?
5.
Change the setting of MY_FIRST_SEQ so that the minimum value that can be generated is -1000.
6.
Create a private synonym that enables you to reference the MY_FIRST_SEQ object as NUMGEN.
7.
Use a SELECT statement to view the CURRVAL of NUMGEN. Delete the NUMGEN synonym and MY_FIRST_SEQ.
8.
Create a bitmap index on the CUSTOMERS table to speed up queries that search for customers based on their state of residence. Verify that the index exists, and then delete the index.
9.
Create a B-tree index on the customer’s Lastname column. Verify that the index exists by querying the data dictionary. Remove the index from the database.
10.
Many queries search by the number of days to ship (number of days between the order and shipping dates). Create an index that might improve the performance of these queries.
Part-B
1.
Create a new user account. The account name should be a combination of your first initial and your last name.
2.
Attempt to log in to Oracle 11g with the newly created account.
3.
Assign privileges to the new account that allows connecting to the database, creating new tables, and altering an existing table.
4.
Using an account with the required privileges, create a role named CUSTOMERREP that allows inserting new rows in the ORDERS and ORDERITEMS tables and deleting rows from these tables.
5.
Assign the account created in Assignment 1 the CUSTOMERREP role.
6.
Log in to Oracle 11g with the new account created in Assignment 1. Determine the privileges currently available to the account.
7.
Revoke the privilege to delete rows in the ORDERS and ORDERITEMS tables from the CUSTOMERREP role.
8.
Remove the CUSTOMERREP role from the account created in Assignment 1.
9.
Delete the CUSTOMERREP role from the Oracle 11g database.
10.
Delete the user account created in Assignment 1.
Part-C
1.
The head DBA has requested the creation of a sequence for the primary key columns of the Criminals and Crimes tables. After creating the sequences, add a new criminal named Johnny Capps to the Criminals table by using the correct sequence. (Use any values for the remainder of columns.) A crime needs to be added for the criminal, too. Add a row to the Crimes table, referencing the sequence value already generated for the Criminal_ID and using the correct sequence to generate the Crime_ID value. (Use any values for the remainder of columns.)
2.
The last name, street, and phone number columns of the Criminals table are used quite often in the WHERE clause condition of queries. Create objects that might improve data retrieval for these queries.
3.
Would a bitmap index be appropriate for any columns in the City Jail database (assuming the columns are used in search and/or sort ope rations)? If so, identify the columns and explain why a bitmap index is appropriate for them.
4.
Would using the City Jail database be any easier with the creation of synonyms? Explain why or why not.
Part-D
Use the JustLee Books database to perform the following activity: There are three major classifications for employees who don’t work for the Information Systems Department of JustLee Books: account managers, wh o are responsible for the company’s marketing activities (for example, promotions based on customers’ previous purchases or for specific books); data entry clerks, who enter inventory updates (for example, add new books and publishers, change prices, and so on); and customer service representatives, who are responsible for adding new customers and entering orders in the database. Each employee group has different tasks to perform and, therefore, needs different privileges for various tables in the database. To simplify administration of system and object privileges, a role should be created for each employee group. Create a document for your supervisor that contains the following information: •
List the tables that each group of employees needs to access from these tables: BOOKS, CUSTOMERS, ORDERS, ORDERITEMS, AUTHOR, BOOKAUTHOR, PUBLISHER, and PROMOTION.
•
Name the privileges each group of employees needs.
•
For each group of employees, name a role containing the necessary privileges for that group.
•
For all groups of employees, list the exact command s for creating and assigning specific privileges to their roles.
•
Explain your rationale for the privileges granted to each role.
