DMIT Investors Inc
Now that you finished developing the database backend, it is time to design and develop a front end for the application. You will create forms and a menu to allow users to access your database. Listed below are the requirements for the forms and menus.
Please be aware that the appendix is an integral part of the lab requirements.
Please do not put your name or any other identifying information on your forms.
NOTE: Prior to starting Lab 2, ensure your Lab 1 tables are correct. You will also need to drop all procedures, functions, packages, and database triggers associated with your Lab 1 tables.
P => Form Property, T => Form Trigger
1)Source Code / Executables / Submission 5 marks
Each form will be in its own separate file
Each data block will be based a single table, do NOT use table joins or views when creating a data block!
Note: master-detail relationships are not considered table joins
All multi-line forms MUST have all the data from one record on a single line
Create all your forms and menu in the OracleDocs\Marking directory
When you are ready to start fully testing your lab: leave the executable files in OracleDocs\Marking, move all your source code files to the OracleDocs\Source directory, open an Internet Explorer browser, and type the following URL:
When you are done your testing, submit your lab via Moodle as follows:
Copy your executable files to “Lab 2 Marking Files Submission”
Copy your source code files to “Lab 2 Source Files Submission”
Ensure you only submit your final Lab 2 files (i.e. No classroom, test files or versions of Lab 2) and do not compress any files
Email your instructor a discussion about the lab including, but not necessarily limited to, the following:
What you liked and/or disliked about the lab
How long the lab took to complete (divided into “in class” and “out of class” time)
Any significant problems you encountered
Whether there was enough material provided in the lectures to complete the lab
What, if any, changes, or improvements could be made to the lab
Anything additional as specified by your instructor
2)
Investor Maintenance Form 15 marks
This form will be used to insert new investors as well as query and update, but not delete (P), existing investor information
Name this form Lab2_Q2 (P)
The form requirements are:
Display all fields from the investor table (all in one block) (P)
Display one record at a time in a form-style layout (P)
Ensure the postal code field is entered in upper case (P)
The province will be displayed using a list item displaying province names, not abbreviations (P)
Valid province values will be the thirteen Canadian provinces and territories (P)
The default province will be Alberta (P)
Users must not be able to navigate to the investor number field unless the form is in query mode (P)
The investor number will be generated from the sequence Investor_Seq when a new record is saved (T)
Attach a list of values (LOV) to the account number field that will display and populate the account number field (P)
Do not allow an invalid account number to be entered (P)
3)Exchange Stock Maintenance Form 15 marks
This form will be used to insert new exchange stock records as well as query, update and delete, existing exchange stock information
Name this form Lab2_Q3 (P)
The form requirements are:
Display the exchange code, exchange description, stock code, stock name and active status (all in one block) (P)
Display four (4) records in a tabular style layout with a scrollbar on the left side of the information (P)
A check box, with an appropriate and unambiguous label, will be used to display the active status (checked implies inactive; cleared implies active) (P)
Attach a list of values (LOV) to the exchange code field that will display and populate the exchange code and exchange description fields (P)
Do not allow an invalid exchange code to be entered (P)
Attach a list of values (LOV) to the stock code field that will display and populate the stock code and stock name fields (P)
Do not allow an invalid stock code to be entered (P)
Users must not be able to navigate to the exchange description or stock name fields (P)
Ensure that the exchange description and stock name fields are populated when records are retrieved (T)
Form must open with all records retrieved (T)
4)
Portfolio and Transaction Maintenance Form 35 marks
This form will be used to insert new transactions as well as query, update and delete existing portfolio and transaction information
Name this form Lab2_Q4 (P)
The form requirements are:
Portfolio Information (all in one block)
Display the portfolio number, portfolio description, investor number, investor’s first and last names (as one field) and the investor’s email address (P and T)
Users must not be able to navigate to the first and last names or email address (P)
You can assume the user will always enter valid portfolio and investor numbers
Display one record at a time in a form-style layout (P)
Transaction Information (all in one block)
Display the stock code, stock name, transaction date, exchange code, exchange description, broker number, buy sell flag, quantity, and price per share (P)
Display three records in a tabular style layout with a scroll bar (P)
Add an extended price (quantity times price per share) field for each record showing negative numbers for purchases (B) and positive number for sales (S) – Hint: use of a hidden field makes this easier (T, or P and T)
Add an extended price grand total for the transactions in each portfolio (P and/or T)
The user must NOT be able to navigate to the stock name, transaction date, exchange description, extended price, or extended price grand total fields (P)
Ensure the time portion of the transaction date is visible (P)
Have the transaction date AND time automatically populated with the current system date when a new record is saved (P and T)
Attach a list of values (LOV) to the stock code field that will display and populate the stock code and the stock name (P)
Do not allow an invalid stock code to be entered (P)
Attach a list of values (LOV) to the exchange code field that will display and populate the exchange code and the exchange description (P)
Do not allow an invalid exchange code to be entered (P)
Attach a list of values (LOV) to the broker number which displays the first and last names and populates, but does not display, the broker number (P)
The “Buy/Sell” flag will be displayed using radio buttons (P)
Have the stock name field filled correctly when:
An existing transaction is retrieved (T)
A new stock code is entered, whether entered manually (T) or by using the LOV (P)
An existing stock code is changed (T)
Have the exchange description field filled correctly when:
An existing transaction is retrieved (T)
A new exchange code is entered, whether entered manually (T) or by using the LOV (P)
An existing exchange code is changed (T)
Have the extended price field filled correctly when:
An existing transaction is retrieved (P or T)
A new price per share or quantity is entered (P or T)
An existing price per share, quantity or Buy/Sell is changed (P or T)
Navigation
In order to make transaction entry easier set properties so that the following occurs:
When the user tries to navigate (tab) to the next field from the last field in the portfolio block, the focus will move to the transaction block (P)
When the user tries to navigate (tab) to the next field from the last field in the transaction block, the focus will move to the next record in the transaction block (P)
5)Investor Portfolio Information Form 15 marks
This form will be used to display investor and portfolio information
Name this form Lab2_Q5 (P)
The form requirements are:
You must utilize multiple canvases on this form
If you use a stacked canvas, you must have functional show and hide buttons (T)
If you use a tab canvas, you must have data on multiple tab pages (i.e. one block per tab page) (P)
You may use a content canvas if you choose (P)
You will need to display the following information:
Investor Information (all in one block) (P)
Investor number (P)
First name (P)
Last name (P)
Account number (P)
Account balance (T)
The number of portfolios the investor has (T)
Portfolio Information (all in one block)
Portfolio number (P)
Portfolio description (P)
A field to display each portfolio’s total transaction value (quantity * price per share of Sells minus Buys) (T)
A field to display each portfolio’s total transaction count, including both Sell and Buy transactions (T)
The user must be able to query but must NOT be able to change any data! (P)
Add an informational alert to notify the user when an investor is retrieved that does not currently have any transactions (T)
If you chose to have the default buttons appear on a canvas, ensure that the SAVE button will either be permanently disabled or not displayed (your choice)
6)Opening Form 15 marks
All forms and reports will be able to be run from this form
Name this form Lab2_Q6 (P)
This form includes command buttons AND a drop-down menu
A form opened with the drop-down menu will operate in the same manner as if the form was opened by pressing the corresponding command button
Buttons
Investor Maintenance Form – open so the user can navigate between (and operate) the opening form and this form (T)
Exchange Stock Maintenance Form – open so the user can navigate between (and operate) the opening form and this form (T)
Portfolio and Transaction Maintenance Form – open so the user may not operate any item on the opening form without closing this form (T)
Investor Portfolio Information Form – open so the user may not operate any item on the opening form without closing this form (T)
Stock Exchange Report– provide a simple message (including the report name) to the user that this report is currently unavailable (T)
Investor Portfolio Report – provide a simple message (including the report name) to the user that this report is currently unavailable (T)
Exit (P or T)
Menu (attached to the Opening Form): (P)
Forms Reports
Investor Maintenance Stock Exchange
Exchange Stock Maintenance Investor Portfolio
---------------------------------------------- ------------------------
Portfolio and Transaction Maintenance Exit
Investor Portfolio Information
----------------------------------------------
Exit
Ensure that you include menu separator items (as indicated by the dashed lines) (P)
A form opened with the drop-down menu will operate in the same manner as if the form was opened by pressing the corresponding command button (P and/or T)
Appendix
The customer likes variety so change the standard screen colours to something completely different than the standard Forms colour (P)
All forms can be the same colour, or all different colours, your choice
Ensure the values in all attributes are completely displayed (height and width) with the exception of stock name and exchange description (height only) on Form 4 (Portfolio and Transaction Maintenance) (P)
Each form will have its own menu (unless otherwise specified, Oracle’s default menu is required)
The entire form must be visible upon opening
The close button on the form’s title bar will close the form regardless of what mode the form is in (P and T)
The “Exchange Stock Maintenance” and “Portfolio and Transaction Maintenance” forms will have the following standard buttons:
Next Record – moves focus to the next record (T)
Previous Record – moves focus to the previous record (T)
Query (T)
if the user is in normal mode, pushing the Query button will work the same as pressing the standard Enter-Query button
if the user is in query mode, pushing the Query button will work the same as pressing the standard Execute-Query button
Clear – clears the form (T)
Save – saves all pending changes (T)
Exit – closes the form (T)
You may add the standard buttons to all your forms if you choose
Do not allow the user to minimize the window (P)
All objects are to be named appropriately (i.e. “Window1” is not appropriate) (P)
You will use the following item types appropriately at least once in this lab: text item, display item, list item, check box, radio group, list of values displaying at least two fields, list of values including at least one displayed field and one hidden field
Lab Expectations:
All monetary amounts will be displayed with a dollar sign, commas, and at least one digit to the left, and two digits to the right, of the decimal place
All dates will include an easily identifiable month, day and four-digit year
All code must be executable from within the NAIT Oracle VM environment
Anything additional as specified by your instructor