Oracle

DBM 405 Lab 4 PL/SQL Packages Advanced Database Oracle

Step 1: Creating the Package Specifications
Before you begin, there are several things you will want to do to get ready for the lab.
1.  Refresh your database tables by running the movierental.sql script. This will restore all of the data back to where it was in the beginning for the course.
2.  Drop the two procedures and the function created in Lab 3. Be sure that you have your script file from Lab 3 so you can copy the procedure and function code from it.

Now you are ready to create your package specification. Your package name should be MM_RENTALS_PKG and it will contain the two procedures and the one function that were created in Lab 3.
Remember that for the specification, you only need to list the procedure and function header data (CREATE statement with parameters).

Test your package specification by running the script in SQL*Plus. If you have any errors, debug them and once you have a clean compile, move on to Step 2.

Step 2: Creating the Package Body
Creating the package body should be simple since you already have the code for the two procedures and the function, and you know it works.
Remember that the name for the package body must match the name of the specifications, and that the procedure and function header in the body must match that of the specification exactly.
Once you have created the body then run the script in your SQL*Plus session. Once you have a clean compile then move on to Step 3 to do your testing.

Step 3: Testing the Package
To test your package, you will need to run the same exact tests you did in Lab 3. The following outlines what you will test for:
Testing the first procedure -
1.  No movie for the ID supplied (use 13, 10, and 2 for the parameters).
2.  No member for the ID supplied (use 10, 20, and 2 for the parameters).
3.  No payment method for the ID supplied (use 10, 10, and 7 for the parameters).
4.  A successful rental (use 5, 10, and 2 for the parameters).
5.  No movie available for the ID supplied (use 5, 11, and 2 for the parameters). Since there is only one movie available for ID 5, you will get this exception.
Testing the second procedure -
1.  No rental for the ID supplied (use 20 for the parameter).
2.  A successful rental return (use 1 for the parameter).
3.  Try to return the same rental in Step 2.
Testing the function -
1.  Test for a movie in stock using movie ID 11.
2.  Test for a movie not in stock using movie ID 5 (from your tests of the second procedure above, the quantity should be 0).
3.  Test for an invalid movie ID using movie ID 20.
IMPORTANT: Remember that all of your testing needs to be saved in a spool session so that it can be submitted to the Dropbox for grading.

Step 4: Determining Dependencies
Having created a package that contains program units to support the movie rental process is a major step in customizing the new database.
As application modifications are made in the future, however, we need to be able to identify all object dependencies to test changes.
For this step in the lab, you are to use either data dictionary views or the dependency tree utility found in Doc Sharing (utldtree.sql file) to compile a list of dependencies for all the More Movies database objects.
Remember that an object is anything that was created using the CREATE statement.
Present your finding in a separate Word document in a tabular format as in the following sample.
Each dependency type should be listed as either direct or indirect.

CMIS 320 Project 2 Mom and Pop Johnson Video Store Oracle database
 
In this project you will perform the physical design and implementation using SQL Data Definition Language (DDL) and proceed with populating the Mom and Pop Johnson Video Store database via Data Manipulation Language (DML) SQL commands. The ERD and detailed documentation from Project 1 will be supplied for your entry point for those who need it.
 
Project 2 Details:
1. Create Oracle database tables using SQL Data Definition Language (DDL) for each table listed in Project 1. Make sure that entity and referential integrity are enforced by declaring a primary key for each table (these may be composite keys) and declaring all appropriate foreign keys. Your CREATE TABLE statements must show integrity constraints, as appropriate, for NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, REFERENCES, and CHECK constraints. Be sure to save your script used to create these tables as yournameproject2step1.sql. You should test your script to make sure it runs without error.
2. Provide two examples of SQL DML (i.e., "INSERT") commands that fail different table integrity constraints you set up in one of your table. Explain why the statements fail. Be sure to save your script used to as yournameproject2step2.sql. You can include comments in the SQL script describing why the insert statements failed.
3. Populate each of your tables with at least five valid rows of data each and show the SQL you used. Populate other tables in your database, as necessary, to satisfy referential integrity. Be sure to save your script used to create these records as yournameproject2step3.sql. You should test your script to make sure it runs without error.
4. Write SQL to perform the following queries and updates. Be sure to save your script used to create these records as yournameproject2step4.sql. You should test your script to make sure it runs without error:
a. Retrieve all of your customers' names, account numbers, and addresses (street and zip code only), sorted by account number.
b. Retrieve all of the videos rented in the last 30 days and sort in chronological rental date order.
c. Produce a list of your distributors and all their information sorted in order by company name.
d. Update customer names to change their maiden names to married names. You can choose which records to update.
e. Delete customers from the database. You can choose which records to delete.
 
