Search results for 'eco'

DBM 405 Lab 6 Reading and Writing to External Files

Scenario/Summary
This week, we are going to make a change to the processing in our schema by altering one of the procedures contained in the package previously created. At this point, we have dealt with procedures that required us to pass parameter values each time the procedure was executed. This type of processing greatly limits the ability to process large amounts of data efficiently. The lab this week will introduce you to a couple of the procedures contained in the UTL_FILE package that is part of Oracle's group of built-in packages. By using the GET_LINE and PUT_LINE procedures contained in UTL_FILE, you will be able to process multiple records from a file and create a results report on the events performed by your movie rental procedure with one call to execute the procedure.
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. You will only need to recompile both the package specifications and the package body since you will be making a change to the movie_rental_sp procedure in the specifications. You will be running tests to verify that the changes to your procedure are working as they should. Spool your output and name your files with your last name plus lab 6 and give the file a text (txt) extension. For example, if your last name was Johnson then the file would be named johnson_lab6.txt. Submit both the spooled output files AND the script file for grading of the lab.
IMPORTANT: Before beginning the lab, you will want to refresh the tables in your schema by running the movierental.sql script.

LAB STEP
Step 1: Setting Up Your Environment
Before you can effectively use the procedures in UTL_FILE to work with external files for this lab, you will need to set up your environment. The first step of this process will involve creating a directory folder under the R: drive which is the mapped drive for DBM405. The second part of the process will involve creating the Directory Object in Oracle to point to this folder. To accomplish this task, please follow the steps listed here.
1. Create the directory folder (You can refer to the SQL*Plus Tutorial in Week 1 for visuals of this process).
•    First, log into the iLab and open up your Oracle folder. Next, open Windows Explorer and expand the R: drive tree.
•    Under the R: drive, create your own folder by selecting File/New/Folder from the main menu bar. Name your folder using a unique name such as DBM405_lastname_lab6 or some other unique name. Make sure that there are no other folders under the R: drive with the same name as yours. You are the only person who has access to your folder, but the name must be unique.
2. Create the Directory Object.
•    In Windows Explorer (the one in the Oracle folder in the iLab), you want to take note of the path name for the R: drive. It will be DBM405\ followed by the session you are in. For example, if you are in Spring A, the path would be DBM405\SPRINGA, or if Spring B, it would be DBM405\SPRINGB. This is important because this will be used to define the path for the Directory Object. Remember that the R: drive is really mapped to the Oracle server F: drive.
•    Start up a session in SQL*Plus and log into your user schema.
•    Use the following SQL command to create the Directory Object that you will use for this lab.
Create or Replace Directory DBM405_YOURNAME_DIR As 'F:\DBM405\session\YourFolderName';
IMPORTANT: Replace the word YOURNAME with your actual last name, the word session with the session name found in the path for the R: drive (see first bullet above), and the word YourFolderName with the name of the folder you created in Part 1 above. For example, if your last name were SMITH and you were taking this course is Spring session A and the folder you create was named DBM405_SMITH_lab6, the statement would be:
Create or Replace Directory DBM405_SMITH_DIR As 'F:\DBM405\SPRINGA\DBM405_SMITH_LAB6';
3. Set up the data file.
•    Download the file "movierentaldata.txt" from Doc Sharing and place this file in the directory folder you created above in Part 1.
You have now set up Oracle to work with the procedures in UTL_FILE that we will use for this lab.

Step 2: Changing the Package Specifications
We are now ready to start updating the package that we modified in Lab 5 so that it will allow us to read a file and make changes to the database. To do this, we will need to make changes to the parameter list of the MOVIE_RENT_SP procedure to reflect a new process. Since we will be changing the parameter list, we will need to make changes to the Package Specifications as well as to the Package Body.
For the change to the Package Specifications, you will need to define a parameter for the Directory Object, one for the input file, and one for an output file that will be used for a verifications report. Each of these parameters can be defined as VARCHAR2 data types.

Step 3: The DECLARE (IS) Section of the Procedure
Now, we can move on to the Package Body and the MOVIE_RENT_SP procedure itself. The first thing you want to do is make sure that the parameter list in the procedure matches the parameter list that you just changed in the Specifications in Step 2. The two parameter lists must match exactly or you will get errors when trying to compile the Package Body.
When you read in the file, you will need several variables to handle both the record and the various pieces of data in the record. When the record is read in, you will need to substring each piece of data from the record into a variable which then can be used like you were using the parameters before. To keep from having to make numerous changes in the code, you might want to consider using the actual parameter names from your parameter list in the code from Lab 5. For example, if the parameter name for the movie ID was P_MOVIE_ID then you would create a variable named P_MOVIE_ID. Each of the variables for movie ID, member ID, and payment method can be cast to the data type in the MM_RENTAL table. For example:
p_movie_id mm_rental.movie_id%TYPE;
You will also need a variable to hold the record data that will be read in and a variable to hold record data that will be written out. Check the length of the data in the movierentaldata.txt file to determine the length for the input data variable. For the output data variable, you can use your own judgment, but using VARCHAR2(80) would probably be more than adequate.
The last thing you will need to add is a variable to act as the handle for the input record and one for the output record. Remember that the name for these is not important, but they must be based on the FILE_TYPE data type.

Step 4: Opening the Files
Now, we move to the body of the code and the BEGIN section. The first thing that needs to happen is to open both the files. Remember that we are going to set up our processing just like we would any IPO logic, i.e., we need to open the files, read a record, process the data, read the next record, and repeat this process until there is no more data.
To accomplish this first step of opening the files, you will need to initialize the two variables you declare to handle the files. For the input file, you will need to use the FOPEN program. Remember that you have to pass this program three variables: the name of the parameter for the directory object, the name of the parameter for the input file, and the mode the file is to be in. For the input file, the mode needs to be "r", and for the output file, the mode needs to be "w".
An example of what your code might look like can be found in the lecture material for this week.

Step 5: Setting Up the LOOP
Since our code in its current format is designed for only one set of input values (remember that in previous labs we were using an EXECUTE command and then passing data to the procedure) and we now want to process multiple records of data, we need to make a change to the overall structure of the code. We need to put all of the processing in the body of the procedure after the files are opened into a basic LOOP using the LOOP/END LOOP commands. Also, we will need a new BEGIN section inside this LOOP that all of the existing code will go into. The basic structure of body of your code should look like the following when you complete this step.
BEGIN
open files
LOOP
BEGIN
file processing
BEGIN
existing process to increment rental_id
EXCEPTION
END
EXCEPTION
END
END LOOP
END

Step 6: Setting Up the File Read and Process
Now, we need to get our data record so that we can process the data brought into the program. To do this, we need to read a record using the GET_LINE program in UTL_FILE. This involves using the UTL_FILE.GET_LINE process while passing the file handle variable and input record variable to the GET_LINE program.
Once the record is in the input variable, we then need to parse out the individual pieces of data (movie ID, customer ID, and payment method) from the record using the SUBSTR function. Remember that the parameters for the SUBSTR function are the record variable name, the value for the first byte of data we want to pull, and the length of the data. For example, to get the movie ID from our record, the code might look like the following.
p_movie_id := SUBSTR(v_rental_record, 1, 2);
This would pull two bytes of data from the data record and put them in the variable p_movie_id. Now we can use the variable in our code that follows to see if that movie ID exists in the mm_rental table. You will need to repeat the process above for the member ID and the payment method. Do all three together between the line of code that reads the file and the first SELECT statement.

Step 7: Setting Up the Output Process
If you have used the same names for your three data variables that you used for the original parameter names in the previous lab then you should not have to make any changes at all to any of the SELECT statements or the INSERT statement in the main body of code. Now, we need to set up the processing that will create the output data that will go into our output validation record. To do this, you will be using the PUT_LINE program within UTL_FILE.
There are going to be six different places you will want to write out a record. The first will be after the insert statement when a new rental record is added to the mm_rental table. To create this output line you can concatenate variables and character strings together to create the data record. For example, to create an output line that would read "Rental record 13 for member 10 has been added" after the new record has been inserted, you would use code similar to the following (keep in mind that your variable names might be different).
v_report_record := 'Rental record '||v_rental_id||' for member '||p_member_id||
' has been added';
UTL_FILE.PUT_LINE(v_report_filehandle, v_report_record);
This same type of process and format will need to be repeated for each exception handler that in the previous lab used the DBMS_OUTPUT.PUT_LINE package and procedure. In these cases, your output line is already formatted and set, you just need to replace the DBMS_OUTPUT.PUT_LINE with the initialization of your output record and then add the UTL_FILE.PUT_LINE code.

Step 8: Getting Out of the LOOP and Closing the Output File
There is one final step that has to be taken care of before you can start testing your code. Since we are reading a record file within a LOOP we need to be able to EXIT out of the loop after the last record has been processed. To do this you will need to add a NO_DATA_FOUND exception to your main exception handling section. This exception handle must be added before the WHEN OTHERS exception handle (remember, that on has to be last). This exception handle will have three pieces to process.
The first will be some form of message stating that all the records have been processed and this is the end of the report, i.e., ALL RECORDS PROCESSED - END OF REPORT. The second piece will be a line that will close the output file using UTL_FILE.FCLOSE. The only parameter you will need to pass is the name of the output file variable. This must be done or your output file will not have any data in it. The third thing will be the word EXIT which will tell the program to exit out of the loop.
You are now ready to compile your package specifications (must be done first) and then the package body. Debug any errors you might have and then run your test.

Step 9: Testing Your New Procedure
For this lab, you only need to test the MOVIE_RENT_SP procedure of the package. To do this, enter an EXECUTE command for the package.procedure and pass the name of your Directory Object, input file name, and output file name to the procedure. IMPORTANT: The Directory Object name MUST BE IN UPPERCASE! This is the way it is stored in the data dictionary in Oracle and if it is not in uppercase, an error will be generated. Your call to the new procedure should look similar to the following.
execute MM_RENTALS_PKG.movie_rent_sp('DBM405_SMITH_DIR','movierentaldata.txt','rentalreport.txt');
The output in your output file should look similar to the following (the wording may be different but the processes recorded should be the same).
There is no movie with id 13. Cannot proceed with rental.
There is no member with id 20. Cannot proceed with rental.
There is no payment method with id 7. Cannot proceed with rental.
Rental record 13 for member 10 has been added.
Movie id 5 is not available at this time. Cannot proceed with rental.

This concludes the Lab for Week 6.

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.

POS 410 Week 2 Table Queries SQL for Business

Create two tables using the specifications listed.
• Use the database built in last week’s assignment to create the two tables and associated columns listed below.  Table names, data types, and column lengths must be used as specified; column names may be created as desired and appropriate.  Avoiding the use of spaces and special characters in the column names is recommended.
• In the Employee table, create an Employee ID column that generates a unique number for each employee and designate the column as the Primary Key. In the Job table, use the job title column as the table’s primary key.

Table Name = Employee_Tbl
• Employee ID (type= numeric identity) PK
• Last name (type=varchar(30))
• First name (type=varchar(30))            
• Address (type=varchar(30))
• City (type=varchar(30))        
• State (type=varchar(2))
• Telephone area code (type=varchar(3))            
• Telephone number (type=varchar(7))
• Employer Information Report (EEO-1) classification                  (type=varchar(30))
• Hire date (type=date)
• Salary (type=money)
• Gender (type=varchar(1))
• Age (type=numeric)
• Job Title (type=varchar(30))

    
Table Name = Job_Tbl
• Job title (type=varchar(30)) PK    
• Job description (type=varchar(2000))
• Exemption status (type=varchar(1))

Use the SQL INSERT statement to go to the human resources department in the Kudler Fine Foods Virtual Organization. Using information found in the employee files for the La Jolla and Encinitas stores, enter records into the Employee table for the following employees:
• Glenn Edelman
• Eric McMullen
• Raj Slentz
• Erin Broun
• Donald Carpenter
• David Esquivez
• Nancy Sharp

Use the information from the job classifications and descriptions to enter records into the Job Title table for the following titles:
• Accounting clerk
• Assistant manager
• Bagger
• Cashier
• Computer support specialist
• Director of finance and accounting
• Retail assistant bakery and pastry
• Retail assistant butchers and seafood specialists
• Stocker

Hint: Data entered into the job title column of the employee_tbl and job_tbl tables must match exactly, including case, spaces, and so on.  If they do not match exactly, you will not be able to create the foreign key or perform the joins that are required in later assignments.
Deliverables:  check your results by selecting all of the data from both tables (one table at a time).  Copy and paste screen images of your work in a word doc, then post as an attachment in your assignment tab of the classroom.  You may need to resize your windows and take multiple screen shots to capture all of the required information.  The screen images should show the following information:
• Database, table, and column definitions
• SQL statements used to load the data
• Data stored in both tables (display contents after data is loaded)
Refer to the materials forum for the rubric that will be used to grade this assignment.

POS 410 Week 3 Data Changes and SQL Statements SQL for Business

Use the Kudler_FF database to build a foreign key.  The foreign key should be built on the job title column in the employee_tbl, which references the job title column in the job_tbl.  An “alter table” statement will be required to build this FK on the employee_tbl.

Deliverable: Copy (screen image) the SQL statement used to build the foreign key and the associated output message from its execution into a Word document and post them to the assignment tab of the classroom.

Use the Kudler_FF database to write SQL statements and enter records into the Employee table for workers identified in the employee files for the administrative offices and the DelMar store. Check results by selecting all columns from both tables.

Deliverable: Copy (screen image) the SQL used to check your results and the output to a Word document and post to the assignment tab of the classroom.  

Use the tables in the Kudler_FF database to write the following SQL queries:
o Write a SQL query that joins the two tables in the Kudler_FF database, and displays the Employee Last Name, First Name, Hire Date, Area Code, Job Title, and Exemption Status.
o Write a SQL query that joins the two tables in the Kudler_FF database and uses BETWEEN to restrict record selection; use hire dates to restrict data.
o Write a SQL query that joins the two tables in the Kudler_FF database and uses LIKE to restrict record selection; use telephone area codes to restrict data.
o Write a SQL query that joins the two tables in the Kudler_FF database, and restricts the record selection to an exemption status.
o Write a SQL query that uses the UNION of the two tables to produce the results set

Deliverable: Copy (screen image) each query and the results set to a Word document and post them to the assignment tab of the classroom.  

Refer to the materials forum for the rubric that will be used to grade this assignment.

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.

CMIS 420 Project 2 Advanced Relational Database Concepts and Applications

1. Description:
Demonstrate your knowledge of PL/SQL programming by writing and thoroughly testing triggers and stored procedures associated with an e-commerce application that provides security logs for all transactions by user, product, and date. I will provide specific requirements and design details for this project below and we could have more discussion about the project in the Conferences area. Submit the scripts with all of your function SQL and PL/SQL code, and provide the results of running your scripts with the SQL*Plus spool command.

2. Functional requirement:
We will develop a small online transaction application supported by our database, movie distributing and renting system. In the application, we should meet three functional requirements:
1) Track transaction events, and track runtime errors.
2) Handle either movie distributing business process.
3) Application interface which allows users to process either orders.
4) No additional database tables are required unless you think it is necessary to enhance your application.

3. Track transaction events, and track runtime errors:
The link below provides a script which allow you to create two tables for tracking purposes One table for tracking events and the other for errors.
Script to create logs tables
Track errors or exceptions during runtime.
Track any error or exception occurs during your application execution. this means that you should include tracking code in every program unit you develop.
Take a look of the table for error logging and see what data should be inserted into the table when error occurs. The code should be part of error handling in exception handle section.
Track event or application process during runtime.
This type of logs for recording the events during the code execution. Content of the log may contain part of the data being changed and who changed it. The code to track events should be located at the end of a process.
Take a look of the table for event logging and see what data should be inserted into these tables after event occurs.

4. Handle movie distributing business process.
In this part of the project, you are asked to design a few stored procedure or functions to handle online movie distribution.
These procedures or functions should be able to
1)process orders for distributors. When a customer (movie store) places order, ordering information should be stored in database tables.
2)allow customers (movie stores) to check movie availability. if it is available, provide number in stock and unit price. If unavailable message the customer the movie requested is not available.
3) if the order has been filled, generate an invoice to send to the customer.
Note: This is one of the place where tracking event is necessary.
Errors and events should be both tracked as described before.

5. Application interface which allows users to process either orders:
In general, the interface should provide GUI to users. However, PL/SQL does not have that feature. What we are going to do is to develop script which performs the similar functionality.
Script should be able to
1) take an order (ordering data)
2) place an order by calling all the procedures used to process order.
3) generate and print invoice based on order id.
4) query event_logs and error_logs to show the result of the order transaction and/or any potential issues within the order.

6. Submit requirement:
The scripts to create procedures and function.
The script to execute the procedures to place orders
The text file for output of execution results.

CMIS 420 Final Project Advanced Relational Database Concepts and Applications

1. Description:
In the final project, you will demonstrate your ability to design, build, and populate a small data warehouse to support decision-making, business modeling, and operations research. Using data supplied by me, prepare a star schema and several queries that support decision-making. Submit the following deliverables:
1) SQL scripts showing the design, loading, and queries of the database.
2) results of running the script with the SQL*Plus spool command.
3) presentation to your fellow classmates describing the data warehouse, its design, and possible uses and applications for critical business decisions.

2. Tasks to be completed:
Based on our operational database, movie distributing and renting system (focus on movie distribution and sale), build a data mart (star schema) which can answer the following questions:
1) Which movie and\or movie category have the better sale by months and\or quarters?
2) Which distributor had best or better earnings (suppose profit margin is 12% for all the distributors) by months and\or quarters??
3) Which store should be the better sale target for movie sale?
The following link provides zip file containing a script to create a PL/SQL package which adds ordering records to your database. and another script to execute the procedures in the package to complete the tasks. At the end of execution, you will expect 500 to 1000 records in orders table and 3000 to 6000 records in order_items table. These data plus the existing data would serve you as operational data for your data warehousing project. The data in your dimension and fact tables should be derived from this database.
Run create_package.sql first, and then run_all.sql to execute the procedures in the package.

Create a database using Microsoft Access.

The database should contain three tables: Customers, Products, and Transactions; three queries: list of customers, transactions by customer, and transactions by product; and be something that a small business, such as a jewelry store or used car lot, can use for its daily needs.

The database must contain an input form for the transactions and one report that is based on any of the queries; the tables must each contain 10 records; and the records in the transactions table must be created by way of the input form.

In this assignment, use Microsoft Access to create the following: Forms Tables Queries Reports