Search results for 'dbm 502 learning team'

DBM 405 Lab 1 SQL Review Advanced Database Oracle

Scenario/Summary
The purpose of the first week's lab is to review the SQL language, and familiarize you with the database example that we will use in labs through this course. If you have not done it yet, please download the MovieRentals.SQL file to your computer from Doc Sharing. This script file will need to be run once logged into your Oracle user account to create the tables and data that will be used for this lab and subsequent labs in the course.
Setting up your environment:
Before attempting Lab 1, you need to be sure you have read through the SQL*Plus tutorial which can be found in Doc Sharing as well as under the SQL*Plus Tutorial tab in Week 1. This tutorial describes the functionality of the editor and will step you through the process of setting up and using the SQL*Plus in the iLab environment so that it will best serve your needs for the remainder of the labs required for this course. Once you have logged into Oracle through iLab then execute the MovieRental.SQL script and create the tables and data for the lab.
For the lab, you will need to create a script file containing the eight queries 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 query and result set. Spool your output to a file named with your last name plus lab 1 and give the file a text (.txt) extension. For example, if your last name was Johnson then the file would be named johnson_lab1.txt. Submit both the spooled output AND the script file for grading of the lab.

LAB STEP
Step 1:
Within SQL*Plus, list names of the tables that you have created whose name starts with MM (Hint: use data dictionary view USER_TABLES).

Step 2:
Use DESCRIBE (in short: DESC) command in SQL*Plus for each of these tables to show columns and their datatypes.

Step 3:
Use SELECT * command to display all data from each of the tables in the MoreMovies schema. Make sure that the LINESIZE and PAGESIZE have large enough values, and that you format columns so that the report looks good. You should end up with five queries and result sets.

Step 4:
Using the mm_movie and mm_movie_type tables, write a query that will list all movie categories together with the count of movies in each category. Give the column with the count in it a meaningful name such as IN STOCK.

Step 5:
Using the mm_movie and mm_rental tables, write a query that will list titles and checkout dates for all movies that were signed out by Wild Coyote (MEMBER_ID=13).

Step 6:
Using the same two tables used in Step 5, write an SQL sub-query that will list all movies (movie ids and titles) of all movies that have never been rented.

Step 7:
Using the mm_member and mm_rental tables, write a query that will list all members (member ID, first name, and last name) and the number of movies they have rented, for all members who have rented at least one movie. Order the result set so that it shows the largest number of movies rented as the first row.

Step 8:
Write a query that will display the largest number of movies rented by one member and that member's name. Give the output column a meaningful name such as MAXIMUM NUMBER.

Step 9:
Using the mm_member and mm_rental tables, write a query that will display member ID, last name, first name, and the number of movies rented for each member. Give the column with the number of movies rented a meaningful name such as NUMBER RENTED.

Step 10:
Using the mm_member, mm_movie and mm_rental tables, write the query that will prepare a report that shows who rented which movie. Use member names (first and last) and movie title rather than the corresponding IDs. Order the report by member names, and for a single member by the movie titles.

This concludes the Lab for Week 1.

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.

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.

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.

Learning Team Instructions Fundraiser Program
A city is sponsoring a run to support local charities and would like an application to track the pledges. The result will be a database that holds data on individuals, total pledges obtained, and the charity for which the donation is designated.
Design and implement a GUI-based program to accept a participant’s name, the amount pledged, and the designated charity’s name. The program will store these data for later retrieval.

PRG 421 Week 3 Learning Team Initial program
Create a GUI-based program to accept name of donor, name of charity, and amount of pledge from the user.
Display a list of entries in a JTextArea or JTable.
Submit the .java source file for this program.

Learning Team Instructions Fundraiser Program
A city is sponsoring a run to support local charities and would like an application to track the pledges. The result will be a database that holds data on individuals, total pledges obtained, and the charity for which the donation is designated.
Design and implement a GUI-based program to accept a participant’s name, the amount pledged, and the designated charity’s name. The program will store these data for later retrieval.

PRG 421 Week 4 Learning Team Instructions Add a Data File
Modify the program you created in Week Three to write the data—name, amount, and charity—into a sequential data file.
Implement a capability of reading what is in the existing data file and displaying it in the text area.
Submit the .java source file for the program.