Submissions:
1. You should submit your four SQL scripts satisfying each of the steps above. Feel free to compress the files using WinZip and save as YournameProject2.zip.
2. Submit in your portfolio in the Project 2 area before the due date. Please contact the instructor before the due date if you have any questions or concerns.

DBM 405 Lab 3 Procedures and Functions Advanced Database Oracle

Step 1: Creating the First Procedure
Your first procedure is to be named MOVIE_RENTAL_SP and is going to provide functionality to process movie rentals. Based on data that will represent the movie ID, member ID, and payment method your procedure will need to generate a rental ID and then insert a new row of data into the mm_rental table. The process will also need to update the quantity column in the mm_movie table to reflect that there is one less copy of the rented movie in stock. Along with the processing, you will also need to define some user-defined exception handlers that will be used in validating the input data. Since you may need to recreate your procedure several times during the debugging process, it is suggested that you use the CREATE OR REPLACE syntax at the beginning of the CREATE statement.

The following steps will help you in setting up your code.
1. You will need to define three parameters, one each for movie ID, member ID, and payment method. Make sure that each one matches the data type of the associated column in the database tables.
2. You will have several other variables that will need to be identified and defined. It might be easier to read through the rest of the specs before you start trying to define these (look for hints in the specifications).
3. You will need to define four user-defined exceptions; one for unknown movies, one for unknown member, one for unknown payment method, and one for if a movie is unavailable.
4. You will need to validate each of the three pieces of data passed to the procedure. One easy way to do this might be to use a SELECT statement with the COUNT function to return a value into a variable based on a match in the database table against the piece of data that you are validating. If the query returns a zero then there is no match and the data is invalid; any value greater than zero means a match was found and thus the data is valid. You will need the following validations.
    1. Validate the movie ID to make sure it is valid. If not then raise the unknown movie exception.
    2. Validate the member ID to make sure one exists for that ID. If not then raise the unknown member exception.
    3. Validate the payment method to make sure it exists. If not then raise the unknown payment method exception.
    4. Check the movie quantity to make sure that there is a movie to be rented for the movie ID. If not then raise the unavailable movie exception.
    5. If all the data passes validation then you will need to create a new rental ID. This process should be in a nested block with its own EXCEPTION section to catch a NO_DATA_FOUND exception if one should happen. You can generate a new rental ID by finding the largest rental ID value in the mm_rental table (Hint: MAX function) and then increasing that value by one. The NO_DATA_FOUND exception would only be raised if there were no rental IDs in the table.
    6. Now you are ready to insert a new row of data into the mm_rental table. Use the SYSDATE function for the checkout date and NULL for the check-in date.
    7. Now, update the mm_movie table to reflect one less movie for the associated movie ID.
    8. Finally, you will need to set up an EXCEPTION section for all of your exception handling. For each exception output, you want to state what the problem is, the invalid data value, and a note that the rental cannot proceed. For example, for an invalid movie ID number, you might say, "There is no movie with id: 13 - Cannot proceed with rental". You also want to include a WHEN OTHERS exception handler.

Compile and check your code. If you get a PROCEDURE CREATED WITH COMPILATION ERRORS message then type in SHOW ERRORS and look in your code for the line noted in the error messages (be sure to compile your code with the session command SET ECHO ON). Once you have a clean compile then your are ready to test.


Step 2: Testing the First Procedure
You will need to test for scenarios that will allow both a clean movie rental and test each exception. This means that you will need to run at least five test cases.

One each for the following:
1. No movie for the ID supplied (use 13, 10, and 2 for the parameters).
2. No member for the ID supplied (use 10, 20, and 2 for the parameters).
3. No payment method for the ID supplied (use 10, 10, and 7 for the parameters).
4. A successful rental (use 5, 10, and 2 for the parameters).
5. No movie available for the ID supplied (use 5, 11, and 2 for the parameters). Since there is only one movie available for ID 5, you will get this exception.
Your output from the testing should look similar to (this would be the output for the first test above):
exec movie_rent_sp(13, 10, 2);
Output:
There is no movie with id: 13
Cannot proceed with rental
PL/SQL procedure successfully completed.

Be sure that when you have verified that everything works, you run your testing in a spools session and save the file to be turned in.


