Oracle

Need Help in Oracle Assignment?
We can help you if you are having difficulty with your Oracle Assignment. Just email your Oracle Assignment at admin@assignmentcache.com.
We provide help for students all over the world in Oracle Assignment.

Items 41 to 50 of 63 total

per page
Page:
  1. 3
  2. 4
  3. 5
  4. 6
  5. 7

Grid  List 

Set Ascending Direction
  1. CIS515 Assignment 2 Database Systems and Database Models

    CIS515 Assignment 2 Database Systems and Database Models

    Regular Price: $20.00

    Special Price: $15.00

    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.

    Learn More
  2. IS562 SQL database queries

    MIS562 SQL Database Queries Assignment

    Regular Price: $10.00

    Special Price: $8.00

    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.

    Learn More
  3. DBM 449 Lab 6 SQL Analytical Extensions and Materialized Views

    DBM 449 Lab 6 SQL Analytical Extensions and Materialized Views

    Regular Price: $20.00

    Special Price: $15.00

    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.










    TNAME                          TABTYPE CLUSTERID
    ------------------------------ ------- ----------
    SUPPLIER                       TABLE
    PRODUCT                        TABLE
    DISTRICT                       TABLE
    CUSTOMER                       TABLE
    TIME                           TABLE
    SALES                          TABLE


     




    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.

    Learn More
  4. DBM 449 Lab 5 SQL Audit and Profile Management

    DBM 449 Lab 5 Audit and Profile Management

    Regular Price: $20.00

    Special Price: $15.00

    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.

    Learn More
  5. DBM 449 Lab 4 Oracle Object Types

    DBM 449 Lab 4 Oracle Object type

    Regular Price: $20.00

    Special Price: $15.00

    L A B O V E R V I E W


    Scenario/Summary
    For this lab you will begin by using the same set of tables that you used for Lab 1 so be sure that you are connected to Oracle as the DBM449_USER user. The objective of this lab will be to create a series of object-relational tables using the SQL*Plus editor that will allow data to be stored in a more "real-world" format. Data for your new tables can be found in the file Lab4_data.txt associated with this link. You will need to manipulate the data in various ways, but the file will give you access to the raw data to use.
    To record your work for this lab use the LAB4_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 Drop Box for Week 4.


    L A B S T E P S
    STEP 1: Create a table with a column data type

    Modify the design of the COURSE table created in iLab 1 to incorporate the use of the column abstract data type.



    1. Write and execute the SQL to create a single object type called COURSE_OBJ1 that contains both the attributes course code and course name. Remember that with abstract objects you must use the / after the CREATE statement to execute it.

    2. Next, write and execute the SQL to create a table called NEW_COURSE1 that contains COURSE_OBJ1 along with the original attributes from the original COURSE table. Keep in mind what attributes the new object type COURSE_OBJ1 contains. Your table should have a total of 4 individual columns when finished.

    3. Using the data from the LAB4_DATA file create and execute the insert statements to load the new table NEW_COURSE1. SUGGESTION: Using the Lab4_data file create a script file of your insert statements and then run the script file. Remember that you will need enclose some of the data in single quotes depending on if it is character, date, or numeric data.

    4. Run DESCRIBE command to describe structure of table NEW_COURSE1.

    5. SET DESCRIBE DEPTH 2 and run DESCRIBE NEW_COURSE1 again.

    6. Execute a SELECT statement to query the data from the new table (DO NOT use a SELECT * type query). Use the COLUMN column_name FORMAT A## session command to format columns within the table to keep the result set data from wrapping around. Be sure that you properly display data inside the object column. (HINT: When querying attributes of an abstract data type, you must use a correlation variable for the table.)


    STEP 2: Create an object table with a row data type
    Create a second COURSE table, this time as an object table using the row abstract data type.



    1. Write and execute the SQL to create an object called COURSE_OBJ2 that contains the attributes course code, course name, course date, instructor, and location.

    2. Write and execute the SQL to create a table called NEW_COURSE2 with a single column defined using the COURSE_OBJ2 object.

    3. Using the data from the LAB4_DATA file create execute the insert statements to load the new table NEW_COURSE2.

    4. Execute a SELECT statement to query the data from the new table (DO NOT use a SELECT * type query).


    STEP 3: Create a Varying Array
    Modify the design of the CLIENT table created in iLab 1 to incorporate the use of the Varying Array.



    1. Write and execute the SQL to create a Varying Array to represent the phone contact information for the client (up to 3 phone numbers). Name the varying array as PHONE_LIST.

    2. Write and execute the SQL to create a table called NEW_CLIENT that contains the attributes that the original CLIENT table contained plus the phone list array.

    3. Using the data from the LAB4_DATA file create execute the insert statements to load the new table NEW_CLIENT.

    4. Execute a SELECT statement to query the data from the CLIENT_NO and CLIENT_NAME columns along with the data in the column containing the phone number Varray (You cannot use a SELECT * type query for this step).


    Deliverables
    Submit your completed Lab 4 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.


     

    Learn More
  6. DBM 405 Lab 2 Simple PL SQL Applications

    DBM 405 Lab 2 Simple PL/SQL Applications Advanced Database Oracle

    Regular Price: $20.00

    Special Price: $15.00

    DBM 405 Lab 2 Simple PL/SQL Applications Advanced Database Oracle

    Scenario/Summary
    The purpose of this week's lab is to work with basic PL/SQL syntax to create an anonymous block of code. In the lab, you will be using SQL*Plus to modify one of the tables in the MovieRental schema and then write a simple block of code to update the table with some new data and then execute the code in SQL*Plus. As an additional task in the lab, you will be asked to modify the existing PL/SQL block of code given to you to add exception handling and then execute it in SQL*Plus. Both of these concepts will help enforce the material covered in this second week.
    For the lab, you will need to create a script file containing the PL/SQL code that will address the lab steps below. Run the script file in your SQL*Plus session using the SET ECHO ON session command at the beginning to capture both the PL/SQL block code and output from Oracle after the block of code has executed. To successfully test the code in Step 3, you will need to copy/paste your code into SQL*Plus for each movie ID as you change the value for the host variable. Spool your output to a file named with your last name plus lab 2 and give the file a text (.txt) extension. For example, if your last name was Johnson then the file would be named johnson_lab2.txt. Submit both the spooled output AND the script file for grading of the lab.

    LAB STEP
    Step 1:
    As business is becoming strong and the movie stock is growing for More Movie Rentals, the manager wants to do more inventory evaluations. One item of interest concerns any movie for which the company is holding $75 or more in value. The manager wants to focus on these movies in regards to their revenue generation to ensure the stock level is warranted. To make these stock queries more efficient, the application team decides that a column should be added to the MM_MOVIE table named STK_FLAG that will hold a value '*' if stock is $75 or more. Otherwise, the value should be NULL. Add the new column to the MM_MOVIE table as a CHAR data type.
    Execute a DESC MM_MOVIE on the table both before you add the new column and after the column is added.
    Note: Since this is code will be in your script file, you will need to comment it out after the first time you have execute the ALTER TABLE statement successfully to avoid getting errors each additional time your script file is run.

    Step 2:
    Create an anonymous block of PL/SQL code that contains a CURSOR FOR loop to accomplish the task described above in Step 1. Your loop will need to interrogate the value (using an IF statement) found in the movie_qty field of the cursor loop variable to see if it is >= 75. If this is true then you will need to update the new column in the table with an '*' WHERE CURRENT OF the table. If the quantity is not >= 75 (the ELSE side of the IF statement) then update the new column with a NULL.
    Execute a SELECT * from MM_MOVIE both before and after you execute the new PL/SQL block of code to show that the process works.

    Step 3:
    Here is a block that retrieves the movie title and rental count based on a movie ID provided via a host variable.
    SET SERVEROUTPUT ON
    VARIABLE g_movie_id NUMBER
    BEGIN
    :g_movie_id := 4;
    END;
    /
    DECLARE
    v_count NUMBER;
    v_title mm_movie.movie_title%TYPE;
    BEGIN
    SELECT m.movie_title, COUNT(r.rental_id)
    INTO v_title, v_count
    FROM mm_movie m, mm_rental r
    WHERE m.movie_id = r.movie_id
    AND m.movie_id = :g_movie_id
    GROUP BY m.movie_title;
    DBMS_OUTPUT.PUT_LINE(v_title || ': ' || v_count);
    END;
    /
    Modify the block of code to add exception handlers for errors that you can and cannot anticipate. You will need to execute the entire code listing shown above each time you wish to test it by changing the value of :g_movie_id for each test.
    Once finished, test your exception handling by running the modified block for the following values of :g_movie_id. Be sure that you can capture the value in the :g_movie_id host variable.
    •    12 - normal output will display title and number of rentals
    •    13 - exception - there is no movie ID for 13
    •    1 - exception - Movie with ID 1 has never been rented

    This concludes the Lab for Week 2.

    Learn More
  7. CMIS 420 Homework Assignment 3 Task 1

    CMIS 420 Homework Assignment 3 Advanced Relational Database Concepts and Applications

    Regular Price: $20.00

    Special Price: $15.00

    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.

    Learn More
  8. DBM 405 Lab 7 Front End GUI

    DBM 405 Lab 7 Study Case Front-End GUI

    Regular Price: $20.00

    Special Price: $15.00

    DBM 405 Lab 7 Study Case front-end GUI

    Scenario/Summary
    The More Movies company has hired you to redesign a database system for them that can facilitate the process of renting out and returning movies.
    They already have an Oracle database that stores information about movies, members who rent the movies, and the rentals. This is the database that you already have become familiar with and the one which includes tables: MM_MOVIE, MM_MOVIE_TYPE, MM_MEMBER, MM_RENTAL, and MM_PAY_TYPE. The machine on which this database is running has both the server and client Oracle9i software installed on it. Every night, a clerk updates data to account for the day's activities, and periodically the reports are run to summarize business, show renting trends, etc. Access to the database is accomplished using a SQL*Plus environment that is very similar to the iSQL*Plus that you know from the previous database course. This business process worked okay for as long as More Movies stayed a very small business.
    However, the company has grown substantially, expanding its operations to more movie selection and more members, and consequently, it has moved to a larger location. It occupies a two-story shop now. It became very impractical to record rentals at the end of the day. They also do not want to rely on clerks knowing any SQL programming in order to record updates and run reports.
    In short, there is a need for a more convenient database system. The machine on which the database is currently running is powerful enough to host the database server. The database should be accessible from four checkout stations that process renting out and returning movies. This system should have an easy-to-use graphical user interface access.
    For the lab, you will be creating several documents to be submitted for the lab. Be sure that you save the documents with your last name and lab7 in the file name. Place all documents into a single ZIP file and submit for grading.

    LAB STEP
    Step 1:
    Describe what software you propose to use to develop the front-end GUI application for the new system. Be sure to justify your choice. Keep in mind portability, ease of use, scalability, and ability to update. What other options have you considered?

    Step 2:
    In setting up the servers and environment, do you propose to use middleware? If so, what kind, and where would you deploy it?

    Step 3:
    Provide a system diagram of the proposed system. Be sure to include such things as servers (application and database), user clients, and any other special pieces to the puzzle that you might think of.

    Step 4:
    Provide a detailed design of the GUI screen that facilitates renting out and returning movies. For every button, or other component that provides reaction to user's events, give detailed pseudocode. Also, clearly indicate where you would use any of the PL/SQL code that you developed for the labs in this course. If the application platform you have selected does not support PL/SQL then describe how you would take the processing developed in the procedures and functions and incorporate it into the system.

    This concludes the Lab for Week 7.

    Learn More
  9. ITSE 2309 Oracle Olympics Database Queries

    ITSE 2309 Project Database Programming Oracle Olympics Database

    Regular Price: $30.00

    Special Price: $25.00

    ITSE 2309 Project Database Programming Oracle Olympics Database


    Project Objective: Design and implement a database from requirements and execute queries using SQL.
    1. Design a database for tracking Olympic events and results.
    2. Write scripts to create the database and populate the tables.
    3. Write SQL queries to produce reports.


    Deliverables
    1. Create an ER Diagram for your Olympics database.
    2. Write a short design document (1 or 2 pages) describing your choices and reasons for designing your database as you did.
    3. Provide scripts to create, populate, and teardown the Olympics database.
    A. makeOlympicsDb.sql – Script to create tables, constraints, & any DB objects.
    B. populateOlympicsDb.sql – Script to add records to the database tables.
    C. dropOlympicsDb.sql – Script to delete all tables and database objects.
    4. Provide one or more scripts (with .sql file extension) to execute the queries defined for the project. Each script should include comments with your name and query/question number.


    Database Requirements
    The International Olympic Committee is creating a database for their upcoming summer Olympic games. Each sport has competitions for several different events. The competitions for each sport are assigned to a specific venue. A competitor is an individual athlete representing a particular country. Each event is scheduled for a single day and time, and we save a single result for each athlete competing in the event.
    Note: For this project, there are no qualifying rounds.
    The goal of the database is to identify when and where competitions occur, the athletes competing in events, and the medalists for each competition. Medals are awarded:
    1. Gold for 1st place
    2. Silver for 2nd place
    3. Bronze for 3rd place
    For each competition, results are stored for each athlete. The results are recorded as an elapsed time, a score, or a measurement.


    Data Population
    To test the database, you need to populate with data to demonstrate that the database will meet requirements and produce desired reports. For demonstration purposes, the data should be populated with events and competitors for Gymnastics, Track and Field, and Swimming. Use your own creativity to name athletes and choose countries for the competitions. Provide at least 5 competitors for each event. Some athletes should compete in multiple events and some compete in only a single event.


    Queries
    1. List all the Olympic events in which women compete, sorted alphabetically. No duplicates.
    2. List all the Olympic sports with the earliest event date/time and latest event date/time for the events contested in the sport. Order results by the name of the sport. Each sport should only be listed once.
    3. List all events scheduled for August 3rd, with the time and venue of the event. Sort events by the time, with the earliest event listed first. If more than one event starts at the same time, sort by the name of the event.
    4. For Gymnastics, list each event and the names of all the athletes competing in those events. Sort results by the name of the event, and secondarily by the name of each athlete.
    5. List all the countries in your database and the number of individual athletes from each country. Sort by country name.
    6. List all the names of athletes who compete in more than one event along with the name of his/her event and the competition date/time. Sort results by the athlete’s name.
    7. List each sport, the names of each event (including gender), and the number of competitors entered into each event.
    8. List results for Track and Field’s 100 Meter race with times for each athlete, with fastest time first. List the athlete’s name, country, and race time.
    9. List all medal results for events that have already occurred and been entered into the Olympics database. List the sport, event, athlete’s name, country and medal (Gold/Silver/Bronze). Order results by the sport, event, and place. (Gold Medal = 1st Place, Silver = 2nd Place, Bronze = 3rd Place)
    10. List the countries that won the most cumulative medals in descending order, listing the number of gold, silver, and bronze medals, along with the total. Report should be sorted with the most medals listed first in the report down to the country with the fewest medals. If countries have the same number of medals, the most Gold/Silver/Bronze next followed by alphabetic listing by country name.

    Learn More
  10. IM300 Week 4 Hotel Database Part 3 DDL script

    IM300 Week 4 Integrity and Constraints Hotel Database Part 3

    Regular Price: $25.00

    Special Price: $20.00

    IM300 Week 4 Integrity and Constraints Hotel Database Part 3


    Resources: SR-bi-003 – Hotel Database, the results from SR-bi-003 – Hotel Database Parts 1 & 2, Oracle® Database Express® and Microsoft® Visio® Virtual Desktop.


    Complete Service Request SR-bi-003 - Hotel Database–Part 3.


    Normalize the entities and attributes to at least the third normal form.


    Use Microsoft® Visio® diagramming tools to create an entity-relationship diagram (ERD) that demonstrates the logical design of your normalized database.


    Create and alter tables in the city’s Oracle® Database Express® database to implement the ERD design.


    Generate a new DDL script.


    Create a fax cover sheet addressed to the project manager for the ERD and a newly generated DDL script to show your progress on the project.

    Learn More

Items 41 to 50 of 63 total

per page
Page:
  1. 3
  2. 4
  3. 5
  4. 6
  5. 7

Grid  List 

Set Ascending Direction