Learning Team Instructions Fundraiser Program
A city is sponsoring a run to support local charities and would like an application to track the pledges. The result will be a database that holds data on individuals, total pledges obtained, and the charity for which the donation is designated.
Design and implement a GUI-based program to accept a participant’s name, the amount pledged, and the designated charity’s name. The program will store these data for later retrieval.

PRG 421 Week 5 Learning Team Connect to a Database
Write a program to create a database from the data in the sequential file you created in Week Four.
Modify the GUI to connect to this database and add entries to it and read entries from it. Entries read from the database should be displayed in the JTextArea or JTable.
Test and debug this final program.
Submit the .java source file or files.

PRG 420 Week 2 Simple Commission Calculation Program Part 1

Write a Java™ application using NetBeans™ Integrated Development Environment (IDE) that calculates the total annual compensation of a salesperson. Either a GUI graphic user interface) program or non-GUI program is acceptable. Consider the following factors:
• A salesperson will earn a fixed annual salary of $25,000.00.
• A salesperson will also receive a commission as a sales incentive. Commission is a percentage of the salesperson’s annual sales. The current commission is 10% of total sales.
• The total annual compensation is the fixed salary plus the commission earned.

The Java™ application should meet these technical requirements:
Create a NetBeans project and name it XXXIA2. XXX is your last name. It is important to use your last name so that each student’s project name is unique. When your team evaluation the members’ program, the team will know whom the program belong to.  Your system can also load in multiple projects without conflicting project names. The number 2 in the file name is the academic week number.
• The application should have at least one class, in addition to the application’s controlling class (a controlling class is where the main function resides).
• There should be proper documentation in the source code.
• The application should ask the user to enter annual sales, and it should display the total annual compensation.

Because NetBeans produces multiple files for one project, the best method to submit an assignment is to zip the files. There should be one project folder created by NetBeans using your project name. Zip the folder. Submit your NetBeans project zip file.

PRG 420 Week 3 Simple Commission Calculation Program Part 2

Modify the Week Two Java™ application using Java™ NetBeans™ IDE to meet these additional and changed business requirements. Either a GUI (graphic user interface) program or non-GUI program is acceptable.
• The company has recently changed its total annual compensation policy to improve sales.
• A salesperson will continue to earn a fixed salary of $25,000.00. The current sales target for every salesperson is $120,000.
• The sales incentive will only start when 80% of the sales target is met. The current commission is 10% of total sales.
• If a salesperson exceeds the sales target, the commission will increase based on an acceleration factor. The acceleration factor is 1.25 which means all sales above the sales target will be earning 25% commission.
• The application should ask the user to enter annual sales, and it should display the total annual compensation.
• The application should also display a table of potential total annual compensation that the salesperson could have earned, in $5000 increments above the salesperson’s annual sales, until it reaches 50% above the salesperson’s annual sales.

Sample Table: Assuming a total annual sales of $100,000, the table would look like this:
Total Sales    Total Compensation
100,000    <<Program calculated value>>
105,000    <<Program calculated value>>
110,000    <<Program calculated value>>
115,000    <<Program calculated value>>
120,000    <<Program calculated value>>
125,000    <<Program calculated value>>
130,000    <<Program calculated value>>
135,000    <<Program calculated value>>
140,000    <<Program calculated value>>
145,000    <<Program calculated value>>
150,000    <<Program calculated value>>

The Java™ application should also meet these technical requirements:
 Create a NetBeans project and name it XXXIA3. XXX is your last name. It is important to use your last name so that each student’s project name is unique. When your team evaluation the members’ program, the team will know whom the program belong to.  Your system can also load in multiple projects without conflicting project names. The number 3 in the file name is the academic week number.
• The application should have at least one class, in addition to the application’s controlling class.
• The source code must demonstrate the use of conditional and looping structures.
• There should be proper documentation in the source code.

Because NetBeans produces multiple files for one project, the best method to submit an assignment is to zip the files. There should be one project folder created by NetBeans using your project name. Zip the folder. Submit your NetBeans project zip file.