Step 3: Creating the Second Procedure
Your second procedure should be named MOVIE_RETURN_SP and should facilitate the process of checking a movie rental back in. For this procedure, you will only need to pass one piece of data to the procedure; the rental ID. You will need two user-defined exceptions; one for no rental record and one for already returned. You will be able to use several of the same techniques you used in the first procedure for your validation.

The following steps will help in setting up your code.
1. You will need to define only one parameter for the rental ID number. Make sure that it matches the data type of the associated column in the database table.
2. You will have several other variables that will need to be identified and defined. It might be easier to read through the rest of the specs before you start trying to define these (look for hints in the specifications).
3. You will need to define the two user-defined exceptions mentioned above.
4. You will need to validate the rental ID that is passed to the procedure. If it is not a valid one then raise the associated exception.
5. If it is valid then get the movie ID and check-in date from the mm_rental table.
6. Now, check the check-in date to make sure that it is NULL. If it is not then raise the associated exception.
7. If everything checks out then update the mm_rental table for the rental ID you have and use the SYSDATE function for the check-in date.
8. Now, you can update the quantity in the mm_movie table for the associated movie ID to reflect that the movie is back in stock.
9. Last, set up your exception section using appropriate error message text and data.

Compile and check your code. If you get a PROCEDURE CREATED WITH COMPILATION ERRORS message then type in SHOW ERRORS and look in your code for the line noted in the error messages (be sure to compile your code with the session command SET ECHO ON). Once you have a clean compile then your are ready to test.


Step 4: Testing the Second Procedure
You will need to test for scenarios that will allow both a clean rental return and test each exception. This means that you will need to run at least three test cases.

One each for the following:
1. No rental for the ID supplied (use 20 for the parameter).
2. A successful rental return (use 1 for the parameter).
3. Try to return the same rental in Step 2.
You output from the testing should look similar to (this would be the output for the first test above):
exec movie_return_sp(20);
Output:
There is no rental record with id: 20
Cannot proceed with return
PL/SQL procedure successfully completed.

Be sure that when you have verified that everything works, you run your testing in a spools session and save the file to be turned in.


Step 5: Creating the Function
Your function should be named MOVIE_STOCK_SF and will be used to return a message telling the user whether a movie title is available or not based on the movie ID passed to the function. The exception handling that will be needed is for NO_DATA_FOUND but we are going to set it up as a RAISE_APPLICATION_ERROR.

The following steps will help in setting up your code.
1. You will need to define only one parameter for the movie ID number. Make sure that it matches the data type of the associated column in the database table. Also, since you will be returning a notification message, you will want to make sure your RETURN statement references a data type that can handle that (Hint: variable length data type).
2. You will have several other variables that will need to be identified and defined. It might be easier to read through the rest of the specs before you start trying to define these (look for hints in the specifications).
3. You will not be doing any validation so the first thing you need to do is retrieve the movie title and quantity available from the mm_movie table based on the ID passed to the function.
4. Now, you need to determine if any are available. IF the value in the quantity column is greater than zero then you will be returning a message saying something like "Star Wars is available: 0 on the shelf", ELSE if the value is zero then you should return a message saying something like "Star Wars is currently not available". Hint: A good way to return a test string is to assign it to a variable and then simply use the variable name in the RETURN clause.
5. Finally, set up your exception section to use a RAISE_APPLICATION_ERROR for the NO_DATA_FOUND exception handler. Assign an error number of -20001 to it and an error message that states there is no movie available for the ID (be sure to include the id in the message).

Compile and check your code. If you get a FUNCTION CREATED WITH COMPILATION ERRORS message then type in SHOW ERRORS and look in your code for the line noted in the error messages (be sure to compile your code with the session command SET ECHO ON). Once you have a clean compile then your are ready to test.


Step 6: Testing the Function
You will need to test for all three possible scenarios.

1. Test for a movie in stock using movie ID 11.
2. Test for a movie not in stock using movie ID 5 (from your tests of the second procedure above, the quantity should be 0).
3. Test for an invalid movie ID using movie ID 20.
For test number 2, you may need to manipulate the quantity amount in the database, which will be fine.
Test your function by using a select statement against the DUAL table like in the example below:
select movie_stock_sf(20) from dual;

Be sure that when you have verified that everything works, you run your testing in a spools session and save the file to be turned in.
This concludes the Lab for Week 3.
 
Deliverables
Your deliverable submission should consist of your Lab 3 script file and the spooled output files described at the beginning of the lab. If you would like, you can include both files in a single ZIP file to be submitted to the Week 3 Lab Dropbox.

This is your final examination which consists of five programming questions.
You are to provide me with a .txt file or a .sql file for supplying the code for each question. Put these in a .zip file and submit them.

1. Use a cursor to retrieve the location number and the city from the locations table. Pass the location number to another cursor to retrieve from the emp_details_view the last_name, job_title and salary for that employees that work at that location.
a. Use the %ISOPEN attribute
b. Use a simple loop
c. Use an EXIT WHEN and a %NOTFOUND attribute

2. Write an exception handler to raise an exception named DUE_FOR_ROTATION
The criteria for raising the exception is if location id is 2700.
If the employee’s location in the em_details_view meets this criteria output 'Due for Rotation'.
Insert a listing of all of the EMPLOYEES that meet this criteria into the employee_analysis table.
You will need to create an analysis table as:
CREATE TABLE location_analysis
(employee_id NUMBER(6) not null,
Last_name VARCHAR(25),
First_name VARCHAR2(20),
Location_id NUMBER(4),
City VARCHAR2(30));
Screenshot #1 Creating the Analysis Table

3. You will be using the EMPLOYEES table in YOUR TABLESPACE. Using a cursor write a block to find the largest salary where the hire date is before ‘ 1-jan-96’ . When you find employees who match this criteria change give them a salary increase of 25%.

4. You will be using the JOBS table in YOUR TABLESPACE. Create a record that incorporates the four columns from the JOBS table. Output the values for a single job on four lines of output.

5. Write a PL/SQL block to print information about a given country.
a. Declare a PL/SQL record based on the structure of the COUNTRIES table.
b. Use the DEFINE command to provide the country ID. Pass the value to the PL/SQL block through a SQL*Plus substitution variable.
c. Use DBMS_OUTPUT.PUT_LINE to print selected information about the country.
d. Execute and test the PL/SQL block for the countries with the IDs CA, DE, UK, US.

CIS515 Assignment 2: Database Systems and Database Models

The Strayer Oracle Server may be used to test and compile the SQL Queries developed for this assignment. Your instructor will provide you with login credentials to a Strayer University maintained Oracle server.

Imagine that you have been hired as a consultant to assist in streamlining the data processing of an international based organization that sells high-end electronics. The organization has various departments such as payroll, human resources, finance, marketing, sales, and operations. The sales department is the only department where employees are paid a commission in addition to their yearly salary and benefits. All other departments compensate their employees with a yearly salary and benefits only. Commission is paid by multiplying the employee’s commission rate by the total amount of product units sold. You have access to the following data sets:

Employee (EmpNumber, EmpFirstName, EmpLastName, CommissionRate, YrlySalary, DepartmentID, JobID)
Invoice (InvNumber, InvDate, EmpNumber, InvAmount)
InvoiceLine (InvLineNumber, InvNumber, ProductNumber, Quantity)
Product (ProductNumber, ProductDescription, ProductCost)
Department (DepartmentID, DepartmentDescription)
Job (JobID, JobDescription)

Write a two to three (2-3) page paper in which you:

Design a query that will allow the finance department to determine the commissions paid to specific employees of the sales department for the month of December. Note: You will need to generate the tables described above (Employee, Invoice, InvoiceLine, Product, Department, and Job) in order to compare and validate your code. Validated query code must be part of your paper.
Compare the code of the query you designed in Question one (1) to one that would show how much total compensation is paid to each employee for the same month.
Determine and explain the factors necessary to ensure referential integrity.
Create an object-oriented model to show how the tables are interrelated through the use of graphical tools such as Microsoft Visio, or an open source alternative such as Dia. Make sure that you are able to show the relationship types such as 1:M, 1:1, or M:1. Additionally, remember to include the determined factors from the previous assignment requirement. Note: The graphically depicted solution is not included in the required page length.
Identify which data components are the entities and attributes, and the relationship between each using an object representation diagram through the use of graphical tools such as Microsoft Visio, or an open source alternative such as Dia. Note: The graphically depicted solution is not included in the required page length.
Describe how Big Data could be used to assist in the productivity and forecasting of the organization’s products and resources.

MIS562 SQL Database assignment

1. Show a list of all employee names and their department names and the employees for each department. Be sure to show all departments whether there is an employee in the department or not. Use an outer join.

2. Select all employee names and their department names. Be sure to show all employees whether they are assigned to a department or not. Use an outer join.

3. Write a query that that performs an inner join of the grade, student, and grade_type tables using ANSI SQL 99 syntax.

4. Write a query that that performs an inner join of inner join of the grade, student, and grade_type tables using the Oracle inner join convention.

5. List all the zip codes in the ZIPCODE table that are not used in the STUDENT or INSTRUCTOR tables. Use a set operator.

6. Write a SQL statement using a set operator to show which students enrolled in a section that are not enrolled in any classes. Exclude students with student id less than 300.

For the lab this week we are going to look at how the ROLLUP and CUBE extensions available in SQL can be used to create query result sets that have more than one dimension to them. Both of these extensions are used in conjunction with the GROUP BY clause and allow for a much more broad look at the data.

The first thing you will do for this lab is download the lab6_create.sql file and run the file in your database instance. This file will log into the DBM449_USER and then create and populate a set of tables that will be used for this lab.  Instructions for this are outlined in Step 1.

To record your work for this lab use the LAB6_Report.doc found in Doc Sharing. As in your previous labs you will need to copy/paste your SQL statements and results from SQL*Plus into this document. This will be the main document you submit to the Dropbox for Week 6.

LAB STEPS

STEP 1: Setting up Your Instance

For this lab you will be using a different user and set of tables than you have used so far for other labs. To set up your instance you will need to do the following.

1. Download the lab6_create.sql file associated with the link to either the C drive on your computer or the F drive in your Citrix account.
2. Open up the file and edit the login information at the top for the new user that is being created. You will need to replace the @ORACLE piece with the specifics for your instance name. DO NOT include AS SYSDBA after the name of your instance for this login.
3. Now log into your instance as the SYS user. Run the script. The script is too long to copy/paste it into your SQL*Plus session so you should run the script using the @ sign from the SQL> prompt.
4. Once the script has finished running then issue a SELECT * FROM TAB; sql statement. The result set will have tables from other labs as well but you want to make sure that you see the following tables listed.

STEP 2: Using the ROLLUP Extension 

In this section of the lab you are going to create a sales report that will show a supplier code, product code and the total sales for each product based on unit price times a quantity. More importantly the column that shows the total sales will also show a grand total for the supplier as well as a grand total over all (this will be the last row of data shown). To do this you will use the ROLLUP extension as part of the GROUP BY clause in the query. Use aliases for the column names so that the output columns in the result set look like the following.

SUPPLIER CODE PRODUCT    TOTAL SALES
------------- ---------- -----------

For this report you are going to use the SALES, PRODUCT and SUPPLIER tables. You should be able to write your query using NATURAL JOIN but if you feel more comfortable using a traditional JOIN method that will work just as well. When finished you should have a total of 16 rows in your report and the grand total amount should show 2810.74.

Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.

STEP 3: Using the CUBE Extension

In this section of the lab you are going to create a sales report that will show a month code, product code and the total sales for each product based on unit price times a quantity. In this report the column that shows the total sales will also show a subtotal for each month (in this case representing a quarter) . Following the monthly totals for each product and the subtotal by month then the report will list a total for each product sold during the period with a grand total for all sales during the period (this will be the last row of data shown). To do this you will use the CUBE extension as part of the GROUP BY clause in the query. Use aliases for the column names so that the output columns in the result set look like the following.

     MONTH PRODUCT    TOTAL SALES
---------- ---------- -----------

For this report you are going to use the SALES, PRODUCT and TIME tables. You should be able to write your query using NATURAL JOIN but if you feel more comfortable using a traditional JOIN method that will work just as well. When finished you should have a grand total amount of 2810.74 (same total as in the step 2).

Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.

STEP 4: Materialized Views and View Logs

Materialized views, sometimes referred to as snapshots are a very important aspect of dealing with data when doing data mining or working with a data warehouse. Unlike regular views, a materialized view does not always automatically react to changes made in the base tables of the view. To help keep track of changes made to the base tables you must create what is call a Materialized View Log on each base table that will be used in the view. In this step of the lab we will do this.

For the Materialized View we are going to create we are going to use the TIME and the SALES tables. Before we can create the view you will need to create a Materialized View Log on each of these two tables that will keep track of the ROWID and Sequence and include new values that have been added to the base table.

Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.

STEP 5: Creating and Using the Materialized View

Now that we have our logs created we can progress on to the view itself. For this part of the lab you are going to create a Materialized View, demonstrate that the view works, insert a row of data into one of the base tables and then update the view. Finally, you will show that the new data is in the view. The following steps will help move you through this process.

1. First, write the SQL CREATE statement that will create a Materialized View based on the following:
• Name the view SALESBYMONTH.
• Include clauses that will build the view immediately, completely refresh the view, and enable a query rewrite.
• For the columns of the view you want to show the YEAR, MONTH, PRODUCT CODE, a TOTAL SALES UNITS, and a TOTAL SALES.
• You will want to group the columns by year, month and product code respectively.
2. Execute your script to create the view and then issue a SELECT * FROM SALESBYMONTH.

The output columns from your view should look similar to the following (use aliases to format the column headings) and you should have 18 rows in the result set.

                                  YEAR      MONTH PRODUCT CO UNITS SOLD SALES TOTAL
                              -------- ---------- ---------- ---------- -----------

Now we are going to add some data and update the view. Because we have several derived columns in out view we will have to force the update as Oracle will not automatically update a view with this configuration.

1. To begin with, insert the following data into the SALES table - (207, 110016, 'SM-18277',1,8.95).
2. Now we are going to use a subprogram within the Oracle built in package DBMS_MVIEW. The REFRESH subprogram within this package will update our view so that we can see the new data.
3. Write an SQL EXECUTE statement that will use the REFRESH procedure in the DBMS_MVIEW package (HINT: packagename.subprogram). The REFRESH subprogram accepts two parameters; the name of the materialized view to refresh, and either a 'c', 'f', or '?'. For the purposes of the lab use the 'c'. (you can refer back to pages 654-659 of the DBA Handbook readings for week 3).
4. Execute your statement to update the view and then query the view once again.

You should now see that the row for units sold in month 10 for SM-18277 has increased from 3 to 4 and total sales amount has gone from 26.85 to 35.80.

Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.

Deliverables

Submit your completed Lab 6 Report to the Dropbox. Your report should contain copies of each query and result set outlined in the lab along with the requested explanation of whether or not it satisfied the business requirement outlined for that particular section of the lab.

In your lab for this week you are going to work with three different areas and processes within the Oracle Database that can be used to control data security. Each of these three processes has its own distinctive application to providing levels of security. In each case the individual processes deal with either limiting a users access to the database, limiting access to processes within the database, or keeping track of what the user is doing while in the database.

For the lab you will be using the SCOTT user which is already created in your instance. In Step 4 you will also be asked to shutdown you instance, make some edits to the init.ora file for your instance and then restart the instance. If you are not comfortable with this process which was first introduced to you in DBM438 the refer to the iLab Manual found in week 1 for guidance.

To record your work for this lab use the LAB5_Report.doc found in Doc Sharing. As in your previous labs you will need to copy/paste your SQL statements and results from SQL*Plus into this document. This will be the main document you submit to the Dropbox for Week 5.

LAB STEPS

STEP 1: Define a New Profile

Oracle provides the ability to set expirations, limit the reuse, and define the complexity of passwords. In addition, accounts can be locked if the password is entered incorrectly too many times. In this section of the lab we are going to create a custom profile that will then be applied to the SCOTT user.

1. To begin, log into your instance as the SYS user.
2. Write SQL script that will create a new profile named DBM449_SCOTT_PROFILE that will do the following:
• Limit the number of failed login attempts to 3 in a row.
• Limit the overall connection time to 10 hours (we will give him a little leeway incase he has to work overtime).
• Allow a session to be idle no more than 1 hour.
• Change the password every 60 days.
• Allow the user 3 days to change the password after it expires.
• Not allow a previous password be reused before there have been three password changes.
3. Execute your pfile script and verify that the profile has been created by running a query against the DBA_PROFILES view in the data dictionary. Limit your output to ONLY the DBM449_SCOTT_PROFILE parameters.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

STEP 2: Testing the New Profile

Now that we have a new profile for the SCOTT user we need to verify that it works properly. For obvious reasons there are going to be parts of the profile that we cannot test within the confines of this lab due to time constraints, but we can test to verify that the SCOTT user is being controlled by the profile.

1. The first thing we need to do is assign the profile to the SCOTT user. While still logged into your instance as the SYS user write and execute the SQL command that will assign the new SBM449_SCOTT_PROFILE profile to the SCOTT user.
2. Now log into SCOTT (password is TIGER). Remember that you must supply the database instance name when logging in from the SQL> prompt just as you do when using the login window, i.e. CONN SCOTT/TIGER@DB####.WORLD.
3. There are several things that we can test related to the logging in and changing a password so here we go.
• You should now be successfully connect to the SCOTT user. Write the connect command again on this time use an incorrect password. NOTE: you should get a warning message stating that you are no longer connected to Oracle. That is fine, just keep trying to log in.
• Repeat the above process until you get the ORA-28000: the account is locked error which will indicate that the profile is working here.
• At this point we need to get the account unlocked so you will need to login to your instance as the SYS user and unlock the SCOTT account BUT DO NOT LOG BACK INTO THE SCOTT USER YET.
• Now we can test the password reuse parameter. To do this we must EXPIRE the current password. Write and execute the SQL command to expire the password for the SCOTT user.
• Now log back into the SCOTT user. You should receive a message stating that the password has expired (ORA-28001: the password has expired) and then prompting you to change the password.
• Try to reuse the TIGER password. You should receive the following - ORA-28007: the password cannot be reused.
4. Now log into the SCOTT user again and this time change the password to LION to complete this step of the lab.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

STEP 3: Using the PRODUCT_USER_PROFILE table

As the owner of a schema a user has certain inherited privileges that would allow the user to pass access to his/her own objects on to other users. Often times this can open up data to scrutiny by individuals who probably do not need to have access to it. These types of decisions should always be made by the DBA in charge of the database. One mechanism the DBA has to keeping users from using these inherited privileges is by excluding those commands using the PRODUCT_USER_PROFILE (PUP) table. In this section of the lab we are going to do this to the SCOTT user by setting up the scenario that will prohibit him from giving the user GEORGE (created in lab 2) access to the EMP table.

1. For this section and remainder of the lab you must have the PRODUCT_USER_PROFILE successfully loaded and accessible in your instance. The creation of this profile was one of the first things done back in Lab 1 when you ran the PUPBLD.SQL script. If you are getting an error message stating "Error accessing PRODUCT_USER_PROFILE" when you log in as the DBM449_USER or the SCOTT user then this profile is not successfully installed. Work with your instructor to figure out why your script from Lab 1 did not work correctly. Until this is resolved you will not be able to complete the remainder of the lab.
2. If you have the PRODUCT_USER_PROFILE successfully working then log in to your database instance as the SYS user.
3. Now we need to limit SCOTT from being able to use the GRANT command.
• Insert the proper values into the PRODUCT_USER_PROFILE table that will keep the SCOTT user from using the GRANT command. Remember that some of the values in your insert statement must be in upper case and some will need to be in mixed case. Once you have done this then query the table to verify the insert (REMEMBER: you cannot query the table as the SYS user, only as the SYSTEM user).
• Now we need to test our above settings and make sure they are working.
• Connect to the SCOTT user (remember that you changed the password to LION).
• Write and execute the statement that would GRANT the user GEORGE the ability to write a select statement and see the data in the EMP table owned by SCOTT. You should receive the following message - SP2-0544: Command "grant" disabled in Product User Profile.
4. This verifies that you have now disabled the ability of the SCOTT user to allow another user to access any of the data in his schema.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

STEP 4: Setting up the Database to use Auditing

Being able to audit what, when and where people are doing things in the database can be a very enlightening thing for a DBA. It can also be a very important tool in working with Data Security. Oracle provides the ability to do various types of auditing, but it takes some special setting up of the environment for this to work. In this step we are going to make the necessary adjustments to the current Oracle instance so that we can enable auditing and make some tests. If you need to review the processes to be used here then refer to the iLab Manual in week 1.

1. First you need to make sure that you are logged into your instance as the SYS user.
2. At this point issue a SHUTDOWN IMMEDIATE command to shut down you database instance.
3. Once the instance is shut down you need to go into your Citrix Windows Explorer application, find your database instance set of directory folders, drill down to the pfile directory folder and open your init.ora file found in that folder.
4. Under the section titled "Security and Auditing" you need to add the parameter AUDIT_TRAIL and set the parameter to DB_EXTENDED. This will allow the SQL_TEXT column of the DBA_AUDIT_OBJECT view to be populated. Save and close the file and then go back to your SQL*Plus session.
5. Now using the init.ora file, start your instance back up to an OPEN status. You can do this by issuing a STARTUP PFILE= statement and pointing to your init.ora file.
6. Once you have completed this process you are ready to begin setting up the database to audit some activity.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

STEP 5: Creating an Audit Trail

