Coursehero >> California >> San Diego State >> CS 514

Course Hero has millions of student submitted documents similar to the one below including study guides, practice problems, reference materials, practice exams, textbook help and tutor support.

Assignment Programming 3 CS514 Fall 1999 This assignment is due Monday November 8 at 6:30 p.m. Late programs will not be accepted. The project has some more SQL queries and also covers the basic ideas in designing a form in ORACLE. I. Forms in ORACLE - an overview. We have seen how some queries could be done by embedding SQL in 3rd generation languages. The procedural aspects of 3rd generation languages are natural in a batch environment but an interactive approach requires a 4th generation approach. For that ORACLE has a number of graphical development interfaces which run as clients of the sql server. These include Forms, Reports, Procedure Builder, Graphics all of which are part of developer/2000. Developer/2000 runs as a client on Sun Solaris, Sun OS, Windows95, Windows NT, Mac OS and others. These gui interface components are object oriented. One of the benefits of this is that the application programmer is able to let the appropriate component do most of the work by guiding the programmer through various templates. We will concentrate our efforts in forms 4.5. Forms has three separate components - forms designer, forms generater, and runforms. The designer part is used by the application programmer to develop the application. This consists of defining various fields on the screen with properties which control how the cursor, and data enters or leaves the various fields. These fields may have queries attached to them so that data(from a select) automatically enters the field. The primary purpose of the form is to allow users easy access to the data in the database in a controlled environment. This happens when running the form. Typical nontechnical users will not be accessing the data by writing SQL queries. The generation part of the process is like a compile so it comes between the designing and running of the form. A form can have one or more modules consisting of one or more blocks each of which may be connected to one database table. Blocks can also be control blocks which are not connected to a table and may be used for displaying things like custom menus and help. A block is made up of items which can relate to a table column or be hidden or be display-only. This is a hierarchical structure. Modules consist of other objects such as triggers, alerts, attached libraries, program units in addition to blocks. Blocks may consist of triggers and relations in addition to items. When starting forms designer the object navigator window appears which allows user navigation among the various blocks, items, triggers, etc. Initially this is a template. As the forms designer continues to work the individual objects shown in the navigator window get names and become more customized (by changing their properties). When the module is saved as a file it is saved in the working directory of the client machine as a .fmb file. The .fmb is a forms binary file and is platform independent (much like a source file written in your favorite programming language is platform independent). When the module is generated it is stored as a .fmx file which is platform specific. In addition to the object navigator Oracled Forms Designer has other windows that may or may not be present at any time such as layout editor, properties window, and PL/SQL editor. These windows serve specific functions and and can be manipulated like any windows according to the individual platform being used. The Designer submenus at the very top of the Designer window are somewhat platform specific. These include Edit, Tools, Help etc. At the very bottom of the Designer window is a message bar which contains various status indicators such as <Con> indicating that Designer is connected to the server. The object navigator shows a list of entries called nodes. A node represents an object type such as a Form, Block, Item, or Trigger or a a user-created object instance of one of these object types which the user names. These nodes are preceded by a + or -. A + icon comes in two types. One of these indicates no instances of this type (grey) and the other indicates instances of this object type exist (blue). When an instance exists, a click on the + will expand the node and the + icon is changed to a -. Clicking on a - collapses the nodes. To create an instance of an object type one selects (with a mouse click) the object type and then clidks the create button on the button bar on the left. The create button has a green plus on it and is about two-thirds of the way down the bar. Any instantiated object can be eliminated by selecting it and clicking the cut button (the one with the scissors) on the button bar. A trigger is custom code that the programmer creates to make SQL*Forms perform a particular task such as to automatically display associated information when the user enters data into some field. The trigger's action is defined by slightly modified SQL statements. For example, SELECT prod_name INTO :company.prod_name FROM product where prod_number = :order.prod_number. Screen field names start with a colon. The INTO clause specifies the destination field of the value retrieved from the table. A trigger has a name which indicates when Forms will activate it. There are more than 30 of these names and include POST-CHANGE (it is activated whenever the field value it is associated with changes), PRE-FIELD (it is fired when the field is entered), and ON-ERROR ( it fires whenever Forms would normally cause an error message to display). A trigger can be associated with a field, a block, a key or the form itself. The programmer can also define his/her own trigger. A form-level procedure contains a set of SQL-like processing commannds that can take arguments and return values. They are typically called by a trigger or procedure associated with the same form. These are general routines that can be used many times. There is a large number of packaged procedures (ie a library of procedures) for commonly used tasks such as CLEAR_FIELD (clears the current field), EXECUTE_TRIGGER (executes a user-named trigger), and GO_BLOCK (navigates to the indicated block). II. Creating a Simple Form. Let us assume that we need a form that will list all of the employees of a certain department together with their salaries and the average salary of those employees. If we didn't need the average this would be done in SQL with the query select dname, ename, salary from dept, employee where dname=dept order by dept asc, salary desc; It is unneccessary to duplicate the department for each employee but this is hard to eliminate in SQL. We first construct a form which doesn't compute the average and later we will add the average. In Forms we require two tables and a join condition. The tables each correspond to a block based on that table. We will create two block instances and give them the same name as the table on which they are based. The join condition corresponds to a master-detail relation and will be an instance of the object type Relations which is a subnode of the dept block. The master block has a primary key (dname for our block dept) which is a foreign key for the detail block (dept for our block employee). To create the form we select the Forms node, click on the create button and change its name to Sample which is to be the name of this sample form. Changing the name requires placing the cursor at the end of the selected name(which is something like MODULE2), clicking the mouse button and entering the new name. Select the Blocks node and then the create button. This brings up the New Block Options dialog box which has four tabs labeled General, Items, Layout, Master/Detail. The box comes up with the General tab selected. The Base Table field contains <NONE>. In it we must enter dept. This can be done by either typing it in or by using the select button which will list the choices from the server. This brings up a Connect dialog box if the programmer is not connected to the server. Fill in the three fields and click the Connect button. A Tables dialog box comes up. Select the correct options and then the OK button. It should be noted here that the tables we are interested in are owned by PUBLIC and will not be seen unless you check the Other users box. In this case you will get a long list of tables to choose from. Unless the programmer is the owner of the table it is difficult to pare down this list. Once the correct Table name has been chosen and the Block Name has been chosen (just click on this field to automatically enter the table name) the Items tab should be selected and then click the Select Columns... button to get a list of the column names. Columns can be excluded by unchecking the Include box. Since we don't want any information about the floor uncheck the floor column. Click the OK box to finish creating the block DEPT. Now repeat this process creating a new block called EMPLOYEE. For the Items tab we want dept, ename, and salary. Select the Layout tab and place 6 in Records, and check the Integrity Constraints, Button Palette, Scrollbar boxes. Now select the Master/Detail tab and enter dept in the Master block field. If one enters this via the select button the Join Condition is automatically entered. The join condition in this case is dept.dname=employee.dept. Click the OK button. You should now see the following instances in the Object Navigator window: The object type Blocks, expands to DEPT, EMPLOYEE, and BUTTON_PALETTE. Expand each of these to see the Items. The BUTTON_PALETTE is present because we selected the Button Palette in the Layout tab when creating the employee block. The master/detail join relation shows up in the Relations node seen from expanding the DEPT block. Notice that the Triggers node shows two triggers which come about as a result of the master/detail relation. III. Testing the Form. Ultimately the form SAMPLE that we created will be run as a stand alone executable. Now, however, we are interested in testing the form before we continue on with the design process. This is requires generating and running the form. This can be done from within Forms Designer by first of all choosing Tools/Options from the Designer menu and ensuring that the Generate Before Run box is checked. Then from the File menu choose run. You can watch the message bar at the bottom of the screen which indicates the generate-run status. Eventually the run window comes up which contains the default graphic version of what we created. Everything that appears on screen at this point can be rearranged by using the layout editor from the Navigator window. This is easy to experiment with. The run window has a menu at the top. Choose query/execute. The buttons at the bottom of the window can be used to go forward or backward through the records. Experiment until you see how this works. We now wish to go back and modify the form so that the average salary is also present when running the form. Exit runforms. In the Object Navigator choose the Block object type and then click the create button to start a new block. Associate this with the base table employee but choose a name of employee2 since we already have a block of employee. Eliminate all of the columns in the items tab. Click OK. Now select the items node in the expansion of employee2 and then the create button. If the properties window doesn't open you will have to open it by choosing the Tools/Properies submenu in the Designer. Make sure that the Display/Item Type is set to Text Item, Display/Displayed is set to true, and Display/Canvas is set to the same canvas as your other blocks. This can be set by selecting Canvas in the properties window and then selecting the correct canvas the from drop list button near the top right hand side of the properties window. Depending on what you have done to this point it should be the case that there are only two choices - <NULL> and CANVASx where x is some small integer like 2 or 3. If x is 2, for example, then CANVAS2 is an object node in the navigator which represents the visual properties at run time. By selecting this node the programmer is put into the layout editor with the CANVAS2 window present. In the layout editor the programmer can rearrange the visual components on the screen in obvious ways by using the buttons on the button bar or the submenus at the top of the window. After the properties of the new item in employee2 have been set, select the Triggers node under the EMPLOYEE block in the navigator and again click the create button on the button bar. We are going to create a trigger for the EMPLOYEE block which will compute the average and fill it into the created item of the EMPLOYEE2 block. This created item will have a generic name such as ITEM5 unless you chose your own name when creating the Item instance. If you followed the above accurately(and made no mistakes) this item will be called ITEM5 since you previously created 4 items in - one in the DEPT block and three in the EMPLOYEE block. Since we will add our own heading for the run time form it doesn't make any difference what we call this item. The new trigger starts by bringing up a list of trigger types. Choose POST-BLOCK. This means that when the block has been traversed this trigger will execute. This is what we want. After choosing the trigger type the PL/SQL editor is brought up where one enters the PL/SQL code to make the trigger work. Enter the following DECLARE errtxt VARCHAR2(80) := ERROR_TEXT; BEGIN SELECT AVG(salary) INTO :employee2.item5 FROM employee WHERE dept=:dept.dname; EXCEPTION WHEN NO_DATA_FOUND THEN MESSAGE('NO DATA FOUND'); RAISE FORM_TRIGGER_FAILURE; WHEN TOO_MANY_ROWS THEN MESSAGE('TOO MANY RECORDS'); RAISE FORM_TRIGGER_FAILURE; WHEN OTHERS THEN MESSAGE(errtxt); RAISE FORM_TRIGGER_FAILURE; END; Here we are assuming that item5 is the name of the ITEM instance of EMPLOYEE2. The EXCEPTION entries are added for completeness. These allow an orderly exit if an error should occur. Generate and run the form. IV. Triggers. We have seen above a simple trigger. This is a short piece of PL/SQL code which is attached to an event, namely, the act of successfully navigating the block. They are almost always named after the event they are attached to. The trigger consists of a simple BEGIN-END block in PL/SQL. In the above case it also requires a declaration of a local variable. The block body consists of a PL/SQL select . The PL/SQL version of the select requires the INTO clause which refers to a forms variable. Forms variable are always preceded by a colon. It is a good idea to fully qualify forms variables by preceding the variable name by the block name and separated by a period. Both NO_DATA_FOUND and TOO_MANY_ROWS are built-in exception procedures. There are many of these exceptions such as VALUE_ERROR, ZERO_DIVIDE, and LOGIN_DENIED. ERROR_TEXT is a built in function which returns the message text of the oracle error. These predefined exceptions and functions are defined in the PL/SQL package known as STANDARD which is owned by the database SYS with execute permisson granted to PUBLIC. These can be used without prefacing the object with the package name. In general, an event which is caused by the response of the database to the application's request for supplying or acting on data can be PRE-Event, ON-Event, or POST-Event. WHEN-Event and KEY-Event are trigger types which are associated with user interface events. The execution of the trigger is described as the trigger firing. V. PL/SQL - the Basics. PL/SQL is a structured language. Its code is written in blocks enclosed by BEGIN and END statements. You can declare variables using the DECLARE statement. In fact, for PL/SQL variables matching database columns, you can use the predefined attribue %TYPE which returns the type of the object. For example, DECLARE v_customer_name customer.customer_name%TYPE will set the PL/SQL variable to the same type as the column customer_name in the block customer. Note in the above trigger example that a PL/SQL variable can be initialized in the DECLARE statement. Assignment is done using := . Then, any changes to the data type of the table will automatically cause a change to the PL/SQL variable data type. PL/SQL supports FOR loops, WHILE loops, GOTO statements, IF statements and ordinary SQL statements. Example of embedded loops <<outer_loop>> FOR ctr IN 1..10 LOOP ... <<middle_loop>> FOR ctr IN 1..100 LOOP ... <<inner_loop>> WHILE outer_loop.ctr < 5 ... END LOOP inner_loop; END LOOP middle_loop; END LOOP outer_loop; The quantities enclosed in << >> are labels. The example shows three nested loops. Example of GOTO GOTO label; label occurs some where as <<label>> Example of IF IF count_A < 10 THEN ... ELSEIF count_A < 20 THEN ... ELSEIF count_A < 30 THEN ... ELSE ... END IF; PL/SQL blocks can also be embedded into third generation languages like C. The block is executed as if it were a single SQL statement. Any variables used in the PL/SQL block have to be declared in the host program and the block has to be bracketed with the keywords EXEC SQL EXECUTE and END-EXEC. It can also be placed in a .sql file and run while in SQL with the start command. There is an example of this using a simple for loop to insert some values into a table in the file ~rhager/oracle/sample1.sql which you can copy and run. VI. Accessing Forms on rohan. To access Forms 4.5 one needs to have the following put in your .login: setenv LD_LIBRARY_PATH /opt/SUNWspro/lib:/opt/oracle/lib . Forms4.5 can then be started by giving f45desm . It should be noted that this is an x-client and therefore requires an x-interface. You can use any x-terminal on campus or an x-terminal emulator off campus but this is usually only acceptable using a cable modem or other very fast connection. The runforms executable is f45runm. VII. Getting Help. I recommend the following to learn more about forms, designer/2000 and PL/SQL. I. CBT training. These computer based training courses are available from the rohan website. This requires an aznet connection. They only run on a PC. The URL is http://uco.sdsu.edu/cbtweb/getstart.htm. There are many of these courses. There is a section called SQL-BASED DBMS(ORACLE). II. Cue Cards. These are accessed when in Forms. Choose the Help Submenu. III. Forms documentation. This is oracle documentation and is accessed with the runbook utility which has executable b22.runm. One then opens any oracle documentation file. These are .obd files(obd stands faor oracle book documentation). As of this time there are no .obd files for forms installed on rohan. I will keep you apraised of the situation. IV. The book Developing Oracle Forms Applications by Albert Lulushi, Prentice Hall 1996, http://www.prenhall.com, ISBN: 0-13-531229-9. It is 714 pages in length. V. The book ORACLE PL/SQL Programming by Scott Urman, Osborne McGrawHill 1996, ISBN 0-07-882176-2. It is 564 pages in length. VIII. The Project. A. This part consists of the following 25 SQL queries. These are done with the same database that you used in the last programming project. As is usual they will be turned in printed on the line printer on one continuous sheet with appropriate documentation and comments. Q31. Q32. List the suppliers that supply at least two items of type_ A to one same department on the second floor. (the logical order of quantifiers: (there exists department) (there exists item.)) List the suppliers that supply all the items of type_ A to departments on the second floor that sell all the items of type_ B. (The logical order of quantifiers: ("items of type_A) (there exists department) ("items of type_ B) .) List the items supplied by exactly one supplier (i.e., list the items always supplied by the same supplier). List the suppliers that are the only supplier of some item. List the suppliers that are the only supplier of at least ten items. Find the suppliers and their names for those suppliers that supply all departments. Find the suppliers and their names for those suppliers that supply all the departments supplied by supplier S200. Q34. Q35. Q36. Q38. Q39. Q40. Q41. Q42. Q43. Q44. Q45. Q46. Q47. Q48. Q49. Q51. Q52. Q55. Q56. Q58. Q60. Q62. Q63. List the departments for which each item supplied to the department is supplied to some other department as well. List each item supplied to at least two departments by each supplier that supplies it. List the items that are supplied only by the suppliers that supply some thing to all the departments (i.e., items such that every supplier that supplies it supplies something to all the departments). List the items supplied by Levi and sold in the men's department. List the items supplied by Levi or sold in the men's department. List the departments selling items of type A that aresupplied by Levi and/or which are sold by the men's department. Find the average salary of the employees in the Shoe department. Find, for each department, the average salary of the employees. Find, for each department on the second floor, the average salary of the employees. Find, for each department that sells items of type A, the average salary of the employees. What is the number of different items supplied bye each supplier that supplies all departments? What is, for each supplier that supplies all the departments, the number of different items supplied to each department? List suppliers that supply a total volume of items of types_ A and B that is altogether greater than 100. List the employees in the Shoe department and the difference of their salary from the average salary of all the departments. List each employee and the difference of his (her) salary from the average of his (her) department. For each department, find the average salary of the employees who earn more than the average salary of the department. List for each employee, his (her) salary, the average salary of the department where he (she) works, and the difference of his (her) salary from the average salary of his (her) department. What is the average volume of items of type_ A supplied per company that supplies them? B. For this part you are to create a simple form. The form will access the same database as in part A. It will have 8 fields . The user will enter the supplier number and department. As soon as the supplier number is entered, the supplier name will be brought up in its field automatically by the form. The form should bring to the screen (5 at a time) the name type_ and color of the items supplied to the given department by the given supplier. In addition there will be two fields which consist of a checkbox (not tied to a table) with a name of done and a date field. When the checkbox is checked(the only allowable entry should be the lower case letter x) the date should automatically appear in the date field. The date can be obtained by using the built-in system function SYSDATE which requires no arguments. You will demonstrate your form to me by running it in my presence. The details of the demonstration process will be explained in class. If you are unable to get your form to produce its output in the exact way described above you may alter the above design statement in as minimal way as possible. The more you alter it the less satisfactory your form becomes and the less credit you will get.

Find millions of documents on Course Hero - Study Guides, Lecture Notes, Reference Materials, Practice Exams and more. Course Hero has millions of course specific materials providing students with the best way to expand their education.

Below is a small sample set of documents: