Search results for 'GU'

Microsoft Access 2010 CHAPTER 5 Multitable Forms Camashaly Design AC 274 - AC 326

Project — Multitable Forms
Camashaly Design uses its database to keep records about clients and business analysts. After using the database for several months, however, Camashaly has found that it needs to maintain additional data on its business analysts. The company wants to identify those business analysts who have a professional certifi cate in business analysis, store the start date of each business analyst in the database, and include notes about each business analyst as well as the business analyst’s picture. Additionally, business analysts now maintain files about potential contacts. These files are separate from the database; some are maintained in Word and others in Excel. Camashaly would like a way to attach these files to the corresponding business analyst’s record in the database. Finally, Camashaly wants to add the Phone Number field to the Business Analyst Table. Users should type only the digits in the telephone number and then have Access format the number appropriately. If the user enters 7195558364, for example, Access will format the number as (719) 555-8364.

After the proposed fields have been added to the database, Camashaly wants users to be able to use a form that incorporates the Client and Business Analyst tables and that includes the newly added fields as well as some of the existing fields. The form also should include the client number, name, amount paid, and current due amount for the clients of each business analyst. Camashaly would like to see multiple clients on the screen at the same time (Figure 5 – 1). The database should allow users to scroll through all the clients of a business analyst and to open any of the attachments concerning the business analyst’s Client Notes. Finally, Camashaly requires queries that use the Certification, Start Date, and Comment fields.

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

IT452 Unit 5 Table Expressions

1. What is the average number of order line items over all orders? The Sales.OrderDetails table has one row for each product for each order. Each row represents a "line item" on a sales invoice for the orderid. Start by writing a query that returns the number (COUNT) of line items (rows) for each orderid. Modify that query to CAST the line item count as decimal (8,4). Then use that query as a derived table to get the average number of line items over all orders.
(Returns 9 rows)

2. Create a view named Sales.LOLJO_Orders that lists all the orderid values for orders placed by customer 'Customer LOLJO'. (Requires an Inner Join)
(Returns 10 rows)

3. Create a view named Sales.WhoSoldToLOLJO that lists the empid, lastname, and firstname of all employees who have sold to customer 'Customer LOLJO'. Execute a Select query to show the results of the view.
(6 rows)

4. Write a CTE to extract the employee with the most orders. Write the Outer query to display the EmployeeID and Highest Orders Sold Count for that Employee Result: (empid 4, 156 orders)

5. Create an inline table-valued function named Sales.fCustOrders that takes a customer name as an argument and returns the orderid and orderdate values for orders placed by that customer. The orderdate should have the date only, no time (Hint: CAST). Your project should have your CREATE FUNCTION statement. Query the function to see the results from the function for the argument (page 165) for companyname 'Customer MLTDN'.
(4 rows)

6. Create a VIEW called TopSales that shows the TOP 10 percent of sales. Verify the results of the view in a SELECT clause.
(216 rows)

7. Encrypt the TopSales View and bind the schema so that it cannot be dropped or altered created in #6. Test it by trying to remove the producID from the underlying Table and show the error

8. Change the TopSales view from # 7 so that it contains a filter that only has qty = 130 and does not allow modifications. Test it with an Update Statement by trying to change the quantity.

IT452 Unit 10 Final Project
In this project, you will create an end-to-end solution to meet a business need. This project is worth 200 points.

Scenario
Your good friend, Misty, has a tutoring business in Southern California. It is quite successful. A client will call with a tutoring need, and Misty will look on her Excel spread sheet of tutors for those qualified in the tutoring area the client needs, and who live near the client.
Misty now has approximately 20 freelance tutors registered with her. It has become quite difficult for her to tell what tutors are qualified and live near the client. You have told her you will develop a way for her to get such a list.
She has sent you the TutorList.xls file with the current list of tutors. (This file is available in Doc Sharing.) The Subject column has values of A (tutor is proficient in English and history), B (tutor is proficient in math and science), or C (tutor is proficient in all basic subjects). Hint: You need to create a database that consists of the data in these to files. The DateQualified column has the latest date the tutor was interviewed and checked out by Misty. In addition, she has provided the current term’s client list (Clients.xls). You will need to use BULK INSERT to load the data from the sheets into the tutoring database and write queries that find tutors in the same zip code as the client and provide the tutor in the same zip code in a report. Misty should be able to select the zip code as a parameter from the tutors in the report and the zip code as a parameter from the clients as a parameter to execute the reports.
Misty also requires that she should be able to interact with a Web page, because she does not want to know how to "run database software". This will require creating a web based report that she can access via a web link.
The first thing you should do is list the steps in your plan. What will be the major steps you need to take to accomplish your goal? The first step will be to install SQL Server 2008 Express with Advanced Services on Misty's computer. (She has agreed to that part of the plan.) The last step will be to create a report that asks for parameter values for a stored procedure or query at run time. This does not literally need to be done but should be a step in the plan if you advise her that it is needed on her machine.
For the purpose of this project, you will use your computer to work out the steps and create the report.
The first item to place in your final project document is your outline of major steps that need to be accomplished. Label this section:

1. Outline – 20 points
<Put your plan step list here.> (The outline will be worth 20 points.)
Then, for each step after the first (the installation step), make a section in your project, e.g.

2. <Whatever Step 2 is in your plan outline> - 80 points
In that section, state how you accomplished that step and/or give the code you used. Enough detail should be given so a knowledgeable person could completely recreate what you did. Show at least some of the results you obtained when you tested to see whether your step had been accomplished. (For
queries, 3 or 4 lines of output, and stating the number of lines returned is sufficient.) If a screen shot is the best way to demonstrate the results, include it in your project. Please use ALT+PrintScreen so that only the in-focus or active window, not the entire desktop, is copied.
[Note: You are starting with step 2, because step 1 of your plan is the installation, and it will be assumed that you know how to do that.]
[Hint: One of your steps should be the creation of a stored procedure that uses a variable for the zip code and determines which tutors live in the same zip code as the client. But, this step will be carried out in #3 below so you might want to letter your steps to avoid confusion.

3. Create the query or stored proc. that will produce the result set for the report - 40 points (Creation and Alter)
Create Procedure – 20 points
Alter Procedure – 20 points
The query / stored procedure that generates the result set for the report will be worth 20 points each. You then will also Alter the Stored Procedure one time and execute it. You should determine that it gives the desired results before configuring your report. Hint: The SELECT query portion of the stored procedure will be the query you use in your report datasource when you get to the portion of the design wizard (if you use the wizard instead of manually creating everything).

4. [The last step.] Configuring the Report and the Report Parameters – 60 points
Query for Report 20 points
Configuring Parameters – 20 points
Configuring/Designing 2 reports – 20 points
The final 60 points will be for the last step in your plan – configuring the report and the parameters for the report. The tutor subject choices should be A, B, or C. The date selection should be from the DateQualified values, with a default value of the earliest DateQualified. The customer zip code must be one of those in the TutorList. [Hint: The latter two parameter items require creation of additional data sets, one for the DateQualified values, and one for the zip code values. Hint: Each of the datasets should use a SELECT query as well to provide the data for the parameter. Look at what you did in Project 9 as an example. The DateQualified data set will have datetime data type. Each parameter will become a drop down on your report.
You should place four screen shots under this final step in your project document.
1. For the parameter that specifies Misty's choice of a tutor that resides in the same zip code as the client show one such client/tutor example.
2. For the parameter that specifies Misty's choice of minimum DateQualified, show how you have configured the Available Values for that parameter.
3. Show the Report Data pane of BIDS, showing all parameters and all data sets with their fields.
4. Deploy the report. Run the first report that does not contain parameters for zip code 91016. This one should show only the tutors available for that zip code. Run the second report with parameters for a customer zip code of 91803 for the report with parameters showing which client they qualify to work with, what their skill set is, and date equal to the earliest DateQualified value. Paste the screen shot of this report into your project document.
Because the entire project involves sequential steps, if you become hung somewhere in the middle and cannot proceed, you may send details to your instructor, and receive a hint in exchange for an appropriate point "cost" or deduction. If you cannot figure out what query you need to create the report, the instructor will send it to you, on request from you, for a "cost" of 20 points so you can move on.
Your screen shot of the report without parameters should look similar to this on the Report BIDS screen. It should be for ZipCode 91016 regardless to subject and qualified date:
Your screen shot of the report without parameters should look similar to this on the Report Manager screen:
Your screen shot of the report with parameters should look similar to this on the Report BIDS screen It should be for ZipCode 91803 regardless to subject and qualified date:
Your screen shot of the report with parameters should look similar to this on the Report Manager screen:

PRG 421 Week 1 Hello World Program

Design, implement, test, and debug a GUI-based version of a “Hello, World!” program.
Create a JFrame that includes a JLabel that reads “Hello, World!” Use a layout manager of your choice.
Include an Exit button to close the program.
Submit the .java source file.

PRG 421 Week 2 GUI Components Program

For this assignment, you will choose from the following options:
Option 1: Retail Calculator
A retailer would like a calculator to indicate sale prices when he marks items down at various percentages.
Design, implement, test, and debug a GUI application that allows the retailer to enter the item’s name, the item’s original price, and the percentage discounted.
• The application should include a Calculate button that, when pressed, computes and displays the sale price in a label or text field.
• The program should allow the retailer to enter any number of items in this manner.
• Include an Exit button to end the execution of the application.
• The program should allow the user to select a department from a menu. You may create the menu as a true menu, a JComboBox, or radio buttons.
• Create your own list to populate the menu: men’s clothing, women’s clothing, house wares, and so forth.
• Include at least five departments.
• Create a JTextArea or a JTable to display each of the following for each item entered:
o Item name
o Department
o Original price
o Sale price

Option 2: Payroll Application
An employer is looking for an easier way to track employee pay.
Design, implement, test, and debug an application to compute an employee’s weekly pay. If the employee works more than 40 hours a week, the employee earns 1.5 times the basic hourly pay for any time worked over 40 hours. The program should allow the user to input the employee’s name, the number of hours worked, and the hourly rate.
• The application should include a Calculate button that, when pressed, computes and displays the total pay in a label or text field.
• The program should allow the user to enter information for any number of employees in this manner.
• Include an Exit button to end the execution of the application.
• The program should allow the user to select the employee’s department from a menu. This can be a standard menu, radio buttons, or a JComboBox.
• Create your own list to populate the menu: Marketing, IT, Accounting, and so forth.
• Include at least five departments.
• Validate the employee’s hourly wage and hours worked according to the following rules:
    The hourly wage must be at least $6 per hour and cannot be more than $150 per hour. It must be numeric.
    The hours worked must be a numeric value greater than or equal to 0 and cannot be greater than 60.
• Create a JTextArea, JTable, or other form of a table to your application and use that to list the following for every employee:
o Name
o Department
o Weekly salary
Add each employee to the list after you have computed that employee’s salary.

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.