Oracle permits audit trails to be generated for session login attempts, access to objects, and activity performed on objects. Again using the SCOTT user we are going to set up several scenarios for auditing what SCOTT does while in a session. NOTE: if you need to work through this process several times you can delete the values in the AUD$ base table by issuing the TRUNCATE TABLE AUD$ command while logged in as the SYS user.

1. Make sure that you are connected as user SYS.
2. Display value of the parameter AUDIT_TRAIL. For the VALUE column you should have a value of DB_EXTENDED.
3. Now we can set up auditing to track what goes on in the database.
• Write SQL statements to audit successful and unsuccessful login attempts by SCOTT.
• Write SQL statement to audit any successful INSERT, UPDATE or DELETE performed on table DEPT in scott's schema.
4. Now we need to test the audits to verify that they work.
• Log into the SCOTT user (remember that the password is LION) and perform the following:
• write and execute an UPDATE statement that will change the value in the LOC column of the DEPT table to MIAMI where the DEPTNO value is 10.  Be sure to issue a COMMIT.
• Write and execute the INSERT statement that will in insert the following values into DEPT - (50, 'LEGAL', 'HOUSTON').  Be sure to issue a COMMIT.
• Write and execute the DELETE statement that will delete the row from the DEPT table that was just inserted in the step above.  Again, be sure to issue a COMMIT.
• Try to reconnect to the SCOTT user with an invalid password.
• Now connect back to the SYS user.

Now we need to see if our auditing worked.

1. While logged into your instance as the SYS user, query the DBA_AUDIT_OBJECT view of the data dictionary for the user name of the account (Not the OS), the object owner, the object name, the action name and the SQL command (text) from the DBA_AUDIT_OBJECT view in the Data Dictionary.
2. Did you notice that the entries for successful logon and unsuccessful logon attempts were not there. Now query the user name, action name and return code values in the DBA_AUDIT_SESSION view. You should find that information here.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

Deliverables

Submit your completed Lab 5 Report to the Dropbox. Your report should contain copies of each query and result set outlined in the lab along with the requested explanation of whether or not it satisfied the business requirement outlined for that particular section of the lab.

CMIS 420 Homework Assignment 3 Advanced Relational Database Concepts and Applications

Homework Task 1:
Create a PL/SQL block to complete the followings.  
Output the cheapest movie information.  You can use the view you created in project 1 - task 1
In your block, referential type should be used to receive the cursor return.
Use DBMS_OUTPUT to output your result.
Possible Exception should be handled in exception handling section.  You are not required to use user-defined exception. Use Oracle predefined exception. When exception occurs, you need to output error code and error message.
Spool the output to a text file.  Don't forget to use "Set serveroutput on"
Submit your code as .sql file and spooled output

Homework Task 2:
Create a stored procedure based on task 1 with an input parameter movie_id. Modify your cursor to use movie_id to select desired movie information. And execute the procedure and spool the execution result.
Spool the output to a text file.  Don't forget to use "set serveroutput on"
Submit your code as .sql file and spooled output

Homework Task 3:
Create a statement trigger on orders table. The trigger fires after updating the table. When the trigger fires one record insert into the following temp table using the insert statement shown below:
Note: you need to create temp_table and its sequence using the following code.

PROMPT creating table temp_table ...............
DROP TABLE temp_table;
CREATE TABLE temp_table
( num_col NUMBER(5) not null primary key,
char_col VARCHAR2(30),
date_col  VARCHAR2(30));

PROMPT creating SEQUENCE trigger_seq ...............
DROP SEQUENCE trigger_seq;
CREATE SEQUENCE trigger_seq
START WITH 1
INCREMENT BY 1;

INSERT INTO temp_table (num_col, char_col, date_col)
VALUES (trigger_seq.NEXTVAL, 'After Statement trigger', TO_CHAR(sysdate, 'DD-MON-YYY HH24:MI:SS'));
Submit your code as .sql file

Homework Task 4:
Create a row trigger on order_items table to fire after inserting the data into order_items table. When the trigger fires it inserts a record into temp_table using the following insert statement:
INSERT INTO temp_table (num_col, char_col, date_col)VALUES (trigger_seq.NEXTVAL, 'After Row Trigger', TO_CHAR(sysdate, 'DD-MON-YYY HH24:MI:SS'));
Submit your code as .sql file

Homework Task 5:
Test your triggers.
Update orders table to set total payment to 300. fire statement trigger on orders table.
Insert one record into order_items. Fire row trigger on order_items table.
Query temp_table to get the inserted records for trigger firing.
Spool the output to a text file and submit it.