Question

The Marketing Department of JustLee Books is preparing for its annual sales promotion. Each customer who places an order during the promotion will receive a free gift with each book purchased. Each gift will be based on the retail price. JustLee Books also participates in a partnered advertising program with certain publishers. If the publisher's name is included in the advertisements, JustLee Books will be reimbursed a percentage of the advertisement costs.

Answer 1

• Create and execute SQL statements to perform the following actions, using the SH database:

  1. Create a new user account. The account name should be a combination of your first initialand your last name.

  2. Attempt to log in to Oracle 10g with the newly created account.

  3. Assign privileges to the new account that allow connecting to the database, creating newtables, and altering an existing table.

  4. Using an account with the required privileges, create a role named CUSTOMERREP thatallows inserting new rows in the ORDERS and TIME tables and deleting rows fromthese tables.

  5. Assign the account created in project 1 the CUSTOMERREP role.

  6. Log in to Oracle 11g with the new account created in Assignment 1. Determine the privilegescurrently available to the account.

  7. Revoke the privilege to delete rows in the ORDERS and ORDERITEMS tables from theCUSTOMERREP role.

  8. Remove the CUSTOMERREP role from the account created in project 1.

  9. Delete the CUSTOMERREP role from the Oracle 11g database.

  10. Delete the user account created in project 1.

  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, who are responsible for the company’smarketing activities (for example, promotions based on customers’ previous purchases or for specificbooks); 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 responsiblefor adding new customers and entering orders in the database. Each employee group has differenttasks to perform and, therefore, needs different privileges for various tables in the database. Tosimplify administration of system and object privileges, a role should be created for each employeegroup.

  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 forthat group.

  • For all groups of employees, list the exact commands for creating and assigning specificprivileges to their roles.

  • Explain your rationale for the privileges granted to each role.